PG_EXPECTO 9.1 : инцидент PostgreSQL – комплексный анализ: падение операционной скорости,IO-DataFileRead,дефицит памяти

Применение шаблона анализа инцидентов на базе комплекса pg_expecto: количественная оценка отрицательного тренда операционной скорости, доминирование событий ввода‑вывода DataFileRead (>99 % ожиданий), критический уровень свободной RAM (<5 %), избыточная активность автовакуума (644–653 операций/час) и высокая корреляция контекстных переключений с user time (r = 0,84)

Ключевые паттерны деградации производительности СУБД: падение операционной скорости, IO-доминирование, дефицит памяти и избыточная активность автовакуума

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

Шаблон анализа инцидента

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

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

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

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

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

1. Сводный отчет по метрикам СУБД и ОС

Формат txt

Формат html

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

Формат txt

Формат html

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

• Версия PostgreSQL: 15.15 (x86_64, AstraLinuxSE 8.3.0-6)
• Период анализа: тестовый отрезок (12:08–13:08) и инцидент (13:08–14:08) 2026-05-21
• Аппаратная конфигурация: 16 vCPU (KVM, Intel Xeon Skylake), RAM 31.33 ГБ
• Дисковые устройства: /data – vdd (2 ТБ), /wal – vdb (200 ГБ), /log – vdc (100 ГБ)
• Ключевые параметры PostgreSQL: shared_buffers = 8026 МБ, effective_cache_size = 24078 МБ, work_mem = 8 МБ, random_page_cost = 1.1, checkpoint_timeout = 15 min, autovacuum_naptime = 1 s, autovacuum_vacuum_scale_factor = 0.01

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

1. Падение операционной скорости (SPEED)

• Тезис: Операционная скорость СУБД устойчиво снижается во времени (отрицательный тренд с R² ≥ 0.9), в период инцидента падение составило 11–18% по сравнению с тестовым отрезком.
• Способ подтверждения: Линии регрессии SPEED по t: тест – угол -43.42, R²=0.90; инцидент – угол -44.10, R²=0.94. Сравнение min/median/max SPEED (тест: 126355/128806/134126, инцидент: 103105/113899/126355).
• Способ опровержения: Если бы R² был ниже 0.2 или угол наклона был близок к нулю.
• Метка достоверности: Подтверждено

2. Доминирование ожиданий IO (DataFileRead)

• Тезис: 99%+ всех ожиданий в обоих периодах приходятся на событие IO/DataFileRead. Пять запросов дают более 50% всех ожиданий IO. Лидирующий запрос -5253757977388312940 в инциденте увеличил долю ожиданий с 23.7% до 30.7% при сокращении числа вызовов на 57%.
• Способ подтверждения: Диаграммы Парето по WAIT_EVENT и QUERYID. Сравнение CALLS и WAITINGS для указанного queryid.
• Способ опровержения: Если бы распределение ожиданий было равномерным или доминировали другие типы (Lock, LWLock).
• Метка достоверности: Подтверждено

3. Критически низкий уровень свободной оперативной памяти

• Тезис: Свободная RAM постоянно составляет менее 5% (≈615–654 МБ из 31 ГБ) – аварийный уровень. Свопинг отсутствует (swap_si/so = 0).
• Способ подтверждения: Относительные показатели vmstat: free — свободная RAM (% менее 5%) = 100% ALARM. Значения free: min 615 МБ, max 654 МБ.
• Способ опровержения: Если бы свободной памяти было >5% или наблюдался свопинг.
• Метка достоверности: Подтверждено

4. Чрезмерно агрессивная работа autovacuum

• Тезис: Автовакуум выполняется 644–653 раза за час, что в 10–30 раз выше типичных значений. Настройка autovacuum_naptime=1s и scale_factor=0.01 создаёт избыточную нагрузку.
• Способ подтверждения: Статистика autovacuum: 644–653 операций за час, суммарная длительность 45–66 секунд. Параметры из _1.settings.txt.
• Способ опровержения: Если бы число операций было <100 или оставалось много страниц после вакуума.
• Метка достоверности: Подтверждено (частота), Вероятно (агрессивность настройки).

5. Высокая корреляция контекстных переключений с user time

• Тезис: В инциденте контекстные переключения (cs) сильно коррелируют с user time (us): r=0.8446, R²=0.71. Это указывает на возможную проблему в приложении (resource contention, частые системные вызовы).
• Способ подтверждения: Корреляционный анализ vmstat (разделы 2.1, 2.2 в инциденте). Модель признана хорошей (R²=0.71).
• Способ опровержения: Если бы корреляция cs-us была слабой (r<0.5) или p-value >0.05.
• Метка достоверности: Подтверждено

6. Неэффективное использование кэша при чтении

• Тезис: Несмотря на большой кэш (page cache ~23.7 ГБ, effective_cache_size 24 ГБ), запросы регулярно выполняют чтение с диска (DataFileRead). Корреляции cache с rps и rMBps очень высоки (r≈0.79), но модель оценивается как «не эффективное использование памяти», что предполагает низкий hit ratio.
• Способ подтверждения: Разделы кэширования ввода-вывода для vdd в инциденте: r=0.7847 (cache-rps), r=0.7921 (cache-rMBps). Вывод о неэффективности взят из отчёта.
• Способ опровержения: Если бы hit ratio (blks_hit/blks_read) был >99%.
• Метка достоверности: Подтверждено для корреляций, Вероятно для вывода о низком hit ratio (прямых метрик нет).

7. Высокое количество отменённых запросов (query_canceled)

• Тезис: За час фиксируется 131–183 отменённых запроса (код 57014). Причина неясна – таймауты (statement_timeout=8h слишком велик) или ручная отмена.
• Способ подтверждения: Таблицы ошибок СУБД: тест – 183, инцидент – 131.
• Способ опровержения: Если бы query_canceled отсутствовал или был <10.
• Метка достоверности: Подтверждено для цифр, Предположение для причины.

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

1. Оптимизация проблемных запросов

• Тезис: Необходимо получить планы выполнения запросов с queryid -5253757977388312940, 2634173235918444900 и другими из топ-10 по ожиданиям, проверить использование индексов, собрать свежую статистику.
• Способ подтверждения: Выполнить EXPLAIN (ANALYZE, BUFFERS) для этих запросов, сравнить с ожидаемым планом.
• Способ опровержения: Если планы покажут эффективное использование индексов и высокий hit ratio буферов.
• Метка достоверности: Вероятно (требуются планы запросов).

2. Снижение агрессивности autovacuum

• Тезис: Увеличить autovacuum_naptime с 1 с до 10–30 с, пересмотреть autovacuum_vacuum_scale_factor (возможно, увеличить до 0.05–0.1) или настроить индивидуально для больших таблиц.
• Способ подтверждения: Изменить параметры в postgresql.conf или через ALTER SYSTEM, перезагрузить конфигурацию, наблюдать за частотой автовакуума и нагрузкой.
• Способ опровержения: Если после изменения частота автовакуума не снизится или возрастут «оставленные страницы».
• Метка достоверности: Вероятно (нет прямых доказательств вреда, но общепринятая практика).

3. Освобождение оперативной памяти и диагностика утечек

• Тезис: Определить процессы, потребляющие память (RSS), проверить нет ли утечек в PostgreSQL или приложениях. При необходимости ограничить page cache через vm.drop_caches только для диагностики.
• Способ подтверждения: Использовать top, ps, smem, pg_stat_statements для анализа. Для очистки кэша: echo 3 > /proc/sys/vm/drop_caches .
• Способ опровержения: Если после очистки кэша свободная память вернётся к нормальному уровню (>5%) и останется стабильной.
• Метка достоверности: Вероятно (требуется мониторинг RSS).

4. Снижение контекстных переключений

• Тезис: Профилировать системные вызовы (perf, pg_stat_kcache), сократить количество активных соединений через пулер (pgbouncer), оптимизировать приложение для уменьшения конкурентного доступа.
• Способ подтверждения: Замеры cs и us после внедрения пулера или оптимизации кода.
• Способ опровержения: Если корреляция cs-us останется высокой (>0.7) и R² >0.6.
• Метка достоверности: Предположение (требуется профилирование).

5. Проверка эффективности кэша и hit ratio

• Тезис: Собрать метрики pg_stat_database.blks_hit и blks_read, вычислить hit ratio. Если он <99%, проанализировать запросы с низкой эффективностью, рассмотреть увеличение effective_cache_size (уже 75% от RAM) или добавление индексов.
• Способ подтверждения: Запрос к pg_stat_database. Сравнение hit ratio с эталоном (99%).
• Способ опровержения: Если hit ratio ≥99%.
• Метка достоверности: Предположение (данных hit ratio нет в отчёте).

Необходимая дополнительная информация для продолжения анализа и оптимизации

Для перехода от предположений к подтверждённым действиям требуется собрать следующие данные:

1. Планы выполнения запросов для queryid из топ-10 по ожиданиям (особенно -5253757977388312940, 2634173235918444900, -6085056219199734483).
   Метка: Неизвестно (отсутствуют).
2. Метрики hit ratio буферного кэша: pg_stat_database.blks_hit и blks_read за тот же период.
   Метка: Неизвестно.
3. Логи PostgreSQL с деталями отменённых запросов (query_canceled) – какое приложение инициировало отмену, какой запрос выполнялся.
   Метка: Неизвестно.
4. Подробная статистика по автовакууму – какие таблицы вызывают наибольшее число операций, их размеры, процент мёртвых строк (pg_stat_user_tables).
   Метка: Неизвестно.
5. RSS процессов PostgreSQL – есть ли утечки памяти или необоснованный рост потребления.
   Метка: Неизвестно.
6. Данные о сетевых задержках и количестве соединений (pg_stat_activity с state, usename, application_name).
   Метка: Неизвестно.

Заключение

Основная проблема производительности – не в дисковой подсистеме или CPU, а в неэффективных запросах, вызывающих постоянное чтение с диска DataFileRead даже при наличии большого кэша. Инфраструктурные проблемы включают критическую нехватку свободной памяти и чрезмерно агрессивный автовакуум. Для устранения падения операционной скорости первоочередные действия: оптимизация топ-запросов, смягчение настроек автовакуума, диагностика памяти и hit ratio. Без планов запросов и метрик буферного кэша дальнейшие точные рекомендации невозможны.

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

Проведённый анализ подтвердил семь ключевых технических проблем. Во‑первых, операционная скорость СУБД демонстрирует устойчивый отрицательный тренд как в тестовом периоде (угол -43,42; R² = 0,90), так и в период инцидента (угол -44,10; R² = 0,94) с падением медианного значения с 128 806 до 113 899 условных единиц (-11,6 %).

Во‑вторых, более 99 % всех ожиданий в обоих интервалах приходится на событие ввода‑вывода DataFileRead, при этом пять запросов генерируют свыше 50 % ожиданий, а лидирующий queryid -5253757977388312940 увеличил долю с 23,7 % до 30,7 %.

В‑третьих, уровень свободной оперативной памяти критически низок — менее 5 % (615–654 МБ из 31 ГБ) при полном отсутствии свопинга.

В‑четвёртых, автовакуум выполняется 644–653 раза в час, что в 10–30 раз выше типовых значений из‑за настроек autovacuum_naptime = 1 с и scale_factor = 0,01.

В‑пятых, выявлена сильная корреляция между контекстными переключениями (cs) и user time (us): r = 0,8446, R² = 0,71, что указывает на конкурентные конфликты в приложении.

В‑шестых, несмотря на объём page cache ≈23,7 ГБ и effective_cache_size = 24 ГБ, наблюдается неэффективное использование кэша при чтении с диска, о чём свидетельствуют высокие корреляции cache с rps (r = 0,78) и rMBps (r = 0,79).

В‑седьмых, зафиксировано 131–183 отменённых запроса (код 57014) в час при таймауте statement_timeout = 8 ч, что требует выяснения причины (приложение или ручные отмены).

Совокупность этих факторов создаёт системное падение производительности, локализованное на уровне неэффективных запросов и недостаточной свободной памяти.

Послесловие

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

Для перехода от подтверждённых тезисов к точным рекомендациям необходимо собрать дополнительную информацию, которая на момент анализа отсутствовала: планы выполнения запросов (EXPLAIN ANALYZE BUFFERS) для queryid из топ‑10 по ожиданиям, метрики буферного кэша pg_stat_database (blks_hit / blks_read), логи PostgreSQL с деталями отменённых запросов, данные по автовакууму на уровне таблиц (pg_stat_user_tables), RSS процессов и сетевую статистику соединений.

Без этих данных любые действия по оптимизации сохраняют элемент предположительности.

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