Конфигурация сервера: ключевые параметры

[Egor_DBA]

Урок 39. Конфигурация сервера: ключевые параметры

Свежеустановленный PostgreSQL запускается с очень осторожными настройками по умолчанию: он рассчитан на то, чтобы стартовать даже на слабой машине. На реальном сервере с десятками гигабайт памяти эти дефолты оставляют половину ресурсов простаивать, а запросы тормозят без видимой причины. В этом уроке разберём, какие параметры в postgresql.conf реально влияют на скорость, как сервер делит память между буферами и сортировками, как работают WAL и контрольные точки, зачем нужен autovacuum и как менять всё это без ручного редактирования файла через ALTER SYSTEM и pg_reload_conf. По сути это базовый тюнинг postgresql, который стоит сделать сразу после установки, в том числе под нагрузку вроде postgresql для 1с.



Как это работает

Конфигурация живёт в текстовом файле postgresql.conf. При старте сервер читает его построчно, а затем накладывает сверху файл postgresql.auto.conf, который пишет команда ALTER SYSTEM. Поэтому значение из auto.conf всегда побеждает то, что вы руками вписали в основной файл - это частый источник путаницы.

Параметры делятся на категории по тому, когда они вступают в силу. Часть применяется мгновенно после перечитывания конфига (sighup), часть требует переподключения сессии (user), а самые тяжёлые, вроде размера общих буферов, просят полный перезапуск (postmaster). Узнать категорию можно в системном представлении pg_settings в колонке context.

Память сервер делит на две принципиально разные части. Общая память (shared_buffers) выделяется один раз на весь экземпляр и служит кешем страниц таблиц и индексов: данные сначала читаются с диска сюда, и пока страница тут, повторное обращение идёт без обращения к диску. Локальная память выделяется каждой операции сортировки или хеширования отдельно через work_mem - и вот тут кроется ловушка масштабирования, о которой ниже.

Параметр effective_cache_size память не выделяет вообще. Это подсказка планировщику: сколько примерно гигабайт суммарно кешируют PostgreSQL и операционная система. Чем больше это число, тем охотнее планировщик выбирает индексное чтение вместо полного перебора, потому что верит, что нужные страницы уже в кеше. Похожую роль играет random_page_cost - оценка стоимости случайного чтения относительно последовательного. На SSD случайное чтение почти так же дёшево, как последовательное, поэтому дефолтные 4.0 завышены и мешают использовать индексы.

Запись на диск устроена через журнал WAL. Любое изменение сначала пишется в журнал, и только потом - когда-нибудь - грязные страницы из буферов попадают в файлы данных. Это сбрасывание называется контрольной точкой (checkpoint). Если контрольные точки случаются слишком часто, диск захлёбывается от записи; если редко - восстановление после сбоя затягивается, а сами точки бьют по диску пиками.

Наконец, autovacuum - фоновый процесс, который убирает мёртвые версии строк, оставшиеся после UPDATE и DELETE (это про MVCC и vacuum), и обновляет статистику для планировщика. Без него таблицы пухнут, а планы деградируют.

SQL и примеры

Посмотреть текущее значение и категорию параметра:

Код: Выделить всё

SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','random_page_cost');

Колонка context подскажет, понадобится ли перезапуск. Удобная команда в psql - SHOW shared_buffers; для одного значения.

Базовый набор правок через ALTER SYSTEM - он пишет в postgresql.auto.conf, файл руками трогать не надо:

Код: Выделить всё

ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET max_connections = 100;

Здравые ориентиры для сервера с 16 ГБ ОЗУ: shared_buffers около 25 процентов памяти, effective_cache_size около 60-75 процентов, work_mem поднимаем умеренно, maintenance_work_mem делаем щедрым, потому что от него зависит скорость VACUUM и построения индексов, random_page_cost опускаем до 1.1 для SSD.

Параметры sighup-категории применяются без перезапуска:

Код: Выделить всё

SELECT pg_reload_conf();

Но shared_buffers и max_connections относятся к postmaster - их подхватит только рестарт сервиса.

Настройка контрольных точек, чтобы запись размазалась во времени, а не шла пиками:

Код: Выделить всё

ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

Проверим, что effective_cache_size действительно толкает планировщик к индексам. На демобазе Авиаперевозки посчитаем число посадочных талонов на конкретный рейс - тут хочется именно индексное чтение по boarding_passes, а не полный перебор:

Код: Выделить всё

EXPLAIN ANALYZE
SELECT count(*)
FROM boarding_passes
WHERE flight_id = 12345;

Если в плане вы видите Seq Scan на большой таблице там, где есть подходящий индекс, попробуйте снизить random_page_cost и перезапустить explain analyze - часто план меняется на Index Scan.

Полезно подсмотреть, как autovacuum относится к самой нагруженной таблице бронирований:

Код: Выделить всё

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'ticket_flights';

Если n_dead_tup сопоставимо с n_live_tup, autovacuum не успевает - стоит сделать его агрессивнее.

Частые грабли

• Правка в postgresql.conf не сработала, потому что то же значение раньше выставили через ALTER SYSTEM - auto.conf перекрывает основной файл. Сбросить можно ALTER SYSTEM RESET имя_параметра.
• work_mem умножается на число операций сортировки и хеша во всех активных запросах. 256MB при 100 соединениях с несколькими сортировками в каждом - это уже десятки гигабайт и риск ухода в своп. Считайте work_mem на операцию, а не на сервер.
• Поставили shared_buffers и удивляетесь, что не применилось: это postmaster-параметр, нужен полный перезапуск, а не pg_reload_conf.
• Задрали max_connections до тысяч вместо пула соединений. Каждый бэкенд - отдельный процесс со своей памятью; для многих коротких соединений нужен PgBouncer, а не огромный max_connections.
• random_page_cost оставили дефолтным 4.0 на SSD - планировщик избегает индексов и валится в полный перебор.
• Отключили autovacuum, чтобы не мешал нагрузке. Через сутки таблицы распухли, а планы развалились из-за устаревшей статистики. Autovacuum не выключают - его настраивают.
• effective_cache_size приняли за выделение памяти и испугались большого числа. Это только подсказка планировщику, ни байта он не резервирует.

Мини-лаба

• Подключитесь psql и выполните SHOW shared_buffers; SHOW work_mem; SHOW random_page_cost; - запишите дефолты.
• Через SELECT по pg_settings найдите колонку context для shared_buffers и work_mem, отметьте, какому нужен перезапуск.
• Выполните ALTER SYSTEM SET work_mem = '16MB'; и ALTER SYSTEM SET random_page_cost = 1.1; затем SELECT pg_reload_conf();
• Проверьте новые значения через SHOW - убедитесь, что work_mem применился без рестарта.
• На демобазе bookings запустите EXPLAIN ANALYZE для выборки из ticket_flights по одному flight_id и зафиксируйте узел плана (Seq Scan или Index Scan).
• Выполните ALTER SYSTEM SET shared_buffers = '256MB'; pg_reload_conf и убедитесь, что значение НЕ изменилось без перезапуска - объясните почему.
• Откатите эксперимент: ALTER SYSTEM RESET ALL; и перечитайте конфиг.

Контрольные вопросы

• Чем shared_buffers принципиально отличается от work_mem по способу выделения памяти?
• Почему большое значение work_mem опаснее большого shared_buffers при многих соединениях?
• Что произойдёт, если задать параметр и в postgresql.conf, и через ALTER SYSTEM с разными значениями?
• Зачем планировщику effective_cache_size, если этот параметр не выделяет ни байта памяти?
• Каким параметром снижают random_page_cost на SSD и к чему это приводит в планах запросов?
• Чем грозит частые контрольные точки и как их размазать во времени?

[kotlindev]

А есть простое правило для work_mem? У меня 32 ГБ ОЗУ и max_connections 200, боюсь словить своп если подниму выше дефолта.

[ceph2026]

Поставил random_page_cost 1.1 на SSD и тот же запрос по ticket_flights в explain analyze переключился с Seq Scan на Index Scan, время упало в разы. Реально работает.