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 : DataFileRead L'événement `IO:DataFileRead` se produit lorsqu'une connexion attend qu'un processus backend lise une page requise à partir du stockage parce que la page n'est pas disponible dans la mémoire partagée. **Topics** + [Versions de moteur prises en charge](#apg-waits.iodatafileread.context.supported) + [Contexte](#apg-waits.iodatafileread.context) + [Causes probables de l'augmentation du nombre d'événements d'attente](#apg-waits.iodatafileread.causes) + [Actions](#apg-waits.iodatafileread.actions) ## Versions de moteur prises en charge Ces informations sur les événements d’attente s’appliquent à toutes les versions d’Aurora PostgreSQL. ## Contexte Toutes les requêtes et opérations en langage de manipulation de données (DML) accèdent aux pages du groupe de mémoires tampons. Les instructions qui peuvent induire des lectures sont : `SELECT`, `UPDATE` et `DELETE`. Par exemple, une instruction `UPDATE` peut lire des pages à partir de tables ou d'index. Si la page demandée ou mise à jour ne se trouve pas dans le groupe de mémoires tampons partagées, cette lecture peut provoquer l'événement `IO:DataFileRead`. Comme le groupe de mémoires tampons partagées est limité, il peut être saturé. Dans ce cas, les requêtes de pages qui ne sont pas en mémoire forcent la base de données à lire des blocs sur le disque. Si l'événement `IO:DataFileRead` se produit fréquemment, votre groupe de mémoires tampons partagées est peut-être trop petit pour prendre en charge votre charge de travail. Ce problème se pose avec acuité pour les requêtes `SELECT` qui lisent un grand nombre de lignes qui ne rentrent pas dans le groupe de mémoires tampons. Pour en savoir plus sur le groupe de mémoires tampons, consultez [Groupe de mémoires tampons](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool). ## Causes probables de l'augmentation du nombre d'événements d'attente Les principales causes de l'événement `IO:DataFileRead` sont les suivantes : **Pics de connexion** Il se peut que plusieurs connexions génèrent le même nombre d'événements IO : DataFileRead wait. Dans ce cas, un pic (augmentation soudaine et importante) d'événements `IO:DataFileRead` peut se produire. **Instructions SELECT et DML effectuant des analyses séquentielles** Votre application est peut-être en train d'effectuer une nouvelle opération. Ou une opération existante peut changer suite à un nouveau plan d'exécution. Dans ce cas, recherchez les tables (en particulier les tables volumineuses) qui présentent une valeur `seq_scan` plus élevée. Pour les trouver, interrogez `pg_stat_user_tables`. Pour suivre les requêtes qui génèrent plus d'opérations de lecture, utilisez l'extension `pg_stat_statements`. **CTAS et CREATE INDEX pour les jeux de données volumineux** Un *CTAS* est une instruction `CREATE TABLE AS SELECT`. Si vous exécutez un CTAS en utilisant un jeu de données volumineux comme source, ou si vous créez un index sur une table volumineuse, l'événement `IO:DataFileRead` peut se produire. Lorsque vous créez un index, la base de données peut avoir besoin de lire l'objet entier à l'aide d'une analyse séquentielle. Un CTAS génère des lectures `IO:DataFile` lorsque les pages ne sont pas en mémoire. **Exécution simultanée de plusieurs processus employés vacuum** Les processus employés vacuum peuvent être déclenchés manuellement ou automatiquement. Nous vous recommandons d'adopter une stratégie vacuum agressive. Toutefois, lorsqu'une table comporte de nombreuses lignes mises à jour ou supprimées, le nombre d'attentes `IO:DataFileRead` augmente. Une fois l'espace récupéré, le temps vacuum passé sur `IO:DataFileRead` diminue. **Ingestion de grandes quantités de données** Lorsque votre application ingère de grandes quantités de données, les opérations `ANALYZE` peuvent être plus fréquentes. Le processus `ANALYZE` peut être déclenché par un lanceur autovacuum, ou être appelé manuellement. L'opération `ANALYZE` lit un sous-ensemble de la table. Le nombre de pages à analyser est calculé en multipliant 30 par la valeur `default_statistics_target`. Pour plus d’informations, consultez la [documentation sur PostgreSQL](https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET). Le paramètre `default_statistics_target` accepte des valeurs comprises entre 1 et 10 000, la valeur par défaut étant 100. **Pénurie de ressources** Si l'UC ou la bande passante réseau de l'instance sont entièrement utilisés, l'événement `IO:DataFileRead` peut se produire plus fréquemment. ## Actions Nous vous recommandons différentes actions en fonction des causes de votre événement d’attente. **Topics** + [Vérifiez les filtres de prédicat pour détecter les requêtes qui génèrent des attentes](#apg-waits.iodatafileread.actions.filters) + [Minimisez l'effet des opérations de maintenance](#apg-waits.iodatafileread.actions.maintenance) + [Réagissez à un nombre élevé de connexions](#apg-waits.iodatafileread.actions.connections) ### Vérifiez les filtres de prédicat pour détecter les requêtes qui génèrent des attentes Supposons que vous identifiez des requêtes spécifiques qui génèrent des événements d'attente `IO:DataFileRead`. Vous pouvez les identifier à l'aide des techniques suivantes : + Performance Insights + Vues catalogue telles que celles fournies par l'extension `pg_stat_statements` + Vue catalogue `pg_stat_all_tables`, si elle affiche périodiquement un nombre accru de lectures physiques + Vue `pg_statio_all_tables`, si elle montre que les compteurs `_read` sont en augmentation Nous vous recommandons de déterminer quels filtres sont utilisés dans le prédicat (clause `WHERE`) de ces requêtes. Suivez ces instructions : + Exécutez la commande `EXPLAIN`. Dans la sortie, identifiez les types d'analyses utilisés. Une analyse séquentielle n'indique pas nécessairement un problème. Les requêtes qui utilisent des analyses séquentielles produisent naturellement plus d'événements `IO:DataFileRead` par rapport aux requêtes qui utilisent des filtres. Vérifiez que la colonne répertoriée dans la clause `WHERE` est indexée. Si ce n'est pas le cas, envisagez de créer un index pour cette colonne. Cette approche évite les analyses séquentielles et réduit le nombre d'événements `IO:DataFileRead`. Si une requête comporte des filtres restrictifs et continue à produire des analyses séquentielles, assurez-vous que les index appropriés sont utilisés. + Déterminez si la requête accède à une table très volumineuse. Dans certains cas, le partitionnement d'une table peut améliorer les performances, en permettant à la requête de ne lire que les partitions nécessaires. + Examinez la cardinalité (nombre total de lignes) de vos opérations de jointure. Notez le caractère restrictif des valeurs que vous transmettez dans les filtres de la clause `WHERE`. Si possible, ajustez votre requête pour réduire le nombre de lignes transmises à chaque étape du plan. ### Minimisez l'effet des opérations de maintenance Les opérations de maintenance telles que `VACUUM` et `ANALYZE` sont importantes. Nous vous recommandons de ne pas les désactiver parce que vous trouvez des événements d’attente `IO:DataFileRead` liés à ces opérations de maintenance. Les approches suivantes peuvent minimiser l’effet de ces opérations : + Exécutez les opérations de maintenance manuellement pendant les heures creuses. Cette technique empêche la base de données d'atteindre le seuil des opérations automatiques. + Pour les tables très volumineuses, partitionnez la table. Cette technique permet de réduire les frais liés aux opérations de maintenance. La base de données accède uniquement aux partitions qui nécessitent une maintenance. + Lorsque vous intégrez de grandes quantités de données, pensez à désactiver la fonction d'analyse automatique. La fonction autovacuum est automatiquement déclenchée pour une table lorsque la formule suivante est vraie. ``` pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold ``` La vue `pg_stat_user_tables` et le catalogue `pg_class` comportent plusieurs lignes. Une ligne peut correspondre à une ligne de votre table. Cette formule suppose que les `reltuples` sont destinés à une table spécifique. Les paramètres `autovacuum_vacuum_scale_factor` (0,20 par défaut) et `autovacuum_vacuum_threshold` (50 tuples par défaut) sont généralement définis globalement pour l'ensemble de l'instance. Vous pouvez toutefois définir des valeurs différentes pour une table spécifique. **Topics** + [Recherche des tables qui consomment de l'espace inutilement](#apg-waits.iodatafileread.actions.maintenance.tables) + [Recherchez les index qui consomment inutilement de l'espace](#apg-waits.iodatafileread.actions.maintenance.indexes) + [Recherchez les tables éligibles au processus autovacuum](#apg-waits.iodatafileread.actions.maintenance.autovacuumed) #### Recherche des tables qui consomment de l'espace inutilement Pour trouver les tables consommant plus d'espace que nécessaire, exécutez la requête suivante. Lorsque cette requête est exécutée par un rôle d'utilisateur de base de données qui n'a pas le rôle `rds_superuser`, elle renvoie des informations sur les seules tables pour lesquelles le rôle de l'utilisateur détient les autorisations de lecture. Cette requête est prise en charge par PostgreSQL version 12 et versions ultérieures. ``` WITH report AS ( SELECT schemaname ,tblname ,n_dead_tup ,n_live_tup ,block_size*tblpages AS real_size ,(tblpages-est_tblpages)*block_size AS extra_size ,CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages)/tblpages::float ELSE 0 END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*block_size AS bloat_size ,CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float ELSE 0 END AS bloat_ratio ,is_na FROM ( SELECT ceil( reltuples / ( (block_size-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages ,ceil( reltuples / ( (block_size-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff ,tblpages ,fillfactor ,block_size ,tblid ,schemaname ,tblname ,n_dead_tup ,n_live_tup ,heappages ,toastpages ,is_na FROM ( SELECT ( 4 + tpl_hdr_size + tpl_data_size + (2*ma) - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END ) AS tpl_size ,block_size - page_hdr AS size_per_block ,(heappages + toastpages) AS tblpages ,heappages ,toastpages ,reltuples ,toasttuples ,block_size ,page_hdr ,tblid ,schemaname ,tblname ,fillfactor ,is_na ,n_dead_tup ,n_live_tup FROM ( SELECT tbl.oid AS tblid ,ns.nspname AS schemaname ,tbl.relname AS tblname ,tbl.reltuples AS reltuples ,tbl.relpages AS heappages ,coalesce(toast.relpages, 0) AS toastpages ,coalesce(toast.reltuples, 0) AS toasttuples ,psat.n_dead_tup AS n_dead_tup ,psat.n_live_tup AS n_live_tup ,24 AS page_hdr ,current_setting('block_size')::numeric AS block_size ,coalesce(substring( array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor ,CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma ,23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END AS tpl_hdr_size ,sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024) ) AS tpl_data_size ,bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR count(att.attname) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON (att.attrelid = tbl.oid) JOIN pg_stat_all_tables AS psat ON (tbl.oid = psat.relid) JOIN pg_namespace AS ns ON (ns.oid = tbl.relnamespace) LEFT JOIN pg_stats AS s ON (s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname) LEFT JOIN pg_class AS toast ON (tbl.reltoastrelid = toast.oid) WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind = 'r' GROUP BY tbl.oid, ns.nspname, tbl.relname, tbl.reltuples, tbl.relpages, toastpages, toasttuples, fillfactor, block_size, ma, n_dead_tup, n_live_tup ORDER BY schemaname, tblname ) AS s ) AS s2 ) AS s3 ORDER BY bloat_size DESC ) SELECT * FROM report WHERE bloat_ratio != 0 -- AND schemaname = 'public' -- AND tblname = 'pgbench_accounts' ; -- WHERE NOT is_na -- AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1 ``` Vous pouvez vérifier qu'il n'existe pas de gonflement de tables et d'index dans votre application. Pour de plus amples informations, veuillez consulter [Diagnostic du gonflement de la table et de l'index](AuroraPostgreSQL.diag-table-ind-bloat.md). #### Recherchez les index qui consomment inutilement de l'espace Pour rechercher les index qui consomment inutilement de l'espace, exécutez la requête suivante. ``` -- WARNING: run with a nonsuperuser role, the query inspects -- only indexes on tables you have permissions to read. -- WARNING: rows with is_na = 't' are known to have bad statistics ("name" type is not supported). -- This query is compatible with PostgreSQL 8.2 and later. SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size, bs*(relpages-est_pages)::bigint AS extra_size, 100 * (relpages-est_pages)::float / relpages AS extra_ratio, fillfactor, bs*(relpages-est_pages_ff) AS bloat_size, 100 * (relpages-est_pages_ff)::float / relpages AS bloat_ratio, is_na -- , 100-(sub.pst).avg_leaf_density, est_pages, index_tuple_hdr_bm, -- maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, sub.reltuples, sub.relpages -- (DEBUG INFO) FROM ( SELECT coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth) ) AS est_pages, coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0 ) AS est_pages_ff, bs, nspname, table_oid, tblname, idxname, relpages, fillfactor, is_na -- , stattuple.pgstatindex(quote_ident(nspname)||'.'||quote_ident(idxname)) AS pst, -- index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO) FROM ( SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, relam, table_oid, fillfactor, ( index_tuple_hdr_bm + maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign ELSE index_tuple_hdr_bm%maxalign END + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN WHEN nulldatawidth = 0 THEN 0 WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign ELSE nulldatawidth::integer%maxalign END )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO) FROM ( SELECT i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.relam, a.attrelid AS table_oid, current_setting('block_size')::numeric AS bs, fillfactor, CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS maxalign, /* per page header, fixed size: 20 for 7.X, 24 for others */ 24 AS pagehdr, /* per page btree opaque data */ 16 AS pageopqdata, /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */ CASE WHEN max(coalesce(s.null_frac,0)) = 0 THEN 2 -- IndexTupleData size ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8) END AS index_tuple_hdr_bm, /* data len: we remove null values save space using it fractionnal part from stats */ sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth, max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na FROM pg_attribute AS a JOIN ( SELECT nspname, tbl.relname AS tblname, idx.relname AS idxname, idx.reltuples, idx.relpages, idx.relam, indrelid, indexrelid, indkey::smallint[] AS attnum, coalesce(substring( array_to_string(idx.reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor FROM pg_index JOIN pg_class idx ON idx.oid=pg_index.indexrelid JOIN pg_class tbl ON tbl.oid=pg_index.indrelid JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace WHERE pg_index.indisvalid AND tbl.relkind = 'r' AND idx.relpages > 0 ) AS i ON a.attrelid = i.indexrelid JOIN pg_stats AS s ON s.schemaname = i.nspname AND ((s.tablename = i.tblname AND s.attname = pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE)) -- stats from tbl OR (s.tablename = i.idxname AND s.attname = a.attname)) -- stats from functionnal cols JOIN pg_type AS t ON a.atttypid = t.oid WHERE a.attnum > 0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 ) AS s1 ) AS s2 JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree' ) AS sub -- WHERE NOT is_na ORDER BY 2,3,4; ``` #### Recherchez les tables éligibles au processus autovacuum Pour rechercher les tables éligibles au processus autovacuum, exécutez la requête suivante. ``` --This query shows tables that need vacuuming and are eligible candidates. --The following query lists all tables that are due to be processed by autovacuum. -- During normal operation, this query should return very little. WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') , vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') , fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') , sto AS (SELECT opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value FROM (SELECT oid opt_oid, unnest(reloptions) setting FROM pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation , pg_size_pretty(pg_table_size(c.oid)) as table_size , age(relfrozenxid) as xid_age , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age , (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples , n_dead_tup as dead_tuples FROM pg_class c JOIN pg_namespace ns ON ns.oid = c.relnamespace JOIN pg_stat_all_tables stat ON stat.relid = c.oid JOIN vbt on (1=1) JOIN vsf ON (1=1) JOIN fma on (1=1) LEFT JOIN sto cvbt ON cvbt.param = 'autovacuum_vacuum_threshold' AND c.oid = cvbt.opt_oid LEFT JOIN sto cvsf ON cvsf.param = 'autovacuum_vacuum_scale_factor' AND c.oid = cvsf.opt_oid LEFT JOIN sto cfma ON cfma.param = 'autovacuum_freeze_max_age' AND c.oid = cfma.opt_oid WHERE c.relkind = 'r' AND nspname <> 'pg_catalog' AND ( age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) or coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup -- or 1 = 1 ) ORDER BY age(relfrozenxid) DESC; ``` ### Réagissez à un nombre élevé de connexions Lorsque vous surveillez Amazon CloudWatch, vous constaterez peut-être que les `DatabaseConnections` statistiques augmentent. Cette augmentation indique un nombre accru de connexions à votre base de données. Nous vous recommandons l'approche suivante : + Limitez le nombre de connexions que l'application peut ouvrir avec chaque instance. Si votre application dispose d'une fonction intégrée de regroupement des connexions, définissez-la sur un nombre raisonnable de connexions. Basez le nombre sur ce que le v CPUs de votre instance peut paralléliser efficacement. Si votre application n'utilise pas de fonction de regroupement des connexions, envisagez d'utiliser un proxy Amazon RDS ou une autre solution. Cette approche permet à votre application d'ouvrir plusieurs connexions à l'aide de l'équilibreur de charge. L'équilibreur peut alors ouvrir un nombre restreint de connexions avec la base de données. Comme le nombre de connexions exécutées en parallèle est moindre, votre instance de base de données effectue moins de changements de contexte dans le noyau. Les requêtes progressent plus rapidement, ce qui entraîne une diminution des événements d'attente. Pour de plus amples informations, veuillez consulter [Proxy Amazon RDS pour Aurora](rds-proxy.md). + Dans la mesure du possible, tirez parti des nœuds de lecture pour Aurora PostgreSQL et des réplicas en lecture pour RDS pour PostgreSQL. Lorsque votre application exécute une opération en lecture seule, envoyez ces requêtes au point de terminaison en lecture seule. Cette technique répartit les demandes d'application sur tous les nœuds de lecture, réduisant ainsi la I/O pression sur le nœud d'écriture. + Envisagez une augmentation d'échelle de votre instance de base de données. Une classe d'instance de plus grande capacité offre davantage de mémoire, ce qui permet à Aurora PostgreSQL de disposer d'un groupe de mémoires tampons partagées plus important pour contenir les pages. La plus grande taille donne également à l'instance de base de données plus CPUs de v pour gérer les connexions. More v CPUs est particulièrement utile lorsque les opérations qui génèrent des événements d'`IO:DataFileRead`attente sont des écritures.