PostgreSQL checkpoint špičky: prečo p99 exploduje každých N minút

Toto je klasický graf, ktorý sa tvári ako záhada:

• CPU stabilné
• throughput relatívne rovný
• ale latencia (hlavne p99) robí periodické špičky – napríklad každých 5 alebo 15 minút
• počas špičiek často rastie disk IO latency

Veľmi často je koreň: checkpoint.

Nie “checkpoint je zlý”, ale checkpoint je mechanizmus, ktorý pri zlej kombinácii konfigurácie a storage spraví z flushovania dirty stránok krátky IO burst. A ten vám zoberie tail latency budget.

Cieľ článku: metodika, ako to reprodukovať, zmerať a vyladiť tak, aby checkpointy prestali byť p99 killer.

Testované na: PostgreSQL 13–16, lokálne NVMe aj cloudové network-attached disky. Príklady používajú Linux nástroje.

Čo checkpoint robí (iba to, čo potrebujete vedieť pre výkon)

Z pohľadu operácií checkpoint znamená:

• Postgres musí garantovať, že určitý “bod v čase” je bezpečne na disku,
• čo v praxi znamená veľa zápisov dirty bufferov,
• a následné fsync/synchronizáciu.

Ak sa tieto zápisy “zhluknú”, uvidíte:

• zvýšenú disk IO latency,
• fronty v storage,
• a následne spomalenie query (aj read-only!) kvôli IO contention.

Metodika: najprv merajte, potom tuning

Minimum signálov, ktoré chcem vidieť

1. PostgreSQL štatistiky checkpointov/bgwriter
2. WAL tempo (koľko generujete)
3. OS disk latency a queue (napr. iostat)
4. Latenciu workloadu (pgbench alebo vaše SLI)

SQL: checkpointy a bgwriter

Začnite s pg_stat_bgwriter:

SELECT
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_checkpoint,
  buffers_clean,
  maxwritten_clean,
  buffers_backend,
  buffers_backend_fsync
FROM pg_stat_bgwriter;

Praktická interpretácia:

• checkpoints_timed vs checkpoints_req: ak checkpoints_req rastie rýchlo, často máte vynútené checkpointy (WAL sa naplní skôr než timeout).
• checkpoint_write_time a checkpoint_sync_time: špičky tu často sedia so špičkami p99.

Ak máte PostgreSQL 16+, pg_stat_io pridá viac detailu – ale dobrú diagnózu spravíte aj bez toho.

OS: IO fronty a latencia

Na databázovom node:

iostat -xz 1

Sledujte hlavne:

• %util (dlhodobo 100% je zlé znamenie)
• await / latenciu (pri špičke vyskočí)
• queue indikátory (záleží od platformy)

Reprodukčný lab: naschvál spravte checkpointy bolestivé

Toto robte na test DB, nie na produkcii.

1) Vygenerujte rovnomerný workload

pgbench -i -s 50 mydb
pgbench -c 32 -j 32 -T 300 -P 1 mydb

-P 1 vám dá priebežný output latencie/throughputu.

2) Vynúťte časté checkpointy (len v lab-e)

Pointa labu je:

• spraviť checkpointy častejšie (napr. malý max_wal_size alebo kratší checkpoint_timeout),
• a pozorovať, že p99 špičky sa zrovnajú s checkpoint signálmi.

Nekopírujte “magické hodnoty”. Cieľ je pochopiť tvar problému na vašom storage.

3) Korelácia: p99 vs checkpoint signály

Počas testu:

• logujte pgbench latenciu
• odčítajte pg_stat_bgwriter
• sledujte iostat

Ak špička sedí s checkpoint write/sync časom a IO latenciou na disku, máte vinníka.

“Checkpoint budget”: jednoduchá realita

Aby checkpoint nezabil latenciu, musíte zosúladiť:

• váš write/WAL rate
• s vašou IO kapacitou a latenciou

Ak generujete veľa WAL a máte malé max_wal_size, checkpointy budú časté a často vynútené.

Cieľ tuningovania nie je “najmenej checkpointov”, ale:

• checkpointy, ktoré sú predvídateľné
• a flushing je rozložený v čase

Tuning: čo skúšať (a ako overiť)

1) Znížiť vynútené checkpointy cez WAL sizing

Ak checkpoints_req dominuje, často narazíte na WAL size limit skôr než na timeout.

Smer:

• zvýšiť max_wal_size (v rámci disk budgetu)

Overenie:

• checkpoints_req spomalí voči checkpoints_timed
• menšie alebo zriedkavejšie p99 špičky

2) Rozložiť zápisy v čase

checkpoint_completion_target pomáha rozložiť checkpoint prácu na väčšiu časť intervalu.

Overenie:

• menej krátkych IO burstov
• hladší await v iostat
• menšie p99 špičky

3) Storage realita: niektoré disky majú burst limit

Na cloude je bežné:

• burst výkon, potom throttling.

Ak špičky sedia s throttlingom, DB tuning má limit. Potrebujete:

• lepší disk tier,
• iný disk layout,
• alebo zmenu architektúry (write shaping/batching).

Najčastejšie pasce

Pasca 1: “CPU je v pohode, tak to nie je DB”

Checkpoint špičky sú primárne IO problém. CPU môže byť stabilné.

Pasca 2: “Zvýšime checkpoint_timeout a hotovo”

Môže pomôcť, ale ak vás tlačí max_wal_size, checkpointy budú aj tak vynútené.

Pasca 3: “Tuningujeme query, ale ignorujeme disk queue”

Ak storage je saturovaný, query tuning často nič nevyrieši.

Čo by som spravil v produkcii

1. Spravím koreláciu: p99 špičky ↔ checkpoint signály ↔ disk latency
2. Skontrolujem checkpoints_timed vs checkpoints_req
3. Ak dominujú vynútené checkpointy, riešim WAL sizing a storage limity
4. Nastavím checkpoint budget (IO stabilita, predvídateľnosť, alerty)
5. Zmeny robím postupne a vždy ich overím meraním

FAQ

Ako zistím, či mám vynútené checkpointy?

Pozrite pg_stat_bgwriter a porovnajte checkpoints_req vs checkpoints_timed.

Prečo checkpoint spomalí aj read-only dotazy?

Lebo čítania čakajú na disk IO. Ak checkpoint spraví IO contention, čítania stoja v rade.

Stačí len zvýšiť max_wal_size?

Nie vždy. Zníži frekvenciu checkpointov, ale ak storage nestíha ani rozložené flushing, potrebujete lepšiu IO kapacitu.

Môže sa to biť s archivingom alebo replikáciou?

Áno. Zmerajte aj WAL tempo a prípadný lag (replication/archiving).

Súvisiace články

• /sk/blog/postgresql-wal-forensics/ (WAL a čo z neho viete vyčítať)
• /sk/blog/logical-replication-slot-wal-retention/ (WAL retention tlak)
• /sk/blog/postgresql-autovacuum-slo/ (ďalší periodický “p99 killer”)

Further reading

• https://www.postgresql.org/docs/current/runtime-config-wal.html
• https://www.postgresql.org/docs/current/monitoring-stats.html
• https://www.postgresql.org/docs/current/pgbench.html
• https://www.postgresql.org/docs/current/wal.html