Оптимизация производительности PostgreSQL: настраиваем фундамент

Правильная работа с БД начинается не с индексов, а с подготовки «фундамента» — операционной системы и базовых параметров конфигурации.

Грамотное администрирование включает регулярную настройку параметров конфигурации postgresql.conf и контроль состояния системы.

Использование Huge Pages в Linux позволяет снизить нагрузку на процессор (CPU overhead) и предотвратить попадание критически важной памяти в swap.

По умолчанию Linux использует страницы размером 4 КБ. Стандартный инстанс PostgreSQL может выделить много гигабайт памяти и таблица страниц становится огромной, что замедляет работу TLB (Translation Lookaside Buffer). Huge Pages увеличивает размер страницы до 2 МБ, сокращая размер этой таблицы. Кроме того, эти страницы будут фрагментированы, поэтому их использование для больших наборов данных потребует дополнительного времени для сопоставления, что в конечном счёте приведет к проблемам с производительностью даже при таком небольшом размере страницы

Включение huge pages в Linux повысит производительность PostgreSQL, так как он будет выделять большие блоки памяти вместе. К тому же блоки  huge pages не вытесняются в swap area. Есть два аспекта настройки — конфигурация операционной системы и конфигурация PostgreSQL.

PostgreSQL использует Huge Pages в основном для shared_buffers. Чтобы узнать, сколько страниц нужно выделить, проще всего спросить сам Postgres.

Перед тем как прописывать настройки "навечно", проверьте их в рантайме. Например, если вам нужно 5000 страниц:

Чтобы настройки сохранялись после перезагрузки:

Теперь нужно сказать Postgres, чтобы он обязательно использовал эти страницы.

После перезапуска выполните команду:

shared_buffers
Этот параметр имеет наибольшую дисперсию из всех. Некоторые рабочие нагрузки лучше всего работают с маленькими значениями (например, 1 ГБ или 2 ГБ) даже с внушительными объёмами базы данных. Другие рабочие нагрузки требуют больших значений. Оптимально — LEAST(RAM/2, 10GB).

Для формулы нет конкретной причины, кроме многолетнего коллективного опыта сообщества PostgreSQL. Существуют сложные взаимодействия между кэшем ядра и shared_buffers, которые делают практически невозможным точное описание того, почему эта формула обычно даёт хорошие результаты.

work_mem
Рекомендуемое значение для work_mem — ((Total RAM - shared_buffers)/(16 x CPU cores)). Логика формулы заключается в том, что если у вас так много запросов, что вы рискуете исчерпать память, вы автоматически привязаны к процессору. 

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

maintenance_work_mem
Определяет максимальный объём памяти, который используется для операций обслуживания (VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY) и операций загрузки данных. Может увеличивать объём операций ввода-вывода на серверах баз данных, поэтому выделение большего объёма памяти приводит к более быстрому завершению операций. Значение 1 ГБ — хорошее начало, поскольку это команды, которые явно выполняются администратором баз данных.

max_connections
Оптимальное число для max_connections примерно в 4 раза превышает количество ядер CPU. Формула часто даёт небольшое число, которое не оставляет места для ошибки. Рекомендуемое число — GREATEST(4 x CPU cores, 100). Помимо этого следует использовать средство объединения подключений, например pgbouncer.

Важно избегать установки слишком большого значения max_connections, так как это увеличит размер структур данных в Postgres, что может привести к потере циклов CPU. И наоборот: необходимо убедиться, что выделено достаточно ресурсов для поддержки требуемой рабочей нагрузки.

effective_io_concurrency
Параметр используется для предварительного чтения во время определённых операций и должен быть установлен на количество дисков, задействованных для хранения данных. Изначально он предназначался, чтобы помочь Postgres понять, сколько чтений может происходить параллельно при использовании чередующихся RAID-массивов, но позже улучшения были отмечены использовании кратного этого числа. Вероятно, потому что RAID-адаптеры хорошего качества могут переупорядочивать и направлять запросы для повышения эффективности.

Упреждающая журнализация

wal_compression
Когда параметр включен, сервер PostgreSQL сжимает полностраничный образ, записываемый в WAL, при включённом параметре full_page_writes или во время базового резервного копирования. Установите для wal_compression значение «on», так как большинство серверов баз данных, скорее всего, будут ограничены вводом-выводом, а не процессором.

wal_log_hints
Параметр необходим для использования pg_rewind. Включите его.  

wal_buffers
Определяет объём пространства, доступного серверным частям для записи данных WAL в память, чтобы затем WALWriter мог записать их в журнал WAL на диске в фоновом режиме. Сегменты WAL по умолчанию имеют размер 16 МБ каждый, поэтому буферизация сегмента обходится требует немного памяти. Отмечается, что большие размеры буфера потенциально оказывают положительное влияние на производительность при тестировании. Установите для этого параметра значение 64 МБ.

checkpoint_timeout
Более длительные тайм-ауты уменьшают общий объём WAL, но увеличивают время восстановления после сбоя. Рекомендуемое значение — не менее 15 минут, но, в конечном счёте, бизнес-требования определяют, каким оно должно быть.

checkpoint_completion_target
Определяет количество времени, за которое PostgreSQL стремится выполнить контрольную точку. Контрольная точка не обязательно должна приводить к скачку ввода-вывода. Вместо этого она нацелена на распределение операций записи по части значения checkpoint_timeout. Рекомендуемое значение — 0.9.

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

archive_mode
Для изменения этого параметра требуется перезагрузка, поэтому для него следует установить значение «on», если только вы не уверены, что никогда не будете использовать архивирование WAL.

archive_command
Если включен archive_mode, требуется команда archive_command. Пока архивирование не будет готово к настройке, предлагается значение по умолчанию 'to be configured'.

Примитив ':' сообщает Postgres, что сегмент WAL может быть переработан или удалён. 

to be configured — это набор аргументов, которые будут проигнорированы.

Если штатные средства оптимизации исчерпаны, а база продолжает "тормозить", необходим внешний аудит. Экспертное вмешательство требуется при планировании архитектуры High Availability, сложной миграции данных или переходе на новые версии СУБД.