Discussion :

[elsuket]

Bonjour à tous,

Suite à une réponse de dbaffaleuf ici, je repose la question dans ce sujet.

Pour ma part, je pense que distinguer les axes physiques que l'on réserve aux fichiers de données (accès aléatoires) et aux fichiers des journaux de transaction est important.
Mais comme bien souvent, il est difficile d'avoir cette discussion avec un responsable d'Infrastructure, et on se retrouve donc avec des fichiers qui répartis sur plusieurs axes physiques.

Intuitivement, j'aurais dit que stocker les fichiers de données, les fichiers réservés aux index, et les fichiers des journaux de transaction chacun sur son/ses axe(s) physique(s) est bénéfique.
Mais je préfèrerai obtenir un avis d'expert, étant donné que je passe bien plus de temps sur l'optimisation du code que du hardware.

Qu'en pensez-vous ?

@++

[mikedavem]

Tu n'es pas un expert toi ?

++

[dbaffaleuf]

Mais non tu as raison de lancer le débat on se rend compte que ce n'est en fait pas si simple que ça (comme bcp de choses quand on commence à gratter un peu). J'ai été longtemps adepte du fait qu'il faille séparer data et journaux systématiquement sauf que je me suis rendu compte que ça peut être pire dans certains cas.

La séparation des données et des journaux est importante dans le scope d'une seule base. Séparer le ou les fichiers de données de son journal est mécaniquement plus intéressant sur des supports magnétiques, c'est clair.

Maintenant on a rarement qu'une seule base sur une instance. Et ce qui a été constaté c'est que mettre plusieurs journaux sur le même axe peut parfois amener davantage de latence parce qu'on n'a plus qu'un seul flux en écriture mais plusieurs intensifs en même temps, à des secteurs très différents du disque. Tu vois les écritures dans le journal vont avoir des tailles assez variables de 512 (secteur) à 64K il me semble (merci Mike de corriger), alors que les lectures / écritures aléatoires dans un fichier de données font en principe 8K.

Et séquentiel mélangé à du séquentiel, c'est potentiellement pire que aléatoire mélangé à du séquentiel, et en effet dans un cas récent de mon côté, j'ai vu des temps de service encore pire qu'avant après avoir séparé les logiques d'accès (disques physiques et contrôleurs différents, RAID10, FC-SCSi). On a dû passer les journaux sur du SSD pour voir disparaître les WRITELOG du haut de la pile.

Dans la pratique si on a plusieurs bases en TP il vaut mieux que chacune ait son axe physique dédié pour le journal. S'il n'y a vraiment qu'une seule base dont le journal est utilisé, et une petite dizaine de bases sous utilisées, on peut envisager de mettre leurs journaux sur le même disque, il faudra mesurer les temps de service. Après chacun choisit en fonction de ses possibilités, mais la performance a un coût on le sait.

Ce n'est qu'après avoir discuté sur la liste de diff MVP et visionné les conférences de Thomas Kejser (que je recommande à tout le monde) et discuté un peu avec lui que j'ai réalisé à quel point cette bonne pratique avait ses limites. Sans parler que sur du SSD, le raisonnement ne vaut plus rien.

HTH

[dbaffaleuf]

Par contre pour ce qui est des données et indexes séparés, je suis plus dubitatif. Je pars du principe général qu'avoir 20 disques pour mettre data + indexes sera plus intéressant que de mettre 10 disques pour les données et 10 disques pour les indexes. Souvent compartimenter est une bonne chose pour la maintenance ou la sécurité, mais c'est contre-performant (à l'exception de ce qu'on vient de dire sur les données / journaux).

Par exemple, l'idée de mettre tempdb sur des disques dédiés. Tu as 20 disques, tu mets données + indexes sur 10 disques, et tempdb sur 10 disques. Ca n'a de sens que pour éviter qu'une saturation de tempdb ait des conséquences sur la partie données / indexes. Je ne pense pas qu'on y gagne en performance. Et si ton axe données + indexes pose des problèmes de performances parce que 10 disques ce n'est pas suffisant, alors que tempdb ne fait que moins de 5% d'utilisation de ses 10 disques ? Pas de lissage possible de la charge dans ce cas, c'est dommage.

[dbaffaleuf]

j'ajoute que le problème a été discuté il y a peu sur le forum MS:

http://social.msdn.microsoft.com/For...3-0f53ebc2ff37

[mikedavem]

Tu vois les écritures dans le journal vont avoir des tailles assez variables de 512 (secteur) à 64K il me semble (merci Mike de corriger), alors que les lectures / écritures aléatoires dans un fichier de données font en principe 8K

.

Tout dépend ce qui est fait dans le journal mais on peut aller jusqu'à une 120Ko par IO il me semble.


J'ai également pu expérimenter chez certains clients que le fait d'avoir des disques dédiés et séparés pour chaque type de fichier SQL Server n'était pas vraiment la règle absolue.

Il est vrai qu'avant j'aurais eu tendance à dire que la bonne pratique est de séparer accès disques sur des axes différents pour les données, journaux de transactions et tempdb. Mais avec l'expérience je me rends compte que cela n'est pas forcément le cas.

J'ai déjà travaillé avec des clients qui avaient un axe physique pour l'ensemble des fichiers et qui n'avaient aucun problème. Le sous-système disque était taillé pour cela (par exemple un client avec une grappe de plus de 54 disques de mémoire en RAID-DP sur du Netapp et 80 bases de données).

Comme l'explique David c'est le problème de latence qui va être engendré par l'activité IO. Les écritures séquentielles par nature vont engendrés plus de latence de traitement que des les écritures aléatoires. Mélanger plusieurs journaux de transactions peut se révéler effectivement contre performant dans certains cas.

Avoir les fichiers de données + tempdb est effectivement une autre question et je te rejoins David la dessus. J'ai également visionné les vidéos à l'époque ou tu m'en avais parlé et c'est vrai que mon approche est différente depuis. C'est vrai que l'on a tendance à suivre les recommandations données par Microsoft sans réfléchir quelque fois mais ces recommandations doivent être revues surtout avec l'évolution rapide du stockage que l'on connait maintenant avec les SAN, le pooling des disques, les types de disques, l'auto tiering, les caches SSD et autres mécanismes de bas de niveau ...

++

[dbaffaleuf]

et bien voilà ce qu'on appelle un consensus

[SQLpro]

La séparation des données et des journaux est importante dans le scope d'une seule base.
[...]
Maintenant on a rarement qu'une seule base sur une instance.
[...]
Dans la pratique si on a plusieurs bases en TP il vaut mieux que chacune ait son axe physique dédié pour le journal.

je me bat depuis des années contre certaines SSII très proche de microsoft dont le nom commence M et finit par S, parce que chaque fois dans les conseils aux clients je voit la même erreur stupide :
toutes les données sur le même disque et tous les journaux sur un autre, avec dans le rapport mentionné "recommandation MS" !!!
C'est vrai, mais mal interprété...

En effet, comme tu le dit, les opérations de disque sont synchrones sur les journaux et asynchrones sur les datas. Pire les opérations de données s'effectuent toutes en même temps pour toutes les bases (mais c'est moins "contentionnant" que pour les JT)

Il faut donc ventiler et croiser les journaux et données car il est probable que les différentes bases n'agissent pas toutes en même temps et faire attention de répartir les batchs à différentes heures entre les différentes bases.

Ceci si l'on a pas la possibilité d'avoir 1 disque (ou agrégat) par journal, et 1 à 2 (ou agrégat) disques par base pour les données.

A +