Resource Governor: управление ресурсами в SQL Server 2008. Часть 2

Resource Governor: управление ресурсами в SQL Server 2008. Часть 2

denish
08.11.2007 21:27

Продолжение. Начало здесь.

реконФигурное катание

 

Разработчики MS SQL Server 2008 приложили массу усилий, чтобы общение с новой технологией проходило в лёгкой и непринуждённой атмосфере. На зависть всем Эллочкам Людоедкам лексикон Katmai обогатился словарным запасом из семи интуитивно понятных DDL-инструкций, так что «трудности перевода» обойдут вас стороной и прибегать к языку жестов, настраивая RG, не придётся.

 

Принцип завинчивания гаек в Resource Governor чем-то напоминает работу с процедурой sp_configure. Заданные через sp_configure значения не применяются до тех пор, пока не будет выполнена команда RECONFIGURE. Аналогичный подход используется и для установки параметров RG (рис.5).

 

rg_configure_process.gif

 

Сначала с помощью DDL-инструкций мы дотошно объясняем серверу, какую конфигурацию хотим получить: «Землю — крестьянам, фабрики — рабочим, броневик — в автосервис и т.д.» (1). Сервер как Надежда Крупская стенографирует наши пожелания в блокнотик с метаданными. Руководствуясь поговоркой «семь раз отмерь, один раз отрежь», просим показать, что в итоге получилось (2). Если всё в порядке, и мы ничего не напутали, распоряжаемся эти указания претворить в жизнь (3). Результат можно проконтролировать, используя соответствующие DMV (4).

 

 

Эксперимент 1. Настройка RG и распределение CPU между пулами.

 

Для выполнения нам потребуется три учётные записи user1, user2 и user3, входящие только в роль public. Также предполагается, что на первом этапе эксперимента Resource Governor выключен, и вы не меняли его конфигурацию.

 

1.      Подключитесь к серверу под административной учётной записью и установите контекст на БД master. Все изменения метаданных осуществляйте в рамках транзакции. Она позволит обеспечить целостность настроек и сэкономит вам не один миллион нервных клеток.  

 

usemaster

go

 

set nocount on

 

begintran

  

2.      Опишите пулы и группы нагрузки.

 

createresourcepool Pool1

with (

      min_cpu_percent = 30

      , max_cpu_percent = 100

)

 

createresourcepool Pool2

with (

      min_cpu_percent = 50

      , max_cpu_percent = 70

     

)

 

createresourcepool Pool3

with (

      min_cpu_percent = 5

      , max_cpu_percent = 100

     

)

 

createworkloadgroup Group1

using Pool1

 

createworkloadgroup Group2

using Pool2

 

createworkloadgroup Group3

using Pool3

Если задать для Pool3 min_cpu_percent равным не 5, а 55, то сумма минимумов превысит 100%. Это чревато ошибкой на этапе выполнения: «The specified 'min_cpu_percent' value, 55, causes the sum of minimums on all resource pools to exceed 100 percent». Отсутствие открытой транзакции в данном случае выльется в создание Pool1 и Pool2, в то время как Pool3 и группы нагрузки созданы не будут. Так что не усложняйте себе жизнь, всегда обрамляйте настройки RG в транзакцию.

 

 

3.      Реализуйте функцию-классификатор.

 

-- Завершаем предыдущий batch, так как объявление функции

-- должно быть первой инструкцией в пакете.

go

 

-- Классификатор может быть создан только в БД master.

createfunction dbo.rg_class_simple() returns sysname

with schemabinding -- обязательное требование к классификатору

asbegin

      declare

            @grp_name as sysname

     

      if (suser_name() = 'user1')

            -- Нагрузка всех сессий user1 будет направлена в Group1

            set @grp_name = 'Group1'

      elseif (suser_name() = 'user2')

            -- Нагрузка всех сессий user2 будет направлена в Group2

            set @grp_name = 'Group2'

      elseif (suser_name() = 'user3')

            -- Нагрузка всех сессий user3 будет направлена в Group3

            set @grp_name = 'Group3'

           

      -- Остальные сессии будут работать в контексте Default

     

      return @grp_name

end

go

  

4.      Зарегистрируйте вашу функцию.

 

alterresourcegovernor

with (classifier_function = dbo.rg_class_simple)

go

  

5.      Проверьте метаданные RG.

 

select

      object_name(classifier_function_id) as classifier_function

      , is_enabled -- если RG выключен, здесь должен быть 0

from sys.resource_governor_configuration

classifier_function  is_enabled

-------------------- ----------

rg_class_simple      0

 

 

6.      Проверьте конфигурацию пулов (в этом эксперименте нас интересует только распределение CPU).

 

select

      pool_id

      , name

      , min_cpu_percent

      , max_cpu_percent

from sys.resource_governor_resource_pools

pool_id     name         min_cpu_percent max_cpu_percent

----------- ------------ --------------- ---------------

1           internal     0               100

2           default      0               100

260         Pool1        30              100

261         Pool2        50              70

262         Pool3        5               100

 

 

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

 

select

      group_id

      , a.name

      , a.pool_id

      , b.name as pool_name

from sys.resource_governor_workload_groups a

innerjoin sys.resource_governor_resource_pools b on a.pool_id = b.pool_id

group_id    name         pool_id     pool_name

----------- ------------ ----------- ------------

1           internal     1           internal

2           default      2           default

256         Group1       260         Pool1

257         Group2       261         Pool2

258         Group3       262         Pool3

 

 

8.      Подтвердите транзакцию.

 

commit

 

 

9.      Сейчас конфигурация хранится только в каталоге метаданных и не оказывает никакого влияния на работу сервера. Resource Governor знает, что настройки изменились и ждёт команды их применить.

 

select

      is_reconfiguration_pending

from sys.dm_resource_governor_configuration

is_reconfiguration_pending

--------------------------

1

 

 

10.    Дайте указание свыше следовать заветам Ильича.

 

-- Дорогой сервер, прими, пожалуйста, во внимание наши пожелания!

alterresourcegovernorreconfigure

В каком случае сервер может отказать? Ну, например, если инструкцией DROP WORKLOAD GROUP вы удалили группу нагрузки из метаданных, и к этому времени в контексте удалённой группы работала одна или несколько сессий. Сервер откажет вам не только в удалении группы, но и во всех изменениях, которые были внесены в конфигурацию с момента предыдущего ALTER RG RECONFIGURE.

 

Хочу уберечь вас от искушения объединить инструкцию, выполненную на шаге 4, с ALTER RESOURCE GOVERNOR RECONFIGURE. Это две взаимоисключающие команды! К примеру, мы можем написать так:

 

alterresourcegovernor

with (classifier_function = dbo.rg_class_simple)reconfigure

 

Синтаксический анализатор распознает в этом две независимые инструкции

1)     alter resource governor with(…)

2)     reconfigure (которая используется после вызова sp_configure).

 

 

11.    Убедитесь, что настройки вступили в силу.

 

select

      object_name(classifier_function_id) as classifier_function

      , is_reconfiguration_pending –- обнуляется после успешного reconfigure

from sys.dm_resource_governor_configuration

 

-- Статистика и текущая конфигурация пулов

select *

from sys.dm_resource_governor_resource_pools

 

-- Статистика и текущая конфигурация групп нагрузки

select *

from sys.dm_resource_governor_workload_groups

 

 

 

12.  Для проверки классификации создайте вспомогательную UDF, которая будет возвращать имя группы для текущей сессии.

 

createfunction dbo.session_group_context()

returns sysname withexecuteas owner

asbegin

 

declare @group_name sysname

select

      @group_name = g.name

from sys.dm_exec_sessions s

innerjoin sys.resource_governor_workload_groups g on s.group_id = g.group_id

where

      session_id = @@spid

 

      return @group_name

end

go

 

-- Даём права на запуск функции роли public

grantexecon object::dbo.session_group_context topublic

 

 

 

13.  Откройте три сессии под учётными записями user1, user2 и user3. В каждой из них выполните:

 

select dbo.session_group_context()

У вас должно получиться Group1, Group2 и Group3 соответственно.

 

 

14.  Запустите Perfmon и выберите счётчик CPU usage % объекта SQLServer:Resource Pool Stats. Он покажет, как распределяется процессорное время между пулами. Отметьте в списке только экземпляры Pool1, Pool2 и Pool3.

 

 

15.   В окне сессии user1 запустите скрипт, интенсивно использующий ресурсы CPU.

 

set nocount on

declare

      -- MS SQL Server 2008 допускает присвоение значений

      -- в объявлении переменной

      @i          int = 1

      , @name     varchar(128)

     

while (@i != 500000) begin

 

      select

            @name = name

      from sys.sysobjects

      orderby newid()

 

      set @i += 1 -- Теперь так тоже можно ;)

end

 

 

16.      Выждав несколько секунд, запустите скрипт с нагрузкой на CPU сначала в сессии user2, а затем user3. Результат, который должен получиться на двуядерной машине с привязкой SQL-сервера к одному логическому процессору (affinity mask = 1), изображён на рис.6 (я нарочно отключил один узел, чтобы не вдаваться сейчас в нюансы параллелизма).

 

pools_load_cpu.gif

Для ответа на излюбленный вопрос психотерапевта «Что вы видите на этой картинке?» нам потребуется воскресить в памяти термин Effective MAX% (обсуждалось в первой части статьи) и, пока свежо преданье старины глубокой, сочинить вычисляющую его функцию.

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

 

-- Создаём табличный тип, чтобы использовать его в параметрах функции

createtype PoolList astable (pool_id int)

go

 

createfunction dbo.pools_cpu_effective_max(

      @pools PoolList readonly

) returns table

      return (

            select

                  pool_id

                  , name

                  , min_cpu_percent

                  , max_cpu_percent

                  , isnull(

                        case

                        when name = 'internal'then 100

                        when max_cpu_percent < (100 - sum_of_cpu_min) then max_cpu_percent

                        else (100 - sum_of_cpu_mein)

                        end

                  , 100) as effective_cpu_max

            from (

                  select

                        pls.pool_id

                        , name

                        , min_cpu_percent

                        , max_cpu_percent

                        , (

                             select

                                   sum(min_cpu_percent)

                             from sys.resource_governor_resource_pools x

                             innerjoin @pools tp on x.pool_id = tp.pool_id

                             where x.pool_id != pls.pool_id

                        ) as sum_of_cpu_min

                  from sys.resource_governor_resource_pools pls

                  innerjoin @pools tp on pls.pool_id = tp.pool_id

            )pls

      )    

go

  

Воспроизведём события в хронологическом порядке.

 

В точке (A) начал выполнение нагрузочный скрипт сессии user1, которая через группу Group1 потребляет ресурсы Pool1 (чёрная ветка на графике). Так как потребители других пулов к этому моменту в CPU не нуждаются, Resource Governor отдаёт user1 всё, чем располагает сам. А располагает он только временем одного ядра (второе мы у него отобрали, задав affinity). Таким образом, SQL-сервер может использовать только 50% от ресурса машины, что и составит 100% ресурса, доступного пулам.

 

Как мы можем убедиться, расчёт Pool1 Effective MAX% не входит в противоречие с поведением сервера (пул может занять от 30% до 100%).

 

declare

      @pools PoolList

 

insert @pools values (260) –- Идентификатор Pool1

select * from dbo.pools_cpu_effective_max(@pools)

pool_id     name         min_cpu_percent max_cpu_percent effective_cpu_max

----------- ------------ --------------- --------------- -----------------

260         Pool1        30              100             100

 

 

В точке (B) весь в зелёном появляется потребитель Pool2, олицетворяемый сессией user2. Resource Governor корректирует распределение времени CPU в рамках изменившихся фактических максимумов, выделив каждому пулу по 50% (или 25%, если принимать во внимание неиспользуемое SQL-сервером ядро процессора).

  

declare

      @pools PoolList

 

insert @pools values (260), (261) –- Идентификаторы Pool1 и Pool2 соотв.

select * from dbo.pools_cpu_effective_max(@pools)

pool_id     name         min_cpu_percent max_cpu_percent effective_cpu_max

----------- ------------ --------------- --------------- -----------------

260         Pool1        30              100             50

261         Pool2        50              70              70

 

 

Ещё раз перераспределение происходит в точке (С), когда сессия user3 начинает выполнять свою работу (синяя ветка). Её доля процессорного времени задана в параметрах Pool3.

Новые значения Effective MAX% вступают в силу, что тут же сказывается на Pool1 (Pool2 ещё на предыдущем этапе «упёрся» в свой минимум, поэтому экспроприировать время можно только у Pool1).

 

declare

      @pools PoolList

 

insert @pools select pool_id from sys.resource_governor_resource_pools

select * from dbo.pools_cpu_effective_max(@pools)

pool_id     name         min_cpu_percent max_cpu_percent effective_cpu_max

----------- ------------ --------------- --------------- -----------------

1           internal     0               100             100

2           default      0               100             15

260         Pool1        30              100             45

261         Pool2        50              70              65

262         Pool3        5               100             20

 

 

17.      Не останавливая нагрузочные скрипты, измените в административной сессии настройки Pool3. Напомню, что ранее MAX CPU% у этого пула был равен 100, а используемое значение в точке (С) составляло 20%.

 

Увеличение MAX% до уровня выше используемого на текущий момент никакого влияния на распределение времени CPU между пулами не оказывает. Тогда как уменьшение до величины ниже используемой приведёт к очередному переделу (опять же в рамках от MIN% до Effective MAX%).

 

alterresourcepool Pool3

with (max_cpu_percent = 10)

go

 

alterresourcegovernorreconfigure

go

 

Pool3 высвободил 10% ресурса, которые достались самому обделённому пулу, а именно Pool1 (точка (D) на рис.7). Вообще говоря, сервер всегда пытается «помогать» наиболее ограниченным пулам, отдавая им предпочтение при раздаче освободившихся ресурсов. Так может продолжаться до тех пор, пока они не достигнут своего Effective Max% (Pool3 уже достиг своего максимума в 10% и Resource Governor не позволит ему сейчас взять больше).

 

alter_pool_cpu.gif

18.      Уменьшите MIN% для Pool2 до 40%. Излишек будет поделен поровну между Pool1 и Pool2 (точка (E) на рис.7). Любое изменение MIN% приводит к перерасчёту Effective MAX% и Shared%.

 

alterresourcepool Pool2

with (min_cpu_percent = 40)

go

 

alterresourcegovernorreconfigure

go

 

 

 

19.   Не останавливая работу скриптов, выключите Resource Governor. Обратите внимание, что сервер применил для всех пулов настройки по умолчанию (MIN = 0, MAX = 100). На графике вы увидите, как все пулы заняли по ~33% (100% / [кол-во пулов]). При этом метаданные в представлениях sys.resource_governor_* остались неизменны.

 

 

alterresourcegovernordisable

go

 

select

      name

      , min_cpu_percent

      , max_cpu_percent

from sys.dm_resource_governor_resource_pools

name         min_cpu_percent max_cpu_percent

------------ --------------- ---------------

internal     0               100

default      0               100

Pool1        0               100

Pool2        0               100

Pool3        0               100

 

 

Все новые подключения, которые вы будете выполнять от имени любой учётной записи (включая user1, user2 и user3), попадут уже в группу Default.

 

 

 

Продолжение следует…

 

denish
08.11.2007 21:27
Комментариев:2 RSS Просмотров:153
Теги: SQL
mihailik
09.11.2007 15:46
То ли я пропустил, то ли не понял.

А что если попытка "alter resource governor reconfigure" провалилась, и мы решили откатиться? Есть какой-то способ?
Ссылка
Nikolay Denishchenko
09.11.2007 16:26
2mihailik
Если alter resource governor reconfigure завершилось ошибкой, is_pending будет оставаться равным 1. Т.е. изменения, которые вы внесли, просто не применились на сервере. На метаданных это никак не сказывается. Например, вы изменили используемый группой пул и на момент alter rg reconfigure в эту группу входила одна или несколько открытых сессий.

alter workload group Group1
using Pool2 -- был Pool1, а мы меняем на Pool2
go

alter resource governor reconfigure
go

-- Допустим, тут вылетает ошибка "Не могу применить, так как существуют сессии этой группы"

У нас есть два варианта:
1) Вернуть в метаданных группе старый пул alter workload group Group1 using Pool1, тогда реконфигурация не будет приводить к ошибке. Далее дождаться пока сессии группы закончат работу и попытаться изменить пул для группы ещё раз.
2) Командой kill "прибить" все сессии группы. После чего выполнить реконфигурацию.

Сама команда alter resource governor reconfigure не может находится в транзакции, так как параметры загружаются в память сервера, а не складируются в таблицу. После перезапуска сервера, настройки снова подгружаются из каталога метаданных.

Чтобы сбрасывать настройки до первоначального состояния Rg, я для себя написал процедурку. Постараюсь в следующей части её опубликовать.

Спасибо за вопрос!
Ссылка