Решение одной проблемы разностного отчёта pgpro_pwr.
Методика формирования сводной сравнительной аналитики, опирающейся на сопоставление двух стандартных отчётов pgpro_pwr. Эти отчёты используются в качестве альтернативы одному разностному отчёту pgpro_pwr в тех случаях, когда размер последнего оказывается чрезмерно большим. Экспериментальное исследование данной методики проведено на примере оценки влияния трёх параметров PostgreSQL 17 на дисковый ввод-вывод и планы выполнения запросов в условиях продуктивной нагрузки.
Содержание
Предисловие
Предшествующие исследования, выполненные на иной продуктивной СУБД, позволили разработать методику оценки производительности с использованием разностного отчёта pgpro_pwr . В настоящей работе ставится цель экспериментально проверить, сохраняет ли указанная методика свою диагностическую ценность при переносе на другую продуктивную систему — в данном случае на PostgreSQL 17 с иным характером транзакционной активности.
Предыдущий эксперимент
Тонкая настройка PostgreSQL 17: как три параметра изменили ландшафт ввода-вывода
Постановка задачи
Экспериментальная проверка рекомендаций о влияния параметров:
на показатели производительности СУБД , для иного характера нагрузки.
Показатели утилизации дисковой подсистемы до и после изменения параметров:
Рис.1 - График изменения метрики "Disk utilization" для диска, используемого файловой системой PGDATA
Первый интервал (снимки 3310-3316) соответствовал конфигурации:
online_analyze.enable = off,
autoprepare_threshold = 2,
generic_plan_fuzz_factor = 0.9.
Во втором интервале (снимки 3354-3360) были применены изменения:
online_analyze.enable = on,
autoprepare_threshold = 0,
generic_plan_fuzz_factor = 1.
Никакие другие параметры СУБД не менялись.
1. Особенность данного эксперимента
Вследствие значительного объёма разностного отчёта pgpro_pwr (>2MB), превышающего ограничение контекстного окна бесплатной версии DeepSeek, применение ранее использованной методики подготовки аналитического отчёта, предусматривающей стандартные промпты, оказалось невозможным. Для решения проблемы, методика была модифицирована, путём отказа от использования разностного отчёта. Были использованы два отдельных отчёта по снимкам 3310–3316 и 3354–3360.
Модифицированная процедура состоит из двух этапов:
Этап 1: извлечение исходных разделов отчётов pgpro_pwr в отдельные текстовые файлы для последующего анализа.
Этап 2: формирование аналитического отчёта не на основе разностного отчёта pgpro_pwr, а на основе подготовленных текстовых файлов.
Подробности использованного метода и другие проблемы pgpro_pwr:
2. Шаблоны промптов для подготовки сводного аналитического отчета
deepseek-pg-perf-prompts/load_distribution_top_sql at main · pg-expecto/deepseek-pg-perf-prompts
Использованные промпты и подготовленные в результате исходные текстовые файлы для формирования аналитического отчета:
3. Формирование сводного аналитического отчета
prompt_final_analyze.txt — Яндекс Диск
Ключевые отличия показателей «Load distribution» между отчётами по снимкам 3310–3316 и 3454–3460 .
База DB-8 резко активизировалась:
Total time: 0.01% → 1.00% (рост в ~100 раз).
Shared blocks written: 0.00% → 24.58% (появление интенсивной записи).
WAL generated: 0.00% → 4.74%.
Temp & Local blocks written/read: с нуля до 6.03% / 13.63% – активное использование временных таблиц на диске.
DB-7 снизила активность
Total time: 4.12% → 2.92%, Executed count: 2.19% → 0.36%.
При этом I/O time сохранился на уровне ≈9.4%, то есть оставшиеся запросы стали более I/O-интенсивными.
Изменение доминирующего приложения
Вместо «1C:Enterprise» (98.11% total time) основным стало «(empty)» (97.31% total time). Возможная причина – изменение application_name или способа подключения.
Появилось приложение take_sample (0.88% total time).(Формирование снимка pgpro_pwr. Добавлено мной.)
Перераспределение нагрузки по хостам
Хост ip (3) вырос с 1.50% до 5.46% total time, стал заметным потребителем Temp I/O (16.89% writes, 37.42% reads).
[local]-хост начал генерировать 24.58% shared blocks written (совпадает с DB-8).
Пользователь role-10 (связан с DB-8) проявил активность:
0.01% → 1.25% total time, 24.58% shared blocks written.
4. Гипотеза о влиянии ключевого фактора
Основной механизм:
включение online_analyze обновило статистику, что привело к смене планов выполнения у части запросов – планировщик стал выбирать операции с временными таблицами (Hash Join, сортировка с вытеснением на диск) вместо ранее использовавшихся индексных сканирований. Это объясняет рост Temp I/O и появление записей в WAL/shared buffers для DB-8. Отключение autoprepare_threshold не дало кэшировать планы, увеличив затраты на планирование, но основной вклад в изменение нагрузки внесла именно смена планов.
Обоснование через планы запросов
Запрос f21cecec8bb10ac7 (TOP по времени)
Период 3310–3316: план 43e3839edf8e5ad9 – Hash Left Join с хеш-таблицей на 74 MB, активное использование work_mem.
Период 3454–3460: план f356a7a75a11f0d7 – Nested Loop Left Join + Memoize, без объёмных хешей.
Следствие: Статистика, обновлённая online_analyze, показала меньшее количество строк в правой части соединения, и планировщик выбрал вложенные циклы вместо хеша – снизилось потребление памяти, но могло увеличить число обращений к temp при сбросе кеша Memoize.
Запрос 5361d28fd4412420 (SELECT BinaryData)
План не изменился (a5f6db5b2fb2c077 Index Scan), но I/O time снизился с 188s до 53s, Executions с 531k до 424k.
Следствие: online_analyze помог точнее оценить число подлежащих чтению блоков, что улучшило буферизацию и сократило физические чтения.
Новый тяжелый запрос 1e55802ad1806af7 (Exec 585s, Read I/O 210s, Tmp Reads 622713)
План в предоставленных фрагментах отсутствует, но обилие Tmp Reads указывает на масштабную сортировку или хеш-агрегацию с вытеснением на диск.
Следствие: Скорее всего, новый план, выбранный из-за обновлённой статистики, начал использовать временные файлы, что напрямую объясняет взрыв Temp I/O в DB-8 и на ip (3).
Рост Shared Blocks Written / WAL у DB-8
Совпадает с появлением у этой БД активности , связанной с временными таблицами: temp-файлы создаются и изменяются, что генерирует запись в WAL (при wal_log_hints или если temp-таблицы созданы как unlogged, но в PG Pro возможны отличия).
Учитывая, что autoprepare_threshold = 0, каждый вызов запроса планируется заново, но это не объясняет объём записи – он вызван именно выполнением тяжёлых операторов с материализацией.
Цепочка рассуждений
Исходные данные: Единственное задокументированное изменение конфигурации между периодами – включение online_analyze, отключение autoprepare, установка generic_plan_fuzz_factor = 1.
Связь с планами: online_analyze непрерывно собирает статистику по изменяемым таблицам → статистика становится точнее → планировщик может выбрать другие планы (часто более оптимальные, но иногда с использованием временных структур).
Наблюдаемый эффект в нагрузке: Резкий рост Temp I/O, частичное снижение физических чтений, появление записи на хосте, где ранее её не было. Это соответствует переходу от планов, избегавших временных таблиц, к планам, активно их использующим.
Исключение альтернатив: autoprepare_threshold = 0 и generic_plan_fuzz_factor = 1 влияют на способ кэширования планов, но не меняют сами планы, если не используются подготовленные операторы. Основной драйвер изменений – online_analyze.
5. Способы подтверждения и опровержения гипотезы
Подтверждение
Сравнить статистику pg_stat_user_tables (n_tup_ins, n_live_tup, last_autoanalyze) для таблиц, используемых в запросах с изменившимся планом (например, InfoRg10591, Document1231, Reference1211). Если между периодами выросла точность оценок nlive_tup и снизилось расхождение с реальным числом строк, это укажет на работу online_analyze.
Проверить pg_stat_statements (или pgpro_stats) – если у тех же queryid изменились shared_blks_read/temp_blks_read в соответствии с новыми планами, гипотеза подтвердится.
Проанализировать EXPLAIN (ANALYZE, BUFFERS) для запроса f21cecec8bb10ac7 при старой и новой конфигурации: должно воспроизвестись переключение плана и изменение использования temp.
Опровержение
Если после отключения online_analyze и возврата autoprepare_threshold к значениям первого периода нагрузка и планы вернулись бы к исходному профилю, это доказывает влияние параметров.
Если анализ логов приложений покажет, что между периодами изменился характер запросов (например, в DB-8 добавились новые пакетные задания, генерирующие 1e55802ad1806af7), то рост Temp I/O объясняется не сменой планов, а изменением трафика.
Если планы запросов с большим Temp I/O (1e55802ad1806af7, a4d4eece4d17eb97) окажутся одинаковыми в обоих периодах (при их наличии в первом), значит, online_analyze не был причиной их появления – просто эти запросы не выполнялись в первом интервале.
6. Итоговая оценка достоверности
online_analyze вероятно является основной причиной изменения планов и профиля Temp I/O, поскольку статистика – единственный фактор, способный вызвать переключение между Hash Join и Nested Loop без изменения текста запроса.
Полное объяснение роста Shared Blocks Written в DB-8 требует дополнительного изучения конкретных операторов, генерирующих эту запись – предположительно это материализация временных таблиц или CTE, активированная новыми планами.
7. Общий технический итог
Проведённый эксперимент подтвердил, что изменение трёх параметров — включение online_analyze.enable, установка autoprepare_threshold = 0 и generic_plan_fuzz_factor = 1 — привело к статистически значимой перестройке профиля ввода-вывода.
Ключевым драйвером изменений выступила актуализация статистики, вызвавшая смену планов выполнения с индексных сканирований и хеш-соединений на вложенные циклы с активным использованием временных таблиц и вытеснением на диск.
Это выразилось в росте доли записи разделяемых блоков с 0% до 24,58% для БД-8, возникновении Temp I/O (6,03% записи, 13,63% чтения) и увеличении времени выполнения отдельных запросов (например, f21cecec8bb10ac7).
Предложенная альтернативная методика — анализ текстовых файлов, извлечённых из pgpro_pwr, — позволила выявить указанные эффекты даже при невозможности построения разностного отчёта, что доказывает её эффективность в условиях, аналогичных ранее апробированной продуктивной СУБД.
8. Послесловие
Полученные результаты, однако, не следует интерпретировать как универсальную рекомендацию по умолчанию для всех конфигураций. Выявленная смена планов в сторону увеличения операций с временными таблицами может быть как улучшением (снижение физических чтений, экономия памяти), так и деградацией (рост WAL, повышение утилизации диска).
Окончательное заключение о целесообразности применения исследованных параметров требует воспроизведения эксперимента с фиксацией планов конкретных проблемных запросов (например, 1e55802ad1806af7) и измерения времени ответа приложения, а не только дисковой метрики.
Представленная методика анализа на основе текстовых файлов может быть рекомендована для использования в аналогичных ситуациях, когда стандартный анализ с помощью нейросети DeepSeek становится невозможным из-за объема разностного отчёта pgpro_pwr.