Корреляционный анализ аномалий автовакуума, перераспределения типов ожиданий (BufferIO vs. Extension) и дисковой утилизации в высоконагруженной СУБД PostgreSQL (192 CPU, 1 ТБ RAM).
Содержание
Предисловие
Анализ инцидентов производительности в высоконагруженных СУБД PostgreSQL требует не только фиксации метрик, но и выявления каузальных структур между системными событиями, ожиданиями ядра СУБД и операционной скоростью.
В настоящей работе представлены результаты применения комплекса pg_expecto к инциденту, зафиксированному на конфигурации с 192 виртуальными CPU и 1 ТБ оперативной памяти.
В фокусе исследования — количественная оценка сдвигов корреляционных связей между wait_event_type (IPC, Extension, IO, Lock), показателями iostat (util, aqu_sz), статистикой автовакуума и ошибками блокировок.
Предлагаемый подход позволяет отделить инфраструктурные ограничения (диски, планировщик) от внутренней патологии СУБД, в частности — неэффективной активности autovacuum, имитирующей дисковой дефицит.
Шаблон анализа инцидента производительности СУБД PostgreSQL
DeepSeek + PG_EXPECTO 9.1 : стандартный шаблон анализа инцидента производительности СУБД
Инцидент производительности СУБД
Операционная скорость
Рис.1 График изменения операционной скорости в процессе инцидента.
Ожидания СУБД
Рис.2 График изменения ожиданий СУБД в процессе инцидента.
1. Сводный отчет по метрикам СУБД и ОС
2. Аналитический отчет по инциденту производительности СУБД PostgreSQL
Общая информация
-
Период теста: 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.
ссылка на оригинал статьи https://habr.com/ru/articles/1043998/