PostgreSQL 17: от pgpro_tune до DeepSeek-оптимизации через PG_EXPECTO.

pg_expecto показал: pgpro_tune был только началом

DeepSeek увидел то, что скрывал pgpro_tune

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

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

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

Оптимизация базовой производительности СУБД(конфигуратор "Тантор Лабс "):

Введение

В данном исследовании представлен комплексный подход к оптимизации производительности СУБД PostgreSQL 17, совмещающий автоматическую начальную настройку с помощью утилиты pgpro_tune и последующий углублённый статистический анализ инструментом pg_expecto, интерпретированный нейросетью DeepSeek. В ходе эксперимента, проведённого на оборудовании с 8 ядрами CPU и 8 ГБ RAM под нагрузкой OLTP, сравниваются два состояния системы: baseline-конфигурация, сгенерированная pgpro_tune, и модифицированный вариант, основанный на рекомендациях ИИ. Результаты демонстрируют, что даже при среднем приросте операционной скорости в 22% ключевые проблемы производительности (доминирование IO-ожиданий и дефицит памяти) не только сохранились, но и усугубились в новых условиях, что подчёркивает необходимость выхода за рамки параметрической настройки и перехода к оптимизации запросов и аппаратных ресурсов.

Задача

Проанализировать и оптимизировать конфигурацию СУБД подготовленную с помощью инструмента "pgpro_tune — утилита командной строки для автоматической настройки"

1.Условия эксперимента

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

• CPU = 8
• RAM = 8GB
• Тип нагрузки = OLTP
• Платформа = Linux
• Версия PostgreSQL = 17

Параметры нагрузочного тестирования

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ

# Начальная нагрузка

start_load = 10

# Максимальная нагрузка

finish_load = 80

# Тестовая БД

testdb = default

# Тип синтетической нагрузки

load_mode = oltp

# Веса сценариев по умолчанию

scenario1 = 0.7

scenario2 = 0.2

scenario3 = 0.1

# Инициализировать тестовую БД

init_test_db = on

# Размер тестовой БД

#~10GB

scale = 685

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

2. Формирование baseline: конфигурация pgpro_tune

Формирование файла конфигурации с помощью pgpro_tune

$ pgpro_tune -D /data/pg_data

Результирующая конфигурация (фрагмент):

• shared_buffers: 2 ГБ (25% от RAM) Оптимально для OLTP на 8 ГБ RAM
• effective_cache_size: 6 ГБ (75% от RAM) Оценка кэша ОС + PostgreSQL
• work_mem: 10 МБ Для 200 соединений: 2 ГБ / 200 = 10 МБ
• maintenance_work_mem: 512 МБ Достаточно для VACUUM, CREATE INDEX
• max_wal_size: 4 ГБ Увеличивает интервалы checkpoint
• min_wal_size: 2 ГБ Снижает частоту сброса WAL
• checkpoint_completion_target: 0.9 Равномерное распределение I/O
• wal_buffers: 16 МБ Оптимизация записи WAL
• random_page_cost: 1.1 SSD-диски имеют близкую к последовательной скоростью чтения
• effective_io_concurrency: 200 Для SSD с высокой IOPS
• max_worker_processes: 8 Соответствует числу CPU-ядер
• max_parallel_workers_per_gather: 4 50% от ядер — оптимально для OLTP
• default_statistics_target: 100 Улучшает точность планировщика

Конфигурация СУБД, подготовленная pgpro_tune

Операционная скорость СУБД в ходе нагрузочного тестирования с использованием базовой конфигурации, подготовленной pgpro_tune

График изменения операционной скорости в ходе нагрузочного тестирования с базовыми параметрами подготовленными с помощью конфигуратора pgpro_tune

Рисунок 1. Операционная скорость PostgreSQL 17 с конфигурацией pgpro_tune (baseline).

3. Анализ статистических результатов нагрузочного тестирования, с использованием PG_EXPECTO + DeepSeek

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

cd /postgres/pg_expecto/sh/performance_reports

./load_test_report.sh

Что получил DeepSeek на вход?

• _1.settings.txt — конфигурационные параметры СУБД и ОС.

• _2.postgresql_vmstat.txt — результат комплексного корреляционного анализа метрик СУБД и vmstat за период теста.

3.2 Промпт для DeepSeek:

Промпт №1 (для анализа базовой конфигурации) — файл _3.1.prompt.txt

Сводный отчет по производительности СУБД и инфраструктуры (конфигурация pgpro_tune)

3.3 Рекомендация нейросети DeepSeek и новая конфигурация

Итоговый перечень изменений

shared_buffers

• Текущее значение: 1919 MB
• Рекомендуемое значение: 2560 MB

maintenance_work_mem

• Текущее значение: 479 MB
• Рекомендуемое значение: 1024 MB

autovacuum_work_mem

• Текущее значение: 239 MB
• Рекомендуемое значение: 512 MB

effective_io_concurrency

• Текущее значение: 200
• Рекомендуемое значение: 500

random_page_cost

• Текущее значение: 1.1
• Рекомендуемое значение: 1.1 (без изменений)

effective_cache_size (сопутствующая настройка)

• Текущее значение: 3838 MB
• Рекомендуемое значение: 5120 MB

vm.dirty_background_ratio

• Текущее значение: 10
• Рекомендуемое значение: 5

vm.dirty_ratio

• Текущее значение: 30
• Рекомендуемое значение: 15

5. Применение настроек СУБД и ОС, рекомендованных DeepSeek

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

Графики изменения операционной скорости в ходе нагрузочного тестирования , для базовых значений (baseline) и после применения рекомендованных изменений (advice)

Рисунок 2. Сравнение операционной скорости: базовая конфигурация (синий) и после оптимизации pg_expecto + DeepSeek (оранжевый). Средний прирост составил 22%.

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

6.1 Промпт для DeepSeek:

Промпт №2 (сравнительный анализ "До/После") — файл _3.3.prompt.diff.txt

6.2 Сравнительный анализ производительности СУБД базовые настройки pgpro_tune - рекомендованные DeepSeek настройки

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

• Версия PostgreSQL: 17.5 на x86_64-pc-linux-gnu
• Аппаратная платформа: 8 виртуальных CPU (Intel Xeon Skylake), RAM 7.5 ГБ, диски разделены LVM (data, wal, log на отдельных томах)
• ЭКСПЕРИМЕНТ-1 (PGPRO_TUNE): период наблюдения 2026-03-14 09:56 – 12:18
• ЭКСПЕРИМЕНТ-2 (PG_EXPECTO+DeepSeek): период наблюдения 2026-03-14 14:25 – 16:47

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

Параметры СУБД (изменённые):

shared_buffers:

• PGPRO_TUNE: 1919 MB
• PG_EXPECTO+DeepSeek: 3 GB (+57%)

effective_cache_size:

• PGPRO_TUNE: 3838 MB
• PG_EXPECTO+DeepSeek: 5120 MB (+33%)

maintenance_work_mem:

• PGPRO_TUNE: 479 MB
• PG_EXPECTO+DeepSeek: 1024 MB (+114%)

autovacuum_work_mem:

• PGPRO_TUNE: 239 MB
• PG_EXPECTO+DeepSeek: 512 MB (+114%)

effective_io_concurrency:

• PGPRO_TUNE: 200 (SSD)
• PG_EXPECTO+DeepSeek: 500 (NVMe) – соответствует фактическому оборудованию (NVMe SSD)

Остальные параметры (work_mem, max_connections, bgwriter, wal, autovacuum) совпадают.

Параметры ОС (vm.dirty_*):

vm.dirty_background_ratio:

• PGPRO_TUNE: 10
• PG_EXPECTO+DeepSeek: 5 (более ранний запуск фоновой записи)

vm.dirty_ratio:

• PGPRO_TUNE: 30
• PG_EXPECTO+DeepSeek: 15 (более ранняя синхронная блокировка)

Остальные параметры (dirty_expire_centisecs, dirty_writeback_centisecs, swappiness) совпадают.

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

• Структура ожиданий не изменилась – доминирует IO.
• Увеличение буферов и настройка ОС не повлияли на типы ожиданий; IO остаётся единственным значимым фактором.

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

• Главная проблема – рост числа процессов в состоянии «b» (ожидание IO) и падение idle CPU – осталась без изменений.
• В эксперименте 2(PG_EXPECTO+DeepSeek) исчез тренд по очереди на CPU (r), что может указывать на более равномерную загрузку процессоров.

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

• В эксперименте 2(PG_EXPECTO+DeepSeek) проявилась новая проблема – сильная зависимость dirty pages от свободной памяти. Это указывает на дефицит оперативной памяти и агрессивное использование кэша.
• Остальные корреляции (dirty pages ↔ b, dirty pages ↔ bo, dirty pages ↔ sy) сохранились на том же высоком уровне.

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

• Тип нагрузки и основные проблемные запросы не изменились. Узкое место – чтение данных с диска (DataFileRead) в рамках сценария scenario1.

6.2.7 Ключевые проблемы

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

1. Доминирование IO-ожиданий (DataFileRead) – более 99% всех ожиданий, вызванных запросом scenario1.
2. Низкая эффективность кэширования – даже после увеличения shared_buffers hit ratio остаётся ~97%, что недостаточно для существенного снижения IO.
3. Неоптимальный запрос – scenario1 генерирует основную нагрузку на чтение с диска.

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

1. Хронический дефицит оперативной памяти – свободная RAM <5% на всём протяжении обоих тестов, что приводит к росту dirty pages и блокировкам процессов (b).
2. Высокая нагрузка на диск – процессы массово находятся в uninterruptible sleep (b), wa >60%.
3. Накладные расходы ядра – рост dirty pages вызывает рост системного времени (sy) и переключений контекста.
4. В эксперименте 2 – дополнительное ухудшение: при увеличении shared_buffers зависимость dirty pages от свободной памяти стала критической (корреляция -0.78). Система работает на пределе памяти.

6.2.8 Итоговый анализ влияния мероприятий "ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek)"

Положительные эффекты:

• Медианная операционная скорость выросла на 23% (с 433k до 534k операций).
• Небольшое снижение медианных ожиданий (~1%).
• Улучшение hit ratio на 0.2 п.п.
• Незначительное снижение cpu wa (с 68% до 66%).
• Исчез тренд роста очереди на CPU (r), что может указывать на более равномерную загрузку.

Отрицательные эффекты / нерешённые проблемы:

• Основное узкое место – IO-ожидания от запроса scenario1 – не устранено.
• Рост числа процессов в состоянии «b» продолжается с той же скоростью.
• Дефицит памяти усугубился: появилась сильная корреляция dirty pages ↔ free, что сигнализирует о нехватке RAM для обслуживания увеличенного shared_buffers.
• Системное время CPU (sy) выросло из-за обработки dirty pages.

Заключение: Оптимизация параметров (увеличение буферов, настройка vm.dirty, соответствие effective_io_concurrency оборудованию) дала прирост скорости, но не решила главную проблему – избыточные чтения с диска. Более того, повышение shared_buffers без увеличения физической памяти привело к критическому давлению на память. Дальнейшие усилия должны быть направлены на оптимизацию запроса scenario1 (индексация, пересмотр логики) и, возможно, увеличение RAM.

6.3 Полный сравнительный анализ

Общий итог анализа

Проведённое исследование показало, что оптимизация параметров PostgreSQL с помощью pg_expecto и DeepSeek позволила добиться заметного прироста операционной скорости — медианная производительность выросла на 23% по сравнению с базовой настройкой pgpro_tune.

Однако детальный статистический анализ выявил, что корневая проблема системы осталась нерешённой: более 99% всех ожиданий по-прежнему связаны с дисковым вводом-выводом (DataFileRead), вызванным неоптимальным запросом scenario1. Более того, увеличение shared_buffers без соответствующего наращивания оперативной памяти привело к критическому дефициту RAM и усилению корреляции между грязными страницами и свободной памятью.

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

Практическое применение pg_expecto + DeepSeek для оптимизации производительности СУБД

Комплекс pg_expecto в связке с DeepSeek продемонстрировал свою ценность как инструмент глубокой диагностики, выходящий за рамки традиционных рекомендаций. Нейросеть не только предложила конкретные изменения конфигурации (увеличение буферов, тонкая настройка vm.dirty, корректировка effective_io_concurrency), но и предоставила развёрнутый анализ корреляций между метриками СУБД и системы, указав на скрытые проблемы — например, на возникшую зависимость dirty pages от свободной памяти после изменений. Это позволяет DBA не просто следовать шаблонным советам, а понимать цену каждого тюнинга и вовремя идентифицировать моменты, когда дальнейшая параметрическая оптимизация становится бесполезной без вмешательства в логику приложений или модернизации оборудования. Таким образом, pg_expecto + DeepSeek выступает не заменой, а мощным дополнением к экспертизе администратора, помогая принимать обоснованные решения на основе статистических закономерностей.