Привет, Хабр!

При работе с PostgreSQL (да и в целом с любой БД) важно правильно настраивать и управлять ресурсами, такими как память, процессорное время и дисковые операции, и так далее для обеспечения лучшей производительности и стабильности работы БД.

В этой статье мы как раз и рассмотрим кратко о том, как управлять ресурсами в PostgreSQL.

Основные параметры конфигурации памяти

shared_buffers определяет количество памяти, выделенное сервером для кэширования данных. Рекомендуется установить его в пределах 15-25% от общего объема оперативной памяти системы. Например, для сервера с 32 ГБ RAM рекомендуемое значение составляет около 8 ГБ. Изменение этого параметра требует перезапуска сервера базы данных.

Work_mem управляет объемом памяти, используемой для операций сортировки и хеш-таблиц во время запросов. Настройка адекватного значения work_mem может уменьшить количество операций записи на диск, что, в свою очередь, приводит к ускорению выполнения запросов. Рекомендуется начинать с низкого значения, например, 32-64МБ, и адаптировать его в соответствии с наблюдаемой производительностью и размерами временных файлов, записанных в логах.

maintenance_work_mem задает максимальный объем памяти для операций обслуживания, таких как VACUUM и CREATE INDEX. В большинстве случаев безопасно установить это значение значительно выше, чем work_mem, поскольку это может ускорить процесс очистки. Общая рекомендация - установить его на уровне 10% от общего объема системной памяти, но не более 1 ГБ.

effective_cache_size предоставляет оценку объема памяти, доступной для кэширования на диске операционной системой и самой базой данных. Параметр используется планировщиком запросов PostgreSQL для определения, могут ли рассматриваемые планы запросов поместиться в RAM. Рекомендуется устанавливать его значение равным примерно 50% от общего объема оперативной памяти на сервере.

Для того чтобы применить эти настройки, необходимо изменить файл postgresql.conf, находящийся в директории данных PostgreSQL $PGDATA. Например:

shared_buffers = '8GB'
work_mem = '64MB'
maintenance_work_mem = '1GB'
effective_cache_size = '16GB'

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

Однако стоит помнить, что эти значения являются лишь отправной точкой. Для достижения максимальной производительности необходимо провести бенчмаркинг конкретной рабочей нагрузки и скорректировать настройки в соответствии с полученными результатами. Полезно будет использовать инструменты, такие как pgTune, для получения начальных рекомендаций по настройке, и pgBadger.

Настройка WAL и autovacuum

WAL сохраняет изменения БД в специальном журнале перед их фиксацией в БД. Это позволяет восстановить базу данных после сбоев, не потеряв важные данные.

max_wal_size и min_wal_size: параметры контролируют максимальный и минимальный размеры журнала. Увеличение max_wal_size может уменьшить количество контрольных точек и, как следствие, снизить задержку при записи, но увеличивает время восстановления после сбоя. Рекомендуется найти баланс, обеспечивающий достаточный размер WAL для рабочей нагрузки, сохраняя при этом приемлемое время восстановления.

checkpoint_completion_target: параметр задаёт цель завершения контрольной точки как долю от общего времени между контрольными точками. Настройка ближе к 1.0 распределяет I/O нагрузку более равномерно, снижая вероятность внезапных скачков задержек.

Автоочистка (autovacuum) создана для удаления мертвых кортежей. Неоптимизированная автоочистка может привести к накоплению мусора и ухудшению производительности.

autovacuum_vacuum_scale_factor и autovacuum_vacuum_threshold: параметры определяют, насколько часто должна выполняться автоочистка для каждой таблицы. Значения по умолчанию обычно консервативны, и их регулировка может помочь ускорить удаление мертвых кортежей, особенно на активных БД.

autovacuum_max_workers: увеличение количества рабочих процессов автоочистки может ускорить процесс очистки, особенно на системах с большим количеством ядер.

Пример настройки WAL и autovacuum:

-- WAL Configuration
max_wal_size = '2GB'
min_wal_size = '1GB'
checkpoint_completion_target = 0.9

-- Autovacuum Configuration
autovacuum_vacuum_scale_factor = 0.02
autovacuum_vacuum_threshold = 50
autovacuum_max_workers = 5

Управление дисковыми операциями

PostgreSQL использует временные файлы для различных операций, таких как сортировка или хеширование, когда не хватает оперативной памяти. Параметр temp_file_limit ограничивает максимальный размер всех временных файлов, которые может использовать один процесс. Если этот предел превышен, процесс будет прерван. Например, установка temp_file_limit = '20GB' позволит ограничить использование дискового пространства до 20 ГБ для временных файлов, что поможет избежать засорения диска при выполнении операций, требующих большого объема временного хранения.

effective_io_concurrency позволяет PostgreSQL оценить, сколько операций ввода-вывода может быть выполнено параллельно. Для SSD рекомендуется значение около 200, в то время как для HDD более подходящими будут нижние значения, например, 2 для effective_io_concurrency, учитывая, что HDD хуже справляются с параллельными операциями чтения из-за механических ограничений.

Пример настройки:

-- Ограничение использования временных файлов
temp_file_limit = '20GB';

-- Оптимизация для SSD
effective_io_concurrency = 200;

-- Для HDD
effective_io_concurrency = 2;

Прочая информация

Конфигурация на основе типов нагрузки

Для OLTP систем, где производительность зависит от скорости обработки транзакций, рекомендуется использование процессоров с большим количеством быстрых ядер и большим кэшем L3.

Для OLAP систем, ориентированных на аналитическую обработку больших объемов данных, важно иметь достаточное количество оперативной памяти и быстрые накопители (например, SSD или NVMe), поскольку это напрямую влияет на скорость чтения данных и выполнения сложных агрегационных запросов.

Один из инструментов, упрощающих настройку конфигурации PostgreSQL, — pgTune. Этот инструмент анализирует характеристики вашего оборудования и предлагает оптимизированные настройки конфигурации для вашего случая использования, будь то OLTP или OLAP нагрузка. PgTune позволяет настроить такие параметры, как объем используемой памяти, параметры сортировки и обслуживания, а также настройки, связанные с сохранением данных и их восстановлением после сбоев.

Для улучшения производительности OLAP-нагрузок, можно использовать Foreign Data Wrappers, например, clickhousedb_fdw для интеграции PostgreSQL с ClickHouse — колоночной СУБД, оптимизированной для аналитических запросов. Такое решение позволяет ускорить выполнение OLAP-запросов, перенаправляя их на ClickHouse, где они выполняются значительно быстрее, чем в самом PostgreSQL. Это создает гетерогенную среду, в которой каждая система управления базами данных выполняет те задачи, для которых она оптимизирована, тем самым повышая общую производительность системы.

Важные метрики мониторинга

• Активные сессии: максимальное количество активных сессий должно быть настроено через параметр max_connections. Рекомендуется, чтобы активные соединения не превышали 90% от max_connections.

• Логи: мониторинг логов позволяет идентифицировать ошибки, такие как долгие запросы, проблемы с аутентификацией, взаимоблокировки или какие либо фатальные ошибки.

• Производительность запросов: отслеживание запросов дает возможность оптимизировать базу данных и улучшить её производительность​.

• Задержки репликации: мониторинг состояния репликации помогает обеспечить высокую доступность и согласованность данных между узлами.

• Коэффициент попаданий в кэш: идеально, когда как минимум 99% чтений данных происходит из кэша​.

• Соотношение индексных сканирований к общему числу сканирований: желательно, чтобы это соотношение было выше 99% для оптимизированных БД.

• Частота взаимоблокировок: отслеживание взаимоблокировок помогает предотвратить доп. нагрузку на ресурсы ОС и задержки в будущем​.

Инструменты для мониторинга

• pg_stat_statements: отслеживает статистику планирования и выполнения всех SQL-запросов, выполняемых сервером базы данных​.

• pgBuffercache: дает представление о том, какие страницы каких объектов БД в данный момент находятся в общем кэше​.

• pgBadger: анализатор логов PostgreSQL, предоставляющий подробные отчеты​.

• Prometheus с PostgreSQL Exporter: интегрируется с PostgreSQL Exporter для извлечения метрик базы данных, таких как запросы в секунду, кол-во обработанных строк в секунду, блокировки базы данных, активные сессии, репликации и т. д​.

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

Также приглашаю вас на бесплатный вебинар курса Системный аналитик. Advanced

• Зарегистрироваться на бесплатный вебинар