Multiplication des fichiers de données

Bonjour à tous,
Aujourd'hui j'ai pour habitude lors de la création d'une nouvelle base, de créer plusieurs fichiers de données :
- Un pour les tables à données "statiques"
- Un pour les tables à données "dynamiques"
- Un pour les logs
- Un pour les index de clé primaire
- Un pour les autres index.

En partant du principe que cette méthode est bonne (je vous laisserais me contredire si ce n'est pas le cas !), je me posais une question pour une base de suivi de production, donc avec de nombreuses insertion et mises à jour :
Serait-il judicieux de scinder le fichier de données des tables "dynamiques", en regroupant les tables par étape de production par exemple, afin d'améliorer les performances, afin de limiter les accès concurrents par exemple.

Merci d’avance à tous pour les éclaircissement que vous pourrez m'apporter sur le sujet !

Bonjour,
Séparer les données du dictionnaire (groupe de fichiers PRIMARY) des données utilisateurs est dans l'absolu une bonne chose.
La séparation des pages de données selon leur usage n'a de sens que lorsque les fichiers sont sur des axes disques différents, souvent offrant des performances différentes (disque SSD pour les données très dynamiques, disques "low cost" pour les données statiques, ...).
Placer les différents fichiers de votre base sur le même disque (la même lettre de lecteur pour être précis) n'a aucun intérêt en terme de performance.
Placer les différents fichiers de votre base sur le même disque mais des lettres de lecteurs différents peut avoir un impact négatif sur la performance dans certains cas bien précis.

Bien entendu, cette séparation peut entraîner un surcoût d'exploitation important (il est bien plus facile de savoir qu'une base avec un unique fichier de données arrive à saturation, il est bien plus délicat de connaître cette information lorsque les objets sont répartis dans un grand nombre de fichiers de données qui n'ont pas tous les mêmes propriétés d'extension...).

Personnellement, j'ai tendance à préconiser d'aller vers la solution la plus exploitable par défaut (donc peu de fichiers de données), et de dissocier le stockage de certains objets dans ces cas spécifiques (saturation avérée de la capacité de traitement des disques, base stockant un grand volume de données mortes, ...).


Lorsque vous annoncez vouloir dissocier encore plus finement vos objets pour éviter des accès concurrents lors des mises à jours, je ne vous suis pas bien. La mise à jour des pages de données sur disque s'effectue de façon totalement asynchrone (mécanisme checkpoint) par rapport à vos ordres insert/update/delete. Donc pas de problème de concurrence entre les mises à jour, même si votre disque qui héberge les données est lent.

Globalement, si les disques qui hébergent vos différents objets offrent des temps de réponse corrects, il n'y a pas de nécessité à dissocier encore plus finement vos objets. Si par contre certains des disques offrent des performances non optimales parce qu'ils sont trop sollicités, il peut être envisageable d'isoler les objets les plus fréquemment accédés.


Cordialement.



PS: Attention à votre séparation: puisque par défaut avec SQL Server, une clé primaire est un indexe clusterisé, le fait de définir un groupe de fichiers spécifique aux clés primaires risque fort d'avoir pour conséquence que toutes vos tables sont hébergées dans ce groupe de fichiers !

Citation:

---

Envoyé par Tusozor

Bonjour à tous,
Aujourd'hui j'ai pour habitude lors de la création d'une nouvelle base, de créer plusieurs fichiers de données :
- Un pour les tables à données "statiques"
- Un pour les tables à données "dynamiques"
- Un pour les logs
- Un pour les index de clé primaire
- Un pour les autres index.

En partant du principe que cette méthode est bonne (je vous laisserais me contredire si ce n'est pas le cas !), je me posais une question pour une base de suivi de production, donc avec de nombreuses insertion et mises à jour :
Serait-il judicieux de scinder le fichier de données des tables "dynamiques", en regroupant les tables par étape de production par exemple, afin d'améliorer les performances, afin de limiter les accès concurrents par exemple.

Merci d’avance à tous pour les éclaircissement que vous pourrez m'apporter sur le sujet !

---

Quelques remarques :

Ce ne sont pas les fichiers qui permettent de mettre les objets dedans, mais les groupes de fichiers, un groupe de fichier pouvant comporter plusieurs fichiers.

Le groupe de fichier PRIMARY est de loin le plus important. Il contient des données systèmes stratégique notamment sur la structure de la base. Mieux vaut qu'il ne comporte qu'un fichier et qu'aucun autre objet n'y figure. Vous perdez ce fichier, vous perdez la base et il y a de fortes chances de ne pas pouvoir la récupérer.

Ensuite dispatcher plusieurs fichiers n'a de sens que :
1) si vous avez autant d'axe mécanique de stockage (via des grappes RAID de disques indépendants)
2) ou que vous prévoyez de le faire

Un fichier pour les index CLUSTERED n'a pas de sens si vous avez déjà un fichier pour les tables, car l'index CLUSTERED c'est la table !

Pour des raisons de performances, mieux vaudrait que toute tables ait une clef primaire et que toutes les clefs primaires aient un index CLUSTERED sous-jacent (fait par défaut). ATTENTION cependant à minimiser la longueur de la clef (pas trop de colonnes, des colonens de petite taille genre INT).

Vous ne pouvez pas utiliser plusieurs fichiers simultanément pour le journal de transaction.

Il y aurait bien des choses à dire, et nous avons consacré un chapitre de 87 pages dans notre ouvrage à paraître sur SQL Server, chez Eyrolles...

A +

Tout d'abord merci à vous deux pour vos réponses, j'y vois plus clair maintenant.

Si j'ai bien compris l’intérêt d'avoir plusieurs fichiers par rapport au nombre de disque physique, je me pose la question du rapport entre un SSD et un disque mécanique. Est-ce que le fait que le disque soit en flash ou en mécanique à une incidence sur la façon dont nous allons séparer les différents fichiers, ou cela n'est pas a prendre en compte disons dans "structure" à mettre en place.

Concernant le journal des transactions, si l'on crée deux fichiers, ceux-ci sont utilisés l'un après l'autre et donc le gain d'est pas réel c'est cela ?

Concernant mon idée de dissocier plus finement mes objets, je pensais (et apparemment à tort) que si une opération DML affectait une table, son fichier se retrouvait "vérouillé" le temps de l'opération et créait une sorte de file d'attente et que donc si on séparait les fichiers on limitait cette file d'attente. Cependant j'avoue ne pas pleinement maitriser cette notion de mise à jour asynchrone du fichier par rapport aux ordres. Connaissez vous un article qui me permettrait d’approfondir le sujet ?

Contrairement à un disque à plateaux, un disque SSD est complètement insensible aux problèmes de contiguïté de données . Les lectures/écritures aléatoires et les lectures/écritures séquentielles sont donc globalement aussi coûteuses sur ce type de disque, alors que les lectures/écritures aléatoires sont bien plus coûteuses sur une technologie à plateaux. Donc dans l'absolu, il y aurait un gain à placer les objets souvent accédés avec des lectures aléatoires (indexes non clusterisés principalement) sur du SSD.

Mais il faut très fortement relativiser sur ce type de considérations, puisque:

• Un disque n'est presque jamais utilisé seul (sur un environnement de production), mais il est l'un des éléments d'un RAID. La notion de contiguïté des données n'est plus la même.
• Le contrôleur qui gère les accès aux disques dispose d'une mémoire cache pour réduire le coût de certaines opérations
• Les systèmes de fichiers (NTFS ici) disposent d'algorithmes permettant de réduire les coûts de certaines opérations (lecture en avant, groupement d'écritures avec scatter/gather io, ...)

Donc la distinction entre SSD et disque à plateau est très facilement masquée, sauf lorsque les disques sont très fortement sollicités. Pour prendre un raccourci, on peut bêtement dire qu'un SSD sera plus rapide pour toutes les entrées/sorties qu'un disque à plateaux ou qu'un RAID de disques à plateaux. Donc les objets pour lesquels on effectue le plus d'entrées/sorties sur disque gagneront à être sur du SSD.

Pour le journal de transactions étalé sur plusieurs fichiers, les enregistrements sont effectivement écrits sur un fichier à la fois (SQL Server voit l'ensemble des fichiers comme un unique fichier logique, qu'il divise ensuite en Virtual Log Files), il n'y a donc aucun gain de performance à créer plusieurs fichiers.

Et je rejoints complètement SQLPro pour dire que ce sujet (qui est au cœur de la gestion des performances d'un moteur de bases de données) est très vaste, et qu'il n'est pas réellement possible de formuler recommandations en quelques échanges sur un forum...

Pour compléter, un extrait de mon livre au sujet des SSD :
"
Disques SSD

On pourrait penser, en termes économiques, qu’un disque de type SSD (Solid State Device) permet de meilleures performances qu’un ensemble de disques magnétiques. C’est vrai dans un sens mais à budget égal et volumétrie comparable, c’est faux :
• les SSD sont encore très chers par rapport aux disques magnétiques ;
• Leurs performances sont instables (très élevées en début de vie, se stabilisant à un niveau plus bas à l’utilisation) ;
• La rétention des informations n’est possible que sur quelques mois (il faut constamment les maintenir en exploitation) ;
• leurs algorithmes de stockage rendent difficiles voire impossibles la récupération de données en cas de panne du disque ;
• l’écriture dans un SSD est bien moins rapide que la lecture, particulièrement dans le cas d’une modification. Or les SGBDR cachent les données en RAM et font donc peu de lectures mais beaucoup d’écritures ;
Tant est si bien qu’actuellement et à même budget, une bonne organisation de disque dur magnétique en RAID 0 bât à plate couture les meilleurs SSD disponibles à ce jour ! Mais le jour est proche ou le SSD remplacera avantageusement leurs ancêtres magnétiques...

Cependant, outre la vitesse de lecture accrue, le disque SSD présente certains avantages dont un bon nombre sont d’ordre écologique :
• moindre consommation d’énergie ;
• énergie dissipée sous forme de chaleur moindre ;
• pas de bruits ni de vibrations.
Notez que le MTBF (temps moyen de bon fonctionnement, en anglais Mean Time Between Failures) autrefois bien plus court sur du SSD a été amélioré en redondant le stockage de manière interne.

PERFORMANCE : dans le cas d’une organisation mixte (SSD + disques magnétiques) préférez pour les bases OLTP placer en premier tempdb en SSD, et pour les bases OLAP, les tables et les index les plus lus.

NOTA : certains fabricants, proposent un stockage contournant le biais du contrôleur de disque en passant par le bus PCI, ce qui accélère encore les accès au stockage (Fusion-io par exemple).
"

A +