IO : DataFileRead - Amazon Relational Database Service
Versions de moteur prises en chargeContexteCauses probables de l'allongement des temps d'attenteActions

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.

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

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 plus d'informations sur le groupe de mémoires tampons, consultez Consommation des ressources dans la documentation PostgreSQL.

Causes probables de l'allongement des temps 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 de plus amples informations, veuillez consulter la documentation sur PostgreSQL. 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.

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.

Recherche des tables qui consomment de l'espace inutilement

Pour rechercher les tables qui consomment inutilement de l'espace, vous pouvez utiliser les fonctions issues de l'extension PostgreSQL pgstattuple. Cette extension (module) est disponible par défaut sur toutes les instances de base de données RDS for PostgreSQL et peut être instanciée sur l'instance à l'aide de la commande suivante.

CREATE EXTENSION pgstattuple;

Pour plus d'informations sur cette extension, consultez pgstattuple dans la documentation PostgreSQL.

Vous pouvez vérifier qu'il n'existe pas de gonflement de tables et d'index dans votre application. Pour en savoir plus, consultez Diagnostic du gonflement de la table et de l'index.

Recherche des index qui consomment de l'espace inutilement

Pour rechercher les index gonflés et estimer la quantité d'espace inutilement consommée sur les tables pour lesquelles vous disposez de privilèges de lecture, vous pouvez exécuter la requête suivante.

-- 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 functional 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 ce nombre sur ce que les vCPU de votre instance sont en mesure de paralléliser.

    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 Utilisation d'Amazon RDS Proxy .

  • Dans la mesure du possible, tirez parti des réplicas en lecture pour RDS for PostgreSQL. Lorsque votre application exécute une opération en lecture seule, envoyez ces demandes aux réplicas en lecture. Cette technique réduit la pression des E/S sur le nœud (d'écriture) principal.

  • Envisagez une augmentation d'échelle de votre instance de base de données. Une classe d'instances de plus grande capacité offre davantage de mémoire, ce qui permet à RDS for PostgreSQL de disposer d'un groupe de mémoires tampons partagées plus important pour stocker les pages. Cette plus grande taille confère également à l'instance de base de données davantage de vCPU pour gérer les connexions. Ce plus grand nombre de vCPU est particulièrement utile lorsque les opérations qui génèrent des événements d'attente IO:DataFileRead sont des écritures.