Для визуализации данных и метрик ИТ-инфраструктуры мы используем Zabbix+Grafana, DataDog, ElasticSearch+Kibana, специализированные пакеты мониторинга для разных СУБД. Но эти инструменты не всегда дают необходимый результат. Поэтому мы дополнительно создали мониторинг, который делает ровно то и ровно так, как надо. Этот материал будет полезен тем, кто хочет улучшить свою систему мониторинга, кого не устраивает гибкость существующего инструмента, цена или поддержка.
Ниже приведены простые примеры, которые сможет повторить каждый, исходя из собственных СУБД и потребностей в рисовании красивых и интерактивных графиков (диаграмм, карт, деревьев и т.д.) с небольшим размером. СУБД при этом не обязательна, можно создать пример на любом языке программирования.
Решение первое: простой график Google Charts
Пример простого графика
<html>
<head>
<style type="text/css">
.legend{
font-size:16pt;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-weight: bold;
text-align: center;
}
</style>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js">
</script>
<script type="text/javascript">
google.charts.load('current', {packages: ['corechart', 'line'], language: 'ru'});
google.charts.setOnLoadCallback(drawBasic);
function drawBasic() {
var color0 = '#0066ff'
var color1= '#ff4d4d'
var color2 = 'green'
var options1 =
{
lineWidth: 2,
hAxis: {
format: 'yyyy-MM-dd',
slantedText: true,
slantedTextAngle: 45,
textStyle: {
fontSize: 12
},
},
vAxes: {
0: {
title: 'Deadlocks title',
viewWindow: {
max:undefined
}
},
},
colors: [color0, color1, 'transparent'],
series: {
0:{targetAxisIndex:0},
1:{targetAxisIndex:0}
}
};
var data1= new google.visualization.DataTable();
data1.addColumn('datetime', 'X');
data1.addColumn('number', 'Deadlocks Count');
data1.addRows([
[new Date(2021, 10, 17, 8, 55, 30), 1],
[new Date(2021, 10, 18, 8, 55, 30), 1],
[new Date(2021, 10, 19, 8, 55, 30), 2],
[new Date(2021, 10, 20, 8, 55, 30), 1],
[new Date(2021, 10, 21, 8, 55, 30), 1],
[new Date(2021, 10, 22, 8, 55, 30), 1],
[new Date(2021, 10, 24, 9, 35, 30), 1],
[new Date(2021, 10, 25, 9, 35, 31), 1],
[new Date(2021, 10, 25, 13, 55, 30), 1],
[new Date(2021, 10, 25, 14, 45, 30), 1],
[new Date(2021, 10, 26, 9, 35, 30), 3],
[new Date(2021, 10, 27, 9, 35, 30), 1],
[new Date(2021, 10, 28, 13, 45, 30), 1],
[new Date(2021, 10, 29, 6, 35, 30), 1],
]);
var chart1 = new google.visualization.LineChart(document.getElementById('chart_div1'));
chart1.draw(data1, options1);
}
</script>
</head>
<table>
<tr>
<td><div class="legend">[ServerName] Deadlocks count</div></td>
</tr>
<tr>
<td>
<div id="chart_div1" style="width: 1600px; height: 650px"></div>
</td>
</tr>
</table>
</html>
Возможностей у Google Charts много. Ознакомиться с ними можно на их сайте. Всё это бесплатно.
Ссылка на основной loader.js, который занимается отрисовкой в окне браузера: <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js">
Помимо установок шрифтов, цветов, надписей для осей графика есть список данных. Это то, что вы получаете из БД и вставляете в текстовый файл:
var data1= new google.visualization.DataTable();
data1.addColumn('datetime', 'X');
data1.addColumn('number', 'Deadlocks Count');
data1.addRows([
[new Date(2021, 10, 17, 8, 55, 30), 1],
[new Date(2021, 10, 18, 8, 55, 30), 1],
…
[new Date(2021, 10, 29, 6, 35, 30), 1],
]);Здесь все просто: замените на свои данные, и график будет готов. Вам останется в заголовках и названиях осей прописать свои подписи. Размер файла HTML 2 Кб. Трафик и место на дисках для полноценной картинки будут расходоваться экономно.
Пример мониторинга СУБД
Теперь вы можете брать данные из БД и рисовать диаграммы: бизнес-показатели, мониторинг процессов и т.д.
Для простоты возьмём Microsoft SQL Server. В ней просто создать джоб, написать скрипт, который при настроенной учётной записи почты высылает письмо. Если у вас в некой таблице складываются данные, то просто заполните текстовую строку этими данными и высылайте по почте.
Пример: у вас есть таблица, куда складывается число дедлоков за промежуток времени. Дедлоки — это перекрёстные блокировки объектов разными сессиями, которые могут быть разрешены только прерыванием работы скрипта у одной сессии. Сервер MS SQL сам решает кого оборвать, хотя на это можно влиять. В общем, мы отслеживаем нехорошие проявления блокировок.
CREATE TABLE [dbo].[MonitoringDeadlocks](
[DateOfAdded] [DATETIME] NOT NULL,
[CountOfDDeadlocksAll] [INT] NOT NULL,
[CountOfDDeadlocksByPeriod] [INT] NOT NULL,
PRIMARY KEY CLUSTERED ([DateOfAdded] ASC)
)ON [PRIMARY]
GO
Как формировать текст, отсылать письмо с заголовком, отправлять по почте.
Declare @CountOfDDeadlocksByPeriod INT -- Число дедлоков за последний период
SELECT TOP 1 @CountOfDDeadlocksByPeriod=ISNULL(CountOfDDeadlocksByPeriod,0) FROM MonitoringDeadlocks MD WITH(nolock) ORDER BY MD.DateOfAdded desc
DECLARE @html VARCHAR(MAX)='';
DECLARE @t VARCHAR(MAX)='';
-- Статичная часть до таблицы
SET @html = '
<html>
<head>
<style type="text/css">
.legend{
font-size:16pt;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-weight: bold;
text-align: center;
}
</style>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js">
</script>
<script type="text/javascript">
google.charts.load(''current'', {packages: [''corechart'', ''line''], language: ''ru''});
google.charts.setOnLoadCallback(drawBasic);
function drawBasic() {
var color0 = ''#0066ff''
var color1= ''#ff4d4d''
var color2 = ''green''
var options1 =
{
lineWidth: 2,
hAxis: {
format: ''MM-dd HH:MM'',
slantedText: true,
slantedTextAngle: 45,
textStyle: {
fontSize: 12
},
},
vAxes: {
0: {
title: '' '',
viewWindow: {
max:undefined
}
},
},
colors: [color0, color1, ''transparent''],
series: {
0:{targetAxisIndex:0},
1:{targetAxisIndex:0}
}
};
var data1= new google.visualization.DataTable();
data1.addColumn(''datetime'', ''X'');
data1.addColumn(''number'', ''Deadlocks Count'');
data1.addRows([
';
-- В текстовую переменную складываем значения, в виде которые поймёт JS скрипт
------------------
WITH cte AS
(
SELECT DateOfAdded, CountOfDDeadlocksByPeriod
FROM [msdb].[dbo].[MonitoringDeadlocks]s WITH (NOLOCK)
WHERE 1 = 1
AND DateOfAdded > DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
)
SELECT @t = @t +'[new Date(' +
CAST(DATEPART(YEAR, DateOfAdded) AS VARCHAR(4)) + ', ' +
CAST(DATEPART(MONTH, DateOfAdded) - 1 AS VARCHAR(2)) + ', ' +
CAST(DATEPART(DAY, DateOfAdded) AS VARCHAR(2)) + ', ' +
CAST(DATEPART(HOUR, DateOfAdded) AS VARCHAR(2)) + ', ' +
CAST(DATEPART(MINUTE, DateOfAdded) AS VARCHAR(2)) + ', ' +
CAST(DATEPART(SECOND, DateOfAdded) AS VARCHAR(2)) + '), ' +
CAST(cte.CountOfDDeadlocksByPeriod AS VARCHAR(100)) + '],
'
FROM cte
ORDER BY cte.DateOfAdded
-- Добавили таблицу
SET @html = @html + @t
-- Статичная часть после таблицы
SET @html = @html + '
]);
var chart1 = new google.visualization.LineChart(document.getElementById(''chart_div1''));
chart1.draw(data1, options1);
}
</script>
</head>
<table>
<tr>
<td><div class="legend">[' + @@SERVERNAME + ']: Deadlocks count</div></td>
</tr>
<tr>
<td>
<div id="chart_div1" style="width: 1500px; height: 650px"></div>
</td>
</tr>
</table>
</html>
'
-- Используем временный объект (реально по другому работает, но это простой пример)
IF OBJECT_ID('tempdb..##tempTableForSentGraphd') IS NOT NULL
DROP TABLE ##tempTableForSentGraphd;
CREATE TABLE ##tempTableForSentGraphd (value VARCHAR(MAX));
INSERT ##tempTableForSentGraphd ([value]) VALUES(@html);
-- Высылаем письмо
DECLARE @theme NVARCHAR(255),
@Message NVARCHAR(MAX)
SET @theme = N'DEADLOCKS: ' + @@SERVERNAME ;
SET @Message = N'ATTENTION: The DeadlocksMonitoring job on ' + @@SERVERNAME + N' has detected ' + CAST(@CountOfDDeadlocksByPeriod AS NVARCHAR(255)) + N' deadlocks in the past 10 minutes'
EXEC msdb.dbo.sp_send_dbmail_ansi
@profile_name = N'<!!!! ПРОФИЛЬ ПОЧТЫ В MS SQL !!!!!!>',
@recipients = '!!!<АДРЕСА ПОЛУЧАТЕЛЕЙ ЧЕРЕЗ ТОЧКУ С ЗАПЯТОЙ>!!!',
@subject = @theme,
@body = @Message,
@query ='set nocount on
SELECT value FROM ##tempTableForSentGraphd',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'graph.html',
@query_no_truncate = 1
IF OBJECT_ID('tempdb..##tempTableForSentGraphd') IS NOT NULL
DROP TABLE ##tempTableForSentGraphd;
Краткое описание кода. В переменную CountOfDDeadlocksByPeriod сложили число дедлоков за период. Здесь проверки нет, как в реальном коде. К статичным частям документа добавляются данные за 1 день в формате, который ожидает JS скрипт. И потом отсылаем письмо, но тут мы поменяли стандартную функцию dbo.sp_send_dbmail на собственную msdb.dbo.sp_send_dbmail_ansi, в которой в аттаче вырезается признак BOM для Unicode.
Решение второе: реализация мониторинга на MS SQL Server
Допустим, вы захотели следить за работой хранимых процедур (ХП). А также собирать и хранить данные о работе, чтобы была возможность задним числом посмотреть, что происходило.
Для начала вы решили отслеживать, когда процедура начинает работать хуже или дольше, потреблять больше CPU, или, допустим, когда количество вызовов увеличивается. При этом вы хотите совершенно не нагружать и без того нагруженные SQL сервера.
Если разбить задачу на части, то получится следующее:
Сбор и хранение данных о работе хранимых процедур;
Анализ собранных данных, оценка работы процедур, сбор и хранение данных о процедурах, которые стали работать хуже;
Оповещение о таких процедурах;
Визуализация: в оповещении для наглядности должны быть не только цифры, но и графики.
1. Сбор данных
Где можно найти информацию о работе ХП? Например, в трэйсах, расширенных события. Но тогда пункт «не нагружать сервер» может и не сработать. Пример из жизни: полный трэйс, который собирал все запросы, сам употреблял примерно 7% CPU сервера и тратил ресурсы дисков. Сбор данных решено было сделать из динамической вью sys.dm_exec_procedure_stats. Она содержит нарастающие данные о количестве вызовов, потраченном CPU, суммарной длительности работы процедур. Также есть время создания плана выполнения – можно видеть когда менялся план. Делаем джоб, который раз в 10 минут снимает данные из вью и складывает их в таблицу. 10 минут – некий период опроса, не слишком редкий и не слишком частый. Ничего не мешает выбрать другой период. Из сравнения данных последнего и предпоследнего периодов легко вычисляется количество вызовов, потраченное CPU и длительность работы ХП за последние 10 минут. На основе этих данных можно вычислить средние значения CPU и длительности, а также процент CPU среди всех процедур за период.
Кажется всё просто: снимай данные и анализируй. Но если у процедуры менялся план выполнения (оптимизатор поменял план или процедура была обновлена), то счетчики сбрасываются и начинают увеличиваться заново с нуля. Получается, что работу ХП в таком периоде не оценить. Приходится его просто игнорировать. К счастью, план меняется довольно редко, и картина работы процедур выглядит весьма цельной.
Оценка будет ниже в статье. Пока стоит пояснить, что данные снимались в «2021-10-22 05:35:30». Видна дата создания плана для каждой процедуры. Например, dbo.Search, видно, выполнилась 2716 раз за 10 минут, работала в среднем 126 мс и тратила 125 CPU. При этом среди всех процедур на сервере (не для одной базы, а для всего SQL сервера) она создала 13,7% нагрузки в этом периоде.
Посмотрев данные о работе процедуры dbo.SelectID за несколько десятиминутных периодов, видно, что она работает ровно. Длительность и CPU не меняются.
Данные в sys.dm_exec_procedure_stats по большей части точные. Но иногда SQL сервер может написать в эту вью ерунду. Например, однажды я обнаружил в собираемых данных, что одна из процедур стала вызываться за 10 минут не 1000, как обычно, а 10 млн раз. Вскоре выяснилось, что у SQL сервера произошло временное помутнение, и он начал записывать в sys.dm_exec_procedure_stats ошибочные данные по одной процедуре. А потом самостоятельно прекратил. Такая ситуация происходит не часто, но бывает на разных серверах и процедурах. Собираемую статистику такие данные портят. Как решать — помечать строки с некорректными данными как игнорируемые. На итоговую картину они не сильно влияют.
Собираемые данные можно не чистить месяцами. Даже на крупных SQL серверах, на которых вызываются тысячи разных ХП, таблица с данными не занимает много места. В любой момент их можно переместить в архив. Нагрузка от процедур мониторинга чрезвычайно мала.
Какие есть минусы. Частое изменение плана у ХП не даст нормально использовать этот механизм. Как выше описывалось, счетчик в этом случае сбрасывается, данные собираются некорректные. Также если процедура вызывается редко, например, один раз в день, то SQL сервер может вычистить план процедуры из кэша и информацию о работе ХП из sys.dm_exec_procedure_stats. И получается, что статистику не собрать. Также все данные в sys.dm_exec_procedure_stats пишутся только по хранимым процедурам. Если на сервере есть запросы без использования ХП, то этим механизмом они никак не учитываются.
2. Анализ данных
Конечно, всё это делалось не для того, чтобы просто собирать и хранить данные. Практическую пользу приносит их анализ. В первую очередь нас интересует увеличение CPU или длительности. Если процедура работала ровно, потребляя, например, 1 ms CPU и вдруг стала потреблять 10 ms CPU – то это проблема, но для частых процедур. Например, оптимизатор решил поменять план и сделал хуже. Если процедура вызывается нечасто и тратит мало CPU, то такое ухудшение сложно заметить. Но это может и не быть проблемой.
С помощью анализа собранных данных о работе процедур увеличение замечается, оценивается его проблемность. Далее можно поработать со статистиками, самой процедурой или сбросить план процедуры (используется sp_recompile с именем процедуры). После обновления процедуры на новую версию она может начать работать хуже. А это значит, что её необходимо оптимизировать.
Увеличение длительности работы при том же CPU также может говорить о проблемах. Типичный случай: ХП вместо 50 миллисекунд стала работать секунду. Посмотрели ожидания - ASYNC_NETWORK_IO. Оказалось, что в одну из таблиц ошибочно вставилось много ненужных строк. ХП и SQL честно и быстро их отдавала, а вот вызывающий процедуру сервис не мог быстро эти данные принять и работал с перебоями.
3. Оповещение
Алгоритмы оценки работы процедур продумывали сами: во сколько раз хуже процедура должна работать по CPU или по длительности; сколько периодов подряд; какие процедуры надо игнорировать при оценке и т.п. Данные о некорректной работе складываются в отдельную таблицу. Можно их просто хранить, а потом просматривать. Можно сразу оповещать заинтересованных лиц.
Оповещения делается на почту через sp_send_dbmail. Первый шаг джоба собрал данные, второй оценил и при необходимости отправил письмо. Ещё в текст письма вставили наглядную таблицу при помощи тегов HTML.
Для примера, оповещение об ухудшении работы одной из процедур:
В данном случае высылается вот такая информация: оценивается последний период 10 минут, последние 5 периодов по 10 минут, последние 2 недели.
Письмо с «@body_format = 'HTML'». HTML формируется в скрипте SQL через динамику. С тэгами и данными. Вместе с CSS.
MS Outlook не очень требователен к правильной разметке и письмо отображается корректно, даже если в разметке есть ошибки. Но если отправлять письмо в gmail и смотреть через браузер, то разметка должна быть правильной, иначе всё будет показываться криво.
3. Визуализация
Не все могут понять масштаб проблемы по цифрам. Нагляднее анализировать графики. Для вышеуказанного оповещения приложены графики за 2 недели с CPU, длительностью и количеством вызовов.
График довольно простой, но к нему добавили кнопки. Можно смотреть CPU, длительность и количество вызовов по отдельности, масштабировать график если не видно.
Этот же график, но с 2 осями. По одной — длительности, по другой — количество вызовов. Размер графика — десятки килобайт максимум.
Аттач с html-файлом формируется также в динамике SQL, как в простом примере ранее. Внутри простой html с кнопками и JS от Google Chart.
Фрагмент файла:
Еще один фрагмент файла. Данные снова передаются обезличенным массивом.
При некоторых проблемах хочется, чтобы автоматом обновлялись статистики и сбрасывался план процедуры. Если при анализе данных видно, что изменилась дата кэширования плана процедуры и увеличилось CPU этой процедуры – делается отметка. И еще один джоб обновляет статистики и сбрасывает план процедуры. Но не более двух раз за N часов. Если уж 2 раза подряд такие манипуляции не помогают, тогда нужно разбираться вручную.
В оповещении приходит отметка, что у процедуры будет сброшен план.
Существует ещё один частый сценарий. Обнаружили, что некая процедура стала работать в несколько раз хуже, чем пару месяцев назад. Почему же тогда мониторинг ничего не прислал? Оказалось, что процедура изо дня в день работала чуть хуже. Сравнение текущего периода (10 минут) с аналогичным прошлым периодом никакой динамики не показывало. Но в долгосрочной перспективе работа ухудшилась. Обычно это происходит после плавного роста таблицы и плана работы процедуры со сканом таблицы.
Чтобы отслеживать такие, был сделан еще один анализ. Ежедневно работа процедур сравнивается с их работой месяц назад. Высылается оповещение с графиком.
Пример:
График за 2 месяца. Пример, как что-то идет не так: процедура работает дольше и CPU выросло.
Но есть всевозможные исключения. Как оценить процедуру, которая из-за разных входящих параметров может работать как быстро, так и весьма долго? И то, и другое является для неё совершенно нормальным и добавляется в исключения. Есть настроечные таблицы, которые могут влиять на принятие решения об алерте, слишком много ложно-положительных предупреждений никому не нужны.
Графики можно присылать не в письме, а сделать веб-приложение с бэком и фронтом, с heat map, где можно будет удобно смотреть диаграммы.
И напоследок текст графика в начале статьи с картинками пива.
<html>
<head>
<style type="text/css">
.imgGr {
border: none;
position: absolute;
}
.legend{
font-size:16pt;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-weight: bold;
text-align: center;
}
</style>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js">
</script>
<script type="text/javascript">
google.charts.load('current', {packages: ['corechart', 'line']});
google.charts.setOnLoadCallback(drawBackgroundColor);
function drawBackgroundColor() {
// Данные для графика
var data = new google.visualization.DataTable();
data.addColumn('number', 'X');
data.addColumn('number', 'Y');
data.addRows([
[0, 0], [1, 10], [2, 23], [3, 17], [4, 18], [5, 9],
[6, 11], [7, 27], [8, 33], [9, 40], [10, 32], [11, 35],
[12, 30], [13, 40], [14, 42], [15, 47], [16, 44], [17, 48],
[18, 52], [19, 54], [20, 42], [21, 55], [22, 56], [23, 57],
[24, 60], [25, 50], [26, 52], [27, 51], [28, 49], [29, 53],
[30, 55], [31, 60], [32, 61], [33, 59], [34, 62], [35, 65],
[36, 62], [37, 58], [38, 55], [39, 61], [40, 64], [41, 65],
[42, 63], [43, 66], [44, 67], [45, 69], [46, 69], [47, 70],
[48, 72], [49, 40], [50, 40], [51, 42], [52, 44], [53, 39],
[54, 33], [55, 38], [56, 45], [57, 48], [58, 49], [59, 54],
[60, 64], [61, 60], [62, 65], [63, 67], [64, 68], [65, 69],
[66, 70], [67, 72], [68, 75], [69, 80]
]);
var options = {
// Цвет графика зелёный
colors: ['green'],
legend: 'none',
// Толщина линии
lineSize: 5
};
var container = document.getElementById('chart_div');
var chart = new google.visualization.LineChart(container);
google.visualization.events.addListener(chart, 'ready', function () {
var layout = chart.getChartLayoutInterface();
for (var i = 0; i < data.getNumberOfRows(); i++) {
// рисуем в каждой n-ой точке
if ((i % 3) === 0) {
var xPos = layout.getXLocation(data.getValue(i, 0));
var yPos = layout.getYLocation(data.getValue(i, 1));
// Если значение >50 то рисуем картинку
if (data.getValue(i, 1)>50)
{
var imgGr = container.appendChild(document.createElement('img'));
imgGr.src = 'https://www.iconsdb.com/icons/download/orange/beer-24.png';
if (data.getValue(i, 1)>69)
{
imgGr.src = 'https://a.deviantart.net/avatars/a/r/arto-uk.gif';
}
imgGr.className = 'imgGr';
// Смещение картинки от графика
imgGr.style.top = (yPos - 5) + 'px';
imgGr.style.left = (xPos) + 'px';
imgGr.style.height = '32px';
imgGr.style.width = '32px';
}
}
}
});
chart.draw(data, options);
}
</script>
</head>
<table>
<tr>
<td><div class="legend">Important Chart </div></td>
</tr>
<tr>
<td>
<div id="chart_div" style="width: 1600px; height: 650px"></div>
</td>
</tr>
</table>
</html>