1.Philosophical_instruction_v3_5.txt

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

• Периоды наблюдения:
  P1 (тестовый): 2026-03-11 14:35 – 15:35
  P2 (инцидент): 2026-03-11 15:35 – 16:35
• Версия СУБД: PostgreSQL 15.13 on x86_64-pc-linux-gnu, compiled by gcc 11.4.1
• Аппаратная конфигурация:
  CPU: 192 логических ядра (Intel Xeon Platinum 8280L @ 2.70 GHz, 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_parallel_workers_per_gather = 0 (параллелизм запросов отключен)
  synchronous_commit = remote_write
  random_page_cost = 1.1
  effective_io_concurrency = 300
• Дисковая подсистема:
  WAL: отдельное устройство vdg
  Данные: LVM на устройствах vdh, vdi, vdj, vdk

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

Ниже приведены выводы, основанные исключительно на данных сравнительного отчета. Статус достоверности каждого утверждения оценен согласно эпистемическим светофорам (🟢 — проверено по источнику / детерминированной логике; 🟡 — правдоподобно, но основано на вторичной агрегации или требует дополнительного подтверждения; 🔴 — основано на предположении или устаревших данных; ⬛ — недостаточно данных). В данном анализе все выводы базируются на обработанных метриках отчета, поэтому преобладает 🟡.

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

• 🟡 В P2 операционная скорость перешла от слабого роста (наклон +38.68, R²=0.64) к устойчивому падению (наклон –44.09, R²=0.94).
• 🟡 Суммарные ожидания СУБД (WAITINGS) в P2 демонстрируют выраженный восходящий тренд (наклон +37.28, R²=0.58) против нестабильного тренда в P1.
• 🟡 Корреляция SPEED ↔ WAITINGS усилилась с умеренной обратной (r = –0.55 в P1) до сильной обратной (r = –0.77 в P2).
• 🟢 Статистически значимые типы ожиданий в P2 (по интегральному приоритету):
  Extension (ВКО=0.14, r=0.81, R²=0.66)
  LWLock (ВКО=0.30, r=0.74, R²=0.55)
  IO (ВКО=0.08, r=0.80, R²=0.65)
  Lock (ВКО=0.08, r=0.80, R²=0.65)

2. Дисковая подсистема данных как узкое место

• 🟢 Утилизация устройств данных (vdh–vdk) в P2: медиана 89.9–90.3%, пиковые значения >90%.
• 🟢 Очередь запросов (aqu_sz) выросла с ~2.0 до ~2.5 при сохранении низких задержек обслуживания (r_await / w_await <0.3 мс).
• 🟢 Операционная скорость в P2 сильно коррелирует с объемом записи на устройства данных (r ≈ 0.73, R² ≈ 0.54). В P1 связь была слабой (R² ≈ 0.22).
• 🟡 На устройствах данных появилась сильная корреляция между vmstat/wa и iostat/util (r 0.80–0.84), указывающая на прямую зависимость ожиданий ввода-вывода от загрузки дисков.

3. Резкий рост нагрузки на WAL

• 🟢 На устройстве vdg (WAL) операции записи выросли в P2:
  w/s: с 373 до 689 (+85%)
  wMB/s: с 7.75 до 16.0 (+106%)
• 🟢 В ожиданиях типа IO появилась значимая доля события WALSync (11.03% в P2 против отсутствия в P1).
• 🟡 Корреляция между операционной скоростью и пропускной способностью WAL в P2 снизилась (R² с 0.60 до 0.44), что может свидетельствовать о том, что WAL не является прямым ограничителем, но рост записи на него — симптом увеличения транзакционной активности.

4. Проблемный запрос -5038981907002478858

• 🟢 Запрос демонстрирует аномальный рост доли ожиданий в P2:
  Extension: с 8.51% до 33.69% от всех ожиданий Extension
  LWLock: с 6.10% до 32.24% от всех ожиданий LWLock
• 🟡 Текст запроса и план выполнения в отчете отсутствуют, поэтому точная причина роста ожиданий не может быть установлена.
• 🟡 Доля вызовов запроса (~38.8 млн в P2) и его вклад в общие ожидания указывают на высокую вероятность того, что оптимизация именно этого запроса даст наибольший эффект.

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

• 🟢 Основные события LWLock в P2:
  BufferMapping: 30.07%
  WALWrite: 26.07%
  pgpro_stats: 18.17%
• 🟡 Ожидания BufferMapping указывают на конкуренцию за доступ к страницам буферного кэша. При 246 GB shared_buffers и 720 GB кэша ОС это может быть следствием высокой частоты обращений к ограниченному набору страниц или недостаточного количества буферных разделов (параметр lwlock_shared_limit не задан, используется значение по умолчанию).
• 🟡 Ожидания WALWrite коррелируют с ростом записи WAL.
• 🟡 Ожидания pgpro_stats связаны с работой расширения сбора статистики. Накладные расходы расширения в условиях высокой конкурентной нагрузки могут быть значительными.

6. Блокировки на уровне строк (tuple locks)

• 🟢 В P2 появились ожидания Lock/tuple, составляющие 20.41% от всех ожиданий Lock (в P1 tuple отсутствовал).
• 🟡 Это свидетельствует о возросшей конкуренции за одни и те же строки в транзакциях, вероятно, при выполнении UPDATE/DELETE или SELECT FOR UPDATE.

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

• 🟢 Свободная память (free) стабильно <5% в обоих периодах, swap не используется.
• 🟡 Объем файлового кэша (memory_cache ~720 GB) близок к effective_cache_size (730 GB). Ситуация типична для heavily loaded сервера БД, но требует мониторинга на предмет вытеснения страниц при пиковых аллокациях.
• 🟢 Тренд cpu idle в P2 показывает устойчивое снижение (наклон –41.71, R²=0.79). Медианные значения cpu us (20–23%) и cpu sy (5%) изменились незначительно, рост cpu wa с 2% до 3% объясняет снижение idle лишь частично.
• 🟡 Детализация по CPU (распределение между процессами, возможный steal time) отсутствует, что не позволяет точно определить причину снижения idle.

8. Отсутствие параллелизма запросов

• 🟢 max_parallel_workers_per_gather = 0 отключает параллельное выполнение сканирований. На системе с 192 ядрами это может приводить к недоиспользованию CPU для операций, потенциально выигрывающих от распараллеливания.

9. Изменение паттернов ввода-вывода и буферизации

• 🟡 В P2 разрушились сильные корреляции между vmstat/buff и чтением с дисков данных, наблюдавшиеся в P1 (R² ~0.9). Появилась умеренная связь buff с записью на WAL. Это косвенно подтверждает сдвиг профиля нагрузки в сторону записи.

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

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

Приоритет 1: Исследование запроса -5038981907002478858

• 🟡 Получить текст запроса и план выполнения с EXPLAIN (ANALYZE, BUFFERS, TIMING).
• 🟡 Проанализировать использование расширений внутри запроса — какие функции вызываются и можно ли сократить их вызовы или оптимизировать логику.
• 🟡 Проверить статистику по таблицам, участвующим в запросе, актуальность индексов.

Приоритет 2: Анализ и возможная оптимизация конкуренции за LWLock

• 🟡 Оценить динамику pg_stat_bgwriter (buffers_alloc, buffers_backend_fsync) для понимания частоты выделения буферов.
• 🟡 Рассмотреть увеличение shared_buffers (текущие 246 GB при 720 GB кэша ОС) — при условии, что большая часть активно используемых страниц поместится в shared buffers, это может снизить конкуренцию за BufferMapping. Требуется тестирование.
• 🟡 Изучить возможность увеличения числа буферных разделов через параметр lwlock_shared_limit (только после анализа текущей конкуренции с помощью pg_stat_lwlock).
• 🟡 Оценить накладные расходы расширения pgpro_stats. Возможно, снижение частоты сбора статистики или временное отключение для проверки влияния на LWLock.

Приоритет 3: Оптимизация работы с WAL

• 🟡 Выявить причины роста объема записываемого WAL: увеличение числа транзакций, частоты UPDATE/DELETE, объема изменяемых данных. Использовать pg_stat_database.xact_commit/xact_rollback, pg_stat_user_tables.n_tup_upd/n_tup_del.
• 🟡 Проверить настройки wal_writer_delay, wal_writer_flush_after.
• 🟡 На уровне приложения рассмотреть возможность группировки мелких изменений в более крупные транзакции для уменьшения числа сбросов WAL.

Приоритет 4: Снижение блокировок tuple

• 🟡 Проанализировать pg_stat_user_tables на предмет таблиц с высоким числом seq_scan, n_tup_upd, n_tup_del.
• 🟡 Проверить наличие индексов на столбцах, используемых в условиях WHERE для UPDATE/DELETE. Отсутствие индексов может приводить к блокировке избыточного числа строк.
• 🟡 Рассмотреть использование оптимистичных блокировок на уровне приложения или изменение логики доступа к данным.

Приоритет 5: Параллелизм запросов

• 🟡 Оценить возможность включения параллельного выполнения запросов (max_parallel_workers_per_gather > 0, max_parallel_workers). Учитывая 192 ядра, потенциал ускорения сканирований больших таблиц значителен. Начать с консервативных значений (например, 2–4) с последующим мониторингом влияния на конкуренцию за ресурсы.

Приоритет 6: Дисковая подсистема

• 🟡 Провести профилирование ввода-вывода на уровне процессов (pidstat -d, iotop) для точного определения источников нагрузки на запись.
• 🟡 Проверить, не наблюдается ли неоднородность нагрузки между устройствами vdh–vdk (по медианам она равномерна, но пиковые значения могут различаться).
• 🟡 Если текущая пропускная способность дисковой подсистемы данных является ограничивающим фактором (на что указывает корреляция SPEED с MBps записи), рассмотреть возможность добавления более производительных накопителей или увеличения числа устройств в LVM.

Приоритет 7: Мониторинг памяти

• 🟡 Несмотря на тревожный статус free <5%, при отсутствии swap и стабильном объеме кэша ситуация штатная. Рекомендуется отслеживать pg_stat_database.blks_hit/blks_read для контроля cache hit ratio и sar -B для анализа вытеснения страниц.

Приоритет 8: Детализация CPU

• 🟡 Выполнить сбор детальной статистики CPU в период нагрузки (mpstat -P ALL 1, perf top) для выявления причины снижения idle и возможного роста steal time (если среда виртуализирована).

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

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

1. 🟡 Тексты и планы выполнения проблемных запросов
   Запрос -5038981907002478858 и другие из топ-5 по ожиданиям.
   Результаты EXPLAIN (ANALYZE, BUFFERS, TIMING) для каждого запроса.
2. 🟡 Данные о конкуренции за легковесные блокировки
   Представление pg_stat_lwlock за период инцидента.
   Параметр lwlock_shared_limit (если задан не по умолчанию).
3. 🟡 Статистика по буферному кэшу и фоновым процессам
   pg_stat_bgwriter за периоды P1 и P2.
   pg_stat_database (особенно blks_hit, blks_read, xact_commit, xact_rollback, temp_files).
4. 🟡 Информация о блокировках на уровне строк
   Данные pg_locks в моменты пиковых ожиданий Lock.
   pg_stat_user_tables (n_tup_upd, n_tup_del, seq_scan, idx_scan) для таблиц с высокой активностью.
5. 🟡 Логи PostgreSQL за периоды P1 и P2
   Сообщения о контрольных точках (checkpoint starting/complete).
   Сообщения об autovacuum (в том числе о конфликтах, пропущенных очистках).
   Сообщения о взаимоблокировках (deadlocks) и долгих запросах (log_min_duration_statement).
6. 🟡 Детализация нагрузки на CPU
   Временные ряды mpstat -P ALL или аналогичные с разбивкой по режимам (user, system, iowait, steal).
   Профиль CPU (perf или pg_stat_statements с планами) для выявления функций с наибольшим потреблением.
7. 🟡 Информация о количестве одновременных сессий
   pg_stat_activity (count by state) в моменты инцидента.
   Среднее и пиковое число активных подключений.
8. 🟡 Данные о влиянии расширения pgpro_stats
   Конфигурация расширения (частота сбора, детализация).
   Сравнение нагрузки с включенным и отключенным расширением (если возможно).
9. 🟡 Более детальные метрики дисковой подсистемы
   iostat -x 1 за оба периода для оценки не только медиан, но и выбросов по задержкам (r_await, w_await, svctm).
10. 🟡 Сведения об изменениях в нагрузке или конфигурации между P1 и P2
    Были ли деплой новых версий приложения, изменения в данных, запуск фоновых задач обслуживания.

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