Корреляционный анализ аномалий автовакуума, перераспределения типов ожиданий (BufferIO vs. Extension) и дисковой утилизации в высоконагруженной СУБД PostgreSQL (192 CPU, 1 ТБ RAM).
GitHub - Комплекс pg_expecto для статистического анализа производительности и тестирования СУБД PostgreSQL
Предисловие
Анализ инцидентов производительности в высоконагруженных СУБД PostgreSQL требует не только фиксации метрик, но и выявления каузальных структур между системными событиями, ожиданиями ядра СУБД и операционной скоростью.
В настоящей работе представлены результаты применения комплекса pg_expecto к инциденту, зафиксированному на конфигурации с 192 виртуальными CPU и 1 ТБ оперативной памяти.
В фокусе исследования — количественная оценка сдвигов корреляционных связей между wait_event_type (IPC, Extension, IO, Lock), показателями iostat (util, aqu_sz), статистикой автовакуума и ошибками блокировок.
Предлагаемый подход позволяет отделить инфраструктурные ограничения (диски, планировщик) от внутренней патологии СУБД, в частности — неэффективной активности autovacuum, имитирующей дисковой дефицит.
Шаблон анализа инцидента производительности СУБД PostgreSQL
Инцидент производительности СУБД
Операционная скорость
Рис.1 График изменения операционной скорости в процессе инцидента.
Ожидания СУБД
Рис.2 График изменения ожиданий СУБД в процессе инцидента.
1. Сводный отчет по метрикам СУБД и ОС
Формат txt
Формат html
2. Аналитический отчет по инциденту производительности СУБД PostgreSQL
Формат txt
Формат html
Общая информация
- Период теста: 2026-05-18 09:10 – 10:10 (1 час)
- Период инцидента: 2026-05-18 10:10 – 11:10 (1 час)
- Версия PostgreSQL: 15.13
- Аппаратная конфигурация: 192 CPU (Intel Xeon Platinum 8280L, 4 сокета по 48 ядер), RAM 1007.58 GB, KVM виртуализация
- Дисковые устройства: vdg (WAL), vdh, vdi, vdj, vdk (data, LVM /data), vdc (/backup), vde (/log)
- Ключевые параметры СУБД: shared_buffers = 251807 MB, effective_cache_size = 747230 MB, work_mem = 1 GB, autovacuum_naptime = 1s, checkpoint_timeout = 15 min, max_wal_size = 8 GB, random_page_cost = 1.1, max_parallel_workers_per_gather = 0
Краткое описание меток
- Подтверждено — значение получено из предоставленных метрик или прямого математического следствия.
- Вероятно — вывод основан на косвенных признаках, корреляциях или общеизвестных практиках, но не подтверждён прямыми данными.
- Предположение — гипотеза, для проверки которой необходимы дополнительные данные либо данные отсутствуют.
- Неизвестно — термин или метрика не фигурируют в отчёте, значение неизвестно.
Ключевые проблемы СУБД и инфраструктуры
1. Аномальная активность автовакуума
- Тезис: Количество операций autovacuum выросло с 693 до 18 642 за час (+2590%) при одновременном падении числа удалённых страниц с 19 930 до 2 068 (–89.6%). Это свидетельствует о чрезмерно частых, но малоэффективных запусках vacuum.
- Способ подтверждения: Прямое сравнение метрик из раздела 3.2 отчёта (операций autovacuum, удалено страниц).
- Способ опровержения: Если бы рост числа операций сопровождался пропорциональным ростом удалённых страниц.
- Метка: Подтверждено
2. Смещение ожиданий в сторону IPC (BufferIO) и Extension
- Тезис: В инциденте 90.98% всех ожиданий приходится на BufferIO (тип IPC), 9.02% – на Extension. Ожидания IO и Lock статистически незначимы (p > 0.05). Корреляция SPEED с WAITINGS в инциденте отрицательная (r = –0.9184, R²=0.84).
- Способ подтверждения: Диаграммы Парето по wait_event_type и корреляционный анализ из разделов 1 и 1.1 отчёта.
- Способ опровержения: Если бы в инциденте сохранилась значимость корреляций IO или Lock (p < 0.05, ВКО ≥ 0.01).
- Метка: Подтверждено
3. Высокая и стабильная загрузка дисков data без прямой корреляции со скоростью
- Тезис: Диски vdh, vdi, vdj, vdk работают с утилизацией 91–92% и глубиной очереди >1 в 100% времени. Однако корреляция операционной скорости с IOPS и MBps слабая (R² < 0.4), поэтому дисковая подсистема не является прямым ограничением производительности.
- Способ подтверждения: iostat метрики (util, aqu_sz) и корреляции SPEED–IOPS / SPEED–MBps из раздела 2.1.4.
- Способ опровержения: Если бы R² для SPEED и IOPS был >0.6.
- Метка: Подтверждено
4. Рост ошибок блокировок и появление взаимоблокировки
- Тезис: В инциденте число ошибок lock_not_available выросло с 34 до 58 (+70%), зафиксирован один deadlock_detected (было 0). Ожидания Lock и LWLock при этом статистически незначимы.
- Способ подтверждения: Сравнение статистики ошибок из раздела 3.1.
- Способ опровержения: Если бы количество ошибок не изменилось или снизилось.
- Метка: Подтверждено
5. Изменение корреляционной структуры между ожиданиями и системными метриками
- Тезис: В тесте ожидания IPC и Extension сильно коррелировали с переключениями контекста (cs), прерываниями (in) и системным временем (sy). В инциденте эти корреляции исчезли, а ожидания Extension стали коррелировать с пользовательским временем (us, R²=0.68).
- Способ подтверждения: Сравнение R² из раздела 1.3 отчёта (тест vs инцидент).
- Способ опровержения: Если бы в инциденте сохранились высокие значения R² для cs, in, sy.
- Метка: Подтверждено
6. Отсутствие временных файлов (temp_files = 0)
- Тезис: Временные файлы не создавались ни в тесте, ни в инциденте. Это означает, что выделенного work_mem (1 GB) достаточно для всех сортировок и хеш-таблиц.
- Способ подтверждения: Значение 0 в статистике temp_files (раздел 3.4).
- Способ опровержения: Если бы temp_files > 0.
- Метка: Подтверждено
7. Недостаток данных для оценки корреляции vmstat/wa с iostat/util в инциденте
- Тезис: В тесте корреляция wa и util для data-устройств была высокой (R² 0.61–0.66). В инциденте эта корреляция не рассчитана, поэтому связь неизвестна.
- Способ подтверждения: Отсутствие раздела «1. КОРРЕЛЯЦИЯ VMSTAT и IOSTAT» для инцидента в source.txt.
- Способ опровержения: Если бы в инциденте были приведены значения корреляции.
- Метка: Неизвестно
Рекомендации по оптимизации СУБД и инфраструктуры
Рекомендация 1. Изменить параметры autovacuum
- Тезис: Увеличить autovacuum_naptime с 1с до 5–10 с, пересмотреть autovacuum_vacuum_scale_factor (с 0.001 до 0.01–0.02) и настроить autovacuum_vacuum_cost_delay/cost_limit для снижения влияния vacuum на основную нагрузку.
- Способ подтверждения: После изменений число операций autovacuum за час должно снизиться до сотен, длительность – уменьшиться.
- Способ опровержения: Если нагрузка не изменится – возможно, высокая скорость обновления строк требует иного подхода (например, partitioning).
- Метка: Подтверждено (на основе аномальной активности и общеизвестной практики)
Рекомендация 2. Исследовать и оптимизировать запросы, лидирующие по ожиданиям
- Тезис: Проанализировать планы запросов для queryid из топов Парето (например, -4280293605113329019, -1757223094415174739) с помощью auto_explain. Искать недостающие индексы, неэффективные сканирования, избыточные параллельные операции.
- Способ подтверждения: После оптимизации ожидания по IPC должны снизиться.
- Способ опровержения: Если планы запросов оптимальны – проблема в другом (например, в расширениях).
- Метка: Предположение (требуется анализ планов запросов)
Рекомендация 3. Проверить расширения (Extension)
- Тезис: Определить, какие расширения активны (postgres_fdw, dblink, кастомные) и какой код они выполняют. Рассмотреть перенос вызовов в фоновые процессы или оптимизацию логики.
- Способ подтверждения: Снижение ожиданий Extension после отключения/оптимизации.
- Способ опровержения: Если ожидания не связаны с расширениями, а ошибочно классифицированы.
- Метка: Предположение
Рекомендация 4. Рассмотреть увеличение max_parallel_workers_per_gather
- Тезис: Текущее значение 0 отключает параллельные запросы. Для аналитических операций это может быть неоптимально, но включение параллелизма может усилить конкуренцию за буферы. Требуется анализ планов запросов.
- Способ подтверждения: Рост операционной скорости для тяжёлых запросов при осторожном увеличении параметра.
- Способ опровержения: Ухудшение ожиданий IPC из-за увеличения параллельных сканирований.
- Метка: Предположение
Рекомендация 5. Снизить утилизацию дисков data
- Тезис: Перенести наиболее активные таблицы/индексы на отдельные табличные пространства (другие диски). Уменьшить effective_io_concurrency (с 300 до 100–200) для снижения глубины очереди.
- Способ подтверждения: Снижение %util и aqu_sz на data-дисках.
- Способ опровержения: Если утилизация останется высокой – объём IO слишком велик для текущей дисковой подсистемы.
- Метка: Вероятно
Рекомендация 6. Настроить мониторинг автовакуума и блокировок
- Тезис: Добавить алерты на число операций autovacuum >1000 в час и на длительные блокировки в pg_stat_activity.
- Способ подтверждения: Быстрое обнаружение аномалий в будущем.
- Способ опровержения: Если аномалии не повторятся – мониторинг не сработает.
- Метка: Вероятно
Необходимая дополнительная информация для продолжения анализа и оптимизации производительности
- Планы запросов (auto_explain) для queryid, лидирующих по ожиданиям IPC и Extension (особенно -4280293605113329019, -1757223094415174739, -5248322003985466995).
- Список активных расширений и их конфигурация (pg_extension, параметры для postgres_fdw, если используется).
- Данные о самых обновляемых/удаляемых таблицах (pg_stat_user_tables: n_tup_upd, n_tup_del, n_tup_hot_upd) для оценки необходимости автовакуума.
- Статистика контрольных точек с разбивкой по времени (checkpoint_write_time, checkpoint_sync_time) и распределение записываемых буферов – для оценки влияния на IO.
- Топ запросов по времени выполнения и по числу блокировок (pg_stat_statements, если включён).
- Логи автовакуума (с параметром log_autovacuum_min_duration) – для понимания, какие таблицы вакуумируются чаще всего.
- Измерения пропускной способности дисковой подсистемы (максимальные IOPS и MBps на устройство) – для оценки запаса.
- Данные о использовании буферного кэша PostgreSQL (pg_buffercache) – для оценки эффективности shared_buffers.
Общий технический итог
Проведённый анализ подтверждает, что непосредственным триггером инцидента производительности явилась аномальная активация автовакуума: за час число операций выросло на 2590% при снижении объёма удаляемых страниц на 89,6%, что свидетельствует о чрезмерно частых, но малопродуктивных запусках. Доминирующим типом ожиданий стал BufferIO (IPC-группа, 90,98%) при статистически незначимых вкладах IO и Lock; отрицательная корреляция операционной скорости с ожиданиями достигла r = –0,9184 (R²=0,84). Дисковая подсистема data-массива эксплуатировалась с утилизацией 91–92% и глубиной очереди >1 в 100% времени, однако прямая связь скорости с IOPS/MBps оказалась слабой (R²<0,4), что исключает диск как первичное узкое место.
Выявлено изменение корреляционной структуры: ожидания IPC и Extension перестали коррелировать с переключениями контекста и прерываниями, но Extension стали значимо связаны с пользовательским временем (R²=0,68). Отсутствие временных файлов (temp_files=0) указывает на достаточность work_mem (1 ГБ), тогда как рост ошибок lock_not_available (+70%) и появление deadlock_detected, при одновременной незначимости ожиданий Lock, требуют пересмотра логики блокировок на уровне прикладных запросов.
Послесловие
Представленный анализ выявил ряд зон неопределённости, требующих дополнительных инструментальных измерений. Для верификации гипотез о роли расширений (Extension) необходима детализация активных модулей (postgres_fdw, кастомные расширения) и их планов выполнения. Также настоятельно рекомендуется получение планов запросов для queryid, лидирующих по ожиданиям IPC и Extension (например, -4280293605113329019), с помощью auto_explain, а также включение расширенного логирования автовакуума (log_autovacuum_min_duration) и сбора pg_stat_statements.
Лишь после этого возможно окончательное заключение о необходимости увеличения max_parallel_workers_per_gather или переноса горячих таблиц на отдельные табличные пространства.
Предложенный в работе методологический каркас pg_expecto, однако, уже сейчас позволяет уверенно дифференцировать инфраструктурные и внутрисистемные аномалии производительности PostgreSQL.