GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL
Глоссарий терминов | Postgres DBA | Дзен
Процесс начальной конфигурации сервера PostgreSQL, как правило, опирается на использование автоматизированных генераторов параметров (конфигураторов). Такие инструменты, включая широко применяемый конфигуратор компании «Тантор Лабс», предлагают готовый набор настроек на основе ограниченных входных данных — объёма оперативной памяти, количества ядер CPU и предполагаемого типа нагрузки. Однако на практике сг��нерированная таким образом конфигурация не учитывает специфику реального рабочего профиля и особенности взаимодействия СУБД с аппаратным обеспечением, что может приводить к критическому снижению производительности, росту I/O wait и неэффективному использованию ресурсов.
Возникает закономерный вопрос: возможно ли выявить и устранить эти «узкие места» без модернизации оборудования, опираясь лишь на углублённый анализ статистических данных?
Настоящая работа представляет собой практический кейс оптимизации PostgreSQL 17 на сервере с ограниченными ресурсами (8 ядер CPU, 8 ГБ RAM). В качестве отправной точки используется конфигурация, сгенерированная конфигуратором «Тантор Лабс». Для диагностики и последующей настройки применяется комплексный подход, основанный на двух инструментах:
pg_expecto — Open source комплекс для сбора и статистического анализа метрик производительности СУБД и операционной системы .
Нейросеть DeepSeek — для автоматизированного анализа собранных данных, выявления неочевидных закономерностей и формирования рекомендаций по изменению параметров PostgreSQL и ядра ОС.
Цель эксперимента — не просто продемонстрировать прирост скорости на 40%, но и верифицировать гипотезу о том, что синергия статистического анализа и методов машинного обучения позволяет существенно повысить эффективность типовой конфигурации, приближая её к оптимальной для заданного профиля нагрузки. В статье подробно разбираются исходные и итоговые параметры, приводится сравнительный анализ метрик, а также фиксируются ограничения, которые не удалось преодолеть исключительно настройкой (дефицит оперативной памяти и неоптимизированный запрос). Полученные результаты формируют основу для дальнейших исследований, направленных на сравнение различных стратегий начальной генерации конфигураций PostgreSQL.
1.Условия эксперимента
Инфраструктура и СУБД
CPU = 8
RAM = 8GB
Тип нагрузки = OLTP
Платформа = Linux
Версия PostgreSQL = 17
Методика нагрузочного тестирования
Для создания контролируемой синтетической нагрузки и сбора первичных метрик использовался открытый инструмент pg_expecto.
Параметры тестирования задавались следующим образом:
Режим нагрузки: OLTP (сценарии с весами 0.7 / 0.2 / 0.1).
Диапазон нагрузки: от 10 до 80 одновременных сессий.
Объём тестовых данных: ~10 ГБ (scale = 685).
Нагрузка по сценариям в ходе тестирования
2. Формирование baseline: конфигурация «Тантор Лабс»
В качестве отправной точки (baseline) для эксперимента была выбрана конфигурация, сгенерированная онлайн-конфигуратором критически важных параметров PostgreSQL от компании «Тантор Лабс». Данный инструмент широко известен в профессиональном сообществе и предлагает быстрый способ получения стартового набора настроек на основе минимальных входных данных.
Конфигуратор параметров производительности PostgreSQL
Результирующая конфигурация (фрагмент):
shared_buffers = 1779MB
max_wal_size = 2021MB
checkpoint_timeout = 15min
bgwriter_delay = 54ms
bgwriter_lru_maxpages = 515
bgwriter_lru_multiplier = 7.0
track_io_timing = on
logging_collector = on
Полный листинг конфигурации
Конфигурация Тантор Лабс
# Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. track_io_timing = 'on' listen_addresses = '0.0.0.0' logging_collector = 'on' log_directory = '/log/pg_log' log_destination = 'stderr' log_rotation_size = '0' log_rotation_age = '1d' log_filename = 'postgresql-%u.log' log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| ' log_truncate_on_rotation = 'on' log_checkpoints = 'on' archive_mode = 'on' archive_command = 'true' archive_timeout = '30min' pg_stat_statements.track_utility = 'off' shared_preload_libraries = 'pg_stat_statements , pg_wait_sampling' log_autovacuum_min_duration = '0' wipe_file_on_delete = 'on' wipe_heaptuple_on_delete = 'on' wipe_mem_on_free = 'on' wipe_memctx_on_free = 'on' wipe_xlog_on_free = 'on' log_connections = 'on' log_disconnections = 'on' pg_stat_statements.track = 'all' wal_compression = 'lz4' jit = 'on' client_connection_check_interval = '3s' default_toast_compression = 'pglz' enable_async_append = 'on' autovacuum_vacuum_insert_threshold = '1596' autovacuum_vacuum_insert_scale_factor = '0.01' logical_decoding_work_mem = '64MB' maintenance_io_concurrency = '128' wal_keep_size = '1506MB' hash_mem_multiplier = '1.2' max_parallel_maintenance_workers = '4' max_parallel_workers = '4' max_logical_replication_workers = '4' max_sync_workers_per_subscription = '2' autovacuum = 'on' autovacuum_max_workers = '4' autovacuum_work_mem = '189MB' autovacuum_naptime = '15s' autovacuum_vacuum_threshold = '1596' autovacuum_analyze_threshold = '798' autovacuum_vacuum_scale_factor = '0.001' autovacuum_analyze_scale_factor = '0.0007' autovacuum_vacuum_cost_limit = '2188' vacuum_cost_limit = '8000' autovacuum_vacuum_cost_delay = '10ms' vacuum_cost_delay = '10ms' vacuum_cost_delay = '10ms' autovacuum_freeze_max_age = '500000000' autovacuum_multixact_freeze_max_age = '800000000' shared_buffers = '1779MB' max_connections = '91' max_files_per_process = '1391' superuser_reserved_connections = '4' work_mem = '35MB' temp_buffers = '3990kB' maintenance_work_mem = '196MB' huge_pages = 'try' fsync = 'on' wal_level = 'replica' synchronous_commit = 'on' full_page_writes = 'on' wal_buffers = '23MB' wal_writer_delay = '100ms' wal_writer_flush_after = '3050kB' min_wal_size = '1010MB' max_wal_size = '2021MB' max_replication_slots = '0' max_wal_senders = '0' wal_sender_timeout = '0' wal_log_hints = 'off' hot_standby = 'off' wal_receiver_timeout = '0' max_standby_streaming_delay = '-1' wal_receiver_status_interval = '0' hot_standby_feedback = 'off' checkpoint_timeout = '15min' checkpoint_warning = '30s' checkpoint_completion_target = '0.8' commit_delay = '0' commit_siblings = '0' bgwriter_delay = '54ms' bgwriter_lru_maxpages = '515' bgwriter_lru_multiplier = '7.0' effective_cache_size = '5081MB' cpu_operator_cost = '0.0025' default_statistics_target = '500' random_page_cost = '1.1' seq_page_cost = '1' join_collapse_limit = '8' from_collapse_limit = '8' geqo = 'on' geqo_threshold = '12' effective_io_concurrency = '128' max_worker_processes = '8' max_parallel_workers_per_gather = '2' max_locks_per_transaction = '190' max_pred_locks_per_transaction = '190' statement_timeout = '86400000' idle_in_transaction_session_timeout = '86400000'
Основная задача первого этапа заключалась не в достижении идеальной производительности, а в объективной фиксации поведения системы при «рекомендованной» конфигурации. Полученные данные формируют baseline — точку отсчёта, относительно которой будет оцениваться эффект от последующих оптимизаций.
Результаты первого этапа
Медианное значение скорости составило 383 994 , что само по себе является лишь констатацией факта, но не оценочной характеристикой.
Рисунок 1. Операционная скорость PostgreSQL 17 с конфигурацией «Тантор Лабс» (baseline). Наблюдается высокая вариативность, медианное значение ~384 000
Почему это важно?
Сформированный baseline позволяет перейти от субъективных ощущений («работает быстро» или «тормозит») к количественным показателям. Именно эти данные — метрики pg_expecto— послужили входной информацией для последующего анализа с участием нейросети DeepSeek. Конфигуратор выполнил свою функцию: предоставил стартовую точку. Вопрос в том, насколько далеко можно уйти от этой точки с помощью тонкой настройки, основанной на данных, а не на общих рекомендациях.
3. Инструментарий: синергия pg_expecto и DeepSeek
Для проведения эксперимента требовался инструмент, который не просто собирает метрики, а позволяет установить причинно-следственные связи между состоянием операционной системы, внутренними событиями PostgreSQL и итоговой производительностью. Эту задачу решил комплекс pg_expecto(версия 7) .
3.1. pg_expecto: мост между СУБД и «железом»
В отличие от традиционных мониторинговых решений, pg_expecto сознательно фокусируется на статистических методах анализа и обеспечивает полную прозрачность процесса . Ключевые возможности, использованные в эксперименте:
Сбор метрик операционной системы: Инструмент интегрируется с утилитами vmstat и iostat, что позволяет напрямую коррелировать нагрузку на диск, память и процессор с поведением базы данных . Это критически важно, так как многие проблемы производительности коренятся именно на уровне ОС.
Детальный анализ ожиданий СУБД: pg_expecto выполняет глубокий разбор событий ожидания (wait_event_type/wait_event), помогая установить, что именно тормозит запросы — чтение с диска (DataFileRead), блокировки (LWLock) или что-то иное .
Встроенное нагрузочное тестирование: Комплекс позволяет проводить тесты по гибким сценариям (в нашем случае — OLTP-нагрузка с тремя типами операций) и автоматически собирать статистику .
Корреляционный анализ: pg_expecto рассчитывает зависимости между метриками (например, между ожиданиями ввода-вывода и состоянием процессов b в vmstat), что автоматически подсвечивает «узкие места» .
3.2. DeepSeek: аналитик, работающий с данными, а не с графиками
Сырые данные, собранные pg_expecto, были переданы нейросети DeepSeek для семантического анализа. Однако ключевой момент — формат передачи. Вместо того чтобы загружать графики или общее описание проблемы, мы использовали структурированные текстовые файлы, подготовленные pg_expecto. Этот подход многократно описан в сообществе как наиболее эффективный для интеграции с ИИ .
Формирование отчета по итогам нагрузочного тестирования
cd /postgres/pg_expecto/sh/performance_reports
./load_test_report.sh
Что получил DeepSeek на вход?
_1.settings.txt — полный дамп настроек PostgreSQL и параметров ядра ОС (sysctl).
_1.settings.txt | Postgres DBA | Дзен
2.postgresqlvmstat.txt — результат комплексного корреляционного анализа метрик СУБД и vmstat за период теста.
2.postgresqlvmstat.txt | Postgres DBA | Дзен
3.3. «Золото» эксперимента: промпты для DeepSeek
Ниже приведены точные формулировки промптов, которые использовались для анализа. Именно детализация запроса позволила получить конкретные, а не общие рекомендации.
Промпт №1 (для анализа базовой конфигурации) — файл _3.1.prompt.txt
Сформируй сводный отчет по производительности СУБД и инфраструктуры. Для формирования отчета используй списки, вместо таблиц. Состав отчета: # Общая информация # Общий анализ операционной скорости и ожиданий СУБД ## Граничные значение операционной скорости (SPEED) и ожиданий СУБД(WAITINGS) ## Анализ трендов операционной скорости (SPEED) и ожиданий СУБД(WAITINGS) ## 1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД ### Итог по разделу "1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД" ## 2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat ### Итог по разделу "2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat" ## 3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat ### Итог по разделу "3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat" ## 4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID ### Итог по разделу "4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID" # Детальный анализ – граничные значения и корреляции ## Ожидания СУБД ## Память и буферный кэш ## Дисковая подсистема (I/O) ## CPU и системные вызовы ## Блокировки и ожидания LWLock ## Анализ запросов (queryid) # Ключевые проблемы ## Проблемы СУБД ## Проблемы инфраструктуры # Рекомендации ## Рекомендации по настройкам СУБД ## Рекомендации по настройкам операционной системы # Заключение
Промпт №2 (сравнительный анализ "До/После") — файл _3.3.prompt.diff.txt
Сформируй сводный сравнительный отчет по производительности СУБД и инфраструктуры: ЭКСПЕРИМЕНТ-1 ("Тантор Лабс"-pgconfigurator) ЭКСПЕРИМЕНТ-2 (ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek)) Для формирования отчета используй списки, вместо таблиц. Состав отчета: # Общая информация # Сравнение конфигурации СУБД и опреационной системы для "ЭКСПЕРИМЕНТ-1 ("Тантор Лабс"-pgconfigurator)" и "ЭКСПЕРИМЕНТ-2 (ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek))" # Общий анализ операционной скорости и ожиданий СУБД для "ЭКСПЕРИМЕНТ-1 ("Тантор Лабс"-pgconfigurator)" и "ЭКСПЕРИМЕНТ-2 (ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + eepSeek))" ## Сравнительный анализ граничных значений операционной скорости (SPEED) и ожиданий СУБД(WAITINGS) ## Сравнительный анализ трендов операционной скорости (SPEED) и ожиданий СУБД(WAITINGS) ## 1. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД ### Итог по разделу "1. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД" ## 2. СРАВНИТЕЛЬНЫЙ ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat ### Итог по разделу "2. СРАВНИТЕЛЬНЫЙ ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat" ## 3. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat ### Итог по разделу "3. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat" ## 4. СРАВНЕНИЕ ДИАГРАММ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID ### Итог по разделу "4. СРАВНЕНИЕ ДИАГРАММ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID # Детальный анализ – граничные значения и корреляции для "ЭКСПЕРИМЕНТ-1 ("Тантор Лабс"-pgconfigurator)" и "ЭКСПЕРИМЕНТ-2 (ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek))" ## Ожидания СУБД ## Память и буферный кэш ## Дисковая подсистема (I/O) ## CPU и системные вызовы ## Блокировки и ожидания LWLock ## Анализ запросов (queryid) # Ключевые проблемы для "ЭКСПЕРИМЕНТ-1 ("Тантор Лабс"-pgconfigurator)" и "ЭКСПЕРИМЕНТ-2 (ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek))" ## Проблемы СУБД ## Проблемы инфраструктуры # Итоговый анализ влияния мероприятий "ОПТИМИЗАЦИЯ НАСТРОЕК СУБД и ОС (PG_EXPECTO + DeepSeek)" на производительность СУБД и инфраструктуры
3.4. Почему эта связка работает
Собранные pg_expecto данные представляют собой не просто сырые логи, а уже предварительно обработанный статистический материал с рассчитанными трендами и корреляциями . DeepSeek, получив такую структурированную информацию, выступает в роли эксперта, который способен:
Сопоставить значения shared_buffers с давлением на диск (метрики bo из vmstat).
Увидеть, что высокий bgwriter_lru_multiplier приводит к излишним сбросам и росту ожиданий LWLock.
Сформулировать выводы на естественном языке, понятном инженеру.
В результате мы получаем не абстрактный совет «увеличить буферы», а точное предписание, валидированное на реальных данных нагрузочного тестирования. Именно этот подход позволил добиться прироста в +40%, сохранив при этом честность анализа и указав на оставшиеся ограничения.
4. Диагноз от ИИ и новая конфигурация
На основе анализа, выполненного DeepSeek (промпт №1), был сформирован следующий перечень рекомендаций, разделённый на параметры PostgreSQL и настройки ядра ОС.
Рекомендации по настройкам СУБД
Увеличить 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-нитей.
Полный отчет, подготовленный нейросетью:
"Тантор Лабс": Сводный отчет по производительности СУБД и инфраструктуры. | Postgres DBA | Дзен
Применение настроек СУБД и ОС, рекомендованных DeepSeek
Новая конфигурация СУБД и ОС - рекомендованная DeepSeek (Тантор Лабс)
Увеличить shared_buffers (до 2–2.5 GB, учитывая 7.5 GB RAM), чтобы больше данных помещалось в кэш. Увеличить max_wal_size (например, до 4–5 GB), чтобы сгладить пики записи. Увеличить checkpoint_timeout (до 20–30 мин) и проверить checkpoint_completion_target (0.9). Увеличить bgwriter_delay (например, 100–150 мс) и bgwriter_lru_maxpages, чтобы сгладить сброс грязных страниц. bgwriter_delay – увеличить с 54 мс до 100 мс. bgwriter_lru_maxpages – увеличить с 515 до 1000. bgwriter_lru_multiplier – рекомендуется снизить с 7.0 до 4.0–5.0. postgres=# ALTER SYSTEM SET shared_buffers = '3GB'; ALTER SYSTEM postgres=# ALTER SYSTEM SET max_wal_size = '5GB'; ALTER SYSTEM postgres=# ALTER SYSTEM SET checkpoint_timeout ='30min'; ALTER SYSTEM postgres=# ALTER SYSTEM SET bgwriter_delay = '100ms'; ALTER SYSTEM postgres=# ALTER SYSTEM SET bgwriter_lru_maxpages = 1000; ALTER SYSTEM postgres=# ALTER SYSTEM SET bgwriter_lru_multiplier = 4 ; ALTER SYSTEM $ pg_ctl restart -D /data/pg_data waiting for server to shut down.... done server stopped waiting for server to start....2026-03-06 09:45:04.692 MSK| | | | | 990478| 00000| СООБЩЕНИЕ: Start CFS version 0.55 supported compression algorithms pglz,zlib,lz4,zstd encryption disabled GC enabled 2026-03-06 09:45:04.700 MSK| | | | | 990478| 00000| СООБЩЕНИЕ: передача вывода в протокол процессу сбора протоколов 2026-03-06 09:45:04.700 MSK| | | | | 990478| 00000| ПОДСКАЗКА: В дальнейшем протоколы будут выводиться в каталог "/log/pg_log". done server started postgres=# show shared_buffers; shared_buffers ---------------- 3GB (1 row) postgres=# show max_wal_size ; max_wal_size -------------- 5GB (1 row) postgres=# show checkpoint_timeout; checkpoint_timeout -------------------- 30min (1 row) postgres=# show bgwriter_delay; bgwriter_delay ---------------- 100ms (1 row) postgres=# show bgwriter_lru_maxpages; bgwriter_lru_maxpages ----------------------- 1000 (1 row) postgres=# show bgwriter_lru_multiplier ; bgwriter_lru_multiplier ------------------------- 4 (1 row) Уменьшить vm.dirty_background_ratio до 2–3% (сейчас 10%). Уменьшить vm.dirty_ratio до 10–15% (сейчас 30%). Уменьшить vm.dirty_writeback_centisecs до 200–300 (сейчас 500) для более частого пробуждения flusher-нитей # sysctl -w vm.dirty_background_ratio=2 vm.dirty_background_ratio = 2 # sysctl -w vm.dirty_ratio=10 vm.dirty_ratio = 10 # sysctl -w vm.dirty_writeback_centisecs=200 vm.dirty_writeback_centisecs = 200 #
Общая информация
Эксперимент‑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.) остались без изменений.
5. Результаты и выводы
5.1. Количественные показатели (таблица + графики)
Граничные значения производительности и ожиданий СУБД
+40% на пике и в медиане по операционной скорости
Рисунок 2. Сравнение операционной скорости: базовая конфигурация (синий) и после оптимизации pg_expecto + DeepSeek (оранжевый). Медианный прирост составил 40,6%.
Ожидания IO
Рисунок 3. Динамика ожиданий ввода-вывода. Медианные ожидания снизились незначительно (на 2,2%), что указывает на сохранение проблемы с дисковой подсистемой.
5.2. Анализ узких мест (проблемы СУБД и инфраструктуры)
Общий анализ операционной скорости и ожиданий СУБД
Сравнительный анализ граничных значений операционной скорости (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) остались на прежнем критическом уровне.
Системные вызовы и переключения контекста по‑прежнему крайне высоки.
5.3. Итоговый вердикт (общий вывод)
Итоговый анализ влияния мероприятий
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 позволил не только улучшить конфигурацию, но и точно локализовать корневые причины торможений.
На этом эксперименты не заканчиваются. В следующих статьях проверим, как покажут себя pgpro_tune и классический PgTune в аналогичных условиях. Цель — найти идеальный «фундамент» для PostgreSQL 17.
Цель — сравнить эффективность под нагрузкой и определить, какой подход даёт наилучший «фундамент» для последующей тонкой оптимизации с помощью pg_expecto.
