IO:DataFileRead

El evento IO:DataFileRead ocurre cuando una conexión espera en un proceso backend para leer una página requerida del almacenamiento porque la página no está disponible en la memoria compartida.

Versiones del motor admitidas

Esta información de eventos de espera es compatible con todas las versiones de RDS para PostgreSQL.

Context

Todas las consultas y operaciones de manipulación de datos (DML) acceden a páginas en el grupo de búferes. Las instrucciones que pueden inducir lecturas incluyen SELECT, UPDATE y DELETE. Por ejemplo, un UPDATE puede leer páginas de tablas o índices. Si la página que se solicita o actualiza no está en el grupo de búferes compartidos, esta lectura puede provocar el evento IO:DataFileRead.

Dado que el grupo de búferes compartidos es finito, puede llenarse. En este caso, las solicitudes de páginas que no están en la memoria obligan a la base de datos a leer bloques del disco. Si el evento IO:DataFileRead se produce con frecuencia, es posible que el grupo de búferes compartidos sea demasiado pequeño para acomodar la carga de trabajo. Este problema se agudiza en las consultas SELECT que leen un gran número de filas que no caben en el grupo de búferes. Para obtener más información acerca del grupo de búferes, consulte el punto Resource Consumption (Consumo de recursos) en la documentación de PostgreSQL.

Causas probables del aumento del tiempo de espera

Las causas más comunes del evento IO:DataFileRead son las siguientes:

Acciones

Recomendamos diferentes acciones en función de las causas del evento de espera.

Verificar los filtros de predicado para las consultas que generan esperas

Supongamos que identifica consultas específicas que generan eventos de espera IO:DataFileRead. Puede identificarlos con las siguientes técnicas:

Le recomendamos que determine qué filtros se utilizan en el predicado (cláusula WHERE) de estas consultas. Siga estas instrucciones:

Minimizar el efecto de las operaciones de mantenimiento

Las operaciones de mantenimiento como VACUUM y ANALYZE son importantes. Le recomendamos que no las desactive ya que encontrará eventos de espera IO:DataFileRead relacionados con estas operaciones de mantenimiento. Los siguientes enfoques pueden minimizar el efecto de estas operaciones:

La característica de autovacuum se activa de forma automática para una tabla cuando la siguiente fórmula es verdadera.

pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold

La vista pg_stat_user_tables y el catálogo pg_class tienen varias filas. Una fila puede corresponder a una fila de la tabla. Esta fórmula asume que las reltuples son para una tabla específica. Los parámetros autovacuum_vacuum_scale_factor (0,20 de forma predeterminada) y autovacuum_vacuum_threshold (50 tuplas de forma predeterminada) se suelen establecer de forma global para toda la instancia. Sin embargo, puede establecer valores diferentes para una tabla específica.

Buscar tablas que consuman espacio de forma innecesaria

Para encontrar tablas que consuman espacio innecesariamente, puede utilizar funciones de la extensión pgstattuple de PostgreSQL. Esta extensión (módulo) está disponible de forma predeterminada en todas las instancias de base de datos de RDS para PostgreSQL y se puede instanciar en la instancia con el siguiente comando.

CREATE EXTENSION pgstattuple;

Para obtener más información sobre esta extensión, consulte pgstattuple en la documentación de PostgreSQL.

Puede comprobar si hay una sobrecarga de tablas e índices en su aplicación. Para obtener más información, consulte Diagnóstico de sobrecarga de tablas e índices.

Buscar índices que consuman espacio de forma innecesaria

Para encontrar índices sobrecargados y estimar la cantidad de espacio consumida innecesariamente en las tablas para las que tiene privilegios de lectura, puede ejecutar la siguiente consulta.

-- 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;

Buscar tablas aptas para autovacuum

Para buscar tablas que se puedan vaciar automáticamente, ejecute la siguiente consulta.

--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;

Responder a un gran número de conexiones

Cuando se monitorea Amazon CloudWatch, se puede encontrar que la métrica DatabaseConnections se dispara. Este aumento indica un mayor número de conexiones a su base de datos. Se recomienda el siguiente enfoque: