PG_EXPECTO v.7 + DeepSeek: Статистический анализ инцидентов производительности СУБД PostgreSQL⁠⁠

Взято с основного технического канала Postgres DBA (возможны правки в исходной статье).

Комплекс pg_expecto помогает администраторам PostgreSQL собирать и структурировать статистику производительности, превращая сырые метрики в понятные отчёты. Однако ключевая проблема всегда оставалась неизменной: интерпретация данных. Именно здесь на помощь приходят большие языковые модели. Интеграция pg_expecto с DeepSeek позволяет выйти за рамки сухих цифр и графиков — нейросеть выступает в роли эксперта, который не просто фиксирует аномалии, но и объясняет причинно-следственные связи между падением скорости, ростом ожиданий и состоянием инфраструктуры.

В представленных отчётах DeepSeek не только выявил переход от проблем с записью к проблемам с чтением в первом инциденте, но и точно определил во втором случае виновника деградации — новый тяжёлый запрос на фоне острого дефицита памяти. Благодаря pg_expecto, нейросеть оперирует не догадками, а точными статистическими показателями (корреляциями, трендами R², приоритетами ожиданий), превращая процесс расследования инцидента из гадания по графикам в быстрый и доказательный анализ.

Инцидент №1

Дата и время инцидента: 2026-02-25 15:58

• Операционная скорость - снижается📉

• Ожидания СУБД - растут📈

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

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

Этап-1 : cбор статистической информации о производительности-ожиданиях СУБД и метриках инфраструктуры

cd /postgres/pg_expecto/sh/load_test/

./incident_report.sh '2026-02-25 15:58'

Результат отчета : исходные файлы для формирования аналитических отчетов с использованием нейросети

Этап-2: Экспресс-анализ инцидента СУБД с использованием нейросети

Анализ выполняется путем сравнения статистических показателей за отрезки времени:

• Инцидент: [ Время инцидента - 1 час ; Время инцидента ]

• Тест: [ Время инцидента - 2 часа ; Время инцидента-1час ]

Входные файлы для нейросети:

1. _1.settings.txt: Конфигурационные параметры СУБД и инфраструктуры

2. _2.1.test.postgresql_vmstat.txt: Статистически обработанные данные производительности/ожиданий СУБД и метрик инфраструктуры в тестовый период.

3. _2.postgresql_vmstat.txt: Статистически обработанные данные производительности/ожиданий СУБД и метрик инфраструктуры в период инцидента.

Краткий аналитический отчет, подготовленный нейросетью DeepSeek

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

Динамика операционной скорости

• Тестовый отрезок: скорость демонстрирует слабый положительный тренд (R² = 0,27, угол +27,25).

• Инцидент: скорость падает с хорошей достоверностью (R² = 0,70, угол –39,83).

Объём ожиданий СУБД

• Медиана ожиданий выросла с 830 955 (тест) до 928 446 (инцидент), при этом тренд роста остаётся очень сильным в обоих случаях (R² ~0,98).

Характер связи скорости и ожиданий

• Тест: связь отсутствует или положительная, модель слабая (R² = 0,24).

• Инцидент: чёткая отрицательная корреляция (–0,804), модель хорошая (R² = 0,65).

Типы ожиданий

• В обоих периодах доминирует IO (интегральный приоритет 0,6271). Однако в тесте IO сильно связан с записью на диск (bo: r = 0,815, R² = 0,66), а в инциденте — с чтением с диска (bi: r = 0,515, R² = 0,27), связь с записью исчезает.

Метрики vmstat (процессы и I/O)

• procs r (очередь на CPU): медиана выросла с 9 до 14, в инциденте 100% времени превышает число ядер (8). Тренд r стал достоверным (R² = 0,58 против 0,18 в тесте).

• procs b (процессы в uninterruptible sleep): медиана увеличилась с 152 до 177, тренд остаётся крайне сильным в обоих случаях (R² ~0,98).

• wa (iowait): медиана снизилась с 31% до 28%, но в инциденте тренд снижения wa замедлился (угол –19,16 против –41,09 в тесте).

Грязные страницы (dirty pages)

• Медиана размера dirty pages уменьшилась с ~14,8 МБ (тест) до ~11,0 МБ (инцидент).

• Связь dirty pages со свободной памятью (free) усилилась: в тесте R² = 0,46, в инциденте R² = 0,76.

• Связь dirty pages с wa остаётся значимой, но коэффициент детерминации снизился (0,73 → 0,54).

Приоритеты корреляций (CPI)

• Тест: лидируют связи cs–in, cs–us, dirty–wa.

• Инцидент: на первое место выходит связь dirty–free, затем скорость–записанные блоки, dirty–wa, появляется корреляция hit–us.

Нагрузка по запросам

• В обоих случаях основным источником IO является один и тот же запрос ( queryid -709787657973380026), его доля в общих ожиданиях IO практически неизменна (~87,2%).

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

Тренд скорости (R²):

• Тестовый отрезок: 0,27 (слабый)

• Инцидент: 0,70 (хороший)

Изменение: появление достоверного падения скорости.

Тренд ожиданий (R²):

• Тест: 0,97 (очень высокий)

• Инцидент: 0,98 (очень высокий)

Изменение: сохраняется устойчивый рост ожиданий.

Корреляция скорость–ожидания (r):

• Тест: отсутствует / положительная

• Инцидент: –0,804 (отрицательная)

Изменение: переход к обратной зависимости (рост ожиданий → падение скорости).

R² регрессии скорость–ожидания:

• Тест: 0,24 (непригодная модель)

• Инцидент: 0,65 (хорошая модель)

Изменение: установлена причинно-следственная связь.

Корреляция IO–bi (чтение):

• Тест: незначима

• Инцидент: 0,515 (R² = 0,27)

Изменение: появление вклада чтений в IO-ожидания.

Корреляция IO–bo (запись):

• Тест: 0,815 (R² = 0,66)

• Инцидент: незначима

Изменение: исчезновение связи с записью.

Корреляция dirty–free (r):

• Тест: –0,680 (R² = 0,46)

• Инцидент: –0,872 (R² = 0,76)

Изменение: усиление зависимости dirty pages от свободной памяти.

Корреляция dirty–wa (r):

• Тест: 0,856 (R² = 0,73)

• Инцидент: 0,733 (R² = 0,54)

Изменение: снижение, но сохранение значимости.

Корреляция hit–us:

• Тест: незначима

• Инцидент: –0,669 (R² = 0,45)

Изменение: появление связи попаданий в кэш с пользовательским временем CPU.

procs r тренд (R²):

• Тест: 0,18 (неудовлетворительный)

• Инцидент: 0,58 (удовлетворительный)

Изменение: рост очереди на CPU стал статистически значимым.

procs b тренд (R²):

• Тест: 0,99

• Инцидент: 0,98

Изменение: стабильно высокий рост числа заблокированных процессов (ожидание I/O)

3. Наиболее вероятная причина снижения производительности СУБД в ходе инцидента

Изменение характера дисковой нагрузки с преимущественно записи на чтение

В тестовом периоде рост ожиданий IO был обусловлен операциями записи (сильная корреляция с bo), что характерно для контрольных точек или фоновой записи dirty pages.

В инциденте связь IO с записью исчезает, но появляется корреляция с чтением (bi), при этом размер dirty pages снижается, а их зависимость от свободной памяти резко усиливается. ➡️ Это указывает на то, что система памяти находится под давлением: свободная память минимальна, кэш заполнен, и запросы на чтение данных, не помещающихся в shared buffers, вызывают физические чтения с диска.

Дополнительные факторы:

• Рост очереди процессов на выполнение (r) и числа процессов в uninterruptible sleep (b) подтверждает перегрузку дисковой подсистемы.

• Появление корреляции hit–us говорит о том, что даже при высоком hit ratio (99,3%) часть запросов требует интенсивной работы CPU, возможно, из-за неоптимальных планов или увеличения сложности обработки.

Таким образом, основной причиной падения производительности стало увеличение доли операций физического чтения, вызванное нехваткой памяти и/или изменением паттерна запросов (например, более тяжёлые сценарии чтения), что привело к росту ожиданий IO и, как следствие, к снижению операционной скорости.

Инцидент №2

Дата и время инцидента: 2026-02-26 12:24

• Операционная скорость - снижается📉

• Ожидания СУБД - растут📈

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

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

Этап-1 : cбор статистической информации о производительности-ожиданиях СУБД и метриках инфраструктуры

cd /postgres/pg_expecto/sh/load_test/

./incident_report.sh '2026-02-26 12:24 '

Результат отчета : исходные файлы для формирования аналитических отчетов с использованием нейросети

Этап-2: Экспресс-анализ инцидента СУБД с использованием нейросети

Анализ выполняется путем сравнения статистических показателей за отрезки времени:

• Инцидент: [ Время инцидента - 1 час ; Время инцидента ]

• Тест: [ Время инцидента - 2 часа ; Время инцидента-1час ]

Входные файлы для нейросети:

1. _1.settings.txt: Конфигурационные параметры СУБД и инфраструктуры

2. _2.1.test.postgresql_vmstat.txt: Статистически обработанные данные производительности/ожиданий СУБД и метрик инфраструктуры в тестовый период.

3. _2.postgresql_vmstat.txt: Статистически обработанные данные производительности/ожиданий СУБД и метрик инфраструктуры в период инцидента.

Краткий аналитический отчет, подготовленный нейросетью DeepSeek

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

Загрузка CPU

• Тест: us+sy = 23.7% (норма)

• Инцидент: us+sy = 95.7% (ALARM, более 50% времени)

Резкий рост загрузки процессора, почти полное отсутствие простоя (idle упал с 4% до 1%).

Очередь процессов (r)

• Тест: медиана 24, макс 39

• Инцидент: медиана 59, макс 80

Увеличение более чем в 2 раза, 95.7% времени очередь превышает число ядер CPU.

I/O wait (wa)

• Тест: wa >10% в 100% времени, медиана 14%

• Инцидент: wa >10% только в 10.9% времени, медиана 4%

Снижение явных простоев в ожидании ввода-вывода.

Память и подкачка

• Тест: свободная RAM <5% в 100% времени, swap in/out 100% времени

• Инцидент: те же показатели 95.7% (сохраняется острая нехватка памяти и активный свопинг).

Структура ожиданий СУБД (по приоритету ВКО)

• Тест: IPC (0.66 – критический), Timeout (0.01 – низкий)

• Инцидент: IO (0.34 – критический), LWLock (0.17 – высокий), Timeout (0.01), IPC (0.01)

Смещение фокуса с межпроцессного взаимодействия на ввод-вывод и легковесные блокировки.

Запросы-лидеры по ожиданиям

• Тест: один тяжелый запрос (-709787657973380026) – 100% IPC (BufferIo)

• Инцидент: тот же запрос дает 52% IO (DataFileRead/Write), 86% LWLock; появляется новый запрос (-343482726002183894) с 46.6% IO, включая расширение файлов (DataFileExtend) и запись WAL (SlruWrite).

2. Сравнительный статистический анализ (корреляции и регрессии)

Корреляция cs (переключения контекста) с sy (system time)

• Тест: r = 0.49, R² = 0.24 (слабая)

• Инцидент: r = 0.90, R² = 0.82 (очень сильная)

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

Корреляция Timeout с r (run queue)

• Тест: отсутствовала (не значима)

• Инцидент: r = 0.98, R² = 0.97 – чрезвычайно сильная связь, указывающая на рост системных таймеров при перегрузке CPU.

Корреляция прочитанных блоков с swap in

• Тест: не значима

• Инцидент: r = 0.87, R² = 0.76 – чтение данных с диска теперь сопровождается подкачкой страниц из swap, прямой признак нехватки памяти.

Корреляция грязных блоков с wa (I/O wait)

• Тест: не значима

• Инцидент: r = 0.87, R² = 0.76 – рост «грязных» страниц теперь вызывает ожидание ввода-вывода.

Корреляция операционной скорости с записанными блоками

• Тест: r = 0.98, R² = 0.97 (очень высокая)

• Инцидент: r = 0.92, R² = 0.84 (остается высокой, но немного снизилась) – производительность по-прежнему сильно зависит от записи.

Корреляция грязных блоков с bo (блоки записи на диск)

• Тест: r = 0.97, R² = 0.94

• Инцидент: r = 0.70, R² = 0.49 – связь ослабла, что может указывать на вмешательство других факторов (например, свопинга).

  Корреляция dirty pages size с bo

• Тест: r = 0.80, R² = 0.64

• Инцидент: r = 0.45, R² = 0.21 – механизмы фоновой записи стали менее эффективны на фоне дефицита памяти.

3. Наиболее вероятная причина снижения производительности

Всплеск интенсивной записи данных на фоне острой нехватки оперативной памяти

В инциденте появился новый тяжелый запрос (-343482726002183894), выполняющий массовые вставки/обновления, что привело к:

• расширению файлов данных (DataFileExtend);

• интенсивной записи в WAL и таблицы (DataFileWrite, SlruWrite);

• росту конкуренции за буферный кэш и WAL (LWLock: BufferContent, WALInsert).

Нехватка памяти (свободная RAM <5%, активный свопинг) заставляет систему вытеснять страницы на диск, увеличивая нагрузку на ввод-вывод и вызывая дополнительные задержки.

CPU оказался перегружен из-за:

• обработки системных вызовов при свопинге;

• роста переключений контекста (корреляция cs–sy);

• активной работы фоновых процессов записи.

Хотя hit ratio остаётся высоким (98.6%), наличие свопинга и корреляции чтения с swap in указывает, что кэш не спасает от подкачки – либо shared_buffers велики относительно доступной RAM, либо другие процессы вытесняют страницы PostgreSQL.

Таким образом, ключевой фактор деградации – совокупность интенсивной записи и дефицита памяти, что привело к исчерпанию ресурсов CPU и росту ожиданий ввода-вывода и блокировок.

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

В результате вы получаете не просто графики, а готовую диагностику: в первом случае методика укажет на дефицит памяти и переход нагрузки на физическое чтение (подсказывая проверить размер shared_buffers и характер запросов), а во втором — выявит конкретный запрос-виновник, вызвавший перегрузку CPU и свопинг из-за массовых вставок. Это позволяет перейти от гадания к быстрым и обоснованным действиям по спасению СУБД.