DevOps и серверы

Тюнинг PostgreSQL

По объёму памяти, числу ядер и типу нагрузки считает shared_buffers, work_mem и остальные параметры postgresql.conf — с объяснением, что каждый из них делает.

Железо и нагрузка

Память, ГБ

Ядер

Тип нагрузки

Накопитель

Одновременных соединений

По умолчанию для этой нагрузки — 200

Много коротких запросов, данные помещаются в память.

Параметры

Параметр	Значение
max_connections	200
shared_buffers	4GB
effective_cache_size	12GB
maintenance_work_mem	1GB
work_mem	10MB
checkpoint_completion_target	0.9
wal_buffers	16MB
default_statistics_target	100
random_page_cost	1.1
effective_io_concurrency	200
min_wal_size	1GB
max_wal_size	4GB
max_worker_processes	4
max_parallel_workers_per_gather	2
max_parallel_workers	4
max_parallel_maintenance_workers	2
huge_pages	off

Оговорки

Это отправная точка, а не истина: после применения смотрите pg_stat_statements и долгие запросы, иначе тюнинг превращается в гадание.

ALTER SYSTEM

ALTER SYSTEM SET max_connections = '200';
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET work_mem = '10MB';
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET default_statistics_target = '100';
ALTER SYSTEM SET random_page_cost = '1.1';
ALTER SYSTEM SET effective_io_concurrency = '200';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET max_worker_processes = '4';
ALTER SYSTEM SET max_parallel_workers_per_gather = '2';
ALTER SYSTEM SET max_parallel_workers = '4';
ALTER SYSTEM SET max_parallel_maintenance_workers = '2';
ALTER SYSTEM SET huge_pages = 'off';
SELECT pg_reload_conf();
-- shared_buffers, max_connections и huge_pages требуют перезапуска сервера.

Что означает каждый параметр

max_connections = 200

Столько одновременных соединений держит база. Каждое — отдельный процесс, поэтому больше нескольких сотен обычно берут пулером (PgBouncer), а не этим числом.

shared_buffers = 4GB

Собственный кэш страниц PostgreSQL — четверть памяти машины. Остальное оставляем кэшу файловой системы: он тоже держит эти страницы.

effective_cache_size = 12GB

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

maintenance_work_mem = 1GB

Память под VACUUM, CREATE INDEX и ALTER TABLE. Больше — быстрее перестройка индексов.

work_mem = 10MB

Память на одну операцию сортировки или хеша. Множится на число операций и соединений — отсюда деление на 200 × 3.

checkpoint_completion_target = 0.9

Растягивает запись контрольной точки почти на весь интервал: диск не получает всплеск в конце.

wal_buffers = 16MB

Буфер журнала предзаписи. 16 МБ — размер одного сегмента WAL, больше не нужно.

default_statistics_target = 100

Подробность статистики по столбцам. Для аналитики выше: планировщику важнее точная оценка числа строк.

random_page_cost = 1.1

Стоимость случайного чтения относительно последовательного. Для SSD / NVMe разница почти исчезла, и значение 4 из времён HDD заставляет планировщик избегать индексов.

effective_io_concurrency = 200

Сколько параллельных чтений имеет смысл запускать. У SSD очередь глубокая, у HDD — головка одна.

min_wal_size = 1GB

Ниже этого объёма WAL-файлы не удаляются, а переиспользуются — меньше работы файловой системе.

max_wal_size = 4GB

Порог, после которого начинается контрольная точка. Больше — реже, но дольше восстановление после сбоя.

max_worker_processes = 4

Общий потолок фоновых процессов: параллельные запросы, логическая репликация, расширения.

max_parallel_workers_per_gather = 2

Сколько процессов помогает одному запросу. Слишком много — и параллельные планы вытеснят обычные запросы.

max_parallel_workers = 4

Потолок параллельных исполнителей на всю базу.

max_parallel_maintenance_workers = 2

Ускоряет CREATE INDEX и VACUUM, не трогая обычные запросы.

huge_pages = off

Большие страницы экономят память под таблицу страниц. Имеет смысл от 32 ГБ и требует настройки vm.nr_hugepages.