IO:BufFileRead et IO:BufFileWrite

Les événements IO:BufFileRead et IO:BufFileWrite se produisent quand RDS for PostgreSQL crée des fichiers temporaires. Lorsque des opérations requièrent plus de mémoire que n'en confèrent les paramètres de mémoire de travail définis, elles écrivent des données temporaires sur un stockage permanent. Cette opération est parfois appelée « déversement sur disque ».

Versions de moteur prises en charge

Ces informations sur les événements d'attente sont prises en charge pour toutes les versions de RDS for PostgreSQL.

Contexte

IO:BufFileRead et IO:BufFileWrite se rapportent à la zone de mémoire de travail et à la zone de mémoire des travaux de maintenance. Pour plus d'informations sur ces zones de mémoire locale, consultez Consommation des ressources dans la documentation PostgreSQL.

La valeur par défaut du paramètre work_mem est 4 Mo. Si une session effectue des opérations en parallèle, chaque employé gérant le parallélisme utilise 4 Mo de mémoire. Par conséquent, définissez work_mem prudemment. Si vous augmentez trop la valeur, une base de données exécutant plusieurs sessions peut utiliser trop de mémoire. Si vous définissez une valeur trop faible, RDS for PostgreSQL crée des fichiers temporaires dans le stockage local. Les I/O disque de ces fichiers temporaires peuvent réduire les performances.

Si vous observez la séquence d'événements suivante, votre base de données génère peut-être des fichiers temporaires :

1. Diminution soudaine et brutale de la disponibilité

2. Récupération rapide de l'espace libre

Vous pouvez également observer un schéma en dents de scie. Ce schéma peut indiquer que votre base de données crée constamment de petits fichiers.

Causes probables de l'augmentation du nombre d'événements d'attente

En général, ces événements d'attente sont provoqués par des opérations qui utilisent plus de mémoire que n'en allouent les paramètres work_mem ou maintenance_work_mem. Pour compenser, les opérations écrivent dans des fichiers temporaires. Les principales causes des événements IO:BufFileRead et IO:BufFileWrite sont les suivantes :

Requêtes nécessitant plus de mémoire qu'il n'en existe dans la zone de mémoire de travail

Les requêtes présentant les caractéristiques suivantes utilisent la zone de mémoire de travail :

• Jointures par hachage

• ORDER BYClause

• GROUP BYClause

• DISTINCT

• Fonctions de fenêtrage

• CREATE TABLE AS SELECT

• Actualisation de la vue matérialisée

Instructions nécessitant plus de mémoire qu'il n'en existe dans la zone de mémoire des travaux de maintenance

Les instructions suivantes utilisent la zone de mémoire des travaux de maintenance :

• CREATE INDEX

• CLUSTER

Actions

Nous vous recommandons différentes actions en fonction des causes de votre événement d'attente.

Rubriques

• Identifiez le problème
• Examinez vos requêtes de jointure
• Examinez vos requêtes ORDER BY et GROUP BY
• Évitez d'utiliser l'opération DISTINCT
• Envisagez d'utiliser des fonctions de fenêtrage à la place des fonctions GROUP BY
• Examinez les vues matérialisées et les instructions CTAS
• Reconstruction des index à l'aide de pg_repack
• Augmentez maintenance_work_mem lorsque vous mettez des tables en cluster
• Réglez la mémoire de manière à éviter IO:BufFileRead et IO:BufFileWrite

Identifiez le problème

Imaginons une situation dans laquelle Performance Insights n'est pas activé et dans laquelle vous soupçonnez que les événements IO:BufFileRead et IO:BufFileWrite se produisent plus souvent qu'à l'accoutumée. Pour identifier la source du problème, vous pouvez définir le paramètre log_temp_files de manière à consigner toutes les requêtes qui génèrent un nombre de Ko de fichiers temporaires supérieur au seuil spécifié. Par défaut, log_temp_files est défini sur -1, ce qui désactive cette fonctionnalité de journalisation. Si vous définissez ce paramètre sur 0, RDS for PostgreSQL consigne tous les fichiers temporaires. Si vous définissez la valeur 1024, RDS for PostgreSQL consigne toutes les requêtes qui produisent des fichiers temporaires de plus de 1 Mo. Pour en savoir plus sur log_temp_files, consultez Error Reporting and Logging dans la documentation PostgreSQL.

Examinez vos requêtes de jointure

Il est probable que votre requête utilise des jointures. Par exemple, la requête suivante joint quatre tables.

SELECT * 
       FROM "order" 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

Les pics d'utilisation des fichiers temporaires peuvent être dus à un problème dans la requête proprement dite. Par exemple, une clause rompue peut ne pas filtrer correctement les jointures. Prenons la deuxième jointure interne de l'exemple suivant.

SELECT * 
       FROM "order"
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

La requête précédente joint par erreur customer.id à customer.id, générant un produit cartésien entre chaque client et chaque commande. Ce type de jointure accidentelle génère des fichiers temporaires volumineux. Selon la taille des tables, une requête cartésienne peut même saturer le stockage. Votre application peut présenter des jointures cartésiennes lorsque les conditions suivantes sont réunies :

• Vous observez des baisses importantes et brutales de la disponibilité du stockage, suivies d'une récupération rapide.

• Aucun index n'est créé.

• Aucune instruction CREATE TABLE FROM SELECT n'est émise.

• Aucune vue matérialisée n'est actualisée.

Pour savoir si les tables sont jointes à l'aide des clés appropriées, examinez votre requête et les directives de mappage objet-relationnel. Gardez à l'esprit que certaines requêtes de votre application ne sont pas appelées en permanence, et que certaines requêtes sont générées dynamiquement.

Examinez vos requêtes ORDER BY et GROUP BY

Dans certains cas, une clause ORDER BY peut entraîner un nombre excessif de fichiers temporaires. Considérez les directives suivantes :

• N'incluez des colonnes dans une clause ORDER BY que lorsqu'elles doivent être classées. Cette directive est particulièrement importante pour les requêtes qui renvoient des milliers de lignes et spécifient de nombreuses colonnes dans la clause ORDER BY.

• N'hésitez pas à créer des index pour accélérer les clauses ORDER BY lorsqu'elles correspondent à des colonnes qui présentent le même ordre croissant ou décroissant. Les index partiels sont préférables car ils sont plus petits. Les index de petite taille sont lus et parcourus plus rapidement.

• Si vous créez des index pour des colonnes qui peuvent accepter des valeurs nulles, déterminez si vous souhaitez que les valeurs nulles soient stockées à la fin ou au début des index.

  Si possible, réduisez le nombre de lignes à classer en filtrant l'ensemble de résultats. Si vous utilisez des instructions ou des sous-requêtes liées à la clause WITH, n'oubliez pas qu'une requête interne génère un ensemble de résultats et le transmet à la requête externe. Plus le nombre de lignes qu'une requête peut filtrer est élevé, moins elle a de classement à effectuer.

• Si vous n'avez pas besoin de l'ensemble de résultats complet, utilisez la clause LIMIT. Par exemple, si vous avez uniquement besoin des cinq premières lignes, une requête utilisant la clause LIMIT ne continue pas à générer des résultats. La requête a ainsi besoin de moins de mémoire et de moins de fichiers temporaires.

Une requête qui utilise une clause GROUP BY peut également avoir besoin de fichiers temporaires. Les requêtes GROUP BY résument les valeurs à l'aide de fonctions telles que les suivantes :

• COUNT

• AVG

• MIN

• MAX

• SUM

• STDDEV

Pour régler les requêtes GROUP BY, suivez les recommandations relatives aux requêtes ORDER BY.

Évitez d'utiliser l'opération DISTINCT

Dans la mesure du possible, évitez d'utiliser l'opération DISTINCT pour supprimer les lignes en double. Plus votre requête renvoie de lignes inutiles et en double, plus l'opération DISTINCT devient coûteuse. Si possible, ajoutez des filtres dans la clause WHERE, même si vous utilisez les mêmes filtres pour différentes tables. Un filtrage de la requête et une jointure correctes vous permettent d'améliorer les performances et de réduire l'utilisation des ressources. Ils vous permettent également d'éviter les rapports et les résultats incorrects.

Si vous devez utiliser DISTINCT pour plusieurs lignes d'une même table, n'hésitez pas à créer un index composite. Le regroupement de plusieurs colonnes dans un index peut améliorer le temps nécessaire à l'évaluation des lignes distinctes. De plus, si vous utilisez RDS for PostgreSQL version 10 ou ultérieure, vous pouvez corréler les statistiques entre plusieurs colonnes à l'aide de la commande CREATE STATISTICS.

Envisagez d'utiliser des fonctions de fenêtrage à la place des fonctions GROUP BY

Avec GROUP BY, vous modifiez l'ensemble de résultats, puis récupérez le résultat agrégé. Avec les fonctions de fenêtrage, vous pouvez agréger les données sans modifier l'ensemble de résultats. Une fonction de fenêtrage utilise la clause OVER pour effectuer des calculs sur les ensembles définis par la requête, en corrélant une ligne avec une autre. Les fonctions de fenêtrage vous permettent d'utiliser toutes les fonctions GROUP BY ainsi que les fonctions suivantes :

• RANK

• ARRAY_AGG

• ROW_NUMBER

• LAG

• LEAD

Pour réduire le nombre de fichiers temporaires générés par une fonction de fenêtrage, supprimez les doublons d'un même ensemble de résultats lorsque vous avez besoin de deux agrégations distinctes. Considérons la requête suivante :

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

Vous pouvez réécrire la requête en utilisant la clause WINDOW comme suit.

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Par défaut, le planificateur d'exécution RDS for PostgreSQL regroupe les nœuds similaires afin de ne pas dupliquer les opérations. Toutefois, en utilisant une déclaration explicite pour le bloc de fenêtres, vous pouvez gérer la requête plus facilement. Vous pouvez également améliorer les performances en empêchant la duplication.

Examinez les vues matérialisées et les instructions CTAS

Lorsqu'une vue matérialisée est actualisée, elle exécute une requête. Cette requête peut contenir une opération telle que GROUP BY, ORDER BY ou DISTINCT. Lors d'une actualisation, vous pouvez observer un grand nombre de fichiers temporaires et les événements d'attente IO:BufFileWrite et IO:BufFileRead. De même, lorsque vous créez une table basée sur une instruction SELECT, l'instruction CREATE TABLE exécute une requête. Pour réduire le nombre de fichiers temporaires nécessaires, optimisez la requête.

Reconstruction des index à l'aide de pg_repack

Lorsque vous créez un index, le moteur classe l'ensemble de résultats. À mesure que la taille des tables augmente et que les valeurs de la colonne indexée se diversifient, les fichiers temporaires ont besoin de plus d'espace. Dans la plupart des cas, vous ne pouvez pas empêcher la création de fichiers temporaires pour les tables volumineuses sans modifier la zone de mémoire des travaux de maintenance. Pour plus d'informations sur maintenance_work_mem, consultez maintenance_work_mem dans la documentation PostgreSQL.

Une solution de contournement possible lors de la recréation d'un index volumineux consiste à utiliser l'extension pg_repack. Pour en savoir plus, consultez Reorganize tables in PostgreSQL databases with minimal locks dans la documentation pg_repack. Pour obtenir des informations sur la configuration de l'extension dans votre instance de base de données RDS for PostgreSQL, consultez Réduction du ballonnement des tables et des index avec l'extension pg_repack.

Augmentez maintenance_work_mem lorsque vous mettez des tables en cluster

La commande CLUSTER met en cluster la table spécifiée par table_name à partir d'un index existant spécifié par index_name. RDS for PostgreSQL recrée physiquement la table en suivant l'ordre d'un index donné.

Lorsque le stockage magnétique était prédominant, la mise en cluster était courante car le débit de stockage était limité. Maintenant que le stockage SSD est plus répandu, la mise en cluster est moins fréquente. Toutefois, en mettant des tables en cluster, vous pouvez encore bénéficier d'une légère amélioration des performances en fonction de la taille de la table, de l'index, de la requête, etc.

Si vous exécutez la commande CLUSTER et observez les événements d'attente IO:BufFileWrite et IO:BufFileRead, réglez maintenance_work_mem. Augmentez la taille de la mémoire en la définissant sur une valeur relativement élevée. Une valeur élevée permettra au moteur d'utiliser davantage de mémoire pour l'opération de mise en cluster.

Réglez la mémoire de manière à éviter IO:BufFileRead et IO:BufFileWrite

Dans certaines situations, vous devez régler la mémoire. Votre objectif est d'équilibrer la mémoire entre les zones de consommation suivantes à l'aide des paramètres appropriés, comme suit.

• La valeur work_mem

• La mémoire restant après déduction de la valeur shared_buffers

• Nombre maximal de connexions ouvertes et en cours d'utilisation, qui est limité par max_connections

Pour plus d'informations sur le réglage de la mémoire, consultez Consommation des ressources dans la documentation PostgreSQL.

Augmentez la taille de la zone de mémoire de travail

Dans certains cas, votre seule option consiste à augmenter la mémoire utilisée par votre session. Si vos requêtes sont correctement écrites et utilisent les bonnes clés pour les jointures, augmentez la valeur work_mem.

Pour savoir combien de fichiers temporaires une requête génère, définissez log_temp_files sur 0. Si vous définissez la valeur work_mem sur la valeur maximale identifiée dans les journaux, vous empêchez la requête de générer des fichiers temporaires. Toutefois, work_mem définit le maximum par nœud du plan pour chaque connexion ou employé parallèle. Si la base de données compte 5 000 connexions, et si chacune d'entre elles utilise 256 Mio de mémoire, le moteur a besoin de 1,2 Tio de RAM. Votre instance risque donc de manquer de mémoire.

Réservez suffisamment de mémoire pour le groupe de mémoires tampons partagées

Votre base de données utilise des zones de mémoire telles que le groupe de mémoires tampons partagées, et pas seulement la zone de mémoire de travail. Prenez en compte les besoins de ces zones de mémoire supplémentaires avant d'augmenter work_mem.

Par exemple, supposons que votre classe d'instances RDS for PostgreSQL est db.r5.2xlarge. Cette classe dispose de 64 Gio de mémoire. Par défaut, 25 % de la mémoire est réservée pour le groupe de mémoires tampons partagées. Après avoir soustrait la quantité allouée à la zone de mémoire partagée, il reste 16 384 Mo. Évitez d'allouer la mémoire restante exclusivement à la zone de mémoire de travail, car le système d'exploitation et le moteur ont également besoin de mémoire.

La mémoire que vous pouvez allouer à work_mem dépend de la classe d'instance. Si vous utilisez une classe d'instance plus importante, vous disposerez de plus de mémoire. Toutefois, dans l'exemple précédent, vous ne pouvez pas utiliser plus de 16 Gio. Sinon votre instance devient indisponible lorsqu'elle est à court de mémoire. Pour récupérer l'instance en cas d'indisponibilité, les services d'automatisation de RDS for PostgreSQL redémarrent automatiquement.

Gérez le nombre de connexions

Supposons que votre instance de base de données compte 5 000 connexions simultanées. Chaque connexion utilise au moins 4 Mio de work_mem. La forte consommation de mémoire des connexions est susceptible de dégrader les performances. En réponse, vous disposez des options suivantes :

• Passez à une classe d'instance supérieure.

• Réduisez le nombre de connexions simultanées à la base de données à l'aide d'un proxy ou d'un regroupement de connexions.

Pour les proxies, utilisez Amazon RDS Proxy, pgBouncer ou un regroupement de connexions basé sur votre application. Cette solution réduit la charge de l'UC. Elle réduit également le risque lorsque toutes les connexions ont besoin de la zone de mémoire de travail. Lorsque les connexions à la base de données sont moins nombreuses, vous pouvez augmenter la valeur de work_mem. De cette façon, vous réduisez l'occurrence des événements d'attente IO:BufFileRead et IO:BufFileWrite. De plus, les requêtes en attente d'accès à la zone de mémoire de travail s'accélèrent de manière significative.