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 quand RDS pour 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 le disque.* Pour plus d’informations sur les fichiers temporaires et leur utilisation, consultez [Gestion des fichiers temporaires avec PostgreSQL](PostgreSQL.ManagingTempFiles.md). **Topics** + [Versions de moteur prises en charge](#wait-event.iobuffile.context.supported) + [Contexte](#wait-event.iobuffile.context) + [Causes probables de l'augmentation du nombre d'événements d'attente](#wait-event.iobuffile.causes) + [Actions](#wait-event.iobuffile.actions) ## Versions de moteur prises en charge Ces informations sur les événements d’attente sont prises en charge pour toutes les versions de RDS pour 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](https://www.postgresql.org/docs/current/runtime-config-resource.html) 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 application de travail 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 pour PostgreSQL crée des fichiers temporaires dans le stockage local. Le disque I/O contenant ces fichiers temporaires peut 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é 1. 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 BY`Clause + `GROUP BY`Clause + `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. **Topics** + [Identifiez le problème](#wait-event.iobuffile.actions.problem) + [Examinez vos requêtes de jointure](#wait-event.iobuffile.actions.joins) + [Examinez vos requêtes ORDER BY et GROUP BY](#wait-event.iobuffile.actions.order-by) + [Évitez d'utiliser l'opération DISTINCT](#wait-event.iobuffile.actions.distinct) + [Envisagez d'utiliser des fonctions de fenêtrage à la place des fonctions GROUP BY](#wait-event.iobuffile.actions.window) + [Examinez les vues matérialisées et les instructions CTAS](#wait-event.iobuffile.actions.mv-refresh) + [Reconstruction des index à l'aide de pg\$1repack](#wait-event.iobuffile.actions.pg_repack) + [Augmentez maintenance\$1work\$1mem lorsque vous mettez des tables en cluster](#wait-event.iobuffile.actions.cluster) + [Réglez la mémoire pour empêcher les E/S : BufFileRead et E/S : BufFileWrite](#wait-event.iobuffile.actions.tuning-memory) ### Identifiez le problème Vous pouvez consulter l'utilisation des fichiers temporaires directement dans Performance Insights. Pour de plus amples informations, veuillez consulter [Affichage de l’utilisation des fichiers temporaires avec Performance Insights](PostgreSQL.ManagingTempFiles.Example.md). Lorsque Performance Insights est désactivé, vous remarquerez peut-être une augmentation `IO:BufFileRead` des `IO:BufFileWrite` opérations. 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 pour PostgreSQL consigne tous les fichiers temporaires. Si vous définissez la valeur `1024`, RDS pour 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](https://www.postgresql.org/docs/current/runtime-config-logging.html) 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 pour 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 pour 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\$1repack 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 [https://www.postgresql.org/docs/current/runtime-config-resource.html](https://www.postgresql.org/docs/current/runtime-config-resource.html) dans la documentation PostgreSQL. Une solution de contournement possible lors de la recréation d'un index volumineux consiste à utiliser l'extension pg\$1repack. Pour en savoir plus, consultez [Reorganize tables in PostgreSQL databases with minimal locks](https://reorg.github.io/pg_repack/) dans la documentation pg\$1repack. Pour obtenir des informations sur la configuration de l’extension dans votre instance de base de données RDS pour PostgreSQL, consultez [Réduction du gonflement des tables et des index avec l’extension pg\$1repack](Appendix.PostgreSQL.CommonDBATasks.pg_repack.md). ### Augmentez maintenance\$1work\$1mem lorsque vous mettez des tables en cluster La commande `CLUSTER` met en cluster la table spécifiée par *table\$1name* à partir d'un index existant spécifié par *index\$1name*. RDS pour 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 pour empêcher les E/S : BufFileRead et E/S : 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](https://www.postgresql.org/docs/current/runtime-config-resource.html) 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 application de travail 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 pour 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 pour 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.