PG_EXPECTO 8.1: аналитический отчёт по результатам нагрузочного тестирования PostgreSQL.

Как с помощью PG_EXPECTO 8.1 (философское ядро, самопроверка, эпистемологическая строгость, статанализ) выявить в PostgreSQL 17.5: дефицит ОЗУ, высокую дисковую активность, доминирование проблемных запросов (scenario1, scenario3), рост очереди CPU и противоречия нагрузочного тестирования — с корреляциями, регрессионными трендами, рекомендациями по оптимизации и перечнем недостающих данных для углублённого анализа.

PG_EXPECTO 8.1: не гадай на планах — измерь давление памяти, диска и очередей.

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.

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

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

Предисловие

Аналитический отчёт подготовлен с использованием методологии PG_EXPECTO 8.1 — комплекса для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL, объединяющего философское ядро (эпистемологическая строгость, защита от галлюцинаций, самопроверка) и процедурный скелет автономного AI-агента. В отчёте применён научный метод с конвейерами мышления CoVe, ToT, Pre-Mortem и Red Teaming для диагностики реального состояния сервера PostgreSQL 17.5 по итогам двухчасового нагрузочного теста. Ниже представлены выявленные узкие места — от критической нехватки памяти и свопинга до доминирования проблемных запросов и роста очереди CPU — а также верифицированные корреляции, противоречия в данных и приоритетные рекомендации, сформулированные на основе принципа максимальной правдивости и с указанием уровня достоверности каждого вывода.

Предыдущая работа по теме

Задача

Применить и протестировать методику формирования аналитического отчета производительности СУБД по результатам нагрузочного тестирования.

Исходные данные - статистические результаты нагрузочного тестирования

Инструкция PG_EXPECTO

Подробнее про уровни уверенности

Промпт для подготовки исходного отчета

Философская инструкция

Промпт для применения философской инструкции при формировании итогового аналитического отчета

Шаг-1: Загрузить инструкцию PG_EXPECTO

Шаг-2 : Сформировать исходный отчет по результатам нагрузочного тестирования

Отчет по результатам нагрузочного тестирования

Шаг-3 : Загрузить инструкции для подготовки финального аналитического отчета

Шаг - 4 : Формирование финального аналитического отчета по результатам нагрузочного тестирования

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

Сервер и СУБД

• PostgreSQL: 17.5 (Postgres Pro Enterprise Certified 17.5.1), платформа x86_64-pc-linux-gnu.
• Аппаратное обеспечение: Виртуальная машина с 8 vCPU (Intel Xeon Skylake, без SMT), 7.5 ГБ RAM.
• Дисковая подсистема:
• vdd (100 ГБ, LVM /data) – файлы данных и табличные пространства.
• vdc (50 ГБ, LVM /wal) – журнал предзаписи (WAL).
• vdb (30 ГБ, LVM /log) – журналы PostgreSQL.
• vda – системный диск.
• Ключевые параметры конфигурации PostgreSQL (из _1.settings.txt):
• shared_buffers = 512 МБ (524288 блоков по 8 КБ).
• effective_cache_size ≈ 3.75 ГБ (около 50% RAM).
• work_mem = 32 МБ, maintenance_work_mem ≈ 479 МБ.
• random_page_cost = 1.1, seq_page_cost = 1.0.
• checkpoint_timeout = 3600 с, max_wal_size = 4096 МБ.
• autovacuum = on, параметры по умолчанию (scale_factor = 0.2, threshold = 50).
• max_connections = 100, max_parallel_workers = 4, max_parallel_workers_per_gather = 3.
• Включены pg_stat_statements, pg_wait_sampling, track_io_timing.
• Логирование контрольных точек и ожиданий блокировок включено.
• Параметры управления памятью ОС (VM):
• vm.dirty_background_ratio = 10% (≈ 768 МБ), vm.dirty_ratio = 30% (≈ 2.3 ГБ).
• vm.dirty_expire_centisecs = 500 (5 с), vm.dirty_writeback_centisecs = 100 (1 с).
• vm.swappiness = 1, vm.vfs_cache_pressure = 100.

Период наблюдения: 2026-04-13 09:47 – 2026-04-13 12:13 (≈2 часа 26 минут, 147 точек измерений).

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

1. Критическая нехватка оперативной памяти

• Подтверждено: Свободная память (free) составляет менее 5% от общего объёма (7.5 ГБ) на протяжении 100% времени наблюдения. Медианное значение свободной памяти ≈ 128 МБ.
• Подтверждено: Присутствует активность подкачки (swap in – 17.7% наблюдений, swap out – 20.4%), несмотря на vm.swappiness=1. Это указывает на острое давление на память.
• Подтверждено: Коэффициент попадания в shared_buffers (hit ratio) критически низок: медиана 64.3%, минимум 59.5%, максимум 70.8%. Для OLTP-нагрузок нормальным считается значение >95%. Большая часть операций чтения страниц данных выполняется с диска.
• Подтверждено: Сильная корреляция (0.844, R²=0.71) между количеством прочитанных блоков (bi) и объёмом подкачки из swap (swap in). Чем больше чтений с диска, тем активнее система вытесняет страницы в swap.

2. Высокая и растущая нагрузка на дисковую подсистему

• Подтверждено: Утилизация устройства данных vdd превышает 50% в 96.6% времени, медианное значение 68.6%. Утилизация устройства WAL vdc превышает 50% в 100% времени, медиана 91.0%. Оба устройства работают на пределе возможностей.
• Подтверждено: Доля времени ожидания ввода-вывода CPU (wa) стабильно высока: минимум 12%, медиана 15%, максимум 18%. Это подтверждает, что дисковая подсистема является узким местом.
• Подтверждено: Рост операционной скорости (SPEED) сильно коррелирует с объёмом записанных блоков (bo) – корреляция 0.971, R²=0.94. Производительность системы ограничена пропускной способностью записи на диск.
• Подтверждено: Ожидания ввода-вывода СУБД (IO) и межпроцессного взаимодействия (IPC) демонстрируют устойчивый положительный тренд (наклон регрессии +43.06, R²=0.87) и тесно связаны с операциями чтения/записи.

3. Доминирование проблемных запросов

• Подтверждено: Запрос select scenario1() (queryid -1868913168813536936) генерирует 100% всех ожиданий типа IPC (событие BufferIo) и является основным источником ожиданий IO (62.13% от общего числа) и LWLock (88.17%). На его долю приходится подавляющая часть негативной динамики производительности.
• Подтверждено: Второй значимый запрос – select scenario3() (queryid -3530536158217781259) – создаёт 37.75% ожиданий IO (преимущественно DataFileRead и SlruWrite).
• Вероятно: Ожидания BufferIo в scenario1() могут быть вызваны неоптимальным планом выполнения, приводящим к избыточным операциям чтения/записи буферов, либо конфликтами при параллельном выполнении (используется параллелизм, max_parallel_workers_per_gather=3).

4. Рост очереди выполнения и снижение запаса CPU

• Подтверждено: Среднее количество процессов в очереди выполнения (procs r) имеет сильный негативный тренд (наклон +43.15, R²=0.88). В пиковые моменты значение достигает 9 при 8 доступных ядрах, что указывает на перегрузку CPU.
• Подтверждено: Процент простоя CPU (id) снижается (наклон -43.63, R²=0.91), медиана составляет 45%. Система приближается к исчерпанию вычислительных ресурсов.
• Подтверждено: Системное время CPU (sy) медиана 8% и сильно коррелирует с ожиданиями IPC (0.984, R²=0.97), что свидетельствует о значительных затратах ядра на обработку межпроцессного взаимодействия (сигналы, синхронизацию).

5. Несоответствия конфигурации и наблюдаемой нагрузки

• Вероятно: Параметр effective_cache_size = 3.75 ГБ (50% RAM) при хронической нехватке свободной памяти может вводить планировщик в заблуждение, заставляя выбирать индексные сканы вместо последовательных, даже когда требуемые страницы отсутствуют в кэше и требуют физического чтения.
• Вероятно: Параметры vm.dirty_background_ratio = 10% и vm.dirty_ratio = 30% (768 МБ и 2.3 ГБ соответственно) слишком высоки для сервера баз данных с интенсивной записью. Это может приводить к накоплению большого объёма «грязных» страниц и последующим резким всплескам записи, вызывающим задержки.
• Предположение: Низкий коэффициент попадания в shared_buffers (64%) может быть следствием как недостаточного размера буферного пула (512 МБ при 7.5 ГБ RAM), так и преобладания последовательных сканов больших таблиц, которые неэффективно кэшируются.

6. Противоречия в данных

• Подтверждено противоречие: Высокий процент cpu wa (12-18%) не коррелирует с утилизацией дисков vdd и vdc (корреляции незначимы). Это указывает на то, что ожидания ввода-вывода могут быть связаны с другими устройствами (например, системным диском vda, где могут располагаться временные файлы или логи ОС), либо wa агрегирует ожидания от нескольких источников, маскируя вклад каждого.
• Вероятно: Операционная скорость (SPEED) слабо коррелирует с пропускной способностью дисков vdd (MBps) – корреляция 0.446, R²=0.20 – несмотря на сильную связь с агрегированными показателями bi/bo из vmstat. Это может объясняться тем, что vmstat учитывает ввод-вывод со всех устройств, включая системный диск и, возможно, сетевые файловые системы (данные отсутствуют).

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

Рекомендации сформулированы с учётом ограниченности предоставленных данных. Перед внедрением в промышленную среду требуется валидация в тестовом окружении.

Приоритет 1 (Критический) – Немедленные действия

1. Анализ и оптимизация запроса select scenario1()

• Действие: Получить план выполнения запроса с использованием EXPLAIN (ANALYZE, BUFFERS, TIMING). Выявить узкие места: операции последовательного сканирования больших таблиц, неэффективные соединения, отсутствие необходимых индексов, избыточное использование временных файлов.
• Уровень достоверности: Подтверждено, что данный запрос является основным источником ожиданий всех типов.
• Ожидаемый эффект: Снижение нагрузки на дисковую подсистему и уменьшение ожиданий IPC и IO.

2. Увеличение объёма оперативной памяти

• Действие: Увеличить объём RAM виртуальной машины минимум до 16 ГБ, предпочтительно до 32 ГБ.
• Уровень достоверности: Подтверждено, что текущий объём (7.5 ГБ) недостаточен, что приводит к свопингу и низкому hit ratio.
• Ожидаемый эффект: Увеличение доли кэшируемых страниц данных и индексов, снижение физических чтений с диска, устранение своп-активности, повышение hit ratio.

Приоритет 2 (Высокий) – Действия после увеличения памяти

3. Увеличение shared_buffers

• Действие: После добавления RAM увеличить параметр shared_buffers до 2–3 ГБ (25–40% от нового объёма памяти).
• Уровень достоверности: Вероятно, текущее значение 512 МБ является основным ограничителем эффективности кэширования.
• Предостережение: Изменение требует перезапуска PostgreSQL. Перед применением убедиться, что новый объём не вызовет конкуренции с page cache ОС и другими процессами. Рекомендуется постепенное увеличение с мониторингом hit ratio и использования свопа.
• Ожидаемый эффект: Повышение hit ratio, снижение ожиданий IO (DataFileRead), уменьшение конкуренции за LWLock (особенно BufferMapping).

4. Настройка параметров виртуальной памяти ОС

• Действие: Уменьшить значения vm.dirty_background_ratio до 1–2% и vm.dirty_ratio до 5–10% от общего объёма RAM.
• Уровень достоверности: Вероятно, текущие высокие пороги способствуют накоплению больших объёмов грязных страниц, что может вызывать периодические всплески записи и задержки.
• Ожидаемый эффект: Более плавная и предсказуемая запись на диск, снижение пиковых нагрузок на устройства хранения.

5. Пересмотр параметра effective_cache_size

• Действие: После увеличения памяти и shared_buffers скорректировать effective_cache_size. Рекомендуемое значение – сумма shared_buffers и ожидаемого размера page cache ОС (обычно 75–80% от RAM). При 16 ГБ RAM и shared_buffers = 3 ГБ можно установить effective_cache_size = 10–12 ГБ.
• Уровень достоверности: Вероятно, текущее значение (3.75 ГБ) занижено относительно доступной памяти после апгрейда, что может приводить к выбору неоптимальных планов запросов.

Приоритет 3 (Средний) – Дополнительные улучшения

6. Анализ параллельного выполнения запроса scenario1()

• Действие: Проверить, используется ли параллельный план для scenario1(). Если да, экспериментально отключить параллелизм для этого запроса (SET max_parallel_workers_per_gather = 0) и оценить изменение производительности и характера ожиданий BufferIo.
• Уровень достоверности: Вероятно, ожидания BufferIo могут быть связаны с координацией параллельных процессов. Если отключение параллелизма снизит ожидания и общее время выполнения, это укажет на неэффективность распараллеливания в данном случае.
• Альтернатива: При сохранении параллелизма увеличить max_parallel_workers (сейчас 4) при условии наличия достаточных ресурсов CPU.

7. Увеличение effective_io_concurrency

• Действие: Установить effective_io_concurrency = 200 (для типичного SSD). Текущее значение, вероятно, равно значению по умолчанию 1 или 2.
• Уровень достоверности: Вероятно, параметр не адаптирован под характеристики SSD-хранилища (r_await медиана 1.9 мс). Это может улучшить эффективность упреждающего чтения при bitmap heap scan.
• Ожидаемый эффект: Снижение задержек при чтении большого количества страниц.

8. Мониторинг контрольных точек

• Действие: Проанализировать журнал PostgreSQL на предмет сообщений о контрольных точках (включено log_checkpoints = on). Оценить частоту и длительность контрольных точек. При необходимости скорректировать checkpoint_timeout и max_wal_size.
• Уровень достоверности: Предположение. Без логов невозможно оценить влияние контрольных точек на производительность записи, хотя высокая утилизация WAL и корреляция SPEED с записью указывают на потенциальную проблему.

9. Перенос WAL на более быстрое хранилище

• Действие: Рассмотреть возможность размещения WAL на отдельном NVMe-диске, если текущее устройство vdc не справляется с нагрузкой (утилизация 91%).
• Уровень достоверности: Вероятно, высокая утилизация WAL может стать узким местом при дальнейшем росте нагрузки, даже при низких задержках записи (0.62 мс). Перенос на более производительное хранилище обеспечит запас пропускной способности.

10. Анализ использования системного диска

• Действие: Собрать статистику iostat для устройства vda (системный диск) за аналогичный период. Проверить, не создают ли временные файлы PostgreSQL (каталог pgsql_tmp) или логи ОС дополнительную нагрузку, объясняющую высокий cpu wa.
• Уровень достоверности: Предположение. Требуется для разрешения противоречия между высоким wa и утилизацией vdd/vdc.

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

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

1. Тексты и планы выполнения проблемных запросов
2. Что требуется: Полный текст запросов select scenario1() и select scenario3(). Результат выполнения EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE) для каждого запроса в моменты высокой нагрузки.
3. Что даст: Позволит выявить конкретные операции, вызывающие ожидания BufferIo, DataFileRead, использование временных файлов, неэффективные сканы или соединения. Без этого любые рекомендации по оптимизации запросов остаются на уровне предположений (Неизвестно).
4. Статистика по таблицам и индексам, к которым обращаются проблемные запросы
5. Что требуется: Результаты запросов к pg_stat_user_tables и pg_stat_user_indexes для соответствующих таблиц (размер таблиц, количество последовательных и индексных сканов, количество операций вставки/обновления/удаления). Информация о существующих индексах (\d+ table_name).
6. Что даст: Позволит оценить адекватность индексов, необходимость их добавления или перестроения, целесообразность изменения параметров autovacuum для этих таблиц.
7. Логи PostgreSQL за период наблюдения
8. Что требуется: Фрагменты журнала PostgreSQL, содержащие предупреждения (WARNING), сообщения о контрольных точках (LOG: checkpoint starting/complete), сообщения о длительных блокировках (LOG: process ... acquired lock ... after ... ms), сообщения об использовании временных файлов (LOG: temporary file size exceeds temp_file_limit).
9. Что даст: Подтвердит или опровергнет гипотезы о частых контрольных точках, утечках временных файлов, наличии подавленных ошибок. Уровень достоверности текущих выводов по этим аспектам – Неизвестно.
10. Детальная статистика по ожиданиям на уровне отдельных событий и процессов
11. Что требуется: Данные из представления pg_wait_sampling за период, сгруппированные по pid и queryid, с указанием конкретных wait_event и длительности ожиданий.
12. Что даст: Позволит точно определить, какие именно процессы и в какой фазе выполнения запроса испытывают ожидания BufferIo и DataFileRead. Уточнит природу ожиданий IPC.
13. Статистика использования временных файлов
14. Что требуется: Значения temp_files и temp_bytes из pg_stat_database за период наблюдения, желательно с разбивкой по времени.
15. Что даст: Покажет, создают ли запросы большие временные файлы (например, для сортировок или хеш-таблиц), что может объяснить часть нагрузки на дисковую подсистему.
16. Статистика ввода-вывода для всех дисковых устройств
17. Что требуется: Вывод iostat -x 1 для всех устройств (vda, vdb, vdc, vdd) за тот же период.
18. Что даст: Разрешит противоречие между высоким cpu wa и утилизацией vdd/vdc. Позволит выявить, не является ли системный диск или диск с логами скрытым узким местом.
19. Данные о сетевой активности (если применимо)
20. Что требуется: Статистика сетевых интерфейсов (например, netstat -i или sar -n DEV) за период наблюдения.
21. Что даст: Позволит исключить или подтвердить влияние сетевых задержек на ожидания IPC (если используется синхронная репликация или клиент-серверное взаимодействие с большим объёмом данных). В предоставленной конфигурации synchronous_standby_names пуст, но без данных утверждать об отсутствии репликации нельзя.
22. Информация о других процессах на сервере
23. Что требуется: Список процессов, потребляющих значительные ресурсы CPU и памяти (например, вывод top -b -n 1 в пиковые моменты).
24. Что даст: Позволит оценить, не конкурируют ли с PostgreSQL другие приложения за память и диск, усугубляя нехватку ресурсов.

Без указанных данных дальнейший анализ будет ограничен и не позволит сформировать окончательные рекомендации с высоким уровнем достоверности.

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

В ходе нагрузочного тестирования PostgreSQL 17.5 с применением методологии PG_EXPECTO 8.1 установлено, что производительность СУБД лимитируется тремя взаимосвязанными факторами: хроническим дефицитом оперативной памяти (свободно <5%, активный свопинг), экстремальной утилизацией дисковых устройств (vdd >68%, vdc >91% в медиане) и доминированием двух проблемных запросов — scenario1 (генерирует 100% ожиданий IPC и 62% IO) и scenario3 (37,8% IO). Статистически подтверждены сильные корреляции между объёмом чтения/записи блоков и операционной скоростью (R²=0,94), а также рост очереди выполнения процессов (наклон регрессии +43,15) при снижении процента простоя CPU (наклон –43,63). Выявлено противоречие: высокий процент ожидания ввода-вывода (wa 12–18%) не коррелирует с утилизацией основных дисков, что указывает на неучтённое узкое место (возможно, системный диск или временные файлы). Ключевые рекомендации: немедленное увеличение RAM до 16–32 ГБ, оптимизация scenario1() через EXPLAIN, увеличение shared_buffers и перенастройка параметров грязных страниц ОС. Для верификации гипотез необходимы логи PostgreSQL, планы выполнения запросов и iostat по всем устройствам.

Послесловие

Представленный анализ демонстрирует не просто диагностику конкретной инсталляции, но и принципиальную возможность внедрения эпистемологически строгого подхода в инженерную практику производительности баз данных. Методология PG_EXPECTO 8.1, сочетающая научный метод, самопроверку и защиту от галлюцинаций, позволяет перейти от разрозненных метрик к верифицированным причинно-следственным связям — с указанием уровня достоверности каждого вывода, фиксацией противоречий и честным перечнем недостающих данных. В эпоху усложнения стеков и роста требований к отказоустойчивости такой подход превращает нагрузочное тестирование из формальной процедуры в инструмент познания внутренней динамики системы, где каждое ожидание, каждый блок подкачки и каждая корреляция получают своё объяснение или требуют дальнейшего исследования. Именно в этой методологической дисциплине — а не в количестве собранных графиков — заключается истинная ценность PG_EXPECTO для сообщества PostgreSQL.