이 페이지에서는 PostgreSQL 관측 가능성 스크립트를 사용하여 AlloyDB Omni 데이터베이스 성능을 모니터링하는 방법을 설명합니다.
연결된 프로세스 및 대기 이벤트의 상태 보기
pg_stat_activity 뷰를 쿼리하여 AlloyDB Omni 인스턴스에 연결된 프로세스의 상태와 활동을 기다리는 모든 백엔드를 확인할 수 있습니다.
SELECT
pid,
datname,
age(backend_xid) AS age_in_xids,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
state,
wait_event_type,
wait_event,
query_id,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
AND pid <> pg_backend_pid()
ORDER BY
4 DESC
LIMIT 10;
가장 큰 테이블 보기
pg_stat_user_tables 뷰를 쿼리하여 가장 큰 테이블의 크기를 확인할 수 있습니다.
SELECT
oid,
oid::regclass table_name,
pg_size_pretty(pg_relation_size(oid)),
relpages,
s.seq_scan,
s.idx_scan
FROM
pg_class,
pg_stat_user_tables s
WHERE
s.relid = oid
AND oid > 16383
AND relpages > 100
AND relkind = 'r'
ORDER BY
relpages DESC
LIMIT 20;
상위 순차 스캔 보기
pg_stat_user_tables 뷰를 쿼리하여 상위 순차 스캔을 확인할 수 있습니다.
SELECT
relid,
relname,
seq_scan,
pg_size_pretty(pg_relation_size(relid))
FROM
pg_stat_user_tables
ORDER BY
seq_scan DESC
LIMIT 15;
상위 색인 스캔 보기
pg_stat_user_tables 뷰를 쿼리하여 상위 색인 스캔을 확인할 수 있습니다.
SELECT
relid,
relid::regclass table_name,
idx_scan,
pg_size_pretty(pg_relation_size(relid))
FROM
pg_stat_user_tables
WHERE
idx_scan > 10
ORDER BY
idx_scan DESC
LIMIT 15;
가장 오래 실행 중인 트랜잭션 보기
pg_stat_activity 뷰를 쿼리하고 각 트랜잭션의 경과 시간을 확인하여 가장 오래 실행 중인 트랜잭션을 볼 수 있습니다.
SELECT
pid,
age(backend_xid) AS age_in_xids,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
state,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
ORDER BY
2 DESC
LIMIT 10;
진공 청소 진행 상황 확인
pg_stat_progress_vacuum 뷰를 쿼리하고 프로세스 ID를 사용하여 pg_stat_activity 뷰와 조인하여 정리 작업의 진행 상황을 확인할 수 있습니다.
SELECT
p.pid,
now() - a.xact_start AS duration,
coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
CASE
WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
WHEN a.query ~*'^vacuum' THEN 'user'
ELSE
'regular'
END AS mode,
p.datname AS database,
p.relid::regclass AS table,
p.phase,
pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
p.index_vacuum_count,
round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;
비동기 쿼리 보기
비동기식으로 실행 중인 쿼리를 보려면 pg_stat_activity 뷰를 쿼리하고 리더 프로세스가 아닌 쿼리를 필터링하면 됩니다.
SELECT
query,
leader_pid,
array_agg(pid) FILTER (WHERE leader_pid != pid) AS members
FROM
pg_stat_activity
WHERE
leader_pid IS NOT NULL
GROUP BY
query,
leader_pid;
차단 잠금 SQL 보기
pg_locks 뷰를 쿼리하고 pg_stat_activity 뷰와 조인하여 차단된 활동을 볼 수 있습니다.
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocked_activity.wait_event AS blocked_wait_event,
blocking_activity.wait_event AS blocking_wait_event,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
work_mem 및 temp_buffers 크기 효과 확인
work_mem 및 temp_buffers의 크기가 필요에 맞게 올바른지 확인하려면 pg_stat_database 뷰를 쿼리하고 postgres.log 파일을 확인하면 됩니다.
pg_stat_database를 사용하여 다음 쿼리를 실행하고 실행 간에 temp_files 또는 temp_bytes가 증가하면 work_mem 또는 temp_buffers를 조정해야 할 수 있습니다.
SELECT
datname,
temp_files,
temp_bytes
FROM
pg_stat_database;
이 작업을 실행한 후 postgres.log 파일을 확인하여 임시 파일이 사용되었는지 확인합니다.
LOG: [fd.c:1772] temporary file: path "base/pgsql_tmp/pgsql_tmp4640.1", size 139264
목표는 임시 파일 생성을 완전히 방지하는 것이 아니라 최소화하는 것입니다. 이는 work_mem와 temp_buffers를 모두 설정하는 것이 호스트의 사용 가능한 메모리와 메모리가 필요한 연결 수를 균형 있게 유지하기 위한 것이기 때문입니다. 이러한 매개변수를 올바르게 설정하려면 각 개별 워크로드를 이해해야 합니다.