Распределение ресурсов между приложениями SQL Server при помощи Resource Governor
Регулятор ресурсов (Resource Governor) был впервые реализован в SQL Server 2008 и является функциональностью корпоративной редакции. Управление ресурсами –очень полезная на практике вещь, т.к. позволяет назначить приоритеты выполняющимся приложениям в зависимости от их текущей актуальности.
Наступили новые трудовые будни (месяц, квартал и т.д.), и в базу бурным потоком хлынули проводки. В этот момент бухгалтерия обнаруживает, что отчет за предыдущий период, считавшийся накануне, посчитался как-то криво. То есть посчитался он правильно, но все равно требуется откорректировать некоторые данные и запустить по новой. Отчет напрягает много таблиц, строит между ними джойны, считает агрегаты, данные ему нужны практически все, поэтому по большинству таблиц происходит полный скан, т.е. он оттягивает на себя по максимуму SQL Serverных ресурсов. Приложение OLTР начинает, понятно, прозябать и отчаянно тормозит. Работа встает, на предприятии поднимается гвалт, главного айтишника вызывают на ковер, где тот безуспешно пытается объяснить начальству, что уже неоднократно поднимал идею построения хранилища или, на худой конец, сделать зеркало, по нему снэпшот и разгрузить туда аналитику. Начальство слушает и подводит короткий итог: сейчас средств нет, иди и делай, что хочешь, но чтоб работало. В этой ситуации DBA, было бы оч.полезно уметь выделить коротким обновляющим транзакциями львиную долю процессорного времени и памяти, т.к. оперативная работа в данный момент приоритетней. Отчет можно загнать в фон, потому что посчитается он за час или за два, сейчас большой разницы не имеет.
Прогрессивное начальство отличается тем, что все понимает (хотя средств все равно нет). Оно не только смотрит отчеты, но еще и пытается их самостоятельно ваять в каком-нибудь интерактивном дизайнере. Криво составленный запрос выводит из себя SQL Server, отжирает ресурсы у других приложений, работа встает, поднимается гвалт и т.д., пока не отловить кривой запрос через выделенное административное соединение и не пристрелить его. В этой ситуации, чтобы не прибегать к крайним мерам, DBA было бы оч.полезно поместить запрос начальника в своеобразную песочницу (в смысле системных ресурсов) и иметь механизм, который бы давал тому по рукам всякий раз, как он захочет из нее вылезти.
В рассмотренных, а также еще в массе типовых сценариев, на помощь приходит регулятор ресурсов SQL Server. Участники российской группы пользователей SQL Server одними из первых имели возможность познакомиться с регулятором ресурсов на примере предварительной версии продукта, а позднее - что называется, из первых уст благодаря семинарам в Санкт-Петербурге и Москве, на которых выступил лично Борис Барышников, one of the primary architects of the feature. Данный материал основывается на написанной им совместно с Аароном Бертраном статье «Using the Resource Governor». Можно также порекомендовать замечательную статью Николая Денищенко, опубликованную в блоге gotdotnet: «Resource Governor: управление ресурсами в SQL Server 2008», часть 1, часть 2, часть 3. В данном посте мы на коротком практическом примере попытаемся пронаблюдать основные принципы действия Resource Governor. Сейчас я попытаюсь состряпать какой-нибудь убийственный запрос, от которого SQL Server резко поплохеет.
use tempdb
declare @i int = 0, @name sysname
set nocount on
while (@i < 150000) begin
select @name = name from sys.sysobjects order by newid()
set @i += 1
end
Скрипт 1
Честно говоря, как-то плохо ему поплохело:
Рис.1
Это все оттого, что у него сейчас слишком много процессоров.
Рис.2
Чтобы планировщик SQL Server не перекидывал выполнение запроса между процессорами:
Рис.3
надо у него их отобрать. Давайте оставим SQL Server один процессор:
exec sp_configure @configname = 'show advanced', @configvalue = 1
reconfigure
exec sp_configure @configname = 'affinity mask', @configvalue = 1
reconfigure
Скрипт 2
Конфигурационная настройка affinity mask содержит битовую маску, в которой задается, какие процессоры используются SQL Server. Если после выполнения Скрипт 2 зайти в свойства сервера на Рис.2, можно увидеть, что галка Automatically set affinity mask for all processors снялась, и осталась только одна явная галка напротив CPU0.
И еще память урежем, чтоб служба медом не казалась. Я думаю, 512 метров для эксперимента ему хватит:
exec sp_configure @configname = 'min server memory', @configvalue = 512
exec sp_configure @configname = 'max server memory', @configvalue = 512
reconfigure
Скрипт 3
Рис.4
А-а, блин, сказали суровые сибирские лесорубы. Нажмите на кнопку Resource Monitor, чтобы убедиться, что на 100% страдает действительно sqlservr.exe:
Рис.5
То-то, брат. Теперь не забалуешь. Перед нами наглядная картина, как SQL Server пошел вразнос из-за ресурсоемкого запроса. Регулятор ресурсов позволяет защитить SQL Server от аппетитов не в меру охочих приложений, раздав всем сестрам по серьгам, но не более того. Основными понятиями регулятора ресурсов выступают функция-классификатор, группы нагрузки
if exists (select 1 from sys.resource_governor_workload_groups where name = 'Group1') drop workload group Group1
if exists (select 1 from sys.resource_governor_workload_groups where name = 'Group2') drop workload group Group2
if exists (select 1 from sys.resource_governor_workload_groups where name = 'Group3') drop workload group Group3
Скрипт 4
и пулы ресурсов. В SQL Server 2008 – 2008 R2 под ресурсами понимаются процессорное время и память, причем память, выделяемая сугубо аллокатором единичных страниц, то есть, скажем, память CLR при помощи регулятора ресурсов контролировать нельзя. В будущем обещали добавить дисковый I/O, но пока не знаю. В данном эксперименте мы для наглядности будем распределять только ресурсы процессора. Пулы ресурсов позволяют разделить каждый тип ресурса согласно заданным пропорциям. Их можно рассматривать как виртуальные экземпляры SQL Server внутри инстанса. MIN% задает гарантированный минимум ресурса, который при необходимости будет предоставлен потребителям пула, а MAX% — верхнюю границу в условиях конкуренции за ресурс. Понятно, что сумма минимумов всех пулов не должна превышать 100%, тогда как MAX% может быть задан в диапазоне от MIN% до 100%. MIN% пул гарантированно получит, что касается верхней границы – мечтать не вредно. Если конкурировать за ресурсы больше не с кем, то пожалуйста, в противном случае – извините. Зависит от оставшихся нераспределенных излишков после того, как остальные пулы получили свою гарантированную пайку. Исключение составляет пул под названием Internal. В SQL Server всегда существует два предопределенных пула: Internal и Default. Как легко догадаться из названия, Internal предназначается для внутренних системных процессов SQL Server. Его настройки менять нельзя, да и бестолку – все равно он получит столько ресурсов, сколько ему требуется, даже если при этом придется урезать минимальные пайки обычных граждан. В пул Default попадают пользовательские сессии, которые не были явно классифицированы по принадлежности к какой-либо группе нагрузки. Либо в него валится, по определению, все, если Resource Governor отключен. Включение/выключение регулятора ресурсов делается так:
alter resource governor enable / disable
alter resource governor reconfigure
Скрипт 5
По умолчанию, в свежеустановленном SQL Server регулятор ресурсов включен. Однако, даже если Resource Governor был отключен, это не значит, что внутри SQL Server происходит полный коммунизм, как в 2005-м. Сессия будет отнесена в пул Default, у которого тоже есть свои настройки по минимальным и максимальным значениям ресурсов. В отличие от Internal, в Default эти настройки можно менять, как в случае пользовательского пула, но удалить ни тот, ни другой системный пул нельзя. На данный момент в SQL Server пулов может быть не более 20 (с учетом системных). Я создам два пользовательских пула ресурсов:
if exists (select 1 from sys.resource_governor_resource_pools where name = 'Pool1') drop resource pool Pool1
create resource pool Pool1
if exists (select 1 from sys.resource_governor_resource_pools where name = 'Pool2') drop resource pool Pool2
create resource pool Pool2
Скрипт 6
Просмотр информации по пулам ресурсов осуществляется при помощи системного представления sys.resource_governor_resource_pools:
select * from sys.resource_governor_resource_pools
Рис.6
По умолчанию каждый пул ресурсов имеет нижние границы ресурсов 0 и верхние – 100%.
Группы нагрузки являются логическим объединением пользовательской активности (например, запросы одного приложения). Группа нагрузки потребляет ресурсы ассоциированного с ней пула. Ассоциация пула ресурсов с группой нагрузки происходит в момент создания группы:
create workload group Group1 using Pool1
create workload group Group2 using Pool1
create workload group Group3 using Pool2
Скрипт 7
В данном примере показано, что в один пул ресурсов могут относиться несколько групп нагрузки. Разрулирование ресурсов внутри пула между группами нагрузки происходит сообразно их приоритетам (importance). Параметрами группы являются: with (,)
· importance = LOW | MEDIUM | HIGH - приоритет группы внутри пула (по умолчанию, средний);
· max_dop = ограничение уровня параллелизма;
· group_max_requests = ограничение числа одновременно выполняемых запросов;
· request_max_cpu_time_sec = ограничение общего потребления CPU одним запросом; когда оно превышается, вспыхивает профайлерное событие CPU Threshold Exceeded;
· request_max_memory_grant_percent = ограничение памяти, выделяемой одному запросу;
· request_memory_grant_timeout_sec = ограничение времени ожидания выделения памяти.
Просмотр информации по группам нагрузки и их распределению по пулам осуществляется при помощи системного представления sys.resource_governor_workload_groups:
select * from sys.resource_governor_workload_groups
Рис.7
Если группа ресурсов была создана без явного указания пула ресурсов, она будет относиться к пулу по умолчанию.
Функция классификации задает, какая сессия к какой группе нагрузки относится. Следует обратить внимание, что в качестве объекта классификации выступает сессия, т.е. нельзя задавать ограничения по ресурсам на уровне отдельных запросов. Также важным моментом является, что принадлежность сессии к группе определяется только в момент входа. Сменить группу нагрузки после классификации сессии нельзя. В качестве атрибутов классификации могут использоваться результаты функций HOST_NAME() – название рабочей станции, с которой происходит подключение; APP_NAME() – название приложения, устанавливающего подключение; SUSER_NAME(), SUSER_SNAME() – текущий логин; IS_MEMBER() – проверка членства логина в группе Windows; IS_SRVROLEMEMBER() – проверка членства логина в серверной роли;LOGINPROPERTY() – возвращает полезные свойства логина, такие, как DefaultDatabase и DefaultLanguage; ORIGINAL_DB_NAME() – имя базы, как оно указано в строке соединения, а также CONNECTIONPROPERTY(property), где property = AUTH_SCHEME, NET_TRANSPORT, PROTOCOL_TYPE, CLIENT_NET_ADDRESS, LOCAL_NET_ADDRESS, LOCAL_TCP_PORT. Возвращаемые ею значения совпадают с DMV sys.dm_exec_connections. Функция классификации срабатывает после проверки подлинности логина и триггера на логон (если есть) – см., напр., посты «Аудит в SQL Server. Триггер на логон» и «Ограничение на клиентский IP».Еще на всякий случай напомню, что имя хоста и имя приложения берутся из строки соединения, в каковой приложение может прописать их совершенно волюнтаристским способом. Однако, если приложение находится под нашим контролем, и мы можем доверять значениям указанных свойств, их тоже можно рассматривать в качестве критерия классификации. Так, в данном примере я собираюсь классифицировать нагрузку в зависимости от передаваемого в соединении названия приложения. Предварительно переходим в БД master, т.к. ф-ция классификации может быть создана только в ней. Обратите также внимание, что для того, чтобы ее удалить, функция-классификатор должна быть разотмечена как классификатор (alter resource governor with (classifier_function = null)). Еще обязательным требованием к функции-классификатору является то, что она должна создаваться привязанной к схеме:
use master
go
if exists (select 1 from sys.objects where name = 'rg_classify' and type = 'FN' and schema_id = schema_id('dbo')) begin
alter resource governor with (classifier_function = null)
alter resource governor reconfigure
drop function dbo.rg_classify
end
go
create function dbo.rg_classify() returns sysname
with schemabinding
as begin
declare @grp_name as sysname
if (app_name() = 'aaa') set @grp_name = 'Group1' --приложение ааа будет направлено в группу 1
else if (app_name() = 'bbb') set @grp_name = 'Group2' --приложение bbb - в группу 2
else if (app_name() = 'ccc') set @grp_name = 'Group3' --приложение ccc - в группу 3
-- Остальные сессии будут работать в контексте Default
return @grp_name
end
go
Скрипт 8
Вместо App_Name() нагрузку можно было бы, например, разводить по пользователям: if (suser_name() = 'user1')... Тогда нагрузка всех сессий пользователя user1 была бы направлена в определенную группу и т.д. Смысл, я думаю, понятен. Подключение созданной функции в регулятор ресурсов в качестве классификатора выполняется также при помощи команды ALTER RESOURCE GOVERNOR:
alter resource governor with (classifier_function = dbo.rg_classify)
Скрипт 9
Эта команда сохраняет изменения в память. Конфигурацию регулятора ресурсов в памяти можно посмотреть при помощи динамических представлений
select * from sys.dm_resource_governor_workload_groups
select * from sys.dm_resource_governor_resource_pools
select * from sys.dm_resource_governor_configuration
Скрипт 10
А конфигурацию, сохраненную на диск, при помощи аналогичных DMV, но без префикса dm_:
select * from sys.resource_governor_workload_groups
select * from sys.resource_governor_resource_pools
select * from sys.resource_governor_configuration
Скрипт 11
Сейчас мы видим, что в памяти имеются конфигурационные настройки регулятора ресурсов, ожидающих сохранения на диск, о чем говорит поле is_reconfiguration_pending = 1 последнего запроса Скрипта 10. Сохранение настроек на диск выполняется командой
alter resource governor reconfigure
Скрипт 12
Убеждаемся, что изменения в части функции-классификатора вступили в силу:
select object_name(classifier_function_id) as classifier_function, is_reconfiguration_pending from sys.dm_resource_governor_configuration
Рис.8
Имя функции значится правильно, и эти настройки уже не ждут сохранения на диск.
Осталось засунуть нагрузочный Скрипт 1 в приложение, которое идентифицирует себя соответствующим образом, чтобы нагрузка была классифицирована правильно. Я сделал это в виде скрипта на PowerShell, который обозвал ResourceGovernor_Workload.ps1:
param ([string] $appName = "aaa")
[System.Data.SqlClient.SqlConnection] $cnn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:localhost;Database=tempdb;Trusted_Connection=true;Asynchronous Processing=false;Application Name=" + $appName)
$cnn.Open(); [System.Data.SqlClient.SqlCommand] $cmd = $cnn.CreateCommand()
#Убеждаемся, что соединение отождествило себя под заданным именем приложения:
$cmd.CommandText = "select app_name(), @@spid"
[System.Data.SqlClient.SqlDataReader] $dr = $cmd.ExecuteReader([System.Data.CommandBehavior]::SingleRow)
$dr.Read(); $dr.GetString(0); $dr.GetInt16(1); $dr.Close()
#Проверяем, в какую группу ресурсов попали:
$cmd.CommandText = "select g.name from sys.dm_exec_sessions s join sys.resource_governor_workload_groups g on s.group_id = g.group_id where session_id = @@spid"; $cmd.ExecuteScalar()
#Выполняем какую-нибудь могучую нагрузку, интенсивно использующую ресурсы CPU:
$cmd.CommandText = "declare @i int = 0, @name sysname
set nocount on
while (@i < 150000) begin
select @name = name from sys.sysobjects order by newid()
set @i += 1
end"
$cmd.CommandTimeout = 0 #Чтобы избежать ошибки Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
$cmd.ExecuteNonQuery() | Out-Null
$cnn.Close()
Рис.9
Запускаем Performance Monitor и выбираем счетчик CPU usage % объекта SQLServer:Resource Pool Stats. Он покажет, как распределяется процессорное время между пулами. Выбираем из списка созданные выше (Скрипт 6) экземпляры Pool1 и Pool2.
Рис.10
Запускаем
powershell c:\Temp\RGDemo\ResourceGovernor_Workload.ps1 "aaa"
Выжидаем некоторое время и запускаем
powershell c:\Temp\RGDemo\ResourceGovernor_Workload.ps1 "ссс"
Скрипт 13
В Perfmone наблюдается следующая картина:
Рис.11
Давайте ее проанализируем. Красная линия, в 21:08 с копейками, поднявшаяся до отметки 12.5, это ResourceGovernor_Workload.ps1 "aaa", нагрузка №1, попавшая в Pool1. Во-первых, мы видим, что регулятор ресурсов не жадный – пока другой нагрузки нет, он отдает этому пулу все, что у него есть. Как вы помните, из-за установленной affinity mask у SQL Server есть только 1 процессор из 8, т.е. 12.5% от общих ресурсов компьютера – это максимум, который он может распределить. Поэтому я заранее поставил Vertical Scale Maximum на закладке Graph в Performance Monitor Properties = 13.
Затем в 21:09:07 появилась вторая нагрузка - powershell c:\Temp\RGDemo\ResourceGovernor_Workload.ps1 "ссс", отнесенная согласно функции Скрипт 8 в Pool2. Оба пула абсолютно равноправны по ограничениям на ресурсы, поэтому первый пул поджимает свои аппетиты до 6.25% (или 50% доступной SQL Server процессорной мощи), а второй, соответственно, до 50% вырастает. Обе нагрузки рассчитаны на одинаковую длительность работы. Первая началась раньше, поэтому раньше и закончилась (21:09:36). Ощутив, что конкуренция исчезла и делиться больше не с кем, Pool2 радостно распахивается на 100% (12.5% от общего объема).
Усложним картину. Сначала, как водится, стартуем,
powershell c:\Temp\RGDemo\ResourceGovernor_Workload.ps1 "aaa"
Потом
powershell c:\Temp\RGDemo\ResourceGovernor_Workload.ps1 "bbb"
Эта нагрузка упадет в тот же самый пул. А еще спустя некоторое время стартуем третью:
powershell c:\Temp\RGDemo\ResourceGovernor_Workload.ps1 "ссс"
Скрипт 14
Вот, что происходит на этот раз:
Рис.12
В 21:32:25 стартовала первая нагрузка. Пул, как водится, в отсутствие конкурентов занял 100% доступных процессоров. В 21:32:35 к ней присоединилась вторая нагрузка. Она была классифицирована в тот же пул, что и первая. У пула конкурентов по-прежнему нет, поэтому доступные ему ресурсы не изменились. Сами нагрузки имеют одинаковую конфигурацию, поэтому внутри пула они уживаются 50:50, но этого здесь не видно. В 21:32:45 появляется третья нагрузка, которая приходится на Pool2. Настройки обоих пулов одинаковы, но внутри Pool1 живут две одинаковых нагрузки, а внутри Pool2 такая же, только одна. Поэтому в 21:32:45 Pool1 ужимается, но не вдвое, как на Рис.11, а на треть. Так Pool1 и Pool2 живут в пропорции 2:1, пока внутри Pool1 не заканчивается в 21:33:40 первая, самая ранняя нагрузка. Теперь пулы по обслуживаемой ими нагрузке становятся одинаковыми, и они тут же корректируют пропорцию до 1:1, как на Рис.11. Окончание эксперимента происходит аналогично.
Комментарий 1.
Вначале я хотел схитрить и допустил ошибку. Я хотел, чтобы Powershell Рис.9 отрабатывал асинхронно, т.е. отправил на выполнение Скрипт 1 и завершился, не дожидаясь, пока он закончится, поэтому в строке соединения поставил Asynchronous Processing=true, а в конце процедуры - $cmd.BeginExecuteNonQuery(). Из-за этого счетчик SQLServer:Workload Group Stats object\CPU usage % в PerfMone давал не константу в 100%, а зубчатую пилу. При запуске второй нагрузки, естественно, тоже происходила пила и эффектного уступа ресурсов не получалось. Еще в асинхронном режиме закрытие соединения терминирует выполняющуюся команду. Чтобы этого избежать, пришлось бы оставить соединение открытым и делать kill спидам.
Комментарий 2.
Отключение регулятора ресурсов и возврат SQL Serverных настроек в позицию по умолчанию:
alter resource governor with (classifier_function = null)
alter resource governor reconfigure
alter resource governor disable
alter resource governor reconfigure
exec sp_configure @configname = 'affinity mask', @configvalue = 0
exec sp_configure @configname = 'min server memory', @configvalue = 0
exec sp_configure @configname = 'max server memory', @configvalue = 0
reconfigure
Скрипт 15
Алексей Шуленин