Может ли ИИ заменить эксперта по PostgreSQL?

Искусственный интеллект все активнее интегрируется в задачи оптимизации баз данных, однако вопрос о его способности полностью заменить человека-эксперта остается открытым. В представленном исследовании анализируется работа нейросетевой модели DeepSeek при оценке производительности СУБД PostgreSQL с применением статистического метода «majority vote» (голосование). В рамках работы выполнен сравнительный анализ двух сценариев выборки — с использованием пяти и одиннадцати независимых прогонов модели — с целью оценки эффективности данного подхода для верификации выводов, минимизации случайных ошибок и повышения надежности итоговых рекомендаций.

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

Постановка проблемы

В отличие от классического программного обеспечения, нейросеть не выполняет жестко заданный алгоритм. Она 🔴генерирует ответ путем вероятностного предсказания следующей единицы информации (токена).🔴

...

Для инженера по производительности это означает катастрофу воспроизводимости.

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

Постановка эксперимента

Провести серию экспериментов по тестированию метода "majority vote (Голосование)"

ℹ️Метод majority vote (Голосование): Модель запускается N раз (например, 5 раз) на одних и тех же данных. Ответы записываются. Если 4 из 5 раз нейросеть указала на проблему с вводом-выводом — это, скорее всего, достоверный сигнал. Если голоса разделились поровну — данные требуют более глубокого анализа человеком. Это снижает влияние «случайной ошибки» конкретного прогона.

1. Результат голосования при N=5

На основе анализа пяти отчетов (test1.txt — test5.txt) с применением метода «majority vote» (голосование 5 из 5) сформирован итоговый отчет. Рекомендации ранжированы по частоте упоминаний (от наиболее консенсусных к менее очевидным).

Достоверные рекомендации по оптимизации производительности

Ниже приведены рекомендации, которые были подтверждены как минимум в 4 из 5 отчетов. Это наиболее надежные выводы, требующие первоочередного внимания.

☑️Оптимизация критического запроса (5 из 5 отчетов)

• Во всех пяти отчетах однозначно идентифицирован главный источник проблем производительности — выполнение хранимой процедуры/функции scenario1() (идентификатор запроса -2753873076014177353). На него приходится более 85% всех ожиданий ввода-вывода.

Рекомендация: Провести немедленный и детальный разбор плана выполнения этого запроса с помощью EXPLAIN (ANALYZE, BUFFERS). Цель — выявить причины огромного количества чтений с диска (операции DataFileRead), проверить эффективность индексов (отсутствие Seq Scan) и логику самой функции для снижения числа читаемых блоков.

☑️Настройка параметров виртуальной памяти ядра (vm.dirty_*) (5 из 5 отчетов)

• Высокие или неоптимальные значения порогов «грязных» страниц (vm.dirty_background_ratio / vm.dirty_ratio) напрямую коррелируют с ростом процессов в состоянии b (непрерываемый сон, ожидание IO) и пиковыми нагрузками на запись. Это усугубляет дисковый bottleneck.

• Рекомендация: Снизить пороги для более раннего и плавного сброса данных на диск. Консенсусные значения (в разных отчетах варьируются):

• vm.dirty_background_ratio рекомендуется уменьшить до 2-3% (было 5-10%).

• vm.dirty_ratio рекомендуется уменьшить до 8-10% (было 15-30%).

• Действие: Внести изменения в /etc/sysctl.conf и применить их.

☑️Анализ и апгрейд дисковой подсистемы (I/O Subsystem) (5 из 5 отчетов)

• Метрики procs -> b (процессы в ожидании IO) и cpu -> wa (iowait) стабильно высоки (более 10% в 100% времени). Это явный признак того, что дисковая подсистема является узким местом, несмотря на наличие отдельных томов для WAL и данных.

Рекомендация:

• Провести стресс-тестирование дисков с помощью iostat -x 1, fio или pg_test_fsync для определения реальной задержки (await), утилизации (%util) и количества IOPS.

• Убедиться, что диски vdd (данные) и vdc (WAL) являются физически разными и быстрыми (NVMe с гарантированной производительностью), а не общим виртуальным хранилищем с ограничениями.

☑️Увеличение оперативной памяти (RAM) (5 из 5 отчетов)

• Во всех отчетах зафиксирован критический сигнал тревоги: уровень свободной оперативной памяти постоянно ниже 5% от общего объема (7.5 ГБ). Это создает сильное давление на память, вытесняет кэш страниц ОС и увеличивает нагрузку на диск.

Рекомендация: Увеличить объем ОЗУ сервера как минимум до 16 ГБ (в идеале до 32 ГБ). Это наиболее эффективная долгосрочная мера для расширения кэширования данных и снижения количества обращений к диску.

☑️Настройка контрольных точек (Checkpoint) и WAL (5 из 5 отчетов)

• Текущий размер WAL (max_wal_size = 4 ГБ) может быть недостаточным, что приводит к частым контрольным точкам и пиковым нагрузкам на запись.

Рекомендация: Увеличить max_wal_size до 8-16 ГБ (или даже 32 ГБ), чтобы сгладить нагрузку на диск и уменьшить частоту синхронных записей во время контрольных точек. Включить log_checkpoints = on для мониторинга.

☑️Мониторинг и проактивное обнаружение (4 из 5 отчетов)

• Необходимо внедрить систему сбора метрик для раннего выявления подобных инцидентов в будущем.

Рекомендация: Настроить алерты по критическим метрикам: свободная RAM < 5%, iowait (wa) > 10%, количество процессов в состоянии b > количества ядер CPU, размер dirty pages. Регулярно анализировать данные из pg_stat_statements и pg_wait_sampling.

☑️Корректировка shared_buffers (4 из 5 отчетов)

• Увеличение shared_buffers до 3 ГБ (во втором эксперименте) дало некоторый прирост, но hit ratio остался на уровне ~97%, что указывает на нехватку памяти для рабочего набора данных.

Рекомендация: Хотя в отчетах есть разночтения (кто-то предлагает еще увеличить, кто-то — уменьшить в пользу кэша ОС), консенсус в том, что текущий размер в 3 ГБ при дефиците общей памяти требует пересмотра. Оптимальным видится увеличение RAM, а затем повторная калибровка shared_buffers до 25-40% от нового объема ОЗУ.

Возможные случайные ошибки

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

❌Снижение shared_buffers до 2–2.5 ГБ

• Где встретилось: В отчете test4.txt.

• Предполагаемая причина ошибки: Эта рекомендация противоречит выводам остальных четырех отчетов, где предлагается либо оставить текущий объем, либо увеличить его. Вероятно, модель в этом прогоне сделала неверный акцент на том, что shared_buffers отнимает память у кэша ОС (page cache), посчитав это первостепенной проблемой. Однако при тотальном дефиците памяти (менее 5% свободной RAM) уменьшение shared_buffers лишь незначительно увеличит кэш ОС, но не решит проблему нехватки памяти в принципе. Корень проблемы — общий объем RAM, а не распределение между кэшами PostgreSQL и ОС.

❌Упоминание LWLock как скрытой проблемы

• Где встретилось: В отчете test2.txt.

• Предполагаемая причина ошибки: Модель могла придать излишнее значение наличию статистически значимой корреляции (p<0.05) для LWLock, хотя в тексте самого отчета указано, что вклад этих блокировок ничтожно мал ("ВКО <0.01"). В условиях доминирующей проблемы с IO (85% ожиданий), попытка углубиться в микроскопические корреляции LWLock является избыточной и отвлекает от главной цели. Это пример «ложного позитива» при анализе больших данных.

❌Некорректная интерпретация autovacuum_work_mem

• Где встретилось: В отчете test4.txt.

• Предполагаемая причина ошибки: В отчете test4.txt рекомендуется проверить, достаточно ли autovacuum_work_mem (512 МБ). Однако в условиях жесточайшего дефицита оперативной памяти (менее 5% свободной RAM) любые рассуждения об увеличении памяти для автовакуума выглядят преждевременными и потенциально опасными, так как могут усугубить memory pressure. Проблема с автовакуумом (если она есть) является вторичной по отношению к проблеме нехватки памяти и перегрузки диска. Модель могла ошибочно воспринять штатный параметр как проблемный, не оценив общий контекст нехватки ресурсов.

2. Результат голосования при N=11

Ниже представлен анализ 11 отчетов с использованием метода «majority vote». Рекомендации, упомянутые в 6 и более отчетах (т.е. более чем в половине), считаются достоверным сигналом. Рекомендации, встретившиеся менее 3 раз, отнесены к возможным случайным ошибкам.

Достоверные рекомендации по оптимизации производительности

(отранжировано по частоте упоминаний – от самых частых к менее частым)

☑️Оптимизация проблемного запроса select scenario1() (queryid -2753873076014177353)

• Частота: 11 из 11

• Суть: Запрос генерирует ~85% всех ожиданий ввода-вывода (DataFileRead). Требуется немедленный разбор с EXPLAIN (ANALYZE, BUFFERS), проверка индексов, рефакторинг или секционирование таблиц.

☑️Настройка параметров виртуальной памяти ядра ОС (vm.dirty_*)

• Частота: 11 из 11

• Суть: Высокая корреляция размера «грязных» страниц с процессами в состоянии b (ожидание I/O) и записанными блоками (bo). Рекомендуется снижение порогов: vm.dirty_background_ratio до 2–5% и vm.dirty_ratio до 8–15% для более раннего старта фоновой записи и предотвращения блокировок.

☑️Увеличение оперативной памяти (RAM)

• Частота: 11 из 11

• Суть: Во всех отчетах зафиксирован критический уровень свободной RAM (<5% при 7.5 ГБ). Это вызывает memory pressure, вытеснение кэша страниц ОС и рост дисковой нагрузки. Рекомендуется расширение ОЗУ как минимум до 16–32 ГБ.

☑️Увеличение max_wal_size

• Частота: 9 из 11

• Суть: Текущий размер (4 ГБ) приводит к частым контрольным точкам и пиковым нагрузкам на запись. Увеличение до 8–16 ГБ (с учетом свободного места на /wal) позволит сгладить нагрузку на дисковую подсистему.

☑️Анализ дисковой подсистемы с помощью iostat

• Частота: 9 из 11

• Суть: Необходимо провести замеры %util, await, avgqu-sz для устройств /dev/vdd (данные) и /dev/vdc (WAL), чтобы подтвердить, что диск является узким местом, и определить, какое именно устройство не справляется.

☑️Проверка физического разделения дисков для WAL и данных

• Частота: 7 из 11

• Суть: В конфигурации используется разделение на тома (/wal на vdc, /data на vdd). Рекомендуется убедиться, что эти логические тома находятся на физически разных дисках, а не конкурируют за один и тот же накопитель.

☑️Корректировка shared_buffers

• Частота: 7 из 11

• Суть: Есть два противоположных мнения (увеличить до 4–4.5 ГБ или уменьшить до 2–2.5 ГБ для экономии места под кэш ОС). Однако сам факт необходимости пересмотра этого параметра на фоне дефицита памяти является достоверным сигналом.

☑️Настройка контрольных точек (checkpoint_completion_target)

• Частота: 6 из 11

• Суть: Увеличение checkpoint_completion_target с дефолтного 0.5 до 0.9 для растягивания записи "грязных" страниц во времени и сглаживания пиковых нагрузок.

☑️Настройка планировщика ввода-вывода (I/O Scheduler)

• Частота: 6 из 11

• Суть: Для SSD/NVMe в виртуальной среде (KVM) рекомендуется использовать планировщик none (или noop), чтобы избежать лишних накладных расходов.

☑️Проверка параметров random_page_cost и effective_io_concurrency

• Частота: 6 из 11

• Суть: Текущие значения (1.1 и 500) соответствуют NVMe. Однако требуется убедиться, что фактическая производительность дисков соответствует этим настройкам (возможно, требуется калибровка с помощью fio).

☑️Мониторинг переключений контекста (cs) и прерываний (in)

• Частота: 6 из 11

• Суть: Высокая корреляция между cs и in указывает на высокую нагрузку на ядро, вызванную дисковой системой. Рекомендация по анализу perf и настройке affinity для прерываний.

Возможные случайные ошибки

*(рекомендации, встретившиеся в 1-2 отчетах)*

❌Увеличение work_mem до 128 МБ

• Где встретилось: Отчет №2.

• Вероятная причина ошибки: Недооценка количества одновременных соединений. При max_connections = 239 увеличение work_mem до 128 МБ может привести к тому, что общий объем выделяемой памяти под сортировки/хэши превысит доступную RAM, усугубив проблему нехватки памяти (которая и так критична).

❌Отключение synchronous_commit

• Где встретилось: Отчеты №3, №6.

• Вероятная причина ошибки: Это рискованная рекомендация, которая может привести к потере данных при сбое. Она уместна только в том случае, если бизнес-требования допускают потерю последних транзакций. В контексте общей проблемы (узкое место диска) это паллиатив, а не лечение, и предлагается без анализа рисков для целостности.

❌Использование материализованных представлений

• Где встретилось: Отчет №4.

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

❌Увеличение autovacuum_max_workers до 8

• Где встретилось: Отчет №4.

• Вероятная причина ошибки: При 8 ядрах CPU и уже существующей дисковой перегрузке, увеличение числа рабочих процессов автовакуума может усилить конкуренцию за диск и ухудшить ситуацию, а не улучшить ее.

❌Уменьшение max_wal_size для более частых контрольных точек

• Где встретилось: Отчеты №8, №9.

• Вероятная причина ошибки: Эта рекомендация противоречит основному тренду (увеличение max_wal_size). В условиях высокой нагрузки на запись частые контрольные точки приведут к еще более интенсивной пиковой записи, что противопоказано при перегруженном диске. Вероятно, это результат неверной интерпретации данных.

3. Анализ влияния количества отчетов в голосовании на достоверные рекомендации и случайные ошибки

Сравнительный анализ результатов голосования для N=5 и N=11

Введение

Метод majority vote применялся к двум наборам отчетов: первый включал 5 прогонов модели, второй – 11 прогонов. Цель – оценить, как увеличение числа голосующих отчетов влияет на достоверность итоговых рекомендаций и снижение случайных ошибок. Ниже представлены ключевые наблюдения и выводы.

☑️Достоверные рекомендации (основной консенсус)

При N=5 (порог ≥4 голосов)

• Оптимизация проблемного запроса (5/5) – главный источник I/O ожиданий.

• Настройка параметров виртуальной памяти ядра (vm.dirty_*) (5/5).

• Анализ и апгрейд дисковой подсистемы (5/5).

• Увеличение оперативной памяти (5/5).

• Настройка контрольных точек и WAL (5/5).

• Мониторинг и проактивное обнаружение (4/5).

• Корректировка shared_buffers (4/5).

При N=11 (порог ≥6 голосов)

• Оптимизация проблемного запроса (11/11).

• Настройка vm.dirty_* (11/11).

• Увеличение RAM (11/11).

• Увеличение max_wal_size (9/11).

• Анализ дисковой подсистемы с iostat (9/11).

• Проверка физического разделения дисков (7/11).

• Корректировка shared_buffers (7/11).

• Настройка checkpoint_completion_target (6/11).

• Настройка планировщика ввода-вывода (6/11).

• Проверка random_page_cost и effective_io_concurrency (6/11).

• Мониторинг переключений контекста (6/11).

Сравнение:

Базовый набор проблем (запрос, память, диск, WAL) подтверждается в обоих случаях. Однако при N=11 добавляются более тонкие, но статистически значимые аспекты (планировщик I/O, параметры стоимости, анализ контекстных переключений). Они не набрали бы нужного числа голосов при N=5, но при большем количестве прогонов становятся достоверными сигналами.

❌Возможные случайные ошибки

При N=5 (встретились в 1–2 отчетах)

• Снижение shared_buffers до 2–2.5 ГБ – противоречит общему выводу о нехватке памяти.

• Упоминание LWLock как скрытой проблемы – ничтожный вклад в ожидания.

• Некорректная интерпретация autovacuum_work_mem – преждевременная рекомендация на фоне дефицита RAM.

При N=11 (встретились в 1–2 отчетах)

• Увеличение work_mem до 128 МБ – риск перерасхода памяти.

• Отключение synchronous_commit – риск потери данных без анализа требований.

• Использование материализованных представлений – может усугубить нагрузку на запись.

• Увеличение autovacuum_max_workers до 8 – усилит конкуренцию за диск.

• Уменьшение max_wal_size для более частых контрольных точек – противоречит основной рекомендации.

Сравнение:

При N=11 список возможных ошибок шире, но все они отвергнуты большинством. Это демонстрирует, что с ростом числа отчетов ложные идеи не набирают критической массы и отсеиваются, тогда как при N=5 некоторые из них могли бы остаться незамеченными просто потому, что не попали в выборку. Важно, что ни одна из ошибок не пересекается с достоверными рекомендациями, что говорит о хорошей фильтрации.

Влияние количества отчетов на достоверность

1️⃣Укрепление доверия к основным выводам

• Рекомендации, получившие 5/5 при N=5, при N=11 также имеют максимальную поддержку (11/11). Это подтверждает их фундаментальную важность.

2️⃣Выявление дополнительных значимых факторов

• При увеличении выборки становятся заметны проблемы, которые ранее могли быть скрыты шумом или не достигали порога (например, настройка планировщика I/O). Это позволяет получить более полную картину узких мест.

3️⃣Снижение влияния случайных ошибок

• Ложные или вредные советы, возникающие в единичных прогонах, не набирают большинства. При N=11 порог (6 голосов) жёстче, чем при N=5 (4 голоса), поэтому фильтрация шума эффективнее. Редкие аномалии остаются в разделе «возможные ошибки» и не попадают в финальные рекомендации.

4️⃣Ранжирование по частоте упоминаний

• В N=11 чётко видна градация важности: от абсолютного консенсуса (11/11) до умеренного (6/11). Это помогает расставить приоритеты при внедрении изменений. В N=5 все достоверные рекомендации находятся в узком диапазоне 4–5 голосов, что не позволяет так же тонко дифференцировать их значимость.

Заключение

Увеличение числа отчетов при голосовании повышает надёжность итоговых рекомендаций за счёт:

• многократного подтверждения ключевых проблем;

• выявления менее очевидных, но систематических факторов;

• надёжного отсеивания случайных и потенциально вредных советов.

Метод majority vote с большим N (например, 11) даёт более полную и точную картину, позволяя принимать обоснованные решения по оптимизации производительности. ➡️Рекомендуется использовать не менее 10–11 прогонов для достижения устойчивого консенсуса и минимизации ложноположительных срабатываний.

Послесловие

Проведённое исследование подтвердило эффективность применения метода «majority vote» для нивелирования недетерминированности выводов нейросетевой модели при анализе производительности СУБД PostgreSQL. Увеличение числа независимых прогонов с пяти до одиннадцати позволило не только многократно верифицировать ключевые проблемы (дефицит оперативной памяти, перегрузка дисковой подсистемы, неоптимальные параметры ядра и контрольных точек), но и выявить статистически значимые, хотя и менее очевидные факторы, такие как необходимость тонкой настройки планировщика ввода-вывода и параметров стоимостных оценок. Одновременно с этим расширение выборки обеспечило надёжное отсеивание ложноположительных и потенциально вредных рекомендаций, которые возникали в единичных отчётах, что подтверждает высокую фильтрующую способность метода при достаточном объёме голосующих экземпляров.

Полученные результаты непосредственно влияют на развитие методики анализа производительности, реализованной в комплексе pg_expecto. Обоснована целесообразность включения в стандартный сценарий использования комплекса требования о многократном (не менее 10–11 повторений) запуске модели с последующим автоматизированным голосованием. Это позволяет повысить достоверность итогового заключения, ввести объективное ранжирование рекомендаций по частоте упоминаний и минимизировать риск принятия ошибочных решений при оптимизации.

Анализ инцидента производительности высоконагруженной СУБД (CPU=200 RAM=1TB).

Введение

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

В данной демонстрации наглядно показано применение инструментария pg_expecto, который позиционируется не только как средство нагрузочного тестирования, но и как эффективный механизм для постмортем-анализа и оптимизации «боевых» инсталляций. Использование pg_expecto позволило не ограничиться констатацией факта падения операционной скорости, а выявить критическую конкуренцию за буферный кэш (LWLock: BufferMapping), изменение паттернов работы расширений СУБД и скрытые проблемы дисковой подсистемы, маскируемые усредненными метриками. Публикация детализирует методологию сравнительного статистического и корреляционного анализа, демонстрируя, как сопоставление эталонного и аварийного периодов функционирования позволяет сформировать научно обоснованные рекомендации по оптимизации как конфигурации СУБД, так и поддерживающей её инфраструктуры.

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

Дата и время инцидента: 11/03/2026 16:35

Производительность и ожидания СУБД в период 14:35 - 16:35

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

Рис.1 - График изменения операционной скорости в отрезке [время инцидента; время инцидента - 2 часа]. Рост производительности сменился снижением.

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

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

Производительность и ожидания СУБД в период инцидента производительности СУБД

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

Рис.3 - Снижение операционной скорости в ходе инцидента. Коэффициент детерминации = 0.9

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

Рис.4 - Рост ожиданий СУБД в ходе инцидента. Коэффициент детерминации = 0.6

1. Сводный сравнительный отчет по производительности СУБД и инфраструктуры 2026-03-11 14:35 - 16:35

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

Объект анализа: СУБД PostgreSQL 15.13 (конфигурация из файла _1.settings.txt) и инфраструктура (vmstat).

Периоды сравнения:

• Инцидент: 2026-03-11 15:35 — 16:35 (файл _2.postgresql_vmstat.txt). Период, квалифицированный как инцидент производительности.

• Тест: 2026-03-11 14:35 — 15:35 (файл _2.1.test.postgresql_vmstat.txt). Период, взятый как тестовый отрезок для сравнения.

Аппаратная конфигурация:

• CPU: 192 ядра (Intel Xeon Platinum 8280L), 4 NUMA-узла.

• RAM: ~1008 GB.

• Дисковая подсистема: Тома LVM на отдельных дисках для данных (/data, 56T), WAL (/wal, 1T), резервных копий (/backup, 2.9T) и логов (/log, 100G).

Итог по разделу "1. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД"

• Ключевое изменение: Основная нагрузка сместилась с Extension в тестовом периоде на LWLock в инциденте. Хотя Extension все еще имеют высокий приоритет, их вклад в общее время ожиданий снизился, в то время как вклад LWLocks остался критически высоким.

• В инциденте также появились и стали значимыми ожидания типов IO и Lock, которые в тестовом периоде не оказывали влияния. Это говорит о том, что система вошла в фазу комплексной деградации, затронувшей диск и блокировки транзакций.

Итог по разделу "2. СРАВНИТЕЛЬНЫЙ ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat"

• Главный сигнал инцидента: Резкое падение cpu_id (простоя CPU) при одновременном падении скорости БД. CPU начинает активно работать, но не на выполнении полезной нагрузки, а на обслуживании простоев (скорее всего, связанных с LWLocks и конкуренцией).

• В тестовом периоде CPU простаивал больше, очереди на выполнение (r) сокращались — система была сбалансирована.

• Проблемы с диском (b, wa), наблюдавшиеся в тесте, в инциденте пошли на спад, что подтверждает смещение фокуса проблемы с IO на CPU и блокировки.

Итог по разделу "3. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat"

• Основное различие в поведении Extension. В инциденте они стали значительно сильнее коррелировать с прерываниями (in), что может указывать на изменение их паттерна работы (например, увеличение частоты вызовов внешних API или обмен по сети).

• Модель зависимости блокировок (Lock) от очереди на CPU (r) в инциденте стала слабой, хотя связь осталась. Это значит, что в формирование очереди на CPU теперь вносят вклад и другие факторы, прежде всего LWLocks, которые не отслеживаются напрямую vmstat.

Итог по разделу "4. СРАВНЕНИЕ ДИАГРАММ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID"

Усложнение структуры отказов: В инциденте к проблемам с Extension и transactionid (блокировки строк) добавились серьезные проблемы с:

• Конкуренцией за буферный кэш (LWLock: BufferMapping).

• Физическими чтениями данных (IO: DataFileRead).

• Синхронной записью WAL (IO: WALSync).

• Блокировками версий строк (Lock: tuple).

Концентрация нагрузки: Основные проблемные запросы — прежние, но теперь они создают более сложный и разнообразный профиль ожиданий. Появление новых запросов в топе по IO и Lock говорит о том, что либо начали выполняться новые, тяжелые операции, либо изменился план выполнения старых запросов.

Проблемы СУБД

1. Критическая конкуренция за буферный кэш (LWLock: BufferMapping): Является основной причиной падения скорости в инциденте. Процессы проводят время в очередях за доступом к страницам в shared_buffers.

2. Деградация работы расширений (Extension): Расширения стали работать иначе, вызывая всплески прерываний и нагружая CPU, что усугубляет общую ситуацию. Главные виновники — запросы -503898190... и -428029360....

3. Рост физических чтений с диска (IO: DataFileRead): Буферный кэш перестал эффективно кэшировать данные, что привело к падению производительности и росту IO-ожиданий.

4. Появление синхронных проблем с WAL (IO: WALSync): Указывает на то, что запись в WAL начала тормозить транзакции, вероятно, из-за исчерпания пропускной способности диска с WAL или из-за конкурентной записи.

5. Расширение спектра блокировок (Lock: tuple): К проблемам с блокировками транзакций (transactionid) добавились блокировки отдельных версий строк (tuple), что говорит о высоком конкурентном доступе к одному и тому же набору данных.

Проблемы инфраструктуры

1. Неэффективная утилизация CPU: Процессорное время уходит не на полезную работу, а на оверхед (прерывания, ожидания). Это подтверждается падением cpu_id при падении скорости БД.

2. Рост прерываний: Увеличение корреляции Extension с in (прерывания) указывает на возросшую нагрузку на ядро ОС.

3. Косвенные признаки дисковой проблемы: Хотя прямые метрики wa и b улучшились, появление IO: DataFileRead и WALSync в топе ожиданий СУБД говорит о том, что дисковая система перестала справляться с пиковыми нагрузками, даже если это не видно на уровне усредненных метрик vmstat.

4. Острый дефицит свободной памяти: В обоих периодах свободной RAM менее 5% (ALARM). Система постоянно работает на грани нехватки оперативной памяти, что увеличивает вероятность вытеснения страниц и усиливает конкуренцию за буферный кэш. Это фоновый хронический фактор, который, вероятно, и спровоцировал инцидент при изменении паттерна нагрузки.

2. Рекомендации по итогам анализа инцидента производительности СУБД и инфраструктуры 2026-03-11 14:35 - 16:35

Рекомендации по оптимизации СУБД

1. Оптимизировать запросы-лидеры:

Провести детальный разбор планов выполнения для queryid:

• -5038981907002478858

• -4280293605113329019

• -1757223094415174739

• 7667106468890705260

• Выявить причины конкуренции за BufferMapping (частое чтение разных страниц) и LockManager.

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

2. Проанализировать и оптимизировать работу расширений:

• Определить, какие расширения (из списка shared_preload_libraries) вызывают наибольшие ожидания и прерывания.

• Проверить конфигурацию расширений (особенно pgpro_stats, pg_wait_sampling, dblink, postgres_fdw).

• Если расширения выполняют внешние вызовы (через dblink или fdw), оценить сетевые задержки и возможность кэширования результатов.

• Рассмотреть возможность отключения или замены неэффективных расширений.

3. Настроить параметры памяти для снижения конкуренции:

• shared_buffers (сейчас 251807 MB ~ 245 GB). Учитывая 1 TB RAM и нехватку свободной памяти, значение уже велико. Возможно, требуется уменьшение для освобождения памяти ОС под кэш файловой системы. Либо наоборот, увеличение не даст эффекта, так как конкуренция за буферы может возрасти. Рекомендуется провести тестирование с значениями 20-25% RAM.

• work_mem (1 GB) – очень высокое значение. Оно может приводить к быстрому исчерпанию памяти при большом числе сортировок/хешей. Уменьшить до разумных пределов (например, 32-128 MB) и контролировать использование через log_temp_files.

• maintenance_work_mem (16 GB) – для VACUUM и CREATE INDEX. Можно оставить, но следить за параллельными операциями.

• effective_cache_size (747230 MB) – завышено, но не критично. Уменьшить до 80-90% от RAM.

4. Снизить нагрузку на WAL и контрольные точки:

• Проверить, оправдано ли значение synchronous_commit = remote_write. Для особо критичных транзакций можно оставить, для остальных рассмотреть off или local.

• Увеличить max_wal_size (сейчас 8 GB) для уменьшения частоты контрольных точек. Рекомендуется 16-32 GB, учитывая интенсивность записи.

• Оптимизировать checkpoint_timeout (15 мин) и checkpoint_completion_target (0.9) для растягивания записи "грязных" страниц.

5. Анализ блокировок:

• Включить сбор статистики по блокировкам (track_wait_timing и pg_locks).

• Искать длинные транзакции и неоптимальные уровни изоляции (особенно Serializable, если используется).

• Для уменьшения блокировок tuple рассмотреть возможность использования индексов, покрывающих запросы, и оптимизации планов, избегающих последовательного сканирования.

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

1. Увеличить объем оперативной памяти:

• Текущая нехватка свободной RAM (<5%) – критический фактор. Добавление памяти (если возможно) или перераспределение ресурсов ВМ даст наибольший эффект, снизив давление на буферный кэш и позволив увеличить shared_buffers или файловый кэш ОС.

2. Оптимизировать дисковую подсистему:

• Для WAL: Перенести WAL на отдельный высокоскоростной диск (NVMe) с низкой задержкой. Текущий /wal на LVM поверх vdg (вероятно, HDD) не справляется с синхронной записью (WALSync).

• Для данных: Оценить производительность тома /data (56 ТБ на нескольких дисках). Возможно, требуется увеличение количества дисков в LVM для распараллеливания IO, либо использование более быстрых накопителей.

• Настроить планировщик ввода-вывода в ОС (например, none для NVMe, deadline или mq-deadline для HDD).

3. Настроить параметры ядра ОС:

• vm.swappiness: Уменьшить (например, до 10), чтобы избежать лишнего свопирования, хотя свопинг не используется, но параметр влияет на склонность к вытеснению страниц.

• vm.dirty_ratio / vm.dirty_background_ratio: Уменьшить для контроля фоновой записи и предотвращения накопления грязных страниц.

• Сетевые настройки: Увеличить буферы сокетов, если расширения активно используют сеть.

• Параметры планировщика задач: Убедиться, что процессы PostgreSQL закреплены за ядрами CPU (нужно проверить распределение по NUMA-узлам). На мощной машине с 4 сокетами важно избегать перекосов памяти.

4. Профилирование CPU и прерываний:

• Использовать perf, mpstat -I для выяснения источника роста прерываний, связанных с расширениями.

• Проверить драйверы сетевых карт и настройки очередей прерываний (RSS, RPS). Возможно, требуется настройка affinity прерываний для распределения нагрузки.

5. Усилить мониторинг:

• Настроить оповещения по достижению порогов: свободная память <10%, рост procs b > 1-2, увеличение времени ожидания WALSync.

• Собирать данные iostat для дисков с данными и WAL для выявления пиков latency.

6. Рассмотреть возможность контейнеризации/виртуализации:

• Если сервер виртуальный (KVM), проверить, не происходит ли конкуренция с соседними ВМ за дисковые ресурсы. Обратиться к администратору гипервизора.

3. Сводный сравнительный отчет по подсистеме IO 2026-03-11 14:35 - 16:35.

Список дисковых устройств

Анализ проводился для следующих дисковых устройств, входящих в состав LVM для хранения данных СУБД и WAL:

• vdg — физический диск, используемый для WAL (/wal) и раздела подкачки.

• vdh, vdi, vdj, vdk — физические диски, объединенные в LVM-том /data для хранения данных СУБД.

Сравнительный анализ граничных значений по дисковым устройствам

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

Устройство vdg (WAL):

• Тест: Нагрузка на запись (w/s, wMB/s) стабильно низкая. Операции чтения практически отсутствуют. Утилизация устройства (device_util) минимальна (медиана ~5.9%).

• Инцидент: Резкий рост нагрузки на запись. Медиана w/s выросла с ~373 до ~689, а wMB/s — с ~7.75 до ~16.0. Несмотря на рост, утилизация устройства остается низкой (медиана ~10.2%), что говорит о его высокой производительности и малом влиянии на общую картину.

Устройства данных vdh, vdi, vdj, vdk:

Тест: На всех четырех дисках наблюдается высокая и стабильная смешанная нагрузка. Медианные значения составляют:

• r/s: ~12 200

• w/s: ~500

• Утилизация (device_util): ~87%

• Глубина очереди (aqu-sz): ~2.0

Инцидент: Нагрузка на диски данных значительно возрастает.

• r/s: медиана увеличивается до ~13 500.

• w/s: медиана удваивается, достигая ~1 100.

• wMB/s: медиана возрастает с ~4.8 до ~10.5.

• Утилизация остается на критическом уровне (медиана ~90%).

• Глубина очереди незначительно растет (медиана ~2.5).

Вывод: В период инцидента произошло существенное увеличение нагрузки на диски данных (vdh-vdk), особенно на операции записи (рост в 2 раза). Диск WAL (vdg) также испытал рост нагрузки на запись, но остался далек от насыщения.

Итог по разделу "СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ КОРРЕЛЯЦИЯ VMSTAT и IOSTAT по дисковым устройствам"

В тестовом периоде наблюдалась высокая нагрузка на диски данных, но механизмы кэширования и буферизации работали иначе. В период инцидента характер взаимосвязей изменился: главным фактором стало прямое влияние загрузки диска на ожидание процессов (wa). Ключевым ограничивающим фактором стала пропускная способность дисков, о чем свидетельствует появление сильных корреляций с метриками MBps.

Проблемы инфраструктуры по итогам сравнительного анализа

1. Хроническая перегрузка дисков данных: Диски vdh, vdi, vdj, vdk постоянно работают с утилизацией более 80-90% и глубиной очереди более 2, что является критическим состоянием.

2. Усугубление проблемы в период инцидента: На фоне и без того высокой нагрузки произошел скачок операций записи (в 2 раза), что превратило пропускную способность дисков в главное узкое место.

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

4. Рост зависимости от кэша: Увеличение корреляции кэша с дисковыми операциями в период инцидента указывает на то, что система пытается компенсировать недостаток дисковой производительности за счет памяти, но это не решает проблему полностью.

5. Отсутствие запаса пропускной способности: Система данных СУБД уперлась в физический предел скорости чтения/записи дисков.

Рекомендации по инфраструктуре по итогам сравнительного анализа

1. Анализ характера нагрузки: Определить, какие запросы/процессы генерируют столь высокий уровень операций записи (w/s и wMB/s) на дисках данных. Рассмотреть возможность их оптимизации, дедупликации или переноса.

2. Масштабирование дисковой подсистемы:

• Увеличение количества дисков: Добавить еще физических дисков в LVM-пул /data для распределения нагрузки и увеличения суммарной пропускной способности и IOPS.

• Апгрейд на более производительные диски: Рассмотреть замену текущих дисков на более скоростные модели (например, NVMe), если текущая конфигурация не позволяет обеспечить требуемую пропускную способность.

3. Тюнинг параметров СУБД: Пересмотреть настройки, влияющие на использование буферов и кэша при высоких нагрузках на запись.

• Проанализировать адекватность размера shared_buffers (установлен в 251 ГБ) и work_mem (1 ГБ) для текущей рабочей нагрузки.

• Проверить эффективность настроек контрольной точки (checkpoint_timeout, max_wal_size), так как рост записи на диски данных может быть связан с сбросом "грязных" страниц на диск.

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

Общий итог

Проведенный сравнительный анализ производительности СУБД PostgreSQL в периоды штатной работы и развития инцидента позволил выявить комплексный характер деградации системы. Установлено, что непосредственной причиной падения операционной скорости стала критическая конкуренция за буферный кэш (LWLock: BufferMapping), усугубленная изменением паттернов работы расширений, ростом физических чтений с диска и появлением синхронных ожиданий записи WAL. Хронический дефицит оперативной памяти и перегрузка дискового массива данных (утилизация более 90%) создали благоприятные условия для развития инцидента, переведя фокус нагрузки с подсистемы ввода-вывода на механизмы блокировок и процессорное время, что подтверждается изменением корреляционных связей между метриками СУБД и инфраструктуры.

Представленный пример практического применения инструментария pg_expecto наглядно демонстрирует его эффективность не только в задачах нагрузочного тестирования, но и при анализе производительности высоконагруженных продуктивных систем. Возможность проведения сравнительного статистического анализа, трендового моделирования и корреляции метрик СУБД с показателями операционной среды позволяет перейти от простой констатации факта падения скорости к выявлению первопричин деградации. Использование pg_expecto в данном исследовании обеспечило формирование научно обоснованных, верифицированных рекомендаций по оптимизации, что подтверждает его ценность как инструмента глубокой диагностики и постмортем-анализа для промышленных инсталляций PostgreSQL.

В современном мире администрирования баз данных всё большую популярность приобретают автоматические конфигураторы — инструменты, которые обещают сгенерировать оптимальный набор параметров СУБД на основе минимальных входных данных: объёма оперативной памяти, количества ядер процессора, предполагаемого типа нагрузки и версии PostgreSQL. «Тантор Лабс», pgpro_tune, PgTune и подобные решения стали незаменимыми помощниками для быстрого старта. Однако, как показывает практика, доверять таким конфигураторам безоговорочно нельзя. Они задают лишь отправную точку, но не гарантируют эффективной работы под реальной нагрузкой. Недавнее сравнительное тестирование, проведённое с участием инструмента pg_expecto и нейросети DeepSeek, наглядно продемонстрировало ограничения подобных генераторов и риски, связанные с их использованием.

В эксперименте участвовала система с 8 ядрами CPU и 8 ГБ RAM под управлением PostgreSQL 17. Исходная конфигурация была подготовлена конфигуратором «Тантор Лабс» и содержала, на первый взгляд, разумные значения: shared_buffers = 1779 МБ, max_wal_size = 2021 МБ, checkpoint_timeout = 15 мин и ряд других параметров. Однако при нагрузочном тестировании по сценарию OLTP система сразу же попала в состояние «серьёзного кризиса производительности». Наблюдалась постоянная нехватка оперативной памяти, более 10% времени процессор простаивал в ожидании ввода-вывода (iowait), а количество процессов в состоянии uninterruptible sleep (b) превышало число ядер CPU. Медианная скорость выполнения транзакций составляла около 384 тысяч операций в минуту, а ожидания ввода-вывода достигали 226 тысяч. Основным источником проблем оказался один и тот же запрос scenario1, генерировавший 85% всех событий ожидания DataFileRead.

Почему же конфигуратор, зная объём памяти и тип нагрузки, не смог предотвратить этот кризис? Ответ кроется в самой природе таких инструментов. Они оперируют усреднёнными шаблонами и эвристиками, основанными на общих рекомендациях из документации и типовых профилях нагрузки. Например, shared_buffers обычно устанавливают в 20–25% от RAM, но конкретное значение зависит от множества факторов: характера запросов, размера рабочих наборов данных, активности записи. В данном случае 1779 МБ оказалось недостаточно для кэширования часто используемых данных, что и привело к постоянным обращениям к диску. Конфигуратор не мог предвидеть, что конкретный запрос будет потреблять огромное количество операций ввода-вывода, и что даже такой объём shared_buffers не спасёт от дефицита памяти. Кроме того, выбранные параметры контрольных точек (checkpoint_timeout = 15 мин, max_wal_size = 2 ГБ) оказались слишком агрессивными для реальной интенсивности записи, вызывая частые сбросы грязных страниц и пиковые нагрузки на диск.

После анализа ситуации с помощью pg_expecto и DeepSeek были предложены изменения, которые увеличили медианную скорость на 40,6%: shared_buffers подняли до 3 ГБ, max_wal_size — до 5 ГБ, checkpoint_timeout — до 30 минут, а также скорректировали параметры bgwriter и vm.dirty_*. Эти изменения сгладили пики записи, улучшили попадание в кэш (hit ratio вырос с 96,64% до 96,95%), но принципиально не решили проблему: дефицит памяти остался (доступная RAM упала с 4,9 до 3,5 ГБ), iowait и очередь заблокированных процессов сохранились на прежнем уровне, а запрос-виновник по-прежнему генерировал 85% всех ожиданий. То есть даже оптимизированная конфигурация не смогла устранить корневую причину, которая лежит за пределами настроек СУБД — в логике конкретного запроса и аппаратных ограничениях.

Этот случай высвечивает фундаментальные ограничения конфигураторов:

1. Отсутствие учёта реальной рабочей нагрузки. Конфигуратор не знает, какие запросы будут выполняться, как они используют индексы, какие объёмы данных сканируются. Он оперирует обобщёнными типами (OLTP, OLAP), но внутри одного типа может быть огромное разнообразие паттернов доступа.

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

3. Игнорирование взаимодействия с операционной системой. Конфигураторы редко учитывают настройки ядра Linux (vm.dirty_ratio, dirty_background_ratio и т.п.), хотя они критически влияют на производительность ввода-вывода. В эксперименте именно корректировка этих параметров дала часть прироста.

4. Невозможность выявить проблемные запросы. Ни один конфигуратор не проанализирует, что конкретный запрос scenario1 потребляет 85% ресурсов. Это задача мониторинга и профилирования, которые должны проводиться отдельно.

5. Отсутствие итеративности. Конфигуратор выдаёт готовый файл, но не предлагает цикл «измерение-настройка-измерение», который необходим для тонкой подстройки под конкретное железо и нагрузку.

Аналогичные претензии можно предъявить и к pgpro_tune, популярному инструменту от Postgres Professional. Он также генерирует конфигурацию на основе анкеты, но не учитывает многие тонкости: например, не анализирует статистику по индексам, не видит реальные очереди ожидания, не предлагает вариантов для разных подсистем хранения (HDD vs SSD). В результате пользователь получает «среднюю температуру по больнице», которая может быть далека от идеала.

Вывод из проведённого эксперимента очевиден: конфигураторы полезны как стартовая площадка, но полагаться только на них при построении высоконагруженных систем опасно. Они не заменяют экспертного анализа, нагрузочного тестирования и постоянного мониторинга. Необходимо использовать инструменты, подобные pg_expecto, которые позволяют проводить корреляционный анализ метрик СУБД и ОС, выявлять узкие места, итеративно менять параметры и оценивать эффект. Только такой подход способен адаптировать систему к реальной нагрузке и, как в этом примере, дать прирост в 40% без замены оборудования. В ближайших экспериментах планируется сравнить эффективность различных конфигураторов, включая pgpro_tune, чтобы на практике показать, насколько их рекомендации соответствуют реальности и какие дополнительные усилия требуются для достижения максимальной производительности.

В статье представлен практический кейс оптимизации PostgreSQL 17 на сервере с 8 ядрами CPU и 8 ГБ RAM. Исходная конфигурация, сгенерированная конфигуратором «Тантор Лабс», при нагрузочном тестировании продемонстрировала серьёзный кризис производительности из-за дефицита памяти и перегрузки дисковой подсистемы. С помощью инструмента pg_expecto и нейросети DeepSeek был проведён всесторонний анализ, выявлены узкие места и выполнена тонкая настройка параметров СУБД и ОС. Результат — прирост операционной скорости на 40% без модернизации оборудования. В материале — детали эксперимента, сравнение конфигураций и выводы о дальнейших шагах.

Задача

Проанализировать и оптимизировать конфигурацию СУБД подготовленную с помощью инструмента "Конфигуратор критически важных параметров производительности PostgreSQL от компании «Тантор Лабс»"

Входные данные для конфигуратора

CPU = 8

RAM = 8GB

Тип нагрузки = OLTP

Платформа = Linux

Версия PostgreSQL = 17

Нагрузка по сценариям в ходе нагрузочного тестирования

Производительность СУБД с базовыми параметрами

Формирование отчета по итогам нагрузочного тестирования

cd /postgres/pg_expecto/sh/performance_reports

./load_test_report.sh

Входные файлы, используемые для анализа с помощью нейросети

• _1.settings.txt: НАСТРОЙКИ СУБД и VM

• _2.postgresql_vmstat.txt: КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ СУБД и VMSTAT

• _3.1.prompt.txt: промпт для нейросети DeepSeek

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

Рекомендации по настройкам СУБД

• Увеличить shared_buffers (до 2–2.5 GB, учитывая 7.5 GB RAM), чтобы больше данных помещалось в кэш.

• Увеличить max_wal_size (например, до 4–5 GB), чтобы сгладить пики записи.

• Увеличить checkpoint_timeout (до 20–30 мин) .

• bgwriter_delay – увеличить с 54 мс до 100 мс.

• bgwriter_lru_maxpages – увеличить с 515 до 1000.

• bgwriter_lru_multiplier – рекомендуется снизить с 7.0 до 4.0–5.0.

Рекомендации по настройкам операционной системы

• Уменьшить vm.dirty_background_ratio до 2–3% (сейчас 10%).

• Уменьшить vm.dirty_ratio до 10–15% (сейчас 30%).

• Уменьшить vm.dirty_writeback_centisecs до 200–300 (сейчас 500) для более частого пробуждения flusher-нитей

...

Заключение

Система находится в состоянии серьёзного кризиса производительности, вызванного недостатком оперативной памяти и перегрузкой дисковой подсистемы. Основной вклад вносит один проблемный запрос, генерирующий огромное количество операций ввода-вывода. Тренды указывают на быстрое ухудшение ситуации.

Критические точки:

• Постоянная нехватка RAM.

• Iowait >10% и блокировки процессов (b) > числа ядер CPU.

• Неэффективная работа механизмов dirty page writeback на уровне ОС.

Результат применения рекомендованных изменений

Подготовленные входные файлы, используемые для анализа с помощью нейросети

• baseline.settings.txt: БАЗОВЫЕ НАСТРОЙКИ("Тантор Лабс"-pgconfigurator) НАСТРОЙКИ СУБД и VM ( файл _1.settings.txt по результатам нагрузочного тестирования с базовыми настройками)

• baseline.postgresql_vmstat.txt: БАЗОВЫЕ НАСТРОЙКИ("Тантор Лабс"-pgconfigurator) КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ СУБД и VMSTAT ( файл _2.postgresql_vmstat.txt по результатам нагрузочного тестирования с базовыми настройками)

• advice.settings.txt: ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek) НАСТРОЙКИ СУБД и VM ( файл _1.settings.txt по результатам нагрузочного тестирования с рекомендованными изменениями)

• advice.postgresql_vmstat.txt: ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek) КОМПЛЕКСНЫЙ КОРРЕЛЯЦИОННЫЙ АНАЛИЗ СУБД и VMSTAT ( файл _2.postgresql_vmstat.txt по результатам нагрузочного тестирования с рекомендованными изменениями)

• advice.txt: ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek) (применение рекомендованных изменений)

• _3.3.prompt.diff.txt (промпт для нейросети DeepSeek )

Сравнительный анализ результатов нагрузочного тестирования подготовленный нейросетью

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

Эксперимент‑1 (базовый) – настройки, сгенерированные «Тантор Лабс» (pgconfigurator).
Период наблюдения: 2026‑03‑05 18:09 – 20:31 (2 ч 22 мин, 143 точки).

Эксперимент‑2 (оптимизированный) – настройки, рекомендованные PG_EXPECTO + DeepSeek.
Период наблюдения: 2026‑03‑06 10:48 – 13:10 (2 ч 22 мин, 143 точки).

Аппаратная платформа (одинакова для обоих экспериментов):

• CPU: 8 ядер (Intel Xeon Skylake, виртуализация KVM)

• RAM: 7,5 GB

• Диски: LVM, отдельные тома для /data (pg_data), /wal, /log

Сравнение конфигурации СУБД и операционной системы

Изменённые параметры PostgreSQL

• shared_buffers
  Эксперимент‑1: 1779 MB
  Эксперимент‑2: 3 GB

• max_wal_size
  Эксперимент‑1: 2021 MB
  Эксперимент‑2: 5 GB

• checkpoint_timeout
  Эксперимент‑1: 15 min
  Эксперимент‑2: 30 min

• bgwriter_delay
  Эксперимент‑1: 54 ms
  Эксперимент‑2: 100 ms

• bgwriter_lru_maxpages
  Эксперимент‑1: 515
  Эксперимент‑2: 1000

• bgwriter_lru_multiplier
  Эксперимент‑1: 7,0
  Эксперимент‑2: 4,0

Изменённые параметры ядра (vm)

• vm.dirty_background_ratio
  Эксперимент‑1: 10 %
  Эксперимент‑2: 2 %

• vm.dirty_ratio
  Эксперимент‑1: 30 %
  Эксперимент‑2: 10 %

• vm.dirty_writeback_centisecs
  Эксперимент‑1: 500
  Эксперимент‑2: 200

Остальные настройки (work_mem, maintenance_work_mem, wal‑уровни, автовакуум, etc.) остались без изменений.

Общий анализ операционной скорости и ожиданий СУБД

Сравнительный анализ граничных значений операционной скорости (SPEED) и ожиданий СУБД (WAITINGS)

SPEED :

• Эксперимент‑1: минимум 375 955, медиана 383 994, максимум 683 866.

• Эксперимент‑2: минимум 472 713, медиана 539 761, максимум 749 919.

Медианная скорость выросла на 40,6 %.

WAITINGS :

• Эксперимент‑1: минимум 48 536, медиана 78 011, максимум 226 349.

• Эксперимент‑2: минимум 47 168, медиана 76 311, максимум 254 309.

Медианные ожидания снизились на 2,2 %, хотя максимальные выросли – возможно, из‑за более интенсивной нагрузки.

Сравнительный анализ трендов операционной скорости и ожиданий СУБД

• Тренд SPEED
  Эксперимент‑1: R² = 0,77 (хорошая модель), угол наклона +41,33
  Эксперимент‑2: R² = 0,89 (очень высокая), угол наклона +43,29
  В обоих случаях скорость растёт, во втором эксперименте тренд чуть круче.

• Тренд WAITINGS
  Эксперимент‑1: R² = 0,87 (очень высокая), угол +43,01
  Эксперимент‑2: R² = 0,86 (очень высокая), угол +42,90
  Ожидания также растут синхронно со скоростью.

• Регрессия SPEED по WAITINGS
  Эксперимент‑1: R² = 0,97, угол +44,56
  Эксперимент‑2: R² = 0,99, угол +44,84
  Связь исключительно сильная, почти линейная.

1. Сравнительный статистический анализ ожиданий СУБД

В обоих экспериментах доминирующий тип ожиданий – IO:

• Интегральный приоритет IO = 0,6271.

• Взвешенная корреляция ожиданий (ВКО) для IO = 1,0 (критическое значение).

• Остальные типы (IPC, Lock, LWLock, Timeout) имеют статистически значимые корреляции, но ВКО < 0,01 – игнорируются.

Итог по разделу

Основной источник задержек – операции ввода‑вывода. Прочие типы ожиданий не оказывают заметного влияния на общую нагрузку.

2. Сравнительный трендовый анализ производительности vmstat

Ключевые метрики (медианы за период)

• procs → r (процессы в очереди на выполнение)
  Эксперимент‑1: 3 (диапазон 2–4)
  Эксперимент‑2: 3 (диапазон 2–5)
  Характер тренда: негативный, R² ~0,75 (высокая скорость ухудшения)

• procs → b (процессы в uninterruptible sleep)
  Эксперимент‑1: 14 (9–40)
  Эксперимент‑2: 14 (9–40)
  Характер тренда: негативный, R² ~0,86 (очень высокая скорость)

• cpu → wa (процент простоя CPU в ожидании IO)
  Эксперимент‑1: 62 % (58–67)
  Эксперимент‑2: 61 % (54–63)
  Характер тренда: статистически незначим

• cpu → id (процент полного простоя CPU)
  Эксперимент‑1: 14 % (2–19)
  Эксперимент‑2: 12 % (1–18)
  Характер тренда: негативный, R² ~0,93 (резкое падение idle)

Негативные тренды по очередям выполнения (r) и блокированным процессам (b) сохраняются, процессорное время простоя (id) быстро сокращается.

Итог по разделу

Система испытывает хроническую перегрузку по вводу‑выводу: более 50 % времени wa > 10 %, очередь процессов в состоянии b постоянно превышает количество ядер CPU.

3. Сравнительный статистический анализ ожиданий СУБД и метрик vmstat

Связь IO с чтением (bi) и записью (bo)

• IO ↔ bi
  Эксперимент‑1: корреляция 0,55, R² = 0,31 (слабая модель)
  Эксперимент‑2: корреляция 0,82, R² = 0,67 (хорошая модель)
  После оптимизации чтение с диска стало гораздо сильнее влиять на IO‑ожидания.

• IO ↔ bo
  Эксперимент‑1: корреляция 0,85, R² = 0,72 (хорошая)
  Эксперимент‑2: корреляция 0,75, R² = 0,56 (удовлетворительная)
  Влияние записи немного ослабло – возможно, благодаря увеличению max_wal_size и checkpoint_timeout.

Анализ переключений контекста (cs)

• cs ↔ in (прерывания): корреляция ~0,996, R² = 0,99

• cs ↔ us (user time): корреляция ~0,99, R² = 0,99

• cs ↔ sy (system time): корреляция ~0,97, R² ~0,95
  Во всех случаях связь исключительно сильная. Высокая интенсивность переключений контекста и прерываний сохраняется.

Влияние на буферный кэш

• Shared buffers hit ratio
  Эксперимент‑1: медиана 96,64 % (min 96,09, max 98,36)
  Эксперимент‑2: медиана 96,95 % (min 96,48, max 98,47)
  Небольшое улучшение попаданий в кэш.

• Скорость ↔ прочитанные блоки
  Эксперимент‑1: корреляция 0,58, R² = 0,34 (слабая)
  Эксперимент‑2: корреляция несущественна
  После увеличения shared_buffers скорость перестала зависеть от объёма чтения с диска.

Грязные страницы (dirty pages)

• Размер dirty pages (KB): медиана снизилась с 7126 до 6972, максимум с 18146 до 15672.

• dirty pages ↔ b (процессы в uninterruptible sleep): корреляция ~0,992, R² = 0,98 (чрезвычайно сильная) – проблема сохраняется.

• dirty pages ↔ bo: корреляция снизилась с 0,86 до 0,79, R² с 0,74 до 0,62 – механизм обратной записи стал немного эффективнее.

• dirty pages ↔ sy: корреляция ~0,975, R² = 0,95 – высокие накладные расходы ядра на управление памятью.

Доступная память

• available_mem_mb: медиана упала с 4883 MB до 3528 MB (из‑за увеличения shared_buffers). Доля времени со свободной RAM <5 % по‑прежнему 100 %.

Итог по разделу

Увеличение shared_buffers и настройка контрольных точек позволили улучшить попадания в кэш и немного сгладить пики записи, однако фундаментальные проблемы с памятью (дефицит свободной RAM) и перегрузкой ввода‑вывода (процессы в состоянии b, высокий wa) остаются.

4. Сравнение диаграмм Парето по wait_event_type и queryid

Типы ожиданий (wait_event_type)

• Эксперимент‑1: IO (DataFileRead) – 14 159 430 событий, 99,84 %

• Эксперимент‑2: IO (DataFileRead) – 13 790 683 события, 99,94 %
  Абсолютное число событий IO немного снизилось, их доля осталась подавляющей.

Запросы (queryid)

• Эксперимент‑1: запрос select scenario1() (ID 8275902800498673318) – 85,04 % всех ожиданий.

• Эксперимент‑2: запрос select scenario1() (ID 6751319641472471819) – 85,03 % всех ожиданий.
  Основным потребителем ресурсов остаётся один и тот же сценарий (scenario1).

Итог по разделу

Узкое место не изменилось – это по‑прежнему чтение данных с диска (DataFileRead), генерируемое запросом scenario1. Оптимизация не затронула логику самого запроса.

Детальный анализ – граничные значения и корреляции

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

• IO – единственный значимый тип (ВКО = 1,0).

• IPC, Lock, LWLock, Timeout – корреляции есть, но вклад в общую нагрузку ничтожен (ВКО < 0,01).

Память и буферный кэш

• shared_buffers увеличены с 1,8 GB до 3 GB.

• hit ratio вырос на 0,3 п.п. (медиана 96,95 %).

• Доступная память снизилась на ~1,4 GB, дефицит свободной RAM сохраняется (100 % времени <5 %).

• Грязные страницы: объём немного уменьшился, но их влияние на блокировку процессов (b) осталось критическим.

Дисковая подсистема (I/O)

• Чтение (bi) стало значительно сильнее коррелировать с ожиданиями IO (R² вырос с 0,31 до 0,67).

• Запись (bo) коррелирует чуть слабее, чем раньше, что может указывать на более плавную запись благодаря увеличенному max_wal_size.

• Процессы в состоянии b по‑прежнему сильно зависят от объёма грязных страниц (r² = 0,98).

• Процент времени с wa >10 % – 100 % в обоих экспериментах.

CPU и системные вызовы

• Переключения контекста (cs) тесно связаны с прерываниями (in) и user/system time – все корреляции >0,97.

• System time (sy) сильно коррелирует с размером грязных страниц (r² = 0,95) – ядро тратит много ресурсов на управление памятью.

• Процессорное время простоя (id) продолжает падать (негативный тренд, R² >0,9).

Блокировки и ожидания LWLock

• Корреляции для Locks и LWLocks статистически значимы, но их вес в общей структуре ожиданий ничтожен (ВКО < 0,01).

• Основная блокировка – ожидание IO, а не внутренние блокировки PostgreSQL.

Анализ запросов (queryid)

• Запрос scenario1 генерирует 85 % всех ожиданий (DataFileRead).

• Остальные запросы (scenario2, scenario3) вносят пренебрежимо малый вклад.

Ключевые проблемы для экспериментов

Эксперимент‑1 («Тантор Лабс»)

Проблемы СУБД

• Недостаточный размер shared_buffers (1,8 GB) – низкая эффективность кэширования.

• Слишком частые контрольные точки (checkpoint_timeout = 15 мин, max_wal_size = 2 GB) – пиковые нагрузки на запись.

• Агрессивная работа bgwriter (bgwriter_lru_multiplier = 7) могла вызывать лишние сбросы.

Проблемы инфраструктуры

• Свободной памяти <5 % в течение всего теста – риск вытеснения страниц.

• Высокий процент IO‑wait (wa >10 % всегда) и очередь процессов в состоянии b (превышение ядер CPU 100 % времени).

• Грязные страницы накапливаются до 18 MB, вызывая блокировки процессов.

• Высокие накладные расходы ядра (sy) из‑за управления памятью.

Эксперимент‑2 (PG_EXPECTO + DeepSeek)

Проблемы СУБД

• Основной запрос scenario1 не оптимизирован – по‑прежнему 85 % ожиданий приходится на чтение с диска.

• Несмотря на увеличение shared_buffers, hit ratio остаётся на уровне 97 %, что недостаточно для полного устранения чтений.

Проблемы инфраструктуры

• Дефицит свободной памяти усугубился (доступно ~3,5 GB вместо ~4,9 GB) – плата за увеличение shared_buffers.

• IO‑wait и блокировки процессов (b) остались на прежнем критическом уровне.

• Системные вызовы и переключения контекста по‑прежнему крайне высоки.

Итоговый анализ влияния мероприятий

1. Производительность (скорость) выросла на 40 % – это главный положительный эффект.
Увеличение shared_buffers, размера WAL и интервала контрольных точек позволило эффективнее использовать кэш и сгладить пики записи.

2. Нагрузка на ввод‑вывод частично перераспределилась

• Корреляция IO с чтением (bi) стала сильнее – теперь чтения из файлов данных лучше отражаются в ожиданиях.

• Корреляция с записью (bo) немного ослабла – запись стала более равномерной.

3. Проблемы операционной системы не устранены

• Свободная память уменьшилась, и её дефицит сохраняется.

• Процессы в состоянии b и высокий IO‑wait остались на прежнем уровне.

• Переключения контекста и нагрузка на ядро не снизились.

4. Запрос‑виновник не оптимизирован
Более 85 % ожиданий по‑прежнему приходятся на select scenario1(). Без изменений в логике запроса или добавления индексов дальнейший рост скорости ограничен.

Общий вывод:

1. Проведённая оптимизация дала значительный прирост производительности (медианная скорость +40,6 %) за счёт более эффективного использования памяти и сглаживания записи.

2. Однако инфраструктурные ограничения (нехватка RAM, медленная дисковая подсистема) и неоптимизированный запрос остаются «узкими горлами».

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

Послесловие

Проведённый эксперимент наглядно демонстрирует, что даже значительный прирост производительности (медианная скорость +40 %) не является пределом, если остаются нерешёнными фундаментальные проблемы — неоптимизированный запрос и аппаратные ограничения. Тандем pg_expecto и DeepSeek позволил не только улучшить конфигурацию, но и точно локализовать корневые причины торможений. Однако поиск эталонной стратегии настройки PostgreSQL продолжается: в ближайшее время планируется серия аналогичных экспериментов, где в качестве отправной точки будут использоваться другие популярные инструменты генерации базовой конфигурации — PgTune, онлайн-калькуляторы от различных вендоров, а также рекомендательные системы на основе машинного обучения. Цель — сравнить их эффективность под нагрузкой и понять, какой подход даёт наилучший «фундамент» для последующей тонкой оптимизации.