Эмпирическое исследование изменения профиля производительности СУБД PostgreSQL при обновлении с версии 15 до 17 на продуктивной нагрузке: анализ временных рядов утилизации дисковой подсистемы, сравнительная оценка ожиданий (DataFileRead, BufferIo), декомпозиция времени выполнения SQL-запросов по показателям I/O wait time, а также выявление аномального роста сбросов разделяемого кеша (cache_resets) и его корреляции с катастрофическим увеличением времени дискового ввода-вывода.
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 | Дзен
Предыдущая статья цикла
Предисловие
В процессе эксплуатации реляционных СУБД переход на более новую версию традиционно рассматривается как фактор повышения производительности и стабильности за счёт усовершенствований оптимизатора, планировщика и механизмов работы с памятью. Однако, как показывает практика, такое обновление может сопровождаться непредвиденной деградацией отдельных компонентов системы, особенно в условиях специфической схемы данных и сложившейся структуры запросов. В настоящей работе представлены результаты анализа инцидента, возникшего после обновления PostgreSQL с версии 15 до 17 на сервере под продуктивной нагрузкой, выразившегося в аномальном росте утилизации дисковой подсистемы при отсутствии явных признаков перегрузки центрального процессора. Основное внимание уделяется количественному сравнению профилей ожиданий, динамике времени ввода-вывода, изменению планов выполнения ключевых SQL-запросов и необъяснимому увеличению частоты сбросов разделяемого кеша.
Постановка проблемы
После обновления версии СУБД с 15 на 17 наблюдается изменение профиля производительности СУБД и аномальный рост утилизации дисковой подсистемы
Рис.1 График утилизации диска, используемого для файловой системы PGDATA
Эмпирическую базу исследования составляют данные, полученные из отчетов утилиты pgpro_pwr, собранных в идентичный временной интервал (09:00–10:00) в условиях продуктивной нагрузки на целевую СУБД:
- pgpro_pwr_5635_5636.clear.html — отчет, сформированный до обновления (версия 15);
- pgpro_pwr.20-21.clear.html — отчет, сформированный после обновления (версия 17).
Анализ отчета pgpro_pwr.20-21.clear.html
Ожидания СУБД
- DataFileRead (IO) — 4285 с (64,4% всех ожиданий) — абсолютное доминирование чтения с диска
- BufferIo (IPC) — 346 с (5,2%) — ожидания доступа к буферному кэшу (конкуренция)
- ClientRead (Client) — 1860 с (28%) — естественное ожидание клиента (не проблема)
- transactionid (Lock) — 40 с (0,6%) — блокировки на уровне транзакций
CPU и системные вызовы
- Явных признаков перегрузки CPU нет
- Высокий уровень BufferIo ожиданий косвенно указывает на возможную конкуренцию за буферный кэш
Дисковая подсистема (I/O)
- Суммарное время I/O (blk_read_time) для клиентских бэкендов — 25 070 с
- Среднее время чтения одного блока (8 КБ) ≈ 100 мкс — отличный показатель (SSD)
- Проблема не в скорости единичного I/O, а в огромном объёме чтений, которых можно избежать кэшированием и оптимизацией запросов
Полный отчет:
Сравнительный анализ SQL запросов по разделу "Top SQL by execution time"
Проведённый сравнительный анализ планов выполнения SQL-запросов на основе отчётов pgpro_pwr, сформированных до (PostgreSQL 15) и после (PostgreSQL 17) миграции, выявил разнонаправленную динамику производительности.
ℹ️Для пяти исследуемых запросов установлено, что ключевыми факторами деградации стали:
- многократный (в 11–105 раз) рост частоты вызовов при сохранении неоптимальных планов последовательного сканирования,
- отсутствие индексов по фильтруемым полям на фоне объективного увеличения объёмов табличных данных.
ℹ️В то же время для одного из запросов зафиксировано улучшение плана (переход с Bitmap Heap Scan на прямой Index Scan), что позволило компенсировать 39-кратный рост вызовов и подтвердило эффективность современного оптимизатора PostgreSQL 17 при наличии корректной индексной поддержки.
Методика анализа:
Полный отчет
Сравнительный анализ SQL запросов по разделу "Top SQL by I/O wait time"
Из 12 проанализированных SQL-запросов, отобранных по разделу «Top SQL by I/O wait time» в новом отчёте (20-21), для 11 зафиксировано статистически значимое ухудшение производительности при неизменных или снизившихся количествах вызовов. Ключевой фактор деградации — многократный рост времени ожидания ввода-вывода (DataFileRead): от 1,6 до 32 раз в зависимости от запроса. Планы выполнения остались неизменными для всех запросов, кроме одного (запрос 6, SELECT из _Reference109), где оптимизатор переключился с индекса первичного ключа на менее селективный индекс, что привело к росту прочитанных блоков с 2 292 до 34 526. Единственный запрос, не показавший деградации (SELECT из _InfoRg13163), характеризовался нулевым временем ввода-вывода в обоих периодах, что подтверждает дискретный характер наблюдаемой регрессии.
Методика анализа:
Полный отчет:
Сравнительный анализ профиля нагрузки в отчетах pgpro_pwr по разделу "Load distribution among heavily loaded databases"
Ключевой вывод:
- ℹ️Между отчётами произошло катастрофическое увеличение времени выполнения запросов (Total time +536%) при снижении их количества.
- ➡️Корневой причиной является лавинообразный рост дискового ввода-вывода (I/O time +2310%), спровоцированный многократным увеличением сбросов разделяемого кеша (Cache resets +3300%).
Следствия:
- ℹ️Эффективность кеширования упала: запросы вынуждены читать данные с диска вместо использования кеша.
- ⚠️Нагрузка на дисковую подсистему критически возросла, что может привести к деградации всего сервера.
Полный отчет
Углубленный анализ: причины роста Cache resets (+3300%)
Контекст:
➡️В сравниваемых отчетах количество сбросов разделяемого кеша (pgpro_stats_totals.cache_resets) увеличилось с 1 до 34 событий за интервал.
➡️Одновременно зафиксирован рост I/O time (+2310%) и Shared blocks read (+248%) при снижении числа выполненных запросов (–10%).
ℹ️Такая динамика указывает на потерю прогретых данных в кеше и вынужденный переход к физическим чтениям с диска.
Природа метрики:
В расширении pgpro_stats счетчик cache_resets фиксирует события, приводящие к принудительной очистке (сбросу) разделяемого кеша PostgreSQL — преимущественно кеша буферов данных (shared buffers) ➡️и/или⬅️ кеша планов запросов.
К таким событиям относятся:
- Явное выполнение команд DISCARD ALL или DISCARD PLANS.
- Сброс кеша при перезапуске обслуживающего процесса (backend) после фатальной ошибки.
- Принудительная инвалидация кеша расширениями или административными командами.
Полный отчет:
Подробнее о cache_resets
Итог
На текущий момент исследований - установить корневую причину аномальной утилизации IO после обновления версии 15 -> 17 не удалось.
Исследования продолжаются.
Послесловие
Проведённый анализ позволяет уверенно констатировать наличие устойчивой регрессии производительности дисковой подсистемы после миграции на PostgreSQL 17, проявляющейся в многократном росте времени ожидания DataFileRead, снижении эффективности буферного кэша и аномальном увеличении событий сброса разделяемого кеша. Несмотря на выявление статистически значимых корреляций между ростом cache_resets, увеличением объёма физических чтений и деградацией времени выполнения запросов, корневая причина наблюдаемых явлений на текущем этапе не установлена. Дальнейшие исследования должны быть направлены на детальный анализ поведения механизма управления shared buffers в PostgreSQL 17, проверку гипотез , изменений в логике инвалидации кеша планов, а также на воспроизведение инцидента в изолированной тестовой среде с идентичной схемой данных и трассировкой системных вызовов.