Обеспечение надёжности результатов анализа производительности PostgreSQL при её деградации: квантификация уверенности, внедрение методологий критического мышления (включая CoVe, ToT, Pre-Mortem, Red Teaming) и идентификация зон априорной неопределённости.
⚠️Официальное предупреждение (дисклеймер)⚠️
Настоящая статья подготовлена с использованием технологий искусственного интеллекта.
В частности:
— экспериментальные данные обработаны и проанализированы нейросетью;
— иллюстративный материал, сопутствующие слоганы, а также предисловие и послесловие сгенерированы нейросетью;
— макет статьи редактировался и корректировался нейросетью.
Лицам, придерживающимся позиции «ИИ-веганства» (испытывающим устойчивый страх, неприязнь или психологический дискомфорт по отношению к нейросетевым системам), настоятельно не рекомендуется ознакомление с содержанием данной публикации, равно как и участие в её обсуждении, во избежание возможного нанесения вреда психологическому благополучию.
GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
Данная статья открывает цикл публикаций, посвящённых применению инструкций для нейросетей в процессе анализа статистических данных, характеризующих производительность СУБД PostgreSQL.
Содержание
Предшествующие исследования, принятые в качестве теоретико-методологической базы
4. Анализ инцидента производительности СУБД с использованием системного промпта PG_EXPECTO
Предшествующие исследования, принятые в качестве теоретико-методологической базы
Сравнение отчётов PG_EXPECTO: экспертный промпт против философской рамки. | Postgres DBA | Дзен
Без инструкции → норм, с шаблоном → хуже, с v10.2 → лучший результат. | Postgres DBA | Дзен
«Эпистемический протокол» и «Доменная методология» | Postgres DBA | Дзен
Сравнение PG_EXPECTO и BRO v14: снижение галлюцинаций в отчётах PostgreSQL. | Postgres DBA | Дзен
PG_EXPECTO:Системный промпт для анализа производительности СУБД PostgreSQL. | Postgres DBA | Дзен
Благодарность
Степан@LocID за идею использования инструкций для нейросети:
Ваша LLM галлюцинирует? Наденьте на неё экзоскелет — и заставьте работать по правилам
Продолжение:
Я не программист. Я два месяца учил нейросеть не подлизывать
Результат: инструкция нейросети для практического применения при анализе производительности СУБД PostgreSQL:
1. Предисловие
В статье разбирается реальный инцидент деградации производительности PostgreSQL 15.14.
Для исследования применена двухэтапная методология:
Этап-1
Анализ статистических данных производительности СУБД PostgreSQL с применением системного промпта PG_EXPECTO (vmstat, iostat, pg_stat_statements, ожидания): доминирование IO-блокировок, насыщение диска vdb по IOPS и конкретный queryid, генерирующий 89% нагрузки.
Этап-2
Философская инструкция Philosophical_instruction_v3.5_beta переработала полученные выводы через эпистемологический фильтр: каждый тезис прошёл проверку источником и свежестью, получил светофор уверенности (???⬛), а также процедуры CoVe, ToT, Pre-Mortem и Red Teaming — чтобы итоговый отчёт был максимально правдивым, защищённым от галлюцинаций и честно фиксировал как доказанные факты, так и области неизвестного.
Результат
Объективная диагностика инцидента с чёткими рекомендациями и списком недостающих данных для дальнейшей оптимизации.
2. Постановка задачи
Провести анализ инцидента производительности СУБД с использованием системного скрипта PG_EXPECTO и инструкции для нейросети Philosophical_instruction_v3_5_beta.md
Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.
3. Инцидент производительности СУБД
Рис.1 Панель Zabbix - индикатор деградации скорости
Операционная скорость
Рис.2 График изменения операционной скорости в период инцидента
Ожидания СУБД
Рис.3 График изменения ожиданий СУБД в период инцидента
Инцидент снижения производительности СУБД подтверждается одновременным проявлением двух факторов: операционная скорость падает, а ожидания СУБД - растут.
4. Анализ инцидента производительности СУБД с использованием системного промпта PG_EXPECTO
Входные данные для анализа
_1.settings.txt - НАСТРОЙКИ СУБД и VM
2.1.test.postgresqlvmstat.txt - ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД: КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ СУБД и VMSTAT
2.postgresqlvmstat.txt - ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД: КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ СУБД и VMSTAT
3.1.test.vmstatiostat.txt - ТЕСТОВЫЙ ОТРЕЗОК: КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ МЕТРИК VMSTAT-IOSTAT
3.vmstatiostat.txt - ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД: КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ МЕТРИК VMSTAT-IOSTAT
x.1.test.postgresql.cluster_performance.txt - ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД: ИСХОДНЫЕ ДАННЫЕ ПРОИЗВОДИТЕЛЬНОСТИ И ОЖИДАНИЙ СУБД
x.1.test.queryid_pareto.txt - ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД: ДИАГРАММА ПАРЕТО ПО QUERYID
x.1.test.vmstat.txt - ТЕСТОВЫЙ ОТРЕЗОК ПРОИЗВОДИТЕЛЬНОСТИ СУБД: Данные VMSTAT
x.postgresql.cluster_performance.txt - ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД: ИСХОДНЫЕ ДАННЫЕ ПРОИЗВОДИТЕЛЬНОСТИ И ОЖИДАНИЙ СУБД
x.queryid_pareto.txt - ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД: ДИАГРАММА ПАРЕТО ПО QUERYID
x.vmstat.txt - ИНЦИДЕНТ ПРОИЗВОДИТЕЛЬНОСТИ СУБД: Данные VMSTAT
Системный промпт PG_EXPECTO
Системный промпт PG_EXPEXTO - анализ инцидента.txt — Яндекс Диск
Результат: отчет по инциденту производительности СУБД, с использованием системного промпта PG_EXPECTO
5. Подготовка аналитического отчёта по инциденту производительности СУБД, с использованием инструкции Philosophical_instruction_v3_5_beta.md
Входные данные для анализа
1.incident.txt - отчет по инциденту производительности СУБД с использованием системного промпта PG_EXPECTO
Philosophical_instruction_v3_5_beta.md - Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.
Промпт с использованием инструкции Philosophical_instruction_v3_5_beta для анализа отчета по инциденту производительности СУБД
5.1 Светофоры уверенности (???⬛)
Это главный инструмент эпистемической честности агента.
Каждый цвет показывает, насколько утверждение обосновано — не «кажется ли оно правильным», а какова доказательная база.
5.1.1 Определения цветов
? Зелёный — Проверено
Утверждение опирается на внешний источник (документация, спецификация, воспроизводимый эксперимент) или получено путём детерминированной логики из проверенных посылок.
? Жёлтый — Правдоподобно, но не проверено
Информация из памяти модели, логически непротиворечивая, но без актуального внешнего подтверждения. Либо вывод сделан по индукции или абдукции с возможными альтернативами.
? Красный — Нет информации или устаревшие данные
Догадка, устаревшая информация (более 18 месяцев для быстро меняющихся областей), либо утверждение, противоречащее документации. Требует самостоятельной проверки пользователем.
⬛ Чёрный — Не найдено
Честное «я не знаю». В обучающих данных нет релевантной информации, и внешняя проверка недоступна.
5.1.2 Как определяется цвет: правило min(Источник, Свежесть)
Итоговый светофор — худший из двух показателей: откуда взято утверждение и насколько оно актуально.
2.1. Источник (Source)
? Внешний проверенный источник
Спецификация, документация, результат выполнения кода.
Пример: «Согласно PEP 8, отступ — 4 пробела».
? Память модели
Знания из обучающих данных, без текущей проверки.
Пример: «В Python для работы с JSON есть модуль json».
? Догадка или экстраполяция
Неподтверждённое предположение, аналогия без проверки.
Пример: «Скорее всего, эта функция работает так же, как в предыдущей версии».
⬛ Полное отсутствие данных
Пример: «Не знаю, поддерживает ли эта библиотека WebSocket».
2.2. Свежесть (Freshness)
Категории данных и их «возраст» влияют на цвет:
Вневременные факты (математика, базовая грамматика, логика):
Любой возраст → ?.
SaaS, цены, тарифы:
Менее 6 месяцев → ?.
6–18 месяцев → ?.
Более 18 месяцев → ?.
Неизвестный возраст → ?.
Библиотеки, фреймворки:
Менее 6 месяцев → ?.
6–18 месяцев → ?↓ (понижение даже при ? источнике).
Более 18 месяцев → ?.
Неизвестный возраст → ?.
Языки программирования, СУБД:
Менее 6 месяцев → ?.
6–18 месяцев → ?↓.
Более 18 месяцев → ?.
Неизвестный возраст → ?.
Протоколы, стандарты:
Менее 6 месяцев → ?.
6–18 месяцев → ?.
Более 18 месяцев → ?.
Неизвестный возраст → ?.
?↓ означает, что даже если источник ?, свежесть опускает итог до ?.
2.3. Итоговый расчёт
Итоговый светофор = min(Source, Freshness) — то есть самый низкий из двух показателей.
Примеры расчёта:
☑️Утверждение: «Функция print() выводит текст в консоль».
Источник: ? (память, вневременной факт).
Свежесть: ? (вневременное).
Итог: ?.
☑️Утверждение: «В React 18 появился Concurrent Mode».
Источник: ? (память модели).
Свежесть: ? (данные 2021 года).
Итог: ?.
☑️Утверждение: «Последняя версия Next.js — 14.2.3».
Источник: ? (без проверки на npm).
Свежесть: ? (данные быстро устаревают).
Итог: ?.
5.1.3 Fast-Path ?
Для простых запросов, соответствующих всем условиям:
Ответ короче 50 слов.
Не код, не архитектура, не High-Stakes.
Нет двусмысленности.
Нет противоречия с контекстом.
Не требуются актуальные данные (например, арифметика, базовая грамматика).
Светофор ? ставится без дополнительной проверки. Это единственное исключение из строгого чеклиста.
5.1.4 Как светофоры отображаются в ответе
Гранулярно по отдельным фактам, а не один на весь ответ. Каждое значимое утверждение получает свой цвет.
Светофор ставится перед утверждением (или в скобках после него), например:
? let и const имеют блочную область видимости.
? В большинстве браузеров const работает быстрее, но это зависит от JIT-компилятора.
? Текущая версия Chrome поддерживает эту фичу, но требуется уточнить по Can I Use.
Запрещено использовать слова «очевидно», «конечно», «безусловно» без ? источника.
5.1.5 Почему это важно
? даёт пользователю право доверять без дополнительной проверки.
? сигнализирует: «Скорее всего верно, но я не уверен — проверьте, если критично».
? требует обязательной самостоятельной проверки пользователем.
⬛ честно ограничивает область знания агента.
Без светофоров уверенность в ответе неотличима от реальной обоснованности — это ведёт к карго-культу знания. Агент обязан предоставлять пользователю инструмент для информированного риска.
6. Результат - Аналитический отчет по инциденту производительности СУБД, с использованием инструкции для нейросети Philosophical_instruction_v3_5_beta.md
Общая информация
Периоды анализа:
Тестовый период: 2026-04-07 13:02 – 14:02.
Инцидентный период: 2026-04-07 14:02 – 15:02.
Конфигурация сервера:
CPU: 16 vCPU (Intel Xeon Skylake, KVM).
RAM: 62.80 ГБ.
ОС: AstraLinux SE.
PostgreSQL 15.14.
Дисковая подсистема:
/data → vdb (2 ТБ).
/wal → vdc (100 ГБ).
/log → vdd (20 ГБ).
/backup → vde+vdf (2.5 ТБ).
Ключевые настройки PostgreSQL:
shared_buffers = 16079 МБ (≈ 25% RAM).
effective_cache_size = 48237 МБ (≈ 77% RAM).
maintenance_work_mem = 1024 МБ.
work_mem = 12 МБ.
max_wal_size = 8 ГБ.
checkpoint_timeout = 15 мин.
autovacuum_naptime = 1 сек, scale factor vacuum = 0.01, analyze = 0.005.
wal_compression = on.
effective_io_concurrency = 300.
random_page_cost = 1.1.
Методология анализа: статистическая обработка метрик (p‑value, ВКО, R²). Корреляции с p≥0.05 исключены. R²<0.2 признан непригодным для интерпретации.
Ключевые проблемы СУБД и инфраструктуры
1. Доминирование ожиданий ввода‑вывода (IO)
? В обоих периодах тип IO составляет >99.9% всех ожиданий СУБД (источник – отчёт).
? Основное событие ожидания: DataFileRead (98.8% в первом периоде, 99.8% во втором).
? Интегральный приоритет IO вырос с 0.3718 (период 1) до 0.6271 (период 2) — усиление влияния дисковой подсистемы.
Ограничение: высокая доля IO автоматически даёт корреляцию 1.0 с общими ожиданиями; дополнительного диагностического смысла это не несёт. Критичность подтверждается другими метриками (см. ниже).
2. Насыщение дискового устройства данных (vdb) по IOPS
? Утилизация vdb (%util) ≈100% на протяжении 100% времени в обоих периодах.
? Средняя глубина очереди aqu_sz постоянно >1 (медиана 3.72–4.03, максимум до 6.36).
? Время отклика (r_await/w_await) остаётся ≤3.2 мс — в пределах допустимого для виртуализированного хранилища.
? Сильная обратная корреляция операционной скорости (SPEED) с IOPS устройства vdb:
Период 1: r = -0.8625, R² = 0.74.
Период 2: r = -0.7520, R² = 0.57.
? Корреляция SPEED с пропускной способностью (MBps) статистически незначима.
Вывод: производительность лимитирована количеством операций ввода‑вывода в секунду (IOPS), а не объёмом передаваемых данных. Устройство vdb работает на пределе возможностей.
3. Основной генератор нагрузки — запрос 8811732978066195686
? Запрос с queryid = 8811732978066195686 ответственен за ~89% всех ожиданий IO в обоих периодах.
? Количество вызовов запроса выросло незначительно (+5.7%), но суммарные ожидания IO увеличились на 26.1% (с 99202 до 125139).
? Во втором периоде добавились события: DataFilePrefetch и DataFileWrite (ранее только DataFileRead и DataFileExtend).
Интерпретация: изменение плана выполнения или рост объёма обрабатываемых данных.
4. Ухудшение тренда операционной скорости во втором периоде
? Тренд SPEED в периоде 1: положительный (коэффициент +33.14, R²=0.43).
? Тренд SPEED в периоде 2: отрицательный (коэффициент -29.61, R²=0.32 — слабая модель).
? В периоде 2 появилась значимая обратная корреляция SPEED с общими ожиданиями: r = -0.7192, R² = 0.52 (в первом периоде связь отсутствовала).
5. Рост нагрузки на запись и WAL
? На устройстве vdb во втором периоде:
w/s выросло с медианы 36 до 48 (максимум 67).
wMB/s увеличилось (максимум с 0.96 до 1.22 МБ/с).
? На устройстве vdc (WAL):
w/s выросло с медианы 15 до 20.
Появилась значимая корреляция wa ↔ %util vdc (r=0.512, R²=0.26).
Усилилась обратная корреляция SPEED ↔ IOPS на vdc: r = -0.8548, R²=0.73 (период 2).
? Ослабление связей буферов/кэша ОС с операциями записи (R² упал с 0.61–0.63 до незначимых) указывает на возможное увеличение доли синхронных записей или частых сбросов грязных страниц.
6. Снижение простоя CPU
? cpu → id (простой) в периоде 1 имел сильный отрицательный тренд (коэф. 23.77, R²=0.62).
? Во втором периоде тренд слабее (коэф. 6.06, R²=0.23).
? us+sy остаётся низким (медиана ~12%), перегрузки CPU не наблюдается.
7. Память и буферный кэш
? Свободная RAM <5% в 100% времени, но свопинг отсутствует — память полностью занята под page cache ОС и буферы PostgreSQL.
? При shared_buffers = 16 ГБ и effective_cache_size = 48 ГБ объём данных, читаемых проблемным запросом, вероятно, превышает доступный кэш.
8. Ограничения анализа (недостающие данные)
⬛ Текст и план выполнения (EXPLAIN (ANALYZE, BUFFERS)) запроса 8811732978066195686.
⬛ Статистика по таблицам и индексам (pg_stat_user_tables, pg_stat_user_indexes, pg_class).
⬛ Показатели буферного кэша (pg_statio_user_tables, hit ratio).
⬛ Детализация по событиям LWLock (какие именно блокировки растут).
⬛ Данные о контрольных точках и bgwriter (pg_stat_bgwriter).
⬛ Информация о типе и конфигурации дискового хранилища (политики кэширования гипервизора).
Рекомендации по оптимизации СУБД и инфраструктуры
В соответствии с ограничениями задачи конкретные изменения конфигурации не предлагаются. Приведённые ниже направления являются логическими выводами из анализа, но требуют дополнительной проверки перед внедрением.
1. Оптимизация запроса 8811732978066195686
? Получить текст запроса и план выполнения с BUFFERS.
? Проверить возможность создания индексов для замены Seq Scan на Index Scan / Bitmap Index Scan.
? Оценить эффективность предвыборки (effective_io_concurrency = 300) — возможно, её влияние избыточно при насыщении IOPS.
? Рассмотреть рефакторинг запроса: уменьшение объёма читаемых данных (фильтрация на ранних этапах, денормализация, материализованные представления).
2. Снижение нагрузки на дисковую подсистему
? Перенос части данных на более быстрые носители (NVMe/SSD с высокими IOPS) — решение уровня инфраструктуры.
? Увеличение shared_buffers нецелесообразно при текущем уровне использования RAM (уже 25%) без анализа hit ratio; может привести к вытеснению page cache ОС.
? Проверить параметры bgwriter и частоту контрольных точек — возможно, слишком частые сбросы грязных страниц усиливают запись.
3. Управление записью и WAL
? Исследовать причину роста записи на vdb и vdc:
Увеличение числа модифицирующих операций в запросе 8811732978066195686.
Возможное образование temp files при выполнении запроса (проверить log_temp_files).
? Оценить влияние wal_compression = on — положительно сказывается на объёме WAL, но не на IOPS.
4. Мониторинг и сбор дополнительных метрик
? Регулярно снимать показатели pg_stat_bgwriter, pg_stat_user_tables, pg_statio_user_tables с интервалом 1–5 минут.
Необходимая дополнительная информация для продолжения анализа и оптимизации
1. Текст и план выполнения проблемного запроса
SELECT query FROM pg_stat_statements WHERE queryid = 8811732978066195686;
EXPLAIN (ANALYZE, BUFFERS, TIMING) <текст запроса>;
2. Статистика по таблицам и индексам, задействованным в запросе
pg_stat_user_tables (seq_scan, idx_scan, n_tup_ins/upd/del, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze).
pg_stat_user_indexes (idx_scan, idx_tup_read, idx_tup_fetch).
pg_class.relpages, pg_class.reltuples.
3. Показатели буферного кэша PostgreSQL
pg_statio_user_tables (heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit).
Вычисление hit ratio для таблиц и индексов.
4. Данные о работе контрольных точек и фонового писателя
pg_stat_bgwriter (checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_alloc).
5. Профиль ожиданий LWLock
Использовать pg_wait_sampling или аналоги для получения списка конкретных событий LWLock (например, WALWriteLock, BufferContent, LockManager).
6. Информация о временных файлах
Параметр log_temp_files = 0 и анализ логов PostgreSQL.
Статистика pg_stat_database.temp_files, temp_bytes.
7. Характеристики дисковой подсистемы
Тип дисков (HDD/SSD/NVMe), конфигурация RAID (если есть).
Политики кэширования на уровне гипервизора/стораджа.
Максимальные значения IOPS и пропускной способности, гарантированные провайдером.
8. Детальные графики метрик с минутным разрешением
Для проверки гипотезы о кратковременных пиках нагрузки, сглаженных в предоставленных агрегатах.
До получения перечисленных данных углублённый анализ причин деградации и выработка конкретных мер оптимизации невозможны.
Общий итог
Применение связки PG_EXPECTO и Philosophical_instruction_v3.5_beta позволило не просто констатировать факт деградации производительности PostgreSQL, а статистически доказать её корневые причины: доминирование IO-ожиданий (99.9% всех ожиданий),насыщение дискового устройства /data (vdb) по IOPS при 100% утилизации и отсутствии перегрузки по пропускной способности, а также выявление единственного запроса с queryid 8811732978066195686, ответственного за 89% всех IO-ожиданий.
Установлено, что операционная скорость обратно коррелирует с IOPS (R² до 0.74), а рост вызовов запроса всего на 5.7% привёл к увеличению суммарных ожиданий на 26.1% — явный признак изменения плана выполнения или роста объёма обрабатываемых данных.
Без PG_EXPECTO эти взаимосвязи остались бы скрытыми за шумом метрик;
Без Philosophical_instruction мы рисковали бы выдать правдоподобные догадки за доказанные факты.
Послесловие
Представленная методология не является «серебряной пулей» — она честно маркирует области неизвестного (⬛): текст и план проблемного запроса, детальную статистику буферного кэша, показатели bgwriter и контрольных точек, а также характеристики дисковой подсистемы. Именно эти пробелы не позволяют дать окончательные рекомендации по изменению конфигурации или рефакторингу запроса.
Тем не менее, сам подход — статистический корреляционный анализ через PG_EXPECTO с последующей эпистемологической фильтрацией через Philosophical_instruction — может быть воспроизведён на любом инциденте производительности СУБД.
Светофоры уверенности превращают отчёт из набора «экспертных мнений» в инструмент информированного риска:
инженер точно знает, каким выводам можно доверять без дополнительной проверки (?),
какие требуют верификации (?),
а какие — лишь догадки (?).
Анонс следующей статьи
Диагностика инцидентов производительности в PostgreSQL традиционно требует от администратора не только глубоких технических знаний, но и способности отделять причинно-следственные связи от случайных корреляций в потоке метрик vmstat и iostat. Методология PG_EXPECTO предлагает структурированный подход к такому анализу, превращая сырые данные в сводный отчет. Однако ключевым фактором, определяющим глубину и достоверность выводов, становится качество инструкций, управляющих логикой искусственного интеллекта. В этой статье мы исследуем эволюцию Philosophical_instruction — от базовой версии v3.5 до философски усиленной v5.1 — и на примере реального инцидента показываем, как внедрение протоколов эпистемической строгости, Pre-Mortem анализа и самопроверки меняет подход к поиску узких мест в СУБД.