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

СУБД это сложная система взаимосвязанных компонент и подсистем.

Инцидент производительности СУБД

Дашборд панели мониторинга Zabbix

Отчет по инцидентам производительности СУБД

Операционная скорость в течении 1 часа до начала инцидента

Ось X - точка наблюдения. Ось Y - Операционная скорость . Красный график - Линия регрессии

Ожидания СУБД в течении 1 часа до начала инцидента

Ось X - точка наблюдения. Ось Y - Операционная скорость . Красный график - Линия регрессии

План действий при возникновении инцидента производительности СУБД

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

1. Чек-лист инфраструктуры
2. Корреляционный анализ ожиданий СУБД
3. SQL- запросы - кандидаты для оптимизации

1.Чек-лист инфраструктуры

Используемые методики для формирования чек-листов

1.1. Чек-лист IO - OK

Проблем с IO - нет:

1. Влияние IO на производительность СУБД отсутствует или минимально .

1.2. Чек-лист CPU - ALARM

Проблемы с CPU(аварийная ситуация):

1. Возможно внешнее влияние со стороны приложений.

1.3. Чек-лист RAM - WARNING

Возможные проблемы с RAM(предаварийная ситуация):

1. Имеется дефицит RAM.

2.Корреляционный анализ ожиданий СУБД

Ось X - точка наблюдения. Ось Y - Операционная скорость . Красный график - Линия регрессии

Корреляция ожиданий СУБД и типов ожиданий СУБД

Ожидания типа IO

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

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

3.SQL- запросы - кандидаты для оптимизации

3.1. Тип ожидания IO

IO: Серверный процесс ожидает завершения операции ввода/вывода.

3.1.1. События ожидания для запроса 2062554616027847198

• DataFileRead: Ожидание чтения из файла данных отношения.

Возможные причины роста событий ожидания DataFileRead

📌 1. Недостаточный размер буферного кэша (shared_buffers)

Причина:

• Если размер shared_buffers слишком мал для рабочей нагрузки, данные не могут удерживаться в памяти, что приводит к частым чтениям с диска

📌 2. Интенсивные операции последовательного сканирования (Sequential Scans)

Причина:

• Запросы, выполняющие полное сканирование больших таблиц (из-за отсутствия индексов или неоптимальных планов выполнения), генерируют большой объем операций чтения с диска

📌 3. Высокая конкуренция за ресурсы ввода-вывода

Причина:

• Одновременное выполнение множества операций, требующих дискового ввода-вывода (например, массовые запросы, обслуживание БД), может перегружать дисковую подсистему

📌 4. Активные фоновые процессы (VACUUM, ANALYZE, индексация)

Причина:

• Процессы автоочистки (autovacuum), создание индексов или сбор статистики выполняют интенсивные чтения данных с диска

📌 5. Неоптимальная схема данных или запросов

Причина:

• Отсутствие индексов или использование неэффективных индексов.
• Запросы, которые возвращают большие объемы данных (например, SELECT * без ограничений

📌 6. Проблемы с аппаратным обеспечением или конфигурацией диска

Причина:

• Медленные диски (например, HDD вместо SSD), недостаточное количество IOPS или неправильная настройка RAID/файловой системы

📌 7. Резкое увеличение количества подключений

Причина:

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

📌 8. Нехватка оперативной памяти (RAM)

Причина:

• Если операционной системе не хватает памяти, это может затрагивать файловый кэш ОС, который дополняет shared_buffers PostgreSQL

⚠️См. 1.3.(Чек-лист RAM)

📌 9. Статистика и планирование запросов

Причина:

• Устаревшая статистика может приводить к неоптимальным планам запросов (например, выбор последовательного сканирования вместо индексного)

📌 10. Влияние других системных процессов

⚠️См. 1.3.(Чек-лист RAM)

3.1.2. Текст SQL запроса 2062554616027847198

3.1.3. План выполнения SQL-запроса 2062554616027847198

3.2. Тип ожидания IPC

IPC Серверный процесс ожидает взаимодействия с другим процессом.

3.2.1 События ожидания для запроса -3985919093425059746

BgWorkerStartup: Ожидание запуска фонового рабочего процесса.

Возможные причины роста событий ожидания BgWorkerStartup

📌 1. Высокая нагрузка от автоочистки (autovacuum)

Причина:

• Большое количество мёртвых строк (dead tuples) в таблицах из-за интенсивных операций UPDATE/DELETE.
• Настройки autovaculum не оптимизированы (например, слишком низкие значения autovacuum_vacuum_cost_delay или autovacuum_max_workers).

📌 2. Большое количество фоновых workers в очереди

Причина:

• Лимит одновременных фоновых процессов (max_worker_processes) исчерпан.
• Медленная работа отдельных workers (например, autovaculum «зависает» на больших таблицах).

📌 3. Конкуренция за ресурсы CPU или I/O

Причина:

• Фоновые процессы не могут запуститься или начать работу из-за нехватки CPU или дисковой подсистемы.
• Высокая нагрузка от пользовательских запросов конкурирует с фоновыми задачами.

⚠️См. 1.2 (Чек-лист CPU)

📌 4. Проблемы с логической репликацией

Причина:

• На реплике запущены подписчики логической репликации (logical replication workers), которые не успевают применлять изменения.
• Большой объем транзакций или медленная сеть задерживают запуск workers.

📌 5. Использование расширений или FDW

Причина:

• Расширения, такие как pg_cron, postgres_fdw или пользовательские background workers, создают дополнительную нагрузку.
• Workers для внешних таблиц (FDW) блокируются из-за медленных внешних источников.

⚠️См. 1.2. (Чек-лист CPU).

📌 6. Нехватка памяти (OOM)

Причина:

• Попытка запуска фонового процесса завершается с ошибкой из-за нехватки памяти.

❓См. 1.3.(Чек-лист RAM)

📌 7. Блокировки из-за длительных транзакций

Причина:

• Длительные транзакции блокируют autovaculum (например, мешают очистке таблиц).

3.2.2. Текст SQL запроса -3985919093425059746

3.2.3. План выполнения SQL-запроса -3985919093425059746

3.3. Тип ожидания LWLock

LWLock: Серверный процесс ожидает лёгкую блокировку. В большинстве своём такие блокировки защищают определённые структуры данных в общей памяти.

3.3.1. События ожидания для запроса -4217442339750081997

ParallelHashJoin: Ожидание синхронизации рабочих процессов в процессе выполнения узла плана Parallel Hash Join.

Возможные причины роста событий ожидания ParallelHashJoin

⚙️ 1. Неоптимальные настройки памяти

Причина:

• Недостаточный объем памяти, выделенный для параллельных операций (например, work_mem или hash_mem_multiplier), может приводить к тому, что хеш-таблицы не помещаются в память и используют дисковые операции (batch), что увеличивает ожидания

📊 2. Высокая конкуренция за ресурсы CPU

Причина:

• Параллельные операции HashJoin требуют значительных CPU-ресурсов. Если система перегружена другими процессами, это может приводить к ожиданиям из-за нехватки CPU

⚠️См. 1.2. (Чек-лист CPU)

🗃️ 3. Неравномерное распределение данных

Причина:

• Если данные в соединяемых таблицах имеют сильный перекос (например, много дубликатов по ключу соединения), это может приводить к неравномерной нагрузке между рабочими процессами и ожиданиям синхронизации

⏱️ 4. Несоответствующий уровень параллелизма

Причина:

• Неправильная настройка параметров параллелизма (например, parallel_tuple_cost или parallel_setup_cost) может приводить к тому, что планировщик выбирает параллельное выполнение даже когда это неэффективно

📉 5. Проблемы с дисковым I/O

Причина:

• Если хеш-таблица не помещается в память и использует временные файлы на диске, медленный I/O может значительно увеличить время ожидания

🔍 6. Отсутствие индексов или статистики

Причина:

• Неактуальная статистика или отсутствие индексов может приводить к неоптимальным планам запросов, включая неэффективные параллельные хеш-соединения

📈 7. Ограничения числа рабочих процессов

• Причина: Если значение max_parallel_workers слишком низкое, это может ограничивать количество доступных рабочих процессов для параллельных операций, приводя к очередям и ожиданиям

3.3.2. Текст SQL запроса -4217442339750081997

3.3.3 План выполнения SQL-запроса -4217442339750081997

Продолжение работ по теме