PG_EXPECTO: инцидент PostgreSQL – когда нехватка CPU и RAM маскируется под ожидания ввода-вывода

2️⃣Методологический пример диагностики CPU-связанного снижения производительности PostgreSQL с применением pg_expecto и анализа паттернов ожиданий

CPU в огне, IO лишь тень

Max: PG_EXPECTO

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

Philosophical_instruction_BETA_v5.1.md - Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.

Коллекция промптов для нейросети DeepSeek (на русском языке), предназначенных для анализа и оптимизации производительности СУБД PostgreSQL.

GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL

Глоссарий терминов | Postgres DBA | Дзен

Предисловие

В современной практике эксплуатации реляционных СУБД идентификация корневых причин инцидентов производительности осложняется феноменом маскировки одного типа ресурсного лимитирования другим. В частности, исчерпание вычислительных ресурсов центрального процессора способно генерировать вторичные ожидания ввода-вывода, что направляет инженера по ложному следу оптимизации дисковой подсистемы. Представленный ниже разбор реального инцидента демонстрирует эффективность комплексного статистического подхода, реализованного в инструменте pg_expecto, для преодоления подобных диагностических иллюзий.

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

Рис.1 Панель Zabbix - индикатор инцидента производительности СУБД

Операционная скорость СУБД

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

Инструкция по подготовке аналитического отчета

Аналитический отчет по инциденту производительности СУБД, подготовленный с помощью DeepSeek

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

Периоды анализа:

• Тестовый период: 2026-04-28 08:16 – 09:16 (Тест)
• Инцидентный период: 2026-04-28 09:16 – 10:16 (Инцидент)

Инфраструктура:

• Сервер: 8 vCPU (Intel Xeon Skylake), 15.61 ГБ ОЗУ, PostgreSQL 15.15.
• Дисковая подсистема: отдельные быстрые SSD-устройства под данные (/data, vdb) и WAL (/wal, vdc). Метрики задержек (await) и очередей (aqu_sz) в норме. (Подтверждено)

Резюме инцидента:

➡️В инцидентный период произошел резкий рост нагрузки, который перевел систему из сбалансированного состояния в состояние деградации. Несмотря на то, что основной метрикой ожиданий является ввод-вывод (DataFileRead), корневой причиной снижения производительности является исчерпание процессорных ресурсов (CPU-bound) на фоне критически низкого объема свободной оперативной памяти. (Вероятно)

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

В результате анализа выделены следующие ключевые проблемы:

☑️Достигнут предел производительности CPU (CPU-bound), маскирующийся под ожидания ввода-вывода.

ℹ️Доказательства: Тренд простоя CPU (id) демонстрирует устойчивое и ускоряющееся падение с очень высокой достоверностью (R²=0.84) в инцидентный период. Операционная скорость (SPEED) имеет сильную отрицательную корреляцию с IOPS (r ≈ -0.96 для vdb) при отличных показателях дисковой подсистемы (низкая утилизация, отсутствие очередей). Это означает, что с ростом операций ввода-вывода, генерируемых запросами, процессор перестает справляться с их обработкой. (Подтверждено)

➡️Следствие: Система вошла в режим, когда дальнейший рост нагрузки напрямую снижает производительность (отрицательная регрессия SPEED по WAITINGS, r = -0.8271).

☑️Неэффективные запросы являются основным генератором избыточной дисковой и процессорной активности.

ℹ️Доказательства: Идентифицированы конкретные QUERYID, генерирующие непропорционально много ожиданий IO:DataFileRead. Запрос 2105654058821398675 при 82 вызовах создал 376 ожиданий (20% от всех), что указывает на высокую стоимость единичного выполнения. (Подтверждено)

➡️Следствие: Неоптимальные планы запросов (вероятно, отсутствие индексов или неверные настройки) вынуждают СУБД читать большие объемы данных с диска, нагружая как подсистему ввода-вывода, так и CPU для их обработки.

☑️Критически низкий объем свободной оперативной памяти (<5%) создает риск нестабильности.

ℹ️Доказательства: Объем свободной памяти (free) стабильно составлял 320-350 МБ на протяжении всего наблюдения. Вся остальная память распределена между shared_buffers (4 ГБ) и файловым кэшем ОС (ок. 10.5 ГБ). Свопинг отсутствует. (Подтверждено)

➡️Следствие: Отсутствует резерв памяти для пиковых нагрузок, существует риск срабатывания OOM-убийцы (Out-Of-Memory killer) и невозможность безопасного увеличения work_mem.

☑️Параметры конфигурации PostgreSQL (effective_io_concurrency, work_mem) не соответствуют реальным условиям работы.

ℹ️Доказательства: effective_io_concurrency = 300 является завышенным для одиночного SSD без очередей, что может приводить к выбору неоптимальных планов (например, излишне агрессивного параллельного последовательного сканирования), нагружающих CPU. Стандартное значение work_mem = 8 МБ может провоцировать сброс данных во временные файлы на диске для задач с сортировкой или хешированием, увеличивая IO. (Предположение)

☑️Обнаружены потенциальные "грехи инженера" (согласно pg_expecto):

• Грех 3: «Copy-paste without understanding». Настройки effective_io_concurrency и work_mem, вероятно, установлены без учета реальной производительности дисковой подсистемы (отличные SSD) и характера нагрузки. random_page_cost, скорее всего, оставлен по умолчанию или завышен. (Предположение)
• Грех 4: «Race conditions» в процессах. Рост контекстных переключений (cs), сильно коррелирующих с us (user CPU), может указывать на избыточную конкуренцию на уровне приложения или борьбу за спин-блокировки внутри PostgreSQL, что вносит дополнительные накладные расходы в CPU. (Предположение)

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

Рекомендации разделены на немедленные и среднесрочные.

‼️Немедленные (ожидаемый быстрый эффект)

Провести тюнинг конфигурации PostgreSQL.

1. effective_io_concurrency : Снизить до 2. Текущее значение 300 вводит планировщик в заблуждение, заставляя предпочитать более ресурсоемкие операции, не давая выигрыша на одиночном SSD. (Предположение — требуется подтверждение после изменения)
2. random_page_cost : Установить значение в диапазоне 1.1–1.5. Это соответствует низким задержкам вашей SSD-дисковой подсистемы и рекомендует планировщику чаще выбирать индексное сканирование. (Предположение — требует проверки после изменения)
3. work_mem : Аккуратно увеличить до 16–32 МБ на уровне БД. Это снизит необходимость создания временных файлов на диске, но потребует мониторинга свободной памяти. (Вероятно)

Начать целевую оптимизацию запросов.

1. Цель: Проанализировать планы выполнения (EXPLAIN (ANALYZE, BUFFERS)) для запросов с ID: 2105654058821398675, -8298172557622738496 и других из топа по ожиданиям IO.
2. Действия: Искать операции полного сканирования (Seq Scan) больших таблиц. Проверить возможность создания покрывающих индексов для самых тяжелых запросов. (Подтверждено)

❗Среднесрочные (требуют планирования и ресурсов)

Решить проблему с дефицитом оперативной памяти.

1. Путь 1 (приоритетный): Увеличить объем ОЗУ сервера до 32 ГБ. Это наиболее эффективный способ обеспечить стабильность и производительность. (Вероятно)
2. Путь 2 (если апгрейд невозможен): Уменьшить shared_buffers до 2-3 ГБ. Это высвободит память под файловый кэш ОС, что может частично скомпенсировать дефицит. Однако это может снизить hit ratio в буферном кэше, поэтому требует тщательного мониторинга. (Предположение)

Планировать масштабирование CPU-ресурсов.

1. Устойчивый тренд падения простоя CPU (с достоверностью R²=0.84) является опережающим индикатором. Рекомендуется начать планирование по добавлению vCPU (например, до 16) или миграции на инстанс с более производительным процессором, чтобы превентивно избежать полной деградации при дальнейшем росте нагрузки. (Вероятно)

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

Для дальнейшего, более точного анализа и оптимизации, критически важно получить следующие данные:

1. Планы выполнения проблемных запросов. Требуется вывод EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT) для каждого из топ-5 запросов по ожиданиям IO (Query ID: 2105654..., -82981725... и др.). Без этого любые рекомендации по индексам или переписыванию запросов остаются предположением.
2. Содержимое системного представления pg_stat_statements. Необходим срез данных по shared_blks_hit, shared_blks_read, и temp_blks_written для тех же ID запросов, чтобы точно оценить эффективность кэширования и факты обращения к диску/временным файлам.
3. Текущие настройки планировщика. Явно запросить полный вывод SHOW effective_cache_size;, SHOW random_page_cost;, чтобы подтвердить или опровергнуть предположение о "грехе инженера 3".
4. Данные о раздувании таблиц и индексов. Сделать выборку из pg_stat_user_tables (особенно n_dead_tup и n_live_tup) для таблиц, участвующих в проблемных запросах, чтобы исключить фактор неактуальной статистики или раздувания. Это подтвердит или опровергнет необходимость корректировки autovacuum.

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

В ходе анализа инцидентного периода (09:16–10:16) относительно тестового (08:16–09:16) выявлено:

1. устойчивый отрицательный тренд простоя CPU (R²=0,84) при нормативных показателях дисковой подсистемы (низкая утилизация, отсутствие очередей);
2. сильная отрицательная корреляция операционной скорости СУБД с IOPS (r ≈ -0,96), что свидетельствует о CPU-лимитированной природе деградации;
3. идентифицированы проблемные запросы (например, QUERYID 2105654058821398675) с непропорционально высоким числом ожиданий DataFileRead;
4. подтверждён критический дефицит свободной оперативной памяти (<5%), создающий риск OOM.

⚠️Корневой причиной признано достижение предела производительности CPU на фоне неоптимальных планов запросов и завышенных настроек планировщика (effective_io_concurrency = 300).

Послесловие

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

ℹ️Для углублённой верификации гипотез требуется получение планов выполнения (EXPLAIN ANALYZE) проблемных запросов и срезов pg_stat_statements.

ℹ️В среднесрочной перспективе масштабирование оперативной памяти до 32 ГБ и добавление vCPU являются экономически обоснованными мерами предотвращения рецидивов инцидентов данного класса.