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

Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).

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

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

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

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

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

4. Перезагрузка или рестарт служб: Большинство изменений требует перезагрузки сервера или, как минимум, перезапуска 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% необходимы для операционной системы, дискового кэша и других процессов.

• Приоритет для OLTP (много одновременных коротких транзакций, чтение/запись): акцент на shared_buffers.

• Приоритет для 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 и количестве подключений, требует стартовых вычислений и последующего мониторинга.