IO:DataFileRead - Amazon Aurora
Versões compatíveis do mecanismoContextoPossíveis causas do maior número de esperasAções

IO:DataFileRead

O evento IO:DataFileRead ocorre quando uma conexão aguarda em um processo de backend para ler uma página necessária do armazenamento porque essa página não está disponível na memória compartilhada.

Versões compatíveis do mecanismo

Essas informações de eventos de espera têm suporte para todas as versões do Aurora PostgreSQL.

Contexto

Todas as consultas e operações de manipulação de dados (DML) acessam páginas no grupo de buffer. Instruções que podem induzir leituras incluem SELECT, UPDATE e DELETE. Por exemplo, um UPDATE pode ler páginas de tabelas ou índices. Se a página que está sendo solicitada ou atualizada não estiver no grupo de buffer compartilhado, essa leitura poderá gerar o evento IO:DataFileRead.

Como o grupo de buffer compartilhado é finito, ele pode ficar lotado. Nesse caso, solicitações de páginas que não estão na memória forçam o banco de dados a ler blocos do disco. Se o evento IO:DataFileRead ocorre com frequência, o grupo de buffer compartilhado pode ser pequeno demais para acomodar sua workload. Esse problema é grave para consultas SELECT que fazem a leitura de um grande número de linhas que não cabem no grupo de buffer. Para obter mais informações sobre o grupo de buffer, consulte Grupo de buffer.

Possíveis causas do maior número de esperas

As causas comuns do evento IO:DataFileRead incluem:

Picos de conexão

Você pode encontrar várias conexões gerando o mesmo número de eventos de espera IO:DatafileRead. Nesse caso, pode ocorrer um pico (aumento súbito e grande) em eventos IO:DataFileRead.

Instruções SELECT e DML realizando varreduras sequenciais

Sua aplicação pode estar executando uma nova operação. Ou uma operação existente pode mudar por conta de um novo plano de execução. Nesses casos, procure tabelas (particularmente grandes) que tenham um valor de seq_scan maior. Encontre-as consultando pg_stat_user_tables. Para rastrear consultas que estão gerando mais operações de leitura, utilize a extensão pg_stat_statements.

CTAS e CREATE INDEX para conjuntos de dados grandes

Um CTAS é uma instrução CREATE TABLE AS SELECT. Se você executar um CTAS utilizando um conjunto de dados grande como fonte ou criar um índice em uma tabela grande, o evento IO:DataFileRead poderá ocorrer. Quando você cria um índice, talvez o banco de dados precise ler o objeto inteiro utilizando uma varredura sequencial. Um CTAS gera leituras de IO:DataFile quando as páginas não estão na memória.

Vários operadores de vacuum em execução ao mesmo tempo

Operadores de vacuum podem ser acionados manual ou automaticamente. Convém adotar uma estratégia de vacuum agressiva. No entanto, quando uma tabela possui muitas linhas atualizadas ou excluídas, as esperas de IO:DataFileRead aumentam. Depois que o espaço é recuperado, o tempo de vacuum gasto em IO:DataFileRead diminui.

Ingestão de grandes quantidades de dados

Quando a aplicação ingere grandes quantidades de dados, operações ANALYZE podem ocorrer com mais frequência. O processo ANALYZE pode ser acionado por um launcher de autovacuum ou chamado manualmente.

A operação ANALYZE lê um subconjunto da tabela. O número de páginas que devem ser varridas é calculado multiplicando-se 30 pelo valor de default_statistics_target. Para obter mais informações, consulte a Documentação do PostgreSQL. O parâmetro default_statistics_target aceita valores entre 1 e 10.000, em que o padrão é 100.

Inanição de recursos

Se a largura de banda ou a CPU da rede da instância forem consumidas, o evento IO:DataFileRead poderá ocorrer com mais frequência.

Ações

Recomenda-se ações distintas, dependendo dos motivos do evento de espera.

Verificar filtros de predicados em busca de consultas que geram esperas

Suponha que você identifique consultas específicas que estão gerando eventos de espera IO:DataFileRead. É possível identificá-los utilizando as seguintes técnicas:

  • Insights de Performance

  • Visualizações de catálogo, como a fornecida pela extensão pg_stat_statements

  • A visualização de catálogo pg_stat_all_tables, se ela mostrar periodicamente um número mais alto de leituras físicas

  • A visualização pg_statio_all_tables, se ela mostrar que os contadores de _read estão aumentando

Convém determinar quais filtros são utilizados no predicado (cláusula WHERE) dessas consultas. Siga estas diretrizes:

  • Execute o comando EXPLAIN. Na saída, identifique quais tipos de varreduras são utilizados. Uma varredura sequencial não indica necessariamente um problema. Consultas que utilizam varreduras sequenciais naturalmente produzem mais eventos IO:DataFileRead quando comparados a consultas que utilizam filtros.

    Descubra se a coluna listada na cláusula WHERE é indexada. Caso contrário, considere criar um índice para essa coluna. Essa abordagem evita varreduras sequenciais e reduz eventos IO:DataFileRead. Se uma consulta tiver filtros restritivos e ainda produzir varreduras sequenciais, avalie se os índices adequados estão sendo utilizados.

  • Descubra se a consulta está acessando uma tabela muito grande. Em alguns casos, o particionamento de uma tabela pode melhorar a performance, permitindo que a consulta leia apenas as partições necessárias.

  • Observe a cardinalidade (número total de linhas) das suas operações de junção. Observe o quão restritivos são os valores que você está transmitindo nos filtros para a sua cláusula WHERE. Se possível, ajuste sua consulta para reduzir o número de linhas que são transmitidas em cada etapa do plano.

Minimizar o efeito de operações de manutenção

Operações de manutenção, como VACUUM e ANALYZE, são importantes. Recomendamos que você não as desative ao encontrar eventos de espera IO:DataFileRead relacionados a essas operações de manutenção. As abordagens a seguir podem minimizar o efeito dessas operações:

  • Execute operações de manutenção manualmente fora do horário de pico. Essa técnica impede que o banco de dados atinja o limite para operações automáticas.

  • Considere particionar tabelas muito grandes. Essa técnica reduz a sobrecarga das operações de manutenção. O banco de dados somente acessa as partições que exigem manutenção.

  • Ao ingerir grandes quantidades de dados, considere desativar o recurso de análise automática.

O recurso de autovacuum é acionado automaticamente para uma tabela quando a seguinte fórmula é verdadeira.

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

A visualização pg_stat_user_tables e o catálogo pg_class têm várias linhas. Uma linha pode corresponder a uma linha na sua tabela. Essa fórmula pressupõe que os reltuples sejam para uma tabela específica. Os parâmetros autovacuum_vacuum_scale_factor (0,20 por padrão) e autovacuum_vacuum_threshold (50 tuplas por padrão) geralmente são definidos globalmente para toda a instância. Porém, é possível definir valores diferentes para uma tabela específica.

Localizar tabelas que consomem espaço desnecessariamente

Para localizar tabelas que consomem mais espaço do que o necessário, execute a consulta a seguir. Quando essa consulta é executada por uma função de usuário do banco de dados que não tenha a função rds_superuser, ela retorna informações somente sobre as tabelas que a função de usuário tem permissão para ler. Essa consulta é compatível com o PostgreSQL versão 12 e versões posteriores. 

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

É possível verificar se há sobrecarga na tabela e no índice em sua aplicação. Para obter mais informações, consulte .

Localizar índices que consomem espaço desnecessário

Para localizar índices que consomem espaço desnecessário, execute a seguinte consulta.

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

Localizar tabelas elegíveis qualificadas para receber autovacuum

Para localizar tabelas qualificadas para receber autovacuum, execute a seguinte 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 um alto número de conexões

Ao monitorar o Amazon CloudWatch, você pode descobrir que a métrica DatabaseConnections atinge picos. Esse aumento indica um número maior de conexões com o seu banco de dados. Recomendamos a seguinte abordagem:

  • Limite o número de conexões que a aplicação pode abrir com cada instância. Se a aplicação tiver um recurso de grupo de conexões incorporado, defina um número razoável de conexões. Baseie o número no que as vCPUs na instância podem paralelizar de maneira eficiente.

    Se a aplicação não utilizar um recurso de grupo de conexões, considere utilizar o Amazon RDS Proxy ou uma alternativa. Essa abordagem permite que a aplicação abra várias conexões com o balanceador de carga. O balanceador pode então abrir um número restrito de conexões com o banco de dados. À medida que menos conexões são executadas em paralelo, sua instância de banco de dados realiza menos alternâncias de contexto no kernel. As consultas devem progredir com mais rapidez, resultando em menos eventos de espera. Para obter mais informações, consulte Usar o Amazon RDS Proxy para o Aurora.

  • Sempre que possível, aproveite nós de leitura para o Aurora PostgreSQL e réplicas de leitura do RDS for PostgreSQL. Quando a aplicação executar uma operação somente leitura, envie essas solicitações ao endpoint somente leitura. Essa técnica dispersa as solicitações de aplicações em todos os nós de leitor, reduzindo a pressão de E/S no nó de gravador.

  • Considere aumentar a escala vertical da sua instância de banco de dados. Uma classe de instância com maior capacidade fornece mais memória, o que dá ao Aurora PostgreSQL um grupo de buffer compartilhado maior para conter páginas. O tamanho maior também dá à instância de banco de dados mais vCPUs para lidar com conexões. Mais vCPUs são particularmente úteis quando as operações que estão gerando eventos de espera IO:DataFileRead são gravações.