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

Сводный сравнительный отчет по производительности СУБД и инфраструктуры

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

• Версия PostgreSQL: 17.5 на x86_64-pc-linux-gnu
• Аппаратная платформа: 8 виртуальных CPU (Intel Xeon Skylake), RAM 7.5 ГБ, диски разделены LVM (data, wal, log на отдельных томах)
• ЭКСПЕРИМЕНТ-1 (pgpro_tune): период наблюдения 2026-03-14 09:56 – 12:18
• ЭКСПЕРИМЕНТ-2 (ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС): период наблюдения 2026-03-14 14:25 – 16:47

Сравнение конфигурации СУБД и операционной системы

Параметры СУБД (изменённые):

• shared_buffers:
  Эксперимент 1: 1919 MB
  Эксперимент 2: 3 GB (+57%)
• effective_cache_size:
  Эксперимент 1: 3838 MB
  Эксперимент 2: 5120 MB (+33%)
• maintenance_work_mem:
  Эксперимент 1: 479 MB
  Эксперимент 2: 1024 MB (+114%)
• autovacuum_work_mem:
  Эксперимент 1: 239 MB
  Эксперимент 2: 512 MB (+114%)
• effective_io_concurrency:
  Эксперимент 1: 200 (SSD)
  Эксперимент 2: 500 (NVMe) – соответствует фактическому оборудованию (NVMe SSD)
• Остальные параметры (work_mem, max_connections, bgwriter, wal, autovacuum) совпадают.

Параметры ОС (vm.dirty_*):

• vm.dirty_background_ratio:
  Эксперимент 1: 10
  Эксперимент 2: 5 (более ранний запуск фоновой записи)
• vm.dirty_ratio:
  Эксперимент 1: 30
  Эксперимент 2: 15 (более ранняя синхронная блокировка)
• Остальные параметры (dirty_expire_centisecs, dirty_writeback_centisecs, swappiness) совпадают.

Общий анализ операционной скорости и ожиданий СУБД

Сравнительный анализ граничных значений операционной скорости (SPEED) и ожиданий СУБД (WAITINGS)

• SPEED (операций/интервал):
  Эксперимент 1: MIN 381 424, MEDIAN 433 329, MAX 700 915
  Эксперимент 2: MIN 473 423, MAX 770 135, MEDIAN 533 803
  Изменение: медианная скорость выросла на +23%, максимальная – на +10%.
• WAITINGS (суммарные ожидания):
  Эксперимент 1: MIN 49 576, MEDIAN 79 295, MAX 225 664
  Эксперимент 2: MIN 48 615, MEDIAN 78 240, MAX 224 437
  Изменение: медианные ожидания снизились незначительно (-1,3%), максимум – без изменений.

Сравнительный анализ трендов операционной скорости (SPEED) и ожиданий СУБД (WAITINGS)

• Тренд SPEED (линейная регрессия Y = a + b·t):
  Эксперимент 1: R² = 0.85, угол наклона = +42.73 (рост)
  Эксперимент 2: R² = 0.87, угол наклона = +43.07 (рост)
  Вывод: в обоих экспериментах скорость растёт во времени, качество модели очень высокое, темп роста практически одинаков.
• Тренд WAITINGS:
  Эксперимент 1: R² = 0.88, угол наклона = +43.23 (рост)
  Эксперимент 2: R² = 0.87, угол наклона = +43.02 (рост)
  Вывод: ожидания также растут синхронно со скоростью, темпы роста совпадают.
• Регрессия SPEED по WAITINGS:
  Эксперимент 1: R² = 0.99, угол = +44.83
  Эксперимент 2: R² = 0.99, угол = +44.85
  Вывод: практически линейная зависимость (почти 1:1), рост скорости сопровождается пропорциональным ростом ожиданий.

1. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД

• Основной тип ожиданий: в обоих экспериментах IO (интегральный приоритет 0.6271, критический уровень).
• Корреляции IO с другими типами:
  IO практически полностью определяет все ожидания (R² = 1.0).
  Остальные типы (IPC, Lock, LWLock, Timeout) имеют высокую корреляцию с общими ожиданиями, но их вклад в общее время ничтожен (ВКО < 0.01).
• Рекомендации по IO: в обоих отчётах одинаковые – оптимизация запросов, индексов, настройка effective_io_concurrency и random_page_cost (в эксперименте 2 уже учтено: effective_io_concurrency = 500, random_page_cost = 1.1 для NVMe).

Итог по разделу "1. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД"

• Структура ожиданий не изменилась – доминирует IO.
• Увеличение буферов и настройка ОС не повлияли на типы ожиданий; IO остаётся единственным значимым фактором.

2. СРАВНИТЕЛЬНЫЙ ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat

• procs → r (очередь на CPU):
  Эксперимент 1: R² = 0.75 (хорошая модель), наклон = +40.86 (рост)
  Эксперимент 2: R² = 0.19 (непригодная модель)
  Вывод: в эксперименте 2 тренд по r стал незначимым – возможно, снизилась конкуренция за CPU.
• procs → b (процессы в uninterruptible sleep, ожидание IO):
  Эксперимент 1: R² = 0.87, наклон = +42.96 (очень высокий рост)
  Эксперимент 2: R² = 0.87, наклон = +43.00 (аналогично)
  Вывод: проблема сохраняется – число процессов, заблокированных на IO, растёт с высокой скоростью.
• cpu → wa (простой CPU в ожидании IO):
  Эксперимент 1: R² = 0.13 (непригодная), наклон незначим
  Эксперимент 2: R² = 0.19 (непригодная), наклон незначим
  Вывод: wa не имеет устойчивого тренда, но его абсолютные значения очень высоки.
• cpu → id (полный простой CPU):
  Эксперимент 1: R² = 0.96, наклон = -44.45 (сильное падение)
  Эксперимент 2: R² = 0.95, наклон = -44.27 (аналогично)
  Вывод: время простоя CPU быстро сокращается – система всё больше времени проводит в обработке (вероятно, из-за роста скорости).

Итог по разделу "2. СРАВНИТЕЛЬНЫЙ ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat"

• Главная проблема – рост числа процессов в состоянии «b» (ожидание IO) и падение idle CPU – осталась без изменений.
• В эксперименте 2 исчез тренд по очереди на CPU (r), что может указывать на более равномерную загрузку процессоров.

3. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat

• Относительные показатели (оба эксперимента):
  Свободная RAM < 5% – 100% времени (ALARM).
  wa > 10% – 100% времени (ALARM).
  b > числа ядер CPU – 100% времени (ALARM).
  Свопинг отсутствует.
• Корреляции IO:
  IO ↔ bi (чтения): в эксперименте 1 – слабая корреляция (0.28), в эксперименте 2 – несущественна.
  IO ↔ bo (записи): в обоих экспериментах очень высокая корреляция (0.88–0.90), R² ~0.77–0.81 – подтверждает, что IO ожидания связаны со сбросом грязных страниц.
  IO ↔ wa: в эксперименте 1 корреляция отсутствует, в эксперименте 2 – слабая (0.14) и статистически незначимая на 95% уровне.
• Корреляции dirty pages:
  dirty pages ↔ b: в обоих экспериментах очень высокая корреляция (~0.99, R²=0.99) – чем больше грязных страниц, тем больше процессов блокируется на IO.
  dirty pages ↔ bo: высокая корреляция (0.91–0.92, R²~0.82–0.84) – объём записи на диск тесно связан с размером dirty pages.
  dirty pages ↔ sy: высокая корреляция (0.95–0.97) – рост dirty pages увеличивает системное время CPU (накладные расходы ядра).
  dirty pages ↔ free: в эксперименте 1 корреляция отсутствует, в эксперименте 2 – сильная отрицательная (-0.78, R²=0.61) – при увеличении dirty pages свободная память резко падает.
• shared_buffers hit ratio:
  Эксперимент 1: медиана 96.80%, мин 96.18%, макс 98.51%
  Эксперимент 2: медиана 97.01%, мин 96.49%, макс 98.45%
  *Небольшое улучшение (+0.2 п.п.) благодаря увеличенным shared_buffers.*

Итог по разделу "3. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat"

• В эксперименте 2 проявилась новая проблема – сильная зависимость dirty pages от свободной памяти. Это указывает на дефицит оперативной памяти и агрессивное использование кэша.
• Остальные корреляции (dirty pages ↔ b, dirty pages ↔ bo, dirty pages ↔ sy) сохранились на том же высоком уровне.

4. СРАВНЕНИЕ ДИАГРАММ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID

• WAIT_EVENT_TYPE:
  Эксперимент 1: 99.95% ожиданий – DataFileRead (IO)
  Эксперимент 2: 99.96% ожиданий – DataFileRead (IO)
  Без изменений.
• QUERYID (основной потребитель):
  В обоих экспериментах запрос -2753873076014177353 (select scenario1()) вызывает ~85% всех ожиданий IO.
  Остальные запросы (scenario2, scenario3) вносят незначительный вклад.
• Список SQL:
  Эксперимент 1: те же три запроса, что и в эксперименте 2.
  Нагрузка полностью идентична по составу.

Итог по разделу "4. СРАВНЕНИЕ ДИАГРАММ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID"

• Тип нагрузки и основные проблемные запросы не изменились. Узкое место – чтение данных с диска (DataFileRead) в рамках сценария scenario1.

Детальный анализ – граничные значения и корреляции

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

• Эксперимент 1: медиана ожиданий 79 295, IO составляет 99.95%.
• Эксперимент 2: медиана ожиданий 78 240, IO – 99.96%.
• Вывод: незначительное снижение общего времени ожиданий (на ~1%).

Память и буферный кэш

• Эксперимент 1:
  shared_buffers = 1919 МБ
  hit ratio = 96.8%
  свободная RAM = очень мала (<5% всё время), но корреляция dirty pages ↔ free отсутствует.
• Эксперимент 2:
  shared_buffers = 3 ГБ (+57%)
  hit ratio = 97.0% (+0.2 п.п.)
  свободная RAM – ещё меньше, появилась сильная отрицательная корреляция dirty pages ↔ free.
• Вывод: увеличение shared_buffers дало микроскопический выигрыш в hit ratio, но усугубило давление на память.

Дисковая подсистема (I/O)

• В обоих экспериментах:
  Основное событие – DataFileRead.
  Сильная корреляция IO с bo (записанные блоки) – сброс грязных страниц через background writer / checkpoint.
  Очень высокая корреляция dirty pages с b (процессы в uninterruptible sleep) – прямое влияние на производительность.
  cpu wa стабильно выше 60% (критично).
• Изменения: незначительное снижение wa (медиана с 68% до 66%).

CPU и системные вызовы

• Высокие корреляции cs ↔ in, cs ↔ us, cs ↔ sy сохраняются – много переключений контекста, связанных с пользовательским временем и прерываниями.
• В эксперименте 2 несколько снизилась корреляция cs ↔ sy (с 0.95 до 0.97 осталась высокой), но появилась сильная связь dirty pages ↔ sy (накладные расходы ядра на управление памятью).
• Вывод: нагрузка на CPU сместилась в сторону обработки dirty pages.

Блокировки и ожидания LWLock

• В обоих экспериментах их вклад в общее время ожиданий ничтожен (ВКО < 0.01), несмотря на статистически значимые корреляции. Не являются проблемой.

Анализ запросов (queryid)

• Единственный запрос, вызывающий 85% ожиданий – select scenario1().
• Необходима его оптимизация (индексы, переписывание, возможно, материализованные представления).

Ключевые проблемы

Проблемы СУБД

1. Доминирование IO-ожиданий (DataFileRead) – более 99% всех ожиданий, вызванных запросом scenario1.
2. Низкая эффективность кэширования – даже после увеличения shared_buffers hit ratio остаётся ~97%, что недостаточно для существенного снижения IO.
3. Неоптимальный запрос – scenario1 генерирует основную нагрузку на чтение с диска.

Проблемы инфраструктуры

1. Хронический дефицит оперативной памяти – свободная RAM <5% на всём протяжении обоих тестов, что приводит к росту dirty pages и блокировкам процессов (b).
2. Высокая нагрузка на диск – процессы массово находятся в uninterruptible sleep (b), wa >60%.
3. Накладные расходы ядра – рост dirty pages вызывает рост системного времени (sy) и переключений контекста.
4. В эксперименте 2 – дополнительное ухудшение: при увеличении shared_buffers зависимость dirty pages от свободной памяти стала критической (корреляция -0.78). Система работает на пределе памяти.

Итоговый анализ влияния мероприятий "ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek)"

• Положительные эффекты:
  Медианная операционная скорость выросла на 23% (с 433k до 534k операций).
  Небольшое снижение медианных ожиданий (~1%).
  Улучшение hit ratio на 0.2 п.п.
  Незначительное снижение cpu wa (с 68% до 66%).
  Исчез тренд роста очереди на CPU (r), что может указывать на более равномерную загрузку.
• Отрицательные эффекты / нерешённые проблемы:
  Основное узкое место – IO-ожидания от запроса scenario1 – не устранено.
  Рост числа процессов в состоянии «b» продолжается с той же скоростью.
  Дефицит памяти усугубился: появилась сильная корреляция dirty pages ↔ free, что сигнализирует о нехватке RAM для обслуживания увеличенного shared_buffers.
  Системное время CPU (sy) выросло из-за обработки dirty pages.

Заключение: Оптимизация параметров (увеличение буферов, настройка vm.dirty, соответствие effective_io_concurrency оборудованию) дала прирост скорости, но не решила главную проблему – избыточные чтения с диска. Более того, повышение shared_buffers без увеличения физической памяти привело к критическому давлению на память. Дальнейшие усилия должны быть направлены на оптимизацию запроса scenario1 (индексация, пересмотр логики) и, возможно, увеличение RAM.