Влияние обновления ядра СУБД на профиль нагрузки, дисковый ввод-вывод и планы выполнения Top-SQL запросов: выявление регрессии чтения/записи на операциях типа FASTTRUNCATE и индексных выборках, а также оценка вклада изменения параметров фоновой записи (enable_background_freezer, wal_buffers, wal_writer_delay) и аномального роста физических чтений конкретного запроса к таблице _Document4154.
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
Philosophical_instruction_BETA_v5.1.md - Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.
Предисловие
Переход на новую мажорную версию СУБД сопряжён с риском немонотонного изменения метрик производительности: наряду с ожидаемым улучшением процессорной эффективности и стабилизации разделяемого кеша могут возникать неочевидные регрессии в подсистеме ввода-вывода, связанные как с внутренними изменениями оптимизатора и фоновых процессов, так и с независимой эволюцией данных и паттернов запросов.
Данная работа представляет собой практическую реализацию методики сравнительного анализа двух последовательных снимков pgpro_pwr, полученных до и после обновления с Postgres Pro Enterprise 15.12 на 17.9.
Задача
- Практическая проверка методики формирования аналитического отчета на основе двух отчетов pgpro_pwr .
- Анализ влияния обновления версии СУБД с Postgres Pro Enterprise 15.12 до 17.9 на профили нагрузки , утилизации дисковой подсистемы и планы выполнения Top-SQL запросов.
Входные данные для анализа
Postgres Pro Enterprise 15.12
Утилизация диска, используемого файловой системой PGDATA:
Рис.1 График изменения утилизации диска за период 09:00 - 10:00 08.05.2026
Postgres Pro Enterprise 17.9
Утилизация диска, используемого файловой системой PGDATA:
Рис.2 График изменения утилизации диска за период 09:00 - 10:00 13.05.2026
Отчеты по снимкам pgpro_pwr:
- pgpro_pwr.42004-42005.clear.html : Postgres Pro Enterprise 15.12
- pgpro_pwr.49-50.clear.html : Postgres Pro Enterprise 17.9
Инструкция по формированию сводного сравнительного отчета
Аналитический отчёт: сравнение производительности PostgreSQL после обновления с 15.12 до 17.9
Ключевые отличия показателей «Load distribution» между отчётами 42004‑42005 (ранний) и 49‑50 (поздний)
Все данные приведены для базы DB‑5, так как на неё приходится основная нагрузка (более 99% общего времени).
Total time (общее время выполнения)
- 42004‑42005: 29819.31 с
- 49‑50: 26929.15 с
- → снижение на 9,7% (Подтверждено)
Executed count (число выполненных запросов)
- 21 148 934 → 21 952 802
- → рост на 3,8% (Подтверждено)
I/O time (время ввода‑вывода)
- 829,55 с → 2320,56 с
- → рост в 2,8 раза (Подтверждено)
Shared blocks read (разделяемые блоки, прочитанные с диска)
- 2 425 766 → 6 321 968
- → рост в 2,6 раза (Подтверждено)
Shared blocks written (разделяемые блоки, записанные на диск)
- 23 827 → 427 289
- → рост в 17,9 раза (Подтверждено)
WAL generated (объём сгенерированного WAL)
- 8 539 312 231 байт → 11 043 556 532 байт
- → рост на 29% (Подтверждено)
Temp and Local blocks written (временные + локальные блоки на запись)
- 36 905 079 → 39 719 489
- → рост на 7,6% (Подтверждено)
Invalidation messages sent (сообщения об инвалидации кеша)
- 9 649 752 → 2 234 576
- → снижение в 4,3 раза (Подтверждено)
Cache resets (сбросы разделяемого кеша)
- 4133 → 8
- → снижение в 516 раз (Подтверждено)
Вывод по разделу:
После обновления общее время выполнения снизилось, но резко выросло время I/O, количество записанных shared‑блоков и объём WAL. При этом значительно сократились сбросы кеша и инвалидационные сообщения.
Гипотеза о влиянии обновления СУБД на показатели Load distribution
Гипотеза (Вероятно):
Основная причина ухудшения I/O, записи блоков и генерации WAL — не само обновление версии, а изменение характеристик нагрузки, в частности резкое увеличение числа физически читаемых блоков запросом к таблице _Document4154.
Это совпало по времени с обновлением, но является независимым фактором. Само обновление с 15.12 до 17.9, напротив, дало положительные эффекты: снижение общего времени выполнения, уменьшение числа сбросов кеша и инвалидационных сообщений.
Обоснование (тезисы и последовательность рассуждений):
Анализ Top SQL by I/O wait time
- В отчёте 49‑50 запрос 9ac42dd05774b73d (выборка из _Document4154 с сортировкой по дате) потребил 307,64 с I/O (26,85% от всего I/O кластера) и прочитал 597 284 shared blocks.
- В отчёте 42004‑42005 аналогичный запрос c8b49b42a7b30062 потребил всего 8,52 с I/O и прочитал 54 357 блоков.
- → Увеличение числа прочитанных блоков в 11 раз напрямую объясняет рост общего I/O. (Подтверждено)
Сравнение планов выполнения
- В обоих периодах план идентичен: Sort → Index Scan using _document4154_4.
- → Изменение плана не является причиной. (Подтверждено)
Неизменность ключевых параметров стоимости
- random_page_cost = 1,1, seq_page_cost = 1, work_mem = 16 MB, effective_cache_size ≈ 98,8 GB — остались прежними (compare_settings.txt).
- → Ожидаемое поведение оптимизатора не изменилось. (Подтверждено)
Положительные изменения в версии 17
- Снижение Total time на 9,7% при росте числа выполнений на 3,8% указывает на улучшение процессорной эффективности.
- Резкое падение Cache resets (с 4133 до 8) и Invalidation messages (в 4,3 раза) говорит о стабилизации разделяемого кеша и снижении DDL‑активности — возможные улучшения в версии 17. (Подтверждено)
Возможные причины роста записи блоков и WAL
- В конфигурации 49‑50 появились параметры enable_background_freezer = on, увеличены wal_buffers (2 MB → 8 MB), уменьшен wal_writer_delay (200 мс → 100 мс).
- Это могло сделать фоновую запись более агрессивной, увеличив количество сброшенных грязных страниц и объём WAL. (Вероятно)
Итог гипотезы:
Основной удар по I/O нанёс конкретный запрос, который стал читать в 11 раз больше блоков по причинам, не связанным напрямую с версией (изменение данных, диапазонов дат или селективности). Обновление при этом принесло улучшения по процессорному времени и стабильности кеша, но могло усугубить запись через новые параметры фоновых процессов.
Способы подтверждения гипотезы
Сравнить фактические параметры проблемного запроса
- Получить значения подстановок ($9, $10 — диапазон дат, $6–$8 — фильтры по _fld4155_*) для обоих периодов из логов или метаданных.
- Если диапазон дат стал шире или значения _fld4155_* стали менее селективными → причина в данных, а не в версии.
Оценить объём данных в _Document4154
- Выполнить SELECT COUNT(*) FROM _Document4154 WHERE ... с теми же фильтрами в каждом периоде.
- Если количество строк, удовлетворяющих условиям, выросло кратно — гипотеза о независимом изменении нагрузки подтвердится.
Измерить hit ratio для таблицы _Document4154
- Через pg_statio_user_tables получить heap_blks_read и heap_blks_hit.
- Снижение hit ratio указывает на вытеснение страниц из кеша (возможно, из‑за роста записи).
Провести нагрузочное тестирование на идентичных данных
- На тестовом стенде восстановить данные на момент 42004‑42005, прогнать тот же запрос на версиях 15.12 и 17.9 с одинаковыми параметрами.
- Если I/O и число прочитанных блоков совпадут → версия не виновата. Если в 17.9 выше → причина в версии.
Проанализировать работу background writer и checkpointer
- Взять pg_stat_bgwriter за оба периода.
- Резкий рост buffers_written и checkpoints_req подтвердит изменение политики записи в версии 17.
Способы опровержения гипотезы
- Если параметры запроса (диапазон дат, значения фильтров) и объём данных не изменились, а I/O в версии 17 всё равно выше → причина в версии СУБД (изменение costing, эффективности индекса, дефект).
- Если при тестировании на одинаковых данных в старой и новой версиях показатели I/O и чтения блоков совпадают → гипотеза о влиянии версии опровергается.
- Если отключение enable_background_freezer (или возврат wal_buffers/wal_writer_delay к старым значениям) в версии 17 приводит к снижению shared blocks written до уровня 15.12, то ухудшение записи связано именно с новыми параметрами, а не с изменением нагрузки. При этом основная гипотеза (независимость данных) останется в силе, если I/O чтения не снизится.
- Если анализ логов покажет, что проблемный запрос в 49‑50 выполнялся с точно такими же диапазонами дат и фильтрами, как в 42004‑42005, но число прочитанных блоков выросло — гипотеза о независимом изменении нагрузки будет опровергнута, и причина будет отнесена к версии/конфигурации.
Заключение (Уровень достоверности: Вероятно):
На основе имеющихся данных нельзя однозначно приписать ухудшение I/O только обновлению версии.
Наблюдается сильная аномалия в одном запросе, которая требует проверки параметров выполнения. Однако положительные изменения (снижение total time, cache resets) с высокой вероятностью связаны с улучшениями в PostgreSQL 17.
Для окончательного диагноза необходимо собрать данные, перечисленные в способах подтверждения/опровержения.
Общий технический итог
В результате анализа установлено, что общее время выполнения запросов сократилось на 9,7% при росте числа выполненных операторов на 3,8%, однако время ввода-вывода возросло в 2,8 раза, количество прочитанных shared-блоков — в 2,6 раза, а записанных — в 17,9 раза, объём WAL увеличился на 29%.
Основным драйвером ухудшения чтения является один запрос к таблице _Document4154, число физически прочитанных блоков которым выросло в 11 раз при неизменном плане выполнения (Sort → Index Scan).
При этом положительные эффекты версии 17.9 включают снижение числа сбросов разделяемого кеша с 4133 до 8 и сообщений об инвалидации в 4,3 раза.
Предположительно, рост записи блоков и WAL частично обусловлен новыми параметрами конфигурации (enable_background_freezer = on, увеличение wal_buffers и уменьшение wal_writer_delay), сделавшими фоновую запись более агрессивной.
Послесловие
Представленные результаты демонстрируют, что прямое приписывание регрессии I/O исключительно обновлению версии СУБД является преждевременным без дополнительных контрольных экспериментов.
Приоритетными направлениями верификации выступают: извлечение фактических параметров подстановок проблемного запроса из логов, измерение селективности фильтров и hit-ratio таблицы _Document4154, а также нагрузочное тестирование на идентичных данных на обеих версиях. Рекомендуется дополнительно проанализировать статистику pg_stat_bgwriter и оценить влияние отключения enable_background_freezer на интенсивность записи.
Только комплексное применение указанных методов позволит окончательно разделить эффекты изменения версии, конфигурации и характера нагрузки.