pg_expecto и DeepSeek в деле: практический пример анализа и ускорения PostgreSQL
GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL
GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
В статье представлен практический кейс оптимизации PostgreSQL 17 на сервере с 8 ядрами CPU и 8 ГБ RAM. Исходная конфигурация, сгенерированная конфигуратором «Тантор Лабс», при нагрузочном тестировании продемонстрировала серьёзный кризис производительности из-за дефицита памяти и перегрузки дисковой подсистемы. С помощью инструмента pg_expecto и нейросети DeepSeek был проведён всесторонний анализ, выявлены узкие места и выполнена тонкая настройка параметров СУБД и ОС. Результат — прирост операционной скорости на 40% без модернизации оборудования. В материале — детали эксперимента, сравнение конфигураций и выводы о дальнейших шагах.
Задача
Проанализировать и оптимизировать конфигурацию СУБД подготовленную с помощью инструмента "Конфигуратор критически важных параметров производительности PostgreSQL от компании «Тантор Лабс»"
Входные данные для конфигуратора
- 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
Нагрузка по сценариям в ходе тестирования
Производительность СУБД с использованием конфигурации СУБД, подготовленной Тантор Лабс.
Формирование отчета по итогам нагрузочного тестирования
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-нитей.
Полный отчет, подготовленный нейросетью:
Сравнительный анализ применения рекомендованных нейросетью изменений
Входные файлы, используемые для анализа с помощью нейросети
- 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. Сравнительный статистический анализ ожиданий СУБД:
Основной источник задержек – операции ввода‑вывода. Прочие типы ожиданий не оказывают заметного влияния на общую нагрузку.
2. Сравнительный трендовый анализ производительности vmstat:
Система испытывает хроническую перегрузку по вводу‑выводу: более 50 % времени wa > 10 %, очередь процессов в состоянии b постоянно превышает количество ядер CPU.
3. Сравнительный статистический анализ ожиданий СУБД и метрик vmstat:
Увеличение shared_buffers и настройка контрольных точек позволили улучшить попадания в кэш и немного сгладить пики записи, однако фундаментальные проблемы с памятью (дефицит свободной RAM) и перегрузкой ввода‑вывода (процессы в состоянии b, высокий wa) остаются.
4. Сравнение диаграмм Парето по wait_event_type и queryid:
Узкое место не изменилось – это по‑прежнему чтение данных с диска (DataFileRead), генерируемое запросом scenario1. Оптимизация не затронула логику самого запроса.
Ключевые проблемы для экспериментов
Эксперимент‑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(). Без изменений в логике запроса или добавления индексов дальнейший рост скорости ограничен.
Общий вывод:
- Проведённая оптимизация дала значительный прирост производительности (медианная скорость +40,6 %) за счёт более эффективного использования памяти и сглаживания записи.
- Однако инфраструктурные ограничения (нехватка RAM, медленная дисковая подсистема) и неоптимизированный запрос остаются «узкими горлами».
- Для дальнейшего улучшения необходимы либо аппаратный апгрейд (больше памяти, более быстрые диски), либо глубокая оптимизация запроса scenario1 (индексы, рефакторинг).
Полный анализ:
Послесловие
Проведённый эксперимент наглядно демонстрирует, что даже значительный прирост производительности (медианная скорость +40 %) не является пределом, если остаются нерешёнными фундаментальные проблемы — неоптимизированный запрос и аппаратные ограничения.
Тандем pg_expecto и DeepSeek позволил не только улучшить конфигурацию, но и точно локализовать корневые причины торможений.
Однако поиск эталонной стратегии настройки PostgreSQL продолжается: в ближайшее время планируется серия аналогичных экспериментов, где в качестве отправной точки будут использоваться другие популярные инструменты генерации базовой конфигурации — pgpro_tune , PgTune.
Цель — сравнить эффективность под нагрузкой и определить, какой подход даёт наилучший «фундамент» для последующей тонкой оптимизации с помощью pg_expecto.