математическая статистика в целом не подходит для общего анализа и сравнения производительности СУБД.

Чем же может оказаться полезной математическая статистика или комментарий к комментарию.

Тренды на график метрики производительности СУБД
Тренды на график метрики производительности СУБД
Активные соединения и утилизация CPU
Активные соединения и утилизация CPU

Для сглаживания данных используется медианное сглаживание:

  • Долгая скользящая: 1 час(красная линия).

  • Короткая скользящая: 10 минут(синяя линия).

  • Активные соединения и утилизация CPU: стандартные метрики Zabbix.

Как видно из графика - имеет место деградация производительности СУБД:

  1. Количество активных сессий растет, но производительность падает

  2. Утилизация CPU растет , но производительность падает

Ситуация, принципиально отличается от описанной в казалось бы похожих кейсах:

Поэтому и решаться данный инцидент будет по другому.

Использование статистического анализа

1.Выделение трендов на графике производительности

Выполняется тривиально, дополнительных инструментов не требуется.

  • 13:00 - 13:28 : Горизонтальный тренд - высокая производительность

  • 13:28 - 13:47 : Деградация производительности

  • 13:57 - 14:05 : Горизонтальный тренд - низкая производительность. Нагрузка на СУБД уменьшилась.

13:00 - 13:28 : Горизонтальный тренд - высокая производительность

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

Рис.1. Статистические показатели горизонтального тренда 13:00-13:28
Рис.1. Статистические показатели горизонтального тренда 13:00-13:28

Прямая корреляция между количество активных сессий и производительностью СУБД . Или другими словами - чем выше нагрузка на СУБД , тем выше производительность.

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

Рис.2. Корреляционный анализ ожиданий и производительности 13:00-13:28
Рис.2. Корреляционный анализ ожиданий и производительности 13:00-13:28

Количество пользовательских запросов по которым имеются события ожидания СУБД - минимально.

13:28 - 13:47 : Деградация производительности

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

Рис.3. Статистические показатели нисходящего тренда 13:28 - 13:47
Рис.3. Статистические показатели нисходящего тренда 13:28 - 13:47

Сильная обратная корреляция - чем выше нагрузка на СУБД тем ниже производительность. Явный признак инцидента производительности СУБД

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

Рис.4. Корреляционный анализ ожиданий и производительности СУБД нисходящего тренда 13:28 - 13:47
Рис.4. Корреляционный анализ ожиданий и производительности СУБД нисходящего тренда 13:28 - 13:47

Как видно из таблицы - количество ожиданий кардинально увеличилось. Явный признак - имеются серьезные проблемы с производительностью СУБД.

2.Определение наиболее значимой причины деградации производительности СУБД

Из Рис.4 видно, что наибольшая обратная корреляция между событиями ожидания и снижением производительности СУБД имеется для события LWLock / BufferMapping

Рис.5. Ожидание LWLock / BufferMapping
Рис.5. Ожидание LWLock / BufferMapping

Как видно - количество ожиданий менее чем за 20 минут - весьма существенно.

Итак, первый результат

Первой( но конечно не единственной) причиной деградации производительности СУБД в период 13:28 - 13:47 является - большое количество ожиданий LWLock / BufferMapping при выполнении пользовательских запросов.

Чуть подробнее об ожидании BufferMapping

Ожидание при связывании блока данных с буфером в пуле буферов.

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.

3. Определение запросов с максимальным количество ожиданий

Рис.6. Запросы с ожиданием LWLock / BufferMapping c количество более 100.
Рис.6. Запросы с ожиданием LWLock / BufferMapping c количество более 100.

Далее, дело техники, используя утилиту pgpro_pwr по queryid, находим проблемный запрос за период 13:30 - 13:50(снимки pgpro_pwr формируются каждые 10 минут).

Запрос передается разработчикам , для анализа .

Дальнейшие события ожидания анализируются схожим образом. Если отсортировать таблицу Рис.4. по количеству пользовательских запросов(более 100) , то можно и нужно сформировать список проблемных запросов для передачи группе разработки на оптимизацию и доработку.

Рис.7. Список ожиданий отсортированный по количеству пользовательских запросов.
Рис.7. Список ожиданий отсортированный по количеству пользовательских запросов.

Итог

Статистический анализ производительности СУБД позволяет подтвердить наличие деградации производительности не дожидаясь деградации на уровне приложения.

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

P.S.

В настоящее время ведутся работы по разработке и тестированию новой версии инструментария по мониторингу и анализу производительности СУБД PostgreSQL - "Орешник".

Методология статистического анализа производительности СУБД PostgreSQL будет довольно существенно дополнена и доработана.