Оценка влияния online_analyze.enable на производительность дисковой подсистемы PostgreSQL после миграции на Postgres Pro 17

Влияние параметра online_analyze.enable на эффективность использования буферного кэша, дисковые операции и генерацию WAL в PostgreSQL 17.9.2: двойная верификация изменений конфигурации после миграции с Postgres Pro 15.14.1 на 17.9.2 на примере нагрузки СУБД в среде «1С:Предприятие»

online_analyze: включить, чтобы не ждать

Max: PG_EXPECTO

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 | Дзен

Предисловие

В ходе выполнения работ по постмиграционному сопровождению перехода с Postgres Pro Enterprise 15.14.1 на Postgres Pro Enterprise 17.9.2 для платформы «1С:Предприятие» был зафиксирован непрогнозируемый дефицит дисковой подсистемы, выражавшийся в необъяснимом росте времени ввода-вывода и утилизации диска. Сравнительный анализ конфигурационных параметров между старым и новым периодами эксплуатации выявил три ключевых отличия: включение автоматической подготовки общих планов (autoprepare_threshold = 2), отключение модуля оперативного обновления статистики (online_analyze.enable = off) и изменение коэффициента generic_plan_fuzz_factor с 1 на 0,9. Настоящая работа посвящена проверке гипотезы о том, что повторное включение online_analyze.enable = on способно устранить наблюдаемый дефицит дисковой подсистемы путём улучшения актуальности статистики и, как следствие, оптимизации планов выполнения запросов.

Продолжение работ по теме - "После миграции СУБД для «1С:Предприятие» с Postgres Pro Enterprise 15.14.1 на Postgres Pro Enterprise 17.9.2 возник непрогнозируемым дефицит дисковой подсистемы"

Задача - проверка гипотезы

Глава 4. Двойная верификация: как тандем autoprepare_threshold и online_analyze методично вымывает кэш планов

Сравнение всех конфигурационных параметров между старым и новым периодами выявило три ключевых отличия, не считая уже известных:

• autoprepare_threshold: 0 → 2 (включена автоматическая подготовка общих планов);
• online_analyze.enable: on → off (отключено автоматическое обновление статистики при DML-операциях);
• generic_plan_fuzz_factor: 1 → 0.9.

Изменение : online_analyze.enable = on

ALTER SYSTEM SET online_analyze.enable = on ;

Показатели мониторинга Zabbix и периоды для аналитического отчет

Рис.1 - График изменения метрики "Disk utilization" для диска, используемого файловой системой PGDATA

Разностный отчет pgpro_pwr: pgpro_pwr_diff.348-356-372-380.clear.html

• 348-356 : online_analyze.enable = off ;
• 372-380 : online_analyze.enable = on ;

1. Подготовить исходную таблицу по разделу "Load distribution among heavily loaded databases"

2. Сравнительный аналитический отчет по разделу "Load distribution among heavily loaded databases"

3. Анализ влияния "online_analyze.enable = on" на профиль нагрузки СУБД

1. Формулировка гипотезы

Гипотеза (Уровень-2: Вероятно, но требует проверки):
Включение online_analyze.enable = on в периоде «372‑380» привело к немедленному обновлению статистики после операций, изменяющих данные, в первую очередь в БД DB‑4. Благодаря более актуальной статистике оптимизатор PostgreSQL начал выбирать планы, которые эффективнее используют буферный кеш, что выразилось в росте hit ratio, снижении I/O‑времени, уменьшении обращений к временным блокам и, как следствие, в общем сокращении времени выполнения запросов.

2. Последовательность рассуждения и обоснование

2.1. Что делает online_analyze (исходные данные)

• Согласно документации, модуль online_analyze немедленно запускает ANALYZE для целевой таблицы после каждой операции INSERT, UPDATE, DELETE, SELECT INTO или COPY.
• По умолчанию online_analyze.scale_factor = 0.1, online_analyze.threshold = 50, то есть анализ запускается при изменении 10 % строк таблицы, но не ранее чем после 50 изменённых строк.
• В штатном режиме (без online_analyze) статистика обновляется только фоновым autovacuum, что может приводить к использованию устаревших оценок количества строк и распределения значений при частых изменениях.

2.2. Ключевые факты отчёта (Уровень-1: Подтверждено данными)

• DB‑4 доминирует по нагрузке в обоих периодах.
• Количество выполненных запросов в DB‑4 практически не изменилось: –0,48 % (24,78 млн → 24,66 млн).
• При этом:
  Hit ratio вырос с 81,5 % до 88,0 % (fetched вырос на 45 %, read снизился на 5,5 %).
  I/O time снизился на 15,7 % (с 31 543 с до 26 590 с).
  Total time снизился на 6,7 % (с 51 235 с до 47 815 с).
  Temp blocks written снизились на 31 %, temp blocks read — на 49 %.
  WAL generated снизился на 34,7 % (с 17,37 ГБ до 11,34 ГБ), dirtied — на 21 %, written — на 60 %.
  Cache resets вырос с 3 до 49.

2.3. Логическая цепочка

1. online_analyze → актуальная статистика.
   Если статистика (количество строк, наиболее частые значения, гистограммы) обновляется немедленно после изменений, оптимизатор получает более точные оценки селективности и размеров промежуточных результатов.
2. Актуальная статистика → улучшенные планы.
   Оптимизатор может корректно выбрать:
   индексное сканирование вместо последовательного для больших таблиц (сокращается количество читаемых блоков, повышается hit ratio);
   подходящий метод соединения (например, Merge Join вместо Hash Join, если оценки строк точны), что снижает потребность во временных файлах;
   правильный порядок соединений и агрегаций, уменьшая объём временных данных.
3. Улучшенные планы → наблюдаемые эффекты.
   Рост hit ratio: план начинает использовать индекс, который уже находится в shared_buffers, вместо чтения всей таблицы с диска.
   Снижение I/O time: прямое следствие роста hit ratio и уменьшения числа физических чтений.
   Снижение temp блоков: сокращение объёма временных файлов (сортировок, хешей), так как оптимизатор выбирает менее «прожорливые» планы.
   Снижение WAL и грязных блоков: если запросы стали меньше изменять данные (например, вместо UPDATE всей таблицы — точечные изменения по индексу) или временные таблицы используются реже.
   Рост cache resets: online_analyze может вызывать инвалидацию кеша при обновлении статистики, либо частые DDL-подобные операции, которые также сбрасывают кеш.
4. Почему эффект проявился только в DB‑4?
   DB‑5 демонстрирует преимущественно читающую нагрузку (hit ratio 99,6 %, минимальные WAL). Статистика для таких таблиц редко устаревает, поэтому online_analyze практически не меняет поведение оптимизатора.
   DB‑7 имеет значительно меньшие объёмы изменений, и эффект от актуализации статистики менее заметен.
   Следовательно, основным бенефициаром является активно изменяемая DB‑4.

2.4. Альтернативные объяснения (контроль)

• Возможно, одновременно с online_analyze были изменены другие параметры (например, увеличен shared_buffers, work_mem), что само по себе могло вызвать наблюдаемые улучшения. Без доступа к конфигурации это нельзя исключить (Уровень-3: Предположение).
• Рост hit ratio может объясняться не только улучшением планов, но и прогревом кеша (если во втором периоде изменился паттерн обращений). Однако отсутствие роста числа запросов делает это объяснение менее вероятным.

3. Способы подтверждения гипотезы

Для повышения уровня достоверности до Уровень-1 требуется выполнить следующие проверки:

1. Сравнение планов запросов
   Получить EXPLAIN (ANALYZE, BUFFERS) для топ‑5 наиболее ресурсоёмких запросов в DB‑4 за оба периода.
   Если планы изменились (например, переход от Seq Scan к Index Scan, изменение методов соединений) и это объясняет рост hit ratio и снижение temp‑блоков — гипотеза подтверждается.
2. Анализ pg_stat_statements
   Сравнить среднее время выполнения, количество обращений к буферам (shared_blks_hit, shared_blks_read), число временных блоков (temp_blks_written) для одних и тех же queryid между периодами.
   Значимое снижение этих показателей при неизменном calls укажет на улучшение планов.
3. Статистика по таблицам (pg_stat_user_tables, pg_statio_user_tables)
   Проверить seq_scan / idx_scan — рост доли индексных сканирований во втором периоде будет сильным аргументом в пользу гипотезы.
   Сравнить last_analyze для активно изменяемых таблиц: если во втором периоде он обновлялся значительно чаще, это прямое свидетельство работы online_analyze.
4. Системная статистика ОС
   Сравнить утилизацию CPU (ожидается рост из-за более сложных планов) и дисковые операции (ожидается снижение iops и await) между периодами, чтобы исключить влияние внешних факторов.
5. Журнал DDL‑операций
   Проверить, не происходили ли массовые изменения схемы данных, которые могли повлиять на cache resets и планы запросов.

4. Способы опровержения гипотезы

• Планы запросов не изменились. Если EXPLAIN показывает идентичные планы, то улучшение hit ratio не связано с online_analyze.
• Статистика таблиц не отличается. Если pg_stat_user_tables (n_live_tup, n_dead_tup, last_analyze) не показывает значимого отличия между периодами, значит, online_analyze либо не срабатывал, либо его влияние было ничтожным.
• Изменение конфигурации оборудования или PostgreSQL. Если во втором периоде был увеличен shared_buffers, effective_cache_size или work_mem, это само по себе могло улучшить hit ratio и снизить I/O.
• Внешние факторы. Снижение общей нагрузки на дисковую подсистему со стороны других приложений могло привести к уменьшению времени I/O ожиданий.

5. Итоговая оценка

• На основании доступных данных гипотеза о положительном влиянии online_analyze на производительность DB‑4 является Уровень-2: Вероятно, но требует проверки.
• Все ключевые метрики (hit ratio, I/O time, temp‑блоки, WAL) согласованно указывают на оптимизацию планов запросов, а единственное задокументированное изменение — включение online_analyze — логически способно вызвать такую оптимизацию.
• Для перевода в Уровень-1 необходимы: планы запросов (EXPLAIN), данные pg_stat_statements и pg_stat_user_tables, а также значения параметров конфигурации PostgreSQL и системная статистика ОС за оба периода. Без них остаётся Неизвестно, не повлияли ли на результат сопутствующие изменения окружения.

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

Включение online_analyze.enable = on в периоде 372–380 (по сравнению с периодом 348–356, где параметр был отключён) привело к статистически значимым улучшениям в наиболее нагруженной базе данных DB‑4: коэффициент попадания в буферный кэш (hit ratio) вырос с 81,5 % до 88,0 % при одновременном снижении времени ввода-вывода (I/O time) на 15,7 %, общего времени выполнения запросов (total time) на 6,7 %, а также объёма временных блоков (temp blocks written) на 31 % и сгенерированных WAL-данных на 34,7 %. При этом количество выполненных запросов в DB‑4 практически не изменилось (–0,48 %), что исключает эффект снижения нагрузки как первичную причину.

Наблюдаемые изменения согласуются с гипотезой о переходе оптимизатора к более эффективным планам (в частности, к индексным сканированиям вместо последовательных) благодаря актуальной статистике, обновляемой немедленно после DML-операций.

Таким образом, гипотеза о положительном влиянии online_analyze.enable на производительность дисковой подсистемы в активно изменяемых базах данных получает статус «Уровень‑2: вероятно, но требует проверки», с необходимостью подтверждения через сравнение реальных планов запросов.

Послесловие

Полученные результаты позволяют рекомендовать включение online_analyze.enable = on в качестве стандартной практики для высоконагруженных транзакционных систем с частыми операциями изменения данных (INSERT/UPDATE/DELETE), особенно после миграции на более новые версии PostgreSQL, где изменилась логика выбора generic планов.

Однако следует учитывать сопутствующий эффект в виде роста числа сбросов кэша (cache resets с 3 до 49), что может указывать на дополнительную нагрузку на планировщик и требует дальнейшего изучения. Для перевода гипотезы на уровень «Уровень‑1: подтверждено» необходимо проведение верификации с использованием детальных планов выполнения (EXPLAIN (ANALYZE, BUFFERS)), сравнения метрик pg_stat_statements по одинаковым идентификаторам запросов, а также анализа статистики таблиц (pg_stat_user_tables, pg_statio_user_tables) на предмет роста доли индексных сканирований. Без этих данных нельзя полностью исключить влияние сопутствующих изменений конфигурации или внешних факторов дисковой подсистемы. Последующее исследование должно быть направлено на квантификацию вклада каждого из трёх изменённых параметров (autoprepare_threshold, online_analyze.enable, generic_plan_fuzz_factor) в итоговый профиль производительности.