チェックポイントの動作を確認(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;

概要

スケジュールされたチェックポイントとWAL量により強制されたチェックポイントの比率、チェックポイントあたりの平均書き込み/同期時間、書き込まれたバッファ量を表示する(PG16以前では、bgwriterとバックエンドの書き込みも併せて)。

使用する場面

書き込みの多い期間にI/Oスパイクが見られるとき、WAL生成量が多いとき、ログに'checkpoints are occurring too frequently'の警告が出るときに。

注意

req_pctが高い(一般に10-20%超)場合、max_wal_sizeが満杯になるためにチェックポイントが強制されていることを意味する。max_wal_sizeを引き上げ、log_checkpoints = onで'checkpoint starting: wal'の行を監視する。checkpoint_timeoutを長くするとI/Oは分散されるが、クラッシュリカバリが長くなる。PG16以前でwritten_by_backendsが高い場合、バックエンドがダーティページを自ら書き込む必要があることを意味する(shared_buffers/bgwriterが不足)。PG17ではこれはpg_stat_ioにある。平均値には作業がなくスキップされたスケジュール済みチェックポイントも含まれるため、おおよその数値として読む。カウンタはstats_reset以降の累積値。