Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

IO:BufFileRead et IO:BufFileWrite

Les événements IO:BufFileRead et IO:BufFileWrite se produisent lorsqu'Aurora 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 s'appliquent à toutes les versions d'Aurora 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 en savoir plus sur ces zones de la mémoire locale, consultez Zone de mémoire de travail et Zone de mémoire des travaux de maintenance.

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, Aurora PostgreSQL crée des fichiers temporaires sur 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 :

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 :

Actions

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

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. Procédez comme suit :

Un schéma en dents de scie indique une consommation et une libération rapides du stockage, souvent associées à des fichiers temporaires. Si vous observez ce schéma, activez Performance Insights. Lorsque vous utilisez Performance Insights, vous pouvez identifier quand les événements d'attente se produisent et quelles requêtes y sont associées. Votre solution dépend de la requête spécifique qui est à l'origine des événements.

Ou définissez le paramètre log_temp_files. Ce paramètre enregistre toutes les requêtes générant un nombre de Ko de fichiers temporaires supérieur au seuil. Si la valeur est 0, Aurora PostgreSQL enregistre tous les fichiers temporaires. Si la valeur est 1024, Aurora PostgreSQL enregistre 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

Votre application utilise probablement 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 :

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 :

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 :

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. En outre, si vous utilisez Amazon Aurora PostgreSQL 10 ou version 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 :

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 Aurora 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.

Utilisez pg_repack lorsque vous créez des index

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, consultez Zone de mémoire des travaux de maintenance.

Une solution de contournement possible lors de la recréation d'un index volumineux consiste à utiliser l'outil pg_repack. Pour en savoir plus, consultez Reorganize tables in PostgreSQL databases with minimal locks dans la documentation 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. Aurora 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 de trouver un équilibre par rapport aux exigences suivantes :

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 plus d'informations, consultez Zone de mémoire de travail.

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. Pour en savoir plus sur le groupe de mémoires tampons, consultez Groupe de mémoires tampons.

Par exemple, supposons que votre classe d'instance Aurora PostgreSQL soit db.r5.2xlarge. Cette classe dispose de 64 Gio de mémoire. Par défaut, 75 % de la mémoire est réservée au 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 d'Aurora 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 :

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.