IO:DataFileRead

IO:DataFileRead 이벤트는 공유 메모리에서 페이지를 사용할 수 없기 때문에 저장소에서 필요한 페이지를 읽기 위해 백엔드 프로세스에서 연결이 대기할 때 발생합니다.

지원되는 엔진 버전

이 대기 이벤트 정보는 모든 RDS for PostgreSQL 버전에서 지원됩니다.

컨텍스트

모든 쿼리 및 데이터 조작(DML) 작업은 버퍼 풀의 페이지에 액세스합니다. 읽기를 유도할 수 있는 명령문은 SELECT,UPDATE 및 DELETE가 있습니다. 예를 들어, 하나의 UPDATE는 테이블 또는 인덱스에서 페이지를 읽을 수 있습니다. 요청하거나 업데이트되는 페이지가 공유 버퍼 풀에 없는 경우 이 읽기는 IO:DataFileRead 이벤트를 생성합니다.

공유 버퍼 풀은 유한하기 때문에 채워질 수 있습니다. 이 경우 메모리에 없는 페이지에 대한 요청은 데이터베이스가 디스크에서 블록을 읽도록 강제로 합니다. 만약 IO:DataFileRead 이벤트가 자주 발생한다면 공유 버퍼 풀이 너무 작아서 워크로드를 수용할 수 없는 것일 수도 있습니다. 이 문제는 버퍼 풀에 맞지 않는 많은 수의 행을 읽는 SELECT 쿼리에서 극심하게 발생합니다. 버퍼 풀에 대한 자세한 내용은 PostgreSQL 설명서의 리소스 소비를 참조하세요.

대기 증가의 가능한 원인

IO:DataFileRead 이벤트의 일반적인 원인에는 다음이 포함됩니다.

작업

대기 이벤트의 원인에 따라 다른 작업을 권장합니다.

대기를 생성하는 쿼리에 대한 술어 필터 확인

IO:DataFileRead 대기 이벤트를 생성 중인 특정 쿼리를 식별한다고 가정합니다. 다음 기법을 사용하여 식별할 수 있습니다.

이 쿼리의 술어(WHERE 절)에 사용되는 필터를 결정하는 것이 좋습니다. 아래 지침을 따르세요.

유지 보수 작업의 영향 최소화

VACUUM과 ANALYZE 같은 유지 관리 작업이 중요합니다. 이러한 유지 관리 작업과 관련된 IO:DataFileRead 대기 이벤트를 찾을 수 있으므로 끄지 않는 것이 좋습니다. 다음 방법을 사용하면 이러한 작업의 효과를 최소화할 수 있습니다.

다음 공식이 true이면 테이블에 대해 autovacuum 기능이 자동으로 트리거됩니다.

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

뷰 pg_stat_user_tables와 카탈로그 pg_class에는 여러 개의 행이 있습니다. 한 행은 테이블의 한 행에 대응할 수 있습니다. 이 공식은 reltuples가 특정 테이블을 위한 것이라고 가정합니다. 파라미터 autovacuum_vacuum_scale_factor(기본적으로 0.20)와 autovacuum_vacuum_threshold(기본적으로 50개의 튜플)는 일반적으로 전체 인스턴스에 대해 전역으로 설정됩니다. 그러나 특정 테이블에 대해 다른 값을 설정할 수 있습니다.

불필요한 공간을 소비하는 테이블 찾기

불필요하게 공간을 소비하는 테이블을 찾으려면 PostgreSQLpgstattuple 확장의 함수를 사용할 수 있습니다. 이 확장(모듈)은 모든 RDS for PostgreSQL DB 인스턴스에서 기본적으로 사용할 수 있으며, 다음 명령을 사용하여 인스턴스에서 인스턴스화할 수 있습니다.

CREATE EXTENSION pgstattuple;

이 확장에 대한 자세한 내용은 PostgreSQL 설명서의 pgstattuple를 참조하세요.

애플리케이션에서 테이블 및 인덱스 팽창을 검사할 수 있습니다. 자세한 내용은 테이블 및 인덱스 팽창 진단을 참조하세요.

불필요하게 공간을 소비하는 인덱스 찾기

다음 쿼리를 실행하여 부풀려진 인덱스를 찾고 읽기 권한이 있는 테이블에서 불필요하게 소비되는 공간을 추정할 수 있습니다.

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

Autovacuum이 가능한 테이블 찾기

Autovacuum이 가능한 테이블을 찾으려면 다음 쿼리를 실행합니다.

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

많은 연결 수에 대응

Amazon CloudWatch를 모니터링할 때 DatabaseConnections 지표 스파이크를 확인할 수도 있습니다. 이 증가는 데이터베이스에 대한 연결 수가 증가했음을 나타냅니다. 다음과 같이 하는 것이 좋습니다.