DeepSeek, PG_EXPECTO и философская инструкция: тройная диагностика аномалий PostgreSQL (15 -> 17). deepseek.. deepseek. PostgreSQL.. deepseek. PostgreSQL. postgresql 17.. deepseek. PostgreSQL. postgresql 17. postgresql performance.. deepseek. PostgreSQL. postgresql 17. postgresql performance. upgrade.. deepseek. PostgreSQL. postgresql 17. postgresql performance. upgrade. искусственный интеллект.. deepseek. PostgreSQL. postgresql 17. postgresql performance. upgrade. искусственный интеллект. нейросеть.. deepseek. PostgreSQL. postgresql 17. postgresql performance. upgrade. искусственный интеллект. нейросеть. философия.

Когда версия виновата… но не совсем. Как две независимые проблемы чуть не убили производительность после апгрейда.

Когда версия виновата… но не совсем. Как две независимые проблемы чуть не убили производительность после апгрейда.

Официальное предупреждение (дисклеймер)

Настоящая статья подготовлена с использованием технологий искусственного интеллекта.

В частности:

— экспериментальные данные обработаны и проанализированы нейросетью;

— иллюстративный материал, сопутствующие слоганы, а также предисловие и послесловие сгенерированы нейросетью;

— макет статьи редактировался и корректировался нейросетью.

Лицам, придерживающимся позиции «ИИ-веганства» (испытывающим устойчивый страх, неприязнь или психологический дискомфорт по отношению к нейросетевым системам), настоятельно не рекомендуется ознакомление с содержанием данной публикации, равно как и участие в её обсуждении, во избежание возможного нанесения вреда психологическому благополучию.

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

во-первых, ошибку планировщика, индуцированную активированным по умолчанию параметром planner_upper_limit_estimation = on и усугублённую совместным влиянием autoprepare_threshold и отключённого online_analyze; 

во-вторых, классическое истощение буферного кэша при фиксированном значении shared_buffers на фоне возросшей рабочей нагрузки.

В статье представлена хронология диагностических мероприятий, опиравшихся на инструмент pgpro_pwr, методологию PG_EXPECTO и верификацию с привлечением нейросетевых моделей, а также приведены конкретные параметры конфигурации, рекомендуемые к проверке администраторам, планирующим или уже осуществившим аналогичное обновление.

Для диагностики использовались:

  • pgpro_pwr — расширение для сбора и анализа статистики производительности PostgreSQL, формирующее детальные отчёты по нагрузке, ожиданиям, планам запросов;

  • PG_EXPECTO — доменная методология анализа, предписывающая проверять внутреннюю согласованность метрик, разделять подтверждённые факты и гипотезы, явно указывать границы применимости выводов;

  • философская инструкция для нейросетевой модели DeepSeek, обеспечивающая эпистемическую честность: каждый вывод снабжался уровнем достоверности («Подтверждено данными», «Вероятно, но требует проверки», «Предположение», «Невозможно оценить»).

  • Коллекция промптов для нейросети DeepSeek (на русском языке), предназначенных для анализа и оптимизации производительности СУБД PostgreSQL.

Содержание

Глава 1. После апгрейда: процессорное время ×5 при тех же планах

Первая проблема с аномальной утилизацией CPU/IO после обновления версии.

Рис.1 Графики метрик Zabbix: "CPU iowait time" и "CPU Utilization" - до и после обновления версии СУБД (СУБД-1)

Рис.1 Графики метрик Zabbix: “CPU iowait time” и “CPU Utilization” – до и после обновления версии СУБД (СУБД-1)

Сравнение двух отчётов pgpro_pwr — до обновления (PG 15) и после (PG 17) — выявило картину, далёкую от ожидаемой:

  • общее количество выполненных запросов выросло на 20% (с 33,7 млн до 40,4 млн);

  • однако суммарное время выполнения подскочило в 2,6 раза (с ~5 094 с до ~13 462 с);

  • процессорное время (сумма user + system) увеличилось более чем в 5 раз — с ~5 550 с до ~31 134 с;

  • пиковое число сессий в новом периоде достигало 1 317 (против 435 ранее), при этом параметр max_connections был установлен в экстремальные 5 000.

Конфигурационные параметры, влияющие на кэширование и стоимость доступа (shared_buffers = 55 ГБ, effective_cache_size = 165 ГБ, random_page_cost = 1.1), остались неизменными.

Но были и различия:

  • work_mem удвоился (с 32 МБ до 64 МБ),

  • max_parallel_workers_per_gather в обоих случаях, по-видимому, был отключён или близок к нулю.

Проблемные запросы сохранили свои паттерны: частые вызовы SELECT FASTTRUNCATE для очистки временных таблиц (до 1,2 млн раз в час), операции с таблицей BinaryData (чтение и вставка BLOB-объектов), активное использование временных файлов (16 ГБ в новом периоде). Но их «цена» в терминах процессорного времени резко возросла. Так, один только запрос FASTTRUNCATE в новом периоде потреблял 37,7% всего CPU, хотя вызывался в 2,4 раза реже, чем в старом.

Первичный вывод (Уровень‑2):

Деградация, скорее всего, связана с изменением поведения оптимизатора или конкуренцией за ресурсы на фоне возросшего числа сессий. Но для точного ответа требовался анализ конкретных планов выполнения.

Корневая причина – не установлена.

️Утилизация со временем относительно нормализовалась.

Глава 2. Планы стабильны, но физические чтения выросли в разы — первый признак системной причины

Повторение проблемы – аномальная утилизация CPU/IO после обновления версии .

📋На этот раз принято решение – провести глубинный сводный анализ проблемы с целью установить причину и запланировать мероприятия для следующих обновлений.

Рис.2 Графики метрик Zabbix: "CPU iowait time" и "CPU Utilization" - до и после обновления версии СУБД (СУБД-2)

Рис.2 Графики метрик Zabbix: “CPU iowait time” и “CPU Utilization” – до и после обновления версии СУБД (СУБД-2)

📋Начало анализа:

  • 5 запросов, лидировавших по времени выполнения,

  • 12 запросов, доминировавших по времени ожидания ввода-вывода.

Поскольку после обновления идентификаторы Query ID изменились, пришлось искать текстовые соответствия между отчётами.

Время выполнения :

Сравнительный анализ раздела “Total exec time”

  • Запрос с _InfoRg12488 (LIMIT + ORDER BY): план улучшился — вместо Bitmap Heap Scan стал чаще выбираться прямой Index Scan. Среднее время одного вызова сократилось с 255 мс до 19,5 мс, но частота вызовов выросла в 39 раз (с 777 до 30 185). Общее время увеличилось, но не критично.

  • Запрос с Reference109 (OR по двум полям): частота выросла в 105 раз (с 15 до 1 580), план остался Seq Scan, и запрос стал главным потребителем CPU (1 197 с usertime).

  • Запросы с InfoRg15516 и Reference15235 показали катастрофический рост: при неизменном Seq Scan и небольшом числе вызовов среднее время взлетело с 0,33 с до 20,9 с и с 37 мс до 23,7 с соответственно. Причина — многократный рост объёмов данных при отсутствии индексов по фильтруемым полям.

Ожидания I/O :

Сравнительный анализ раздела “Top SQL by I/O wait time”

Из 12 проанализированных запросов 11 продемонстрировали рост времени DataFileRead от 1,6 до 32 раз при неизменных или даже снизившихся количествах вызовов.

Планы выполнения для них не изменились.

Например:

  • INSERT в _InfoRg16813: при снижении числа вызовов на 12% общее время выросло в 2,5 раза, а время I/O — с 27,4 с до 84,2 с;

  • INSERT в _InfoRg12488: общее время выросло в 5 раз (с 4,5 с до 22,9 с) при практически неизменном числе вызовов;

  • SELECT из Reference109 по первичному ключу: в новом периоде оптимизатор переключился на менее эффективный индекс (reference109_no_pkey вместо reference109ngpkey1), что привело к росту прочитанных блоков с 2 292 до 34 526 и времени I/O с 0,11 с до 28,49 с.

Лишь один запрос (SELECT из _InfoRg13163) не показал деградации — он полностью обслуживался из кеша (I/O time = 0 в обоих периодах).

Вывод (Уровень‑1):

️Ключевой фактор замедления — многократный рост физических чтений с диска. Планы в основном не изменились, значит, причина не в регрессе оптимизатора, а в изменившихся условиях выполнения.

Но почему планы «поплыли» для отдельных запросов?

Ответ дал следующий этап.

Глава 3. Поворотный момент: +3300% сбросов кэша — ключ к механизму

Изучение раздела «Load distribution among heavily loaded databases» вскрыло аномалию, которая переопределила направление расследования.

️Счётчик cache_resets (сбросов разделяемого кеша) в представлении pgpro_stats_totals увеличился с 1 до 34 событий за час — то есть на 3300%.

Одновременно:

  • время ввода-вывода (I/O time) выросло на 2310%;

  • объём физических чтений (Shared blocks read) — на 248%;

  • общее время выполнения (Total time) — на 536% при снижении числа запросов на 10%.

Метрика cache_resets фиксирует принудительные очистки кеша планов (и/или буферов), вызванные такими событиями, как DISCARD ALL, ALTER SYSTEM, SET, DDL-команды или аварийные перезапуски обслуживающих процессов. Каждый такой сброс обнуляет накопленную статистику и заставляет планировщика заново строить планы, часто на основе устаревших данных.

Возникла гипотеза : что-то в новой конфигурации заставляет сервер регулярно терять кеш планов.

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

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

  • autoprepare_threshold: 0 → 2 (включена автоматическая подготовка общих планов);

  • online_analyze.enable: on → off (отключено автоматическое обновление статистики при DML-операциях);

  • generic_plan_fuzz_factor: 1 → 0.9.

Чтобы подтвердить их причастность, был проведён двойной анализ — с помощью DeepSeek и специализированной нейросети «Ask Postgres».

Обе системы независимо пришли к одному и тому же механизму:

  1. При autoprepare_threshold = 2 сервер начинает кешировать общие (generic) планы в локальной памяти каждого обслуживающего процесса (бэкенда) — после второго выполнения одного и того же шаблона запроса с разными литералами. При значении 0 кеш был пуст, и сбрасывать было нечего.

  2. При online_analyze.enable = off статистика таблиц перестала обновляться немедленно после INSERT/UPDATE/DELETE. Теперь она обновляется только фоновым autovacuum, причём массово — когда накапливается порог изменений (по умолчанию 10% таблицы + 50 строк). Каждый такой ANALYZE изменяет содержимое системного каталога pg_statistic, что для PostgreSQL служит триггером инвалидации всех кешированных планов, зависящих от изменённых таблиц. В результате кеш, наполненный благодаря autoprepare_threshold, периодически полностью очищается — отсюда и взрывной рост cache_resets.

  3. generic_plan_fuzz_factor = 0.9 добавляет «допуск» при сравнении стоимости общего и специализированного планов, делая планировщик более консервативным. В сочетании с устаревшей статистикой это приводит к тому, что единственным способом получить новый план становится полный сброс кеша.

️Таким образом, активация автоподготовки создала сам объект для сбросов, а отключение online_analyze обеспечило регулярный триггер для них.

Но оставался вопрос: почему после сброса кеша планировщик стал выбирать заведомо неоптимальные последовательные сканирования?

Ответ нашёлся в ещё одном новом параметре PostgreSQL 17.

Глава 5. Прорыв: planner_upper_limit_estimation = on — главный виновник Seq Scan и лавины чтений

Параметр planner_upper_limit_estimation (boolean), появившийся в Postgres Pro 17, по умолчанию равен off. Однако в конфигурации после апгрейда он оказался включён (on).

Согласно документации, он «включает возможность планировщика запросов завышать оценку ожидаемого количества строк в выражениях, содержащих сравнение с неизвестной константой».

Проще говоря, для условий, по которым нет статистики, планировщик применял поправочный коэффициент, искусственно занижая селективность.

️Для проверки влияния были поставлены натурные эксперименты :

Параметр отключили (off) и сняли два новых часовых отчёта (периоды 69–71 и 74–76).

Рис.3 Графики метрик Zabbix: "CPU iowait time" и "CPU Utilization" - до и после обновления версии СУБД при разных значениях параметра planner_upper_limit_estimation

Рис.3 Графики метрик Zabbix: “CPU iowait time” и “CPU Utilization” – до и после обновления версии СУБД при разных значениях параметра planner_upper_limit_estimation

Результаты превзошли все ожидания:

  • Total time — с 76 563 с до 19 963 с (–73,9%);

  • I/O time — с 45 356 с до 7 962 с (–82,4%);

  • Blocks fetched — с 4,36 млрд до 2,04 млрд (–53,1%);

  • Shared blocks read — с 532 млн до 155 млн (–70,9%);

  • Cache resets — с 289 до 10 (–96,5%);

  • Executed count — с 11,95 млн до 13,43 млн (+12,4%).

Сравнение планов выполнения показало: при on для ключевого запроса к _InfoRg12488 использовался Seq Scan, а после переключения в off — Bitmap Heap Scan и Index Scan. Доля последовательных сканирований в общем числе операций доступа снизилась с 19,2% до 16,5%. При этом количество индексных сканирований практически не изменилось, зато резко упало количество строк, возвращаемых через индексы (IxFet — на 43,9%). Это означало, что планировщик стал точнее оценивать стоимость и отказывался от избыточных чтений.

Вывод (Уровень‑1): planner_upper_limit_estimation = on был главным виновником первоначальной деградации. Он заставлял планировщика систематически недооценивать селективность условий без статистики, выбирая неоптимальные последовательные сканирования.

В сочетании с частыми сбросами кеша (из-за autoprepare_threshold и online_analyze) это создавало порочный круг: статистика терялась → планировщик ошибался → данные читались с диска → кеш вымывался ещё быстрее.

Глава 6. Вторая волна: hit ratio рухнул с 95% до 86% без единого изменения конфигурации — shared_buffers больше не справляется

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

Сравнение периодов 165–166 и 170–171 (уже на PG 17 с planner_upper_limit_estimation = off) показало иную картину: при полностью идентичной конфигурации и стабильных планах запросов (12 из 13 основных запросов сохранили планы) наблюдалось:

  • Hit ratio кластера упал с 95,2% до 86,3% (на DB‑4 — с 95,1% до 86,2%);

  • Ожидания DataFileRead выросли в 3,9 раза и стали занимать 63,8% всех ожиданий (5 828 с против 1 479 с);

  • Ожидания BufferIo (конкуренция процессов за одни и те же буферные страницы) взлетели в 126 раз (с 2,75 с до 347,9 с);

  • Количество выполненных запросов выросло на 39% (с 4,74 млн до 6,61 млн), а общее время — в 4,3 раза (с 6 228 с до 26 856 с);

  • Объём временных файлов (Temp blocks written) увеличился на 82%.

Размер shared_buffers при этом оставался неизменным — около 12,3 ГБ. Рабочий набор данных вырос и перестал помещаться в буферный кеш, что привело к массовому вытеснению страниц и физическому чтению с диска.

️Планы не изменились — замедление было вызвано исключительно нехваткой буферного пула.

Дополнительно work_mem в 32 МБ при максимальном числе подключений 1 000( создавал риск, что множество одновременных сортировок и хеш-соединений будут сбрасываться на диск во временные файлы. Рост Temp blocks written на 82% косвенно подтверждал эту гипотезу.

Таким образом, вторая волна проблем имела принципиально иную природу: не ошибка планировщика, а истощение ресурсов при росте нагрузки.

Как одна фраза в промпте к DeepSeek чуть не увела расследование в сторону

Отдельного внимания заслуживает побочное, но важное исследование:

Влияние семантики инструкций для нейросети DeepSeek на результаты анализа. Сравнивались две версии промпта:

  • v1: содержала жёсткое допущение «характер нагрузки кардинально не изменился»;

  • v2: без такого допущения, более нейтральная.

Результаты показали, что v1 систематически склонял модель к подтверждению заранее выдвинутой гипотезы, игнорируя или принижая альтернативные объяснения. Например, для запроса _InfoRg13163 отчёт v1 интерпретировал изменения как умеренное улучшение, в то время как v2 зафиксировал, что Seq Scan, присутствовавший в старом периоде, исчез в новом — то есть план формально улучшился, но v1 этого не отметил, фокусируясь на подтверждении общей деградации.

Вывод: прайминг-эффект реален, и при использовании ИИ-ассистентов в диагностике критически важно тестировать промпты на устойчивость и избегать неявных предположений.

Глава 7. Итоги: две независимые проблемы — один чек-лист для DBA

Расследование показало, что аномальная дисковая утилизация после миграции была вызвана двумя независимыми, но наложившимися проблемами.

Проблема 1: ошибка планировщика (сразу после апгрейда)

Корневая причина: planner_upper_limit_estimation = on — завышение оценки селективности для условий без статистики, приводившее к выбору Seq Scan.

Усугубляющие факторы: autoprepare_threshold = 2 создал кеш планов, online_analyze.enable = off вызывал массовые ANALYZE и, как следствие, частые сбросы этого кеша (cache_resets +3300%). generic_plan_fuzz_factor = 0.9 усилил эффект.

️Решение:

  • Установить planner_upper_limit_estimation = off.

  • Включить online_analyze.enable или тщательно настроить пороги autovacuum_analyze_scale_factor и autovacuum_analyze_threshold для минимизации времени жизни устаревшей статистики.

  • Рассмотреть увеличение autoprepare_threshold (например, до 10), чтобы в кеш попадали только действительно частые запросы.

  • Статус: подтверждено прямыми измерениями (Уровень‑1).

Проблема 2: истощение ресурсов при росте нагрузки (отсроченная)

Корневая причина: фиксированный shared_buffers (~12,3 ГБ) перестал вмещать рабочий набор данных, что привело к падению hit ratio с 95,2% до 86,3% и взрывному росту физических чтений.

Сопутствующие факторы: work_mem = 32 МБ при высокой конкурентности (max_connections = 1000) вызывал активное использование временных файлов; рост объёмов данных увеличил генерацию WAL.

Решение:

  • Увеличить shared_buffers с учётом доступной оперативной памяти (например, до 24–36 ГБ, если оборудование позволяет).

  • Рассмотреть увеличение work_mem (например, до 64–128 МБ), но с осторожной оценкой пикового потребления памяти: при 1 000 одновременных активных запросов увеличение work_mem на 32 МБ может потребовать дополнительно 32 ГБ ОЗУ в худшем случае. Рекомендуется мониторинг фактического использования и, возможно, ограничение параллелизма на уровне пула соединений.

  • Актуализировать effective_cache_size пропорционально новому shared_buffers (обычно ~75% от общего объёма ОЗУ).

  • Обеспечить регулярный мониторинг hit ratio, wait events и временных файлов для предотвращения повторной деградации.

  • Статус: hit ratio и ожидания подтверждены прямыми измерениями (Уровень‑1); роль work_mem — вероятна (Уровень‑2).

Вместо заключения: пять уроков миграции, которые сэкономят недели

  1. Новые параметры — зона риска. Появившиеся в новой версии настройки планировщика (planner_upper_limit_estimation) не должны приниматься «как есть», даже если они рекомендованы документацией. Обязательно нагрузочное тестирование на копии продуктивной среды с профилированием планов запросов и системных метрик I/O.

  2. autoprepare_threshold и online_analyze — опасная комбинация. Если вы включаете кеширование общих планов, убедитесь, что статистика обновляется достаточно часто, чтобы избежать лавины cache_resets. Отключение online_analyze в высоконагруженных системах требует компенсации через агрессивный autovacuum.

  3. pgpro_pwr + PG_EXPECTO + нейросеть = мощный инструмент, но требующий дисциплины. Формулировки промптов влияют на объективность выводов — необходимо перекрёстное тестирование гипотез и явная фиксация уровней достоверности.

  4. Недостаточность буферного кеша может имитировать регрессию версии. Рост рабочих данных требует периодического пересмотра shared_buffers и work_mem, особенно в системах без регулярного реконфигурирования.

  5. Смотрите на систему в целом. Проблема редко имеет одну причину. В данном случае сошлись сразу несколько факторов: новый параметр планировщика, отключённая статистика, включённая автоподготовка, а позже — просто нехватка памяти под возросшую нагрузку.

Итог

История этого расследования — хороший пример того, как систематический анализ метрик, поэтапная проверка гипотез и грамотное привлечение автоматизированных средств позволяют докопаться до истинных причин деградации.

Оказалось, что «новая версия во всём виновата» — слишком грубое упрощение.

Реальная картина включила в себя несколько наслоившихся факторов, каждый из которых потребовал своего метода обнаружения.

Именно комплексный подход — от высокоуровневых профилей к точечному сравнению планов, от конфигурационных параметров к событиям ожидания и hit ratio — позволил не только найти корень проблемы, но и выработать конкретные, работающие рекомендации.

Ограничения. Представленные выводы основаны на данных конкретной инсталляции (Postgres Pro Enterprise, платформа «1С»). Абсолютные значения параметров (shared_buffers, work_mem) приведены для иллюстрации и должны адаптироваться под ваше оборудование и профиль нагрузки. Все рекомендации уровня «Вероятно» требуют проверки в тестовой среде перед внедрением в продуктив.

Использованные материалы – хронология работ по проблеме :

  1. Upgrade PostgreSQL 15→17 : Сравнительный аналитический отчёт pgpro_pwr.

  2. Upgrade PostgreSQL 15→17 : анализ проблемных SQL запросов

  3. Upgrade PostgreSQL 15→17 : анализ запросов IO

  4. Upgrade PostgreSQL 15→17 : аномальный рост I/O + cache_resets

  5. Upgrade PostgreSQL 15→17 : рост cache_resets – влияние autoprepare_threshold?

  6. Upgrade PostgreSQL 15→17: возможная причина cache_resets – planner_upper_limit_estimation = on

  7. Upgrade PostgreSQL 15→17: Анализ планов выполнения при planner_upper_limit_estimation = on/off

  8. Upgrade PostgreSQL 15→17 : Гипотеза о влиянии planner_upper_limit_estimation

  9. Upgrade PostgreSQL 15→17: как отключение online_analyze и сбросы статистики обрушили кэш-хит и породили Seq Scan

  10. Анализ планов выполнения SQL запросов PostgreSQL с помощью DeepSeek и философской инструкции: кейс снижения hit ratio с 93,5% до 81,8%

  11. DeepSeek + PostgreSQL: исследование того, как малая правка в промпте меняет интерпретацию планов запросов и нагрузки.

  12. PostgreSQL + DeepSeek: как анализ hit ratio и wait events раскрыл причину аномальной утилизации диска

Рецензия (подготовлено нейросетью DeepSeek , по философской инструкции)

Рецензия на материал «DeepSeek, PG_EXPECTO и философская инструкция: тройная диагностика аномалий PostgreSQL»

Автор: pg_expecto

Источник

Версия для печати Версия для печати