Мониторим тяжелые запросы, классифицируем результаты

Публикация № 1056317

Администрирование - Производительность и оптимизация (HighLoad)

MSSQL highload технологический журнал

11
Дальнейшее развитие инструментария для мониторинга запросов.

В предыдущей статье Мониторим тяжелые запросы описан инструментарий по автоматическому сбору и накоплению информации о тяжелых запросах. Теперь нам нужно классифицировать запросы. Такая классификация позволит ответить на следующие вопросы:

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

Если БД для статистики уже создана на основании предыдущей статьи, то ее доработки можно будет применять инкрементально, по мере их... гм... вкрапления;) в публикации.

Также, в конце приведу аккумулированные скрипты - для обновления существующей БД, и полные, для создания "с нуля".

Классификатор запросов

Для классификации используется сопоставление текстов первичных запросов с "масками" каких-то известных нам запросов. Маска - это текстовка запроса, используемая для оператора LIKE.

Классификатор запросов организуем в новой таблице queries.

 
 Скрипт создания таблицы queries

Поля таблицы:

  • qr_key - идентификатор запроса, ключевое поле таблицы
  • description - произвольное описание
  • mask - маска для сопоставления полного текста запроса и записи из queries
  • related_qr_key - идентификатор qr_key какой-либо другой строки той же таблицы queries (см. подробности ниже)

Текстовки полных запросов одного и того же действия могут различаться - слегка или совсем. Например, какой-то запрос дорабатывается конфигурацией динамически, с добавлением/удалением условий запроса. Тогда для такого запроса можно ввести маску с минимальным набором применяемых условий.

В других случаях запрос полностью рефакторится - с декомпозицией, изменением логики и проч. Но (!) нам хотелось бы видеть общую картину нагрузки, до и после изменений. И тогда вводим две записи в queries. В первой записи вводим идентификатор qr_key (какое-то значение [ID1]), описание запроса description и маску для запроса в редакции ДО его изменений. Во второй записи также вводим идентификатор qr_key (какое-то значение [ID2]), описание запроса description, маску для запроса в редакции ПОСЛЕ его изменений и указываем значение поля related_qr_key, равное введенному qr_key для первой записи (значение [ID1]). Этим получаем привязку статистики обоих запросов к одной записи классификатора (где qr_key = [ID1]) - так работает процедура сопоставления.

Также свяжем таблицы queries и top_cpu_usage внешним ключом и добавим индексы.

 
 Скрипт создания внешнего колюча и доп.индексов

В классификатор нужно сразу добавить "предопределенное значение" - ввести запись с идентификатором qr_key = -1 и описанием "Не определено", чтобы с этой записью связывать запросы, которые не были распознаны по маскам.

 

Механизм сопоставления

Теперь нам нужно сопоставить данные таблиц статистики top_cpu_usage и классификатора queries. Для этого в queries мы предусмотрели маску запроса (поле mask), а в таблице top_cpu_usage уже изначально присутствует поле для сопоставленного идентификатора запроса (qr_id). Но соединять эти таблицы напрямую через like - это затратное мероприятие. Группировать или отбирать top_cpu_usage по текстовкам исходного запроса - тоже накладно. Поэтому будем также работать с хешами текстовок запросов.

В таблице top_cpu_usage создадим новое поле qr_text_hash и проиндексируем его.

 
 Скрипт создания поля и индекса

Хеши произвольных текстовок будем получать с помощью нами созданной скалярной функции MD5Hash.

 
 Скрипт создания MD5Hash

Доработаем процедуру сохранения текущей статистики, чтобы она также сразу сохраняла хеши текстовок запросов.

 
 Скрипт изменения sp_store_top_cpu_usage_data

И вот, наконец, сама процедура sp_fill_qr_id_for_cpu_usage, выполняющая сопоставление запросов.

 
 Скрипт создания процедуры sp_fill_qr_id_for_cpu_usage

У процедуры есть единственный параметр @only_empty. В него передаем 0 или 1, соответственно, для перезаполнения всех идентификаторов запросов, либо, где идентификаторы не определены.

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

 
 Скрипт задачи плана обслуживания

 

Заполнение классификатора запросов

Ну да, теперь мы имеем все механизмы, чтобы распознавать и сопоставить запросы. Но как понять - откуда вообще генерятся те или иные запросы?... Как узнать, что какой-то маске соответствует какой-то метод конфигурации или выполняется какой-то макет СКД?...

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

Реверс-инжиниринг

В моей практике, это всегда входная точка для сопоставлений.

Использую специальную внешнюю обработку, заменяющую имена таблиц и полей БД на соответствующие аналоги метаданных. Эта внешка мне досталась через третьи-четвертые руки от каких-то подрядчиков. Написана в продвинутом стиле. Поэтому публиковать и раздавать ее не буду. Вероятно, аналоги такой внешки уже и есть на ИС. Особо не искал, т.к. лучшее - враг хорошего;)

В общем случае, все достаточно несложно при создании подобных обработок. Используем ПолучитьСтруктуруХраненияБазыДанных(...). Понятно, что имена таблиц у нас уникальны. Но еще и сквозь всей БД - уникальны имена полей. Поэтому обычный СтрЗаменить() хорошо сможет преобразовать запрос.

В итоге, из такого запроса (запрос 1) - получаем эдакий (запрос 2).

 
 SQL-запрос и его реверс

Если запрос достаточно специфичен (содержит какие-то редко используемые таблицы или поля), то его ищем через глобальный поиск. Ну а когда найти не получается, тогда переходим ко второму способу - ищем с помощью ТЖ.

Поиск через серверный технологический журнал
 
 Настраиваем logcfg.xml примерно так

В моем примере, возможно, я перемудрил с маской запроса - навставлял много "%". Но результат был достигнут.

 
 Найденный код

 

Визуализация

Хранящуюся статистику просматриваем с помощью MS PowerBI Desktop. Видим такую вот картинку.

 
 Графики нагрузки

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

 
 Скрипт создания view vw_top_cpu_usage

 

Аккумулированные скрипты

 
 Скрипт для обновления созданной ранее БД
 
 Скрипт для создания объектов с нуля

 

11

См. также

Специальные предложения

Избранное Подписка Сортировка: Древо
В этой теме еще нет сообщений.
Оставьте свое сообщение