Серия «СУБД PostgreSQL»

1. Постановка задачи и предлагаемое решение Автор четко формулирует проблему: проактивный мониторинг из "роскоши превращается в необходимость". Предлагаемое решение — использование инструмента с открытым исходным кодом pg_expecto для статистического анализа, нагрузочного тестирования и построения отчетов. Статья фокусируется на аспекте проактивного анализа.

2. Ключевой алгоритм Наиболее ценная часть статьи — это описание регрессионного анализа для выявления инцидентов. Автор предлагает отслеживать два ключевых метрических тренда:
   Операционная скорость обработки запросов (линия регрессии должна иметь отрицательный наклон).
   Ожидания СУБД (wait_event_type) (линия регрессии должна иметь положительный наклон).
   Совместный анализ этих тенденций позволяет создать надежный индикатор. Приоритет инцидента предлагается определять на основе модуля коэффициента корреляции между скоростью и ожиданиями, что является статистически обоснованным подходом.

3. Практическая реализация Статья не ограничивается теорией. В ней подробно описан путь интеграции решения в систему мониторинга Zabbix. Метрики (текущая операционная скорость, уровень ожиданий и интегральный индикатор) экспортируются в текстовые файлы, откуда их может собирать Zabbix.

Сильные стороны статьи

• Конкретика и практическая применимость: Приведены конкретные SQL-формулы для расчета угла наклона линии регрессии (ATAN(REGR_SLOPE(Y, X)) * 180 / PI()) и коэффициента корреляции.

• Готовность решения: Описанный метод можно реализовать на практике, имея указанное расширение и систему мониторинга.

• Фокус на профилактику: Основная ценность подхода в переходе от реактивного исправления сбоев к проактивному предотвращению проблем.

Потенциальные ограничения и вопросы

• Узкая специализация: Решение заточено под конкретный инструмент (pg_expecto) и может быть избыточным для небольших или менее критичных проектов.

• Отсутствие бенчмарков: В статье нет данных о том, насколько рано система предупреждает о сбое по сравнению с традиционными методами, и какова точность срабатывания (вероятность ложных срабатываний).

• Требует углубленных знаний: Для успешного внедрения администратору необходимы знания не только PostgreSQL, но и основ статистического анализа.

Вывод

Статья является качественным и ценным руководством для специалистов, отвечающих за работу высоконагруженных или критически важных систем на PostgreSQL. Автор предлагает не просто общую идею, а готовую методологию с техническими деталями для реализации. Несмотря на некоторые недостатки, материал однозначно заслуживает внимания и может стать основой для построения надежной системы мониторинга производительности СУБД.

Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic

kznalp/PG_EXPECTO

PG Expecto представляет версию 3.0 с проактивным мониторингом и интеграцией с ИИ

В дополнении к стандартным возможностям расширения pg_expecto

PG_EXPECTO : Статистический анализ производительности СУБД PostgreSQL

Новая версия выводит продукт на качественно новый уровень, превращая его из мощного инструмента диагностики в интеллектуальную систему прогнозирования и автоматизированной оптимизации.

Ключевые инновации версии 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. Пример анализа инцидента

PG_HAZEL + vmstat/iostat + DeepSeek: анализ инцидента производительности СУБД PostgreSQL.

«PostgreSQL — это гость в доме вашего сервера. И если в доме нет электричества (CPU), течет водопровод (память) или разъехался фундамент (диски), гостю будет некомфортно, как бы он ни был совершенен.»

Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic

https://gitflic.ru/project/kznalp/pg_expecto

Задача

Используя отчеты, сформированные с помощью расширения pg_expecto провести анализ производительности инфраструктуры сервера СУБД и взаимного влияния СУБД на инфраструктуру, возникновении инцидента производительности СУБД.

Начало

Типовой шаблон расследования инцидентов PostgreSQL с помощью pg_expecto. Часть 1: Анализ на уровне СУБД

Формирование отчетов по метрикам производительности СУБД и инфраструктуры

Входной параметр отчета summary_report.sh:

• Дата и время возникновения инцидента

cd /postgres/pg_expecto/performance_reports

./summary_report.sh '2025-10-25 11:09'

Результаты сводного отчета в виде текстовых файлов сохраняются в папке /tmp/pg_expecto_reports

Период формирования отчетов: 1 час .

Результирующие файлы отчетов аналогичны отчетам по нагрузочному тестированию

PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL.

1.КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat

Фрагмент отчета:

Результаты анализа отчета:

1. Проблемы с подсистемой IO

2. SQL запросы , возможно нуждаются в оптимизации.

2.КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /data

Фрагмент отчета

Результаты анализа отчета:

1. Серьезные проблемы с дисковым устройством, используемом для файловой системы /data, оказывающие влияние на производительность СУБД.

3.КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /wal

Фрагмент отчета

Результаты анализа отчета:

1. Проблемы с дисковым устройством, используемом для файловой системы /wal, оказывающие влияние на производительность СУБД.

4.Чек-лист подсистемы IO

Фрагмент отчета

Результаты анализа отчета:

1. Серьезные проблемы с подсистемой IO для сервера СУБД, оказывающие влияние на производительность СУБД.

5.Чек-лист CPU

Фрагмент отчета

Результаты анализа отчета:

1. Проблем со стороны CPU, оказывающих влияние на производительность СУБД - нет.

6.Чек-лист RAM

Фрагмент отчета

Результаты анализа отчета:

1. Проблем со стороны RAM, оказывающих влияние на производительность СУБД - нет.

2. Свопинг - отсутствует.

Итог: типовой шаблон анализа производительности инфраструктуры сервера СУБД при расследования инцидентов.

1. По результатам отчета "КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat" - исключить или подтвердить влияние на производительность СУБД недостаточной производительности подсистемы IO.

2. По результатам отчета "КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat" - исключить или подтвердить влияние на инфраструктуры сервера СУБД нагрузки создаваемой выполнением SQL запросов.

3. По результатам отчетов "КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT" , "Чек-лист подсистемы IO" - исключить или подтвердить:

• Наличие проблем подсистемы IO

• Производительность подсистемы IO

4. По результатам отчета "Чек-лист CPU" - исключить или подтвердить гипотезу о недостатке вычислительных ресурсов сервера СУБД.

5. По результатам отчета "Чек-лист RAM" - исключить или подтвердить гипотезу о недостатке RAM и наличии свопинга.

«Правильно заданный вопрос — это половина ответа. Данная статья — это структурированный список вопросов, которые вы должны задать данным из pg_expecto, чтобы докопаться до истинной причины инцидента.»

Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic

https://gitflic.ru/project/kznalp/pg_expecto

Задача

Используя отчеты, сформированные с помощью расширения pg_expecto провести анализ метрик производительности СУБД возникновении инцидента производительности СУБД.

Формирование отчетов по метрикам производительности СУБД и инфраструктуры

Входной параметр отчета summary_report.sh:

• Дата и время возникновения инцидента

cd /postgres/pg_expecto/performance_reports

./summary_report.sh '2025-10-25 11:09'

Результаты сводного отчета в виде текстовых файлов сохраняются в папке /tmp/pg_expecto_reports

Период формирования отчетов: 1 час .

Результирующие файлы отчетов аналогичны отчетам по нагрузочному тестированию

PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL.

1. Определение типа ожидания СУБД имеющего наибольшую корреляцию со снижением производительности СУБД

Формирование таблицы в Excel

PG_EXPECTO : Показатели производительности и ожиданий СУБД в ходе нагрузочного тестирования

Отчет:

Результат анализа отчета - определение типа ожидания с наибольшей корреляцией и абсолютным значением.

Тип ожидания IO - имеет наибольшую корреляцию с ожиданиями СУБД и оказывает наибольшее влияние на снижение производительности СУБД в целом.

2. Определение проблемных SQL запросов.

Список SQL запросов, вызывающих наибольшее количество событий ожиданий по типу ожидания, оказывающего наибольшее влияние на снижение производительности СУБД.

Формирование таблицы в Excel

PG_EXPECTO : Диаграмма Парето по ожиданиям SQL запросов

Результат анализа отчета - определение SQL запросов и событий ожиданий по типу ожидания оказывающего наибольшее влияние на снижение производительности СУБД.

Проблемные SQL запросы:

• -3805078444547199896

• -4883642671474097249

События ожидания по проблемным запросам:

• DataFileRead: Ожидание чтения из файла данных отношения.

• DataFileWrite: Ожидание записи в файл данных отношения.

• DataFileExtend: Ожидание расширения файла данных отношения.

Итог: типовой шаблон анализа производительности и ожиданий СУБД при расследования инцидентов.

1. Определение типа ожидания СУБД имеющего наибольшую корреляцию со снижением производительности СУБД

2. Определение проблемных SQL запросов.

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

Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic

https://gitflic.ru/project/kznalp/pg_expecto

Задача

Расчет и формирование файлов метрик оценки производительности СУБД с использованием расширения pg_expecto, для применения в системах мониторинга при необходимости реализация проактивного мониторинга производительности СУБД .

Предыдущие работы по теме:

Условие начала инцидента снижения скорости СУБД:

Если угол наклона линии регрессии операционной скорости < 0 ,

И

угол наклона линии регрессии ожиданий > 0

ТО

Создать оповещение мониторинга "Инцидент деградации производительности".

В качестве уровня важности оповещения , можно использовать абсолютное значение коэффициента корреляции :

• < 0.7 : низкий уровень

• >= 0.7 : высокий уровень.

"Индикатор снижения скорости" как сигнал для начала корреляционного анализа ожиданий СУБД.

Метрики оценки производительности СУБД PostgreSQL, используемые в оперативно-тактическом комплексе "PG_HAZEL".

Практическая реализация мониторинга производительности СУБД с использованием расширения pg_expecto

Порядок формирования метрик оценки производительности СУБД

1. Рассчитанные значения операционной скорости и ожидания СУБД сохраняются в текстовых файлах для использования системой мониторинга Zabbix

2. По результатам расчета угла наклона линий регрессии операционной скорости и ожиданий СУБД - формируется текстовый файл для формирования метрики индикатора деградации производительности СУБД.

3. Ненулевое значение индикатора деградации производительности СУБД является стартовым событием для начала процесса решения инцидента производительности СУБД.

Пример использования метрик оценки производительности СУБД и индикатора деградации производительности СУБД в системе мониторинга Zabbix.

«Современная производительность — это сложный пазл, где метрики СУБД, дисковые операции, потребление CPU и сетевые задержки тесно переплетены. Традиционное нагрузочное тестирование часто дает лишь часть ответа, заставляя нас собирать данные из десятка разных источников. В этой статье мы рассмотрим, как расширение pg_expecto становится единым источником истины, объединяя метрики инфраструктуры и PostgreSQL в едином контексте. Узнайте, как превратить разрозненные данные в целостную картину и получить точный ответ на вопрос: где на самом деле кроется узкое место вашей системы?»

Задача

Провести аудит состояния и производительности инфраструктуры сервера СУБД по итогам нагрузочного тестирования СУБД.

Проведение нагрузочного тестирования

PG_EXPECTO : Нагрузочное тестирование СУБД PostgreSQL.

Производительность и ожидания СУБД

PG_EXPECTO : Построение графиков производительности и ожиданий по результатам нагрузочного тестирования СУБД

Аудит инфраструктуры сервера СУБД в ходе нагрузочного тестирования

1. КОРРЕЛЯЦИЯ ОЖИДАНИЙ СУБД И МЕТРИК vmstat

PG_EXPECTO : Корреляция ожиданий СУБД и показателей vmstat

Предупреждения по результатам отчета:

1. Корреляция между ожиданиями СУБД типа IO и временем ожидания (wa) / количеством процессов в состоянии сна(b) - признак возможных проблем с дисковой подсистемой сервера СУБД.

2. Корреляция между ожиданиями СУБД типа IO и объемом прочитанных/записанных блоков (bi/bo) - признак недостаточной производительности дисковой подсистемой сервера СУБД.

3. Корреляция между ожиданиями СУБД типа и количество времени работы CPU в user режиме (us) - признак нехватки вычислительных ресурсов и возможно неоптимальных SQL запросов.

2.1 КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /data

PG_EXPECTO : Статистические показатели iostat для дискового устройства

Предупреждения по результатам отчета:

1. Корреляция ожидания процессором IO и загруженности диска (wa - util) - признак проблем или недостаточной производительности дисковой подсистемы сервера.

2. Высокий процент отклика на запись - признак проблем или недостаточной производительности дисковой подсистемы сервера.

3. Высокое значение очереди - признак недостаточной производительности дисковой подсистемы.

4. Высокое значение утилизации дискового устройства - признак проблем или недостаточной производительности дисковой подсистемы сервера.

2.2 КОРРЕЛЯЦИЯ МЕТРИК VMSTAT И IOPSTAT для файловой системы /wal

PG_EXPECTO : Статистические показатели iostat для дискового устройства

Предупреждения по результатам отчета:

1. Корреляция между значениями объема памяти, используемой для буферов и объемом запись на диск (buff - wMB/s) - признак некорректной настройки подсистемы IO сервера.

2. Корреляция между значениями объема памяти, используемой для кэширования и количеством операций записи на диск (cache - w/s) - признак некорректной настройки подсистемы IO сервера.

3. Высокое значение утилизации дискового устройства - признак проблем или недостаточной производительности дисковой подсистемы сервера.

3.Чек-лист IO (vmstat)

PG_EXPECTO : Чек-лист IO

Предупреждения по результатам отчета:

1. Высокое значение времени ожидания процессором окончания операций ввода\вывода - признак проблем или недостаточной производительности дисковой подсистемы сервера.

4.Чек-лист CPU (vmstat)

PG_EXPECTO : Чек-лист CPU

Предупреждения по результатам отчета:

1. Ресурсов CPU - достаточно. Предупреждения - отсутствуют.

5.Чек-лист RAM (vmstat)

PG_EXPECTO : Чек-лист RAM

Предупреждения по результатам отчета:

1. Память использована полностью. Есть риск нехватки RAM при повышении нагрузки .

2. Свопинг - отсутствует.

Итоги аудита инфраструктуры сервера СУБД в ходе нагрузочного тестирования

1. Производительность дисковой подсистемы сервера СУБД - недостаточна для синтетических нагрузок по плану нагрузочного тестирования.

2. Время отклика на запись для дискового устройства используемого для дисковой подсистемы /data - имеет недопустимо высокое значение.

3. Подсистема IO сервера СУБД - требует оптимизации.

В мире СУБД общепринятая догма гласит: «Индексы ускоряют запросы». Но что, если в погоне за производительностью мы создали себе проблему? В этой статье на практике исследуется парадоксальный сценарий, при котором удаление первичного ключа у таблицы pgbench_branch и последующее увеличение стоимости запроса привели к впечатляющему росту общей производительности PostgreSQL под нагрузкой. СУБД не так просты, как кажется.

Продолжение экспериментов с расширением pg_expecto, начатых в предыдущей работе:

Ожидания в избытке: как лишние индексы тормозят 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)

Hash Cond: (br.bid = acc.bid)

-> 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)

Операционная скорость

Среднее увеличение операционной скорости в эксперименте-2 составило ~20%

Ожидания СУБД

Ожидания по SQL запросам