SQL Server 2014 – Gestion des IO avec le resource governor

Par Christophe LAPORTE (MVP)

Web | Blog | Twitter

Dn467406.7B654F178A3842F7F616A829DC6DF588(fr-fr,TechNet.10).png

Septembre 2013



Enfin diront certains! Oui, on attendait tous la gestion des IO dans le gouverneur de ressources de SQL Server. Il était possible de spécifier le MaxDop, le nombre de requêtes simultanées, la quantité de mémoire, l’affinité CPU, le maximum de CPU utilisée. Enfin, SQL Server 2014 s’attaque au throttling d’IO. Cette nouveauté pourrait presque passer inaperçue tant Hekaton monopolise toutes les attentions.

Imaginez simplement que, à chaud, vous puissiez limiter la consommation IO de certaines de vos requêtes, en fonction de l’application, de l’utilisateur ou du nom d’hôte. Cette possibilité est enfin disponible ! Je n’ose même plus compter le nombre de fois, où présentant le resource governor, on m’a interpelé sur ce manque, alors que cette fonctionnalité constitue, pour moi, l’atout majeur de ce dispositif. Oui, j’apprécie le fait de limiter la CPU en cas de stress, oui j’ai du jouer avec la mémoire afin de forcer certaines requêtes a ne pas réserver 58 Gb de RAM avant de pouvoir s’exécuter. Mais j’attends surtout de cette QoS, car il faut bien l’appeler comme cela de pouvoir prioriser mes IO en fonction de l’applicatif, privilégier la prod au reporting (exit les mecs qui font de la BI avec des requêtes énormes – Romain Casteres François Jehl Jean-Pierre Riehl – non, je blague les mecs, j’adore vos requêtes qui écroulent els serveurs !).

Bref, vous l’aurez compris, découvrir dans la DMV dm_resource_governor_configuration une pléiade de nouveau champs a piqué ma curiosité.

dm_resource_governor_configuration – nouveaux champs :

max_outstanding_io_per_volume

min_iops_per_volume

max_iops_per_volume

read_io_queued_total

read_io_issued_total

read_io_completed_total

read_io_throttled_total

read_bytes_total

read_io_stall_total_ms

read_io_stall_queued_ms

write_io_queued_total

write_io_issued_total

write_io_completed_total

write_io_throttled_total

write_bytes_total

write_io_stall_total_ms

write_io_stall_queued_ms

io_issue_violations_total

io_issue_delay_total_ms

Place à un petit script de test pour mesurer l’efficacité du dispositif.

01

02

03

04

05

06

07

08

09

10

11

12

13

14

15

16

17

18

19

20

21

22

23

CREATE RESOURCE POOL Pool_LowIOPS

WITH (

    min_cpu_percent=0,

    max_cpu_percent=100,

    min_memory_percent=0,

    max_memory_percent=100,

    cap_cpu_percent=100,

    AFFINITY SCHEDULER = AUTO,

    min_iops_per_volume=0,

    max_iops_per_volume=0 );

GO

 

 

CREATE WORKLOAD GROUP Group_LowIOPS

USING Pool_LowIOPS

GO

 

CREATE WORKLOAD GROUP Group_LowIOPS

USING Pool_LowIOPS

GO

 

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO


Les deux options min_iops_per_volume et max_iops_per_volume laissent entrevoir la possibilité de caper les nombre d’IO par volume tout comme la possibilité d’en réserver un minimum. La valeur indique que le throttling ne sera pas actif.

Une fois créé le pool de ressources et le groupe de charge, les compteurs de performances nous montrent que la limitation d’IO peut être appliqué autant sur la lecture que sur l’écriture :

Dn467406.28ADAE4D7FF279DADDB61DE871012522(fr-fr,TechNet.10).png

Intéressant, mais j’aurais préféré une granularité plus fine permettant de différentier la lecture et l’écriture au niveau de la création du pool de ressources. peut être dans une prochaine version …

Afin de mener à bien le test, 2 utilisateurs vont être créés afin de distinguer 2 activités concurrentes niveau IO disque. Afin de forcer des lectures physiques, j’autorise l’utilisation du DROPCLEANBUFFERS à mes utilisateurs au travers de l’appartenance au groupe serveur sysadmin. Oui, je sais, c’est bien trop de droits, mais il s’agit d’un test.

1

2

3

4

5

6

CREATE LOGIN UserNormal WITH PASSWORD = 'pwd', CHECK_POLICY = OFF

CREATE LOGIN User_LowIO WITH PASSWORD = 'pwd', CHECK_POLICY = OFF

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [UserNormal]

ALTER SERVER ROLE [sysadmin] ADD MEMBER [User_LowIO]

GO


Reste à présent à créer la fonction de classification et a l’activer pour le gouverneur de ressources.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

CREATE FUNCTION fn_Classification_RessourceGovernor ()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

    DECLARE @val varchar(32)

    SET @val = 'default';

     

    if  'User_LowIO' = SUSER_SNAME()

        SET @val = 'Group_LowIOPS';

     

    return @val;

END

GO

 

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = dbo.fn_Classification_RessourceGovernor)

GO

 

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO


Ensuite, je vais créer 2 fichiers .sql qui vont contenir une requête me permettant de simuler une charge de travail. Pour ne pas me retrouver bloqué par l’autre processus, et donc dissimuler l’impact de la gestion des IO par le gouverneur de ressources, j’ai opté pour 2 tables distinctes, en forçant un cluster index scan pour maximiser les IOs. J’en profiter pour aller à l’essentiel pour ne faire que des IOs en ne respectant pas le verrouillage éventuellement posé par d’autres processus.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

--RQ_IO_Intensive1.sql

set nocount on

declare @i int

set @i = 43659 

 

while @i = 43659

begin

    SELECT count(*)

    FROM    AdventureWorks.sales.salesOrderHeader WITH (INDEX=0,NOLOCK)

    WHERE   SalesOrderID = @i

    OPTION(MAXDOP 1)

     

    DBCC DROPCLEANBUFFERS

 

    set @i = @i - 1;

end


Je lance l’exécution des deux scripts en simultané dans deux invites de commande :

sqlcmd -S localhost -U UserNormal -P pwd -i "C:\temp\RQ_IO_Intensive1.sql"
sqlcmd -S localhost -U User_LowIO -P pwd -i "C:\temp\RQ_IO_Intensive2.sql"

Ensuite je vais graduellement limiter le nombre d’IOSs de chaque pool de ressources.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

ALTER RESOURCE POOL Pool_LowIOPS

WITH (max_iops_per_volume = 80);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

Go

 

 

ALTER RESOURCE POOL Pool_LowIOPS

WITH (max_iops_per_volume = 50);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

Go

 

 

ALTER RESOURCE POOL Pool_LowIOPS

WITH (max_iops_per_volume = 20);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

Go


La DMV sys.dm_resource_governor_resource_pools permet de se rendre compte du travail effectué par le resource governor durant ce test:

Dn467406.453BA3B38ECEA50A2847DB2B828D7D54(fr-fr,TechNet.10).png

Et le graphe issu de Perfmon nous permet de voir le résultat du throttling d’IOs :

Dn467406.D37A89D44BF8CDC9097F4222797DD2E5(fr-fr,TechNet.10).png

On distingue les différentes pallier correspondant aux limitations d’IOs activées sur le groupe de ressources. Il apparait clairement que la limitation d’IO d’un groupe de ressources permet à l’autre groupe de ressources de bénéficier de plus de ressources et augmente ainsi le nombre de Reads/sec.

Si l’on zoome les paliers apparaissent alors plus distinctement :

Dn467406.03E3289FC5E8A0DB74DFA602FA48AAAA(fr-fr,TechNet.10).png

On voit aussi que la charge de travail (mes requêtes de test) ne permettent pas de lisser les IOs, ou du moins d’avoir une courbe plus lisse. Ainsi, la valeur maximale n’est pas toujours atteinte, sauf lorsque le throttling est plus agressif, avec une valeur très faible. Je vais travailler sur ce point de manière a vous proposer rapidement un autre scénario. Cela dit, quel que soit le scénario de test, cela ne remplacera pas un serveur en production qui doit encaisser de très grosses lectures disque (Datawarehouse, Fast Track).

Qui veut mettre mettre SQL 2014 en production dès maintenant ?

Nul doute que cette fonctionnalité fera bien des heureux parmi les DBA et les admins système qui se cassent la tête sur des problématiques d’IO concurrents … Elle n’est pas liée à une condition de stress comme max_cpu_percent, et peut même permettre de lisser des IO entre 2 instances hébergées sur un même serveur. je préfère éviter cette configuration mais si vous n’avez pas le choix, alors le resource governor peut vous aider à prioriser vos flux.

Happy throttling !

Afficher: