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

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

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

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

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

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

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

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

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

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

Для классификации используется сопоставление текстов первичных запросов с "масками" каких-то известных нам запросов. Маска - это текстовка запроса, используемая для оператора 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

 

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

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

 

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

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

См. также

Простое обнаружение проблем производительности в PostgreSQL

Статья Системный администратор Программист Нет файла Бесплатно (free) Производительность и оптимизация (HighLoad)

Предлагаю вашему вниманию перевод статьи Hans-Jürgen Schönig "DETECTING PERFORMANCE PROBLEMS EASILY IN POSTGRESQL". Оригинал доступен по ссылке https://www.cybertec-postgresql.com/en/detecting-performance-problems-easily-in-postgresql/ Актуально для всех 1С ников, перешедших с MS SQL на Postgres

20.02.2020    1150    w.r.    4       

Новый раздел на Инфостарте - Electronic Software Distribution Промо

Инфостарт напоминает: на нашем сайте можно купить не только ПО, связанное с 1С. В нашем арсенале – ESD-лицензии на ПО от ведущих вендоров: Microsoft, Kaspersky, ESET, Dr.Web, Аскон и другие.

  • Низкие цены, без скрытых платежей и наценок
  • Оперативная отгрузка
  • Возможность оплаты с личного счета (кешбек, обмен стартмани на рубли и т.п.)
  • Покупки идут в накопления для получения скидочных карт лояльности Silver (5%) и Gold (10%)

Планы запросов - это просто! Разбор оптимизаций запросов PostgreSQL на живых примерах

Статья Программист Нет файла v8::Запросы Бесплатно (free) Производительность и оптимизация (HighLoad)

Проблема быстродействия 1С напрямую зависит от производительности запросов. Но как понять механику работы СУБД с помощью плана запроса? Андрей Овсянкин и Никита Грызлов на конференции Infostart Event 2019 Inception подробно рассмотрели алгоритм работы с планом запроса СУБД PostgreSQL, полученным из технологического журнала, и рассказали, на что обратить внимание, чтобы оптимизировать работу системы.

17.02.2020    3680    Evil Beaver    13       

Держи данные в тепле, транзакции в холоде, а VACUUM в голоде

Статья Системный администратор Программист Нет файла Бесплатно (free) Производительность и оптимизация (HighLoad)

Чтобы база работала быстро – в ней нужен порядок. Это касается как MS SQL, так и PostgreSQL. Как настроить базу, чтобы в ней поддерживался порядок, какие регламентные операции нужно проводить, чтобы данные чистились, индексы перестраивались и оперативная память высвобождалась в своём выступлении на конференции Infostart Event 2019 Inception поделился руководитель ИТ в компании «ИнфоСофт» Антон Дорошкевич. 

07.02.2020    4105    a.doroshkevich    15       

1C:Предприятие для программистов: Расчетные задачи (зарплата). Онлайн-интенсив с 01 по 17 июня 2020 г. Промо

Данный онлайн-курс предусматривает изучение механизмов платформы “1С:Предприятие”, которые предназначены для автоматизации периодических расчетов, а именно - для расчета зарплаты. Курс предназначен для тех, кто уже имеет определенные навыки конфигурирования и программирования в системе “1С:Предприятие”, а также для опытных пользователей прикладного решения “1С:Зарплата и управление персоналом” и прочих прикладных решений, в которых реализован функционал расчета зарплаты.

4900 рублей

Оптимизатор запросов. Вторая часть

Статья Программист Нет файла v8 Бесплатно (free) Производительность и оптимизация (HighLoad)

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

23.01.2020    3395    darkdan77    59       

Улучшаем производительность 1С. Рекомендации

Статья Программист Нет файла v8 1cv8.cf Россия Бесплатно (free) Производительность и оптимизация (HighLoad)

Каждый уважаемый разработчик 1С сталкивался или столкнется с вопросом производительности высоконагруженных систем. В статье агрегирован основной набор рекомендаций, который позволит повысить производительность системы. Эти рекомендации должны быть просто must have по определению.

23.01.2020    4662    Kaval88    26       

1C:Предприятие для программистов: Запросы и отчеты. Второй поток. Онлайн-интенсив с 17 марта по 16 апреля 2020 г. Промо

Данный онлайн-курс предусматривает углубленное изучение языка запросов и возможностей системы компоновки данных, которые понадобятся при разработке отчетов, работающих на платформе “1С:Предприятие” в рамках различных прикладных решений. Курс предназначен для тех, кто уже имеет определенные навыки конфигурирования и программирования в системе “1С:Предприятие”, а также для опытных пользователей различных прикладных решений, которые используют в своей работе отчеты разного назначения.

6500 рублей

Атака сервера кнопонажималкой

Статья Программист Нет файла Бесплатно (free) Нагрузочное тестирование

Чтобы убедиться, что продукт выдержит планируемую нагрузку, необходимо провести нагрузочное тестирование – написать сценарии пользовательских действий и запустить их в несколько потоков, чтобы заранее найти проблемы в бизнес-логике и «узкие места». О том, как упростить написание сценариев тестирования для конфигурации Тест-центр с помощью фреймворка Vanessa Automation на конференции Infostart Event 2019 Inception рассказал ведущий программист компании «ПервыйБИТ» Никита Грызлов.

20.01.2020    2912    nixel    16       

Мониторим производительность с помощью 1С RAS

Статья Системный администратор Программист Нет файла v8 1cv8.cf Бесплатно (free) Инструментарий разработчика Производительность и оптимизация (HighLoad)

Подключаемся и анализируем данные через 1С RAS. Необходимо выполнить 5 пунктов и серьезный инструмент мониторинга будет у вас в руках.

19.12.2019    5585    ivanov660    12       

Программы для исполнения 54-ФЗ Промо

С 01.02.2017 контрольно-кассовая техника должна отправлять электронные версии чеков оператору фискальных данных - правила установлены в 54-ФЗ ст.2 п.2. Инфостарт предлагает подборку программ, связанных с применением 54-ФЗ, ККТ и электронных чеков.

Весёлые картинки о работе Performance Monitor на Windows Server 2016 Std по мотивам расследования потери производительности на базе 1С

Статья Системный администратор Нет файла v8 Windows Бесплатно (free) Производительность и оптимизация (HighLoad)

Данная публикация посвящена одной особенности Performance Monitor на Windows Server 2016 Std. Как понимать графики Performance Monitor на Windows Server 2016 Std при расследовании проблем в работе 1С.

22.10.2019    3945    EugeneSemyonov    11       

Готовые переносы данных из различных конфигураций 1C Промо

Рекомендуем готовые решения для переноса данных из различных конфигураций 1C. C техподдержкой от разработчиков и гарантией от Инфостарт.

Набор скриптов для знакомства с SQL Server

Статья Системный администратор Программист Нет файла Бесплатно (free) Производительность и оптимизация (HighLoad) Администрирование СУБД

Поговорим о скриптах, которые помогут быстро ознакомиться с состоянием SQL Server, в том числе с вопросами производительности.

30.09.2019    15524    YPermitin    14       

Мониторинг высоконагруженной системы

Статья Системный администратор Программист Нет файла v8 Бесплатно (free) Производительность и оптимизация (HighLoad) Администрирование данных 1С

Высоконагруженной системе (более 8000 клиентских сессий) мониторинг необходим. Про опыт использования инструментов для мониторинга – самописной системы информирования, написанной на C#, и конфигурации «Центр контроля качества» в связке с системой отображения данных Grafana, на конференции Infostart Event 2018 Education рассказал Олег Репников.

13.09.2019    5944    Repich    5       

Подборка программ для взаимодействия с ЕГАИС Промо

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

Использование Zabbix для сбора информации о серверных вызовах и управляемых блокировках с сервера 1С Предприятия, работающего на платформе GNU/Linux

Статья Системный администратор Программист Нет файла v8 Linux Бесплатно (free) Администрирование данных 1С Zabbix

Описанные в данном опусе механизмы ни в коей мере не противопоставляются тому, что реализует КИП от 1С или какие-либо другие инструменты (решения)! Это всего лишь еще один взгляд на "проблему", который может быть полезен в некоторых ситуациях.

10.09.2019    11378    Sloth    11       

Онлайн-курс "Подготовка к экзамену 1С:Эксперт и 1С:Профессионал по технологическим вопросам" с 7 по 24 апреля 2020 г. Промо

На курсе вы получите практические навыки решения задач производительности 1С, в том числе характерных для высоконагруженных информационных систем (более 1000 пользователей). Подготовка к экзамену – только одна из составляющих курса. 70% слушателей приходят за знаниями, которые позволят расти и зарабатывать, делать сложные задачи на крупных проектах.

16450 рублей

Руководство по SQL: Как лучше писать запросы (Часть 2)

Статья Системный администратор Программист Нет файла СУБД Бесплатно (free) Производительность и оптимизация (HighLoad)

Предлагаю вашему вниманию продолжение перевода статьи Karlijn Willems SQL Tutorial: How To Write Better Queries". Оригинал доступен по ссылке https://www.datacamp.com/community/tutorials/sql-tutorial-query. Первая часть доступна по ссылке https://infostart.ru/public/1115809/

03.09.2019    5196    w.r.    1       

Анализ производительности APDEX

Отчеты и формы Системный администратор Программист Внешний отчет (ert,erf) v8 1cv8.cf Бесплатно (free) Производительность и оптимизация (HighLoad)

Отчет для просмотра и анализа замеров производительности в конфигурациях на базе БСП.

31.08.2019    6017    144    YPermitin    7       

Подборка решений для взаимодействия со ФГИС «Меркурий» Промо

С 1 июля 2019 года все компании, участвующие в обороте товаров животного происхождения, должны перейти на электронную ветеринарную сертификацию (ЭВС) через ФГИС «Меркурий». Инфостарт предлагает подборку программ, связанных с этим изменением.

Руководство по SQL: Как лучше писать запросы (Часть 1)

Статья Системный администратор Программист Нет файла Бесплатно (free) Производительность и оптимизация (HighLoad)

Предлагаю вашему вниманию перевод статьи Karlijn Willems SQL Tutorial: How To Write Better Queries". Оригинал доступен по ссылке https://www.datacamp.com/community/tutorials/sql-tutorial-query. Узнайте о антипаттернах, планах выполнения, time complexity, настройке запросов и оптимизации в SQL.

30.08.2019    6434    w.r.    0       

Использование Union вместо OR

Статья Системный администратор Программист Нет файла MS SQL Бесплатно (free) Производительность и оптимизация (HighLoad)

Предлагаю вашему вниманию перевод статьи Derek Dieter "Using Union Instead of OR". Оригинал доступен по ссылке http://sqlserverplanet.com/optimization/using-union-instead-of-or.

22.08.2019    2545    w.r.    35       

​​​​​​​CorelDRAW Graphics Suite 2019 Промо

CorelDRAW – пакет профессиональных инструментов для редактирования фотографий, разработки дизайна, создания макетов страниц и векторных иллюстраций

Тюнинг производительности запросов в PostgreSQL

Статья Системный администратор Программист Нет файла PostgreSQL Бесплатно (free) Производительность и оптимизация (HighLoad)

Предлагаю вашему вниманию перевод статьи Brady Holt "Performance Tuning Queries in PostgreSQL ". Оригинал доступен по ссылке https://www.geekytidbits.com/performance-tuning-postgres/

31.07.2019    4895    w.r.    5       

Неочевидные проблемы производительности: важность системного подхода при анализе

Статья Программист Нет файла v8 Россия MS SQL Бесплатно (free) Производительность и оптимизация (HighLoad)

Часто программисты и 1С-ники сталкиваются с совершенно необъяснимыми на первый взгляд проблемами. Но это потому, что их внимание направлено только на один сегмент системы, а не на всю систему полностью. О том, почему нужно стараться смотреть на ситуацию комплексно, рассказал специалист по производительности компании SOFTPOINT Александр Денисов.

19.07.2019    6269    Филин    12       

Базовый курс по обмену данными в системе 1С:Предприятие. Онлайн-интенсив с 12 по 28 мая 2020 г. Промо

Данный онлайн-курс предусматривает изучение механизмов платформы “1С:Предприятие”, обеспечивающих обмен данными между различными прикладными 1С-решениями и взаимодействие с другими информационными системами. Курс предназначен для тех, кто уже имеет определенные навыки конфигурирования и программирования в системе “1С:Предприятие”.

5500 рублей

Ловля блокировок на связке "Microsoft SQL server - 1С"

Статья Системный администратор Программист Нет файла v8 v8::blocking MS SQL Бесплатно (free) Производительность и оптимизация (HighLoad)

Материал относится к базам данных на связке «1С - MS SQL Server». Один из способов отлова блокировок в бд 1С . Переход к управляемым блокировкам через режим "Автоматический и управляемый".

16.07.2019    6202    fhqhelp    0       

Настройка параметров PostgreSQL для оптимизации производительности

Статья Системный администратор Программист Нет файла PostgreSQL Бесплатно (free) Производительность и оптимизация (HighLoad)

Предлагаю вашему вниманию перевод статьи Ibrar Ahmed "Tuning PostgreSQL Database Parameters to Optimize Performance". Оригинал доступен по ссылке https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/

08.07.2019    4908    w.r.    13       

Управление ИТ-проектами. Модуль 2: продвинутый онлайн-курс по классическим методам управления проектами. Вебинары проходят с 12 марта по 11 июня 2020 года. Промо

Продвинутый онлайн-курс по классическому управлению ИТ-проектами позволит слушателям освоить инструменты из PMBoK® и 1С:Технологии корпоративного внедрения и научиться их применять для проектов любого масштаба. Курс включает в себя 12 вебинаров и 12 видеолекции, разбор кейсов и рекомендации экспертов по проектам слушателей. Ведущая курса - Мария Темчина.

от 13000 рублей

Сравнительное тестирование работы PostgreSQL с большими страницами Linux

Статья Системный администратор Программист Нет файла Linux Бесплатно (free) Производительность и оптимизация (HighLoad)

Представляю вашему вниманию перевод статьи Ibrar Ahmed "Benchmark PostgreSQL With Linux HugePages". Оригинал расположен по ссылке https://www.percona.com/blog/2018/12/20/benchmark-postgresql-with-linux-hugepages/

05.07.2019    3063    w.r.    6       

Настройка параметров ядра Linux для оптимизации PostgreSQL

Статья Системный администратор Программист Нет файла Linux Бесплатно (free) Производительность и оптимизация (HighLoad)

Предлагаю вашему вниманию перевод статьи Ibrar Ahmed "Tune Linux Kernel Parameters For PostgreSQL Optimization". Оригинал доступен по ссылке https://www.percona.com/blog/2018/08/29/tune-linux-kernel-parameters-for-postgresql-optimization/

05.07.2019    3722    w.r.    1       

Голосование за доклады на INFOSTART MEETUP Kazan - до 25 февраля. Промо

Выбирайте и голосуйте за самые интересные доклады! Лучшие из лучших попадут в окончательную программу казанского митапа. Оставить свой голос можно до 25 февраля 2020 года.

Анти-оптимизация: как мы ускорили запрос в 4 раза, сделав его неоптимальным

Статья Программист Нет файла v8 Бесплатно (free) Производительность и оптимизация (HighLoad) Практика программирования Решение задач на 1С:Специалист Разработка

В этой статье приведен пример неочевидной "оптимизации" запроса, которая противоречит всем правилам, описанным в книгах для подготовки к сертификации "1С:Эксперт по технологическим вопросам", а также преподаваемым на курсах подготовки экспертов.

02.07.2019    8128    igordynets    119       

Сравнительное тестирование PostgreSQL на FreeBSD, CentOS, Ubuntu Debian и openSUSE

Статья Системный администратор Программист Нет файла PostgreSQL Бесплатно (free) Производительность и оптимизация (HighLoad)

Данная статья является переводом оригинальной статьи Martin Kováčik "PostgreSQL benchmark on FreeBSD, CentOS, Ubuntu Debian and openSUSE" https://redbyte.eu/en/blog/postgresql-benchmark-freebsd-centos-ubuntu-debian-opensuse/ В ней рассматриваются тесты СУБД PostgreSQL 10.1 в приближенных к реальным условиям средах на различных unix-системах

30.06.2019    4561    w.r.    2