PG_EXPECTO 8.1.2: принцип фальсифициремости по К. Попперу и статистика временных файлов

Сравнительное исследование традиционного экспертного подхода и методологии, основанной на принципе фальсифицируемости (К. Поппер), на примере инцидента производительности PostgreSQL 15.14. Выявляется роль временных файлов как критического, но недооцениваемого фактора: их объём (до 10 ГБ/час) и размещение в переполняющемся разделе /tmp создают паразитную IO-нагрузку, маскируемую при классическом анализе.

Показано, что перевод выводов из формы «тезис (вероятно)» в систему экспериментально проверяемых утверждений позволяет отделить причинно-следственные связи от корреляционных артефактов.

PG_EXPECTO 8.1.2: Постмортем без галлюцинаций

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.

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

GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL

Глоссарий терминов | Postgres DBA | Дзен

Предисловие

Анализ логов и статистик производительности СУБД традиционно опирается на неявный авторитет интерпретатора – эксперта, который формулирует диагностические гипотезы на основе собственного опыта и эвристик. Такой подход, при всей своей практической ценности, подвержен систематическим ошибкам: подтверждающему смещению, игнорированию альтернативных объяснений и неявному допущению единственной причины.

Настоящая работа предлагает альтернативную эпистемологическую рамку, интегрированную в инструмент PG_EXPECTO 8.1.2. Вместо поиска «истинного диагноза» мы строим отчёт как набор жёстко фальсифицируемых утверждений, каждому из которых сопоставлено явное условие опровержения.

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

PG_EXPECTO 8.1.2 : Сравнительное влияние фальсифицируемости промптов на качество аналитических отчетов по производительности PostgreSQL

Инцидент производительности СУБД

Операционная скорость

Рис.1 График изменения операционной скорости СУБД в ходе инцидента

Ожидания СУБД

Рис.2 График изменения ожиданий СУБД в ходе инцидента

Инструкция по подготовке аналитического отчета

Аналитический отчет по инциденту производительности СУБД, подготовленный с помощью DeepSeek

Общая информация

СУБД: PostgreSQL 15.14 на Astra Linux SE 8.3 (x86_64)

Аппаратная платформа: Виртуальная машина KVM (16 vCPU Intel Xeon Skylake, 62.8 ГБ RAM)

Сравниваемые периоды: Тест (10:08–11:08) и Инцидент (11:08–12:08) с минутными наблюдениями

Ключевые параметры конфигурации: shared_buffers = 16GB, effective_cache_size = 48GB, work_mem = 12MB, random_page_cost = 1.1, агрессивный autovacuum (naptime=1s, scale_factor=0.01), checkpoint_timeout=15min

Наблюдается выраженное падение производительности: медианная операционная скорость снизилась на 33%, ожидания (преимущественно ввода-вывода) выросли на 72%. Основной тип ожиданий — DataFileRead (IO), корреляция IO с общими ожиданиями r=1.0. Характер нагрузки на дисковую подсистему претерпел изменения: от чтения, лимитированного числом операций ввода-вывода в единицу времени, к нагрузке, ограниченной пропускной способностью, а также к возрастанию роли записи "грязных" страниц.

Ключевые проблемы СУБД и инфраструктуры

1. Доминирование ожиданий ввода-вывода и смена профиля нагрузки

❓Тезис (Вероятно): Падение производительности обусловлено исключительно ожиданиями IO, причем в инцидентный период основным источником ожиданий стали не чтения с диска, а сброс грязных страниц (запись), что видно по корреляции IO↔bo и появлению DataFileExtend.

Информация для опровержения: Если бы корреляция IO↔bo была ложной из-за вмешивающегося фактора (например, одновременный рост записи WAL, который коррелирует с bo по случайному совпадению), то прямой анализ pg_stat_bgwriter (buffers_backend_fsync и т.д.) показал бы иную картину. Также если бы профиль запросов не изменился (отсутствие новых тяжелых запросов), то не было бы пиков пропускной способности, и изменение корреляции с bi на bo не подтвердилось бы.

2. Появление критически тяжелых запросов

❓Тезис (Вероятно): Два новых запроса (queryid 350… и 697…) генерируют экстремальный объем чтений с диска (до 2332 ожиданий на вызов), что приводит к вытеснению буферного кэша и перегрузке канала ввода-вывода.

Информация для опровержения: Если EXPLAIN (ANALYZE, BUFFERS) этих запросов покажет не последовательное сканирование, а эффективное использование индексов с низким числом buffers read, то гипотеза об их тяжести ошибочна. Альтернативно, если всплески IO совпадают по времени не с вызовами этих запросов, а с другими событиями (например, checkpoint), то их роль второстепенна.

3. Острый дефицит оперативной памяти

❓Тезис (Вероятно): Свободная память ОС не превышает 5% (≈1.1 ГБ) на протяжении всего времени, что при наличии 57 ГБ page cache создает постоянное давление на кэш и приводит к его вытеснению при интенсивном чтении. Это усиливает дисковые ожидания.

Информация для опровержения: Если бы мониторинг показал, что при росте cache не происходит снижения free (т.е. память под кэш берется из неиспользуемой), то дефицит не являлся бы проблемой. Также отсутствие прямой корреляции между снижением cache и ростом ожиданий IO в какие-либо моменты могло бы опровергнуть влияние вытеснения кэша.

4. Недостаточный work_mem и чрезмерное использование временных файлов

❓Тезис (Вероятно): При work_mem = 12MB генерируются значительные объемы временных файлов (10 ГБ в час в тесте, 2 ГБ в инциденте), что создает паразитную IO-нагрузку и риск переполнения /tmp (доступно лишь 11 ГБ).

Информация для опровержения: Если анализ pg_stat_database.temp_files и temp_bytes после увеличения work_mem покажет, что временные файлы не сократились и производительность не улучшилась, то корень проблемы не в work_mem. Также если временные файлы размещаются на быстром отдельном томе и не создают узкого места, их влияние может быть переоценено.

5. Аномальная дисковая очередь vdb при низкой утилизации

❓Тезис (Вероятно): В инцидентный период глубина очереди запросов к диску данных (vdb) постоянно >1, хотя медианная утилизация всего 23%. Это указывает на кратковременные микро‑заторы, возможно, вызванные синхронными сбросами или ограничениями на уровне гипервизора.

Информация для опровержения: Если углубленный мониторинг с высоким разрешением (iostat -x 1) покажет, что очередь возникает только в моменты реального насыщения утилизации (90%+), то предыдущая интерпретация неверна. Либо если при переносе WAL на отдельный физический диск очередь исчезнет, проблема была в смешивании нагрузок.

6. Рост активности autovacuum и грязных страниц

❓Тезис (Вероятно): Увеличение операций autovacuum в 2 раза и записанных буферов checkpoint на 63% усиливает нагрузку на запись (bo), что может конкурировать с чтениями и снижать производительность.

Информация для опровержения: Если измерение blk_write_time в pg_stat_database не показывает пропорционального роста времени записи, то влияние autovacuum/checkpoint на общую производительность незначительно. Также если временно отключить autovacuum в период низкой нагрузки и не увидеть улучшения скорости, то его роль мала.

Рекомендации по оптимизации СУБД и инфраструктуры

Уровень 1 – Немедленные действия (Вероятно, эффект высокий)

Исследовать и переписать проблемные запросы.

• Получить планы выполнения EXPLAIN (ANALYZE, BUFFERS) для queryid 350…, 697… и основного 881….
• Добавить недостающие индексы (для условий WHERE, JOIN), рассмотреть частичные или покрывающие индексы, чтобы избежать последовательных сканирований больших таблиц.
• Оценить возможность рефакторинга кода приложения: сократить объем извлекаемых данных, применить разбиение на батчи, заменить крупные вставки INSERT INTO ... SELECT на COPY или многострочные вставки.
• Ожидаемый эффект: радикальное снижение IO‑ожиданий (50–70% от текущих) и восстановление операционной скорости.
• Риски: изменение планов может привести к новым проблемам (например, резкое увеличение использования CPU). Следует предварительно протестировать изменения.
• Информация для проверки: сравнить количество buffers read и shared_blks_read до и после оптимизации.

Увеличить work_mem с 12MB до 64–128MB.

• Выполнить осторожно: мониторить общее потребление памяти бэкендами (used memory = shared_buffers + сумма work_mem по активным соединениям). При 16 vCPU и типичных 50 активных бэкендах 64MB * 50 = 3.2 ГБ, допустимо. При 128MB — 6.4 ГБ, все еще в пределах с учетом 62.8 ГБ RAM.

Переместить temp_tablespaces на быстрый отдельный том (если доступен), чтобы снизить конкуренцию IO.

• Ожидаемый эффект: резкое сокращение временных файлов, снижение IO-нагрузки и риска переполнения /tmp.
• Риски: чрезмерное увеличение может привести к нехватке памяти и уходу в swap. Установить мониторинг free и pg_stat_database.temp_files.
• Информация для проверки: отследить объем и количество temp_files после изменения.

Уровень 2 – Плановые изменения (Вероятно, эффект средний)

Скорректировать параметры autovacuum для снижения пиковой нагрузки.

• Сохранить scale_factor = 0.01, но увеличить autovacuum_vacuum_cost_delay до 5–10ms либо уменьшить vacuum_cost_limit до 100–200. Это снизит интенсивность операций vacuums по записи и чтению без риска чрезмерного распухания таблиц.
• Ожидаемый эффект: сглаживание нагрузки от записи грязных страниц, стабилизация отклика.
• Риски: рост числа мертвых кортежей. Мониторить pg_stat_user_tables.n_dead_tup и длительность транзакций.
• Информация для проверки: сравнить blk_write_time и корреляцию IO↔bo до и после.

Оптимизировать настройки контрольной точки.

• Снизить checkpoint_completion_target с 0.9 до 0.8, чтобы распределить запись более равномерно в пределах 15-минутного окна и уменьшить пиковую IO-активность.
• Ожидаемый эффект: более плавный профиль записи, меньшее влияние на одновременные чтения.
• Риски: минимальны при текущем объеме буферов. Следить за pg_stat_bgwriter.buffers_checkpoint и временем выполнения.
• Информация для проверки: оценить дисперсию времени синхронизации чекпоинтов в логах log_checkpoints.

Уровень 3 – Инфраструктурные изменения (Вероятно, необходимы для долгосрочной стабильности)

Увеличить оперативную память до 72–80 ГБ (или оптимизировать использование).

Если увеличение RAM невозможно, рассмотреть уменьшение shared_buffers до 12 ГБ в пользу страничного кэша ОС, что может помочь при текущем профиле нагрузки (тяжелые чтения больших объемов). Однако это требует бенчмаркинга: проверить hit ratio и показатели ожиданий.

• Ожидаемый эффект: больший запас для кэширования, снижение вытеснения и дисковых операций.
• Риски: уменьшение shared_buffers может снизить hit ratio и увеличить обращения к диску, если рабочий набор сильно помещается в 16 ГБ.
• Информация для проверки: промоделировать с помощью pg_buffercache или сравнить пиковые значения blk_read_time.

Диагностировать аномальную очередь на vdb.

• Провести детальный мониторинг с интервалом 1 сек (iostat -x 1) для выявления кратковременных всплесков.

Проверить настройки планировщика IO в гостевой ОС (должен быть none или noop; на KVM часто используется mq-deadline). Рассмотреть увеличение nr_requests для виртуального устройства.

• При необходимости согласовать с администратором гипервизора отсутствие жестких ограничений IOPS/пропускной способности.
• Ожидаемый эффект: устранение микро‑задержек, возможно дополнительное улучшение отклика на 5–10%.

Ревизия размещения WAL.

• Убедиться, что диск /wal (vdc) используется эксклюзивно под WAL и не содержит других активных операций. При возможности перенести WAL на NVMe-устройство.
• Рассмотреть отключение synchronous_commit (если приложение допускает потерю последних транзакций в случае сбоя) для снижения давления на синхронную запись WAL.
• Информация для проверки: сравнить задержку w_await и корреляцию SPEED ↔ IOPS (vdc) после изменений.

Необходимая дополнительная информация

Для продолжения анализа и верификации гипотез требуется собрать следующие данные:

• Планы выполнения проблемных запросов: EXPLAIN (ANALYZE, BUFFERS, TIMING) для queryid 350…, 697…, 881…. Это решающий артефакт для подтверждения/опровержения причины 70% ожиданий.
• Статистика по таблицам и индексам: pg_stat_user_tables (seq_scan, idx_scan, n_tup_ins/upd/del, n_dead_tup), размер таблиц (pg_total_relation_size) для всех отношений, вовлеченных в запросы.
• ‼️Детализированные логи PostgreSQL: включить log_min_error_statement = error, log_lock_waits = on, log_checkpoints = on для выявления причин query_canceled и точной картины чекпоинтов.(ℹ️Доступно в исходном отчете pg_expecto)
• Тренды hit ratio: динамика pg_stat_database.blk_hit и blk_read (или blks_hit, blks_read из pg_stat_database) за те же периоды. Без этого трудно оценить эффективность кэша.
• Результаты продвинутого мониторинга ввода-вывода: iostat -x 1 в течение характерного периода нагрузки (≥10 минут), одновременно с vmstat 1, для выявления микро‑заторов.
• Конфигурация гипервизора/СХД: наличие ограничений IOPS, параметры дискового планировщика, фактические характеристики дисков (NVMe/SSD/HDD).
• ‼️Снимок pg_stat_bgwriter за периоды: показатели буферов (buffers_clean, maxwritten_clean, buffers_backend, buffers_alloc), чтобы детализировать долю фонового сброса.
• ❓Полный список табличных пространств и их сопоставление с файловыми системами / LVM‑томами, чтобы точно определить, где создаются временные файлы и есть ли возможность их переноса.

Только на основе этих данных можно будет перевести ключевые выводы из статуса "Вероятно" в "Подтверждено" и сформулировать итоговые неизменяемые рекомендации.

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

В ходе инцидента с PostgreSQL 15.14 под Astra Linux SE (16 vCPU, 62.8 ГБ RAM) медианная операционная скорость снизилась на 33%, ожидания ввода-вывода выросли на 72% при корреляции IO с общими ожиданиями r=1.0. Зафиксирована смена профиля нагрузки: переход от чтения, лимитированного интенсивностью операций ввода-вывода (IOPS), к записи «грязных» страниц, ограниченной пропускной способностью подсистемы хранения; это подтверждается увеличением объёма буферов, сбрасываемых обслуживающими процессами (buffers_backend), и числа записей, инициированных контрольными точками. Выявлены два критических запроса (queryid 350…, 697…), генерирующих до 2332 дисковых ожиданий на вызов, и дефицит свободной памяти ОС (<5%, 1.1 ГБ) при 57 ГБ page cache.

Наиболее значимым паразитным фактором оказались временные файлы: при work_mem = 12 МБ их объём достигал 10 ГБ/час в тестовом периоде, создавая IO-конкуренцию и риск переполнения /tmp (11 ГБ).

Аномальная дисковая очередь (vdb, глубина >1 при утилизации 23%) указывает на микро-заторы, потенциально вызванные синхронными сбросами или ограничениями гипервизора.

Все перечисленные тезисы снабжены условиями опровержения (например, анализ EXPLAIN с BUFFERS, мониторинг temp_files после увеличения work_mem, высокочастотный iostat), что позволяет верифицировать каждый вывод независимо от автора отчёта.

Послесловие

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

В представленном инциденте гипотеза о доминирующей роли временных файлов и work_mem может быть опровергнута простым экспериментом: если после увеличения work_mem до 128 МБ temp_files не сократятся, а производительность не вырастет – значит, корень проблемы лежит в ином слое (например, в планировщике IO гипервизора).

Такая эпистемологическая дисциплина особенно востребована при использовании LLM-агентов в эксплуатации: она заменяет иллюзию генерации «истины» на инженерное правило «утверждение ценно, если мы знаем, как его разбить».

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