Двадцать параметров PostgreSQL: карта по подсистемам и pgbench до и после — 432 против 14 204 TPS

Калькулятор выдаёт двадцать значений по RAM и ядрам, но каждый параметр действует через свою подсистему и проверяется её счётчиком. На pgbench в контейнере с 2 ГБ при обычном COMMIT, который ждёт сброса WAL, пять вариантов настройки дали 411–433 TPS, а synchronous_commit = off — 14 204. Checkpoint и буферный кэш сдвинули счётчики, не TPS.

Откуда значения по умолчанию

Умолчания заданы в guc_tables.c: имя, контекст, указатель на переменную и тройка «значение, минимум, максимум». Для shared_buffers это 16384 блока, и единица GUC_UNIT_BLOCKS делает из них 128 МБ:

	{
		{"shared_buffers", PGC_POSTMASTER, RESOURCES_MEM,
			gettext_noop("Sets the number of shared memory buffers used by the server."),
			NULL,
			GUC_UNIT_BLOCKS
		},
		&NBuffers,
		16384, 16, INT_MAX / 2,
		NULL, NULL, NULL

Источник: guc_tables.c, строки 2378–2386, REL_18_6. Значения всех двадцати параметров контейнер отдаёт сам: boot_val — компилируемое значение, unit — единица, context — когда параметр можно менять.

SELECT name, boot_val, coalesce(unit,'-') AS unit, coalesce(min_val,'-') AS min,
       coalesce(max_val,'-') AS max, context
FROM pg_settings
WHERE name IN ('shared_buffers','effective_cache_size','work_mem','maintenance_work_mem',
  'wal_buffers','max_wal_size','checkpoint_completion_target','random_page_cost',
  'effective_io_concurrency','autovacuum_vacuum_scale_factor','autovacuum_vacuum_cost_limit',
  'autovacuum_max_workers','max_connections','huge_pages','jit','statement_timeout',
  'idle_in_transaction_session_timeout','log_min_duration_statement','track_io_timing',
  'synchronous_commit')
ORDER BY name;

autovacuum_max_workers | 3 | - | 1 | 262143 | sighup
autovacuum_vacuum_cost_limit | -1 | - | -1 | 10000 | sighup
autovacuum_vacuum_scale_factor | 0.2 | - | 0 | 100 | sighup
checkpoint_completion_target | 0.9 | - | 0 | 1 | sighup
effective_cache_size | 524288 | 8kB | 1 | 2147483647 | user
effective_io_concurrency | 16 | - | 0 | 1000 | user
huge_pages | try | - | - | - | postmaster
idle_in_transaction_session_timeout | 0 | ms | 0 | 2147483647 | user
jit | on | - | - | - | user
log_min_duration_statement | -1 | ms | -1 | 2147483647 | superuser
maintenance_work_mem | 65536 | kB | 64 | 2147483647 | user
max_connections | 100 | - | 1 | 262143 | postmaster
max_wal_size | 1024 | MB | 2 | 2147483647 | sighup
random_page_cost | 4 | - | 0 | 1.79769e+308 | user
shared_buffers | 16384 | 8kB | 16 | 1073741823 | postmaster
statement_timeout | 0 | ms | 0 | 2147483647 | user
synchronous_commit | on | - | - | - | user
track_io_timing | off | - | - | - | superuser
wal_buffers | -1 | 8kB | -1 | 262143 | postmaster
work_mem | 4096 | kB | 64 | 2147483647 | user

Что видно в выводе. effective_io_concurrency по умолчанию 16: в PostgreSQL 18 его подняли до 16 по заметкам о выпуске. autovacuum_max_workers меняется по sighup, без рестарта, потому что в 18 появился autovacuum_worker_slots. wal_buffers = -1 значит «подобрать самому»: shared_buffers / 32, не меньше 8 страниц и не больше сегмента WAL:

static int
XLOGChooseNumBuffers(void)
{
	int			xbuffers;

	xbuffers = NBuffers / 32;
	if (xbuffers > (wal_segment_size / XLOG_BLCKSZ))
		xbuffers = (wal_segment_size / XLOG_BLCKSZ);
	if (xbuffers < 8)
		xbuffers = 8;
	return xbuffers;
}

Источник: xlog.c, строки 4656–4667, REL_18_6. При 128 МБ это 16384 / 32 = 512 страниц, то есть 4 МБ (pg_settings показал wal_buffers|512|8kB), при 512 МБ — 2048 страниц, то есть потолок в 16 МБ. Поэтому shared_buffers без явного wal_buffers меняет два параметра сразу.

Карта: параметр, подсистема, симптом, проверка

Для synchronous_commit проверка ниже. Контейнер на умолчаниях, pgbench на 8 клиентов, 40 снимков wait_event активных backend за 12 секунд:

docker exec -d dba-tuning pgbench -U postgres -c 8 -j 2 -T 12 postgres
for i in $(seq 1 40); do
  psql -U postgres -Atc "select coalesce(wait_event,'(CPU)') from pg_stat_activity
    where backend_type='client backend' and state='active' and pid<>pg_backend_pid()"
  sleep 0.2
done | sort | uniq -c | sort -rn | head -5

    169 WALWrite
     35 WalSync
     27 (CPU)
     16 transactionid
      9 ClientRead

В пяти самых частых ожиданиях (256 снимков) WALWrite набрал 169, WalSync — 35, то есть активные backend чаще всего стоят в очереди на запись и fsync журнала, а не считают. Это место, куда бьёт synchronous_commit.

Опыт: стенд и шум

Контейнер postgres:18 с --memory 2g --cpus 4, данные pgbench -s 50:

docker run -d --name dba-tuning --memory 2g --cpus 4 -e POSTGRES_PASSWORD=pg postgres:18
docker exec dba-tuning pgbench -U postgres -i -s 50 -q postgres
docker exec dba-tuning psql -U postgres -Atc "select pg_size_pretty(pg_database_size('postgres'))"

755 MB

База в шесть раз больше shared_buffers по умолчанию и меньше 2 ГБ контейнера. Один прогон варианта: сбросить настройки, задать нужные, перезапустить, сделать CHECKPOINT, прогреть 20 с (не считается), замерить 60 с с журналом транзакций и взять p99 из третьей колонки журнала:

psql -U postgres -c "ALTER SYSTEM RESET ALL" -c "ALTER SYSTEM SET max_wal_size = '4GB'"
docker restart dba-tuning
psql -U postgres -c "CHECKPOINT"
pgbench -U postgres -c 8 -j 2 -T 20 postgres
pgbench -U postgres -c 8 -j 2 -T 60 -l --log-prefix=/tmp/pgb postgres
cat /tmp/pgb* | awk '{print $3}' | sort -n | awk '{a[NR]=$1} END{print a[int(NR*0.99)]/1000}'

Каждый вариант прошёл по три прогона, варианты чередовались кругами (A, B, C, … затем снова A), чтобы дрейф машины не достался одному варианту. В таблицах медиана и в скобках минимум и максимум; счётчик checkpoint снимался за прогрев и замер вместе, 80 с.

Шум здесь велик. Диск общий: в списке процессов во время опыта шёл чужой pgbench на 100 клиентов. pg_test_fsync на диске базы три раза за время опыта показал разное время fdatasync (порядок строк — порядок замеров: до второго запуска, после него, после диагностики):

        fdatasync                           107.179 ops/sec    9330 usecs/op
        fdatasync                           974.840 ops/sec    1026 usecs/op
        fdatasync                           218.775 ops/sec    4571 usecs/op

Девять, одна и четыре с половиной миллисекунды: разница в девять раз на одном диске за время опыта. Всё, что упирается в fsync, на этой машине измеряется с таким разбросом.

Прогон 1: обычный COMMIT

Варианты: A — умолчания; B — shared_buffers = 512MB с закреплённым wal_buffers = 4MB; C — max_wal_size = 4GB; D — wal_buffers = 16MB; F — track_io_timing = on; G — B, C и D вместе.

Первые два прогона (A: 3236 и B: 2838 TPS) пришлись на быстрый диск, дальше машина осталась на 370–800 TPS. Разброс самих умолчаний (371–3236) больше любого различия между вариантами, а медианы 411–433 различаются примерно на 5 %. Вклад shared_buffers, max_wal_size, wal_buffers и track_io_timing в такой нагрузке не отличим от нуля. Приписать им эффект нельзя. Причина видна по ожиданиям выше: каждый COMMIT ждёт fdatasync, а восемь клиентов делят одну очередь на диск. max_wal_size тут не мог проявиться и по счётчику: в медленных прогонах за 80 с не прошло ни одного checkpoint, в двух быстрых прошло по два.

Прогон 2: synchronous_commit = off

Чтобы убрать fsync из пути клиента, к тем же вариантам добавили synchronous_commit = off (E — только он; I, J, K, L — он плюс один параметр; H — он плюс B, C и D). В таблице E и H по шесть прогонов (три круга из первого запуска и три из второго), остальные по три.

Сам synchronous_commit = off дал рост с 432 до 14 204 TPS, то есть примерно в 33 раза. Это единственный параметр, чей эффект больше шума любой строки. Среди остальных отличимо от E только сочетание H: +19 % по медиане, и его минимум (16 233) выше максимума E (14 600). Поодиночке диапазоны I, J и L пересекаются с диапазоном E. У K медиана ниже E на 7 %, диапазоны не пересекаются, но в диагностике ниже тот же вариант K прошёл быстрее E (13 582 против 12 949 TPS) при wal_buffers_full = 0: порядок разный в разных запусках, это дрейф машины, а не вклад параметра. Вклады не складываются: I, J и K вместе дают по медианам около +4 %, а H даёт +19 %. Разложить +19 % по параметрам этим опытом нельзя.

Счётчики показывают то, что TPS скрывает. Для E и I снято по 60 с после сброса pg_stat_io и pg_stat_wal:

== E tps=12949.041681 128MB 4MB
background writer | 28100 | 
checkpointer | 6071 | 
client backend | 674962 | 1069902
wal_buffers_full = 0
== I tps=12957.707917 512MB 4MB
background writer | 28300 | 
checkpointer | 239477 | 
client backend | 160030 | 337928
wal_buffers_full = 0

С shared_buffers = 512MB записи client backend упали с 674 962 до 160 030, вытеснения с 1 069 902 до 337 928: основную запись взял на себя checkpointer (6071 → 239 477). TPS при этом одинаковый, 12 949 и 12 958. Механизм «backend перестал сам писать страницы» подтверждён счётчиком, выигрыш в TPS на этой машине — нет. Для K (16 МБ) wal_buffers_full тоже 0, и у E с буфером 4 МБ он 0: при 8 клиентах буфер не переполнялся, увеличивать его было незачем.

SELECT-нагрузка (pgbench -S, по три прогона по 30 с) дала для 128 МБ 105 699 (105 419–107 067) TPS, для 512 МБ — 112 458 (105 430–115 297). Диапазоны пересекаются, разницу в 6 % по медиане нельзя засчитать shared_buffers.

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

Калькулятор конфигурации знает объём RAM, число ядер и тип диска. Он не знает, чем ограничен твой COMMIT, сколько в нагрузке записи, какой fsync у диска сейчас и что лежит в активном наборе данных, поэтому от него можно брать стартовые значения (документация PostgreSQL называет 25 % RAM для shared_buffers разумной отправной точкой), а не итог: в опыте выше одни и те же параметры дали +19 % при synchronous_commit = off и ничего при обычном COMMIT, и по входным данным калькулятора эти случаи не различить.

• Сначала ожидания, потом параметры. Профиль wait_event показал, что backend стоят на WALWrite и WalSync; настройка кэша и размера WAL при этом ничего не меняет, пока fsync медленный. Как читать профиль — в статье про wait events.
• synchronous_commit = off — не настройка, а смена гарантий: по документации при сбое теряются подтверждённые коммиты за окно до трёх wal_writer_delay (wal_writer_delay = 200 мс в pg_settings контейнера). Решение принимает владелец данных; про вариант с репликой — синхронная репликация.

Чек-лист

Перед правкой postgresql.conf:

1. Выбери подсистему по ожиданиям (pg_stat_activity.wait_event) и найди её параметры в таблице.
2. Запиши счётчик проверки до правки: pg_stat_io, pg_stat_checkpointer, pg_stat_wal или план EXPLAIN.
3. Меняй один параметр (или группу, которая меняется вместе: shared_buffers тянет за собой wal_buffers) и перезапускай, если контекст postmaster.
4. Повтори замер не меньше трёх раз одинаковой длины и сравни диапазоны, а не только медианы.
5. Убери контейнер опыта командой docker rm -f -v dba-tuning.