Postgres производственная настройка, сокращающая задержку

postgresql.confв состоянии по умолчанию предназначен для того, чтобы установка никогда не прерывалась, а не поглощала ваш трафик. Историческая ценность shared_buffers, 128 МБ, позволяет Postgres запускаться на минимальной машине без резервирования каких-либо критических ресурсов. max_connections на уровне 100 подходит для небольшого общего сервера. Ни один из них не был выбран для вашей фактической нагрузки.

Так что проблема не в том, что Postgres по умолчанию плохо настроен: а в том, что он у вас никогда не устанавливался. В следующих разделах настройки рассматриваются в том порядке, в котором они наиболее выгодны: от наиболее распространенных узких мест (соединения) до самых медленных (WAL).

Первый проект – это не память, это связи. Каждое соединение Postgres открывает выделенный серверный процесс, который потребляет ОЗУ и контекстное время ЦП, даже если он простаивает. Увеличение max_connections во избежание ошибок типа «слишком много подключений» меняет проблему: за пределами определенного количества одновременных активных подключений конкуренция за ЦП ухудшает задержку всех запросов, включая самые быстрые.

Правильный подход меняет обычный порядок: масштабируйте max_connections на фактической параллельной работе сервера, затем поглощайте параллельную обработку на стороне приложения с помощью пулера, такого как PgBouncer, в pool_mode=transaction. Создатель пула мультиплексирует сотни клиентских соединений на несколько действительно активных серверных соединений. В нашей специальной статье подробно описывается механизм режима транзакций и его ограничения (подготовленные операторы, LISTEN/NOTIFY), а также сравнение PgBouncer, Supavisor и PgCat для выбора реализации.

max_connections — это параметр контекста «postmaster»: его изменение требует полного перезапуска сервера, а не простой перезагрузки. В выделенных экземплярах Postgres Aurabase (планы Pro и Enterprise, один кластер CNPG на проект) этот параметр настраивается для каждого уровня плана, а не остается со значением по умолчанию. Перезапуск — это не тривиальная операция, которую нужно повторить в рабочей среде, что оправдывает этот выбор поэтапно, а не фиксированным значением. Формула выбора собственного значения с его пределами — тема отдельной статьи: size max_connections.

Четыре настройки памяти весят больше, чем все остальные вместе взятые: shared_buffers, effective_cache_size, work_mem и maintenance_work_mem. Первые три определяют, сколько данных Postgres сохраняет в памяти перед возвратом на диск; четвертый определяет скорость создания ВАКУУМА или индекса.

shared_buffers устанавливает внутренний кеш, общий для всех соединений. Тестовый показатель, обычно документируемый проектом PostgreSQL, составляет примерно 25% оперативной памяти, доступной на выделенном сервере базы данных. Помимо этого, выигрыш снижается, и управление берет на себя дисковый кеш операционной системы. effective_cache_size ничего не выделяет: это оценка, данная планировщику запросов, общего объема памяти, доступной для кэша (вместе Postgres и ОС). Уменьшение его размера подталкивает планировщик к последовательному сканированию, тогда как индекс в основном будет кэшироваться; текущий тест составляет от 50 до 75% оперативной памяти.

work_mem — самая распространенная ловушка. Это не глобальный предел. Каждая операция сортировки или хеширования в запросе может использовать свою долю, а запрос с несколькими соединениями может резервировать свою долю несколько раз. Слишком большое значение в сочетании с высоким значением max_connections может истощить оперативную память сервера при одновременной нагрузке, даже если каждый запрос, рассматриваемый изолированно, кажется разумным. maintenance_work_mem, наоборот, может оставаться значительно более щедрым: он применяется только к операциям обслуживания (VACUUM, CREATE INDEX), которые редко совпадают друг с другом.

Только shared_buffers требует перезагрузки. Остальные три перезагружаются в горячем режиме, в том числе для изолированного сеанса: SET work_mem = '64MB'; на время одного жадного запроса, не затрагивая глобальные настройки.

Никогда не отключайте автоочистку в рабочей среде, даже временно, чтобы «освободить ресурсы» во время пиковой нагрузки. Postgres использует MVCC: каждое UPDATE и каждое DELETE оставляет мертвую строку, которую может восстановить только вакуум. Без этого таблицы раздуваются, индексы ухудшаются, а планы выполнения постепенно ухудшаются, и ошибок не видно, пока не становится поздно.

Значение по умолчаниюautovacuum_vacuum_scale_factor (20 % неактивных строк до запуска) подходит для небольшой таблицы, а не для многомиллионной таблицы с интенсивной записью. В таблице из 10 миллионов строк эти 20% представляют собой 2 миллиона неактивных строк, накопленных до первого прохода. Уменьшайте этот порог по таблице, а не меняйте общее значение всей базы данных.

-- Понизьте порог для таблицы с большим объемом записи, а не в целом.
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

Советник, интегрированный в Aurabase Studio, проверяет эту конфигурацию при каждом анализе проекта точно так же, как таблицы без первичных ключей или неиндексированных внешних ключей. Это явный сигнал, а не молчаливая деградация, обнаруженная слишком поздно.

Большинство проблем с задержкой в ​​производстве происходят не из-за процессора и не из-за оперативной памяти: они происходят из-за отсутствия или неправильно выбранного индекса. Прежде чем коснуться одного параметра postgresql.conf, EXPLAIN (ANALYZE, BUFFERS) в рассматриваемом запросе остается наиболее надежным диагнозом. Это значительно снижает задержку запросов Postgres перед добавлением ресурсов.

-- Прежде чем приступать к настройке, диагностируйте медленный запрос.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Seq Scan в таблице с несколькими миллионами строк, где ожидался Index Scan, почти всегда сигнализирует о проблеме с индексом. Чаще всего возникают три причины: отсутствующий индекс, тип столбца, несовместимый с существующим индексом, или устаревшая статистика после массового импорта без ANALYZE. Добавление оперативной памяти или увеличение work_mem иногда скрывает этот симптом при небольшом объеме данных; проблема появляется снова, как только таблица растет.

Чтобы найти эти запросы без поиска их один за другим, pg_stat_statements объединяет статистику выполнения всех запросов сервера. Распространенная ошибка: расширение сначала должно быть указано в shared_preload_libraries, параметре контекста «postmaster», который требует перезагрузки. Без этого шага CREATE EXTENSION pg_stat_statements; молча выполнит операцию, но ничего не соберет.

# Требуется полный перезапуск (параметр «postmaster»)
shared_preload_libraries = 'pg_stat_statements'

-- После перезапуска сервера в каждой базе данных для аудита:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Это именно та ошибка, которую возвращает серверная часть Aurabase, когда это расширение отсутствует: явное сообщение, а не молчаливый пустой список, который можно спутать с «нет медленного запроса». Studio Advisor идет еще дальше: он автоматически классифицирует любой запрос со средним временем более 150 мс как предупреждение, а свыше 500 мс — как критический. Эти пороговые значения основаны на той же статистике pg_stat_statements.

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

checkpoint_completion_target управляет распространением этой записи в интервале между двумя контрольными точками. Деталь, отсутствующая в старых контрольных списках: PostgreSQL 14, выпущенный в 2021 году, изменил значение по умолчанию с 0,5 на 0,9. Таким образом, в экземпляре PostgreSQL 16, например в выделенных клиентских кластерах Aurabase, этот параметр уже по умолчанию верен; ручная настройка имеет смысл только для версий до 14. См. наше сравнение PostgreSQL 16, 17 и 18,, чтобы узнать о других изменениях версий, влияющих на настройку.

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

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

Три сигнала заслуживают постоянного мониторинга. pg_stat_statements идентифицирует запросы, качество которых со временем ухудшается. pg_stat_activity сообщает о заблокированных или аномально длительных запросах, а соотношение активных подключений к max_connections предвидит насыщение, прежде чем оно приведет к ошибкам на стороне приложения.

Вкладка «Наблюдаемость» Studio охватывает часть этой основы для любого проекта Aurabase без необходимости установки сторонних инструментов. Он перечисляет медленные запросы, позволяет отменить или прекратить активный запрос по PID и отображает индикатор насыщения пула, который выдает предупреждение при использовании более 80%. На локальном экземпляре такой же мониторинг создается вручную с активированным pg_stat_statements и подключенным к нему внешним инструментом мониторинга.

Восемь настроек в том порядке, в котором они приносят наибольшую пользу, и все, что вам нужно знать, прежде чем прикасаться к ним.

Приведенные здесь пороговые значения, такие как 150 мс и 80% насыщение пула, которые отслеживает Aurabase Studio Advisor, являются отправной точкой, проверенной кодом, а не универсальной истиной. Ваше фактическое обвинение остается единственным окончательным судьей. Чтобы точно определить размер max_connections, а не следовать общему правилу, в специальной статье подробно описана формула и ее ограничения.

Три вопроса, которые систематически возникают после первого применения контрольного списка.