Данная статья должна была появится раньше , чем опубликованная вчера https://habr.com/p/827156/ , поскольку планируемые методы анализа производительности отдельного запроса являются частным случаем решения общей задачи - анализ производительности СУБД .

Исследование применимости и эффективности статистических методов при анализе проблем производительности находится в самом начале долгого и интересного пути. Поэтому данную статью ни в коем случае нельзя рассматривать как результат только начавшихся работ, а лишь как промежуточные путевые заметки.

Вводная часть

Известное базовое физическое понятие:

Производительностью называется количество работы, которое выполнено за какую-либо единицу времени.

Перефразируя определение , введем понятие производительность для СУБД:

Производительностью СУБД называется количество объемов информации, обработанных за единицу времени.

Единицей информации СУБД будем считать страницу буферного кэша. СУБД в процессе работы читает, записывает и изменяет страницы в буферном кэше. Таким образом, производительностью СУБД будем считать модуль вектора N размерности ( n1 , n2 , n3 ) , где :

• n1 - количество прочитанных блоков в секунду;

• n2 - количество записанных блоков в секунду;

• n3 - количество измененных блоков в секунду;

Используя расширение pg_stat_statements , требуемую информацию можно легко получить:

Также добавим в вектор еще 2 размерности: QPS (количество завершенных запросов в секунду) , TPS (количество зафиксированных транзакций в секунду).

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

Необходимо отметить , что как было показано ранее https://habr.com/ru/posts/827260/ , метрика "Время отклика СУБД" - не является индикатором производительности СУБД и не используется при анализе инцидентов.

Итак - метрика производительности СУБД(далее - CPI) рассчитывается как модуль вектора (n1,n2,n3,n4,n5) и будет использована для мониторинга и определения инцидентов производительности СУБД .

Важное следствие - рост , количества прочитанных/записанных/измененных блоков не является инцидентом и не требует анализа со стороны DBA.

И наоборот - снижение количества обработанных блоков в единицу времени - повод обратить внимание и начать анализ причин.

Использование метрики для мониторинга производительности СУБД

С помощью cron , рассчитывается метрика CPI, сохраняется в сервисной таблице и передается для мониторинга . Для сглаживания шума и выбросов данные для мониторинга сглаживаются, используя медианное сглаживание по промежуткам 10 минут и 1 час.

В результате получается графическая картина состояния производительности СУБД:

Пример решения инцидента деградации производительности СУБД

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

Задача - установить причину снижения производительности СУБД .

Шаг 1 - Уточнить временные точки инцидента

На графике мониторинга практически невозможно определить точное время начала инцидента. Для этого используются данные по скользящим, загруженным в Excel .

• 11:10 - 12:58 - Нормальная производительности

• 12:58 - 13:08 - Инцидент деградации производительности

• 13:08 - 14:20 - Производительности деградировала - есть проблема .

Шаг 2 - Подтверждение наличия деградации производительности

Для начала, необходимо установить - а действительно ли произошел инцидент производительности СУБД. Возможно , уменьшение метрики вызвано всего лишь лишь снижением нагрузки на СУБД . Или другими словами - проверить , что небыло снижения количества активных сессий(это не инцидент). А вот увеличение количества активных, а значить и сессий не только выполняющих запрос, но и ожидающих освобождение какого либо ресурса, сессий и снижение объемов обработанной информации за единицу времени это явный признак наличия проблемы.

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

Корреля́ция (от лат. correlatio «соотношение»), или корреляцио́нная зави́симость — статистическая взаимосвязь двух или более случайных величин (либо величин, которые можно с некоторой допустимой степенью точности считать таковыми), при этом изменения значений одной или нескольких из этих величин сопутствуют систематическому изменению значений другой или других величин[1].

https://ru.m.ruwiki.ru/wiki/%D0%9A%D0%BE%D1%80%D1%80%D0%B5%D0%BB%D1%8F%D1%86%D0%B8%D1%8F

Рассчитав коэффициент корреляции между значениями метрики CPI и сглаженными значениями метрики активных сессий(Active connections) обнаруживается:

• 11:10 - 12:58 - Нормальная производительности : Слабая прямая корреляция между значениями CPI и Active connections

• 12:58 - 13:08 - Инцидент деградации производительности: Сильная обратная корреляция между значениями CPI и Active connections

• 13:08 - 14:20 - Производительности деградировала: Очень слабая обратная корреляция между значениями CPI и Active connections

Гипотеза - увеличение количества активных сессий и следовательно сессий в состоянии ожидания в период 12:58 - 13:08 , привело к инциденту производительности СУБД .

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

Рассчитываем коэффициенты корреляции между событиями ожидания и метрикой CPI.

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

Если анализировать графики мониторинга ожиданий или отчеты по времени ожидания, то в топе будут события ожидания некоррелированные с падением производительности. И усилия потраченные на установление причин ожиданий могут и не привести в итоге к установлению причины инцидента и решению проблемы производительности.

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

Шаг 4 - определение активных сессий оказывающих наибольшее отрицательное влияние на производительность СУБД.

Далее уже вопрос , чисто технический - любым доступным способом получаем выборку из истории значений системного представления pg_stat_activity и обращаем особое внимание на сессии с wait_event_type = 'Lock' и wait_event = 'transactionid'.

Данные сессии скорее всего и будут причиной деградации производительности .

В данном случае - причина была в использовании запросов select 1 * from *** limit * for update .

Итоги

1)Использование метрики производительности СУБД позволяет корректно оценивать состояние СУБД .

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

Проблемы

Пока не совсем ясно - как использовать метрику производительности СУБД для оповещения о начале инцидента . Ну и конечно невозможно ничего прогнозировать . Проблема в том, что нагрузка на СУБД создаваемая сессиями крайне неравномерна, даже в течении короткого промежутка времени. Всё зависит от работы приложения, и в конечном счете от текущей активности пользователей . А с пользователями количество факторов не подается никакому анализу . И совсем не факт, что низкие показатели производительности это признак проблемы. Может все клиенты на перекур ушли , одновременно .

Так или иначе - исследования продолжаются .

Возможно планируемые работы по анализу производительности отдельных запросов позволят более четко проанализировать спектр показателей производительности .