checkpoint 동작 검토(PG17+ pg_stat_checkpointer, PG16 이하 pg_stat_bgwriter)

명령어

-- PG17+
SELECT num_timed,
       num_requested,
       round(100.0 * num_requested / NULLIF(num_timed + num_requested, 0), 1) AS req_pct,
       restartpoints_timed,
       restartpoints_req,
       round((write_time / 1000)::numeric / NULLIF(num_timed + num_requested, 0), 1) AS avg_write_s,
       round((sync_time / 1000)::numeric / NULLIF(num_timed + num_requested, 0), 1) AS avg_sync_s,
       pg_size_pretty(buffers_written * current_setting('block_size')::bigint) AS written_by_checkpoint,
       stats_reset
FROM pg_stat_checkpointer;

-- PG16 and earlier
SELECT checkpoints_timed,
       checkpoints_req,
       round(100.0 * checkpoints_req / NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS req_pct,
       round((checkpoint_write_time / 1000)::numeric / NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS avg_write_s,
       round((checkpoint_sync_time / 1000)::numeric / NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS avg_sync_s,
       pg_size_pretty(buffers_checkpoint * current_setting('block_size')::bigint) AS written_by_checkpoint,
       pg_size_pretty(buffers_clean * current_setting('block_size')::bigint) AS written_by_bgwriter,
       pg_size_pretty(buffers_backend * current_setting('block_size')::bigint) AS written_by_backends,
       maxwritten_clean,
       stats_reset
FROM pg_stat_bgwriter;

-- Related settings
SELECT name, current_setting(name) AS value
FROM pg_settings
WHERE name IN ('checkpoint_timeout', 'max_wal_size', 'min_wal_size',
               'checkpoint_completion_target', 'log_checkpoints')
ORDER BY name;

하는 일

예약된 checkpoint와 WAL 양 때문에 강제된 checkpoint의 비율, checkpoint당 평균 write/sync 시간, 기록된 buffer 양(PG16 이하에서는 bgwriter 및 backend 쓰기 포함)을 보여 준다.

사용 시점

쓰기가 많은 기간에 I/O 스파이크가 보이거나, WAL 생성량이 많거나, 로그에 'checkpoints are occurring too frequently' 경고가 나타날 때.

주의

req_pct가 높으면(일반적으로 10-20% 초과) max_wal_size가 가득 차서 checkpoint가 강제되는 것이다. max_wal_size를 늘리고 log_checkpoints = on으로 'checkpoint starting: wal' 줄을 확인한다. checkpoint_timeout을 늘리면 I/O가 분산되지만 crash recovery가 길어진다. PG16 이하에서 written_by_backends가 높으면 backend가 dirty page를 직접 써야 한다는 뜻이다(shared_buffers/bgwriter 부족). PG17에서는 이 정보가 pg_stat_io에 있다. 평균에는 작업이 없어 건너뛴 예약 checkpoint도 포함되므로 대략적인 수치로 읽는다. 카운터는 stats_reset 이후 누적이다.