Postgres правильно настроен для экземпляра EPYC с 64 ГБ

Что создаётся в итоге

Один экземпляр Postgres на E-8, использующий предоставленное железо: восемь выделенных ядер Zen 4, 64 ГБ зарегистрированной ECC DDR5 и Gen4 NVMe с защитой от потери питания. Из коробки Postgres настроен так, как будто запускается на ноутбуке 2009 года. Если оставить как есть, он будет использовать примерно одну сотую памяти этой машины и удивляться, почему он медленный.

Каждое число ниже вычислено, а не скопировано. Если в вашем экземпляре другой объём памяти, приведена арифметика, чтобы вы могли пересчитать. Это написано для нагрузки, которая в основном транзакционная с некоторой отчётностью — именно так у большинства людей на самом деле, что бы они ни говорили.

Перед началом

• E-8 или больше. При объёме ниже 64 ГБ пропорции сохраняются, но абсолютные числа нет, и большие страницы перестают быть оправданными.
• ECC-память, которая есть в линейке EPYC, но нет в линейке Ryzen. Ошибка памяти в разделяемых буферах — это повреждённая страница, записанная обратно на диск без предупреждения.
• Debian 13, в котором пакетирован PostgreSQL 17.

1. Установка и инициализация

apt update && apt install -y postgresql postgresql-contrib numactl
systemctl stop postgresql
pg_dropcluster 17 main
pg_createcluster 17 main -- --data-checksums --encoding=UTF8 --locale=C.UTF-8

Контрольные суммы данных нужно выбирать при инициализации, и они стоят пары процентов пропускной способности. Это разница между сообщением о повреждённой странице и выдачей повреждённой страницы, так что возьмите эти два процента.

2. Память

Четыре параметра, четыре расчёта:

• shared_buffers на четверть ОЗУ: 16 ГБ. Раньше большие значения не рекомендовали; на выделенной машине с современным ядром четверть — это хорошо проверенная отправная точка, а треть допустима, если ваш рабочий набор больше.
• effective_cache_size на три четверти ОЗУ: 48 ГБ. Это ничего не выделяет; это говорит планировщику, сколько ядро, вероятно, закешировало, и если задать слишком мало, планировщик будет отказываться от сканирования индексов, которые должен выбирать.
• work_mem на узел сортировки, а не на соединение: при 200 соединениях и, возможно, двух сортировках на каждое, 32 МБ — разумный потолок для 64 ГБ. Для отчётных запросов поднимайте это значение на сессию, а не глобально.
• maintenance_work_mem для vacuum и построения индексов: 2 ГБ, а autovacuum_work_mem оставьте, чтобы оно наследовалось.

shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
huge_pages = try

3. Большие страницы

Шестнадцать гигабайт разделяемых буферов, отображённых страницами по четыре килобайта, — это четыре миллиона записей таблицы страниц на каждый бэкенд. Страницы по два мегабайта уменьшают это в пятьсот раз, и выигрыш проявляется как снижение нагрузки на CPU при конкурентности, а не как громкая цифра в заголовке.

systemctl start postgresql
sudo -u postgres psql -c "SHOW shared_memory_size_in_huge_pages;"

Возьмите число, которое выведется, добавьте десять процентов запаса и запишите:

echo "vm.nr_hugepages = 8600" > /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_memory = 2" >> /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_ratio = 90" >> /etc/sysctl.d/60-postgres.conf
echo "vm.swappiness = 1" >> /etc/sysctl.d/60-postgres.conf
sysctl --system
echo never > /sys/kernel/mm/transparent_hugepage/enabled

Явные большие страницы — да, прозрачные большие страницы — нет. Вторые дефрагментируют память в непредсказуемые моменты и дают всплески задержек, которые люди неделями списывают на своё хранилище.

4. Журнал предзаписи и контрольные точки

Настройки контрольных точек по умолчанию заставляют при нагрузке сбрасывать данные каждые несколько секунд, превращая ваш NVMe в очередь из мелких синхронных записей.

wal_level = replica
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = zstd
wal_buffers = 64MB
synchronous_commit = on
full_page_writes = on

Оставьте synchronous_commit включённым. Его отключение — самое большое доступное увеличение пропускной способности, но это означает подтверждение транзакций, которые может съесть потеря питания. В наших дисках конденсаторная защита кэша записи, так что fsync здесь действительно дешёвый; купите пропускную способность честно в другом месте.

5. Хранилище и планировщик

random_page_cost = 1.1
seq_page_cost = 1.0
effective_io_concurrency = 200
maintenance_io_concurrency = 200
default_statistics_target = 200
jit = off

random_page_cost со значением четыре — это цифра для вращающихся дисков, и это самая распространённая отдельная ошибка конфигурации в реальном мире. На NVMe случайное чтение стоит почти столько же, сколько последовательное, и если сказать планировщику иначе, он будет избегать индексов на больших таблицах. JIT-компиляция выключена, потому что для коротких транзакционных запросов она тратит больше времени на компиляцию, чем на выполнение; включайте её на сессию для аналитических запросов.

6. Параллелизм, autovacuum и соединения

max_connections = 200
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4

autovacuum_max_workers = 4
autovacuum_naptime = 15s
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_limit = 3000

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on
log_min_duration_statement = 500ms
log_checkpoints = on
log_autovacuum_min_duration = 0

Числа воркеров соответствуют восьми выделенным ядрам, потому что они выделенные. На перепроданном хосте эти числа были бы ложью, и вы бы уменьшили их до той доли ядра, которую вам на самом деле продали; здесь такой проблемы нет.

Коэффициент масштабирования autovacuum по умолчанию 0.2 означает, что таблица из ста миллионов строк ждёт двадцать миллионов мёртвых кортежей, прежде чем что-то произойдёт. Два процента вместо двадцати поддерживают vacuum частым и коротким, а не редким и катастрофическим.

7. Пул соединений

Двести бэкендов на восьми ядрах — это не параллелизм, а очередь с лишними накладными расходами на память. Поставьте впереди пулер:

apt install -y pgbouncer

[databases]
app = host=/var/run/postgresql dbname=app

[pgbouncer]
listen_addr = 10.9.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 32
max_client_conn = 2000
server_idle_timeout = 60

Транзакционный пул с тридцатью двумя серверными соединениями даёт по четыре на ядро, что примерно та точка, где пропускная способность перестаёт расти на этом кремнии. Приложения, использующие функции уровня сессии, такие как advisory locks или подготовленные операторы между транзакциями, требуют сессионного пула и получат меньшее число.

systemctl restart postgresql pgbouncer

Проверка

Сначала настройки. Всё, что не применилось, будет очевидно здесь, а не через три недели:

sudo -u postgres psql -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers','effective_cache_size','work_mem','huge_pages','random_page_cost','max_wal_size');"
grep -E "HugePages_(Total|Free)" /proc/meminfo

HugePages_Free должно быть на несколько тысяч страниц ниже HugePages_Total, что означает, что Postgres их действительно отобразил. Равные значения означают, что он откатился к маленьким страницам, и huge_pages = try молча проглотил ошибку.

Затем измерьте. Создайте набор данных достаточно большой, чтобы быть интересным, но достаточно маленький, чтобы поместиться в разделяемые буферы:

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 800 bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 -S bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 bench

На E-8 прогон только на чтение даёт низкие сотни тысяч транзакций в секунду, а прогон на чтение и запись — средние тысячи. Точные цифры зависят от площадки и набора данных, так что полезный сигнал — порядок величины: если тест только на выборку возвращает десятки тысяч, а не сотни тысяч, shared_buffers не применилось или вы всё ещё тестируете через холодный кэш при первом запуске.

Наконец, убедитесь, что расширение статистики загружено, потому что именно его вы будете реально использовать каждую неделю:

sudo -u postgres psql bench -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
sudo -u postgres psql bench -c "SELECT calls, round(mean_exec_time::numeric,2) AS ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

После

Включите архивирование WAL на второй экземпляр в другом городе, прежде чем в этой базе появится что-то, что вам будет жаль потерять. Восстановление базового бэкапа, который вы никогда не тестировали, — это не бэкап, а надежда, и страница локаций существует в том числе для того, чтобы ваша резервная копия не находилась в том же здании, что и основная.