GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
Результаты нагрузочного тестирования
Предисловие
Настоящий отчет подготовлен по результатам комплексного корреляционного анализа производительности СУБД PostgreSQL и инфраструктуры сервера за период 12 февраля 2026 года (14:11 – 16:00). Цель работы — выявление узких мест, количественная оценка влияния различных типов событий ожидания на операционную скорость базы данных, а также анализ системных метрик (vmstat) для определения первопричин наблюдаемой деградации производительности.
В основе отчета лежат методы статистического анализа, включая регрессионный анализ трендов, расчет коэффициентов корреляции и детерминации, а также авторская метрика ВКО (Взвешенная корреляция ожиданий) для ранжирования приоритетности проблем. Все выводы и рекомендации базируются исключительно на собранных за указанный период данных. Отчет предназначен для администраторов баз данных и инженеров, отвечающих за стабильность и эффективность работы PostgreSQL.
Запрос нейросети
Сформируй сводный отчет по производительности СУБД и инфраструктуры.
Для формирования отчета используй списки, вместо таблиц.
Состав отчета:
# Общая информация
# Общий анализ
## 1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД
### Итог по разделу "1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД"
## 2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat
### Итог по разделу "2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat"
## 3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat
### Итог по разделу "3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat"
## 4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID
### Итог по разделу "4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID"
# Детальный анализ – граничные значения и корреляции
## Ожидания СУБД
## Память и буферный кэш
## Дисковая подсистема (I/O)
## CPU и системные вызовы
## Блокировки и ожидания LWLock
## Анализ запросов (queryid)
# Ключевые проблемы
## Проблемы СУБД
## Проблемы инфраструктуры
# Рекомендации
## Рекомендации по настройкам СУБД
## Рекомендации по настройкам операционной системы
# Заключение
Сводный отчет по производительности СУБД и инфраструктуры
Общая информация
Дата анализа: 2026-02-12 14:11 – 16:00
Версия PostgreSQL: 17.5 на Linux (x86_64, gcc 11.4.1)
Аппаратная конфигурация:
- CPU: 8 виртуальных ядер Intel Xeon (Skylake)
- RAM: 7.5 GB
- Диски: отдельные LVM-тома для данных (/data), WAL (/wal), логов (/log)
Ключевые настройки PostgreSQL:
- shared_buffers = 4 GB
- work_mem = 128 MB
- effective_cache_size = 6 GB
- max_parallel_workers_per_gather = 1
- checkpoint_timeout = 5 min
- autovacuum настроен агрессивно (масштабные коэффициенты 0.01/0.005)
Настройки ОС (vm):
- dirty_background_ratio = 5%
- dirty_ratio = 10%
- swappiness = 1
- vfs_cache_pressure = 50
Общий анализ
1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД
Общая картина: операционная скорость растет вместе с общим временем ожиданий (R²=0.69), что говорит об увеличении нагрузки.
Тренд ожиданий: устойчивый рост (R²=0.89, угол наклона +43.3).
Интегральные приоритеты типов ожиданий (чем выше, тем критичнее):
- IPC (межпроцессное взаимодействие) – 0.8482
- Timeout – 0.5919
- LWLock – 0.4048
- IO – 0.3998
- BufferPin, Extension, Lock – приоритет 0 (незначимы в данном периоде)
Итог по разделу "1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД"
Наибольший вклад в ожидания вносят IPC и Timeout.
Ожидания IPC требуют немедленного анализа (критическое значение ВКО).
Наблюдается положительная корреляция между скоростью операций и ожиданиями – система работает на пределе, рост нагрузки ведет к росту задержек.
2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat
procs r (очередь на выполнение) – негативный тренд (умеренный рост), R²=0.49.
procs b (процессы в uninterruptible sleep) – негативный тренд, R²=0.53.
cpu wa (ожидание I/O) – позитивный тренд (снижение), R²=0.82.
cpu id (простой CPU) – негативный тренд, очень высокая скорость падения (коэф. тренда +35.25, R²=0.83). Это критический сигнал.
Итог по разделу "2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat"
- Растет конкуренция за CPU (увеличение r) и число процессов, заблокированных в I/O (рост b).
- Падение простоя CPU (id) при одновременном снижении wa указывает на то, что процессор загружен полезной работой (user/system), а не ожиданием диска.
3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat
IPC очень сильно коррелирует с:
- переключениями контекста (cs) – R=0.895, R²=0.80
- прерываниями (in) – R=0.954, R²=0.91
- системным временем (sy) – R=0.950, R²=0.90
Timeout сильно коррелирует с sy (R=0.972), in (R=0.936), cs (R=0.861), r (R=0.758).
LWLock сильно связан с sy (R=0.936), умеренно с cs (R=0.615) и swap out (R=0.623).
IO сильно коррелирует с чтениями с диска (bi) – R=0.928, и умеренно с записями (bo) – R=0.756.
Индекс приоритета корреляций (CPI) – самые высокие связи:
- cs ↔ in (0.9845)
- Timeout ↔ sy (0.9642)
- IPC ↔ in (0.9395)
- IPC ↔ sy (0.9326)
Итог по разделу "3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat"
Ожидания IPC и Timeout практически полностью объясняются системными метриками (cs, in, sy) – высокая активность ядра, переключения контекста и прерывания.
LWLock ожидания также связаны с системным временем, что указывает на конкуренцию за структуры в shared memory.
I/O ожидания напрямую зависят от объема дисковых операций.
4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID
IPC: 100% ожиданий приходится на queryid 7143487894308847288 (функция scenario1).
Timeout: 99.48% ожиданий – тот же queryid.
LWLock: 94.42% ожиданий – тот же queryid.
IO: 67.98% – scenario1, 31.99% – queryid -1879773681171496870 (scenario3).
Итог по разделу "4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID"
Доминирующий источник всех проблем – запрос select scenario1().
Второстепенный вклад в I/O ожидания вносит select scenario3().
Оптимизация этих двух запросов критически важна для снижения общей нагрузки.
Детальный анализ – граничные значения и корреляции
Ожидания СУБД
Медианные значения за период:
- Общие ожидания: 24 714
- IPC: 19 427
- IO: 5 015
- LWLock: 135
- Timeout: 146
Максимумы:
- Общие ожидания: 35 543
- IPC: 27 688
- IO: 6 937
- LWLock: 576
- Timeout: 341
Тренд ожиданий устойчиво растет (угол +43.3, R²=0.89).
Память и буферный кэш
shared_buffers = 4 GB
Shared buffers hit ratio:
- Минимум: 49.6%
- Медиана: 55.6%
- Максимум: 66.8%
ALARM: критически низкое значение – более половины обращений к данным уходят на диск.
Корреляция операционной скорости с чтениями: R=0.801, R²=0.64 – рост скорости зависит от дисковых чтений.
Корреляция с записями: R=0.911, R²=0.83 – скорость еще сильнее ограничена записью на диск.
Dirty pages: размер растет к концу периода до ~3.1 MB (не превышает порогов dirty_ratio), но наблюдаются значимые корреляции:
- с procs b (R=0.713, R²=0.51) – процессы блокируются в I/O
- с sy (R=0.799, R²=0.64) – нагрузка на ядро при управлении памятью
Дисковая подсистема (I/O)
IO ожидания практически полностью объясняются объемом чтений (bi): R²=0.86.
Связь с записями (bo) слабее (R²=0.57), но тоже значима.
Грязные страницы коррелируют с procs b (R=0.713) – подтверждение I/O-узкого места.
Операционная скорость сильно зависит от записи – возможно, узким местом является диск под WAL или данными.
CPU и системные вызовы
Средняя загрузка CPU: us 27%, sy 6%, wa 16%, id 50%.
Корреляции переключений контекста (cs):
- с in (прерывания) – R=0.984 (закономерно)
- с sy – R=0.793
- с us – R=0.699
IPC и Timeout ожидания тесно связаны с sy, что указывает на высокие системные издержки.
Блокировки и ожидания LWLock
Приоритет LWLock – 0.4048.
Сильная корреляция с sy (R=0.936) – конкуренция за легковесные блокировки требует времени ядра.
Умеренная связь с swap out (R=0.623) – возможное влияние нехватки памяти.
Анализ запросов (queryid)
Основной «тяжелый» запрос: select scenario1() (7143487894308847288) – вызывает почти 100% ожиданий IPC, Timeout, LWLock и 68% IO.
Второй запрос: select scenario3() (-1879773681171496870) – дает 32% IO ожиданий.
Ключевые проблемы
Проблемы СУБД
Крайне низкий hit ratio shared buffers (<60%) – база данных слишком часто читает с диска.
Высокая зависимость производительности от дисковой подсистемы, особенно от записи.
Доминирование одного запроса (scenario1), который генерирует основную массу всех типов ожиданий.
Значительные ожидания IPC – возможно, связаны с интенсивным обменом сигналами между процессами.
LWLock конкуренция – указывает на борьбу за буферный кэш или другие структуры в shared memory.
Проблемы инфраструктуры
Резкое падение cpu id при одновременном снижении wa – CPU занят, но не I/O, а пользовательскими/системными задачами.
Рост очереди процессов (r) и числа процессов в uninterruptible sleep (b) – нарастает конкуренция за ресурсы.
Свободная RAM менее 5% в 100% времени – система постоянно находится в состоянии memory pressure.
Корреляции dirty pages с b и sy – признаки проблем с управлением памятью и фоновой записью.
Рекомендации
Рекомендации по настройкам СУБД
- Увеличить shared_buffers до 6–7 GB (с учетом 7.5 GB RAM), оставляя память для ОС и кэша файловой системы.
- Оптимизировать запросы scenario1 и scenario3 – проанализировать их планы, добавить индексы, переписать логику.
- Увеличить max_parallel_workers_per_gather (с 1 до 2–4), если запросы могут выполняться параллельно.
- Настроить контрольные точки: увеличить checkpoint_timeout до 10–15 мин и max_wal_size до 64 GB для сглаживания пиков записи.
- Для уменьшения IPC-ожиданий проверить параметры синхронной репликации (synchronous_commit, wal_sync_method); рассмотреть асинхронный режим, если допустимо.
- Мониторить и при необходимости увеличить autovacuum_work_mem для ускорения очистки горячих таблиц.
Рекомендации по настройкам операционной системы
Увеличить пороги dirty pages:
- vm.dirty_background_ratio = 10 (с 5)
- vm.dirty_ratio = 20 (с 10)
- Это позволит дольше накапливать записи и сгладить нагрузку на диск.
Настроить vm.vfs_cache_pressure до 100–200 для более активного освобождения кэша inode/dentry.
Рассмотреть увеличение vm.swappiness до 10–20, чтобы при нехватке памяти немного активнее вытеснять редко используемые страницы (но следить за свопом).
Проверить, не упирается ли диск в пропускную способность – при возможности использовать более быстрые накопители (NVMe) или разделить нагрузку по разным физическим дискам.
Увеличить объем RAM – текущий дефицит свободной памяти требует масштабирования.
Заключение
Система работает в условиях высокой нагрузки, вызванной неоптимальным запросом scenario1 и недостаточным кэшированием данных в shared buffers. Низкий hit ratio приводит к интенсивному дисковому I/O, что в сочетании с ограниченной памятью вызывает рост процессорных издержек (системное время, переключения контекста). Наблюдается критическое падение простоя CPU и постоянный дефицит свободной RAM.
Первоочередные действия:
- Оптимизация/переписывание запроса scenario1.
- Увеличение shared_buffers.
- Настройка порогов dirty pages ОС.
В долгосрочной перспективе – апгрейд дисковой подсистемы и добавление оперативной памяти.
Послесловие
Проведенный анализ выявил системную проблему: производительность СУБД в настоящее время ограничена неэффективным кэшированием (низкий shared buffers hit ratio) и высокой нагрузкой на дисковую подсистему, что усугубляется доминированием одного «тяжелого» запроса. Рост системных издержек (переключения контекста, прерывания) на фоне дефицита оперативной памяти сигнализирует о приближении к критическому порогу масштабируемости имеющихся ресурсов.
Реализация предложенных рекомендаций, в первую очередь оптимизация запроса scenario1 и увеличение shared_buffers, позволит снизить нагрузку на диск и уменьшить ожидания IPC и LWLock. Настройка параметров виртуальной памяти ОС (dirty_ratio) сгладит пиковые нагрузки записи. Выполнение этих мер должно перевести систему в более стабильное состояние и создать запас производительности для дальнейшего роста.
При сохранении или усилении негативных трендов после оптимизации может потребоваться более глубокая диагностика на уровне архитектуры приложений или рассмотрение вопроса о расширении аппаратных ресурсов (RAM, диски).