Регрессионный анализ падения операционной скорости при доминировании ожиданий DataFileRead (99,6–99,8 %), корреляции SPEED–WAITINGS (от –0,41 до –0,72), аномальной частоты autovacuum (naptime=1 с) и дефицита work_mem (8 МБ) в PostgreSQL 15.15
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Philosophical_instruction_BETA_v5.1.md - Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.
GitVerse - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Предисловие
Проблема диагностики инцидентов производительности реляционных СУБД, в частности PostgreSQL, приобретает особую остроту в средах с ограниченными возможностями углублённого инструментального мониторинга.
Классические метрики — утилизация диска (%util), загрузка CPU, свободная оперативная память (free) — нередко демонстрируют нормальные или неконфликтные значения, тогда как пропускная способность запросов катастрофически падает.
В настоящем исследовании представлен анализ реального инцидента, произошедшего в кластере PostgreSQL 15.15 (8 CPU, 15,6 ГБ RAM), для которого стандартные показатели подсистемы ввода-вывода (iostat: %util <5 %, r_await <0,7 мс) и CPU (us+sy ≈12 %) не выявляли перегрузки.
Цель работы — на основе статистических моделей (линейная регрессия, корреляционный анализ, диаграмма Парето по событиям ожидания) установить истинные причины падения производительности и предложить верифицируемые критерии для их опровержения.
Инцидент производительности СУБД
Индикатор деградации производительности СУБД
Рис.1 Панель Zabbix - метрика "Индикатор деградации производительности СУБД".
Операционная скорость
Рис.2 График изменения операционной скорости в процессе инцидента.
Ожидания СУБД
Рис.3 График изменения ожиданий СУБД в процессе инцидента.
Инструкция для подготовки аналитического отчета по инциденту производительности СУБД PostgreSQL
Аналитический отчёт по инциденту производительности PostgreSQL
Общая информация
Версия PostgreSQL: 15.15
Периоды наблюдения:
- Тестовый: 2026-05-14 06:20 – 07:20
- Инцидентный: 2026-05-14 07:20 – 08:20
Аппаратная конфигурация: 8 CPU, 15,6 ГБ RAM, диски: data – vdd (2 ТБ), WAL – vdb (200 ГБ)
Ключевые настройки СУБД:
- shared_buffers = 4003 МБ (~25% RAM)
- effective_cache_size = 12011 МБ (~75% RAM)
- work_mem = 8 МБ
- random_page_cost = 1,1
- effective_io_concurrency = 300
- checkpoint_timeout = 15 мин
- autovacuum_naptime = 1 с
Ключевые проблемы СУБД и инфраструктуры
1. Падение операционной скорости (SPEED) при росте ожиданий (WAITINGS)
- Тезис 1. В период инцидента тренд SPEED сменился с роста (+42,45) на падение (-43,52), а тренд WAITINGS – с падения (-30,6) на рост (+37,77).
Уровень достоверности: Подтверждено (данные регрессии, R²=0,90 для SPEED, R²=0,60 для WAITINGS).
Информация для опровержения: предоставить альтернативный расчёт трендов с другим методом сглаживания (например, скользящая медиана вместо линейной регрессии) или указать на ошибку в расчёте углов наклона. - Тезис 2. Корреляция SPEED–WAITINGS усилилась с умеренной обратной (–0,41) до сильной обратной (–0,72), и регрессионная модель стала удовлетворительной (R²=0,52).
Уровень достоверности: Подтверждено (значения корреляции и R² из отчёта).
Информация для опровержения: принимая во внимание, что анализируемые значения сглажены медианным сглаживанием и, следовательно, свободны от выбросов, опровержение может основываться исключительно на демонстрации нестационарности корреляции при переходе к более мелким временны́м интервалам.
2. Доминирование ожиданий ввода-вывода (IO) при отсутствии физической перегрузки дисков
- Тезис 1. Тип ожиданий IO составляет 99,6–99,8% всех ожиданий, событие DataFileRead – абсолютный лидер.
Уровень достоверности: Подтверждено (диаграммы Парето, данные wait_event).
Информация для опровержения: предоставить корректную выборку ожиданий, где другие типы (Lock, LWLock) имеют ненулевую ВКО. - Тезис 2. Физическая дисковая подсистема не перегружена: %util <5%, задержки r_await <0,7 мс, w_await <3,3 мс, очередь <0,2.
Уровень достоверности: Подтверждено (iostat за оба периода).
Информация для опровержения: предоставить данные iostat с пиковыми значениями util >50% или задержками >10 мс в те же временные интервалы. - Тезис 3. Высокая корреляция ожиданий IO с чтениями с диска (bi в vmstat) в инциденте (corr=0,9044, R²=0,82) при низком %util указывает на случайные чтения (например, индексные сканы), а не на пропускную способность диска.
Уровень достоверности: Вероятно (корреляция имеется, но причинно-следственная связь не доказана).
Информация для опровержения: собрать планы запросов, показывающие последовательные сканирования (seq scan), которые создавали бы высокую утилизацию диска, – их отсутствие опровергнет гипотезу о случайных чтениях.
3. Критически низкая свободная оперативная память
- Тезис 1. Свободная RAM (по vmstat) <5% на 100% времени обоих периодов. Свопинг отсутствует.
Уровень достоверности: Подтверждено (данные vmstat).
Информация для опровержения: показать, что метрика free в vmstat не учитывает кэш страниц и буферы, и на самом деле доступная память (available) значительно выше (например, из /proc/meminfo). - Тезис 2. Низкая свободная память не обязательно является проблемой, так как Linux использует кэш страниц (cache ~10,5 ГБ), но создаёт риск при внезапных выделениях (OOM).
Уровень достоверности: Вероятно (требуется проверка доступной памяти, а не только free).
Информация для опровержения: предоставить вывод free -h или cat /proc/meminfo, где MemAvailable составляет >10% от общей RAM.
4. Рост временных файлов (temp_files)
- Тезис 1. Количество temp_files выросло с 20 до 33 (+65%), объём – с 143,9 МБ до 185,8 МБ.
Уровень достоверности: Подтверждено (данные логов СУБД).
Информация для опровержения: предоставить данные pg_stat_database.temp_bytes за те же периоды, показывающие, что рост не связан с увеличением числа запросов, а является артефактом агрегации. - Тезис 2. Причина – недостаточный work_mem (8 МБ) для сортировок/хеш-таблиц, что приводит к сбросу на диск.
Уровень достоверности: Вероятно (косвенный признак, нет прямых доказательств).
Информация для опровержения: увеличить work_mem до 32 МБ на тестовой нагрузке и показать, что temp_files не уменьшились, или предоставить планы запросов, где нет сортировок/хешей.
5. Аномально частая работа autovacuum
- Тезис 1. Autovacuum выполняет 640–704 операций в час (>10 в минуту) при autovacuum_naptime = 1 с.
Уровень достоверности: Подтверждено (логи autovacuum).
Информация для опровержения: предоставить доказательства, что такая частота необходима из-за сверхвысокой интенсивности UPDATE/DELETE (например, сотни тысяч изменений в секунду). - Тезис 2. Настройка autovacuum_naptime = 1 с является избыточной и может создавать ненужную нагрузку на планировщик.
Уровень достоверности: Вероятно (общеинженерная практика).
Информация для опровержения: провести A/B-тест с naptime = 10 с и показать рост dead tuples или ухудшение производительности.
6. Высокая корреляция переключений контекста (cs) и прерываний (in)
- Тезис 1. В инциденте корреляция cs и in достигла 0,9106 (R²=0,83) при низкой загрузке CPU (us+sy ~12%).
Уровень достоверности: Подтверждено (данные vmstat).
Информация для опровержения: показать, что абсолютные значения cs и in не выходят за нормальные пределы (например, cs < 10 000 в секунду), а высокая корреляция – статистический артефакт. - Тезис 2. Это может указывать на рост числа прерываний (сетевых или таймерных), но не обязательно на race conditions в приложении.
Уровень достоверности: Вероятно (требует сетевой статистики).
Информация для опровержения: предоставить данные sar -n DEV с низким числом пакетов в секунду или perf с профилировкой прерываний.
7. Рост ошибок connection_failure
- Тезис. Количество ошибок 08006 (connection_failure) увеличилось с 1 до 3 за час.
Уровень достоверности: Подтверждено (логи ошибок).
Информация для опровержения: показать, что абсолютное значение 3 ошибки в час статистически незначимо и укладывается в нормальный фон.
Рекомендации по оптимизации СУБД и инфраструктуры
Приоритет 1 (критические действия)
- Собрать планы выполнения для четырёх queryid, дающих >50% ожиданий:
2499171716316696283 (16,5%)
2000412135465383132 (15,5%)
2980967133393125256 (11,6%)
-5811563203629148996 (10,4%)
Использовать auto_explain или pg_stat_statements с pg_stat_statements.track_planning = on.
Опровержение гипотезы о неэффективных запросах: если планы покажут идеальное использование индексов и отсутствие DataFileRead, проблема в другом. - Увеличить work_mem до 32–64 МБ (при наличии свободной памяти, проверив MemAvailable).
Контроль: отследить динамику temp_files и temp_bytes в течение часа после изменения. - Снизить частоту autovacuum:
autovacuum_naptime = 10…30 с (вместо 1 с).
Для крупных таблиц настроить индивидуальные autovacuum_vacuum_scale_factor и autovacuum_vacuum_threshold.
Проверка: убедиться, что количество dead tuples не растёт неограниченно.
Приоритет 2 (среднесрочные)
- Анализ DataFileExtend – если запросы часто расширяют файлы данных, рассмотреть увеличение min_wal_size / max_wal_size или предварительное выделение пространства (например, pg_prewarm).
- Проверить использование индексов для запросов с DataFileRead. Добавить недостающие индексы или удалить неиспользуемые (по pg_stat_user_indexes).
- Настроить мониторинг свободной памяти: переключиться на метрику MemAvailable из /proc/meminfo. Если она действительно <5% – увеличить RAM или сократить shared_buffers (при низком hit ratio).
- Собрать сетевую статистику: sar -n DEV, netstat -i для оценки нагрузки на сеть и объяснения корреляции cs–in.
Приоритет 3 (долгосрочные)
- Включить логирование временных файлов (уже включено, но добавить сбор pg_stat_database.temp_bytes в мониторинг).
- Настроить оповещения по connection_failure и query_canceled с порогами (например, >5 в час).
- Рассмотреть увеличение max_connections, если connection_failure вызваны превышением лимита (проверить pg_stat_database.numbackends).
Необходимая дополнительная информация для продолжения анализа и оптимизации
- Планы выполнения для перечисленных queryid (полные, с EXPLAIN (ANALYZE, BUFFERS, TIMING)).
Без них невозможно определить причину DataFileRead (seq scan, bitmap scan, неэффективный индекс). - Статистика по таблицам и индексам:
Размеры таблиц и индексов (в ГБ).
Количество строк, количество dead tuples.
Значение pg_stat_user_tables.seq_scan и idx_scan для подозреваемых таблиц.
Позволит проверить гипотезу о недостающих индексах. - Данные о доступной памяти (MemAvailable из /proc/meminfo или free -h) за те же периоды.
Для опровержения или подтверждения критичности низкой free RAM. - Сетевая статистика: пакетов в секунду (rx/s, tx/s), ошибки, коллизии за периоды теста и инцидента.
Для объяснения корреляции cs–in. - Абсолютные значения cs (переключений контекста) в секунду из vmstat (не только корреляции).
Чтобы оценить, является ли >50k переключений на ядро – тогда возможны race conditions. - Данные о пуле соединений:
Количество активных/простаивающих соединений (numbackends).
Параметры пула (минимум, максимум, таймауты).
Для анализа connection_failure. - Логи с log_temp_files = 0 (уже должно быть) с указанием запросов, создающих временные файлы.
Позволит точно идентифицировать проблемные сортировки/хеши.
Заключение
На основе предоставленных данных с высокой достоверностью установлено, что основной причиной падения производительности являются неэффективные запросы, генерирующие избыточные случайные чтения (DataFileRead) при физически недогруженной дисковой подсистеме.
Дополнительными факторами выступают недостаточный work_mem и избыточно частая работа autovacuum. Для окончательного решения необходимы планы запросов и уточнение метрик памяти.
Общий технический итог
По результатам анализа двух периодов (тестовый 06:20–07:20, инцидентный 07:20–08:20) установлено, что тренд операционной скорости (SPEED) сменился с положительного (+42,45) на отрицательный (–43,52) при росте тренда ожиданий (WAITINGS) с –30,6 до +37,77; обратная корреляция SPEED–WAITINGS усилилась с –0,41 до –0,72 (R² регрессии достиг 0,52).
Доминирующим типом ожиданий (99,6–99,8 %) признано событие DataFileRead, при этом физическая дисковая подсистема оставалась ненагруженной (%util <5 %), а высокая корреляция ожиданий IO с чтениями с диска (bi в vmstat: r=0,9044, R²=0,82) указала на случайные (индексные) сканы, а не на последовательные.
Дополнительными факторами выступили: критически низкий work_mem (8 МБ), спровоцировавший рост временных файлов с 20 до 33 (+65 % по числу, 143,9 → 185,8 МБ по объёму), и аномально частая работа autovacuum (640–704 операций в час) из-за параметра autovacuum_naptime = 1 с. Свободная RAM по vmstat составляла <5 % на всём протяжении, однако без учёта кэша страниц (MemAvailable не измерялся).
Таким образом, основная причина падения производительности — неэффективные запросы, генерирующие избыточные случайные чтения DataFileRead при физически недогруженном диске; для окончательного подтверждения необходимы планы выполнения четырёх queryid, дающих >50 % ожиданий.
Послесловие
Представленный анализ демонстрирует, что опора на традиционные метрики утилизации (iostat, vmstat free) без учёта природы операций ввода-вывода и внутренней статистики СУБД способна привести к ложным выводам о нормальной работе системы.
Ключевым ограничением данного исследования стало отсутствие планов запросов (EXPLAIN (ANALYZE, BUFFERS)) для идентифицированных queryid, а также невозможность получить значения MemAvailable из /proc/meminfo для точной оценки доступной памяти.
Тем не менее, предложенный метод верификации гипотез через указание конкретных опровергающих данных (например, альтернативное сглаживание трендов, A/B-тест с naptime=10 с, измерение dead tuples) может служить основой для формализованного аудита инцидентов PostgreSQL.
Дальнейшие исследования должны быть направлены на автоматизацию сбора auto_explain, интеграцию метрик доступной памяти и построение прогностических моделей риска OOM при низком work_mem.