PG_EXPECTO : тонкая настройка RAM и ядра для PostgreSQL

От стабильности к скорости: каждый байт на счету

Настройка параметров памяти и ядра — критически важный этап развёртывания PostgreSQL в продуктивной среде. Неправильные значения могут привести не только к снижению производительности, но и к невозможности запуска СУБД или аварийным завершениям под нагрузкой. В этом материале систематизированы параметры от критически важных до оптимизационных, а также приведена методика расчёта ключевых настроек PostgreSQL.

Глоссарий терминов | Postgres DBA | Дзен

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Влияние параметров настройки RAM на производительность PostgreSQL

🥇 Критическая важность (влияние на запуск и стабильность)

Эти параметры — обязательный минимум. При неверной настройке PostgreSQL либо не запустится, либо будет аварийно завершаться под нагрузкой.

1. Параметры разделяемой памяти (Shared Memory)

Используются для общего буферного кэша (shared_buffers) и взаимодействия процессов.

• kernel.shmmax: Максимальный размер одного сегмента разделяемой памяти. Должен быть не меньше значения shared_buffers в Postgres.
• kernel.shmall: Общий объем разделяемой памяти в системе. Должен быть достаточным для всех сегментов.
• Последствия ошибки: FATAL: could not create shared memory segment — сервер не запустится.

2. Параметры семафоров (Semaphores)

Координируют доступ множества процессов PostgreSQL к общим ресурсам и блокировкам.

• kernel.sem: Комплексный параметр, определяющий лимиты семафоров. Ключевые значения:
• SEMMNS: Общее количество семафоров в системе.
• SEMMNI: Максимальное количество наборов семафоров.
• Как рассчитать: Требуемое количество зависит от max_connections, max_worker_processes и других параметров. Узнать его можно командой postgres -D $PGDATA -C num_os_semaphores.
• Последствия ошибки: No space left on device при вызове semget, отказ в новых подключениях, блокировки.

🥈 Высокая важность (влияние на отзывчивость и пропускную способность)

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

3. ‼️vm.swappiness (склонность к свопингу)

Определяет, как часто ядро будет перемещать страницы памяти из ОЗУ на диск (своп). Для сервера БД это почти всегда вредно.

• ➡️Рекомендация: Установить значение 1 (минимальная склонность к свопу) вместо стандартных 60. Это резервирует свопинг для крайних случаев, удерживая рабочую нагрузку Postgres в оперативной памяти.

4. ‼️Параметры "грязных" страниц (vm.dirty_*)

Контролируют, как часто измененные (грязные) данные в памяти записываются на диск.

• vm.dirty_background_ratio: Процент памяти, при заполнении которым фоновые процессы начинают асинхронную запись на диск. Рекомендуется 5%.☑️
• vm.dirty_ratio: Процент памяти, при достижении которого новые операции будут блокироваться до освобождения места синхронной записью. Рекомендуется 10%.☑️
• Зачем настраивать: Сглаживает пики ввода-вывода, предотвращая внезапные блокировки всех процессов при заполнении буферов.

🥉 Средняя важность (оптимизация для конкретных сценариев и нагрузок)

Эти настройки дают измеримый прирост в определенных условиях.

5. Большие страницы (Huge Pages)

Уменьшают накладные расходы ядра на управление памятью (TLB-кешем), что может ускорить работу с большим объемом shared_buffers.

Как включить:

• Рассчитать необходимое количество страниц, исходя из потребляемой памяти процессом Postgres.
• Установить vm.nr_hugepages через sysctl.
• В postgresql.conf задать huge_pages = on (или try).
• Важно: Может затруднить выделение памяти при недостатке огромных страниц. Требует предварительного планирования.

6. Политика overcommit памяти (vm.overcommit_*)

Определяет, насколько агрессивно ядро будет выделять память по запросам процессов.

• vm.overcommit_memory: Для рабочих нагрузок с высокой параллельностью (например, как в Greenplum на базе Postgres) часто рекомендуется значение 2, которое строже проверяет доступную память.
• vm.overcommit_ratio: Процент физической RAM, доступной для overcommit. Рассчитывается индивидуально.
• Внимание: Настройка этих параметров требует глубокого понимания нагрузки и доступных ресурсов (RAM + swap). Неправильные значения приведут к Out of Memory и убийству процессов.

💎 Резюме и рекомендации по настройке

Проверка текущих значений: sysctl -a | grep -i <параметр> и ipcs -l.

Настройка параметров:

• Для применения до перезагрузки: sudo sysctl -w <параметр>=<значение>.
• Для постоянного изменения: /etc/sysctl.conf.

Последовательность настройки: 1️⃣Начните с критически важных параметров (shmmax, shmall, sem), 2️⃣затем перейдите к параметрам производительности (swappiness, dirty_*). Большие страницы и overcommit настраивайте только при наличии конкретной необходимости и понимания поведения вашей системы.

Перезагрузка или рестарт служб: Большинство изменений требует перезагрузки сервера или, как минимум, перезапуска PostgreSQL для применения.

Настройка параметров ядра — фундамент. Для дальнейшей оптимизации обратите внимание на внутренние параметры памяти самого PostgreSQL, такие как shared_buffers, work_mem и effective_cache_size.

Общая методика расчета значений shared_buffers и work_mem

Для расчета shared_buffers и work_mem в PostgreSQL универсальной формулы нет. Оптимальные значения зависят от типа нагрузки (OLTP, OLAP, смешанная), количества подключений и доступной RAM. Методика состоит из стартовых расчетов и последующей тонкой настройки на основе мониторинга.

1. Общие принципы и баланс памяти

• Общее правило: Общий объем памяти, выделяемый PostgreSQL (shared_buffers + work_mem * max_connections + прочие буферы), не должен превышать 90% от доступной оперативной памяти. Оставшиеся 10-15% необходимы для операционной системы, дискового кэша и других процессов.
• 1️⃣Приоритет для OLTP (много одновременных коротких транзакций, чтение/запись): акцент на shared_buffers.
• 2️⃣Приоритет для OLAP (сложные аналитические запросы, большие сортировки, агрегации): акцент на work_mem.

2. Методика расчета shared_buffers

Это главный кэш данных PostgreSQL для буферов таблиц и индексов.

• Стартовое значение (общее правило): 25% от объема доступной RAM.
• Для сервера с 64 ГБ RAM: 64 ГБ * 0.25 = 16 ГБ.
• Максимальный практический предел: 40% от RAM (для очень больших машин). Установка выше 8-10 ГБ редко дает линейный прирост производительности, так как ОС эффективно использует оставшуюся память для дискового кэша (Page Cache).

Уточнение в зависимости от нагрузки:

• OLTP (высокий параллелизм, частое чтение): Можно поднять до 30-35% от RAM.
• OLAP (большие последовательные сканирования): Оставить около 15-20% от RAM. Большие последовательные чтения часто минуют shared_buffers, эффективнее полагаться на кэш ОС.
• Смешанная нагрузка: Начать с 25% и корректировать по мониторингу.

3. Методика расчета work_mem

Эта память выделяется на операцию (сортировка, хэш-соединение, агрегация) в рамках одного запроса. Один сложный запрос может использовать work_mem многократно.

Ключевое ограничение: work_mem * max_connections — это потенциальный пиковый расход, а не гарантированное выделение. Расчет должен быть консервативным.

Стартовая формула: work_mem = (Доступная RAM для Postgres - shared_buffers) / (max_connections * 2)

Доступная RAM для Postgres: Например, 90% от общего объема RAM.

Делитель на 2-4: Предполагает, что не все подключения одновременно выполняют тяжелые операции, требующие полного объема work_mem.

Пример расчета для сервера с 64 ГБ RAM:

• RAM для Postgres: 64 ГБ * 0.9 ≈ 58 ГБ.
• shared_buffers: 64 ГБ * 0.25 = 16 ГБ.
• max_connections (например): 100.
• work_mem = (58 ГБ - 16 ГБ) / (100 * 2) = 42 ГБ / 200 ≈ 215 МБ.

Уточнение по типу нагрузки:

• OLAP (сложные запросы): Увеличивайте work_mem, чтобы тяжелые сортировки выполнялись в памяти, а не на диск. Возможно, потребуется снизить max_connections для баланса.
• OLTP (простые запросы): Значение может быть значительно меньше (например, 4-64 МБ).

Резюме: пошаговый алгоритм

1. Рассчитать shared_buffers как 25% от RAM.
2. Оценить доступную для Postgres RAM (обычно 90% от общего объема).
3. Рассчитать стартовое work_mem по формуле: (RAM_for_Postgres - shared_buffers) / (max_connections * 2).
4. Мониторинг: pg_stat_bgwriter, pg_stat_statements, общий мониторинг ОС (своп, использование RAM).
5. Скорректировать shared_buffers в сторону увеличения, если низкий cache hit ratio.
6. Выявлять "прожорливые" запросы по временным файлам и оптимизируйте их (индексы, переписывание) или точечно увеличивайте для них work_mem.

Эта методика даст научно обоснованную стартовую точку для настройки. Итоговые оптимальные значения всегда определяются эмпирически под конкретную нагрузку.

Итог

Правильная настройка памяти для PostgreSQL включает три уровня:

1. Критически важные параметры ядра (shared memory, semaphores) — без них СУБД не запустится.
2. Параметры производительности (swappiness, dirty pages) — влияют на отзывчивость и стабильность под нагрузкой.
3. Оптимизационные настройки (huge pages, overcommit) — дают прирост в специфических сценариях.

Расчёт shared_buffers и work_mem основан на типе нагрузки (OLTP/OLAP), доступной RAM и количестве подключений, требует стартовых вычислений и последующего мониторинга.

Экспериментальная проверка методики