Alignement de partitions pour amélioration des performances de MS-SQL Server

En matière de SGBDR les opérations les plus coûteuses sont celles réalisées par les accès physiques aux disques. Le fractionnement et la structuration des emplacements physiques des disques sont d'une importance capitale en termes de performance et peuvent diviser les temps d'accès aux données par trois. Voici quelques explications sur cette problématique et la façon d'y remédier.

Imaginez que vous êtes bibliothécaire et qu'une partie de votre tâche consiste à ranger des livres en les classant. Aujourd'hui vous devez ranger deux collections de livres dans une partie de la bibliothèque. Une collection, une fois rangée, remplit la totalité de l'étagère de votre bibliothèque. Vous les rangez donc sur leur étagère respective. Si vous devez récupérer une collection entière de livres, il est facile pour vous de le faire : il suffit de repérer l'étagère concernée et de prendre les livres. Maintenant, si votre directeur tient absolument à ranger ses livres au début de cette 1re étagère les collections de livres qui étaient parfaitement rangés se voient maintenant décalées et dispersées sur deux étagères à la fois, car ceux-ci, rappelons-le, prennent la place d'une étagère entière. Dans ce cas, que ce soit pour les ranger ou les récupérer, il vous faudra évidemment plus de travail.

Remplacez les collections de livres par les données sur vos disques et les livres décalés suite à la demande du directeur par un décalage causé par le système d'exploitation et vous obtiendrez la problématique d'alignement des partitions et des problèmes de performances d'entrées/sorties engendrés.

Commençons par aborder certains concepts techniques concernant les disques durs, sans pour autant entrer dans le détail, l'article ne visant pas l'exhaustivité.

Un disque dur est une mémoire de masse magnétique composée d'un ou plusieurs plateaux. Chaque plateau est composé de pistes. Les pistes situées à un même rayon forment un cylindre. Chaque piste est délimitée en secteurs ou blocs qui contiendront les données. Un secteur fait 512 octets. Un disque dur possède également une ou plusieurs têtes de lecture pour pouvoir lire et écrire les données. Il y a autant de têtes que de surfaces à lire (en fonction des différents plateaux).

Qu'est-ce qu'une partition ? Quelle en est son utilité ?

Une partition accueille un système de fichiers et possède accessoirement un secteur d'amorçage ainsi que la table des partitions (MFT) nécessaire à retrouver vos données. Cette table se situe sur le 1er secteur du disque qui contient également toutes les informations « constructeur » relatives au disque lui-même. Il s'agit donc du secteur le plus important, car il est utilisé par le BIOS pour la reconnaissance du disque.

La plupart des serveurs de bases de données reposent aujourd'hui sur des systèmes à tolérance de panne permettant une haute disponibilité et des performances accrues à base de technologie RAID. Nous terminerons par ce point avant d'aborder l'alignement de partitions à proprement parler. La 1re étape, lorsque configurer un RAID, est de créer votre volume RAID. Vous devrez tôt ou tard choisir une taille pour les « strippings blocks ». Mais qu'est-ce que cela signifie ?

Prenons le cas d'un raid 0. Lors de son fonctionnement, le serveur écrit ou lit sur deux disques en même temps en séparant les données en blocs. Un bloc sur deux se retrouvera sur un disque et les autres sur le deuxième. Le paramètre « stripping block » permet de régler la taille de ces blocs.

Toutes les tailles conviennent-elles ? Évidemment non ! À moins de posséder une carte professionnelle coûteuse, le découpage des blocs sera assuré par la CPU. Si l'on paramètre une taille trop petite, la CPU se trouve surchargée par des opérations de découpage de blocs et le serveur verra ses performances diminuer. Il existe pour chaque contrôleur RAID une taille optimale de bloc appelée « CHUNCK ». Les valeurs de CHUNK courantes varient entre 16 Ko et 128 Ko. Pour SQL Server, la taille recommandée est de 64 Ko.

Attardons-nous à présent sur l'alignement des partitions proprement dit. Pourquoi avoir parlé de disque dur, de partitions et de taille de bande (stripe size) ? Lorsque vous créez une partition sur le système d'exploitation, en l'occurrence Windows (Attention, ceci ne concerne pas Windows Server 2008), par défaut celui-ci se réserve les 63 premiers secteurs du disque dur. Ces 63 premiers secteurs peuvent contenir certaines informations comme le Master Boot Record et la table d'allocations des partitions, comme nous l'avons vu précédemment. Le système d'exploitation écrit ensuite les 512 premiers octets sur la 1re piste et écrit le reste sur la 2e piste. Par conséquent une simple écriture ou une simple lecture requiert un double accès ! Nous avons identifié ainsi notre problème, nos livres rangés sur deux étagères…

L'alignement des partitions permet de résoudre ce problème. Comment ? En réservant non pas les 63, mais 64 premiers secteurs du disque. Cette taille peut changer en fonction de la taille d'allocations des clusters de partition. Nous y reviendrons par la suite.

V-A. 1er exemple▲

Prenons une valeur de strippe de 64 Ko. Le système d'exploitation alloue une taille de cluster de 4 Ko par défaut. Chaque cluster contient donc 8 secteurs de 512 octets et chaque largeur de bande du raid (stripe Unit) contient 16 clusters. L'image ci-dessous illustre nos propos.

Nous voyons bien que pour lire le 8e cluster il y aura deux entrées/sorties qui seront effectuées pour une partition non alignée contre une seule pour une partition alignée. Pour arriver à ce résultat, il suffit d'allouer un secteur supplémentaire au début du disque.

V-B. 2e exemple▲

Arrêtons-nous sur un nouvel exemple. Cette fois, prenons une taille d'allocation de cluster de 64 Ko (recommandée pour les fichiers de données, de transactions et tempdb de SQL Server).

Dans ce 2e cas nous observons également les avantages d'aligner correctement les partitions en réservant cette fois-ci les 128 premiers secteurs soit 128 x 512 = 64 Ko. Comme vous pouvez le constater, la taille d'alignement des partitions se fait en fonction de la taille d'allocation des clusters lorsque vous partitionnez vos disques.

De nombreux utilitaires existent pour créer des partitions sur Windows. J'évoquerai simplement quelques utilitaires « natifs » Windows qui nous permettront d'arriver au but recherché, à savoir…l'alignement des partitions !

• diskmgmt.msc : la console de management de Windows permet non pas de procéder à l'alignement des partitions, mais de faire de l'allocation de cluster.
• Msinfo32 : cet utilitaire Windows permet de voir l'alignement des partitions (offset), mais ne permet pas de partitionner ou de faire de l'alignement.
• Diskpar : cet utilitaire en ligne de commande Windows permet de visualiser et de faire de l'alignement de partitions.
• Diskpart : cet autre utilitaire en ligne de commande Windows permet également de faire la même chose que diskpar.

Nous prendrons pour notre exemple « diskpart ».

DISKPART > List disk
DISKPART > Select disk 3
DISKPART > create partition primary align=64                  
DISKPART > assign letter=G
DISKPART >Exit
C:> format /fs:ntfs /A:64K /V:"DiskSql"  /Q   G:

Dans l'exemple ci-dessus, nous avons créé une partition primaire sur le disque 3 avec un décalage ou offset de 64 Ko. Nous avons ensuite réservé la lettre G et formaté notre partition en NTFS avec une taille de cluster à 64 Ko.

Visualisons à présent l'offset créé. Pour cela nous n'utiliserons pas diskpart, car celui-ci se révèle imprécis à la lecture de l'offset qui s'affiche en Ko. Pour rappel, Windows alloue les 63 premiers secteurs du disque par défaut.

Les calculs suivants permettent de vérifier nos propos :

• par défaut : 63 * 512 octets = 32 256 octets = 31.5 Ko ;
• avec alignement : 64 * 512 octets = 32 768 octets = 32 Ko

Diskpart dans les deux cas affichera 32 Ko ! Soyez donc prudent à la lecture de l'offset avec cet utilitaire.

Nous prendrons wmic, autre utilitaire fourni par Windows permettant de visualiser l'offset de la partition avec plus de précision :

C :> wmic partition get BlockSize, StartingOffset, Name, Index
 
BlockSize  Index  Name                         StartingOffset
512        0      Disque n° 0, partition n° 0  32256
512        0      Disque n° 1, partition n° 0  32256
512        0      Disque n° 2, partition n° 0  65536

Nous voyons donc la partition créée précédemment ainsi que l'offset de 65 536 octets soit 64 Ko.

Notez au passage le désalignement des disques n° 0 et n° 1 avec un offset de 32 256 octets par défaut soit 31.5 Ko et 63 secteurs et non 32 Ko et 64 secteurs !

Après avoir aligné et partitionné correctement nos disques, voyons les résultats en termes de performance. Pour cela nous utiliserons SQLIO, utilitaire fourni par Microsoft permettant de mesurer la capacité en termes d'entrées/sorties pour une configuration donnée.

Les tests se baseront sur différents scénarios rencontrés dans SQL Server dans un environnement de type OLTP. Nous aurons les caractéristiques suivantes :

• les lectures des fichiers de données sont constantes par nature et aléatoires (8 Ko) ;
• les écritures dans les fichiers données se font essentiellement lors des opérations de CHECKPOINT et sont aléatoires (8 Ko) ;
• les opérations de type ROLLBACK utilisent un mode de lecture aléatoire sur les fichiers de transactions (8 Ko) ;
• les lectures des fichiers de transactions sont également séquentielles (la taille des secteurs pouvant aller jusqu'à 120 KB) ;
• les écritures dans les fichiers de transactions sont séquentielles et de diverses tailles (tout dépend de la nature de la charge) ;
• les opérations de réindexation de tables, d'insertion en blocs et de vérification de bases de données utilisent un mode de lecture et d'écriture séquentiel (64 Ko - 256 Ko) ;
• les backups utilisent un mode d'écriture séquentiel avec une taille de 1024 Ko.

Nous testerons pour chaque partition (non alignée et alignée) :

• pour les I/O aléatoires : lecture et écriture avec une taille de bloc à 8 Ko ;
• pour les I/O séquentielles : Lecture et écriture avec les tailles de bloc de 8, 64, 128, 256 et 1024 Ko.

VII-A. Environnement du test▲

VII-B. Résultat des tests▲

VII-B-1. Lectures et écritures aléatoires avec une taille de bloc de 8 Ko▲

Les tests montrent une différence notable pour lectures (21 %), mais pas de grande différence en écriture (1,5 %). Ce test peut être difficilement interprétable. Pourquoi n'observe-t-on pas tellement de différence en écriture ? Nous avons une taille de cluster de 8 Ko, une largeur de bande de 64 Ko et des écritures aléatoires. La probabilité d'écrire dans la zone critique, c'est-à-dire celle qui divisera un bloc en deux parties reste faible, que ce soit sur une partition alignée ou pas.

VII-B-2. Lectures et écritures séquentielles avec une taille de bloc allant de 8 ko à 1024 Ko▲

Les tests montrent une différence notable aussi bien en écriture (13 %) qu'en lecture (18 %). Sur des lectures et écritures séquentielles la probabilité d'écrire sur la zone critique est beaucoup plus élevée dans ce cas. L'alignement des partitions y joue tout son rôle.

L'alignement des partitions reste une des nombreuses techniques d'optimisation existantes telles que la mise en place de technologies RAID, l'ajout de disques rapides, de caches disques, de bus de données plus rapides, etc. Corrélée à un paramétrage adéquat de taille de clusters et de largeur de bande (stripe unit size) au niveau de vos configurations RAID, vous devriez tirer parti des avantages d'une technique assez simple à mettre en œuvre avant même d'avoir installé votre serveur SQL.

Bon alignement !!

IX-A. Webographie▲

IX-B. Bibliographie▲

Je remercie SQLPro et Fadace pour la relecture attentive de cet article.