PG_EXPECTO: Прицельный залп по проблемам производительности PostgreSQL

Определи цель, заряди параметры, устрани проблему

Начало

PG_EXPECTO: Принцип «только большие пушки» для СУБД

Предисловие:

Когда производительность СУБД падает под нагрузкой, важно не стрелять наугад. Методика PG_EXPECTO предлагает сначала выявить ключевые цели — проблемные запросы и типы ожиданий — и только затем наносить точный удар, используя «большие пушки» оптимизации.

1. Анализ ожиданий СУБД на основании данных о диаграмме Парето

Проанализируй данные по типам ожидания в ходе инцидента производительности СУБД PostgreSQL

1. Анализ ожиданий типа IPC (Inter-Process Communication)

BgWorkerShutdown (40,62%) — наиболее проблемное событие:

• Указывает на ожидание завершения фоновых процессов
• Возможные причины:
• Медленное завершение параллельных worker-процессов
• Проблемы с освобождением ресурсов
• Возможные deadlock-ситуации при завершении

ParallelFinish (25,4%) и ExecuteGather (21,14%):

• Связаны с параллельным выполнением запросов
• Высокие значения могут указывать на:
• Слишком агрессивные настройки параллелизма
• Конкуренцию за ресурсы между параллельными worker-ами
• Неоптимальные планы выполнения параллельных запросов

2. Анализ ожиданий типа LWLock (Lightweight Locks)

BufferMapping (49,46%) — основная проблема в блокировках:

• Указывает на конкуренцию за доступ к буферному кешу
• Возможные причины:
• Недостаточный размер shared_buffers
• Высокая конкурентная нагрузка на горячие данные
• Отсутствие или неэффективность индексов

SerializableXactHash (31,72%):

• Связано с транзакциями уровня изоляции SERIALIZABLE
• Может указывать на:
• Высокую конкуренцию за структуры данных serializable транзакций
• Необходимость оптимизации транзакций

Рекомендации по устранению проблем:

Для IPC-ожиданий:

1.Проверить настройки параллелизма:

max_parallel_workers_per_gather

max_parallel_workers

max_worker_processes

2.Анализировать логи на предмет ошибок воркеров

3.Рассмотреть уменьшение параллелизма для сложных запросов

Для LWLock-ожиданий:

1. Оптимизировать работу с буферным кешем:
2. Увеличить shared_buffers (если позволяет RAM)
3. Проверить эффективность индексов
4. Рассмотреть использование pg_prewarm для критичных данных
5. Для Serializable транзакций:
6. Проверить необходимость использования SERIALIZABLE изоляции
7. Оптимизировать длительность транзакций
8. Рассмотреть альтернативные уровни изоляции

Общие рекомендации:

1. Сбор дополнительной информации:
2. Получить полный список ожиданий за период инцидента
3. Проверить мониторинг нагрузки на CPU, IO, память
4. Возможные immediate-действия:
5. Перераспределить нагрузку по времени
6. Временно снизить параллелизм
7. Добавить индексы на часто используемые поля в условиях WHERE/JOIN

Вывод:

Основные проблемы связаны с параллельным выполнением запросов и конкуренцией за буферный кеш. Рекомендуется поэтапная оптимизация настроек параллелизма и работы с памятью.

2. Анализ событий ожиданий по отдельным SQL-запросам на основании данных о диаграмме Парето

Проанализируй данные по событиям ожидания для SQL запросов по queryid в ходе инцидента производительности СУБД PostgreSQL

Ключевые выводы по корреляции типов ожиданий и запросов

1. Запросы с наибольшим влиянием на IPC-ожидания:

Топ-1 проблемный запрос (17,6% IPC ожиданий):

• queryid: -1701015661318396920 3018 вызовов, 85k строк
• Ожидания: BgWorkerShutdown, ParallelFinish, BufferIO, ExecuteGather
• Анализ: Часто выполняемый параллельный запрос с проблемами завершения воркеров

Топ-2 (14% IPC ожиданий):

• queryid: -678327810318891437 5183 вызовов, 5183 строк
• Особенность: Каждый вызов возвращает ровно 1 строку
• Высокая частота выполнения усиливает проблемы параллелизма

2. Запросы с наибольшим влиянием на LWLock-ожидания:

Топ-1 проблемный запрос (24,73% LWLock ожиданий):

• queryid: -8182552260313222074 42,7k вызовов, 8,5M строк
• Ожидания: BufferMapping
• Анализ: Крайне часто выполняемый запрос с высокой конкуренцией за буферный кеш

Критический запрос, присутствующий в обеих категориях:

• queryid: 5459520954633506046:
• IPC: 5,34% (параллельное выполнение)
• LWLock: 11,83% (ParallelHashJoin)
• Особенность: 287 вызовов, но 217k строк на вызов (~759 строк/вызов)
• Вероятно: Сложный аналитический запрос с хеш-соединениями

Детальный анализ проблемных паттернов:

A. Проблемы параллельного выполнения:

1. Запросы с ParallelFinish и ExecuteGather:
2. Все топ-8 IPC запросов имеют эти ожидания
3. Рекомендация: Уменьшить max_parallel_workers_per_gather для этих queryid
4. Конкретные кандидаты на оптимизацию:
5. queryid: 1374759154717555017 2053 вызова, 0 строк
6. Возможно, DML-операция с неправильным планом
7. Имеет HashBuildHashInner - проблема с хеш-таблицами

B. Проблемы с блокировками буферов:

1. Высокочастотные запросы:
2. queryid: -8182552260313222074 42k вызовов
3. Решение: Кэширование на уровне приложения, batch-обработка
4. Serializable транзакции в db5:
5. 4 из топ-8 LWLock запросов используют SerializableXactHash
6. Все из базы db5
7. Рекомендация: Пересмотреть уровень изоляции

Приоритетные действия:

1. Немедленные меры:

• Для queryid: -8182552260313222074 (42k вызовов):
• -- Добавить индексы на используемые предикаты
• -- Внедрить кэширование на 5-10 минут
• Для queryid: -1701015661318396920 (частый параллельный запрос):
• -- Принудительно отключить параллельное выполнение
• SET max_parallel_workers_per_gather = 0;

2. Среднесрочная оптимизация:

Группа 1: Частые параллельные запросы

• queryid: -678327810318891437 (5k вызовов)
• queryid: 3449463017331132112 (3k вызовов)
• Действие: Оптимизация планов, уменьшение частоты выполнения

Группа 2: Запросы с хеш-соединениями

• queryid: 5459520954633506046
• queryid: 1374759154717555017
• Действие: Увеличить work_mem, добавить индексы для соединений

3. Долгосрочные изменения:

Для базы db5 (Serializable транзакции):

• Аудит необходимости SERIALIZABLE изоляции
• Возможный переход на REPEATABLE READ

Настройка PostgreSQL:

# Увеличить для уменьшения BufferMapping

shared_buffers

# Оптимизация параллельных запросов

max_parallel_workers_per_gather = 2 (вместо 4-8)

parallel_setup_cost = 1000

parallel_tuple_cost = 0.1

# Увеличение для хеш-соединений

work_mem = 32MB (или больше)

Рекомендуемый план действий:

1. Неделя 1: Оптимизация двух самых проблемных запросов
2. queryid: -8182552260313222074 (кэширование)
3. queryid: -1701015661318396920 (отключение параллелизма)
4. Неделя 2: Анализ и оптимизация запросов с хеш-соединениями
5. Добавление индексов
6. Пересмотр логики запросов
7. Неделя 3: Работа с часто выполняемыми запросами
8. Батчинг
9. Оптимизация бизнес-логики
10. Неделя 4: Пересмотр уровня изоляции в db5
11. Тестирование производительности при READ COMMITTED/REPEATABLE READ

Важно:

Все изменения должны тестироваться на staging-окружении с нагрузочным тестированием.

3.Сводный отчет по ожиданиям и проблемным SQL запросам

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

Контекст

В ходе инцидента производительности СУБД PostgreSQL выявлены проблемные SQL-запросы с высокой корреляцией (≥0.7) между типом ожидания и нагрузкой на СУБД. Отчет сформирован на основе анализа событий ожиданий и соответствующих запросов.

1. Ожидания типа IPC

Общая статистика событий IPC:

2. Ожидания типа LWLock

Общая статистика событий LWLock:

Ключевые наблюдения

1. Наиболее частые события ожидания:
2. IPC: BgWorkerShutdown (40,62%)
3. LWLock: BufferMapping (49,46%)
4. Наиболее проблемные запросы:
5. IPC: -1701015661318396920 (17,6% ожиданий)
6. LWLock: -8182552260313222074 (24,73% ожиданий)
7. Распределение по БД:
8. db1 и db5 имеют наибольшее количество проблемных запросов
9. role1 является общей ролью для всех проблемных запросов
10. Паттерны ожиданий:
11. Запросы с высоким числом CALLS часто имеют ожидания типа LWLock
12. Запросы с большим количеством возвращаемых ROWS склонны к ожиданиям IPC

Рекомендации для дальнейшего анализа

1. Сфокусироваться на оптимизации запросов с наибольшим процентом ожиданий
2. Исследовать причины частых событий BgWorkerShutdown и BufferMapping
3. Проанализировать работу параллельных операций (ParallelFinish, ExecuteGather)
4. Проверить настройки, связанные с управлением блокировками и параллельным выполнением