Общая информация
· Версия PostgreSQL: 17.5 на x86_64-pc-linux-gnu (компилятор GCC 11.4.1)
· Аппаратная платформа:
o 8 виртуальных CPU (Intel Xeon Skylake, 1 поток на ядро, 8 сокетов)
o Оперативная память: 7.50 GB
o Диски:
§ vda (70 GB) – системный, LVM
§ vdb (30 GB) – /log (29 GB)
§ vdc (50 GB) – /wal (49 GB)
§ vdd (100 GB) – /data (99 GB)
· Ключевые настройки PostgreSQL (pgpro_tune):
o shared_buffers = 1919 MB
o effective_cache_size = 3838 MB
o work_mem = 32 MB
o maintenance_work_mem = 479 MB
o max_connections = 239
o wal_compression = lz4
o effective_io_concurrency = 200 (для SSD)
o random_page_cost = 1.1 (NVMe SSD)
o jit = off
· Настройки управления памятью ОС (vm):
o vm.dirty_background_ratio = 10
o vm.dirty_ratio = 30
o vm.dirty_expire_centisecs = 1000
o vm.dirty_writeback_centisecs = 200
o vm.vfs_cache_pressure = 100
o vm.swappiness = 1
Общий анализ операционной скорости и ожиданий СУБД
Граничные значение операционной скорости (SPEED) и ожиданий СУБД (WAITINGS)
· Операционная скорость (SPEED):
o Минимум: 381 424
o Медиана: 433 329
o Максимум: 700 915
· Общие ожидания СУБД (WAITINGS):
o Минимум: 49 576
o Медиана: 79 295
o Максимум: 225 664
Анализ трендов операционной скорости (SPEED) и ожиданий СУБД (WAITINGS)
· Тренд SPEED:
o Коэффициент детерминации R² = 0.85 – модель очень высокого качества.
o Положительный угол наклона (+42.73) – скорость растёт во времени.
· Тренд WAITINGS:
o R² = 0.88 – очень высокое качество.
o Положительный угол наклона (+43.23) – ожидания также растут.
· Регрессия SPEED по WAITINGS:
o R² = 0.99 – исключительно сильная связь.
o Угол наклона +44.83 – увеличение ожиданий напрямую сопровождается ростом операционной скорости (нагрузки).
1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД
· Типы ожиданий (wait_event_type) с валидной корреляцией:
o IO – корреляция 1.000 (значима), ВКО = 1.000 (критическое), R² = 1.000.
Вывод: все ожидания СУБД практически полностью состоят из ожиданий ввода-вывода.
o IPC, Lock, LWLock, Timeout – корреляции высокие (0.92–0.98) и значимые, но ВКО < 0.01, поэтому в текущем анализе игнорируются.
· Интегральный приоритет:
o
1. IO – 0.6271
Итог по разделу "1. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД"
· Единственный критический тип ожиданий – IO.
· Все остальные типы ожиданий присутствуют, но их вклад в общую картину ничтожен.
2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat
· procs -> r (очередь на выполнение):
o R² = 0.75 (хорошая модель), положительный тренд, скорость изменения 30.59 (очень высокая).
o Вывод: растёт число процессов, ожидающих CPU.
· procs -> b (непрерываемый сон, ожидание IO):
o R² = 0.87 (очень высокое качество), положительный тренд, скорость изменения 37.25 (очень высокая).
o Вывод: серьёзный рост числа процессов, заблокированных на IO.
· cpu -> wa (ожидание IO):
o R² = 0.13 – модель непригодна, тренд статистически не значим.
· cpu -> id (простой CPU):
o R² = 0.96 (очень высокое качество), отрицательный тренд (-44.45), скорость изменения 42.76 (очень высокая).
o Вывод: время простоя CPU стремительно падает – CPU занят обработкой, но не всегда полезной работой.
Итог по разделу "2. ТРЕНДОВЫЙ АНАЛИЗ ПРОИЗВОДИТЕЛЬНОСТИ vmstat"
· Наблюдается ухудшение по ключевым метрикам: растут очереди на выполнение и блокировки IO, падает простой CPU.
· Требуется немедленное исследование дисков и планировщика.
3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat
· Алармы по vmstat (проценты времени):
o free RAM < 5% – 100% периода (ALARM)
o wa (ожидание IO) > 10% – 100% периода (ALARM)
o b (процессы в uninterruptible sleep) > кол-ва ядер CPU – 100% периода (ALARM)
· Корреляции IO с vmstat:
o IO и bo (записанные блоки): r = 0.875, R² = 0.77 – хорошая модель, указывает на сброс грязных страниц.
o IO и bi (прочитанные блоки): r = 0.279, R² непригоден – чтение не коррелирует с ожиданиями напрямую.
o IO и wa: корреляция незначима.
· Корреляции между метриками vmstat:
o cs (переключения контекста) и in (прерывания): r = 0.996, R² = 0.99 – исключительная связь.
o cs и us (user time): r = 0.994, R² = 0.99 – переключения контекста вызваны пользовательской нагрузкой.
o cs и sy (system time): r = 0.954, R² = 0.91 – ядро тратит много времени на переключения.
· Корреляции, связанные с дисками и памятью:
o Shared_buffers hit ratio: 96.2% – 98.5% (приемлемо для OLAP).
o Записанные блоки (БД) и bo: r = 0.905, R² = 0.82 – очень сильная связь (ALARM – возможна чрезмерная запись).
o Dirty pages size и b (процессы в uninterruptible sleep): r = 0.994, R² = 0.99 – объём грязных страниц напрямую связан с блокировкой процессов.
o Dirty pages size и bo: r = 0.906, R² = 0.82 – фоновый сброс не успевает.
o Dirty pages size и sy: r = 0.951, R² = 0.90 – высокие накладные расходы ядра на управление памятью.
Итог по разделу "3. СТАТИСТИЧЕСКИЙ АНАЛИЗ ОЖИДАНИЙ СУБД и МЕТРИК vmstat"
· Основной источник проблем – дисковый ввод-вывод, порождающий огромное количество грязных страниц, перегрузку ядра и блокировку процессов.
· Несмотря на высокий hit ratio, запись на диск остаётся узким местом.
4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID
· По типам событий (wait_event):
o DataFileRead – 99.95% всех ожиданий IO.
· По запросам (queryid):
o -2753873076014177353 (select scenario1()) – 85.24% всех ожиданий IO (12.2 млн из 14.3 млн).
o Два других запроса (scenario2, scenario3) также присутствуют, но доля мала.
Итог по разделу "4. ДИАГРАММЫ ПАРЕТО ПО WAIT_EVENT_TYPE и QUERYID"
· Подавляющая часть ожиданий приходится на чтение данных из файлов (DataFileRead).
· Конкретный запрос select scenario1() генерирует 85% всех ожиданий – главный кандидат на оптимизацию.
Детальный анализ – граничные значения и корреляции
Ожидания СУБД
· IO: мин. 49 564, мед. 79 271, макс. 225 366.
· Lock, LWLock, IPC, Timeout – абсолютные значения крайне малы (макс. 141, 89, 14, 55 соответственно).
Память и буферный кэш
· Shared buffers hit ratio: 96.2% – 98.5% – хороший показатель попадания в кэш.
· Обратная зависимость hit ratio и прочитанных блоков: r = -0.983, R² = 0.97 – кэш эффективно снижает чтения с диска.
· Свободная RAM крайне мала (<5% в течение всего периода) – возможна нехватка памяти для кэширования.
Дисковая подсистема (I/O)
· Чтение (bi): мин. 27 692, мед. 28 625, макс. 32 045 блоков/с.
· Запись (bo): мин. 18 624, мед. 20 169, макс. 27 664 блоков/с.
· Корреляция IO с bo (r=0.875) и записанные блоки БД с bo (r=0.905) указывают на то, что запись – основной фактор ожиданий.
· Грязные страницы: размер от 5.6 МБ до 17.9 МБ; их рост прямо пропорционален числу заблокированных процессов и объёму записи на диск.
CPU и системные вызовы
· user time (us): мин. 10%, мед. 12%, макс. 22% – умеренная загрузка пользовательскими процессами.
· system time (sy): мин. 6%, мед. 6%, макс. 9% – низкая, но растёт вместе с dirty pages (r=0.951).
· context switches (cs): от 30 977 до 47 774, тесно связаны с прерываниями и user time.
Блокировки и ожидания LWLock
· Незначимы (ВКО < 0.01) – не являются проблемой.
Анализ запросов (queryid)
· -2753873076014177353 (select scenario1):
o 17 млн вызовов, 12.2 млн ожиданий (85% всех ожиданий IO).
o События: DataFileRead, DataFileWrite, SlruRead.
· Другие запросы: scenario2, scenario3 – с малым числом ожиданий.
Ключевые проблемы
Проблемы СУБД
1. Чрезмерные ожидания ввода-вывода (IO) – 99.95% всех ожиданий приходится на DataFileRead.
2. Один запрос (scenario1) генерирует 85% всех ожиданий – требует немедленной оптимизации (план запроса, индексы, возможное переписывание).
3. Высокая корреляция записанных блоков с bo – запись на диск является узким местом, несмотря на высокий hit ratio.
Проблемы инфраструктуры
1. Перегрузка дискового ввода-вывода:
o Процессы в состоянии uninterruptible sleep (b) постоянно превышают число ядер CPU.
o Время ожидания IO (wa) >10% в 100% времени.
2. Накопление грязных страниц:
o Размер dirty pages сильно коррелирует с b и bo – фоновые механизмы записи не справляются.
o Высокая загрузка ядра (sy) из-за управления памятью.
3. Крайне малый объём свободной RAM (<5% всё время) – возможен дефицит памяти для кэша и буферов.
4. Рост очереди на выполнение (r) и падение idle CPU – система работает на пределе.
Рекомендации
Рекомендации по настройкам СУБД
· Оптимизировать запрос select scenario1():
o Проанализировать план выполнения, проверить наличие индексов, рассмотреть секционирование таблиц, уменьшение объёма сканируемых данных.
· Увеличить maintenance_work_mem и autovacuum_work_mem для ускорения очистки и уменьшения фоновой записи (но с учётом ограничений RAM).
· Рассмотреть увеличение shared_buffers (если позволяет RAM) – хотя hit ratio и так высок, дополнительный кэш может снизить чтения.
· Проверить эффективность current effective_io_concurrency (200) и random_page_cost (1.1) – для NVMe они оптимальны, но возможно стоит увеличить concurrency до 500.
· Включить track_io_timing (уже включён) для более детального анализа.
Рекомендации по настройкам операционной системы
· Снизить пороги dirty_background_ratio и dirty_ratio:
o Например, установить vm.dirty_background_ratio = 5 и vm.dirty_ratio = 15, чтобы сброс грязных страниц начинался раньше и не накапливался до критических значений.
· Уменьшить swappiness (уже 1) – оставить, но следить за использованием swap (сейчас 176 МБ занято).
· Проверить дисковую подсистему:
o Использовать iostat для выявления медленных устройств.
o Убедиться, что /data (где лежит PGDATA) и /wal находятся на быстрых NVMe-дисках (судя по настройкам, предполагается NVMe).
· Рассмотреть увеличение RAM – текущий объём 7.5 ГБ может быть недостаточным для рабочей нагрузки.
· Настроить планировщик ввода-вывода (например, использовать none или mq-deadline для NVMe).
Заключение
Система находится в состоянии серьёзной перегрузки по вводу-выводу, вызванной в первую очередь одним тяжёлым запросом и неоптимальными параметрами управления грязными страницами ОС. Несмотря на хорошее попадание в буферный кэш, объём записи на диск остаётся критическим, что приводит к блокировке процессов и росту системного времени.
Необходимо немедленно приступить к оптимизации запроса scenario1 и скорректировать параметры vm.dirty_*. Параллельно следует провести более глубокий анализ дискового планировщика и, при возможности, увеличить оперативную память. Выполнение этих мер позволит снизить нагрузку на диск и вернуть систему в стабильное состояние.