2.Philosophical_instruction_BETA_v4

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

• Версия СУБД: 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 🟢
• Дисковая подсистема:
  WAL: отдельное устройство vdg 🟢
  Данные: LVM datavg-datalv на устройствах vdh, vdi, vdj, vdk 🟢
  Файловые системы: данные и WAL разделены 🟢
• Периоды наблюдения:
  P1 (базовый): 2026-03-11 14:35 – 15:35 🟢
  P2 (инцидент): 2026-03-11 15:35 – 16:35 🟢

Ключевые проблемы СУБД и инфраструктуры

На основе сравнительного анализа метрик P1 и P2 выявлены следующие критические изменения и узкие места.

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

• 🟢 Операционная скорость (SPEED) в P2 снизилась на 1.05% по медиане, при этом тренд сменился с роста (наклон +38.68, R²=0.64) на устойчивое падение (наклон -44.09, R²=0.94).
• 🟢 Суммарные ожидания (WAITINGS) в P2 перешли от нестабильного поведения (R²=0.07) к выраженному росту (наклон +37.28, R²=0.58).
• 🟢 Обратная корреляция SPEED ~ WAITINGS усилилась с умеренной (r = -0.5469) до сильной (r = -0.7712), подтверждая, что рост ожиданий стал значимо ограничивать производительность.

2. Изменение профиля ожиданий СУБД

• 🟢 В P1 единственным критическим типом ожиданий был LWLock (ВКО=0.27, R²=0.99).
• 🟡 В P2 LWLock сохранил критический уровень (ВКО=0.30), но его объясняющая способность снизилась до R²=0.55.
• 🟢 На первый план по интегральному приоритету вышли Extension (0.6625) и IO (0.5691).
• 🟡 Типы Extension, IO и Lock демонстрируют практически идентичные коэффициенты корреляции с общими ожиданиями (~0.80) и R² (~0.65), что может указывать на общий фактор (например, рост интенсивности транзакций с использованием расширений).
• 🔴 Появление в P2 ожиданий WALSync (11% от всех IO-ожиданий) и блокировок tuple (20.4% от всех Lock) свидетельствует об увеличении нагрузки на WAL и конкуренции за строки.

3. Рост нагрузки на дисковую подсистему

• 🟢 Устройства данных (vdh–vdk) в P2 достигли утилизации >90% (медиана util выросла до ~90%), при этом очередь запросов (aqu_sz) увеличилась с ~2.0 до ~2.5.
• 🟢 Объем записи на устройства данных вырос более чем в 2 раза (медиана w/s с ~500 до ~1090, wMB/s с ~10.5 до ~23.0).
• 🟢 На устройстве WAL (vdg) также зафиксирован двукратный рост записи (w/s с 373 до 689, wMB/s с 7.75 до 16.0).
• 🟢 В P2 появилась сильная корреляция операционной скорости с объемом записи на диски данных (r ~0.73, R² ~0.54), что прямо указывает на насыщение пропускной способности дисковой подсистемы данных как на ограничивающий фактор.
• 🟢 Корреляция vmstat/wa (ожидания ввода-вывода ОС) с iostat/util в P2 стала высокой (r=0.80–0.84), подтверждая прямую связь загрузки устройств с простоями CPU на I/O.

4. Конкуренция за легковесные блокировки (LWLock)

• 🟢 Основные источники LWLock в обоих периодах:
  BufferMapping (~31% от всех LWLock) — конкуренция за доступ к буферному кэшу.
  WALWrite (~25–26%) — конкуренция при записи WAL.
  pgpro_stats (~18–20%) — ожидания внутри расширения сбора статистики.
• 🟡 Конкуренция за BufferMapping при shared_buffers = 246 GB и активном кэше ОС ~720 GB может указывать на недостаточное количество буферных разделов (lwlock_shared_limit по умолчанию) или на высокую частоту обращений к одним и тем же страницам.

5. Аномальная активность конкретного запроса

• 🟢 Запрос с queryid -5038981907002478858 в P2 стал доминирующим генератором ожиданий:
  Доля в ожиданиях Extension выросла с 8.51% до 33.69%.
  Доля в ожиданиях LWLock выросла с 6.10% до 32.24%.
• 🔴 Текст запроса и план выполнения отсутствуют, поэтому точная причина аномалии не установлена. Предположительно запрос активно использует функции расширений и создаёт высокую нагрузку на буферный кэш и WAL.

6. Использование CPU и памяти

• 🟢 В P2 тренд cpu idle резко пошёл вниз (наклон -41.71, R²=0.79), что указывает на устойчивый рост загрузки CPU.
• 🟡 При этом медианные значения us (user) и sy (system) изменились незначительно (us: 20%→23%, sy: 5%→5%), что требует уточнения — вероятно, рост загрузки связан с увеличением времени обработки прерываний или другими компонентами, не отражёнными в агрегированных данных.
• 🟢 Свободная память стабильно ниже 5% (около 13 GB), но swap не используется, что допустимо для dedicated сервера БД при условии отсутствия вытеснения страниц из кэша.
• 🟡 Корреляция Extension с us (r=0.91) и с прерываниями in (r=0.83) в P2 подтверждает влияние расширений на загрузку CPU в userspace и генерацию системных вызовов.

Рекомендации по оптимизации СУБД и инфраструктуры

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

СУБД

1. Анализ проблемного запроса -5038981907002478858 🟡
   Получить план выполнения (EXPLAIN (ANALYZE, BUFFERS, TIMING)) и текст запроса.
   Определить, какие расширения вызываются, и оценить их необходимость и эффективность.
   Проверить наличие частых обновлений/вставок, генерирующих избыточную запись WAL.
2. Снижение конкуренции за LWLock BufferMapping 🟡
   Проверить статистику pg_stat_bgwriter (поля buffers_alloc, buffers_backend_fsync).
   Рассмотреть увеличение shared_buffers (текущие 246 GB при 720 GB кэша ОС) после нагрузочного тестирования.
   Изучить возможность увеличения lwlock_shared_limit (только после подтверждения дефицита разделов через детальную трассировку LWLock).
   Оценить, можно ли уменьшить количество одновременных сессий или перераспределить нагрузку.
3. Оптимизация работы с WAL 🟡
   Выявить причины роста записи WAL (частота UPDATE/DELETE, количество транзакций).
   Проверить настройки wal_writer_delay, wal_writer_flush_after, synchronous_commit (текущий remote_write уже снижает задержки).
   При возможности группировать мелкие транзакции на стороне приложения.
4. Блокировки tuple 🟡
   Проанализировать логи PostgreSQL на предмет сообщений о блокировках.
   Исследовать pg_stat_user_tables для выявления таблиц с высоким числом n_tup_upd и n_tup_del.
   Проверить наличие индексов, поддерживающих WHERE условий в UPDATE/DELETE, для минимизации блокируемых строк.
5. Параллелизм запросов 🟡
   Рассмотреть включение max_parallel_workers_per_gather (>0) для операций seq scan крупных таблиц. Наличие 192 ядер делает это потенциально эффективным.
   Оценить нагрузку на CPU и I/O при включении параллелизма в тестовой среде.
6. Расширение pgpro_stats 🟡
   Оценить накладные расходы на сбор детальной статистики (LWLock pgpro_stats занимает ~18% всех LWLock).
   При необходимости скорректировать частоту сбора или уровень детализации.

Инфраструктура

1. Дисковая подсистема данных 🟡
   Профилировать ввод-вывод на уровне отдельных процессов (pidstat -d, iotop) для выявления источников нагрузки.
   Проверить балансировку нагрузки между устройствами vdh–vdk (по медианам выглядит равномерно).
   Рассмотреть увеличение пропускной способности: добавление более производительных дисков, увеличение количества устройств в LVM-группе или переход на более быстрый тип хранилища.
   Проверить, не является ли текущая нагрузка пиковой и не требует ли она только локальной оптимизации запросов.
2. Устройство WAL 🟢
   Убедиться, что vdg используется исключительно под WAL.
   Мониторить w_await — при росте выше нескольких миллисекунд может потребоваться более быстрое устройство.
3. Память 🟢
   Текущая ситуация со свободной памятью (<5%) допустима, если не происходит активного вытеснения страниц (pg_stat_bgwriter.buffers_backend_fsync и buffers_alloc). Рекомендуется настроить алерт на резкое снижение memory_cache или появление swap.
4. CPU 🟡
   Для уточнения причины снижения cpu idle в P2 провести детальный мониторинг с помощью mpstat -P ALL и perf top.
   Исключить влияние steal (гипервизор KVM).

Необходимая дополнительная информация для продолжения анализа и оптимизации производительности СУБД и инфраструктуры

Для углублённой диагностики и выработки точных рекомендаций требуются следующие данные:

• 🟡 Тексты и планы выполнения топ-запросов (особенно queryid -5038981907002478858), полученные с помощью auto_explain (log_analyze=on, log_buffers=on).
• 🟡 Детализация по CPU за оба периода в разрезе us, sy, wa, id, st с временными рядами (например, из sar -u).
• 🟡 Статистика PostgreSQL:
  pg_stat_bgwriter (особенно поля buffers_alloc, buffers_backend, buffers_backend_fsync, buffers_clean).
  pg_stat_database (конфликты, количество транзакций, blk_read_time, blk_write_time).
  pg_stat_user_tables и pg_stat_user_indexes для оценки паттернов доступа.
• 🟡 Логи PostgreSQL за периоды P1 и P2 с уровнем log_min_messages = warning (или более детальным) для выявления сообщений о checkpoint, autovacuum, deadlocks, long transactions.
• 🟡 Информация о количестве одновременных сессий и их состояниях (из pg_stat_activity) в моменты пиковой нагрузки.
• 🟡 Данные о работе autovacuum: частота срабатывания, длительность, объёмы обработанных строк (из pg_stat_all_tables или логов).
• 🟡 Профиль использования расширений (pg_extension, список установленных расширений, частотность вызовов их функций).
• 🟡 Детализированные метрики LWLock (например, из pg_stat_lwlock или системных представлений расширения pgpro_stats), чтобы точно определить, на каких именно блокировках происходит конкуренция внутри BufferMapping.

Без указанных данных дальнейший анализ будет ограничен предположениями (🔴/🟡), а рекомендации не смогут быть верифицированы.