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

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

Периоды наблюдения:

• Период 1 (P1): 2026-03-11 14:35 – 15:35 (тестовый отрезок)
• Период 2 (P2): 2026-03-11 15:35 – 16:35 (инцидент производительности)

Используемые данные:

• Настройки СУБД и ОС (файл _1.settings.txt)
• Комплексный анализ СУБД и vmstat (файлы _2.1.test.postgresql_vmstat.txt, _2.postgresql_vmstat.txt)
• Анализ метрик vmstat-iostat по дисковым устройствам (файлы _3.1.test.vmstat_iostat.txt, _3.vmstat_iostat.txt)

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

• Версия СУБД: PostgreSQL 15.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1
• Аппаратная конфигурация:
  CPU: 192 логических ядра (Intel Xeon Platinum 8280L @ 2.70GHz, 4 сокета, 48 ядер/сокет, 1 поток/ядро)
  RAM: 1007.58 GB
  NUMA: 4 узла
• Ключевые параметры конфигурации СУБД:
  shared_buffers = 251807 MB (~246 GB)
  effective_cache_size = 747230 MB (~730 GB)
  work_mem = 1 GB
  maintenance_work_mem = 16 GB
  autovacuum_work_mem = 2 GB
  checkpoint_timeout = 15 min
  max_wal_size = 8 GB
  wal_keep_size = 500 GB
  random_page_cost = 1.1
  effective_io_concurrency = 300
  max_parallel_workers_per_gather = 0 (параллелизм запросов отключен)
  synchronous_commit = remote_write
• Дисковая подсистема:
  Отдельные устройства:
  vdg → /wal (WAL)
  vdh, vdi, vdj, vdk → /data (LVM datavg-datalv)
  vde → /log
  vdc → /backup
  Файловые системы на LVM, данные и WAL разделены.

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

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

Медианные значения за P1 (14:35–15:35):

• SPEED (запросы+строки): 11 902 820
• WAITINGS (суммарные ожидания): 326 902 257
• Extension: 59 219 875
• IO: 33 726 969
• IPC: 79 217 761
• Lock: 31 024 665
• LWLock: 127 259 602
• Timeout: 0
• BufferPin: 0

Медианные значения за P2 (15:35–16:35):

• SPEED (запросы+строки): 11 778 367
• WAITINGS (суммарные ожидания): 318 777 363
• Extension: 56 665 278
• IO: 32 835 679
• IPC: 70 793 441
• Lock: 31 158 102
• LWLock: 128 013 134
• Timeout: 0
• BufferPin: 0

Изменение от P1 к P2:

• SPEED: снижение на 1.05%
• WAITINGS: снижение на 2.48%
• Extension: снижение на 4.31%
• IO: снижение на 2.64%
• IPC: снижение на 10.63%
• Lock: рост на 0.43%
• LWLock: рост на 0.59%
• Timeout и BufferPin без изменений.

Комментарий:
Операционная скорость снизилась при одновременном снижении суммарных ожиданий. Снижение ожиданий IPC и IO может указывать на уменьшение соответствующей активности или изменение паттерна нагрузки. Рост медианы LWLock и Lock на фоне снижения SPEED является негативным сигналом.

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

Тренд SPEED (операционная скорость):

• P1:
  Коэффициент детерминации R²: 0.64 (качество модели хорошее)
  Угол наклона (b): +38.68 (рост)
• P2:
  Коэффициент детерминации R²: 0.94 (качество модели очень высокое)
  Угол наклона (b): -44.09 (падение)
• Комментарий: в P2 произошла смена тренда с роста на падение, причем модель в P2 объясняет 94% вариации.

Тренд WAITINGS (ожидания СУБД):

• P1:
  R²: 0.07 (непригодное качество модели)
  Угол наклона (b): -14.93 (незначим)
• P2:
  R²: 0.58 (удовлетворительное качество модели)
  Угол наклона (b): +37.28 (рост)
• Комментарий: в P2 ожидания стали устойчиво расти, тренд стал выраженным.

Регрессия SPEED по WAITINGS:

• P1:
  R²: 0.30 (слабое качество модели)
  Угол наклона (b): -28.68
• P2:
  R²: 0.59 (удовлетворительное качество модели)
  Угол наклона (b): -37.64
• Комментарий: связь усилилась, обратная зависимость стала круче.

Корреляция SPEED ~ WAITINGS:

• P1: коэффициент корреляции r = -0.5469 (умеренная обратная связь)
• P2: коэффициент корреляции r = -0.7712 (сильная обратная связь)
• Комментарий: обратная связь стала сильной, что подтверждает негативное влияние возросших ожиданий на производительность.

Вывод:
В P2 произошло качественное изменение: операционная скорость перешла от слабого роста к устойчивому падению (R²=0.94), а ожидания — от нестабильных к уверенному росту (R²=0.58). Обратная корреляция между скоростью и ожиданиями усилилась с умеренной до сильной, что подтверждает негативное влияние возросших ожиданий на производительность.

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

P1

• Тип ожидания LWLock:
  Взвешенная корреляция ожиданий (ВКО): 0.27 (критическое значение)
  Корреляция с общими ожиданиями (r): 0.9969
  Коэффициент детерминации R²: 0.99
  Интегральный приоритет: 0.3718 (единственный значимый тип)
• Остальные типы ожиданий (BufferPin, Extension, IO, IPC, Lock, Timeout): корреляция отсутствует или отрицательная.

P2

• Тип ожидания Extension:
  ВКО: 0.14 (высокое значение)
  r: 0.8118
  R²: 0.66 (хорошее качество модели)
  Интегральный приоритет: 0.6625
• Тип ожидания LWLock:
  ВКО: 0.30 (критическое значение)
  r: 0.7438
  R²: 0.55 (удовлетворительное качество модели)
  Интегральный приоритет: 0.5960
• Тип ожидания IO:
  ВКО: 0.08 (среднее значение)
  r: 0.8036
  R²: 0.65 (хорошее качество модели)
  Интегральный приоритет: 0.5691
• Тип ожидания Lock:
  ВКО: 0.08 (среднее значение)
  r: 0.8034
  R²: 0.65 (хорошее качество модели)
  Интегральный приоритет: 0.5690

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

• В P1 единственной критической точкой были легковесные блокировки (LWLock), объясняющие 99% вариации общих ожиданий.
• В P2 картина усложнилась: появились значимые корреляции для Extension, IO и Lock. LWLock сохранил критический уровень ВКО, но его объясняющая способность снизилась (R²=0.55). На первый план по интегральному приоритету вышли Extension и LWLock.
• Аномалия: в P2 типы ожиданий Extension, IO, Lock имеют практически идентичные коэффициенты корреляции (~0.80) и R² (~0.65). Это может указывать на общий фактор, влияющий на все три типа (например, рост интенсивности транзакционной нагрузки с участием расширений).

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

Метрика procs r (процессы в очереди на выполнение):

• P1:
  Медиана: 53
  R² тренда: 0.84 (очень высокое качество)
  Наклон: -42.44 (улучшение)
• P2:
  Медиана: 47 (снижение на 6 процессов)
  R² тренда: 0.22 (слабое качество)
  Наклон: +25.03 (ухудшение)
• Комментарий: тренд разрушился, направление сменилось на рост к концу периода.

Метрика procs b (процессы в uninterruptible sleep):

• P1:
  Медиана: 5
  R² тренда: 0.71 (хорошее качество)
  Наклон: +40.18 (рост)
• P2:
  Медиана: 5 (без изменений)
  R² тренда: 0.33 (слабое качество)
  Наклон: -29.77 (снижение)
• Комментарий: тренд ослаб, направление сменилось на улучшение.

Метрика cpu wa (процент простоя CPU в ожидании IO):

• P1:
  Медиана: 2%
  R² тренда: 0.00 (тренд отсутствует)
  Наклон: 0
• P2:
  Медиана: 3% (рост на 1 п.п.)
  R² тренда: 0.28 (слабое качество)
  Наклон: -27.89 (снижение)
• Комментарий: появился слабый тренд на снижение iowait.

Метрика cpu id (процент полного простоя CPU):

• P1:
  Медиана: 69%
  R² тренда: 0.28 (слабое качество)
  Наклон: +28.07 (рост idle)
• P2:
  Медиана: 72% (рост на 3 п.п.)
  R² тренда: 0.79 (хорошее качество)
  Наклон: -41.71 (падение idle)
• Комментарий: резкое ухудшение — сильный отрицательный тренд, классифицированный в отчете как «Очень высокая скорость изменения... указывают на серьёзные проблемы».

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

• Ключевое изменение: в P2 проявился сильный отрицательный тренд cpu idle (R²=0.79, наклон -41.71). Это означает, что свободное процессорное время устойчиво сокращалось на протяжении часа.
• Очередь исполнения (r) снизилась по медиане, но тренд стал положительным (растет к концу периода).
• Процессы в uninterruptible sleep (b) остались на уровне 5, но тренд P2 показывает их снижение, что не согласуется с ростом iowait и загрузки дисков (см. раздел IO). Возможно, процессы быстрее уходят из состояния D, но новые быстро поступают.
• Недостаточно данных: vmstat не предоставляет разбивки CPU на user/system в трендовом анализе, только граничные значения и медианы. Для точной оценки изменения соотношения us/sy требуются исходные временные ряды.

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

Корреляции в P1

• Extension ↔ us (user time):
  r = 0.6521 (высокая корреляция)
  R² = 0.43 (удовлетворительное качество модели)
  Интерпретация: расширения потребляют CPU в user space.
• Lock ↔ r (run queue):
  r = 0.8394 (очень высокая корреляция)
  R² = 0.70 (хорошее качество модели)
  Интерпретация: блокировки приводят к росту очереди процессов на CPU.
• cs ↔ in (переключения контекста и прерывания):
  r = 0.9379 (очень высокая корреляция)
  R² = 0.88 (очень высокое качество модели)
  Интерпретация: сильная связь переключений контекста с прерываниями.

Корреляции в P2

• Extension ↔ us (user time):
  r = 0.9051 (очень высокая корреляция)
  R² = 0.82 (очень высокое качество модели)
  Интерпретация: связь усилилась, расширения стали еще сильнее влиять на загрузку CPU в user mode.
• Extension ↔ in (прерывания):
  r = 0.8252 (очень высокая корреляция)
  R² = 0.68 (хорошее качество модели)
  Интерпретация: новая значимая связь, возможно, из-за интенсивного взаимодействия расширений с внешними ресурсами.
• Lock ↔ r (run queue):
  r = 0.5774 (высокая корреляция)
  R² = 0.33 (слабое качество модели)
  Интерпретация: связь ослабла, модель стала слабой.
• cs ↔ in: корреляция исчезла (стала незначимой).

Относительные показатели vmstat

• free RAM < 5%:
  P1: 100% времени (ALARM)
  P2: 100% времени (ALARM)
  Комментарий: свободной памяти стабильно менее 5% (≈13 GB при 1007 GB). Swap не используется. Вся память занята под файловый кэш (memory_cache ~720 GB) и, вероятно, shared_buffers (246 GB). Это нормально для heavily loaded PostgreSQL, но требует мониторинга на предмет вытеснения страниц.

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

• В P2 усилилось влияние расширений на CPU (us) и появилась связь с прерываниями. Это может указывать на активизацию расширений, интенсивно взаимодействующих с внешними ресурсами (сеть, файлы) через системные вызовы.
• Связь блокировок Lock с очередью run queue ослабла, что при росте LWLock и сохранении Lock на уровне говорит о смещении конкуренции в сторону легковесных блокировок.

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

Основные изменения в WAIT_EVENT

P1:

• Extension: 100% (событие Extension)
• LWLock:
  BufferMapping: 31.68%
  WALWrite: 25.18%
  pgpro_stats: 19.99%
• IO: DataFileRead: 83.5%
• Lock: transactionid: 85.84%

P2:

• Extension: 100% (событие Extension)
• LWLock:
  BufferMapping: 30.07%
  WALWrite: 26.07%
  pgpro_stats: 18.17%
• IO:
  DataFileRead: 78.28%
  WALSync: 11.03% (появились ожидания)
• Lock:
  transactionid: 78.97%
  tuple: 20.41% (появились блокировки)

Вывод:
В P2 в IO добавились ожидания WALSync (11% от всех IO), а в Lock — блокировки tuple (20.4%). Это коррелирует с ростом нагрузки на WAL (устройство vdg) и усилением конкуренции за строки.

QUERYID по типам ожиданий

Доминирующие запросы в обоих периодах:

• Запрос -5038981907002478858 (CALLS ~38.8 млн в P2)
• Запрос -4280293605113329019 (CALLS ~17.5 млн в P2)
• Запрос -1757223094415174739 (CALLS ~9.2 млн)

Изменения в P2:

• Доля ожиданий Extension для запроса -5038981907002478858: рост с 8.51% до 33.69% от всех Extension.
• Доля ожиданий LWLock для этого же запроса: рост с 6.10% до 32.24% от всех LWLock.
• Появились значимые ожидания Lock/tuple у всех топ-запросов (в P1 tuple отсутствовал).

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

• Смена характера нагрузки: запрос -5038981907002478858 стал основным генератором ожиданий Extension и LWLock.
• Появление WALSync и tuple указывает на увеличение интенсивности записи WAL и конфликтов на уровне строк, вероятно, из-за возросшего числа UPDATE/DELETE или конкурентных вставок.

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

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

• LWLock остается критическим в обоих периодах, но в P2 его влияние на общие ожидания снизилось с 99% до 55% (по R²). Основные события — BufferMapping, WALWrite, pgpro_stats.
• Extension вышел на первый план по интегральному приоритету в P2 (0.6625). Сильно коррелирует с user time CPU (r=0.91) и прерываниями (r=0.83).
• IO и Lock в P2 приобрели умеренную значимость (ВКО=0.08), коррелируя с общими ожиданиями на уровне ~0.80.

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

• shared_buffers = 246 GB при общем RAM 1007 GB — соответствует ~24% ОЗУ, что в пределах рекомендаций для систем с большим объемом памяти.
• effective_cache_size = 730 GB — адекватно для планировщика, учитывая объем кэша страниц ОС (~720 GB в memory_cache).
• Потенциальное несоответствие: при maintenance_work_mem = 16 GB и autovacuum_work_mem = 2 GB, work_mem = 1 GB на запрос. Учитывая высокую конкуренцию за LWLock (BufferMapping) и наличие 192 ядер, большое количество одновременных сессий с высоким work_mem может приводить к давлению на память, но данных о количестве активных сессий нет.

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

• vdg (WAL): в P2 значительно выросли операции записи (w/s медиана с 373 до 689, wMB/s с 7.75 до 16.0), при этом util остался низким (до 10%). Появились ожидания WALSync.
• vdh, vdi, vdj, vdk (/data): в P2 util увеличился до 90%+ (в P1 было ~87%), aqu_sz вырос с ~2.0 до 2.5+, r/s и w/s значительно возросли. Корреляция vmstat/wa с iostat/util стала высокой (r ~0.80–0.84), что указывает на прямую связь ожиданий IO с загрузкой устройств.

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

• В P2 тренд cpu idle резко пошел вниз (наклон -41.71). При этом cpu us и cpu sy в абсолютных значениях оставались низкими (медиана us 20–23%, sy 5%). Это может означать, что снижение idle связано с ростом других компонент (возможно, iowait или steal, но steal=0). Рост wa с 2% до 3% лишь частично объясняет снижение idle.
• Недостаточно данных: детализация по CPU (гостевые метрики, детальный профиль) отсутствует.

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

• LWLock BufferMapping — основной источник ожиданий. Указывает на конкуренцию за доступ к буферному кэшу. Возможные причины: высокая частота обращений к одним и тем же страницам, недостаточное количество буферных разделов (lwlock partitioning).
• WALWrite LWLock — конкуренция при записи WAL. Рост в P2 коррелирует с увеличением записи на vdg.
• pgpro_stats LWLock — ожидания внутри расширения pgpro_stats (сбор статистики). Занимает ~18% всех LWLock.

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

• Запрос -5038981907002478858 выделяется аномальным ростом ожиданий Extension и LWLock. Вероятно, содержит вызовы функций расширений или генерирует высокую нагрузку на буферный кэш/WAL.
• Запрос -4280293605113329019 также демонстрирует высокую активность по всем типам ожиданий, но его доля в P2 снизилась по сравнению с -503898....

Ограничения:
Отсутствуют планы выполнения запросов и тексты SQL. Невозможно определить, какие именно операции вызывают конкуренцию. Требуется трассировка запросов с auto_explain (включено, но данные не предоставлены).

2. Анализ IO

Список дисковых устройств

• vdg — WAL
• vdh, vdi, vdj, vdk — данные (LVM datavg-datalv)

Граничные значения по дисковым устройствам (медианы)

Устройство vdg:

• P1:
  w/s: 372.7
  wMB/s: 7.75
  util %: 5.92
• P2:
  w/s: 689.2 (рост на 85%)
  wMB/s: 16.0 (рост на 106%)
  util %: 10.19 (рост на 72%)

Устройство vdh:

• P1:
  r/s: 12194
  w/s: 501.6
  util %: 86.98
  aqu_sz: 2.03
• P2:
  r/s: 13554 (рост на 11%)
  w/s: 1093.2 (рост на 118%)
  util %: 90.26 (рост на 3.8%)
  aqu_sz: 2.54 (рост на 25%)

Устройство vdi:

• P1:
  r/s: 12183
  w/s: 502.6
  util %: 87.16
  aqu_sz: 2.01
• P2:
  r/s: 13535 (рост на 11%)
  w/s: 1094.0 (рост на 118%)
  util %: 89.87 (рост на 3.1%)
  aqu_sz: 2.51 (рост на 25%)

Устройство vdj:

• P1:
  r/s: 12181
  w/s: 498.3
  util %: 86.45
  aqu_sz: 2.00
• P2:
  r/s: 13528 (рост на 11%)
  w/s: 1097.2 (рост на 120%)
  util %: 90.03 (рост на 4.1%)
  aqu_sz: 2.53 (рост на 26%)

Устройство vdk:

• P1:
  r/s: 12189
  w/s: 500.8
  util %: 87.20
  aqu_sz: 2.05
• P2:
  r/s: 13544 (рост на 11%)
  w/s: 1089.6 (рост на 118%)
  util %: 90.33 (рост на 3.6%)
  aqu_sz: 2.51 (рост на 22%)

Относительные показатели iostat

• %util > 50%: 100% времени для vdh, vdi, vdj, vdk в обоих периодах (ALARM). В P2 значения util выросли до 90%+.
• aqu_sz > 1: 100% времени для устройств данных в обоих периодах (ALARM). В P2 средняя очередь увеличилась с ~2.0 до ~2.5.
• r_await, w_await: оставались низкими (<0.3 мс), что указывает на быстрое обслуживание запросов накопителями (вероятно, NVMe/SSD). Рост очереди при низких задержках характерен для высокопараллельной нагрузки.

2.1. СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ "КОРРЕЛЯЦИЯ VMSTAT и IOSTAT" по дисковым устройствам

2.1.1 КОРРЕЛЯЦИЯ VMSTAT и IOSTAT (wa ~ util)

P1: анализ не проводился (отсутствует в отчете).

P2:

• Устройство vdg: r = 0.745, R² = 0.56
• Устройство vdh: r = 0.840, R² = 0.70
• Устройство vdi: r = 0.842, R² = 0.71
• Устройство vdj: r = 0.802, R² = 0.64
• Устройство vdk: r = 0.824, R² = 0.68

Итог по 1.1 КОРРЕЛЯЦИЯ VMSTAT и IOSTAT:
В P2 на всех устройствах данных появилась сильная корреляция между ожиданиями IO на уровне ОС (wa) и загрузкой устройства (util).

2.1.2 БУФЕРИЗАЦИЯ ВВОДА-ВЫВОДА (vmstat buff ~ iostat)

P1:

• На устройствах данных (vdh, vdi, vdj, vdk):
  Корреляция buff с r/s и rMB/s: r ~0.94–0.95, R² ~0.89–0.91 (очень высокая).
• Интерпретация: активное использование буферного кэша ОС при чтении.

P2:

• Эти корреляции разрушились (стали незначимыми или слабыми).
• Появилась умеренная связь buff с wps на vdg: r = 0.51, R² = 0.26.
• Интерпретация: изменение паттерна ввода-вывода — в P2 возросла запись, и буферный кэш стал использоваться иначе.

Итог по 1.2 БУФЕРИЗАЦИЯ ВВОДА-ВЫВОДА:
В P2 произошло разрушение сильных корреляций buff с чтением на устройствах данных и появление слабой связи с записью на WAL.

2.1.3 КЭШИРОВАНИЕ ВВОДА-ВЫВОДА (vmstat cache ~ iostat)

P1:

• Высокая корреляция cache с w/s и wMB/s на всех устройствах данных: r ~0.80, R² ~0.64–0.65.

P2:

• Корреляция cache с метриками чтения (r/s, rMB/s) усилилась: r = 0.70–0.71, R² = 0.49–0.50.
• Корреляция cache с записью осталась на уровне: r ~0.70–0.76, R² = 0.50–0.57.
• Интерпретация: связь стала более сбалансированной между чтением и записью.

Итог по 1.3 КЭШИРОВАНИЕ ВВОДА-ВЫВОДА:
В P2 кэш страниц стал более равномерно связан как с чтением, так и с записью.

2.1.4 КОРРЕЛЯЦИЯ ОПЕРАЦИОННОЙ СКОРОСТИ И МЕТРИК ПРОИЗВОДИТЕЛЬНОСТИ ДИСКОВОГО УСТРОЙСТВА

Устройство vdg:

• P1: r = 0.776, R² = 0.60 (с MBps)
• P2: r = 0.662, R² = 0.44 (с MBps)

Устройство vdh:

• P1: r = 0.464, R² = 0.22 (с MBps)
• P2: r = 0.732, R² = 0.54 (с MBps) — очень высокая корреляция

Устройство vdi:

• P1: r = 0.462, R² = 0.21 (с MBps)
• P2: r = 0.733, R² = 0.54 (с MBps) — очень высокая корреляция

Устройство vdj:

• P1: r = 0.462, R² = 0.21 (с MBps)
• P2: r = 0.732, R² = 0.54 (с MBps) — очень высокая корреляция

Устройство vdk:

• P1: r = 0.464, R² = 0.22 (с MBps)
• P2: r = 0.732, R² = 0.54 (с MBps) — очень высокая корреляция

Ключевое изменение:
В P2 на устройствах данных появилась очень высокая корреляция операционной скорости с пропускной способностью записи (MBps). R² ~0.54 указывает, что ~54% вариации операционной скорости объясняется объемом записываемых данных. В P1 связь была слабой. Это прямой признак того, что в P2 производительность СУБД стала ограничена пропускной способностью дисковой подсистемы данных.

Итог по 1.4 КОРРЕЛЯЦИЯ ОПЕРАЦИОННОЙ СКОРОСТИ И МЕТРИК ПРОИЗВОДИТЕЛЬНОСТИ ДИСКОВОГО УСТРОЙСТВА:
В P2 операционная скорость стала сильно зависеть от объема записи на диски данных, что подтверждает насыщение дисковой подсистемы.

ИНДЕКС ПРИОРИТЕТА КОРРЕЛЯЦИИ (CPI)

P1 (доминирующие корреляции):

• buff ~ rMB/s на устройствах данных: CPI ~0.954

P2 (доминирующие корреляции):

• wa ~ util на устройствах данных: CPI 0.80–0.84
• cache ~ wMB/s на устройствах данных: CPI 0.57–0.64

Итог по разделу "СРАВНИТЕЛЬНЫЙ СТАТИСТИЧЕСКИЙ АНАЛИЗ "КОРРЕЛЯЦИЯ VMSTAT и IOSTAT" по дисковым устройствам"

• В P2 произошел сдвиг нагрузки на запись: резко выросли w/s и wMB/s на всех устройствах.
• Устройства данных (vdh–vdk) достигли утилизации >90%, очередь запросов выросла.
• Появилась сильная корреляция операционной скорости с объемом записи, что подтверждает, что дисковая подсистема стала узким местом.

Проблемы инфраструктуры по итогам сравнительного анализа

1. Высокая утилизация устройств данных (>90% в P2) и рост очереди запросов.
2. Увеличение объема записи на WAL (vdg) в 2 раза, сопровождающееся появлением ожиданий WALSync.
3. Исчерпание свободной памяти (<5% свободно) при отсутствии swap — риск вытеснения страниц из кэша при пиковых нагрузках.
4. Устойчивое снижение CPU idle в P2, указывающее на рост общей загрузки системы.

3. Итог

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

1. Деградация операционной скорости в P2 с одновременным ростом ожиданий СУБД.
2. Дисковая подсистема данных стала узким местом: утилизация >90%, высокая корреляция SPEED с MBps записи.
3. Резкое увеличение нагрузки на WAL (в 2 раза), появление ожиданий WALSync.
4. Критический уровень ожиданий LWLock в обоих периодах, в P2 добавился высокий приоритет Extension.
5. Появление блокировок tuple в P2, указывающее на конкуренцию за строки в транзакциях.
6. Аномальное поведение запроса -5038981907002478858, генерирующего основную долю ожиданий Extension и LWLock в P2.

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

• Конкуренция за LWLock BufferMapping — возможен дефицит буферных разделов (lwlock_shared_limit не задан, используется по умолчанию).
• Высокая активность расширения pgpro_stats, создающая LWLock ожидания.
• Отсутствие параллелизма в запросах (max_parallel_workers_per_gather = 0) — потенциально неэффективное использование CPU для сканирований.
• Возможное влияние расширений (Extension) на CPU user time и прерывания — требуется анализ кода расширений и вызываемых функций.

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

• Насыщение пропускной способности дисков данных при текущем объеме записи.
• Увеличенная нагрузка на WAL-устройство, хотя его утилизация остается низкой, рост объема записи может влиять на синхронизацию.
• Недостаток свободной памяти для дополнительного кэширования, но текущий объем кэша (720 GB) близок к effective_cache_size (730 GB). Риск связан с возможным вытеснением страниц при пиковых аллокациях.

4. Общие рекомендации по оптимизации производительности СУБД и инфраструктуры

Важно: Рекомендации формулируются как направления для дальнейшей диагностики и возможных изменений, без конкретных значений параметров, поскольку окончательные решения требуют дополнительных данных и тестирования.

4.1 Конкретные рекомендации по оптимизации производительности СУБД

1. Исследовать запрос -5038981907002478858:
   Получить план выполнения и трассировку с auto_explain.log_analyze.
   Проверить, какие расширения используются в запросе, оценить их необходимость и эффективность.
2. Анализ LWLock BufferMapping:
   Проверить статистику по pg_stat_bgwriter на предмет buffers_backend_fsync и buffers_alloc.
   Оценить эффективность увеличения shared_buffers (текущие 246 GB при ~720 GB кэша ОС могут быть увеличены, но это требует тестирования).
   Рассмотреть увеличение числа буферных разделов через параметр lwlock_shared_limit (только после анализа конкуренции).
3. Оптимизация работы с WAL:
   Изучить причины роста записи WAL: возможно, увеличилась частота UPDATE/DELETE или количество транзакций.
   Проверить настройки wal_writer_delay, wal_writer_flush_after.
   Рассмотреть возможность группировки мелких транзакций в приложении.
4. Блокировки tuple:
   Выявить запросы, вызывающие конфликты на уровне строк (анализ логов, pg_stat_user_tables, pg_locks).
   Проверить индексы на часто обновляемых таблицах — отсутствие индексов может приводить к блокировкам большого числа строк.
5. Параллелизм:
   Оценить возможность включения параллельного выполнения запросов (max_parallel_workers_per_gather > 0) для операций, которые могут выиграть от распараллеливания (seq scan больших таблиц). Учитывая 192 ядра, потенциал значительный.
6. Расширение pgpro_stats:
   Оценить накладные расходы на сбор детальной статистики. При необходимости уменьшить частоту сбора или детализацию.

4.2 Конкретные рекомендации по оптимизации инфраструктуры

1. Дисковая подсистема данных:
   Провести профилирование ввода-вывода (iostat -x 1, pidstat -d) для выявления источников нагрузки.
   Рассмотреть возможность добавления более производительных дисков или увеличения числа устройств в LVM-группе.
   Проверить, сбалансирована ли нагрузка между устройствами vdh–vdk (по медианам выглядит равномерно).
2. WAL-устройство:
   Убедиться, что vdg действительно выделено исключительно под WAL и не конкурирует с другими процессами.
   Мониторить задержки записи (w_await). При росте задержек может потребоваться более быстрое устройство.
3. Память:
   Несмотря на ALARM по свободной памяти (<5%), текущая ситуация допустима для dedicated сервера БД, если не наблюдается подкачки (swap=0). Рекомендуется контролировать memory_free и memory_cache на предмет резких провалов кэша.
4. CPU:
   Исследовать причину устойчивого снижения cpu idle в P2 с помощью детального мониторинга (mpstat -P ALL, perf top). Убедиться, что нет роста steal из-за гипервизора (KVM).

5. Заключение

В период 15:35–16:35 (P2) зафиксировано значительное ухудшение производительности СУБД по сравнению с предыдущим часом (P1). Основные признаки:

• Операционная скорость перешла к устойчивому падению (R²=0.94).
• Ожидания СУБД стали уверенно расти (R²=0.58), усилилась их обратная корреляция со скоростью (r = -0.77).
• Критическими типами ожиданий стали LWLock (ВКО=0.30) и Extension (ВКО=0.14).
• Дисковая подсистема данных достигла утилизации >90%, а операционная скорость стала сильно коррелировать с объемом записи на диск (r=0.73, R²=0.54).
• Нагрузка на WAL возросла вдвое, появились ожидания WALSync.
• Выделен проблемный запрос -5038981907002478858, генерирующий основную долю ожиданий Extension и LWLock.

Требуются дополнительные данные для углубленного анализа:

• Планы выполнения и трассировка топ-запросов.
• Детализация по CPU (распределение us/sy/wa во времени).
• Статистика pg_stat_bgwriter, pg_stat_database, pg_stat_user_tables.
• Информация о количестве одновременных сессий и их состояниях.
• Логи PostgreSQL за указанные периоды (особенно сообщения о checkpoint, autovacuum, блокировках).

На основе полученных данных рекомендуется сфокусироваться на оптимизации выявленного запроса, анализе конкуренции за легковесные блокировки и оценке пропускной способности дисковой подсистемы данных.