Методология статистического анализа производительности СУБД: опыт применения PG_EXPECTO v.7 на реальном инциденте.

Практическое применение статистического анализа производительности СУБД с использованием pg_expecto v.7: разбор инцидента и верификация гипотез

Математика производительности - когда статистика говорит громче слов

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL

Глоссарий терминов | Postgres DBA | Дзен

Предыдущие материалы

Предисловие

Целесообразность применения нейросети DeepSeek для анализа статистических данных и подготовки рекомендаций по итогам инцидентов обусловлена ограниченностью традиционных методов нагрузочного тестирования, нерелевантных в условиях стохастических пиковых нагрузок промышленных систем. В рамках настоящей работы на базе инструментария pg_expecto v.7 продемонстрирована эффективность перехода к статистическому анализу инцидентов PostgreSQL: от идентификации критических факторов до верификации гипотез оптимизации. Использование DeepSeek обеспечивает математически обоснованные выводы о причинах деградации производительности, что подтверждает высокую эффективность данного подхода для оперативной диагностики и повышения отказоустойчивости информационных систем.

Шаблон анализа инцидента производительности СУБД

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

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

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

Инцидент производительности СУБД

• Операционная скорость - снижается📉
• Ожидания СУБД - растут 📈

График изменения операционной скорости в течении часа до точки инцидента

График изменения ожиданий СУБД в течении часа до точки инцидента

Анализ инцидента и подготовка рекомендаций по оптимизации настроек СУБД и инфраструктуры

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

_2.postgresql_vmstat.txt - ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД

_2.1.test.postgresql_vmstat.txt - ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД

Для формирования отчета используй списки, вместо таблиц.

Состав отчета:

# Ключевые проблемы для "ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД" и "ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД"

## Проблемы СУБД

## Проблемы инфраструктуры

# Рекомендации по итогам анализа инцидента

## Рекомендации по оптимизации СУБД

## Рекомендации по оптимизации инфраструктуры

Ключевые проблемы для "ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД" и "ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД"

Проблемы СУБД

Критическое доминирование IO-ожиданий

• В обоих периодах основным типом ожидания является IO (интегральный приоритет вырос с 0.6271 в тесте до 0.7789 в инциденте). Корреляция операционной скорости с IO-ожиданиями отрицательная и статистически значимая (−0.786 в инциденте), что означает: рост ожиданий напрямую снижает производительность.

Один запрос генерирует основную массу ожиданий

• QueryID -6237913963540934095 вызывает 87% всех IO-ожиданий (в тесте — 87.23%, в инциденте — 87.37%). События: DataFileRead, DataFileWrite, SlruRead. Это указывает на конкретный проблемный запрос с интенсивными операциями чтения/записи данных и обращением к WAL (SlruRead).

Запись стала главным узким местом

• В тестовом отрезке операционная скорость почти полностью определялась объёмом записанных блоков (R² = 0.95). В инциденте также сохраняется высокая корреляция скорости с записанными блоками (R² = 0.65), а грязные блоки сильно коррелируют с ожиданием IO (R² = 0.76) и с записью на диск (R² = 0.62). Это говорит о неспособности фоновых процессов своевременно сбрасывать грязные страницы.

Рост чтений с диска при высоком hit ratio

• Несмотря на hit ratio shared buffers выше 99%, в инциденте появилась сильная корреляция операционной скорости с прочитанными блоками (R² = 0.73). Это означает, что даже 1% чтений, не попавших в кеш, создаёт непропорционально высокую нагрузку из-за большого объёма данных или неэффективных сканов.

Чрезмерное количество подключений

• Параметр max_connections = 3000 при 8 ядрах CPU и 7.5 ГБ RAM создаёт избыточное потребление памяти и увеличивает накладные расходы на переключение контекста. Корреляции cs с us и in, хотя и высокие, имеют пониженный R² в инциденте, что указывает на смешанную природу загрузки.

Агрессивные настройки автовакуума

• autovacuum_naptime = 1s, низкие scale_factor, autovacuum_max_workers = 4 могут вызывать частые сканирования и дополнительные IO-операции, усугубляя общую дисковую нагрузку.

Проблемы инфраструктуры

Острейший дефицит оперативной памяти

• Свободная RAM составляет менее 5% на протяжении 100% времени инцидента (медиана свободной памяти — всего 119 МБ при 7.5 ГБ). Это приводит к вытеснению страничного кеша и росту обращений к диску, хотя сам свопинг не используется.

Лавинообразный рост процессов в состоянии uninterruptible sleep (procs b)

• Количество процессов, заблокированных в ожидании IO, выросло с медианы 128 в тесте до 175 в инциденте, с сильным трендом (R² = 0.99, угол наклона 44.5). Это прямое следствие неспособности дисковой подсистемы обслуживать запросы.

Перегрузка очереди выполнения (procs r)

• Очередь процессов, готовых к выполнению, превышает количество ядер CPU (8) в 100% времени. При этом CPU простаивает в ожидании IO (wa > 10% постоянно). Процессы не могут получить CPU, потому что заблокированы на IO, но планировщик держит их в очереди.

Высокий процент времени ожидания IO (wa)

• Процент простоя CPU в ожидании IO превышает 10% весь период инцидента (достигая 33%). Это свидетельствует о том, что дисковая система не успевает обрабатывать поток запросов.

Корреляция грязных страниц с ожиданием IO

• В инциденте размер грязных страниц сильно коррелирует с wa (R² = 0.88) и умеренно с bo (R² = 0.52). Это говорит о том, что механизм фоновой записи не справляется с потоком изменений: страницы накапливаются, и backend-процессы вынуждены синхронно сбрасывать их, блокируясь.

Недостаточная пропускная способность дисковой подсистемы

• Несмотря на отдельные диски для WAL и данных, система не выдерживает пиковую нагрузку на запись. Виртуальные диски, вероятно, разделяют общую очередь ввода-вывода с другими виртуальными машинами на гипервизоре.⬅️

Параметры ядра dirty_ могут быть неоптимальны*

• vm.dirty_ratio = 10% (около 750 МБ) — лимит не достигается (реальные dirty pages до 16 МБ), но vm.dirty_expire_centisecs = 3000 (30 секунд) заставляет сбрасывать страницы по таймеру, а диск не успевает. Это приводит к росту wa.

Рекомендации по итогам анализа инцидента

Рекомендации по оптимизации СУБД

1️⃣Оптимизировать проблемный запрос

• Провести детальный разбор запроса с queryid -6237913963540934095:
• Проанализировать план выполнения, выявить операции с полным сканированием таблиц или индексов, приводящие к DataFileRead/Write.
• Проверить, используются ли индексы, нет ли избыточных сортировок или хеш-соединений, требующих записи во временные файлы.
• Рассмотреть возможность партиционирования больших таблиц или изменения логики приложения для снижения IO-нагрузки.

2️⃣Снизить количество одновременных подключений

• Уменьшить max_connections до разумного значения (например, 300–500) и использовать пул соединений (PgBouncer) для эффективного распределения нагрузки. Это сократит потребление памяти и уменьшит конкуренцию за ресурсы.

3️⃣Настроить параметры контрольных точек

• Увеличить checkpoint_timeout до 15–30 минут и max_wal_size до 64–96 ГБ, чтобы сгладить пиковые нагрузки на запись. Отслеживать статистику pg_stat_bgwriter — buffers_checkpoint не должен резко превышать buffers_clean.

4️⃣Отрегулировать фоновую запись

• Увеличить bgwriter_lru_maxpages до 800–1000, чтобы фоновый писатель успевал сбрасывать больше грязных страниц за цикл.
• Уменьшить bgwriter_delay до 5–10 мс (оставить как есть, но контролировать эффективность).
• Увеличить bgwriter_lru_multiplier до 4–6, чтобы более агрессивно предвосхищать потребности.

5️⃣Настроить автовакуум

• Увеличить autovacuum_naptime до 5–10 секунд, чтобы снизить частоту запусков.
• Подобрать scale_factor и threshold под реальную скорость обновления строк (например, autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 1000).
• Ограничить autovacuum_max_workers до 2–3, чтобы они не конкурировали с основной нагрузкой.

6️⃣Оптимизировать кеширование для чтения

• Несмотря на высокий hit ratio, следует проанализировать, какие именно объекты не попадают в кеш. Возможно, увеличение shared_buffers до 5–6 ГБ (если позволит ОС) поможет удержать больше данных, но нужно оставить память для ОС и файлового кеша.

Рекомендации по оптимизации инфраструктуры

1️⃣Увеличить объем оперативной памяти

• Так как текущая загрузка упирается в IO, добавление RAM позволит расширить файловый кеш ОС и, возможно, увеличить shared_buffers, снижая потребность в дисковых операциях. Рекомендуется минимум 16–32 ГБ для такой нагрузки.

2️⃣Оптимизировать параметры ядра Linux

• Уменьшить vm.dirty_expire_centisecs до 1500–2000 (15–20 секунд), чтобы страницы сбрасывались быстрее и не накапливались.
• Увеличить vm.dirty_background_ratio до 10% и vm.dirty_ratio до 20–25%, чтобы дать системе больше времени для фоновой записи и избежать синхронных сбросов, но только после анализа скорости дисков.
• Проверить планировщик ввода-вывода: для виртуальных дисков рекомендуется none (или noop), для физических SSD — mq-deadline.
• Установить vm.swappiness = 1 (уже установлено) — оставить, чтобы избежать нежелательного свопирования.

Эти меры помогут стабилизировать производительность и предотвратить дальнейшую деградацию. Первоочередное внимание следует уделить оптимизации проблемного запроса и увеличению оперативной памяти.

Применение рекомендованных изменений СУБД и ОС

☑️Настроить параметры контрольных точек

• Увеличить checkpoint_timeout до 15–30 минут и max_wal_size до 64–96 ГБ, чтобы сгладить пиковые нагрузки на запись.

☑️Отрегулировать фоновую запись

• Увеличить bgwriter_lru_maxpages до 800–1000, чтобы фоновый писатель успевал сбрасывать больше грязных страниц за цикл.
• Уменьшить bgwriter_delay до 5–10 мс (оставить как есть, но контролировать эффективность).
• Увеличить bgwriter_lru_multiplier до 4–6, чтобы более агрессивно предвосхищать потребности.

checkpoint_timeout

--------------------

30min

max_wal_size

--------------

64GB

bgwriter_lru_maxpages

-----------------------

800

bgwriter_delay

----------------

10ms

bgwriter_lru_multiplier

-------------------------

6

☑️Оптимизировать параметры ядра Linux

• Уменьшить vm.dirty_expire_centisecs до 1500–2000 (15–20 секунд), чтобы страницы сбрасывались быстрее и не накапливались.
• Увеличить vm.dirty_background_ratio до 10% и vm.dirty_ratio до 20–25%, чтобы дать системе больше времени для фоновой записи и избежать синхронных сбросов, но только после анализа скорости дисков.

# sysctl -w vm.dirty_expire_centisecs=1500

vm.dirty_expire_centisecs = 1500

# sysctl -w vm.dirty_background_ratio=10

vm.dirty_background_ratio = 10

# sysctl -w vm.dirty_ratio=20

vm.dirty_ratio = 20

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

baseline - Базовые настройки СУБД и ОС. advice - после применения рекомендованных изменений.

Анализ влияния рекомендованных изменений на производительность СУБД

Исходные данные:

1.baseline.settings.txt - БАЗОВЫЕ НАСТРОЙКИ

1.baseline.load_test.postgresql_vmstat.txt - СТАТИСТИЧЕСКИЕ ДАННЫЕ ПО ПРОИВОДИТЕЛЬНОСТИ СУБД И ОС ЗА ПЕРИОД НАБЛЮДЕНИЯ

2.advice.txt - РЕКОМЕНДАЦИИ НЕЙРОСЕТИ ПО РЕЗУЛЬТАТАМ АНАЛИЗА ИНЦИДЕНТА ПРОИЗВОДИТЕЛЬНОСТИ СУБД

2.advice.settings.txt - РЕКОМЕНДОВАННЫЕ НАСТРОЙКИ СУБД и VM

2.advice.load_test.postgresql_vmstat.txt - СТАТИСТИЧЕСКИЕ ДАННЫЕ ПО ПРОИВОДИТЕЛЬНОСТИ СУБД И ОС ЗА ПЕРИОД НАБЛЮДЕНИЯ ПОСЛЕ ПРИМЕНЕНИЯ РЕКОМЕНДОВАННЫХ ИЗМЕНЕНИЙ

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

Состав отчета:

# Анализ рекомендаций и примененных изменений настроек СУБД и ОС

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

# Сравнительный статистический анализ (сравнение коэффициентов корреляции и регрессии) метрик производительности СУБД и инфраструктуры

# Анализ эффективности рекомендованных нейросетью настроек и итоговое влияние изменений на производительность СУБД

Для формирования отчета используй списки, вместо таблиц.

Анализ рекомендаций и примененных изменений настроек СУБД и ОС

Настройки СУБД PostgreSQL

• checkpoint_timeout увеличен с 5 min до 30 min – для более редких, но менее пиковых контрольных точек.☑️
• max_wal_size повышен с 32 GB до 64 GB – чтобы контрольные точки могли растягиваться во времени без преждевременного заполнения WAL.☑️
• bgwriter_lru_maxpages поднят с 400 до 800 – фоновый писатель теперь может сбрасывать больше грязных страниц за цикл.☑️
• bgwriter_lru_multiplier увеличен с 4 до 6 – более агрессивное предвосхищение потребностей в чистых буферах.☑️
• bgwriter_delay планировалось уменьшить с 10 ms до 5 ms, но изменение не удалось из-за ограничения (минимальное значение 10 ms).❌

Настройки ядра Linux (vm)

• vm.dirty_background_ratio поднят с 5% до 10% – фоновый сброс грязных страниц начинается при большем заполнении памяти.☑️
• vm.dirty_ratio увеличен с 10% до 20% – принудительная синхронная запись отодвинута, давая больше времени на фоновый сброс.☑️
• vm.dirty_expire_centisecs уменьшен с 3000 (30 с) до 1500 (15 с) – страницы быстрее признаются устаревшими и подлежат записи.☑️
• vm.dirty_writeback_centisecs, vm.swappiness и остальные параметры ядра остались без изменений.

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

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

• ℹ️Медиана незначительно снизилась: с ~1 045 тыс. до ~1 011 тыс. операций (в пределах статистической погрешности).

Общие ожидания (WAITINGS)

• ℹ️Медиана немного уменьшилась: с ~746 тыс. до ~734 тыс. (‑1,6%).

Тренды vmstat (качество и скорость изменений)

🔴procs → r (очередь на выполнение):

• До: R²=0,83, скорость изменения 35,37 (очень высокий негативный тренд).
• После: R²=0,47 (модель стала хуже), скорость снизилась до 16,16 (умеренный тренд) – положительная динамика, хотя абсолютные значения r выросли (медиана с 10 до 13).

🔴procs → b (ожидание I/O):

• До: R²=0,98, скорость 43,76 (критический рост).
• После: R²=0,97, скорость 43,46 (столь же высокая) – проблема сохраняется.

🔴cpu → wa (ожидание I/O): в обоих случаях позитивный тренд (улучшение), но классифицируется как «шум».

Относительные показатели (% времени превышения порогов)

🔴r > ядер CPU: вырос с 66,7% до 82,2% – ухудшение (очередь процессов стала чаще превышать количество ядер).

🔴wa > 10% и b > ядер CPU: остались на уровне 100% времени – дисковая подсистема по-прежнему перегружена.⚠️

🔴свободная RAM < 5%: также 100% – память остаётся узким местом.⚠️

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

• Медиана размера грязных страниц выросла с ~18 МБ до ~30 МБ.

Ключевой запрос⚠️

• По-прежнему один и тот же queryid (-6237913963540934095, select scenario1()) вызывает ~87% всех ожиданий I/O, количество вызовов даже немного увеличилось (с 19,1 M до 19,6 M).

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

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

• До: корреляция 0,9856, R²=0,97 (ALARM).
• После: корреляция 0,9920, R²=0,98 – ещё выше, зависимость от записи на диск осталась критической.

Корреляция dirty pages size с wa (ожидание I/O)

• До: 0,9695, R²=0,94 (очень сильная).
• После: 0,8815, R²=0,78 (снизилась, но всё ещё значима) – положительный эффект настроек VM.

Корреляции с bo (блоки, записанные на устройства)

• IO и bo: была значимая (0,6761, R²=0,46) – после изменений отсутствует.
• Записанные блоки и bo: была 0,7914 (R²=0,63) – после отсутствует.
• Грязные блоки и bo: была слабая (0,6186, R²=0,38) – после отсутствует.
• ℹ️Это говорит о том, что запись стала более равномерной, пиковые сбросы перестали жёстко коррелировать с дисковыми операциями.

Корреляции контекстных переключений (cs)

• cs и sy (system time): R² вырос с 0,78 до 0,89 – ядро стало тратить больше времени на переключения контекста относительно системного времени.
• cs и us (user time) и cs и in (прерывания) остались на очень высоком уровне (R²>0,95).

Индекс приоритета корреляции (CPI)

• dirty pages – wa снизился с 0,9518 до 0,7810 – переместился с 4‑го на 6‑е место в рейтинге, что подтверждает ослабление проблемы.
• IO – bi (чтения) исчез из списка значимых корреляций (был 0,5343).ℹ️

Анализ эффективности рекомендованных нейросетью настроек и итоговое влияние на производительность СУБД

➕Положительные эффекты

• Уменьшилась корреляция между накоплением грязных страниц и простоями CPU в ожидании I/O (wa).
• Исчезла прямая связь между объёмом записанных блоков PostgreSQL и фактической записью на диск (bo) – контрольные точки и фоновый writer работают более плавно.⬅️
• Тренд роста очереди на выполнение (r) замедлился, хотя абсолютные значения выросли.

➖Отрицательные/нейтральные аспекты

• ❗Основная проблема – зависимость производительности от записи на диск – не устранена (корреляция скорости с записанными блоками осталась крайне высокой).
• ‼️Процессы в непрерываемом сне (b) продолжают расти с прежней скоростью, а процент времени с превышением порогов wa и b остаётся 100%.
• ⚠️Медиана операционной скорости немного снизилась, что может быть следствием изменения характера нагрузки (рост числа вызовов проблемного запроса).

Вывод об эффективности

• Рекомендации частично достигли цели: сгладили пиковые сбросы грязных страниц и уменьшили негативное влияние накопления dirty pages на ожидания.
• Однако они не затронули корень проблемы – неэффективный запрос select scenario1(), на который приходится 87% всех ожиданий I/O.⚠️
• Изменения в настройках ОС и СУБД дали локальные улучшения, но не смогли компенсировать недостатки прикладного уровня.⚠️

Для дальнейшей оптимизации необходимо:

• Исследовать и оптимизировать запрос select scenario1() (индексы, переписывание, изменение логики).
• Рассмотреть возможность аппаратного ускорения дисковой подсистемы (например, размещение WAL на NVMe).
• Продолжить мониторинг и при необходимости скорректировать bgwriter_delay (если позволит версия) или параметры автоочистки.

Послесловие

Практическое применение методологии статистического анализа на базе pg_expecto v.7 позволило не только идентифицировать критические факторы деградации производительности (доминирование IO-ожиданий, дефицит памяти, неэффективный запрос), но и количественно верифицировать эффект от предложенных изменений. Корреляционно-регрессионный анализ доказал, что настройки параметров ядра и СУБД привели к снижению зависимости между накоплением грязных страниц и простоями CPU, а также к более равномерной записи на диск. Вместе с тем, сохранение высокой корреляции операционной скорости с объемом записанных блоков и неизменная доля ожиданий по проблемному запросу указывают на необходимость дальнейшей оптимизации на прикладном уровне. ☑️Таким образом, представленная методология обеспечивает объективную основу для принятия решений и позволяет отделить результаты целенаправленных изменений от фоновых колебаний системы, что подтверждает ее ценность как инструмента повседневной практики сопровождения высоконагруженных СУБД.