Настройка параметров postgresql.conf для оптимальной производительности чтения и записи

PostgreSQL обычно работает приемлемо с настройками по умолчанию, но «приемлемо» может превратиться в медленное чтение, скачкообразную запись или случайные задержки, когда появляется реальный трафик. Файл postgresql.conf — это место, где вы задаете базовый бюджет ресурсов: сколько памяти PostgreSQL может использовать для общего кэша, сколько каждая операция запроса может использовать до сброса на диск, насколько агрессивно контрольные точки записывают грязные страницы и какие подсказки получает планировщик о лежащей в основе машине.

Ошибка, которую я вижу чаще всего, — это отношение к настройке PostgreSQL как к списку магических чисел. Кто-то копирует shared_buffers = 25% от ОЗУ, устанавливает большое значение work_mem, удваивает max_connections и надеется, что база данных станет быстрее. Иногда это срабатывает. Иногда она начинает свопиться во время выполнения отчета или упирается в стену во время контрольных точек.

Более безопасный способ — настраивать, исходя из симптомов. Чтение медленное, потому что рабочий набор не кэшируется? Отчеты сбрасывают сортировки на диск? Записи группируются во время контрольных точек? Слишком много подключений приложений конкурируют за память? Это руководство описывает параметры, которые обычно имеют значение в первую очередь, с примерами, которые вы можете адаптировать, а не слепо копировать.

Понимание основных параметров памяти

Эффективное управление памятью имеет первостепенное значение для высокопроизводительных систем баз данных. PostgreSQL использует различные области памяти, две из которых наиболее критичны: shared_buffers для кэширования часто используемых данных и work_mem для внутренних операций запросов.

shared_buffers

shared_buffers — это, пожалуй, один из самых важных параметров памяти для настройки. Он определяет объем выделенной памяти, которую PostgreSQL использует для кэширования блоков данных. Эти блоки включают данные таблиц, данные индексов и системные каталоги. Когда запрос запрашивает данные, PostgreSQL сначала проверяет shared_buffers. Если данные найдены там (попадание в кэш), они извлекаются намного быстрее, чем если бы их пришлось читать с диска.

Влияние на производительность

• Производительность чтения: Большее значение shared_buffers увеличивает вероятность попаданий в кэш, значительно сокращая дисковый ввод-вывод для рабочих нагрузок с интенсивным чтением. Это приводит к более быстрым ответам на запросы.
• Производительность записи: shared_buffers также содержит «грязные» страницы (блоки данных, которые были изменены, но еще не записаны на диск). Больший буфер может поглотить больше операций записи, позволяя системе группировать их в меньшее количество более крупных операций записи на диск, улучшая пропускную способность записи. Однако, если он слишком велик, это может привести к увеличению времени контрольных точек и всплескам ввода-вывода во время контрольных точек.

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

• Отправная точка: Распространенная рекомендация — установить shared_buffers на 25% от общего объема физической ОЗУ. Например, на сервере с 16 ГБ ОЗУ shared_buffers будет 4 ГБ.
• Системы с большим объемом ОЗУ: На серверах с 64+ ГБ ОЗУ выделение 25% может быть чрезмерным. PostgreSQL также полагается на кэш файловой системы операционной системы. После определенного момента увеличение shared_buffers может дать уменьшающуюся отдачу, поскольку кэш ОС может эффективно обрабатывать большую часть оставшегося кэширования. В таких случаях может быть достаточно 15-20%, оставляя больше ОЗУ для кэша ОС или work_mem.
• Мониторинг: Следите за коэффициентом попадания в кэш в pg_stat_database, но не воспринимайте один процент как доказательство того, что все в порядке. Высокий коэффициент попадания может скрывать несколько очень дорогих запросов, а более низкий коэффициент может быть нормальным для пакетных заданий, которые один раз сканируют большие таблицы. Также отслеживайте поведение контрольных точек и задержки диска.

Пример конфигурации

Чтобы установить shared_buffers в 4 ГБ в postgresql.conf:

shared_buffers = 4GB

Совет: После изменения shared_buffers необходимо перезапустить службу PostgreSQL, чтобы изменения вступили в силу.

Практическая проверка после его изменения:

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

Если вы увеличили shared_buffers, а приложение все еще ждет чтения с диска, проблема может быть в форме запроса, отсутствующих индексах, раздувании таблицы или рабочем наборе, превышающем объем памяти. Больший кэш не заменяет лучший план выполнения.

work_mem

work_mem определяет максимальный объем памяти, который будет использоваться операцией запроса (например, сортировкой или хеш-таблицей) до записи временных данных на диск. Эта память выделяется на сессию, на операцию. Если сложный запрос включает несколько операций сортировки или хеширования, он потенциально может потреблять work_mem несколько раз в рамках одной сессии.

Влияние на производительность

• Сложные запросы: work_mem существенно влияет на запросы, включающие ORDER BY, GROUP BY, DISTINCT, хеш-соединения и материализацию. Когда операция сортировки или хеширования превышает лимит work_mem, PostgreSQL сбрасывает избыточные данные во временные файлы на диске, что приводит к гораздо более медленному выполнению.
• Параллелизм: Поскольку work_mem выделяется на операцию, на сессию, высокое глобальное значение work_mem в сочетании с большим количеством одновременных сложных запросов может быстро исчерпать доступную ОЗУ, что приведет к свопингу и серьезной деградации производительности.

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

• Избегайте чрезмерных глобальных значений: Не устанавливайте слепо work_mem на очень большое значение глобально. Вместо этого учитывайте типичный параллелизм вашего приложения и объем памяти, потребляемый наиболее ресурсоемкими запросами.
• Мониторинг сбросов на диск: Используйте EXPLAIN ANALYZE для проблемных запросов. Ищите строки типа Sort Method: external merge Disk: NkB или HashAggregate batches: N (disk), которые указывают на то, что work_mem было недостаточно и данные были сброшены на диск.
• Целевая настройка: Для конкретных длительных отчетов или пакетных заданий рассмотрите возможность установки work_mem на уровне сессии перед выполнением запроса, а не глобально. Это позволяет использовать больше памяти для этого конкретного запроса, не влияя на другие одновременные сессии.

Пример конфигурации

Чтобы установить work_mem в 16 МБ глобально в postgresql.conf:

work_mem = 16MB

Чтобы установить work_mem для конкретной сессии (например, в psql или подключении приложения):

SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY some_column;

Предупреждение: Будьте осторожны при увеличении work_mem. Если 100 одновременных запросов требуют по 1 ГБ work_mem каждый, это 100 ГБ ОЗУ! Всегда тестируйте изменения в промежуточной среде и отслеживайте использование памяти вашей системой.

Более реалистичный способ использования work_mem — оставить глобальное значение скромным, а затем повышать его только для известных сессий отчетов:

BEGIN;
SET LOCAL work_mem = '256MB';

SELECT customer_id, sum(total_amount)
FROM orders
WHERE created_at >= current_date - interval '90 days'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;

COMMIT;

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

Управление производительностью записи и надежностью с помощью контрольных точек

Контрольные точки — это критически важный механизм в PostgreSQL для обеспечения надежности данных и управления журналом транзакций (WAL — Write-Ahead Log). Они периодически синхронизируют измененные блоки данных из shared_buffers на диск, отмечая точку, до которой все предыдущие изменения были записаны в постоянное хранилище.

checkpoint_timeout

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

Влияние на производительность

• Частые контрольные точки (короткий checkpoint_timeout): Приводит к более частым всплескам ввода-вывода, поскольку грязные страницы сбрасываются на диск. Хотя это сокращает время восстановления после сбоя (меньше WAL для воспроизведения), это может негативно сказаться на производительности активной рабочей нагрузки из-за концентрированной активности записи.
• Редкие контрольные точки (длинный checkpoint_timeout): Уменьшает частоту всплесков ввода-вывода, что приводит к более плавной производительности во время нормальной работы. Однако это означает, что в случае сбоя может потребоваться воспроизвести больше данных из WAL, что приведет к увеличению времени восстановления базы данных. Это также требует большего max_wal_size для хранения накопленных сегментов WAL.

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

• Баланс: Цель состоит в том, чтобы найти баланс между плавной текущей производительностью и приемлемым временем восстановления. Многие производственные системы начинают с 5-15 минут, а затем корректируются в зависимости от объема WAL и целей восстановления.
• Взаимодействие с max_wal_size: Эти два параметра работают вместе. Если checkpoint_timeout длинный, но max_wal_size слишком мал, контрольные точки будут запускаться max_wal_size чаще, чем checkpoint_timeout. Настройте max_wal_size так, чтобы он был достаточно большим, чтобы checkpoint_timeout был основным триггером.
• Мониторинг: Используйте pg_stat_bgwriter для наблюдения за счетчиками checkpoints_timed и checkpoints_req. checkpoints_timed должно быть значительно выше, чем checkpoints_req (контрольные точки, запрошенные из-за ограничений размера WAL), если ваш checkpoint_timeout является основным триггером.

Пример конфигурации

Чтобы установить checkpoint_timeout в 10 минут в postgresql.conf:

checkpoint_timeout = 10min
# Также рассмотрите возможность соответствующей настройки max_wal_size
max_wal_size = 4GB # Пример, настройте в зависимости от рабочей нагрузки

Лучшая практика: Стремитесь к тому, чтобы контрольные точки в первую очередь запускались checkpoint_timeout, а не max_wal_size. Это обеспечивает более предсказуемые шаблоны ввода-вывода. Если max_wal_size часто запускает контрольные точки, увеличьте его значение.

Проверьте шаблон с помощью:

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

Если checkpoints_req быстро растет, PostgreSQL выполняет контрольные точки, потому что WAL вырос за пределы max_wal_size, а не потому, что истек таймер. Это часто проявляется в виде всплесков ввода-вывода записи. Увеличение max_wal_size может сгладить рабочую нагрузку, но также может увеличить время восстановления после сбоя, поскольку может потребоваться воспроизвести больше WAL.

Настройки планировщика и WAL, которые стоит проверить

Три настройки часто находятся рядом с основными параметрами памяти и контрольных точек.

effective_cache_size — это не память, выделяемая PostgreSQL. Это оценка планировщика того, сколько кэша, вероятно, доступно в общих буферах PostgreSQL и кэше файловой системы операционной системы. Если он установлен слишком низким, планировщик может избегать сканирования индексов, предполагая, что чтение будет дорогим. На выделенном сервере базы данных распространенной отправной точкой является большая часть ОЗУ, но правильное значение зависит от того, что еще работает на хосте.

effective_cache_size = 12GB

maintenance_work_mem влияет на операции обслуживания, такие как CREATE INDEX, ALTER TABLE ADD FOREIGN KEY и VACUUM. Он не используется для обычных сортировок запросов так же, как work_mem. Если построение индексов мучительно медленно во время окон обслуживания, повышение этого значения для сессии может помочь:

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

wal_buffers управляет памятью, используемой для записей WAL перед их записью. Значение по умолчанию обычно подходит, поскольку PostgreSQL может автоматически изменять его размер, но рабочие нагрузки с интенсивной записью и большими транзакциями могут выиграть от проверки того, является ли запись WAL узким местом, прежде чем изменять его. Не настраивайте его только потому, что он появляется в контрольном списке.

Разные отправные точки для разных рабочих нагрузок

Для веб-приложения OLTP приоритетом является стабильная задержка при параллелизме. Держите work_mem консервативным, используйте пулер соединений вместо того, чтобы разрешать тысячи прямых подключений, и следите за ожиданиями блокировок и плохими планами, прежде чем винить shared_buffers. Типичная проблема выглядит так: в релизе добавляется запрос панели мониторинга с ORDER BY created_at DESC по миллионам строк, запрос сбрасывается на диск, и внезапно каждый запрос становится медленнее, потому что база данных занимается вводом-выводом временных файлов. Исправлением может быть индекс или более узкий запрос, а не больший глобальный work_mem.

Для базы данных аналитики или отчетности большие сортировки и хеш-агрегаты являются нормой. Вы можете повысить work_mem для ролей отчетности, увеличить maintenance_work_mem для массовой работы с индексами и смириться с более длительными запросами. Риск заключается в параллелизме. Десять аналитиков, одновременно выполняющих ресурсоемкие запросы отчетов, могут потреблять гораздо больше памяти, чем предполагал один успешный тестовый запрос.

Для системы с интенсивной записью контрольные точки и WAL имеют большее значение. Если в приложении наблюдаются периодические остановки записи, проверьте, совпадают ли они с контрольными точками. Также посмотрите на задержку хранилища, насыщение диска WAL, активность autovacuum и то, не препятствуют ли длительные транзакции очистке. Увеличение checkpoint_timeout само по себе не исправит диск, который не успевает за средним объемом записи.

Простой рабочий процесс настройки

Начните с записи текущей конфигурации:

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'checkpoint_timeout',
    'max_wal_size',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

Затем зафиксируйте симптомы, прежде чем что-либо менять. Сохраните один или два плана медленных запросов с помощью EXPLAIN (ANALYZE, BUFFERS). Проверьте журналирование временных файлов, если подозреваете сбросы:

log_temp_files = 0

Эта настройка регистрирует каждый временный файл, поэтому используйте ее осторожно в загруженной системе или установите порог, например 64MB. Если вы видите много больших временных файлов от одной и той же формы запроса, настройте запрос, добавьте индекс или повысьте work_mem для этой рабочей нагрузки.

Меняйте по одной вещи за раз. Некоторые настройки требуют перезапуска, некоторым нужна только перезагрузка, а некоторые можно установить для каждой сессии. PostgreSQL сообщает вам, что есть что:

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout', 'max_wal_size');

Контекст postmaster означает перезапуск. sighup означает перезагрузку. user означает, что возможны изменения на уровне сессии.

Общие советы по настройке и лучшие практики

• Итеративная настройка: Начните с небольших, постепенных изменений. Меняйте по одному параметру за раз, наблюдайте за эффектом, а затем при необходимости корректируйте дальше. Настройка — это не разовая задача, а непрерывный процесс.
• Мониторинг всего: Используйте встроенные представления статистики PostgreSQL (pg_stat_database, pg_stat_bgwriter, pg_stat_activity), инструменты мониторинга на уровне ОС (например, iostat, vmstat, top) и внешние решения для мониторинга для сбора данных о ЦП, памяти, дисковом вводе-выводе и производительности запросов.
• Понимание вашей рабочей нагрузки: Ваше приложение интенсивно читает или пишет? Выполняет ли оно сложные аналитические запросы или простые транзакционные операции? Адаптируйте свою конфигурацию к характеристикам вашей конкретной рабочей нагрузки.
• Учитывайте другие параметры: Хотя shared_buffers, work_mem и checkpoint_timeout имеют решающее значение, многие другие параметры могут влиять на производительность. Например, effective_cache_size (подсказки планировщику запросов о доступном кэше ОС) и wal_buffers (память для записей WAL перед сбросом) часто настраиваются вместе с этими.
• Используйте EXPLAIN ANALYZE: Этот бесценный инструмент помогает вам понять, как PostgreSQL выполняет запрос, выявляет узкие места и может показать, достаточно ли work_mem.

Лучшая работа по настройке PostgreSQL скучна в хорошем смысле: измерьте, измените одну настройку, измерьте снова и сохраните путь отката. shared_buffers, work_mem и настройки контрольных точек могут иметь реальное значение, но они работают вместе с планами запросов, индексами, autovacuum, количеством подключений и хранилищем. Если эти части нездоровы, одна только конфигурация не спасет базу данных.