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 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 ».
Rubriques
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
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 :
-
Diminution soudaine et brutale de la disponibilité
-
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.
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
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 clauseORDER 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 clauseLIMIT
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
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
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.