PG_EXPECTO 9.0 : инцидент PostgreSQL – тестирование новой системной инструкции на практическом кейсе производительности

Эмпирическая верификация системной инструкции PG_EXPECTO 9.0 на основе корреляционного анализа инцидента производительности PostgreSQL 15.15: диагностика доминирования ожиданий ввода-вывода, оценка эффективности кэширования и верификация гипотез о влиянии параметров autovacuum и work_mem.

Корреляционный анализ ожиданий ввода-вывода PostgreSQL: от метрик к диагностике

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

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

Предисловие

В рамках настоящей работы проведено тестирование системной инструкции версии 9.0 комплекса PG_EXPECTO — методологической основы, реализующей формализованные процедуры анализа производительности PostgreSQL с использованием меток достоверности (Подтверждено, Вероятно, Предположение, Неизвестно), трёхэтапной оценки ожиданий (статистическая значимость, взвешенная корреляция ожиданий, коэффициент детерминации), протокола обработки противоречий и диагностики инженерных ошибок. Целью эксперимента являлась валидация инструкции на реальном инциденте СУБД PostgreSQL 15.15, развёрнутой на виртуальной машине под управлением AstraLinux SE. Исходными данными выступали два часовых временных отрезка: тестовый (06:20–07:20) и инцидентный (07:20–08:20), включавшие комплексные корреляционные метрики СУБД и утилиты vmstat. Применение инструкции позволило формализовать диагностический процесс, выявить ключевые причинно-следственные связи и предложить целевые рекомендации без привлечения инфраструктурных артефактов.

Задача

Тестирование системной инструкции 9.0 на реальном инциденте производительности СУБД

Подробнее о системной инструкции версии 9.0

Входные данные:

НАСТРОЙКИ СУБД и VM

ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД: КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ СУБД и VMSTAT

ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД: КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ СУБД и VMSTAT

Инструкция PG_EXPECTO 9.0

Подготовка исходного сводного отчета

Исходный сводный отчет

Подготовка аналитического сводного отчета

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

Общая информация

• Период теста: 2026-05-14 06:20 – 07:20 (1 час)
• Период инцидента: 2026-05-14 07:20 – 08:20 (1 час)
• Версия PostgreSQL: 15.15 на AstraLinuxSE (gcc 8.3.0)
• Аппаратная конфигурация: 8 vCPU (Intel Xeon Skylake, KVM), RAM 15.61 GB, диски vdd (2 TB, /data) и vdb (200 GB, /wal)
• Ключевые параметры СУБД:
• shared_buffers = 4003 MB
• effective_cache_size = 12011 MB
• work_mem = 8 MB
• random_page_cost = 1.1
• checkpoint_timeout = 15 min
• autovacuum_naptime = 1 s
• autovacuum_vacuum_scale_factor = 0.01
• Статус инфраструктуры: загрузка CPU, памяти и дисков в норме, свопинг отсутствует.

Ключевые проблемы СУБД и инфраструктуры

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

• Доминирование ожиданий ввода-вывода (IO) – более 99% всех ожиданий приходится на DataFileRead (чтение страниц с диска). В инциденте взвешенная корреляция ожиданий (ВКО) для IO достигла 0.99, R² = 0.99 – исключительно сильная связь.
• Падение операционной скорости при росте ожиданий – в тестовом периоде скорость росла (+42.45), ожидания снижались; в инциденте скорость падала (-43.52), ожидания росли (+37.77). Корреляция SPEED–WAITINGS = -0.72 (значимая).
• Изменение профиля нагрузки – в инциденте лидирующим по ожиданиям стал запрос с queryid 2499171716316696283 (16.51% всех ожиданий), тогда как в тесте первым был 2000412135465383132 (24.56%). Абсолютное число ожиданий DataFileRead снизилось с 6187 до 5845, но распределение изменилось.
• Низкая эффективность кэширования – несмотря на shared_buffers 4 ГБ и effective_cache_size 12 ГБ, рабочий набор данных не помещается в кэш, либо запросы не используют индексы. В инциденте появилась слабая корреляция между кэшем ОС (cache) и чтениями с диска (rps): r=0.4546, R²=0.21, что указывает на высокий miss ratio.
• Рост временных файлов (temp_files) – количество временных файлов выросло на 65% (20 → 33), объём на 29% (144 → 186 МБ). При work_mem = 8 МБ это признак сортировок или хеш-таблиц, не помещающихся в память.
• Агрессивная настройка autovacuum – autovacuum_naptime = 1s, scale_factor = 0.01. Количество операций autovacuum выросло на 10% (640 → 704) при снижении общей длительности (75 с → 56.7 с). Нагрузка от autovacuum незначительна, но частые запуски потенциально создают фоновые чтения.
• Ошибки соединения и отмены запросов – число connection_failure увеличилось с 1 до 3, query_canceled снизилось с 36 до 30. Остальные ошибки приложения (unique_violation, undefined_table) стабильны.

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

• Не выявлено – все метрики ОС в пределах нормы:
• Загрузка CPU: us+sy ≈ 12–13%, idle 85–87%, iowait 1%.
• Дисковая подсистема: %util < 5%, задержки чтения < 0.7 мс, записи < 3.3 мс, очередь < 0.22.
• Память: свободная RAM менее 5% (нормально, т.к. занята дисковым кэшем ~10.5 ГБ), свопинг отсутствует.
• Переключения контекста (cs) ≤ 6800/с, прерывания (in) ≤ 7100/с – значительно ниже порога проблем (50k/ядро).
• Единственный косвенный признак – в инциденте выросла корреляция cs–in (r=0.9106, R²=0.83), но абсолютные значения cs низкие, поэтому race condition не подтверждается.

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

Оптимизация СУБД

• Детальный анализ и оптимизация запросов-лидеров – получить планы выполнения для queryid:
• 2499171716316696283 (наибольший прирост ожиданий в инциденте)
• 2000412135465383132
• 2980967133393125256
• -5811563203629148996
• 7488933670971969914
• Проверить использование индексов, наличие последовательных сканирований, фильтрацию.
• Создание или доработка индексов – для таблиц, участвующих в запросах с DataFileRead. Оценить коэффициент попадания в кэш (hit ratio) из pg_stat_database.
• Увеличение work_mem – с 8 МБ до 32–64 МБ для снижения использования temp_files. Контролировать изменение количества и объёма временных файлов.
• Смягчение параметров autovacuum – увеличить autovacuum_naptime с 1 с до 5–10 с, оставив scale_factor = 0.01. Это снизит частоту запусков без потери эффективности (удаление мёртвых страниц даже выросло в инциденте).
• Мониторинг hit ratio и эффективности кэша – настроить сбор pg_stat_database (blks_hit, blks_read) для вычисления реального попадания в shared buffers.

Оптимизация инфраструктуры

• Инфраструктурные изменения не требуются – все системные метрики в норме.
• Если после оптимизации запросов проблема сохранится, рассмотреть увеличение оперативной памяти для расширения дискового кэша ОС (текущий cache ~10.5 ГБ, effective_cache_size 12 ГБ – можно увеличить до 16–24 ГБ при добавлении RAM).

Необходимая дополнительная информация для продолжения анализа и оптимизации производительности СУБД и инфраструктуры

• Планы выполнения (EXPLAIN ANALYZE) для каждого из перечисленных queryid, включая фактические объёмы чтения (buffers read), типы сканирования, использование индексов.
• Схемы таблиц и индексов – для таблиц, участвующих в проблемных запросах: определение первичных ключей, внешних ключей, существующих индексов и их кардинальности.
• Статистика pg_stat_database за оба периода – hit ratio (blks_hit / (blks_hit + blks_read)), чтобы оценить реальную эффективность shared_buffers.
• Размеры наиболее крупных таблиц и индексов (pg_total_relation_size) – для оценки соответствия рабочего набора данных объёму доступной памяти.
• Настройки планировщика – effective_io_concurrency = 300 (уже установлен), но нет данных о фактическом параллелизме операций ввода-вывода. Желательно получить распределение wait_event = DataFileRead по отдельным файлам.
• Логи autovacuum – за период инцидента (параметр log_autovacuum_min_duration = 0 должен давать детальные сообщения). Нужно проверить, какие именно таблицы обрабатывались и были ли длительные операции.
• Данные pg_stat_user_tables – количество живых и мёртвых строк, время последнего autovacuum/autoanalyze, чтобы оценить необходимость ручного vacuum.
• Профилирование системных вызовов – при подозрении на высокую корреляцию cs–in, хотя абсолютные значения низкие, можно выполнить perf или strace для выявления избыточных прерываний.
• Сетевая статистика – отсутствует в исходных данных. При наличии распределённой архитектуры (приложение не на том же хосте) нужны задержки сети, ошибки TCP, перегрузки.

Общий технический итог

В инцидентном периоде зафиксировано доминирование ожиданий ввода-вывода типа DataFileRead, составивших более 99% всех ожиданий, при взвешенной корреляции с системными метриками ВКО = 0,99 (R² = 0,99) — что по шкале инструкции соответствует «критическому значению, требующему немедленного анализа». Выявлено разнонаправленное изменение операционной скорости и ожиданий: в тестовом отрезке скорость возросла на 42,45 ед. при снижении ожиданий, тогда как в инциденте скорость снизилась на 43,52 ед. при росте ожиданий на 37,77 ед., корреляция SPEED–WAITINGS достигла –0,72 (R² = 0,52, модель «приемлемая»). Определён сдвиг профиля нагрузки: лидирующим по ожиданиям стал запрос с queryid 2499171716316696283 (16,51% всех ожиданий), ранее не входивший в число основных. Зафиксирован рост числа временных файлов на 65% (с 20 до 33) и их объёма на 29% (со 144 до 186 МБ) при work_mem = 8 МБ, что по критериям инструкции квалифицируется как Вероятно (косвенный признак неадекватного параметра). Частота запусков autovacuum возросла на 10% при снижении суммарной длительности, что позволило отвергнуть гипотезу о значимом вкладе autovacuum в инцидент. Инфраструктурные метрики (загрузка CPU, iowait, использование дисков, переключения контекста) остались в пределах нормы, что исключило ресурсные проблемы ОС. На основе полученных данных сформированы рекомендации, включающие детальный разбор планов выполнения проблемных запросов, увеличение work_mem до 32–64 МБ, смягчение autovacuum_naptime с 1 с до 5–10 с и мониторинг hit ratio через pg_stat_database.

Послесловие

Проведённая верификация подтвердила работоспособность и диагностическую полноту системной инструкции PG_EXPECTO 9.0 в условиях реального инцидента производительности PostgreSQL. Инструкция позволила не только корректно идентифицировать основной фактор деградации (ожидания ввода-вывода), но и отсечь ложные гипотезы, связанные с инфраструктурой.

В процессе анализа применялись исключительно процедуры, явно закреплённые в инструкции: маркировка утверждений метками достоверности, трёхэтапный анализ ожиданий с пороговыми значениями ВКО и R², проверка артефактов агрегации суммируемых метрик, протокол обработки противоречий (с иерархией доверия pg_stat_* > vmstat > косвенные корреляции), а также диагностика инженерных ошибок по категориям (неадекватные параметры, утечки ресурсов, race conditions).

Отсутствие ошибок в выводах и соответствие полученных результатов предписанным алгоритмам свидетельствует о пригодности инструкции для аналитики инцидентов.