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

Сравнительный статистический анализ трендов операционной скорости, ожиданий ввода-вывода, буферизации памяти и пропускной способности дисков в виртуальной среде KVM на примере инцидента PostgreSQL 15.15

Переход от улучшающейся производительности к деградации: влияние медленного ввода-вывода на PostgreSQL в среде KVM.

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

Предисловие

В облачной среде часто возникает феномен: при формально низкой утилизации дисковых устройств (процент занятости, очередь запросов, задержки) наблюдается резкое падение операционной скорости выполнения запросов и рост ожиданий, связанных с чтением данных. Данная работа представляет результаты сравнительного статистического анализа двух временны́х периодов работы PostgreSQL 15.15 на платформе KVM (8 vCPU, 15.6 ГБ RAM): тестового отрезка (06:12–07:12) и отрезка инцидента (07:12–08:12), в течение которого зафиксировано изменение тренда производительности. Использованы методы регрессионного анализа, корреляции (Пирсона), взвешенной корреляции ожиданий (ВКО), а также диаграмма Парето по типам событий ожидания и идентификаторам запросов.

Цель работы – идентификация доминирующего фактора деградации, количественная оценка его влияния и формулировка приоритетных рекомендаций по оптимизации на уровне СУБД, операционной системы и гипервизора.

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

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

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

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

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

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

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

Формат txt

Формат html

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

Формат txt

Формат html

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

Периоды наблюдения:

• Тестовый отрезок: 2026-05-18 06:12 – 07:12
• Инцидент: 2026-05-18 07:12 – 08:12

Версия PostgreSQL: 15.15 on x86_64

Виртуализация: KVM, 8 vCPU, 15.61 GB RAM

Дисковые устройства: vdd (2TB, данные), vdb (200GB, WAL)

Краткое описание меток

• Подтверждено – тезис непосредственно следует из представленных в отчёте метрик, расчётов или математических соотношений (например, значения MIN/MAX, коэффициенты корреляции, R²).
• Вероятно – вывод основан на косвенных признаках, корреляциях или общеизвестных практиках, но не подтверждён прямыми данными (например, отсутствуют планы запросов или замеры длительности ожиданий).
• Предположение – гипотеза, для проверки которой необходимы дополнительные данные либо данные отсутствуют в отчёте (например, причина изменения паттерна буферизации).
• Неизвестно – термин или метрика не фигурируют в отчёте, значение невозможно определить.

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

Проблема 1: Падение операционной скорости и рост ожиданий в инциденте

• Тезис: В инциденте тренд операционной скорости (SPEED) сменился с роста (+34.83 за точку) на падение (–32.85), а тренд ожиданий (WAITINGS) – со снижения (–38.65) на рост (+40.55), что указывает на деградацию производительности.
• Способ подтверждения: Коэффициенты наклона линий регрессии по t (R²=0.48 и 0.64 в тесте; 0.42 и 0.73 в инциденте) из раздела «Анализ трендов SPEED и WAITINGS».
• Способ опровержения: Демонстрация противоположных знаков наклона при стабильной или улучшающейся нагрузке, либо отсутствие статистически значимого тренда.
• Метка: Подтверждено

Проблема 2: Дисковая подсистема является узким местом при аномально низкой пропускной способности

• Тезис: Производительность СУБД в инциденте ограничена пропускной способностью дисков (vdd и vdb): корреляция SPEED с MBps достигает r=0.7476 (R²=0.56), при этом абсолютная скорость ввода-вывода не превышает 0.6 MB/s, что аномально низко для современных хранилищ.
• Способ подтверждения: Пункт 4.2 раздела «Корреляция операционной скорости и метрик производительности дискового устройства» (iostat rMB/s, wMB/s, коэффициенты корреляции).
• Способ опровержения: Рост пропускной способности диска до значений >10 MB/s при аналогичной нагрузке, либо исчезновение значимой корреляции SPEED–MBps.
• Метка: Подтверждено

Проблема 3: Доминирование ожиданий типа DataFileRead (IO) при формально ненагруженном диске

• Тезис: Более 99% ожиданий СУБД приходится на событие DataFileRead, однако утилизация диска vdd составляет всего 3%, очередь <0.2, задержки r_await=0.6 мс. Диск не перегружен в классическом смысле, но каждое чтение становится узким местом из-за крайне низкой пропускной способности.
• Способ подтверждения: Таблицы Парето по wait_event (доля IO 99.87% в инциденте), iostat (%util=3%, r_await=0.6ms, avgqu-sz<0.2).
• Способ опровержения: Рост утилизации диска >50%, увеличение очереди >1, либо рост r_await >10 мс.
• Метка: Подтверждено

Проблема 4: Агрессивные настройки autovacuum (naptime=1с) создают избыточную нагрузку

• Тезис: Частота запусков autovacuum чрезмерно высока (593–654 операций в час) при очень малом объёме работы (удалено ~200 страниц). Параметр autovacuum_naptime = '1s' и scale_factor = 0.01 неадекватны для виртуального окружения с медленным диском.
• Способ подтверждения: Статистика autovacuum из отчёта (количество операций, удалённые страницы), параметры из _1.settings.txt.
• Способ опровержения: Анализ таблиц, показывающий высокий процент мёртвых кортежей (>10%) в каждом цикле, оправдывающий такую частоту.
• Метка: Вероятно (нет прямых данных о мёртвых кортежах, но косвенные признаки сильные)

Проблема 5: Длительные контрольные точки на грани превышения таймаута

• Тезис: Среднее время записи одной контрольной точки составляет ~13.5 минут при checkpoint_timeout = 15 минут. Это приводит к состоянию «checkpoint in progress» почти постоянно, увеличивая количество грязных буферов и пики I/O.
• Способ подтверждения: Расчёт: общее время записи 3239 сек / 4 контрольные точки = 809.75 сек ≈ 13.5 мин. Параметр checkpoint_timeout из настроек.
• Способ опровержения: Данные pg_stat_bgwriter о buffers_backend, показывающие, что контрольные точки завершаются задолго до таймаута.
• Метка: Подтверждено (математическое следствие), предположение о негативном влиянии – Вероятно.

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

Рекомендация 1 (СУБД): Снизить агрессивность autovacuum

• Тезис: Необходимо увеличить autovacuum_naptime до 10–30 секунд и повысить autovacuum_vacuum_scale_factor до 0.05–0.1 для больших таблиц, чтобы уменьшить частоту запусков и нагрузку на диск.
• Способ подтверждения: Измерение частоты autovacuum и объёма удалённых страниц после изменения параметров; ожидается снижение числа операций без роста мёртвых кортежей.
• Способ опровержения: Рост мёртвых кортежей или ухудшение производительности запросов после изменения настроек.
• Метка: Вероятно (основано на стандартных рекомендациях и косвенных признаках)

Рекомендация 2 (СУБД): Оптимизировать контрольные точки

• Тезис: Увеличить max_wal_size с 8 GB до 16–32 GB и checkpoint_timeout с 15 до 30 минут, а также снизить checkpoint_completion_target с 0.9 до 0.7 для более равномерной записи грязных буферов.
• Способ подтверждения: Сравнение времени записи контрольных точек и частоты возникновения пиков I/O до и после изменений.
• Способ опровержения: Увеличение времени восстановления после сбоя или рост объёма WAL-файлов без улучшения производительности.
• Метка: Вероятно

Рекомендация 3 (СУБД): Оптимизировать проблемные запросы

• Тезис: Провести анализ queryid -7529817323075806461 (отношение ожиданий к вызовам 3.54) и 2000412135465383132 (лидер по числу вызовов). Проверить индексы, планы запросов, возможность покрывающих индексов для снижения DataFileRead.
• Способ подтверждения: Сбор планов запросов, анализ использования индексов, повторное выполнение с EXPLAIN (BUFFERS, ANALYZE).
• Способ опровержения: Планы покажут эффективное использование индексов и отсутствие последовательных сканирований; проблема окажется на стороне диска.
• Метка: Подтверждено (наличие явных кандидатов), Вероятно (эффективность оптимизации)

Рекомендация 4 (Инфраструктура): Диагностика и ускорение дискового хранилища

• Тезис: Измерить реальную пропускную способность и IOPS с помощью fio на устройствах vdd и vdb. Проверить настройки гипервизора KVM (лимиты QoS, кэш хоста, тип виртуального диска). Рекомендуется использовать кэш write-back и планировщик none/noop.
• Способ подтверждения: Выполнение fio --rw=randread --bs=8k --direct=1 и сравнение с текущей наблюдаемой скоростью (<0.6 MB/s). Если fio покажет нормальные значения (десятки MB/s), проблема в паттерне нагрузки СУБД или настройках ОС.
• Способ опровержения: fio также покажет аномально низкую скорость – тогда требуется вмешательство на уровне инфраструктуры (повышение квот, смена хранилища).
• Метка: Подтверждено (аномально низкая скорость – факт), рекомендация – Вероятно.

Рекомендация 5 (Инфраструктура): Настройка параметров ОС для работы с памятью и I/O

• Тезис: Уменьшить vm.dirty_ratio и vm.dirty_background_ratio для сглаживания пиков записи, проверить планировщик I/O (установить none или noop), а также оценить достаточность свободной памяти (ALARM <5% свободной RAM может быть нормальным при активном использовании кэша страниц, но требуется мониторинг available).
• Способ подтверждения: Сравнение метрик bi, bo, cpu_wa и ожиданий IO после применения настроек; снижение корреляции SPEED–MBps.
• Способ опровержения: Отсутствие изменений или ухудшение показателей.
• Метка: Вероятно (стандартные рекомендации для PostgreSQL на виртуалке)

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

1. Данные о длительности ожиданий (wait event latency)

• Тезис: В отчёте отсутствуют прямые замеры длительности ожиданий (например, из pg_stat_wait_sampling или pg_stat_io). Без этого невозможно отличить частые, но короткие ожидания от редких, но долгих.
• Способ подтверждения: Установка расширения pg_stat_wait_sampling или сбор метрик из PostgreSQL 16+ (pg_stat_io), анализ среднего времени ожидания по событию DataFileRead.
• Способ опровержения: Если после сбора выяснится, что длительность ожиданий мала (<1 мс), то узкое место не в диске, а в другом компоненте.
• Метка: Неизвестно (отсутствует в отчёте)

2. Планы запросов для проблемных queryid

• Тезис: Нет планов выполнения запросов для queryid -7529817323075806461 и 2000412135465383132. Невозможно определить, используют ли они индексы, вызывают ли последовательное чтение или сортировки на диске.
• Способ подтверждения: Включение логирования планов (log_min_duration_statement с выборкой по queryid) или выполнение запросов в тестовой среде с EXPLAIN (BUFFERS, ANALYZE).
• Способ опровержения: Анализ планов покажет, что все чтения идут только через индексы и не требуют сканирования таблиц.
• Метка: Неизвестно

3. Данные о мёртвых кортежах и статистике таблиц

• Тезис: Отсутствует информация о проценте мёртвых кортежей в таблицах, количестве обновлений/удалений. Без этого нельзя окончательно судить о необходимости столь частого autovacuum.
• Способ подтверждения: Запрос к pg_stat_user_tables (поля n_dead_tup, n_live_tup, autovacuum_count). Сравнение с частотой autovacuum.
• Способ опровержения: Обнаружение таблиц с высоким (>10%) и стабильным уровнем мёртвых кортежей, оправдывающее агрессивные настройки.
• Метка: Неизвестно

4. Логи гипервизора KVM (throttling, QoS)

• Тезис: Нет данных о лимитах пропускной способности или IOPS на гипервизоре. Невозможно подтвердить, что именно виртуализация ограничивает скорость диска до 0.6 MB/s.
• Способ подтверждения: Проверка конфигурации виртуальной машины (qemu‑cmdline, libvirt domain XML), метрик гипервизора (например, perf kvm stat, iostat -x на хосте), логов на предмет throttling или iops limit.
• Способ опровержения: Обнаружение, что ограничение на гипервизоре отсутствует, и проблема в неоптимальной конфигурации гостевой ОС или СУБД.
• Метка: Неизвестно

5. Измерение доступной памяти (available) вместо свободной (free)

• Тезис: В отчёте используется метрика free (свободная RAM <5% всегда), но в Linux available память учитывает кэш, который может быть освобождён. Без available нельзя точно утверждать, есть ли реальный дефицит памяти.
• Способ подтверждения: Запуск команды free -h или чтение /proc/meminfo (поле MemAvailable) во время инцидента.
• Способ опровержения: Если MemAvailable постоянно >10% от общей RAM, то тревога по free ложная.
• Метка: Неизвестно (метрика memory_cache есть, но нет прямой available)

Отчёт подготовлен на основе данных сравнительного сводного отчёта (source.txt). Все выводы и рекомендации привязаны к доступным метрикам и помечены соответствующими метками достоверности.

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

В тестовом периоде операционная скорость демонстрировала положительный тренд (+34,83 усл. ед. на точку), а общие ожидания СУБД – отрицательный (–38,65). В инциденте тренды сменили знак на противоположный: скорость падала (–32,85), ожидания росли (+40,55). Доминирующим типом ожиданий в обоих периодах оставался ввод-вывод (IO) с событием DataFileRead (>99 %), причём в инциденте корреляция между IO и общими ожиданиями достигла 0,9953 (R² = 0,99). Абсолютная пропускная способность дисков vdd (данные) и vdb (WAL) не превышала 0,6 МБ/с, что аномально низко. В инциденте возникла сильная корреляция операционной скорости с общей пропускной способностью дисков (r = 0,7476, R² = 0,56), а связь ожиданий IO с блоками чтения (bi) стала значимой (r = 0,7049). Дополнительными факторами выступили избыточно частый autovacuum (более 600 операций в час при удалении ~200 страниц, настройка *autovacuum_naptime = 1 с*), длительность контрольных точек (~13,5 мин при таймауте 15 мин) и появление запроса с queryid -7529817323075806461, у которого отношение ожиданий к вызовам составило 3,54 против 0,05–0,2 у остальных. Свободная RAM находилась на уровне менее 5 % в 100 % измерений, однако свопинг отсутствовал, а кэш страниц ОС достигал ~10,3 ГБ.

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

Послесловие

Представленные выводы базируются на доступных метриках vmstat, iostat, логах СУБД и статистике ожиданий, собранных в ходе двух смежных одночасовых интервалов.

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

Дальнейшие исследования должны включать нагрузочное тестирование дисков с помощью fio, анализ распределения памяти через /proc/meminfo, детальный разбор планов запросов и эксперименты с изменением параметров autovacuum и checkpoint в контролируемой среде. Полученные результаты могут служить основой для методики экспресс-диагностики инцидентов производительности PostgreSQL в виртуальных окружениях с ограниченным IO.