IO:DataFileRead
IO:DataFileRead 이벤트는 공유 메모리에서 페이지를 사용할 수 없기 때문에 저장소에서 필요한 페이지를 읽기 위해 백엔드 프로세스에서 연결이 대기할 때 발생합니다.
지원되는 엔진 버전
이 대기 이벤트 정보는 모든 Aurora PostgreSQL 버전에서 지원됩니다.
컨텍스트
모든 쿼리 및 데이터 조작(DML) 작업은 버퍼 풀의 페이지에 액세스합니다. 읽기를 유도할 수 있는 명령문은 SELECT,UPDATE 및 DELETE가 있습니다. 예를 들어, 하나의 UPDATE는 테이블 또는 인덱스에서 페이지를 읽을 수 있습니다. 요청하거나 업데이트되는 페이지가 공유 버퍼 풀에 없는 경우 이 읽기는 IO:DataFileRead 이벤트를 생성합니다.
공유 버퍼 풀은 유한하기 때문에 채워질 수 있습니다. 이 경우 메모리에 없는 페이지에 대한 요청은 데이터베이스가 디스크에서 블록을 읽도록 강제로 합니다. 만약 IO:DataFileRead 이벤트가 자주 발생한다면 공유 버퍼 풀이 너무 작아서 워크로드를 수용할 수 없는 것일 수도 있습니다. 이 문제는 버퍼 풀에 맞지 않는 많은 수의 행을 읽는 SELECT
대기 증가의 가능한 원인
IO:DataFileRead 이벤트의 일반적인 원인에는 다음이 포함됩니다.
- 연결 스파이크
-
동일한 수의 Io:DataFileRead 대기 이벤트를 생성하는 여러 연결을 찾을 수 있습니다. 이 경우
IO:DataFileRead이벤트의 스파이크 갑작스럽고 큰 증가)가 발생할 수 있습니다. - 순차 검사를 수행하는 SELECT 및 DML 문
-
애플리케이션에서 새 작업을 수행하고 있을 수 있습니다. 또는 새 실행 계획으로 인해 기존 작업이 변경될 수 있습니다. 이러한 경우 테이블(특히 큰 테이블)이 더 큰
seq_scan값을 가진 테이블을 찾으세요.pg_stat_user_tables를 쿼리하여 탐색 더 많은 읽기 작업을 생성하는 쿼리를 추적하려면pg_stat_statements확장 프로그램을 사용하세요. - 대용량 데이터 세트를 위한 CTAS 및 CREATE 인덱스
-
CTAS는
CREATE TABLE AS SELECT문입니다. 대용량 데이터 세트를 소스로 사용하여 CTAS를 실행하거나 큰 테이블에 인덱스를 만드는 경우IO:DataFileRead이벤트가 발생할 수 있습니다. 인덱스를 만들 때 데이터베이스는 순차 스캔을 사용하여 전체 객체를 읽어야 할 수 있습니다. CTAS는 페이지가 메모리에 없을 때IO:DataFile리드를 생성합니다. - 여러 베큠 작업자가 동시에 실행
-
벸ㅍㅁ 작업자는 수동 또는 자동으로 트리거될 수 있습니다. 공격적인 베큠 전략을 채택하는 것이 좋습니다. 그러나 테이블에 업데이트되거나 삭제된 행이 많으면
IO:DataFileRead대기가 늘어납니다. 공간을 회수한 후IO:DataFileRead에 소비되는 베큠 시간이 감소합니다. - 대용량 데이터 수집
-
애플리케이션이 대용량 데이터를 수집할 때
ANALYZE연산이 더 자주 발생할 수 있습니다.ANALYZE프로세스는 autovacuum 시작 관리자에 의해 트리거되거나 수동으로 호출될 수 있습니다.ANALYZE연산은 테이블의 하위 집합을 읽습니다. 스캔해야 하는 페이지 수는default_statistics_target값에 30을 곱하여 계산합니다. 자세한 내용은 PostgreSQL 설명서를 참조하세요. default_statistics_target파라미터 1에서 10,000 사이의 값을 허용합니다. 여기서 기본값은 100입니다. - 리소스 부족
-
인스턴스 네트워크 대역폭 또는 CPU가 사용되는 경우
IO:DataFileRead이벤트가 더 자주 발생할 수 있습니다.
작업
대기 이벤트의 원인에 따라 다른 작업을 권장합니다.
대기를 생성하는 쿼리에 대한 술어 필터 확인
IO:DataFileRead 대기 이벤트를 생성 중인 특정 쿼리를 식별한다고 가정합니다. 다음 기법을 사용하여 식별할 수 있습니다.
-
성능 개선 도우미
-
pg_stat_statements확장에서 제공하는 것과 같은 카탈로그 뷰 -
카탈로그 뷰
pg_stat_all_tables, 주기적으로 증가하는 물리적 읽기 횟수가 표시되는 경우 -
pg_statio_all_tables뷰,_read카운터의 증가를 보여주는 경우
이 쿼리의 술어(WHERE 절)에 사용되는 필터를 결정하는 것이 좋습니다. 아래 지침을 따르세요.
-
EXPLAIN명령을 실행합니다. 출력에서 어떤 유형의 스캔이 사용되는지 식별합니다. 순차 스캔이 반드시 문제를 의미하지는 않습니다. 순차 스캔을 사용하는 쿼리는 필터를 사용하는 쿼리와 비교할 때 자연스럽게 더 많은IO:DataFileRead이벤트를 생성합니다.WHERE절에 열거된 열이 인덱싱되었는지 확인합니다. 그렇지 않은 경우 이 열에 대한 인덱스를 만드는 것이 좋습니다. 이 접근법은 순차적 스캔을 피하고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개의 튜플)는 일반적으로 전체 인스턴스에 대해 전역으로 설정됩니다. 그러나 특정 테이블에 대해 다른 값을 설정할 수 있습니다.
불필요한 공간을 소비하는 테이블 찾기
필요 이상의 공간을 소비하는 테이블을 찾으려면 다음 쿼리를 실행합니다. rds_superuser 역할이 없는 데이터베이스 사용자 역할이 이 쿼리를 실행하면, 해당 사용자 역할이 읽기 권한이 있는 테이블에 관한 정보만 반환됩니다. 이 쿼리는 PostgreSQL 버전 12 이상에서 지원됩니다.
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
애플리케이션에서 테이블 및 인덱스 팽창을 검사할 수 있습니다. 자세한 내용은 테이블 및 인덱스 팽창 진단 단원을 참조하십시오.
불필요한 공간을 차지하는 테이블 찾기
불필요한 공간을 차지하는 테이블을 찾으려면 다음 쿼리를 실행합니다.
-- 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;
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 지표 스파이크를 확인할 수도 있습니다. 이 증가는 데이터베이스에 대한 연결 수가 증가했음을 나타냅니다. 다음과 같이 하는 것이 좋습니다.
-
애플리케이션이 각 인스턴스에서 열 수 있는 연결 수를 제한합니다. 애플리케이션에 내장된 연결 풀 기능이 있는 경우 적절한 연결 수를 설정합니다. 인스턴스의 vCPU가 효과적으로 병렬화할 수 있는 항목에 따라 숫자를 기준으로 합니다.
애플리케이션에서 연결 풀 기능을 사용하지 않는 경우 Amazon RDS 프록시 또는 대안을 사용하는 것이 좋습니다. 이 접근 방식을 사용하면 애플리케이션이 로드 밸런서와 여러 연결을 열 수 있습니다. 그런 다음 밸런서는 데이터베이스와의 제한된 수의 연결을 열 수 있습니다. 병렬로 실행되는 연결 수가 적을수록 DB 인스턴스는 커널에서 컨텍스트 전환을 덜 수행합니다. 쿼리는 더 빠르게 진행되므로 대기 이벤트 수가 줄어듭니다. 자세한 내용은 Amazon RDS Proxy for Aurora 단원을 참조하십시오.
-
가능하면 Aurora PostgreSQL용 리더 노드와 RDS for PostgreSQL용 읽기 전용 복제본을 활용할 수 있습니다. 애플리케이션이 읽기 전용 작업을 실행하면 이러한 요청을 읽기 전용 엔드포인트로 보냅니다. 이 기술은 애플리케이션 요청을 모든 리더 노드에 분산시켜 작성기 노드의 I/O 부담을 줄입니다.
-
DB 인스턴스를 확장하는 것이 좋습니다. 대용량 인스턴스 클래스는 더 많은 메모리를 제공하므로 Aurora PostgreSQL에 페이지를 저장할 수 있는 더 큰 공유 버퍼 풀이 제공됩니다. 크기가 클수록 DB 인스턴스에 연결을 처리할 수 있는 vCPU가 늘어납니다. 더 많은 vCPU가
IO:DataFileRead대기 이벤트를 생성 중인 연산이 쓰기를 하고 있을 때 특히 유용합니다.
