Postgres DBA
156 постов
•
27 подписчиков
0 просмотренных постов скрыто
Рецензия на статью "pg_expecto для проактивного мониторинга производительноcти СУБД PostgreSQL"
«Сначала мы формируем свои инструменты, а потом наши инструменты формируют нас».
— Маршалл Маклюэн
На основании предоставленной статьи с сайта Habr.com, подробная рецензия на материал, посвященный использованию расширения pg_expecto для проактивного мониторинга производительности СУБД PostgreSQL.
Общая оценка и целевая аудитория
Статья представляет собой тематический туториал, ориентированный в первую очередь на системных администраторов и администраторов баз данных, которые уже имеют опыт работы с PostgreSQL и системами мониторинга, подобными Zabbix. Материал носит ярко выраженный практический характер и предлагает конкретное решение для сложной задачи – предсказания деградации производительности СУБД до того, как это приведет к серьезным простоям.
Анализ содержания и методики
Постановка задачи и предлагаемое решение Автор четко формулирует проблему: проактивный мониторинг из "роскоши превращается в необходимость". Предлагаемое решение — использование инструмента с открытым исходным кодом pg_expecto для статистического анализа, нагрузочного тестирования и построения отчетов. Статья фокусируется на аспекте проактивного анализа.
Ключевой алгоритм Наиболее ценная часть статьи — это описание регрессионного анализа для выявления инцидентов. Автор предлагает отслеживать два ключевых метрических тренда:
Операционная скорость обработки запросов (линия регрессии должна иметь отрицательный наклон).
Ожидания СУБД (wait_event_type) (линия регрессии должна иметь положительный наклон).
Совместный анализ этих тенденций позволяет создать надежный индикатор. Приоритет инцидента предлагается определять на основе модуля коэффициента корреляции между скоростью и ожиданиями, что является статистически обоснованным подходом.Практическая реализация Статья не ограничивается теорией. В ней подробно описан путь интеграции решения в систему мониторинга Zabbix. Метрики (текущая операционная скорость, уровень ожиданий и интегральный индикатор) экспортируются в текстовые файлы, откуда их может собирать Zabbix.
Сильные стороны статьи
Конкретика и практическая применимость: Приведены конкретные SQL-формулы для расчета угла наклона линии регрессии (ATAN(REGR_SLOPE(Y, X)) * 180 / PI()) и коэффициента корреляции.
Готовность решения: Описанный метод можно реализовать на практике, имея указанное расширение и систему мониторинга.
Фокус на профилактику: Основная ценность подхода в переходе от реактивного исправления сбоев к проактивному предотвращению проблем.
Потенциальные ограничения и вопросы
Узкая специализация: Решение заточено под конкретный инструмент (pg_expecto) и может быть избыточным для небольших или менее критичных проектов.
Отсутствие бенчмарков: В статье нет данных о том, насколько рано система предупреждает о сбое по сравнению с традиционными методами, и какова точность срабатывания (вероятность ложных срабатываний).
Требует углубленных знаний: Для успешного внедрения администратору необходимы знания не только PostgreSQL, но и основ статистического анализа.
Вывод
Статья является качественным и ценным руководством для специалистов, отвечающих за работу высоконагруженных или критически важных систем на PostgreSQL. Автор предлагает не просто общую идею, а готовую методологию с техническими деталями для реализации. Несмотря на некоторые недостатки, материал однозначно заслуживает внимания и может стать основой для построения надежной системы мониторинга производительности СУБД.
Показать полностью
PG_EXPECTO 3.0: Когда мониторинг становится проактивным, а оптимизация — интеллектуальной
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
PG Expecto 3.0: Проактивный мониторинг. Искусственный интеллект. Автоматизация решений.
Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic
PG Expecto представляет версию 3.0 с проактивным мониторингом и интеграцией с ИИ
В дополнении к стандартным возможностям расширения pg_expecto
Новая версия выводит продукт на качественно новый уровень, превращая его из мощного инструмента диагностики в интеллектуальную систему прогнозирования и автоматизированной оптимизации.
Ключевые инновации версии 3.0:
1. Проактивный мониторинг и автоматизация реагирования
Вместо того чтобы пассивно ждать появления проблем, PG Expecto 3.0 теперь активно предотвращает их.
Система научилась формировать репрезентативные профили производительности на основе стандартизированных файлов метрик. Это позволяет создать «цифровой двойник» вашей СУБД в ее оптимальном состоянии.
Как это работает? Любое отклонение ключевых метрик от установленного профиля теперь автоматически регистрируется как событие снижения производительности.
Автоматизация инцидентов: Данное событие служит триггером для автоматического создания инцидентов в ваших системах мониторинга (например, в Zabbix, Grafana Labs). Система самостоятельно классифицирует критичность и инициирует стандартные или приоритетные процедуры реагирования, значительно сокращая время на обнаружение проблемы (MTTD).
2. Интеграция с нейросетями для семантического анализа и оптимизации запросов
Самая передовая функция PG Expecto 3.0 — это встроенный «мозг», который помогает не только найти проблему, но и понять пути ее решения. Мы реализовали автоматическую интеграцию с современными языковыми моделями (такими как GPT, Claude и аналогичными).
Автоматическое конструирование контекста: Система автоматически генерирует детализированные и структурированные промпты (вводные инструкции) для нейросети. В них входит вся необходимая информация: от проблемных SQL-запросов, выявленных методами корреляционной аналитики, до контекста выполнения и метрик СУБД.
Семантический анализ рекомендаций: Нейросеть получает идеально подготовленные данные и выдает готовые, понятные рекомендации на естественном языке.
Это позволяет:
Минимизировать задержки обработки: Получать конкретные советы по настройке параметров PostgreSQL, индексов и архитектуры.
Улучшать структуру сложных SQL-запросов: Нейросеть проводит семантический разбор запросов, предлагает варианты рефакторинга, оптимизации JOIN'ов и конструкций WHERE, объясняя логику своих предложений.
Резюме для администраторов и разработчиков:
С выходом PG Expecto 3.0 работа с производительностью PostgreSQL становится проще, быстрее и интеллектуальнее. Вы получаете не просто инструмент с графиками, а проактивного помощника, который сам находит аномалии, создает тикеты и, с помощью интеграции с ИИ, дает вам готовые, обоснованные решения для их устранения.
Это следующий шаг к полностью автономной и самооптимизирующейся системе управления базами данных.
P.S. Пример анализа инцидента
Показать полностью
Типовой шаблон расследования инцидентов PostgreSQL с помощью pg_expecto. Часть 2: Детальный разбор инфраструктуры сервера
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
Производительность СУБД — это лишь верхушка айсберга. Исследуем его основание.
«PostgreSQL — это гость в доме вашего сервера. И если в доме нет электричества (CPU), течет водопровод (память) или разъехался фундамент (диски), гостю будет некомфортно, как бы он ни был совершенен.»
Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic
Задача
Используя отчеты, сформированные с помощью расширения pg_expecto провести анализ производительности инфраструктуры сервера СУБД и взаимного влияния СУБД на инфраструктуру, возникновении инцидента производительности СУБД.
Начало
Формирование отчетов по метрикам производительности СУБД и инфраструктуры
Входной параметр отчета summary_report.sh:
Дата и время возникновения инцидента
cd /postgres/pg_expecto/performance_reports
./summary_report.sh '2025-10-25 11:09'
Результаты сводного отчета в виде текстовых файлов сохраняются в папке /tmp/pg_expecto_reports
Период формирования отчетов: 1 час .
Результирующие файлы отчетов аналогичны отчетам по нагрузочному тестированию
1.КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat
Фрагмент отчета:
Результаты анализа отчета:
Проблемы с подсистемой IO
SQL запросы , возможно нуждаются в оптимизации.
2.КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /data
Фрагмент отчета
Результаты анализа отчета:
Серьезные проблемы с дисковым устройством, используемом для файловой системы /data, оказывающие влияние на производительность СУБД.
3.КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /wal
Фрагмент отчета
Результаты анализа отчета:
Проблемы с дисковым устройством, используемом для файловой системы /wal, оказывающие влияние на производительность СУБД.
4.Чек-лист подсистемы IO
Фрагмент отчета
Результаты анализа отчета:
Серьезные проблемы с подсистемой IO для сервера СУБД, оказывающие влияние на производительность СУБД.
5.Чек-лист CPU
Фрагмент отчета
Результаты анализа отчета:
Проблем со стороны CPU, оказывающих влияние на производительность СУБД - нет.
6.Чек-лист RAM
Фрагмент отчета
Результаты анализа отчета:
Проблем со стороны RAM, оказывающих влияние на производительность СУБД - нет.
Свопинг - отсутствует.
Итог: типовой шаблон анализа производительности инфраструктуры сервера СУБД при расследования инцидентов.
1. По результатам отчета "КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat" - исключить или подтвердить влияние на производительность СУБД недостаточной производительности подсистемы IO.
2. По результатам отчета "КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat" - исключить или подтвердить влияние на инфраструктуры сервера СУБД нагрузки создаваемой выполнением SQL запросов.
3. По результатам отчетов "КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT" , "Чек-лист подсистемы IO" - исключить или подтвердить:
Наличие проблем подсистемы IO
Производительность подсистемы IO
4. По результатам отчета "Чек-лист CPU" - исключить или подтвердить гипотезу о недостатке вычислительных ресурсов сервера СУБД.
5. По результатам отчета "Чек-лист RAM" - исключить или подтвердить гипотезу о недостатке RAM и наличии свопинга.
Показать полностью
6
Типовой шаблон расследования инцидентов PostgreSQL с помощью pg_expecto. Часть 1: Анализ на уровне СУБД
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
От симптома к причине: системный подход к диагностике PostgreSQL.
«Правильно заданный вопрос — это половина ответа. Данная статья — это структурированный список вопросов, которые вы должны задать данным из pg_expecto, чтобы докопаться до истинной причины инцидента.»
Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic
Задача
Используя отчеты, сформированные с помощью расширения pg_expecto провести анализ метрик производительности СУБД возникновении инцидента производительности СУБД.
Формирование отчетов по метрикам производительности СУБД и инфраструктуры
Входной параметр отчета summary_report.sh:
Дата и время возникновения инцидента
cd /postgres/pg_expecto/performance_reports
./summary_report.sh '2025-10-25 11:09'
Результаты сводного отчета в виде текстовых файлов сохраняются в папке /tmp/pg_expecto_reports
Период формирования отчетов: 1 час .
Результирующие файлы отчетов аналогичны отчетам по нагрузочному тестированию
1. Определение типа ожидания СУБД имеющего наибольшую корреляцию со снижением производительности СУБД
Формирование таблицы в Excel
Отчет:
Результат отчета по показателям производительности и ожиданий СУБД
Результат анализа отчета - определение типа ожидания с наибольшей корреляцией и абсолютным значением.
Тип ожидания IO - имеет наибольшую корреляцию с ожиданиями СУБД и оказывает наибольшее влияние на снижение производительности СУБД в целом.
2. Определение проблемных SQL запросов.
Список SQL запросов, вызывающих наибольшее количество событий ожиданий по типу ожидания, оказывающего наибольшее влияние на снижение производительности СУБД.
Формирование таблицы в Excel
Результат отчета по событиям ожиданий для SQL запросов
Результат анализа отчета - определение SQL запросов и событий ожиданий по типу ожидания оказывающего наибольшее влияние на снижение производительности СУБД.
Проблемные SQL запросы:
-3805078444547199896
-4883642671474097249
События ожидания по проблемным запросам:
DataFileRead: Ожидание чтения из файла данных отношения.
DataFileWrite: Ожидание записи в файл данных отношения.
DataFileExtend: Ожидание расширения файла данных отношения.
Итог: типовой шаблон анализа производительности и ожиданий СУБД при расследования инцидентов.
1. Определение типа ожидания СУБД имеющего наибольшую корреляцию со снижением производительности СУБД
2. Определение проблемных SQL запросов.
Показать полностью
2
Использование расширения pg_expecto для проактивного мониторинга производительности СУБД PostgreSQL
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
Производительность базы данных — это не то, что вы измеряете, когда приходят жалобы. Это то, что вы предвосхищаете, чтобы жалоб не было вовсе.
В мире, где простои баз данных измеряются в упущенной выгоде, проактивный мониторинг из роскоши превращается в необходимость. В статье описывается как pg_expecto позволяет не ждать проблем, а предсказывать и предотвращать их.
Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic
Задача
Расчет и формирование файлов метрик оценки производительности СУБД с использованием расширения pg_expecto, для применения в системах мониторинга при необходимости реализация проактивного мониторинга производительности СУБД .
Предыдущие работы по теме:
Условие начала инцидента снижения скорости СУБД:
Если угол наклона линии регрессии операционной скорости < 0 ,
И
угол наклона линии регрессии ожиданий > 0
ТО
Создать оповещение мониторинга "Инцидент деградации производительности".
В качестве уровня важности оповещения , можно использовать абсолютное значение коэффициента корреляции :
< 0.7 : низкий уровень
>= 0.7 : высокий уровень.
Практическая реализация мониторинга производительности СУБД с использованием расширения pg_expecto
Порядок формирования метрик оценки производительности СУБД
Рассчитанные значения операционной скорости и ожидания СУБД сохраняются в текстовых файлах для использования системой мониторинга Zabbix
По результатам расчета угла наклона линий регрессии операционной скорости и ожиданий СУБД - формируется текстовый файл для формирования метрики индикатора деградации производительности СУБД.
Ненулевое значение индикатора деградации производительности СУБД является стартовым событием для начала процесса решения инцидента производительности СУБД.
Пример использования метрик оценки производительности СУБД и индикатора деградации производительности СУБД в системе мониторинга Zabbix.
Дашборд Zabbix
Показать полностью
1
PG_EXPECTO: Аудит производительности инфраструктуры при нагрузочном тестировании СУБД PostgreSQL
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
Выявляем узкие места, о которых вы не подозревали: от конкуренции за дисковые IOPS до неочевидного потребления CPU.
«Современная производительность — это сложный пазл, где метрики СУБД, дисковые операции, потребление CPU и сетевые задержки тесно переплетены. Традиционное нагрузочное тестирование часто дает лишь часть ответа, заставляя нас собирать данные из десятка разных источников. В этой статье мы рассмотрим, как расширение pg_expecto становится единым источником истины, объединяя метрики инфраструктуры и PostgreSQL в едином контексте. Узнайте, как превратить разрозненные данные в целостную картину и получить точный ответ на вопрос: где на самом деле кроется узкое место вашей системы?»
Задача
Провести аудит состояния и производительности инфраструктуры сервера СУБД по итогам нагрузочного тестирования СУБД.
Проведение нагрузочного тестирования
Производительность и ожидания СУБД
График изменения операционной скорости СУБД в ходе нагрузочного тестирования
График изменения ожиданий СУБД в ходе нагрузочного тестирования
Аудит инфраструктуры сервера СУБД в ходе нагрузочного тестирования
1. КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat
Предупреждения по результатам отчета:
Корреляция между ожиданиями СУБД типа IO и временем ожидания (wa) / количеством процессов в состоянии сна(b) - признак возможных проблем с дисковой подсистемой сервера СУБД.
Корреляция между ожиданиями СУБД типа IO и объемом прочитанных/записанных блоков (bi/bo) - признак недостаточной производительности дисковой подсистемой сервера СУБД.
Корреляция между ожиданиями СУБД типа и количество времени работы CPU в user режиме (us) - признак нехватки вычислительных ресурсов и возможно неоптимальных SQL запросов.
2.1 КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /data
Предупреждения по результатам отчета:
Корреляция ожидания процессором IO и загруженности диска (wa - util) - признак проблем или недостаточной производительности дисковой подсистемы сервера.
Высокий процент отклика на запись - признак проблем или недостаточной производительности дисковой подсистемы сервера.
Высокое значение очереди - признак недостаточной производительности дисковой подсистемы.
Высокое значение утилизации дискового устройства - признак проблем или недостаточной производительности дисковой подсистемы сервера.
2.2 КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /wal
Предупреждения по результатам отчета:
Корреляция между значениями объема памяти, используемой для буферов и объемом запись на диск (buff - wMB/s) - признак некорректной настройки подсистемы IO сервера.
Корреляция между значениями объема памяти, используемой для кэширования и количеством операций записи на диск (cache - w/s) - признак некорректной настройки подсистемы IO сервера.
Высокое значение утилизации дискового устройства - признак проблем или недостаточной производительности дисковой подсистемы сервера.
3.Чек-лист IO (vmstat)
Предупреждения по результатам отчета:
Высокое значение времени ожидания процессором окончания операций ввода\вывода - признак проблем или недостаточной производительности дисковой подсистемы сервера.
4.Чек-лист CPU (vmstat)
Предупреждения по результатам отчета:
Ресурсов CPU - достаточно. Предупреждения - отсутствуют.
5.Чек-лист RAM (vmstat)
Предупреждения по результатам отчета:
Память использована полностью. Есть риск нехватки RAM при повышении нагрузки .
Свопинг - отсутствует.
Итоги аудита инфраструктуры сервера СУБД в ходе нагрузочного тестирования
Производительность дисковой подсистемы сервера СУБД - недостаточна для синтетических нагрузок по плану нагрузочного тестирования.
Время отклика на запись для дискового устройства используемого для дисковой подсистемы /data - имеет недопустимо высокое значение.
Подсистема IO сервера СУБД - требует оптимизации.
Показать полностью
8
Обратная сторона индекса: когда первичный ключ становится узким местом
Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).
Мы привыкли, что индексы — это святое. Но под нагрузкой святыни иногда приходится пересматривать
В мире СУБД общепринятая догма гласит: «Индексы ускоряют запросы». Но что, если в погоне за производительностью мы создали себе проблему? В этой статье на практике исследуется парадоксальный сценарий, при котором удаление первичного ключа у таблицы pgbench_branch и последующее увеличение стоимости запроса привели к впечатляющему росту общей производительности PostgreSQL под нагрузкой. СУБД не так просты, как кажется.
Продолжение экспериментов с расширением pg_expecto, начатых в предыдущей работе:
Задача
Оценить удаление ограничения первичного ключа в таблице на производительность СУБД в ходе нагрузочного тестирования.
Тестовая таблица pgbench_branches
Тестовые запросы, в который участвует таблица pgbench_branches
Сценарий-1 "Select only"
Тестовый запрос
select br.bbalance
from pgbench_branches br
join pgbench_accounts acc on (br.bid = acc.bid )
where acc.aid = 1000 ;
План выполнения запроса
Nested Loop (cost=0.84..5.28 rows=1 width=4)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4)
Index Cond: (aid = 1000)
-> Index Scan using pgbench_branches_pkey on pgbench_branches br (cost=0.28..2.49 rows=1 width=8)
Index Cond: (bid = acc.bid)
Сценарий-2 "Select + Update"
Запрос-1,2
SELECT MIN(bid) FROM pgbench_branches ;
SELECT MAX(bid) FROM pgbench_branches ;
План выполнения запроса
Result (cost=0.31..0.32 rows=1 width=4)
InitPlan 1
-> Limit (cost=0.28..0.31 rows=1 width=4)
-> Index Only Scan Backward using pgbench_branches_pkey on pgbench_branches (cost=0.28..24.85 rows=685 width=4)
Update (тест)
UPDATE pgbench_branches SET bbalance = bbalance + 10 WHERE bid = 469 ;
План выполнения
Update on pgbench_branches (cost=0.28..2.49 rows=0 width=0)
-> Index Scan using pgbench_branches_pkey on pgbench_branches (cost=0.28..2.49 rows=1 width=10)
Index Cond: (bid = 469)
Эксперимент - удаление ограничения первичного ключа в таблице pgbench_branches
ALTER TABLE pgbench_branches DROP CONSTRAINT pgbench_branches_pkey CASCADE ;
pgbench_db=# ALTER TABLE pgbench_branches DROP CONSTRAINT pgbench_branches_pkey CASCADE ;
NOTICE: удаление распространяется на ещё 3 объекта
DETAIL: удаление распространяется на объект ограничение pgbench_tellers_bid_fkey в отношении таблица pgbench_tellers
удаление распространяется на объект ограничение pgbench_accounts_bid_fkey в отношении таблица pgbench_accounts
удаление распространяется на объект ограничение pgbench_history_bid_fkey в отношении таблица pgbench_history
Изменение планов выполнения
Сценарий-1 "Select only"
Тестовый запрос
select br.bbalance
from pgbench_branches br
join pgbench_accounts acc on (br.bid = acc.bid )
where acc.aid = 1000 ;
План выполнения запроса
Hash Join (cost=2.80..372.68 rows=1 width=4)
-> Seq Scan on pgbench_branches br (cost=0.00..366.78 rows=1178 width=8)
-> Hash (cost=2.79..2.79 rows=1 width=4)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts acc (cost=0.57..2.79 rows=1 width=4)
Index Cond: (aid = 1000)
Сценарий-2 "Select + Update"
Запрос-1,2
SELECT MIN(bid) FROM pgbench_branches ;
SELECT MAX(bid) FROM pgbench_branches ;
План выполнения запроса
Aggregate (cost=369.72..369.73 rows=1 width=4)
-> Seq Scan on pgbench_branches (cost=0.00..366.78 rows=1178 width=4)
Update (тест)
UPDATE pgbench_branches SET bbalance = bbalance + 10 WHERE bid = 469 ;
План выполнения
Update on pgbench_branches (cost=0.00..369.73 rows=0 width=0)
-> Seq Scan on pgbench_branches (cost=0.00..369.73 rows=1 width=10)
Filter: (bid = 469)
Изменение производительности в ходе нагрузочного тестирования (Эксперимент-2) по сравнению с базовыми значениями (Эксперимент-1)
Операционная скорость
Графики операционной скорости в ходе Эксперимента-1(SPEED-1) и Эксперимента-2(SPEED-2)
Среднее увеличение операционной скорости в эксперименте-2 составило ~20%
Ожидания СУБД
Графики ожиданий СУБД в ходе Эксперимента-1(WAITINGS-1) и Эксперимента-2(WAITINGS-2)
Ожидания по SQL запросам
Показать полностью
4