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

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

Содержание

• Предисловие

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

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

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

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

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

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

Предисловие

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

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

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

Базовый конфигурационный файл: param.conf

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

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

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

period_hours = 2

average_load = 40

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

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

# БЕСКОНЕЧНЫЙ ТЕСТ.

# ДЛЯ ОСТАНОВКИ

# /postgres/pg_expecto/sh/load_test/load_test_stop.sh

period_hours = -1

average_load = 40

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

1.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

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

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

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

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

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

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Тестовая БД
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

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

Операционная скорость

Рис.1 График изменения операционной скорости в процессе инцидента.

Ожидания СУБД

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

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

Формат 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 ГБ – возможно, недостаточно для рабочего набора.

source.txt — Яндекс Диск

Формат html

source.html — Яндекс Диск

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

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

Формат txt

result.txt — Яндекс Диск

Формат html

result.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 часа) для выявления сезонности или постепенной деградации.

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

В ходе эксперимента с бесконечным пуассоновским потоком сессий и имитацией инцидента 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 в контролируемых условиях.