Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Коллеги, всем добрый день.

В очередной раз возникли проблемы в БД с производительностью. Не конкретный проблемный запрос. Просто есть у нас один постоянно работающий процесс - он как индикатор появляющихся проблем. Объем данных, который обрабатывается за час, стал уменьшаться. В день данных приходит больше, чем обрабатывается.
Вместе с этим увидел, что в базе за последние 2 месяца вырос тип ожидания WRITELOG.
Оптимизировал все "дыры" в процессе-индикаторе, покопал запросы, оказывающие наибольшую нагрузку на io-систему, точечно построил индексы по ключевым процессам. В общем, я не наблюдаю каких-то проблем в базе(какой-то запрос\запросы тормозит\стал хуже работать), но наблюдаю факт(просто все стало немного хуже работать), что нагрузка на диск повышенная. Это подтверждает диагностика в системном мониторе Windows: средняя длина очереди диска высокая.

Вопросы:
1) может ли быть, что дело не в MS SQL, а повысилась нагрузка на хранилище, которое общее для многих виртуалок?
2) Как можно это гарантированно диагностировать\подтвердить для аргументации начальству\админу?

p.s. планируем апгрейд MS SQL на новую версию с переходом на новый win-сервер, который будет на другом хранилище. Если, скажем так, проблема в хранилище, может как-то форсирую более скорый переход.
---
Проходя мимо разложенных граблей, ты теряешь драгоценный опыт. (с)
25 ноя 19, 11:11    [22024416]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Megabyte

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

у нас такое было.
вряд ли ваш случай. но все же:
у них тут свой "запускатель" джобов.
в нем есть обязательный таймаут.
и подчас он "срубает" то, что просто не влезло в их таймаут,
т.е. плохо рассчитан таймаут(его выставили в 2016 году),
объемы возросли, вместо 20 минут что-то теперь выполняется 21 и что они делают?
срубают джоб.
а все в транзакции.
и идет роллбэк.
ну вот и выросло ожидание WRITELOG.
они потом повторяют неуспешные джобы,
снова что-то выполняется на 99%, его срубают по таймауту,
роллбэк снова гадит в лог, и так по кругу.
поправили таймауты, WRITELOG ушел из ожиданий.
модель простая.
25 ноя 19, 11:33    [22024444]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
кстати, у вас какая модель?

на прежнем месте работы было такое:
модель тоже простая, ночная загрузка вдруг стала в 2 раза дольше.
все потому, что она стала пересекаться с полным бэкапом.
данных больше, бэкап идет дольше,
пока идет бэкап все логируется полностью даже в простой модели.
ну и полез все тот же WRITELOG,
т.к. все select into стали полностью логироваться
25 ноя 19, 11:37    [22024454]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Все джобы я контролирую. В каждой итерации запускается обработка 1000 записей. В нормальное время это гарантировано отрабатывало за 2 минуты. Сейчас за 8. Ничего не срубается автоматом.
По всем большим таблицам есть большинство необходимых индексов. По многим большим таблицам старые данные перенесены в лог, за исключением одной много где используемой. Там была настроена архивация(переброска данных в архивную БД) конкретного кол-ва записей. А объем новых данных за сутки вырос. Работа с таблицей не самая оптимальная, к сожалению, но я не все запросы могу исправить на данный момент. А юзается она очень сильно, через многие наши процессы проходит, в том числе и через проблемный. В итоге при норме 10млн там сейчас 28млн. Это моя последняя надежда, что проблема в БД. :) Пока еще не решил ее, руки не дошли.

Модель полная, т.к. у нас зеркалирование. Оно работает только для полной модели.

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

Сообщение было отредактировано: 25 ноя 19, 12:23
25 ноя 19, 12:20    [22024504]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Megabyte
В каждой итерации запускается обработка 1000 записей. В нормальное время это гарантировано отрабатывало за 2 минуты. Сейчас за 8.

посмотрите конкретные ожидания (не статистику всех ожиданий)
в течение этих 8 минут
25 ноя 19, 13:04    [22024558]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Yasha123
Megabyte
В каждой итерации запускается обработка 1000 записей. В нормальное время это гарантировано отрабатывало за 2 минуты. Сейчас за 8.

посмотрите конкретные ожидания (не статистику всех ожиданий)
в течение этих 8 минут

Спасибо, попробую.
25 ноя 19, 13:38    [22024617]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Idol_111
Member

Откуда:
Сообщений: 604
Megabyte,

что-то у в вашем рассказе не сходится. То Вы наблюдаете как факт, что все стало НЕМНОГО хуже работать, то вместо 2мин аж 8 - в 4 раза хуже. Это не немного, это где-то что-то серьезно слетело.

Мне кажется Вам стоит покопаться не в тяжелых запросах, а в легких, но частых.

У Вас хоть какая-то мониторинговая система есть (на ДБ, на Виртуалке, на дисках)?

По хорошему все это должно быть, и тогда с доказательствами начальству проблем не будет.
25 ноя 19, 23:30    [22025102]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
PsyMisha
Member

Откуда: другая столица
Сообщений: 769
Yasha123

ночная загрузка вдруг стала в 2 раза дольше.
все потому, что она стала пересекаться с полным бэкапом.


Это очень, очень плохой сценарий, конечно. Особенно когда заявлены RPO/RTO, и в рамках этих заявленных значений ты отрабатываешь DR-план. И такой бекап ой как долго разворачиваться будет - транзакции будут все накатываться и накатываться, а если во время процедуры бекапа еще и факты перегружались в DWH - все, - пиши пропало на n-часов!
Вот поэтому так важно время от времени тестить DR-процедуры и всякие симуляции rollback'а устраивать :)
26 ноя 19, 09:20    [22025214]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Idol_111
Megabyte,

1) что-то у в вашем рассказе не сходится. То Вы наблюдаете как факт, что все стало НЕМНОГО хуже работать, то вместо 2мин аж 8 - в 4 раза хуже. Это не немного, это где-то что-то серьезно слетело.

2) Мне кажется Вам стоит покопаться не в тяжелых запросах, а в легких, но частых.

3) У Вас хоть какая-то мониторинговая система есть (на ДБ, на Виртуалке, на дисках)?

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

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

2) Да все смотрю.

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

По виртуалке, дискам и прочему железу я, честно, не спец. Да и за это у нас сисадмин отвечает. Мне задач по БД с головой хватает. Я лишь запускал системный монитор машины, где стоит сервер БД, там есть стандартный счетчик производительности windows. По нему лишь увидел подтверждение повышенной нагрузки на дисковую подсистему.
Но это лишь фиксатор проблемы. Если порекомендуете систему мониторинга конкретную, я пну начальство и админа рассмотреть.
26 ноя 19, 11:03    [22025316]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Massa52
Member

Откуда:
Сообщений: 379
[quot Megabyte#22025316]
Idol_111
Megabyte,
Я лишь запускал системный монитор машины, где стоит сервер БД

А перегружать сервер пробовали?
26 ноя 19, 11:15    [22025342]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Megabyte,

найдите документ по SQL best practices виртуализации и ЗАСТАВЬТЕ админа настроить всё по этому документу. Иначе ваш сервер будет грязь месить.
26 ноя 19, 11:22    [22025351]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
[quot Massa52#22025342]
Megabyte
пропущено...

А перегружать сервер пробовали?

Недавно перегружали, неделю назад где-то, правда не весь сервер, а службу MS SQL. Но как бы это к чему вообще, поясните?)

Владислав Колосов
Megabyte,

найдите документ по SQL best practices виртуализации и ЗАСТАВЬТЕ админа настроить всё по этому документу. Иначе ваш сервер будет грязь месить.

Спасибо, поищу, сам поизучаю, как минимум, для общего развития.

Сообщение было отредактировано: 26 ноя 19, 11:27
26 ноя 19, 11:26    [22025360]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7780
Megabyte,

надо иметь в виду, что задача админа ВМ - сшить из одной шкуры семь шапок, и победить его крайне сложно.
26 ноя 19, 11:32    [22025373]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Massa52
Member

Откуда:
Сообщений: 379
[quot Megabyte#22025360]
Massa52


Недавно перегружали, неделю назад где-то, правда не весь сервер, а службу MS SQL. Но как бы это к чему вообще, поясните?)

Чтобы убедиться, что именно в SQL проблема.
26 ноя 19, 11:38    [22025388]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Idol_111
Member

Откуда:
Сообщений: 604
Megabyte,

Мониторинговая система в первую очередь это история, а не только текущее состояние.
Как я понял, ее нет.
Простейший и вполне себе эффективный (и бесплатный) способ - Data Collection. Ну и Query Store полезная вещь.

У любой виртуальной системы есть мониторинг основных параметров (ну конечно если его только намеренно не вырубили).

Если ваши сервера крутятся на виртуале, Вам необходимо знать основы этой системы.
Кстати, обратите внимание как настороенна память для SQL сервера на виртуальном уровне. По хорошему SQL сервер не должен делиться своей памятью, по умолчанию это не так. Отсюда и проблемы с чтением дисков.
28 ноя 19, 01:21    [22027141]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
L_argo
Member

Откуда:
Сообщений: 1217
Владислав Колосов
Megabyte,

надо иметь в виду, что задача админа ВМ - сшить из одной шкуры семь шапок, и победить его крайне сложно.
+1.
Например "параллельные" ВМ могут в это время дико поедать винт.
У нас так почта ложилась. :)
28 ноя 19, 10:04    [22027304]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Idol_111
Megabyte,

Мониторинговая система в первую очередь это история, а не только текущее состояние.

Что сделал сам по базе давно:
1) Есть история изменения типов ожиданий(в %). Собственно по ней я и определил, что сильно вырос WRITELOG. Последние 3 года впереди были другие типы ожиданий.

2) Есть история показателей среднего времени чтения и записи по файлам БД.

Но это я про БД.

Idol_111

У любой виртуальной системы есть мониторинг основных параметров (ну конечно если его только намеренно не вырубили).

Если ваши сервера крутятся на виртуале, Вам необходимо знать основы этой системы.
Кстати, обратите внимание как настороенна память для SQL сервера на виртуальном уровне. По хорошему SQL сервер не должен делиться своей памятью, по умолчанию это не так. Отсюда и проблемы с чтением дисков.

Про какую-либо историю по данным виртуального хранилища не в курсе, но скорее всего, да, ее нет. Но это не мое поле деятельности. :(
Понимаю, что надо самому разбираться, чтоб аргументировать изменения по виртуальным системам. В процессе изучения.

Насчет памяти. Попробую выяснить этот момент.Возможно как-то этот момент зафиксировать на сервере через скрипт?
Мне казалось, вот выделили серверу MS SQL столько-то памяти, столько он и юзает(max server memory настроено). Поясните, проблема может быть на уровне ms sql vs виртуалка, на которой он стоит ms sql? На виртуалке с MS SQL больше ничего не стоит, кроме ОС.
28 ноя 19, 13:09    [22027556]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
столько времени уже прошло,
почему вы никак не посмотрите на sys.dm_os_waiting_tasks
во время выполнения того, что теперь стало "8 минут"?
за 8 минут можно и сто раз посмотреть, чего именно стала ждать "обработка 1000 записей"
28 ноя 19, 13:18    [22027570]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Yasha123
столько времени уже прошло,
почему вы никак не посмотрите на sys.dm_os_waiting_tasks
во время выполнения того, что теперь стало "8 минут"?
за 8 минут можно и сто раз посмотреть, чего именно стала ждать "обработка 1000 записей"

Понаблюдал. Типы ожиданий: чаще WRITELOG, DBMIRROR_DBM_EVENT, изредка PAGEIOLATCH_SH и IO_COMPLETION.
Впрочем типы ожидания и потребляемые ресурсы я итак видел через нашу процу - монитор текущих процессов.
Поле blocking_session_id вообще никогда не заполнено. resource_description заполнено очень редко. У нас там 2 джоба параллельно работают: один делает чуть больше вычислений(доп. шаг джоба, раньше было 2 доп. шага) и за итерацию обрабатывает 1000 записей, второй - делает чуть меньше вычислений и за итерацию 50 записей. В редких случаях они пересекаются на вставку\обновление.
28 ноя 19, 17:02    [22027980]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Megabyte,
зеркало синхронное?
28 ноя 19, 17:16    [22027991]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4903
Yasha123
Megabyte,
зеркало синхронное?

Да. Тоже подумывал, что может проблема в зеркалировании. Потому как на другом не главном сервере сталкивались с этим. Зеркальный сервер был построен на более медленном хранилище, как выяснилось путем тестов. Отключили зеркало, проблемы на основном исчезли.

p.s. Вечером\ночью проблемный процесс отрабатывает быстрее, т.к. общая нагрузка на БД существенно ниже. При этом стал сильно проседать при бэкапировании, чего раньше не наблюдалось.
28 ноя 19, 21:15    [22028189]     Ответить | Цитировать Сообщить модератору
 Re: Диагностика нехватки аппаратных ресурсов для БД на виртуалке  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
сделайте асинхронное, хотя бы DBMIRROR_DBM_EVENT уйдет
29 ноя 19, 11:27    [22028576]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить