Система сбора и анализа информации по производительности работы баз данных, работающих  под связкой «кластер 1С 8.2/8.3 - Microsoft SQL server»

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

Администрирование - Оптимизация БД (HighLoad)

анализ производительности узкие места sql-сервер тяжелые запросы

68
Инструмент в помощь специалисту по производительности sql-серверов с базами 1С. Программа (c#) собирает наиболее заметные (по времени исполнения, физическим / логическим чтениям / загрузке CPU ) запросы, группирует по обобщенным текстам запросов и контекстам исполнения 1С (если есть), предоставляет информацию в виде отчетов по наиболее заметным запросам и прочей информации (длительные запросы по данным техжурналов, содержимое буферпула в динамике, содержимое клерков памяти, ожидания сервера в разрезе бд, статистика ввода-вывода по файлам баз).

    Идея довольно проста. Берем парсер файлов техжурнала, настраиваем ТЖ на ловлю запросов долее N секунд, данные из парсера заливаем в sql-табличку. Рядом с парсером настраиваем трассировку длительных запросов (долее M секунд) уже со стороны sql-сервера (либо server-side trace sp_trace_create, либо используем eXtended Events) - результат тоже в sql-табличку. Делаем это непрерывно, данные старше 2 недель удаляем.  Две таблички вполне можно связать друг с другом - получим с одной стороны статистику из трасс (Duration, cpu_time, logical_reads, physical_reads, writes), а с другой - контекст исполнения, пользователя в 1С, имя клиентского компа и т.д.  Текст запроса огрубляем.

    Теперь можно анализировать загрузку sql-сервера за период - выявление самых длительных запросов, нагружающих CPU, читающих и т.д.  При этом, выявив "тяжелый" запрос - сразу видим и контекст исполнения, и историю исполнения, а при желании - и текст запроса в терминах конфигурации.

    Далее, к получившейся программе (парсер + трасировщик) добавляем еще и периодический опрос sys.dm_exec_query_stats - и складываем статистику исполнения в третью sql-табличку. Теперь дополнительно добавляется еще и возможность поймать быстрые (быстрее M секунд), но очень массовые запросы, пожирающие время сессий, CPU, генерящие физические и логические чтения.  Также, теперь при просмотре "тяжелых" запросов из предыдущего пункта - можно исхитриться и подсмотреть план исполнения запроса (и в терминах конфигурации тоже).

  Используется все это, как правило, в двух режимах:

1. Анализ активности сервера за длительный период. За две недели, например, последовательно выявляем top 5 наиболее длительных запросов, каждый отдельно рассматриваем - нет ли грубых ошибок (сканы крупных таблиц в планах и т.п.), тормозят ли они живых людей или роботов, можно ли их ускорить, нужно ли их вообще трогать. Если нужно и можно - создаем задачу разработчикам - с детальным описанием проблемы, указанием проблемного контекста и еще прилагаем планы исполнения. Если есть проблемы с загрузкой sql-сервера по CPU - смотрим top 5 наиболее потребляющих CPU запросов (и частенько видим "знакомые все лица" из top 5 длительных) - и обрабатываем их аналогичным образом. Если есть проблемы с памятью на сервере (низкое значение Page Life Expectancy) - есть смысл отработать top 5 по physical reads. Top 5 по logical reads частенько может показать какие-то ошибки разработчиков в запросах (впрочем - может и НЕ показать). Top 5 по записям, бывает, может намекнуть на какую-то лишнюю активность в базах.

2. Расследование проблем. Если известно, что "в пятницу с трех до пяти тормозили базы А, Б и В на сервере С" и выявлено, что "в пятницу с трех до пяти загрузка sql-сервера С по CPU была ~100% " - можно посмотреть первую пятерку запросов по CPU в указанный период. Работает не всегда. Если запрос убил админ - он не попадет в трассу. Тем не менее, данный способ расследования много раз выручал. Само-собой, только данной системы здесь недостаточно - как минимум дополнительно должен быть настроен сбор счетчиков производительности. 

    Также, можно отдельно смотреть таблицу событий техжурнала - выводить top 5 контекстов по времени (тут, увы - только по времени), если были жалобы от МарьИванны - смотреть события от МарьИванны (или от ее компа GLAVBUH001), и т.д. Это ж sql-табличка, какой хотим запрос - такой и напишем..

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

    Это что есть. Теперь - чего нет, но надо бы..
    Текущая версия не ловит блокировки. Понятно, что блокировки на уровне sql-сервера отразятся в трассе длительных запросами с большим Duration и мелкими (относительно Duration) reads и CPU , но это будет только косвенный намек. Управляемые блокировки не отразятся вообще никак (ну разве что низкой нагрузкой на сервер, возможно). Эта часть пока в процессе реализации. Даже для баз в read commited snapshot оно надо..
    Также нет сбора счетчиков в базу (а джентельменский набор в виде % Processor Time, Process(sqlservr)\% Processor time, Page Life Expectancy, Avg. Disk Sec/Read и Avg. Disk Sec/Write по рабочим дискам - в уважающей себя компании быть обязан.. хотя бы сбор в файлы).
    Также не прикручена система сбора информации о длительности ключевых операций (что-то APDEXоподобное тоже обязано быть).
    Нет и замеров длительности вызовов "клиент-сервер".
    Также, теоретически имеется возможность ловить реальные (действительные, actual) планы исполнения запросов, длившихся долее N секунд. Хочется со временем добавить такое.
    Хотелось бы добавить сбор убитых админами на полном скаку запросов.
    И хранить ошибки из логов sql-сервера, EXCP из техжурнала, и... список бесконечен.
   

    ВАЖНО. Выдержка из мануала про возможные проблемы:

Посему первым делом предупреждения:
1. Включение технологического журнала 1С на сбор данных по dbmssql  (а также по tlock / tdeadlock / ttimeout )  может привести к тем же переполнениям дисков. А т.к. нынче популярны SSD, размеры которых не поражают величиной – требуется отслеживать свободное место на дисках под сбор данных техжурнала и менять пороги времени (<gt property="duration" value="XXX" />) при необходимости. Вручную.
2. Сбор данных на sql-сервере происходит с помощью Extended Events. Расширенные события, конечно, позиционируются как «легковесные, не требующие много ресурсов», и эксперименты подтверждают, что старым добрым SQL Server Profiler-ом свалить сервер не в пример легче.. Но! У всего есть где-то свой предел, не удивлюсь, что и расширенными событиями можно отправить сервер в нокаут. Значит, обращаться следует как с трассировками: ничего лишнего, ненужное выключаем, пороги по времени задаем разумные, отслеживаем зависшие сессии.
3. Сама бд, которая хранит данные о производительности – получается весьма объемной.  Т.к. хранит тексты запросов и планов исполнения. Изначальная идея «чтоб все влезло в 10 Gb и шло на ноутбуке с ms sql enterprise» почти не работает.. если только задрать настройки. Т.е. придется следить за объемом базы для сбора данных, и корректировать параметры глубины хранения истории (закладка «вспомогательные настройки»)
 
    Протестированы релизы 8.2.19.80 и 8.3.9.2033 + SQL server 2012 (SP1 и SP3). Теоретически должно работать с любыми релизами 1С, с SQL server 2008, и с SQL server 2016.

    Текущая версия - за стартмани. Для sql-серверов с объемом баз менее терабайта так и планируется оставить бесплатной, для прочих - ввести ежемесячный платеж в районе даже не знаю скольки (500?) рублей. Зависит от того, насколько оно все вообще кого-то заинтересует. Будущее разработки туманно. Возможно, станет "исключительно за деньги", а может быть и "исходники на GitHub". 

    Программа-сборщик, кроме основной деятельности, собирает информацию о запустившем компе (мак-адрес, инфа об оборудовании и ОС, имена изучаемых sql-серверов и баз на них, размер баз) и раз в сутки отправляет в сеть. Сделано для сбора статистики о пользователях и под будущую систему ключей авторизации. Пока (до 01.04.2018) работает и без доступа к сети. Потом данная версия потребует доступ или перестанет запускаться. Примерно тогда же планирую определиться с ценами, дальнейшей судьбой системы и т.п. В итоге, все это затеяно в надежде заработать хотя бы в размере "жене на сапоги". Perfexpert дорог, сервис //www.gilev.ru/querytj нацелен более на техжурнал.. возможно, и под данное изделие место на рынке осталось.

    Текущая версия работоспособна до 01.01.2019, потом прекратит работать. К этому моменту или появится новая версия, или .. или не появится.

    Код сборщика закрыт, часть функциональности вынесена в dll. Код просмотрщика открыт.

    Мануал вышел на полсотни страниц.. вообще-то мысль была сделать "кратко на пару листов", но получилось внезапно вот так. Выкинуть чего-либо не поднимается рука. Запускать сборщик без прочтения сего талмуда - категорически не рекомендую.
Так же, рискнувшему запустить требуется знать:
 - как в SQL Server Management Studio выглядят сессии eXtended Events и как их останавливать;
 - что такое техжурнал, и зачем оно надо.
 
68

Скачать файлы

Наименование Файл Версия Размер
Система сбора и анализа информации по производительности работы баз данных, работающих  под связкой «кластер 1С 8.2/8.3 - Microsoft SQL server».
.7z 2,25Mb
10.12.17
88
.7z 1.0.1.0 2,25Mb 88 Скачать

См. также

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

Комментарии
Избранное Подписка Сортировка: Древо
1. Darklight 17 12.12.17 14:27 Сейчас в теме
Хорошая вещь. Сам давно уже хотел сделать нечто подобное. Но, несколько более универсальное с чуть большими возможностями по сбору данных (в т.ч. счётчиков, в т.ч. генерирующее свои собственные счётчики), с компактной упаковкой а таблицы SQL и с WEB-доступом. На кросплатформенном .NET Core. В Качестве локального сервиса/демона. Без отправки чего-либо куда-либо (кроме почтовых рассылок отчётов и инфорормирования в месенджер об авариях).
4. fhqhelp 258 26.12.17 10:07 Сейчас в теме
(1) Как показывает практика - главное начать :)
3. DarkAn 808 25.12.17 11:02 Сейчас в теме
Word 2010 мануал открывает с проблемами :( - картинки не видит
а 6 картинок - есть (слайд 2)
Прикрепленные файлы:
5. fhqhelp 258 26.12.17 10:09 Сейчас в теме
(3) Попробую чуть позже выложить копию мануала в формате Word-а.
6. palsergeich 05.02.18 14:10 Сейчас в теме
7. VVi3ard 48 07.02.18 15:07 Сейчас в теме
На мой взгляд хватило бы контекста 1С и актуальных планов исполнения, там уже есть текст запроса, есть данные как по Estimated так и по Actual. При этом схема отлично ложится в колонку с типом XML.
Информация о контексте позволит найти сам текст запроса на языке 1С что бы понять откуда запрос.

Основная проблема для меня это понять как собирать актуальные планы только для тех запросов которые выполняются больше N секунд.
т.к. вариант "Собирать все планы" и отсеивать из собранных не нужные - значительно нагружает SQL сервер.

Интересно было бы почитать именно про:
"Также, теоретически имеется возможность ловить реальные (действительные, actual) планы исполнения запросов, длившихся долее N секунд."
Хотя бы на уровне идеи.
8. astratek 1 13.03.19 16:59 Сейчас в теме
>>Текущая версия работоспособна до 01.01.2019, потом прекратит работать. К этому моменту или появится новая версия, или .. или не появится.
На дворе 2019 год, как обстоят дела с обновлением ?
9. fhqhelp 258 16.03.19 15:12 Сейчас в теме
Новая версия, с ловлей блокировок управляемых и автоматических, и со сбором показаний консоли серверов - готова.
Хотел написать пару новых статей, да начать продавать.
Неясно, однако: какую цену назначать, как защищать от распространения и защищать ли, как обеспечить техподдержку.
И вообще - стоит ли оно усилий.
Посему пребываю пока в некоем творческом ступоре.
Оставьте свое сообщение