PG_EXPECTO 10.1.3 : Новые возможности нагрузочного тестирования СУБД PostgreSQL

Оригинал статьи на основном техническом канале Дзен: PG_EXPECTO 10.1.3 : Новые возможности нагрузочного тестирования СУБД PostgreSQL

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

Экспериментальная верификация диагностики преднамеренно созданных проблем производительности инфраструктуры и СУБД PostgreSQL на основе нагрузочного тестирования с пуассоновским распределением сессий и имитацией инцидента VACUUM FREEZE в среде PG_EXPECTO 10.1.3

Предисловие

Современные методики нагрузочного тестирования СУБД требуют не только генерации синтетической нагрузки, приближенной к реальным паттернам работы приложений, но и способности контролируемо воспроизводить аномальные режимы эксплуатации, такие как внезапное возрастание конкуренции за ресурсы или выполнение фоновых обслуживающих операций. В рамках настоящего исследования представлен комплекс PG_EXPECTO версии 10.1.3, расширяющий возможности нагрузочного тестирования PostgreSQL за счёт имитации пуассоновского потока сессий (период теста – бесконечный, среднее количество сессий – 40–50 в час) и встроенного сценария инцидента – принудительного выполнения VACUUM FREEZE на эталонной таблице pgbench_accounts. Ключевой особенностью эксперимента стало умышленное занижение критических параметров конфигурации СУБД (shared_buffers = 200 МБ, work_mem = 16 МБ, эффективный размер кэша – 1 ГБ) до заведомо недостаточного уровня.

Целью работы являлась экспериментальная проверка способности PG_EXPECTO корректно идентифицировать заранее известные проблемы инфраструктуры и проанализировать причины имитации инцидента производительности.

Дополнительные возможности по настройке нагрузочного тестирования версии PG_EXPECTO 10.1.3 с помощью файла конфигурации param.conf

• Базовый конфигурационный файл: param.conf
• Краткое описание дополнительных возможностей: param.conf.md

1. Нагрузочное тестирование с имитацией распределения Пуассона

# Параметры Пуассоновского распределения
period_hours = 2
average_load = 40

Результат : Период теста = 2 часа (+1 час на разогрев метрик), среднее количество сессий pgbench в час = 40.

2. Бесконечный тест с имитацией распределения Пуассона

# БЕСКОНЕЧНЫЙ ТЕСТ.
# ДЛЯ ОСТАНОВКИ
# /postgres/pg_expecto/sh/load_test/load_test_stop.sh
period_hours = -1
average_load = 40

Результат : Тест не будет остановлен , среднее количество сессий в каждой итерации теста = 40

3. Имитация инцидента (дополнительная нагрузка vacuum/freeze)

vacuum_incident = 1

Результат : В случайную минуту, в течении часа запускается дополнительная нагрузка на СУБД с помощью выполнения vacuum freeze на таблице pgbench_accounts

# Выполняем VACUUM через psql. Все настройки – только для этой сессии.
${PSQL} -d "${PGDATABASE}" -U "${PGUSER}" -v ON_ERROR_STOP=1 <<-SQL
SET vacuum_cost_delay = ${VACUUM_COST_DELAY};
SET vacuum_cost_limit = ${VACUUM_COST_LIMIT};
VACUUM FREEZE ${TABLE_NAME};

Подробнее : run_vacuum.sh

Эксперимент - бесконечный тест и имитации инцидента

Тестовые настройки СУБД

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

Данное решение принято для тестирования результатов анализа инцидента СУБД с применением инструкции PG_EXPECTO.

postgres=# show shared_buffers;
shared_buffers
----------------
200MB
(1 row)
postgres=# show work_mem ;
work_mem
----------
16MB
(1 row)

Конфигурация нагрузочного тестирования : param.conf

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Тестовая БД
testdb = default
# Тип синтетической нагрузки
load_mode = olap
# Параметры Пуассоновского распределения
period_hours = -1
average_load = 50
# Имитация инцидента - vacuum
vacuum_incident = 1
# Веса сценариев по умолчанию
scenario1 = 0.7
scenario2 = 0.2
scenario3 = 0.1
# Размер тестовой БД
#~10GB
scale = 685

Инцидент производительности СУБД в ходе нагрузочного тестирования

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

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

Формат txt

Autovacuum работает очень интенсивно (более 170 запусков в час), но удаляет мизерное количество страниц (80–122).
...
Длительность autovacuum в инциденте почти удвоилась (117,7 сек против 61,1 сек) при том же количестве операций – вероятно, из-за возросшей конкуренции за IO или блокировок.
...
За час создаётся ~480 временных файлов общим объёмом ~21 ГБ. Это прямое следствие использования диска для сортировок/хэшей, не помещающихся в work_mem (16 МБ).
...
Диск данных (vdd) – критическая перегрузка: util 100%, задержки чтения/записи >15 мс, очередь >50.
...
RAM (7,5 ГБ) с shared_buffers=200 МБ и effective_cache_size=1 ГБ – возможно, недостаточно для рабочего набора.

Формат html

Итог : Ключевые проблемы определены корректно.

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

Формат txt
Формат html

Итоговый аналитический отчёт по инциденту производительности PostgreSQL

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

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

Тестовый отрезок: 2026-06-05 12:30 – 13:30
Инцидент: 2026-06-05 13:30 – 14:30
Конфигурация:

• PostgreSQL 17.5 (Postgres Pro Enterprise), 8 vCPU, RAM 7.5 ГБ
• ⚠️shared_buffers = 200 МБ, effective_cache_size = 1 ГБ, work_mem = 16 МБ
• random_page_cost = 1.1 (SSD-ориентированное значение)
• checkpoint_timeout = 3600 с, max_wal_size = 4 ГБ, min_wal_size = 2 ГБ
• ⚠️autovacuum включён (workers=4, scale_factor=0.2, analyse_scale_factor=0.005)
• vm.dirty_background_ratio = 10%, vm.dirty_ratio = 30%, vm.swappiness = 1

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

• Подтверждено — значение получено из предоставленных метрик или прямого математического следствия.
• Вероятно — вывод основан на косвенных признаках, корреляциях или общеизвестных практиках, но не подтверждён прямыми данными.
• Предположение — гипотеза, для проверки которой необходимы дополнительные данные либо данные отсутствуют.
• Неизвестно — термин или метрика не фигурируют в отчёте, значение неизвестно.

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

1. Критическая перегрузка дискового устройства данных (vdd)

• Тезис: Дисковое устройство данных работает на пределе пропускной способности: утилизация 100%, задержки чтения/записи >15 мс, глубина очереди 54–72.
• Способ подтверждения: iostat показатели за оба периода: %util = 99,97–99,98%, r_await = 11–20 мс, w_await = 15–16 мс, aqu_sz = 54–72.
• Способ опровержения: Если бы %util был ниже 50%, а r_await и w_await <5 мс.
• Метка: Подтверждено☑️

2. Доминирование IO-ожиданий и их влияние на производительность

• Тезис: Ожидания ввода-вывода (IO) остаются критическим фактором в обоих периодах, причём в инциденте их связь с общими ожиданиями стала исключительно сильной (R²=0,92).
• Способ подтверждения: В тесте для IO: корреляция 0,7972, R²=0,64; в инциденте: корреляция 0,959, R²=0,92, ВКО 0,84.
• Способ опровержения: Если бы в инциденте R² для IO был ниже 0,6 или ВКО ниже 0,2.
• Метка: Подтверждено☑️

3. Два проблемных запроса генерируют почти все ожидания

• Тезис: Два конкретных запроса (queryid -76972891903573700 и 7783752063509965868) являются основными источниками IO- и LWLock-ожиданий: на них приходится >97% всех IO-ожиданий.
• Способ подтверждения: Диаграммы Парето, где на эти два queryid приходится 65–68% и 32–35% IO-ожиданий соответственно.
• Способ опровержения: Если бы распределение ожиданий было равномерным между многими запросами.
• Метка: Подтверждено☑️

4. Массовое создание временных файлов (temp_files)

• Тезис: За час создаётся ~480 временных файлов общим объёмом ~21 ГБ – прямое следствие того, что операции сортировки/хэширования не помещаются в work_mem (16 МБ).
• Способ подтверждения: temp_files = 479–481, temp_bytes ≈ 21 ГБ/час.
• Способ опровержения: Если бы temp_files отсутствовали или объём был менее 1 ГБ/час.
• Метка: Подтверждено☑️

5. Аномально долгие контрольные точки (checkpoint)

• Тезис: Время записи контрольной точки (2415–3167 секунд) и синхронизации (540–742 секунды) огромно; контрольные точки запускаются из-за заполнения max_wal_size (4 ГБ), а не по тайм-ауту.
• Способ подтверждения: 3–4 checkpoint за час при checkpoint_timeout=3600с (ожидалось 1); длительность записи >> тайм-аута.
• Способ опровержения: Если бы время записи было менее 600 секунд и checkpoint запускались только по тайм-ауту.
• Метка: Подтверждено☑️

⚠️6. Низкая эффективность autovacuum

• Тезис: Autovacuum запускается более 170 раз в час, но удаляет лишь 80–122 страницы из сотен тысяч оставшихся – параметр scale_factor=0.2 слишком консервативен для больших таблиц.
• Способ подтверждения: Оставлено страниц 430 340–450 193, удалено 80–122 (<0,03%).
• Способ опровержения: Если бы autovacuum удалял значительную долю мёртвых кортежей.
• Метка: Вероятно❓

⚠️7. Высокая конкуренция за CPU

• Тезис: Очередь процессов на CPU (procs r) стабильно превышает число ядер (8) в 3–4 раза, доля us+sy = 100% времени – хроническая нехватка CPU.
• Способ подтверждения: vmstat: procs r = 30–34 (при 8 ядрах), us+sy >80% – 100% периода.
• Способ опровержения: Если бы procs r был ниже числа ядер или us+sy <80%.
• Метка: Подтверждено☑️

⚠️8. Переключения контекста (cs) и прерывания (in) сильно коррелируют

• Тезис: Высокая корреляция между context switches и interrupts (r=0,946 в тесте, 0,758 в инциденте) указывает на то, что переключения контекста вызваны прерываниями от дискового IO.
• Способ подтверждения: Коэффициенты корреляции и R² из раздела 2.1.
• Способ опровержения: Если бы cs коррелировали в основном с us или sy.
• Метка: Подтверждено☑️

9. Недостаток свободной RAM и риск OOM

• Тезис: Свободная RAM постоянно менее 5% (100% периода) – это повышает риск отказа в выделении памяти (OOM) и может вызывать рециркуляцию страниц.
• Способ подтверждения: free RAM = 128–133 МБ при общей RAM 7,5 ГБ (<5%).
• Способ опровержения: Если бы свободной RAM было >10% постоянно.
• Метка: Подтверждено☑️

⚠️10. Появление ошибок lock_not_available в инциденте

• Тезис: Три ошибки lock_not_available (55P03) в инциденте указывают на попытки захвата блокировки, не удавшиеся из-за тайм-аута (deadlock_timeout=1000 мс) – косвенный признак конкуренции за ресурсы.
• Способ подтверждения: Лог ошибок за период инцидента.
• Способ опровержения: Если бы таких ошибок не было.
• Метка: Подтверждено☑️

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

Рекомендации для СУБД

1. Оптимизировать два доминирующих запроса

• Тезис: Необходимо получить планы выполнения queryid -76972891903573700 и 7783752063509965868, устранить массовые чтения и записи временных файлов, добавить индексы или переписать запросы.
• Способ подтверждения: После оптимизации должно снизиться значение DataFileRead и BuffileWrite в диаграммах Парето.
• Способ опровержения: Если после изменений IO-ожидания не уменьшатся.
• Метка: Вероятно❓

2. Увеличить work_mem

• Тезис: Увеличить work_mem с 16 МБ до 128–256 МБ (с учётом max_connections=100) для снижения использования temp_files.
• Способ подтверждения: Снижение temp_bytes и количества временных файлов.
• Способ опровержения: Если temp_files не уменьшатся.
• Метка: Вероятно❓

3. Настроить контрольные точки

• Тезис: Увеличить max_wal_size до 16–32 ГБ и уменьшить checkpoint_timeout до 900–1800 с, чтобы контрольные точки были более частыми, но менее тяжёлыми.
• Способ подтверждения: Снижение времени записи и синхронизации checkpoint, уменьшение max WAL usage.
• Способ опровержения: Если время записи останется более 1000 секунд.
• Метка: Вероятно❓

4. Настроить autovacuum

• Тезис: Уменьшить autovacuum_vacuum_scale_factor для больших таблиц (например, до 0,05) и увеличить autovacuum_max_workers (до 8).
• Способ подтверждения: Увеличение доли удалённых страниц при том же количестве запусков.
• Способ опровержения: Если autovacuum продолжит удалять менее 1% оставшихся страниц.
• Метка: Вероятно❓

5. Увеличить shared_buffers и effective_cache_size

• Тезис: Увеличить shared_buffers с 200 МБ до 1–2 ГБ (25% RAM), а effective_cache_size – до 4–5 ГБ для улучшения кэширования.
• Способ подтверждения: Рост hit ratio и снижение DataFileRead.
• Способ опровержения: Если hit ratio не изменится или снизится.
• Метка: Вероятно❓

Рекомендации для инфраструктуры

1. Улучшить дисковую подсистему данных

• Тезис: Перенести табличное пространство данных на более быстрый диск (NVMe) или выделить отдельный LUN с лучшей IOPS/латентностью; увеличить effective_io_concurrency до 100–200.
• Способ подтверждения: Снижение %util, r_await, w_await и aqu_sz по данным iostat.
• Способ опровержения: Если задержки и утилизация останутся на прежнем уровне.
• Метка: Подтверждено☑️

2. Настроить параметры dirty pages ядра

• Тезис: Уменьшить vm.dirty_ratio до 10–15% и vm.dirty_background_ratio до 5%, чтобы снизить накопление грязных страниц и синхронные записи.
• Способ подтверждения: Снижение корреляции dirty pages с wa и bo, уменьшение длительности checkpoint.
• Способ опровержения: Если dirty pages продолжат достигать 40%+ RAM.
• Метка: Вероятно❓

3. Увеличить объём RAM

• Тезис: Увеличить RAM до 16–32 ГБ, чтобы рабочий набор данных помещался в кэш страниц и shared_buffers, и всегда был запас свободной памяти.
• Способ подтверждения: Снижение свободной RAM <5% более не наблюдается, уменьшение IO-ожиданий.
• Способ опровержения: Если после увеличения RAM IO-ожидания не снизятся.
• Метка: Вероятно❓

4. Масштабировать CPU при необходимости

• Тезис: После устранения IO-узких мест, если загрузка CPU останется высокой, увеличить число vCPU или использовать реплики чтения.
• Способ подтверждения: После оптимизации запросов и IO показатель procs r станет близким к числу ядер.
• Способ опровержения: Если procs r снизится сам собой после других оптимизаций.
• Метка: Предположение☑️

Необходимая дополнительная информация для продолжения анализа и оптимизации производительности СУБД и инфраструктуры

1. Планы выполнения (query plans) для двух проблемных queryid, включая реальное использование памяти, сортировок и хэш-таблиц.
2. Размеры объектов БД (таблиц, индексов) и количество мёртвых кортежей для оценки эффективности autovacuum.
3. Логи PostgreSQL за период инцидента для выявления предупреждений (checkpoint occurring too frequently, temporary file size exceeds temp_file_limit и т.п.).
4. Текущие значения параметров автовакуума для конкретных таблиц (per-table settings).
5. Статистика по блокировкам (pg_locks, pg_blocking_pids) для анализа lock_not_available.
6. Данные о сетевой задержке и пропускной способности (если есть удалённые подключения).
7. Тип и характеристики дискового массива (HDD/SSD, RAID-уровень, общая нагрузка на гипервизоре) для проверки несоответствия random_page_cost=1.1 реальному оборудованию.
8. Тренды долгосрочной статистики (а не только за 2 часа) для выявления сезонности или постепенной деградации.

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

В ходе эксперимента с бесконечным пуассоновским потоком сессий и имитацией инцидента VACUUM FREEZE комплекс PG_EXPECTO 10.1.3 позволил корректно и с высокой степенью детализации установить все преднамеренно заложенные дефекты инфраструктуры и конфигурации PostgreSQL.

Аналитический отчёт, сгенерированный инструментом, зафиксировал критическую перегрузку дискового устройства данных (утилизация 99,97–99,98 %, задержки чтения/записи >15 мс, глубина очереди 54–72), что подтверждено метриками iostat; доминирование ожиданий ввода-вывода с коэффициентом детерминации R² = 0,92 в период инцидента; массовое создание временных файлов (около 480 файлов объёмом ~21 ГБ/час) вследствие недостаточного work_mem; аномальную длительность контрольных точек (2415–3167 секунд записи); низкую эффективность автовакуума (более 170 запусков в час при удалении менее 0,03 % мёртвых страниц); хроническую нехватку оперативной памяти (свободно <5 % от 7,5 ГБ) и процессорного времени (очередь на CPU в 3–4 раза превышает число ядер).

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

Послесловие

Представленный эксперимент демонстрирует, что PG_EXPECTO 10.1.3 выступает не только как генератор нагрузки, но и как полноценная платформа для воспроизведения и последующего анализа инцидентов производительности PostgreSQL в контролируемых условиях.

📋Дальнейшее развитие комплекса предполагает расширение библиотеки сценариев инцидентов (имитация сетевых задержек, всплеска блокировок и т.п.).