![](https://habrastorage.org/getpro/habr/upload_files/918/499/863/918499863c8c5ebac002eb74f2a2f6ed.jpg)
СУБД 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-операторов, что создавало дополнительные расходы на вызов функции записи в файл после каждого выполнения запроса.
Что было сделано на начальном этапе:
Зафиксировали время выполнения всех запросов. Оно явно избыточно, поэтому отключили расширенное логирование путём выставления параметра log_min_duration_statement = –1
Проанализировали значения параметров планировщика, отличающихся от значений по умолчанию. Выяснили, что параметры from_collapse_limit и join_collapse_limit были установлены в 30, т.е. количество вариантов соединений, которое может анализировать планировщик, сильно увеличено, что может привести к дополнительным расходам на планирование. Вернули обоим параметрам значения по умолчанию 8.
Для снижения накладных расходов работы с памятью установили 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
Комментарии (9)
rinace
01.08.2024 06:55А теперь главный вопрос - какая выгода для информационной системы в целом в результате работ по снижению утилизации CPU с 80 до 60% была получена ?
Какая была производительность СУБД до начала работ и какая стала после ?
После всех изменений утилизация CPU составила приемлемые 50–53%.
1) вы считаете , что производительность обратно пропорциональна утилизации CPU?
2) На основании чего сделан вывод , что 50-53% это приемлемое значение ? А почему не 45% или 62% ?
Loxmatiymamont Автор
01.08.2024 06:55вы считаете, что производительность обратно пропорциональна утилизации CPU?
Разумеется нет, это не всегда так. Но в данном случае, в результате наших действий удалось значительно увеличить ключевые метрики бизнес-приложения. Можно назвать их "количество обработанных/проведённых документов в единицу времени".
На основании чего сделан вывод, что 50-53% это приемлемое значение ? А почему не 45% или 62% ?
Приемлемое, в нашем случае, относительно исходных значений, меньше которых нам получить не удалось. Если бы это было 10% мы бы тоже не обиделись :-)
И сложно сомневаться, что если программа может выполнить больше работы, используя меньшее количество ресурсов и оставить запас под будущие нагрузки, это плохо.
rinace
01.08.2024 06:55программа может выполнить больше работы
Работа это фундаментальное физическое понятие. Что вы имеете в виду когда говорится о работе выполненной программой ?
Loxmatiymamont Автор
01.08.2024 06:55Я, конечно, не физик, однако работы нет в списке фундаментальных понятий и величин т.к. её определение зависит исключительно от раздела физики, в котором оно рассматривается. Фундаментальные вещи себя так не ведут и всегда однозначны, иначе какие же они фундаментальные.
Да и у нас тут не кружок юного физика, чтобы притягивать за уши определения из других дисциплин. Но раз вам это так принципиально, давайте обратимся к обратной стороне луны - наукам гуманитарным. Возьмём словарь Даля и посмотрим на определение термина "работа", как совершенно очевидного синонима слова "труд". Получаем: "все, что требует усилий, старанья и заботы; всякое напряженье телесных или умственных сил". Кажется всё очевидно, но это же гуманитарии и что они вообще толкового могут сказать, правда? Тогда давайте обратимся обратно к строгой науке физике, где, например, Ожегов даёт нам такое определение этого таинственного термина: "Процесс превращения одного вида энергии в другой".
На этом предлагаю остановиться, договорившись что работа программы это прекращение энергии электрической, в энергию причинения бизнес-результата с конвертацией в деньги.
rinace
Большое спасибо за статью и тему. Потребуется более вдумчивое чтение , а пока сразу же вопросы :
1) Какое определение термина "производительность СУБД" вы используете ?
2) Как рассчитывается и мониторится метрика производительности СУБД ?
Loxmatiymamont Автор
Тут правильнее было бы сказать, что PGPRO_PWR помогает оценить производительность и это просто один из инструментов. Если мы исследуем статистику производительности СУБД за определённый период времени, значит у нас есть сомнения/подозрения, что что-то "было не так". Отчёт профайлера помогает найти явные аномалии или самых активных потребителей ресурсов внутри БД, показывает основные ожидания...
Универсальной метрики "производительность СУБД", к сожалению, не существует. Самые известные "попугаи" для измерений это TPS, но очевидно что надо ориентироваться на требования бизнес-приложения (SLA), следить за ожиданиями/блокировками/запросами в БД и мониторить потребление ресурсов сервера (CPU/Mem/IO). То есть у каждого может быть своё понятие производительности.
Одним словом: производительность СУБД никому не интересна, но всем интересно, насколько быстро работает приложение :-)
rinace
У меня другое мнение - существует, считается, мониторится , статистически анализируется .
В первую очередь интересна для DBA - "с СУБД всё в порядке , разбирайтесь с кривым приложением и проблемами инфоаструктуры ".
VVitaly
Ну как бы конкретную DB в любом случае нужно подстраивать/оптимизировать под специфику прикладного приложения (с учетом доступных ресурсов и профиля нагрузки на нее) и это задача DBA... Другой вопрос что прикладное приложение должно "учитывать особенности реализации конкретной DB", а это сейчас большая проблема для современных прикладных разработчиков которые "по факту" только сторонними фреймворками и умеют пользоваться...
rinace
В самую точку !