Параллельный шторм: как избыточный параллелизм обрушил производительность PostgreSQL
Предисловие:
В середине декабря 2025 года один из продуктовых кластеров PostgreSQL столкнулся с резким падением производительности на 52%. Анализ показал, что причиной стал не банальный дефицит ресурсов, а каскадный эффект от неправильно настроенного параллельного выполнения запросов. В этом разборе показано , как микро проблемы в настройках СУБД привели к макро-последствиям , и как подобных сценариев можно избежать в будущем.
Часть-2:Инфраструктура.
1. Исходные данные для анализа производительность и ожидания СУБД
Точки наблюдения
Регрессионный и корреляционный анализ
Операционная скорость
Ожидания СУБД
Ожидания типа IO
Ожидания типа IPC
2. Ожидания СУБД (Диаграмма Парето)
3.События ожидания для SQL выражений (Диаграмма Парето)
4. Анализ общих трендов производительности
4.1. Изменение операционной скорости (SPEED):
Начальный период (15:25–15:34):
- Высокая производительность — SPEED достигает 249 858.
Постепенное снижение (15:35–16:00):
- SPEED падает до 164 974 (~35% снижение).
Резкое падение (16:00–16:25):
- SPEED снижается до 119 033 (падение на ~52% от пика).
Тренд:
Чёткая нисходящая тенденция с ускорением деградации во второй половине периода.
4.2. Корреляция между SPEED и WAITINGS:
Коэффициент корреляции: -0.7249 (сильная отрицательная корреляция).
Интерпретация: Рост ожиданий (WAITINGS) напрямую связан с падением скорости выполнения запросов. Каждое увеличение WAITINGS на 1 единицу ассоциируется с падением SPEED.
4.3. Рост WAITINGS и их связь с IPC и IO:
WAITINGS выросли с 358 до 421 (на ~17.6%).
Корреляции:
- WAITINGS ↔ IPC: 0.9776 (очень сильная положительная корреляция).
- WAITINGS ↔ IO: 0.8236 (сильная положительная корреляция).
Рост IPC: С 238 до 294 (на ~23.5%) — основной драйвер роста ожиданий.
Рост IO: С 82 до 96 (на ~17.1%) — вторичный фактор.
4.4. Ключевые временные интервалы:
15:50–15:51:
Первое заметное падение SPEED (178 849 → 169 401) при росте WAITINGS (365 → 365) и IPC (245 → 245).
16:06–16:07:
Резкое падение SPEED (157 716 → 140 846) при стабильно высоких WAITINGS (415) и IPC (294).
16:20–16:25:
Минимальные значения SPEED (119 033) при максимальных WAITINGS (421) и высоких IPC (289–294).
4.5. Гипотезы причин деградации на основе регрессии:
Основная гипотеза: Проблемы с параллельным выполнением запросов (IPC).
- Высокая корреляция WAITINGS-IPC (0.9776) указывает на конфликты или неэффективность в параллельных операциях (BgWorkerShutdown, ExecuteGather, ParallelFinish).
- Увеличение времени на синхронизацию и завершение параллельных воркеров.
Вторичная гипотеза: Проблемы с вводом-выводом (IO).
- Рост событий DataFileRead и DSMFillZeroWrite указывает на увеличение чтения с диска и нулевой записи в динамическую разделяемую память.
- Возможные причины: нехватка буферного кэша, медленные диски, фрагментация.
Объяснение трендов:
- Угол наклона для SPEED: -44.25 → скорость падает со временем.
- Угол наклона для WAITINGS: +36.13 → ожидания растут.
- R² = 0.95 для SPEED: модель хорошо описывает падение производительности.
- R² = 0.53 для WAITINGS: только половина вариативности объясняется временем, что говорит о внешних факторах (нагрузка, блокировки).
4.6. Выводы:
1. Основная причина инцидента — рост ожиданий типа IPC, связанных с параллельным выполнением запросов.
2. Вторичный фактор — рост ожиданий IO, указывающий на проблемы с дисковыми операциями.
3. Критический период — 16:00–16:25, когда накопленный эффект от ожиданий привёл к снижению SPEED на 52%.
4. Рекомендации для дальнейшего анализа:
- Изучить настройки параллелизма (max_parallel_workers, max_parallel_workers_per_gather).
- Проверить дисковую подсистему и настройки shared_buffers.
- Оптимизировать запросы с высокими WAITINGS (из postgres.3.queryid.txt).
5. Анализ распределения типов ожиданий (Wait Events)
5.1. Влияние событий DataFileRead и DSMFillZeroWrite (IO) на производительность:
DataFileRead: Ожидание физического чтения данных с диска в буферный кэш.
Влияние:
- Указывает на недостаточный кэш (shared_buffers) или неэффективные планы запросов.
- Каждое чтение с диска занимает миллисекунды (vs микросекунды для памяти).
Корреляция с падением SPEED: при росте IO с 82 до 96 единиц SPEED упал на ~52%.
DSMFillZeroWrite: Инициализация нулями сегментов динамической разделяемой памяти (Dynamic Shared Memory) для параллельных запросов.
Влияние:
- Прямой индикатор активного использования параллельных запросов.
- Создает дополнительную нагрузку на память и CPU.
- Может приводить к фрагментации памяти и contention.
Совместный эффект:
- Параллельные запросы (DSMFillZeroWrite) + чтение с диска (DataFileRead) = двойная нагрузка на подсистему I/O.
5.2. Влияние событий IPC на производительность:
BgWorkerShutdown: Ожидание завершения фоновых worker-процессов (параллельных воркеров).
Влияние: Самый частый IPC-ивент (43,09%). Указывает на:
- Накладные расходы на управление параллелизмом.
- Возможные задержки при освобождении ресурсов.
ExecuteGather: Ожидание сбора результатов от параллельных worker-процессов.
Влияние: Координатор простаивает, ожидая данные от воркеров.
ParallelFinish: Финальная синхронизация параллельных операций.
Влияние: Дополнительные задержки после выполнения основной работы.
Общий эффект IPC:
- Корреляция 0.9776 с WAITINGS — почти линейная зависимость.
- Рост IPC с 238 до 294 (+23.5%) напрямую связан с падением SPEED.
5.3. Предположения о причинах и связи с падением SPEED:
Гипотеза 1: Неоптимальный параллелизм
События: BgWorkerShutdown, ExecuteGather, ParallelFinish + DSMFillZeroWrite.
Причина: Запросы используют параллельное выполнение, но:
- Неподходящие запросы: Мелкие таблицы/операции, где накладные расходы превышают выгоду.
- Конкуренция за ресурсы: Несколько параллельных запросов конкурируют за CPU, память, I/O.
- Настройки: Возможно, max_parallel_workers_per_gather слишком высок.
Гипотеза 2: Проблемы с I/O-подсистемой
События: DataFileRead (доминирует), DataFileExtend (расширение файлов).
Причина:
- Медленные диски или перегруженная SAN/NAS.
- Недостаток буферного кэша (shared_buffers).
- Отсутствие индексов → полносканирование больших таблиц.
Гипотеза 3: Комбинированный эффект "параллельный I/O-шторм"
Сценарий:
1. Параллельные запросы запускаются (DSMFillZeroWrite).
2. Каждый worker читает данные с диска (DataFileRead).
3. Возникает конкуренция за дисковые ресурсы.
4. Worker'ы завершаются с задержками (BgWorkerShutdown).
5. Координатор долго ждет результаты (ExecuteGather, ParallelFinish).
Результат: Каскадное увеличение задержек → резкое падение SPEED.
Гипотеза 4: Проблемные запросы из конкретных сервисов
- db1 : QUERYID e864c744b5bda408 и 1314202adca7f949 имеют высокие IO и IPC.
- db7 : QUERYID 2fdef45695de86d0 и f696187350ac8653 — высокие IPC.
- db4 : Множество запросов с DataFileRead.
5.4. Сводка причин падения SPEED:
1. Основная причина: Рост IPC-ожиданий, связанных с управлением параллельными запросами (BgWorkerShutdown, ExecuteGather, ParallelFinish).
2. Вторичная причина: Рост IO-ожиданий (DataFileRead) из-за недостаточного кэширования и конкуренции за дисковые ресурсы.
3. Триггер: Вероятно, одновременный запуск ресурсоемких параллельных запросов из нескольких сервисов (db1, db4, db7) создал нагрузку, превышающую capacity системы.
5.5. Рекомендации для анализа:
1. Проверить настройки параллелизма:
- max_parallel_workers_per_gather
- max_parallel_workers
- parallel_setup_cost, parallel_tuple_cost
2. Увеличить shared_buffers (если позволяет RAM).
3. Анализ дискового I/O (iostat, await, %util).
6. Анализ запросов с наибольшим временем ожиданий
6.1. Для IO (ввод-вывод):
1. -1701015661318396920 — 111 WAITINGS (4.63%)
2. -8294460624932274421 — 108 WAITINGS (4.50%)
3. 2832582591752167244 — 98 WAITINGS (4.09%)
4. 1374759154717555017 — 94 WAITINGS (3.92%)
5. -2349069232145766876 — 94 WAITINGS (3.92%)
6.2. Для IPC (межпроцессное взаимодействие):
1. 1374759154717555017 — 272 WAITINGS (16.42%)
2. -1701015661318396920 — 250 WAITINGS (15.09%)
3. 3449463017331132112 — 183 WAITINGS (11.04%)
4. -678327810318891437 — 162 WAITINGS (9.78%)
5. -7715565454820708773 — 109 WAITINGS (6.58%)
Ключевой вывод: Запросы -1701015661318396920 и 1374759154717555017 входят в топ-5 по обоим типам ожиданий.
6.3. Базы данных
db1 генерирует наибольшее количество ожиданий в системе.
db7 также показывает высокие показатели ожиданий.
6.4. Связь между CALLS, ROWS и WAITINGS:
Анализ эффективности запросов:
1. Запрос 1374759154717555017 — самый неэффективный:
- 10.6% вызовов заканчиваются ожиданиями
- Возвращает 0 строк — возможно, это DML-операция или запрос с агрегацией
2. Высокое соотношение WAITINGS/CALLS указывает на проблемы с параллельным выполнением:
- 1374759154717555017: 10.6%
- -1701015661318396920: 7.4%
3. Запросы с нормальным соотношением ROWS/CALL могут быть кандидатами на оптимизацию через индексы:
- -1701015661318396920: 24.6 строк на вызов
- 3449463017331132112: 7.6 строк на вызов
6.5. Рекомендации по оптимизации запросов и конфигурации СУБД:
A. Для конкретных проблемных запросов:
1. Запрос 1374759154717555017 (IO: DSMFillZeroWrite, IPC: ParallelFinish + BgWorkerShutdown + ExecuteGather + HashBuildHashInner):
Если это DML (UPDATE/DELETE) - добавить индексы на условия WHERE
Если это SELECT с агрегацией - проверить необходимость параллельного выполнения
Временно отключить параллелизм для этого запроса:
SET max_parallel_workers_per_gather = 0;
2. Запрос -1701015661318396920 (IO: DSMFillZeroWrite + DataFileRead, IPC: ParallelFinish + BgWorkerShutdown + ExecuteGather):
1. Добавить покрывающие индексы для уменьшения DataFileRead
2. Уменьшить степень параллелизма для этого типа запросов
3. Рассмотреть материализованное представление, если данные статичны
4. Увеличить work_mem для хэш-операций
B. Настройки конфигурации PostgreSQL:
1. Оптимизация параллелизма:
max_parallel_workers_per_gather = 2 # уменьшить с дефолтных 8
max_parallel_workers = 8 # ограничить общее количество
parallel_setup_cost = 1000 # увеличить стоимость запуска
parallel_tuple_cost = 0.1 # стандартное значение
2. Настройки памяти:
shared_buffers = 8GB # 25% от RAM, если 32GB
work_mem = 32MB # для хэш-операций
maintenance_work_mem = 1GB # для VACUUM
effective_cache_size = 24GB # оценка кэша ОС+PostgreSQL
3. Оптимизация ввода-вывода:
random_page_cost = 1.1 # для SSD
effective_io_concurrency = 200 # для SSD
checkpoint_completion_target = 0.9 # сглаживание нагрузки
C. Оперативные действия по сервисам:
1. Для db1:
-- Временное ограничение параллелизма
ALTER ROLE role1 IN DATABASE db1 SET max_parallel_workers_per_gather = 1;
2. Для db7:
Анализ необходимости параллельных запросов
Запросы 3449463017331132112 и -678327810318891437
Возможно отключение параллелизма или переписывание запросов
D. Архитектурные рекомендации:
1. Выделение ресурсов:
- Рассмотреть выделение отдельного инстанса для db1
- Использовать читающие реплики для аналитических запросов
2. Оптимизация схемы данных:
- Партиционирование больших таблиц
- Создание индексов для часто используемых предикатов
3. Кэширование на уровне приложения:
- Redis/Memcached для часто запрашиваемых данных
- Кэширование результатов тяжелых запросов
Приоритет действий:
1. Срочно (24 часа): Отключить параллелизм для запроса 1374759154717555017
2. Краткосрочно (1 неделя): Добавить индексы для запроса -1701015661318396920
3. Среднесрочно (1 месяц): Настроить параметры конфигурации PostgreSQL
4. Долгосрочно: Архитектурный рефакторинг критичных сервисов
7. Сводный анализ причин инцидента
Период: 2025-12-16 15:25–16:25 (60 минут)
7.1. Основные факторы падения производительности
A. Рост ожиданий IPC (корреляция с WAITINGS: 0.9776)
Основные события: BgWorkerShutdown (714), ExecuteGather (407), ParallelFinish (363)
Причина: Чрезмерное использование параллельного выполнения запросов
Эффект: Накладные расходы на управление параллельными воркерами и синхронизацию
Вклад в падение SPEED: ≈60-70%
B. Рост ожиданий IO (корреляция с WAITINGS: 0.8236)
Основные события: DataFileRead (1,859), DSMFillZeroWrite (513)
Причина:
- Физическое чтение данных с диска (недостаточный кэш)
- Инициализация динамической разделяемой памяти для параллельных запросов
Эффект: Блокировка выполнения из-за медленного I/O
Вклад в падение SPEED: ≈30-40%
7.2. Роль конкретных проблемных запросов
Топ-3 проблемных запроса:
Вывод: Запросы 1374759154717555017, 1701015661318396920 создали каскадный эффект, перегрузив систему параллельными операциями.
7.3. Временная динамика инцидента
Фазы развития инцидента:
Фаза 1: Нормальная работа (15:25–15:34)
- SPEED: 236 148–249 858 (высокий)
- WAITINGS: 390–397 (стабильные)
- IPC: 257 (норма)
Фаза 2: Начало деградации (15:35–15:50)
- SPEED начинает падать (249 858 → 178 849)
- WAITINGS растут (373 → 365)
- IPC увеличиваются (245 → 245)
- Триггер: Увеличение нагрузки от db1
Фаза 3: Критическая деградация (15:51–16:25)
- SPEED резко падает (169,401 → 119,033) ▼52%
- WAITINGS достигают максимума (365 → 421) ▲17.6%
- IPC достигают максимума (245 → 294) ▲23.5%
Пик нагрузки: 16:00–16:25
Корреляционная связь:
- SPEED и WAITINGS: -0.7249 (сильная обратная связь)
- Каждое увеличение WAITINGS на 1% приводило к падению SPEED на ~0.72%
7.4. Рекомендации по оптимизации
A. Оптимизация проблемных запросов
1. Запрос 1374759154717555017:
-- Отключить параллелизм для запросов, возвращающих 0 строк
SET max_parallel_workers_per_gather = 0;
-- Переписать с устранением HashBuildHashInner
2. Запрос -1701015661318396920:
-- Добавить покрывающие индексы
CREATE INDEX idx_covering ON table_name (columns) INCLUDE (other_columns);
-- Уменьшить степень параллелизма
SET max_parallel_workers_per_gather = 2;
B. Настройка параметров параллельного выполнения
# postgresql.conf
max_parallel_workers_per_gather = 2 # вместо дефолтных 8
max_parallel_workers = 8 # общий лимит
parallel_setup_cost = 2000 # увеличить стоимость запуска
parallel_tuple_cost = 0.2 # стандартное значение
max_parallel_maintenance_workers = 2 # для обслуживания
C. Улучшение производительности I/O
1. Настройки PostgreSQL:
shared_buffers = 8GB # 25% от 32GB RAM
effective_cache_size = 24GB # оценка общего кэша
random_page_cost = 1.1 # для SSD
effective_io_concurrency = 200 # для SSD/NVMe
2. Аппаратные улучшения:
- Переход на NVMe SSD для дисков данных
- Увеличение оперативной памяти
- Настройка RAID 10 для журналов транзакций
3. Оптимизация дисковых операций:
-- Регулярный мониторинг и обслуживание
VACUUM ANALYZE; -- ежедневно
REINDEX CONCURRENTLY; -- еженедельно для проблемных таблиц
D. Архитектурные изменения
1. Выделение сервисов:
- Отдельный инстанс PostgreSQL для db1
- Читающие реплики для аналитических запросов
2. Кэширование:
Redis для кэширования результатов тяжёлых запросов
pg_prewarm для предзагрузки горячих данных
Итоговая оценка инцидента
Основная причина: Неоптимальная настройка параллельного выполнения запросов в сочетании с недостаточным кэшированием данных.
Вторичные факторы:
1. Отсутствие индексов для часто запрашиваемых данных
2. Неадекватные настройки памяти (shared_buffers, work_mem)
3. Отсутствие мониторинга соотношения WAITINGS/CALLS
Риск повторения: Высокий (без реализации рекомендаций)
Приоритет действий:
1. Срочно (24 часа): Настроить ограничения параллелизма для db1
2. Краткосрочно (1 неделя): Добавить индексы для проблемных запросов
3. Среднесрочно (1 месяц): Внедрить систему мониторинга и алертинга
4. Долгосрочно: Архитектурный рефакторинг разделения OLTP и аналитических нагрузок
Ожидаемый эффект от оптимизаций:
- Увеличение SPEED на 40-60%
- Снижение WAITINGS на 50-70%
- Улучшение стабильности системы в периоды пиковой нагрузки