СУБД Postgres Pro – сложный механизм, который можно гибко настраивать под конкретный тип нагрузки. Для этого в нём имеется множество параметров и инструментов. Например, одним из главных потребителей ресурсов CPU является оптимизатор/планировщик запросов, который отвечает за построение оптимального плана выполнения. Существует большое количество параметров, которые прямо или косвенно влияют на работу планировщика, но к их изменению надо подходить очень осторожно, т. к. возможен обратный эффект. Например, параметры _collapse_limit могут и помочь оптимизатору рассмотреть большее количество вариантов планов, и негативно повлиять на время планирования.

Сегодня я расскажу, как мы решали реальную проблему производительности и высокой (> 90%) утилизации ресурсов CPU на промышленном «боевом» сервере с СУБД Postgres Pro Enterprise 15, обслуживающем запросы бизнес-приложения, какие для этого использовали инструменты и что мы изменили в настройках СУБД.

Дано

Нашей целью являлось выявление причин снижения производительности СУБД Postgres Pro Enterprise, снятие диагностической информации, замер бизнес-показателей приложения, а также оптимизация настроек СУБД для повышения производительности. Тестирование было итеративным — запускали синтетический нагрузочный тест, имитирующий бизнес-операции: создание и проводку документов.

В качестве сервера мониторинга и хранения метрик использовали Prometheus 2.43.1, для сбора логов — ElasticSearch 8.5.1, для визуализации работы комплекса и нагрузочных сценариев — Grafana 9.2.1.

Решение

Для начала определили самых активных потребителей CPU. Для этого использовали внешние инструменты Linux: монитор процессов top, показывающий список самых активных процессов, и профайлер perf, который умеет определять на уровне отдельных функций, где процессор тратит больше всего времени, а также средство СУБД Postgres Pro — диагностический профайлер pgpro_pwr, который позволяет анализировать производительность СУБД за определенный период времени.

Выяснили, что больше всего CPU потребляют процессы postgres от имени пользователя postgres, т.е. нужно более внимательно посмотреть на бэкенд-процессы СУБД, выполняющие запросы приложения. Дальнейший анализ отчетов pgpro_pwr показал, что самые активные запросы (секции отчета SQL query statistics – Top SQL by execution time и SQL query wait statistics) имеют аномально высокое время планирования — от 1 до 4 секунд. Кроме того выяснилось, что параметр расширенного протоколирования запросов log_min_duration_statement был установлен в 0, соответственно, в журнал записывалась продолжительность выполнения всех SQL-операторов, что создавало дополнительные расходы на вызов функции записи в файл после каждого выполнения запроса.

Что было сделано на начальном этапе:

1. Зафиксировали время выполнения всех запросов. Оно явно избыточно, поэтому отключили расширенное логирование путём выставления параметра log_min_duration_statement = –1

2. Проанализировали значения параметров планировщика, отличающихся от значений по умолчанию. Выяснили, что параметры from_collapse_limit и join_collapse_limit были установлены в 30, т.е. количество вариантов соединений, которое может анализировать планировщик, сильно увеличено, что может привести к дополнительным расходам на планирование. Вернули обоим параметрам значения по умолчанию 8.

3. Для снижения накладных расходов работы с памятью установили huge_pages = on. При использовании больших страниц процессор тратит меньше времени на управление виртуальной памятью, например трансляцию виртуального адреса в физический.

В результате утилизация CPU немного упала, но по-прежнему оставалась высокой — 80%

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

• Удаление замкнутых соединений из планов путём выставления параметра enable_self_join_removal в off;

• Переупорядочивание ключей в GROUP BY путём выставления значения параметра enable_group_by_reordering в off.

Также рассмотрели различные варианты настроек экземпляра, влияющих на:

• Планирование. Чтобы избежать повторного планирования подготовленных запросов и вытеснения из памяти общих планов, увеличили размер памяти под кеш (параметр plan_cache_lru_memsize с 8 до 100М), а чтобы в принципе избежать повторного планирования для ряда запросов, решили задействовать инструмент фиксации/«замораживания» планов sr_plan

• Сжатие. Заменили алгоритм сжатия данных в TOAST-таблицах и журналах упреждающий записи (WAL) с pglz на lz4:
  default_toast_compression = lz4
wal_compression = lz4

На следующем прогоне тестов наблюдали снижение утилизации CPU до 60%.

Далее сделали еще один тестовый прогон, в котором немного изменили значение параметра generic_plan_fuzz_factor = 0.8, что означает предпочтительный выбор общего (generic) плана по сравнению со специализированными. Это еще один шаг в сторону стабилизации планов, который дал выигрыш примерно 10%.

После всех изменений утилизация CPU составила приемлемые 50–53%.

Выводы

Каждая система имеет свои особенности, поэтому не следует рассматривать рекомендации, приведённые в данной статье, как универсальное решение. Мы попытались показать, как работает настройка СУБД Postgres Pro на реальном примере.

Основные моменты, которые повлияли на производительность системы в нашем случае:

• Настройки параметров планировщика;

• Стабилизация планов запросов через sr_plan и настройки подготовленных операторов;

• Выбор оптимального алгоритма сжатия.

Вполне вероятно, что схожий алгоритм действий поможет и вам снизить нагрузку на сервер. Но если вы не уверены в своих действиях, напишите лучше на support@postgrespro.ru