Checkpoint в PostgreSQL: почему он бьёт по диску и как растянуть запись на 90 % интервала

Каждые checkpoint_timeout секунд график записи на диск у базы с нагрузкой на запись вздувается: в логе checkpoint complete: wrote 12021 buffers за 2,9 с, а между точками диск почти пуст. Checkpoint сбрасывает на диск грязные страницы из shared_buffers и сдвигает точку, с которой начнётся восстановление после сбоя, а WAL до этой точки можно удалять. Пик задаёт checkpoint_completion_target: на 0.1 точка записала 12 021 буфер за 2,9 с, на 0.9 — 4 913 буферов за 26,4 с, и пик упал с 3 911 до 224 буферов в секунду.

DBA / 01koddo

Один интервал в 30 с: короткий пик или ровная запись

Что пишет лог

log_checkpoints пишет в лог две строки на каждую точку. Контейнер с коротким интервалом, чтобы точки шли каждые 30 с (минимум для checkpoint_timeout), и данные pgbench на 157 МБ:

docker run -d --name dba-checkpoints-and-io-spikes -e POSTGRES_PASSWORD=pg postgres:18 \
  -c log_checkpoints=on -c checkpoint_timeout=30s -c max_wal_size=4GB -c shared_buffers=128MB
docker exec dba-checkpoints-and-io-spikes pgbench -U postgres -i -s 10 postgres
docker exec dba-checkpoints-and-io-spikes pgbench -U postgres -c 4 -T 40 postgres
docker exec dba-checkpoints-and-io-spikes psql -U postgres -c "CHECKPOINT"
docker logs dba-checkpoints-and-io-spikes 2>&1 | grep -E "checkpoint (starting|complete)"

LOG:  checkpoint starting: time
LOG:  checkpoint complete: wrote 2714 buffers (16.6%), wrote 2 SLRU buffers; 0 WAL file(s) added, 1 removed, 0 recycled; write=22.660 s, sync=0.071 s, total=22.789 s; sync files=11, longest=0.014 s, average=0.007 s; distance=23807 kB, estimate=79012 kB; lsn=0/537BC438, redo lsn=0/51D355B0
LOG:  checkpoint starting: immediate force wait
LOG:  checkpoint complete: wrote 2968 buffers (18.1%), wrote 3 SLRU buffers; 0 WAL file(s) added, 1 removed, 1 recycled; write=0.073 s, sync=0.049 s, total=0.182 s; sync files=4, longest=0.018 s, average=0.013 s; distance=27163 kB, estimate=73827 kB; lsn=0/537BC508, redo lsn=0/537BC4B0

Вывод: две точки одного размера идут с разной скоростью. Плановая (time) растянула запись 2 714 буферов на 22,7 с, а команда CHECKPOINT (immediate) записала 2 968 буферов за 0,073 с, то есть примерно в 300 раз короче по времени. Фаза sync (fsync файлов) короткая в обоих случаях, время уходит в write. Поле distance — сколько WAL прошло между точками (23 807 кБ), redo lsn — с какого LSN начнётся восстановление после сбоя.

Опыт: completion_target 0.1 и 0.9

Для сравнения нужна запись в разрезе секунд. pg_stat_checkpointer.buffers_written растёт по ходу точки (checkpointer отчитывается при каждом сне), поэтому колонку можно снимать раз в секунду. Команды ниже выполняются внутри контейнера (docker exec -it dba-checkpoints-and-io-spikes bash); параметр меняется без рестарта:

psql -U postgres -c "ALTER SYSTEM SET checkpoint_completion_target = 0.1" -c "select pg_reload_conf()"
psql -U postgres -c "CHECKPOINT" -c "select pg_stat_reset_shared('checkpointer')"
pgbench -U postgres -c 4 -T 62 -R 700 postgres &
for i in $(seq 1 60); do
  psql -U postgres -Atc "select buffers_written from pg_stat_checkpointer"; sleep 0.9
done

То же с 0.9. Из 60 снимков вычитаем соседние значения:

В среднем 12 021 / 2,894 с — около 4 154 буферов в секунду против 186 при 0.9. Буферов в точках разное число, потому что за интервал нагрузка успела испачкать разное количество страниц, поэтому сравнивать надо темп, а не итог. -R 700 в обоих прогонах накопил отставание от графика (rate limit schedule lag): диск стенда не вытянул поток, так что задержки транзакций здесь не сравниваются.

Запись при 0.9 заняла 26,4 с из 30, то есть 88 % интервала: сброс должен закончиться к 90 %, а остаток остаётся на fsync и запас. Как checkpointer это обеспечивает, видно в коде.

Как checkpointer держит график

В цикле записи BufferSync (bufmgr.c, строка 3609) после каждой страницы вызывается CheckpointWriteDelay(flags, num_processed / num_to_scan), а она решает, спать или писать дальше. Решает функция IsCheckpointOnSchedule. Строкой 852 прогресс умножается на target (progress *= CheckPointCompletionTarget;), строками 887–894 он сравнивается с долей WAL, ушедшего с начала точки (elapsed_xlogs), а затем с долей времени:

	gettimeofday(&now, NULL);
	elapsed_time = ((double) ((pg_time_t) now.tv_sec - ckpt_start_time) +
					now.tv_usec / 1000000.0) / CheckPointTimeout;

	if (progress < elapsed_time)
	{
		ckpt_cached_elapsed = elapsed_time;
		return false;
	}

	/* It looks like we're on schedule. */
	return true;

Источник: checkpointer.c, IsCheckpointOnSchedule, строки 842–911, REL_18_6, цитата — строки 899–910. Если выполненная работа не отстаёт от обеих линий, функция возвращает true, и CheckpointWriteDelay засыпает на 100 мс. Условие «не отстаём» — прогресс × target ≥ доля прошедшего времени, поэтому вся работа должна закончиться к доле target интервала: к 3 с при 0.1 (в опыте write=2.894 s) и к 27 с при 0.9 (write=26.421 s). Отстали — пишем подряд без сна, и так же при CHECKPOINT_IMMEDIATE.

Линия WAL — вторая ручка. Число сегментов, через которое точка считается просроченной, зависит от target:

target = (double) ConvertToXSegs(max_wal_size_mb, wal_segment_size) /
	(1.0 + CheckPointCompletionTarget);

CheckPointSegments = (int) target;

Источник: xlog.c, CalculateCheckpointSegments, строки 2189–2193, REL_18_6 (пустая строка и комментарий «round down» убраны). Для max_wal_size = 4GB и сегмента 16 МБ (wal_segment_size) это 256 / 1,9, то есть 134 сегмента. max_wal_size делят на 1 + target: по комментарию в коде, пока точка растянута, WAL пишется ещё target долей интервала.

Счётчики в pg_stat_checkpointer

В PostgreSQL 18 статистика контрольных точек лежит в pg_stat_checkpointer, а не в pg_stat_bgwriter. Колонка num_done появилась в 18, и по заметкам о выпуске num_timed и num_requested теперь считают и выполненные, и пропущенные точки. Сбросили счётчики, прогнали 40 с нагрузки и руками вызвали CHECKPOINT:

SELECT num_timed, num_requested, num_done,
       round(write_time) AS write_ms, round(sync_time) AS sync_ms, buffers_written
FROM pg_stat_checkpointer;

 num_timed | num_requested | num_done | write_ms | sync_ms | buffers_written
-----------+---------------+----------+----------+---------+-----------------
         1 |             1 |        3 |    48700 |     238 |            8214

num_requested = 1 — это наша команда CHECKPOINT, num_timed = 1 — плановая точка, начавшаяся после сброса. num_done = 3, потому что в счёт попала ещё одна плановая точка: она началась до сброса и закончилась после (в логе она завершилась с write=25.967 s). Если num_requested растёт быстрее num_timed, точки запускает не таймер; причина каждой точки стоит в строке checkpoint starting (в нашем опыте time и immediate force wait).

Что придётся проиграть после сбоя

Редкая точка дешевле для диска, но дороже при сбое. Восстановление начинается с redo lsn последней завершённой точки, поэтому сразу после 12 секунд нагрузки:

SELECT redo_lsn, checkpoint_time::time(0) AS ckpt_at, pg_current_wal_lsn() AS cur,
       pg_size_pretty(pg_current_wal_lsn() - redo_lsn) AS to_replay
FROM pg_control_checkpoint();

 redo_lsn  | ckpt_at  |    cur    | to_replay
-----------+----------+-----------+-----------
 0/5383B8B0 | 20:45:01 | 0/54B41EE8 | 19 MB

При сбое сервер проиграет 19 МБ WAL. Чем больше checkpoint_timeout и max_wal_size, тем больше число, и тем дольше стартует база после аварии. Вторая плата — полные образы страниц. Одна и та же строка обновляется 300 раз подряд (pgbench -n -f one.sql -c 1 -t 300), счётчики pg_stat_wal сбрасываются перед каждым прогоном:

после CHECKPOINT:        wal_fpi = 1, wal_records = 901, 57 kB
без новой точки:         wal_fpi = 0, wal_records = 900, 49 kB
после CHECKPOINT снова:  wal_fpi = 1, wal_records = 901, 57 kB

Первое изменение страницы после точки пишет в WAL её образ целиком (8 КБ разницы в объёме), остальные 299 обновлений — нет. Чем чаще точки, тем больше страниц платят эту цену заново.

InnoDB: нет интервала, есть скорость

В InnoDB нет checkpoint_timeout. Страницы сбрасывает фоновый page_cleaner, а скорость сброса растёт вместе с «возрастом» redo-журнала: чем больше накоплено redo, не покрытого сброшенными страницами, тем больший процент innodb_io_capacity берётся под сброс:

lsn_age_factor = (age * 100.0) / limit_for_dirty_page_age;

ut_ad(srv_max_io_capacity >= srv_io_capacity);

return (static_cast<ulint>(((srv_max_io_capacity / srv_io_capacity) *
                            (lsn_age_factor * sqrt(lsn_age_factor))) /
                           7.5));

Источник: buf0flu.cc, get_pct_for_lsn, строки 2483–2489, mysql-8.4.13. Темп растёт как степень 1,5 от возраста, то есть гладко, а не скачком. Поэтому в PostgreSQL пиками записи управляют через интервал и completion_target, а в InnoDB через innodb_io_capacity и размер redo. Код InnoDB здесь только прочитан, в контейнере он не запускался.

Что из этого следует

• Пик записи — это поведение настройки, а не болезнь диска. При target 0.1 точка записала 12 021 буфер за 2,9 с, при 0.9 — 4 913 буферов за 26 с, и пик за секунду упал в 17 раз; CHECKPOINT вручную сбросил 2 968 буферов за 0,073 с.
• Ручной CHECKPOINT на проде бьёт по диску как target 0. Он идёт с флагом immediate и без сна, в логе checkpoint starting: immediate force wait.
• Частота точек выбирается между диском и временем старта. Редкие точки сглаживают запись и экономят полные образы страниц, но увеличивают объём WAL для проигрывания после сбоя (19 МБ в нашем опыте при интервале 30 с).
• Растянутой точке нужен запас по WAL. Если max_wal_size мал, линия elapsed_xlogs обгоняет линию времени, и точка идёт быстрее, чем задано completion_target.

Чек-лист

1. Включи log_checkpoints и найди строки checkpoint complete: сравни write=, wrote N buffers и distance=.
2. SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer: если num_requested обгоняет num_timed, точки идут не по таймеру.
3. Оставь checkpoint_completion_target = 0.9, это значение по умолчанию; меняй checkpoint_timeout и max_wal_size вместе и проверяй write= в логе после каждого изменения.
4. Считай цену сбоя: pg_current_wal_lsn() - redo_lsn из pg_control_checkpoint().
5. Убери контейнер опыта: docker rm -f dba-checkpoints-and-io-spikes.