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

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

· Версия СУБД: PostgreSQL 17.5 на x86_64-pc-linux-gnu (64-bit)

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

CPU: 8 ядер Intel Xeon (Skylake)

RAM: 7.5 ГБ

Диски:

• vda (70 ГБ) — система, разделы LVM: /, /boot, swap, /tmp, /opt, /home, /var
• vdb (30 ГБ) — /log (отдельный LVM том)
• vdc (50 ГБ) — /wal (отдельный LVM том)
• vdd (100 ГБ) — /data (отдельный LVM том для данных)

· Ключевые настройки PostgreSQL:

• shared_preload_libraries = 'pg_stat_statements, pg_wait_sampling'
• track_io_timing = on
• logging_collector = on, логи в /log/pg_log
• archive_mode = on, archive_command = 'true', archive_timeout = 30min
• log_autovacuum_min_duration = 0
• Включены опции очистки памяти (wipe_* = on)

· Параметры управления памятью ОС (vm):

• dirty_background_ratio = 10% (фоновая запись)
• dirty_ratio = 30% (принудительная синхронная запись)
• dirty_expire_centisecs = 1000 (10 сек)
• dirty_writeback_centisecs = 200 (2 сек)
• swappiness = 1 (минимизация подкачки)
• vfs_cache_pressure = 100

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

Граничные значения операционной скорости (SPEED) и ожиданий СУБД (WAITINGS)

• SPEED: мин. 365 651, медиана 431 390, макс. 700 651
• WAITINGS (суммарные ожидания): мин. 49 679, медиана 79 032, макс. 240 293

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

• Тренд SPEED: R² = 0.86 (очень высокое качество), положительный наклон 42.9 ед./наблюдение. Скорость растёт.
• Тренд WAITINGS: R² = 0.87, положительный наклон 43.1 ед./наблюдение. Ожидания также растут синхронно со скоростью.
• Регрессия SPEED по WAITINGS: R² = 0.99 (практически линейная зависимость). Рост ожиданий напрямую сопровождается ростом операционной скорости — вероятно, нагрузка увеличивается пропорционально, но производительность не падает (нет обратной корреляции). Однако это маскирует возможные узкие места.

1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД

· Из всех типов ожиданий только IO имеет значимую корреляцию и высокий приоритет.

• Коэффициент корреляции с общей суммой ожиданий: 1.00 (p < 0.05)
• Взвешенная корреляция ожиданий (ВКО): 1.00 (критическое значение, требует немедленного анализа)
• Регрессия общих ожиданий по IO: R² = 1.00, наклон 45.0 — IO полностью объясняет динамику всех ожиданий.

· Типы BufferPin, Extension — корреляция отсутствует или отрицательная.

· Типы IPC, Lock, LWLock, Timeout — корреляция значимая, но ВКО < 0.01 (доля в общем времени ничтожна).

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

Единственный критический тип ожидания — IO. Все остальные типы можно игнорировать в рамках текущего анализа. Интегральный приоритет IO = 0.6271.

2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat

· procs → r (очередь на CPU): R² = 0.18 (модель непригодна), тренд незначим.

· procs → b (процессы в uninterruptible sleep, ожидание I/O):

• R² = 0.87 (очень высокое качество)
• Положительный тренд: +37.4 ед. за период (высокая скорость роста)
• Серьёзная проблема: процессы блокируются на I/O, очередь b растёт.

· cpu → wa (I/O wait): R² = 0.24 (слабая модель), слабый положительный тренд (+6.3). Фоновое наблюдение.

· cpu → id (простой CPU):

• R² = 0.96 (очень высокое качество)
• Отрицательный тренд: –42.6 ед. за период
• Серьёзная проблема: время простоя CPU стремительно падает — CPU всё больше занят обработкой (вероятно, из-за I/O wait и связанных с ним системных операций).

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

1. Рост процессов в состоянии b (ожидание I/O) — дисковая система не справляется.

2. Падение cpu idle — CPU простаивает всё меньше, но это не связано с полезной работой, а вызвано ожиданием I/O и системными издержками.

3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat

Относительные показатели vmstat (проценты времени)

· free (свободная RAM < 5%): 100% периода — свободной памяти практически нет (медиана свободной RAM ~180 МБ из 7.5 ГБ).

· wa > 10%: 100% периода — CPU постоянно ждёт I/O.

· b > количество ядер CPU: 100% периода — число процессов в состоянии b превышает 8 (до 40 при 8 ядрах) — критическая перегрузка I/O.

· us+sy > 80%: 0% — нагрузка CPU невысокая, основное время — I/O wait.

· r > ядер CPU: 0% — очереди на CPU нет.

· swap in/out: 0% — свопинг не используется.

Часть 1. Анализ ожиданий IO и метрик vmstat

· IO и bi (блоки чтения):

• Корреляция: 0.67 (p < 0.05), R² = 0.45 (модель средняя). Связь подтверждает, что IO ожидания частично вызваны чтениями с диска.

· IO и bo (блоки записи):

• Корреляция: 0.86 (p < 0.05), R² = 0.74 (модель хорошая). Основной вклад в IO ожидания даёт запись на диск.

· IO и wa (I/O wait):

• Корреляция низкая (0.20), R² < 0.2 — модель непригодна. Это указывает, что метрика wa не полностью отражает внутренние ожидания PostgreSQL (возможно, из-за того, что wa считает только время, когда CPU простаивает в ожидании I/O, а часть ожиданий происходит параллельно с работой других ядер).

Часть 2. Анализ метрик vmstat

· cs (context switches) и in (interrupts):

• Корреляция 0.996, R² = 0.99 — почти функциональная связь.

· cs и us (user time):

• Корреляция 0.994, R² = 0.99.

· cs и sy (system time):

• Корреляция 0.943, R² = 0.89.

· Вывод: переключения контекста тесно связаны с прерываниями и временем пользовательских/системных процессов. Высокий уровень cs говорит о частых системных вызовах и возможной конкуренции за ресурсы.

Часть 3. Анализ I/O (PostgreSQL)

· Shared Buffers Hit Ratio:

• мин. 96.3%, медиана 96.9%, макс. 98.6% — приемлемо, но не идеально (для OLTP ожидается >99%). Нагрузка ближе к OLAP.

· Корреляция скорости с прочитанными/записанными блоками:

• с чтениями — нет значимой связи.
• с записями — слабая (0.41), R² < 0.2 — модель непригодна.

· Корреляция hit ratio и прочитанных блоков:

• Отрицательная –0.73, R² = 0.53 — при росте чтений hit ratio падает, что логично: кэш не вмещает все данные.

· Корреляция записанных блоков PostgreSQL и bo (блоки записи ОС):

• 0.896, R² = 0.80 (отличная модель). Это прямое подтверждение, что запись из PostgreSQL вызывает активность дисковой подсистемы.

· Другие корреляции (с swap, грязными блоками и т.д.) — незначимы или модель непригодна.

Часть 4. Анализ vm.dirty* (грязные страницы)

· Размер dirty pages (KB): мин. 5 474, медиана 7 120, макс. 17 282.

· dirty_ratio достигает 1% (редко), обычно 0% — лимит не превышается.

· dirty_background_ratio в пределах 1–3% — фоновая запись работает.

· Корреляции dirty pages:

• с b (процессы в uninterruptible sleep): 0.993, R² = 0.99 (чрезвычайно сильная связь). Размер грязных страниц напрямую определяет число заблокированных процессов.
• с bo (блоки записи): 0.889, R² = 0.79 — хорошая модель. Рост dirty pages приводит к усилению записи на диск.
• с sy (system time): 0.937, R² = 0.88 — ядро тратит много времени на управление памятью и запись.
• с wa и free — слабые или непригодные модели.

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

· Главная причина проблем — накопление «грязных» страниц в памяти, которое приводит к массовой блокировке процессов (рост b) и высокой дисковой записи (bo). Система не успевает сбрасывать dirty pages, несмотря на настройки vm.dirty*.

· Корреляции подтверждают, что I/O ожидания PostgreSQL вызваны преимущественно записью, а не чтением.

· Переключения контекста очень высоки и коррелируют с системным временем — дополнительный признак нагрузки на ядро из-за I/O.

4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID

· По типам событий:

• IO / DataFileRead: 99.94% всех ожиданий. Остальные типы пренебрежимо малы.

· По запросам (queryid):

• -1679588366130117659 (SELECT scenario1()): 85.21% всех ожиданий IO. Вызовов: 16.58 млн.
• Два других запроса (scenario2, scenario3) имеют незначительную долю.

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

Практически все проблемы I/O сконцентрированы на одном запросе — scenario1(). Это главный кандидат для оптимизации.

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

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

· Критический тип: IO (ВКО = 1.00). Доля в общем времени ожиданий – 100%.

· Модель общие ожидания = f(IO) идеальна (R²=1). Все остальные типы можно исключить.

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

· Свободная RAM крайне мала (медиана 179 МБ) — система утилизирует почти всю память под кэш и данные.

· Hit ratio высок (96–98%), но не идеален. При росте чтений hit ratio падает — кэш shared_buffers (вероятно, не настроен оптимально) не покрывает рабочее множество.

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

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

· Чтение (bi) коррелирует с IO (0.67), но слабее, чем запись.

· Запись (bo) — основной драйвер IO ожиданий (корреляция 0.86, R²=0.74). Запись блоков PostgreSQL совпадает с bo ОС (корр. 0.90).

· Процессы в состоянии b достигают 40 при 8 ядрах — диск перегружен, запросы на запись ставятся в очередь.

· I/O wait CPU (wa) стабильно выше 10% (100% времени) — диск является узким местом.

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

· us + sy не превышают 30%, основное время — wa (62–73%).

· Переключения контекста (cs) очень высоки (медиана 33 129) и тесно связаны с in, us, sy. Причина — интенсивные системные вызовы при работе с I/O и управлении памятью.

· sy сильно коррелирует с размером dirty pages — ядро занято обработкой страничного кэша.

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

· Доля ожиданий LWLock и Lock ничтожна (ВКО < 0.01). Проблем с блокировками внутри СУБД не наблюдается.

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

· Основной вклад вносит вызов scenario1() (вероятно, хранимая процедура или функция). Требуется детальное исследование её плана выполнения и объёма обрабатываемых данных.

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

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

1. Чрезмерные ожидания I/O, сконцентрированные на одном запросе scenario1() (85% всех ожиданий).

2. Неоптимальное использование буферного кэша — hit ratio мог бы быть выше для такого объёма памяти; возможно, не хватает shared_buffers или неэффективные сканы.

3. Высокая нагрузка на запись — корреляция IO с bo и dirty pages указывает на то, что СУБД генерирует много грязных страниц, которые не успевают сбрасываться.

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

1. Дисковая подсистема не справляется с нагрузкой записи:

• Процессы массово уходят в состояние b (до 40).
• CPU простаивает в I/O wait 62–73% времени.
• Тренды b и id указывают на ухудшение.

2. Накопление dirty pages в памяти ОС:

• Размер грязных страниц сильно коррелирует с b и bo.
• Настройки vm.dirty_background_ratio (10%) и vm.dirty_ratio (30%) не предотвращают пиков, хотя превышение dirty_ratio редкое (макс 1%). Вероятно, скорость генерации dirty pages выше скорости их сброса.

3. Высокие системные издержки (context switches, system time) — ядро тратит ресурсы на управление памятью и I/O.

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

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

1. Оптимизация запроса scenario1():

• Проанализировать план выполнения (EXPLAIN ANALYZE).
• Проверить наличие необходимых индексов, особенно для условий, вызывающих сканирования.
• Рассмотреть возможность материализованных представлений или изменения логики, чтобы уменьшить объём чтения/записи.

2. Настройка контрольных точек и фоновой записи:

• Увеличить checkpoint_timeout (например, до 15–30 мин) и max_wal_size (до 20–30 ГБ) для более редких, но плавных контрольных точек.
• Отрегулировать bgwriter_delay (снизить до 100–200 мс), bgwriter_lru_maxpages (увеличить до 1000), чтобы фоновый писатель активнее сбрасывал грязные страницы.
• Мониторить статистику pg_stat_bgwriter: buffers_checkpoint, buffers_clean, buffers_backend — если buffers_backend велико, процессы сами вытесняют страницы, что плохо.

3. Увеличение shared_buffers (с учётом 7.5 ГБ RAM можно поднять до 2–3 ГБ, но оставить память для ОС и кэша файловой системы). Проверить текущее значение.

4. Включить мониторинг расширенных событий ожидания и статистики по индексам (pg_stat_user_indexes) для выявления неиспользуемых или отсутствующих индексов.

5. Рассмотреть партиционирование больших таблиц, если scenario1() работает с историческими данными.

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

1. Корректировка параметров vm.dirty*:

• Уменьшить dirty_background_ratio до 5% (или dirty_background_bytes), чтобы фоновый сброс начинался раньше.
• Уменьшить dirty_ratio до 15–20%, чтобы процессы блокировались при меньшем накоплении грязных страниц, но при этом избегать длительных простоев.
• Уменьшить dirty_expire_centisecs до 500 (5 сек), чтобы страницы быстрее считались устаревшими.
• Увеличить dirty_writeback_centisecs до 100 (1 сек) для более частых пробуждений фоновой записи.
• После изменений наблюдать за размером dirty pages и процессами b.

2. Оценка производительности дисков:

• Запустить iostat -x 1 для выявления загрузки устройств (vdd, vdc), времени отклика (await), утилизации (%util).
• Возможно, требуется миграция на более быстрые диски (SSD) или настройка RAID.
• Рассмотреть размещение WAL на отдельном быстром диске (уже есть отдельный том, но проверить его производительность).

3. Планирование ресурсов:

• Увеличить RAM, если возможно, чтобы расширить кэш и уменьшить конкуренцию за память.
• В перспективе — балансировка нагрузки или шардинг.

Заключение

Система испытывает серьёзные проблемы с производительностью ввода-вывода. Почти все ожидания СУБД связаны с I/O, преимущественно с записью, и вызваны одним запросом. Инфраструктура (диски, настройки ОС) не справляется с объёмом грязных страниц, генерируемых PostgreSQL, что приводит к массовой блокировке процессов и высокому I/O wait.

Первоочередные действия: оптимизация запроса scenario1() и настройка параметров контрольных точек/фоновой записи, а также корректировка vm.dirty* для более агрессивного сброса данных на диск. Дополнительно необходима диагностика дисковой подсистемы.

При успешной оптимизации запроса и тонкой настройке можно ожидать снижения I/O ожиданий, уменьшения числа процессов в состоянии b и высвобождения ресурсов CPU для полезной работы.