Revoir le comportement des checkpoints (PG17+ pg_stat_checkpointer, PG16 et antérieur pg_stat_bgwriter)

Commande

-- 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;

Ce que fait la commande

Affiche le rapport entre checkpoints planifiés et checkpoints forcés par le volume de WAL, le temps moyen d'écriture/synchronisation par checkpoint et le volume de buffers écrits (en PG16 et antérieur, avec les écritures du bgwriter et des backends).

Quand l'utiliser

Lorsque les périodes à forte écriture montrent des pics d'E/S, que la génération de WAL est élevée, ou que le journal avertit 'checkpoints are occurring too frequently'.

Attention

Un req_pct élevé (typiquement au-delà de 10-20 %) signifie que les checkpoints sont forcés parce que max_wal_size se remplit ; augmentez max_wal_size et surveillez les lignes 'checkpoint starting: wal' avec log_checkpoints = on. Un checkpoint_timeout plus long étale les E/S mais allonge la crash recovery. En PG16 et antérieur, un written_by_backends élevé signifie que les backends doivent écrire eux-mêmes les pages modifiées (shared_buffers/bgwriter insuffisants) ; en PG17, cela figure dans pg_stat_io. Les moyennes comptent aussi les checkpoints planifiés sautés faute de travail, lisez-les donc comme des ordres de grandeur. Les compteurs sont cumulés depuis stats_reset.