Использование корреляционного анализа для определения корневой причины деградации СУБД
Задача — определить причину деградации производительности СУБД
Общая картина по мониторингу Zabbix.
Необходимые пояснения
Для сглаживания данных используется медианное сглаживание:
Долгая скользящая = 1 час (красная линия).
Короткая скользящая = 10 минут (красная линия).
Активные соединения и утилизация CPU — стандартные метрики Zabbix.
.
Как видно из графика имеет место деградация производительности СУБД:
Количество активных сессий растет, но производительность падает
Утилизация CPU растет , но производительность падает
Порядок использования статистического анализа для решения задачи
Выделение трендов на графике производительности
Выполняется тривиально, дополнительных инструментов не требуется.
13:00 — 13:28: Горизонтальный тренд — высокая производительность
13:28 — 13:47: Деградация производительности
13:57 — 14:05: Горизонтальный тренд — низкая производительность. Нагрузка на СУБД уменьшилась.
13:00 — 13:28: Горизонтальный тренд — высокая производительность
Статистические показатели производительности СУБД
Рис. 1. Статистические показатели горизонтального тренда 13:00–13:28
Прямая корреляция между количество активных сессий и производительностью СУБД. Или другими словами — чем выше нагрузка на СУБД , тем выше производительность.
Статистические показатели ожиданий СУБД — корреляция ожиданий и производительности СУБД
Рис. 2. Корреляционный анализ ожиданий и производительности 13:00–13:28
13:28 — 13:47: Деградация производительности
Статистические показатели производительности СУБД
Рис. 3. Статистические показатели нисходящего тренда 13:28 — 13:47
Сильная обратная корреляция — чем выше нагрузка на СУБД тем ниже производительность. Явный признак инцидента производительности СУБД
Статистические показатели ожиданий СУБД — корреляция ожиданий и производительности СУБД
Рис. 4. Корреляционный анализ ожиданий и производительности СУБД нисходящего тренда 13:28 — 13:47
Как видно из таблицы — количество ожиданий кардинально увеличилось. Явный признак — имеются серьезные проблемы с производительностью СУБД.
Определение первой причины деградации производительности СУБД
Из Рис. 4 видно, что наибольшая обратная корреляция между событиями ожидания и снижением производительности СУБД имеется для события LWLock / BufferMapping
Рис. 5. Ожидание LWLock / BufferMapping
Как видно количество ожиданий менее чем за 20 минут — весьма существенно.
Таким образом — первой (, но конечно не единственной причиной) деградации производительности СУБД в период 13:28 — 13:47 является большое количество ожиданий LWLock / BufferMapping при выполнении пользовательских запросов.
Определение SQL запросов с наибольшим количеством ожиданий LWLock / BufferMapping
Чуть подробнее об ожидании
| Ожидание при связывании блока данных с буфером в пуле буферов. |
Postgres Pro Enterprise: Документация: 16: 27.2. Система накопительной статистики : Компания Postgres Professional
LWLock — buffer_mapping
This event occurs when a session is waiting to associate a data block with a buffer in the shared buffer pool.
Context
The shared buffer pool is an PostgreSQL memory area that holds all pages that are or were being used by processes. When a process needs a page, it reads the page into the shared buffer pool. The shared_buffers parameter sets the shared buffer size and reserves a memory area to store the table and index pages. If you change this parameter, make sure to restart the database. For more information, see Shared Buffer Area.
The buffer_mapping wait event occurs in the following scenarios:
A process searches the buffer table for a page and acquires a shared buffer mapping lock.
A process loads a page into the buffer pool and acquires an exclusive buffer mapping lock.
A process removes a page from the pool and acquires an exclusive buffer mapping lock.
LWLock — buffer_mapping | Redrock Postgres Documentation (rockdata.net)
Определение запросов с максимальным количество ожиданий
Рис. 6. Запросы с ожиданием LWLock / BufferMapping c с количество более 100 в течении минуты
Далее дело техники, используя утилиту pgpro_pwr по имеющемуся queryid находим проблемный запрос в снимке за период 13:30 — 13:50
select
this_.id as id1_13_0_,
this_.application_name as applicat2_13_0_,
this_.assignment_snapshot as assignme3_13_0_,
this_.event_type as event_ty4_13_0_,
this_.start_date as start_da5_13_0_,
this_.parent_oid as parent_o6_13_0_,
this_.reason_data as reason_d7_13_0_,
this_.reason_key as reason_k8_13_0_,
this_.reason_ref as reason_r9_13_0_,
this_.reason_request_business_id as reason_10_13_0_,
this_.reason_request_id as reason_11_13_0_,
this_.requester_full_name as request12_13_0_,
this_.requester_oid as request13_13_0_,
this_.role as role14_13_0_,
this_.role_oid as role_oi15_13_0_,
this_.target_oid as target_16_13_0_,
this_.timestamp as timesta17_13_0_,
this_.end_date as end_dat18_13_0_,
this_.type as type19_13_0_
from
report_role_history_item this_
where
(this_.target_oid in ($1) or this_.parent_oid in ($2))
order by this_.timestamp desc limit $3Запрос передается разработчикам , для анализа .
Дальнейшие события ожидания анализируются схожим образом. Если отсортировать таблицу на Рис. 4. по количеству пользовательских запросов (более 100) , то можно сформировать еще более полный список проблемных запросов для передачи группе разработки.
Рис. 7. Список ожиданий отсортированный по количеству пользовательских запросов.
Итог
Статистический анализ производительности СУБД позволяет подтвердить наличие деградации производительности не дожидаясь деградации на уровне приложения.
Корреляционный анализ ожиданий и производительности СУБД позволяет быстрее определить корневую причину снижения производительности СУБД и определить список проблемных пользовательских запросов.
