Оптимизация PostgreSQL на Linux: ключевые параметры

Баланс и скорость: настраиваем PostgreSQL для максимальной отдачи железа.

Производительность PostgreSQL — это не магия, а результат грамотной настройки множества взаимосвязанных компонентов: от ядра операционной системы до внутренних параметров СУБД. Данный материал представляет собой сжатое, практико-ориентированное руководство, которое систематизирует ключевые настройки для Linux и PostgreSQL. Опираясь на результаты нагрузочного тестирования, мы выделим оптимальные значения для типовой конфигурации сервера и объясним, как они влияют на работу под различными типами нагрузок — OLTP и OLAP.

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

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

Часть-1 : Cписок ключевых параметров Linux и PostgreSQL, сгруппированный по разделам, для оптимизации производительности СУБД.

⚙️ CPU

Параметры, влияющие на эффективность использования процессорных ресурсов.

• Планировщик процессора (governor):
  Описание: Регулирует частоту работы CPU для баланса производительности и энергосбережения. Для серверов БД критична стабильная максимальная производительность.
  Значение по умолчанию: Часто powersave или ondemand.
  Как изменить:
  echo performance | sudo tee /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
  Для постоянного применения: Используйте профиль tuned, например tuned-adm profile throughput-performance или создайте кастомный профиль с [cpu] governor=performance.
• Прозрачные огромные страницы (Transparent Huge Pages, THP):
  Описание: Механизм ядра, автоматически объединяющий страницы памяти (обычно 4 КБ) в огромные (например, 2 МБ) для ускорения доступа. Может вызывать задержки у СУБД из-за динамической дефрагментации памяти.
  Значение по умолчанию: Обычно always или madvise.
  Как изменить:
  Временно: echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
  Навсегда: Добавьте transparent_hugepage=never в параметры загрузки ядра в GRUB (через /etc/default/grub) или используйте профиль tuned.
• max_parallel_workers_per_gather (PostgreSQL):
  Описание: Максимальное число рабочих процессов, которые может использовать один узел Gather для параллельного выполнения запроса. Влияет на скорость сложных аналитических запросов.
  Значение по умолчанию: 2
  Как изменить: В файле postgresql.conf. Для OLAP-нагрузок можно увеличить значение.

💾 Оперативная память (RAM)

Параметры управления распределением и использованием памяти.

• Разделяемая память (shmmax, shmall):
  Описание:
  kernel.shmmax - максимальный размер одного сегмента разделяемой памяти.
  kernel.shmall - общий объем разделяемой памяти в системе.
  Критически важны для работы shared_buffers PostgreSQL.
  Значение по умолчанию: Зависит от дистрибутива, часто слишком мало для продакшена.
  Как изменить:
  sudo sysctl -w kernel.shmmax=17179869184 (16 ГБ) и sudo sysctl -w kernel.shmall=4194304
  Для постоянного применения: Добавьте строки kernel.shmmax=... и kernel.shmall=... в файл /etc/sysctl.conf.
• Огромные страницы (Huge Pages):
  Описание: Статическое выделение памяти большими блоками (напр., 2 МБ вместо 4 КБ). Снижает нагрузку на TLB процессора и накладные расходы ядра, может ускорить работу больших инстансов.
  Значение по умолчанию: Обычно отключены (HugePages_Total: 0).
  Как изменить:
  Рассчитайте необходимое количество: (VmPeak процесса postgres) / Hugepagesize.
  Задайте количество: sudo sysctl -w vm.nr_hugepages=NNN.
  Включите в postgresql.conf: huge_pages = on.
• shared_buffers (PostgreSQL):
  Описание: Основной кэш данных в памяти PostgreSQL. Хранит часто используемые страницы таблиц и индексов. Самая важная настройка памяти.
  Значение по умолчанию: 128MB
  Как изменить: В файле postgresql.conf. Рекомендация: 15-25% от общего объема RAM сервера. Требует перезагрузки СУБД.
• work_mem (PostgreSQL):
  Описание: Память для внутренних операций одного запроса: сортировка, хеширование, соединения. Недостаток ведет к записи на диск, избыток - к чрезмерному потреблению памяти при параллельных запросах.
  Значение по умолчанию: 4MB
  Как изменить: В файле postgresql.conf. Рекомендация: Начинать с 32-64 МБ и корректировать, наблюдая за использованием временных файлов. Внимание: это лимит на операцию, а не на соединение.
• vm.swappiness:
  Описание: Склонность ядра к выгрузке страниц памяти на диск (своп). Слишком высокое значение для сервера БД может привести к ненужной активности диска.
  Значение по умолчанию: 60
  Как изменить:
  sudo sysctl -w vm.swappiness=10
  Для постоянного применения: Добавьте vm.swappiness = 10 в /etc/sysctl.conf или профиль tuned.

📁 Ввод-Вывод (I/O)

Параметры для оптимизации работы с диском.

• Параметры "грязных" страниц (dirty_ratio, dirty_background_ratio):
  Описание: Контролируют, когда ядро начинает записывать на диск измененные ("грязные") страницы кэша.
  dirty_background_ratio - процент памяти, при достижении которого начинается фоновая запись.
  dirty_ratio - процент, при котором процессы принудительно начинают запись, что может их блокировать.
  Значение по умолчанию: Зависит от дистрибутива.
  Как изменить: Через sysctl или профиль tuned. Например: vm.dirty_background_ratio = 3 и vm.dirty_ratio = 10 для более агрессивной записи и снижения пиковой нагрузки.
• Отключение обновления времени доступа (noatime, nodiratime):
  Описание: Отключает обязательное обновление времени последнего доступа к файлу при каждом чтении. Это исключает лишние операции записи на диск.
  Значение по умолчанию: Опция отключена.
  Как изменить: Добавьте опции noatime,nodiratime в соответствующую строку для точки монтирования данных PostgreSQL в файле /etc/fstab. Затем перемонтируйте: sudo mount -o remount /pgdata.
• effective_io_concurrency (PostgreSQL):
  Описание: Указывает планировщику PostgreSQL, сколько параллельных операций I/O может ожидать от одного диска. Крайне важно для SSD/NVMe.
  Значение по умолчанию: 1
  Как изменить: В файле postgresql.conf. Рекомендации: Для SATA/SAS RAID - 2-4, для SSD - 200-300, для NVMe - может быть выше.
• Контрольные точки WAL (max_wal_size, checkpoint_completion_target):
  Описание:
  max_wal_size - мягкий лимит размера WAL, влияющий на частоту контрольных точек.
  checkpoint_completion_target - доля интервала между контрольными точками, за которую они должны быть завершены, для сглаживания нагрузки.
  Значение по умолчанию: 1GB и 0.5
  Как изменить: В файле postgresql.conf. Рекомендации: Увеличение max_wal_size (напр., до 4GB) и checkpoint_completion_target (до 0.9) снижает пиковую нагрузку от контрольных точек.

🔍 Важные рекомендации по настройке

Рабочая нагрузка:
OLTP (много коротких транзакций): уделите больше внимания WAL, контрольным точкам, стабильности ввода-вывода.
OLAP (сложные аналитические запросы): настройте параллелизм, увеличьте work_mem и effective_cache_size.

📚 Источники

Использованные материалы послужили основой для этого отчета:

1. Habr: "PostgreSQL: настройка и оптимизация производительности. Часть 1" — настройка системы, tuned, Huge Pages.
2. Habr: "Основные настройки для управления ресурсами в PostgreSQL" — ключевые параметры PostgreSQL.
3. EnterpriseDB: "Comprehensive Guide How Tune PostgreSQL Parameters".
4. MyDBOps: "PostgreSQL Performance Tuning Best Practices 2025" — настройка под разные нагрузки.
5. SQL-EX.ru: "Настройка конфигурации PostgreSQL: лучшие практики..." — инструменты и методики.
6. SeveralNines: "Tuning Input/Output (I/O) Operations for PostgreSQL" — детали по I/O.
7. Proglib.io: "Оптимизация использования памяти в PostgreSQL" — глубокий разбор памяти.
8. Habr: "Настройка параметров ядра Linux для оптимизации PostgreSQL" — SHMMAX, Huge Pages.
9. Percona: "PostgreSQL Performance Tuning Guide" — практические советы по настройке.
10. Официальная документация PostgreSQL: "Setting Parameters".

Часть-2: Рекомендуемые настройки для сервера PostgreSQL 17 (8 CPU, 8 GB RAM, SSD: /data, /wal) , основанные на результатах нагрузочного тестирования в аналогичной среде

⚙️ CPU

• max_parallel_workers_per_gather
  Описание: Количество рабочих процессов для параллельного выполнения одного запроса.
  Значение по умолчанию: 2
  Рекомендуемое для OLAP: 4-6 (для сложных аналитических запросов).
  Рекомендуемое для OLTP: 1-2 или оставить по умолчанию (параллелизм может мешать коротким транзакциям).
  Способ изменения: В postgresql.conf. Можно задать на уровне БД: ALTER DATABASE имя_бд SET max_parallel_workers_per_gather = N;.

🧠 RAM

• shared_buffers
  Описание: Основной кэш данных. При недостатке shared_buffers (что наблюдалось в тестах) растет нагрузка на диск.
  Значение по умолчанию: 128MB
  Рекомендуемое для OLAP: 3072MB (3-4 GB). Тесты показали, что низкий shared_buffers (например, 2GB) для OLAP ведет к крайне низкому Hit Ratio (57%).
  Рекомендуемое для OLTP: 2048MB (2 GB) — 25% от RAM.
  Способ изменения: В postgresql.conf. Требует перезагрузки СУБД.
• work_mem
  Описание: Память для операций сортировки и хеширования в рамках одной операции.
  Значение по умолчанию: 4MB
  Рекомендуемое для OLAP: 32-64MB. Это помогает снизить запись временных файлов на диск.
  Рекомендуемое для OLTP: 10-16MB. Рассчитывается как: (25% RAM) / max_connections.
  Способ изменения: В postgresql.conf.
• effective_cache_size
  Описание: Оценка кэша ОС и СУБД для планировщика запросов.
  Значение по умолчанию: 4GB
  Рекомендуемое для OLAP/OLTP: 6GB (75% от RAM).
  Способ изменения: В postgresql.conf.
• maintenance_work_mem
  Описание: Память для операций обслуживания (VACUUM, CREATE INDEX).
  Значение по умолчанию: 64MB
  Рекомендуемое для OLAP/OLTP: 512MB-1GB.
  Способ изменения: В postgresql.conf.

💽 I/O (с учетом разделения /data и /wal)

• random_page_cost
  Описание: Стоимость случайного доступа к диску для планировщика. Критически важно для SSD.
  Значение по умолчанию: 4.0 (оптимизировано под HDD).
  Рекомендуемое для OLAP/OLTP (SSD): 1.0-1.1.
  Способ изменения: В postgresql.conf.
• effective_io_concurrency
  Описание: Ожидаемое количество параллельных операций I/O.
  Значение по умолчанию: 1
  Рекомендуемое для OLAP/OLTP (SSD): 200.
  Способ изменения: В postgresql.conf.
• wal_buffers
  Описание: Память под буфер WAL перед записью на диск (расположен в /wal).
  Значение по умолчанию: -1 (автонастройка, обычно 16MB).
  Рекомендуемое для OLTP (интенсивная запись): 16-32MB.
  Рекомендуемое для OLAP: можно оставить по умолчанию.
  Способ изменения: В postgresql.conf.
• max_wal_size / min_wal_size
  Описание: Лимиты размера WAL в директории /wal. Влияют на частоту контрольных точек.
  Значение по умолчанию: 1GB / 80MB
  Рекомендуемое для OLTP: Увеличить max_wal_size до 4GB.
  Рекомендуемое для OLAP: 2-4GB (с учетом нагрузки большими чтениями).
  Способ изменения: В postgresql.conf.
• checkpoint_completion_target
  Описание: Цель завершения контрольной точки. Распределяет нагрузку от контрольных точек.
  Значение по умолчанию: 0.5
  Рекомендуемое для OLAP/OLTP: 0.8-0.9. Помогает сгладить пики I/O.
  Способ изменения: В postgresql.conf.

🔄 Autovacuum

• autovacuum_vacuum_scale_factor / autovacuum_vacuum_threshold
  Описание: Порог и доля измененных строк для запуска автоочистки таблицы.
  Значение по умолчанию: 0.2 / 50
  Рекомендуемое для OLTP (высокая активность): 0.02 / 100 — более частная очистка для предотвращения "раздувания" таблиц (bloat).
  Рекомендуемое для OLAP: можно оставить по умолчанию или немного снизить scale_factor.
  Способ изменения: В postgresql.conf или ALTER TABLE для конкретных таблиц.

🐧 Настройки ядра Linux (кратко)

Эти параметры — фундамент для работы PostgreSQL. Проверьте текущие значения (sysctl -a | grep параметр) и настройте их через /etc/sysctl.conf:

• kernel.shmmax / kernel.shmall: Максимальный размер и общий объем разделяемой памяти. Для shared_buffers=2GB установите shmmax не менее 2147483648 (2GB).
• vm.swappiness: Склонность к выгрузке памяти на диск (своп). Для сервера БД установите 1-10 вместо значения по умолчанию (60).

📝 Ключевые рекомендации и порядок действий

1. Сначала настройте ядро Linux, затем перезагрузите сервер.
2. Начните с консервативных значений (нижняя граница диапазонов) и настраивайте под конкретную нагрузку.
3. OLAP фокус: Увеличивайте shared_buffers и work_mem для кэширования и работы с большими наборами данных. Анализируйте запросы с помощью EXPLAIN ANALYZE.
4. OLTP фокус: Настройте WAL (wal_buffers, max_wal_size) и автовакуум для эффективной записи и поддержания порядка в таблицах. Используйте пулер соединений (PgBouncer).
5. SSD обязательно: Снизьте random_page_cost и увеличьте effective_io_concurrency.
6. Мониторинг: После применения настроек следите за метриками (hit ratio буферов, время отклика диска, активность автовакуума).

📚 Использованные материалы

1. Percona Forum: Рекомендации по postgresql.conf для 8GB RAM, 8 CPU, SSD.
2. Сравнительный анализ паттернов производительности PostgreSQL 17: OLAP vs OLTP. Ключевой источник с данными нагрузочного тестирования.
3. PostgreSQL Performance Tuning (PgEdge Blog).
4. Официальная документация PostgreSQL: Установка параметров.
5. Статья на Habr: Основные настройки для управления ресурсами в PostgreSQL.
6. PostgreSQL Performance Tuning: Best Practices for 2025 (MyDBOps).
7. Percona Blog: Область видимости и приоритет параметров PostgreSQL.
8. Комментарии на Habr к статье по настройке PostgreSQL.
9. PostgreSQL 17 Kernel Tuning Guide.

Послесловие

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