PostgreSQL용 건강 점수: 건강 모니터링

포스트그레SQL의 헬스 스코어: 데이터베이스 건강을 한눈에 파악하는 지표

수십 개의 그라파나 대시보드로만 모니터링하면, 운영자는 차트를 무시하거나 경고가 과도하게 발생하거나 너무 늦게 발동되는 문제가 생깁니다. 헬스 스코어는 pg_stat_*에서 추출한 150개 이상의 메트릭을 하나의 점수(0~100)로 통합하여 문제를 빠르게 진단할 수 있게 해줍니다. 카테고리 가중치와 위험한 조합을 고려한 공식을 통해, 95점 이상은 정상, 70점 미만은 즉각 조치가 필요합니다.

기본 공식: 헬스 스코어 = 100 - Σ(패널티_i × 가중치_i)

패널티는 비선형적으로 계산되며, 트렌드와 이상 징후에 민감하게 반응합니다. 이는 단순 임계값 경고가 아니라, APM에서의 Apdex나 의학에서의 EWS처럼 복합 지표입니다.

카테고리 구조 및 계산 방법

헬스 스코어는 고정된 가중치를 가진 다섯 가지 카테고리로 구성됩니다. 각 카테고리는 PostgreSQL 시스템 뷰를 기반으로 SQL 쿼리를 통해 핵심 메트릭을 평가합니다.

연결 (가중치 0.20)

연결 풀의 과부하와 대기 상태를 측정합니다:

SELECT 
    count(*) FILTER (WHERE state = 'active') AS active_connections,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    now() - min(xact_start) AS longest_transaction_age
FROM pg_stat_activity
WHERE pid != pg_backend_pid();

사용률이 80%를 넘으면 패널티가 지수적으로 증가합니다. 30초 이상 된 "대기 중 트랜잭션" 세션은 자동 정리 작업을 방해하고 스토리지 부풀음을 유발하므로 추가 패널티가 적용됩니다.

성능 (가중치 0.25)

사용자 경험 중심: 캐시 히트율과 느린 쿼리 처리량을 집중 관리합니다.

-- 캐시 히트율
SELECT 
    round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- 느린 쿼리
SELECT count(*), round(avg(mean_exec_time)::numeric, 2)
FROM pg_stat_statements
WHERE mean_exec_time > 1000 AND calls > 10;

히트율이 95% 미만이면 패널티 발생, 90% 미만은 심각한 상황입니다. 실행 시간은 과거 기준선과 비교해 이상 징후를 탐지합니다.

스토리지 (가중치 0.20)

무효 튜플과 스토리지 부풀림을 모니터링합니다:

SELECT 
    schemaname, relname, n_dead_tup, n_live_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
    pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 10000
ORDER BY dead_ratio DESC LIMIT 20;

pgstattuple 또는 relpages 기반 히ュ리스틱을 활용해 부풀림을 평가하며, 디스크 사용량은 파일시스템 제한과 비교합니다.

복제 (가중치 0.15)

레플리카 지연 여부를 확인합니다:

SELECT application_name, state, (sent_lsn - replay_lsn) AS lag_bytes,
    write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

지연이 1MB 이상이거나 5초를 초과하면 패널티가 발생합니다. 레플리카가 없더라도 해당 카테고리는 패널티 대상이 아닙니다.

유지보수 (가중치 0.20)

자동 정리 진행 상황과 XID 나이를 추적합니다:

-- XID 나이
SELECT datname, age(datfrozenxid), 2147483648 - age(datfrozenxid)
FROM pg_database ORDER BY age(datfrozenxid) DESC;

-- 정리 나이
SELECT schemaname, relname, now() - greatest(last_vacuum, last_autovacuum) AS vacuum_age
FROM pg_stat_user_tables WHERE n_live_tup > 10000
ORDER BY vacuum_age DESC NULLS FIRST LIMIT 20;

XID 나이가 15억을 넘으면 회전 위험이 커져 고도의 패널티가 적용됩니다.

가중치 논리와 해석

가중치는 실측 데이터 기반으로 결정되었습니다. 성능(0.25)이 가장 높은 이유는 사용자 경험에 직접적인 영향을 주기 때문입니다. 연결과 유지보수(0.20)는 빠르게 실패를 나타내는 지표이며, 복제(0.15)는 선택 사항입니다.

• 95+: 안정 상태
• 70–94: 모니터링 및 조치 계획 수립
• 40–69: 즉각적인 조치 필요
• <40: 사고 상태

자동 진단 및 권장 사항

헬스 스코어는 우선순위별 문제 목록을 제공합니다:

• 높음: 무효 튜플 비율이 40% 이상이고, 정리가 4일 이상 지난 테이블 — VACUUM ANALYZE 실행 후 autovacuum_vacuum_scale_factor 조정
• 중간: 복제 지연이 500ms 이상 — 레플리카 부하 및 wal_receiver_timeout 확인
• 낮음: 캐시 히트율이 99% 미만 — shared_buffers를 RAM의 25%로 증가

권장 사항은 규칙 기반으로 생성되며, 자동 정리 임계값 검사 포함됩니다.

SELECT relname, n_dead_tup > (n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float + current_setting('autovacuum_vacuum_threshold')::float) AS vacuum_due
FROM pg_stat_user_tables;

핵심 요약

• 헬스 스코어는 가중치 기반 메트릭을 통합해, "대기 중 트랜잭션 + 부풀림" 같은 임계값 경고가 놓칠 수 있는 숨겨진 조합을 드러냅니다.
• 비선형 공식은 중요한 임계값에 도달하기 전에 조기 경고를 제공합니다.
• Prometheus/Grafana와 함께 postgres_exporter를 통해 통합 가능합니다.
• 자동 진단은 실제 SQL 예시와 함께 실행 가능한 권장 사항을 제공합니다.
• 확장성 있음: 과거 트렌드와 비교해 이상 징후를 탐지합니다.

— Editorial Team