A partir de la question d’un internaute sur la gestion des espaces de stockage dans les SGBDR, j’ai voulu donné un panorama rapide des enjeux technique qu’il convient de prendre en compte au moment de passer à l’action…
Attention, c’est du brut de fonderie !
Je me pose une question : laquelle de ces deux approche est la plus avantageuse au niveau des performances sur l’organisation des fichiers de données :
– Un seul gros fichier « tablespace » ou sont stockées toutes les données
– Plein de petits fichiers ou un par table
En théorie je pense que les deux ont chacun leurs avantage pour les raisons suivantes (ce sont que des suppositions):
– Un seul gros fichier : Si le fichier est alloué a une certaine taille dés le début (par exemple Go par Go), cela devrais empêcher la fragmentation lors de l’insert de nouvelle données, le fichier étant déjà positionné sur le disque a ce moment la.
– Plein de petit fichiers : Facilite la mise en cache en RAM par le système de fichier sous Linux/*nix car l’on ne met en cache que les fichiers nécessaires au lieux de tout mettre (ou ça ne serais pas forcément possible vu la taille du seul gros fichier)
Qu’en pensez vous ? Ai-je bon ? Connaissez vous d’autres avantages/inconvénients a l’un et l’autre ?
*** et voici ma réponse ***
Les SGBDR évolué (DB2, Oracle, SQL Server…) implémente leur propre système de gestion de fichiers indépendants de l’OS et du formatage dont les « cluster » sont des pages de 2 à 32 Ko et dont la taille optimale est fonction du sous système disque et du couple OS/hardware. Par exemple pour SQL Server qui est mono système la taille des pages des fichiers est de 8 Ko que ce soit en FAT32 ou en NTFS, sans aucune incidence sur les performances. Sous les autres SGBDR la taille optimale est donc fonction de divers paramètres et les aides en ligne donnent généralement une taille préconisée en fonction des OS.
La question de la multiplicité des STORAGE (nom générique des tableSpace d’oracle ou des groupe de fichiers de SQL Server) est très intéressante et mérite une longue explication qu’un post fait à 11h du soir ne peut détailler.
Disons qu’en gros un storage est l’unité de stockage d’un objet à stocker qui peut être : soit un index, soit une table, soit enfin une partition, c’est à dire une partie d’une table ou d’un index logiquement délimité (par exemple par une valeur pivot de date dans une colonne de ce type). Un storage peut lui même être composé de plusieurs fichiers (et oui, une table peut faire quelques tera octets de données alors que les disques sont limités).
Si vous ne faites qu’un seul storage avec un seul fichier, il y aura concurrence d’accès lors des écritures. Celles ci devront se faire en file. Si vous faites deux fichiers dans un même storage, et à condition que ces deux fichiers soient sur deux axes physique mécaniquement et logiquement indépendants, le SGBDR va répartir les écritures en mettant certaines lignes dans un fichier et d’autres dans l’autre créant ainsi une équi répartition des lignes afin de faire du parallélisme au niveau des écritures…
Vous commencez à comprendre que dans ce cas nous allons favoriser les écriture et qu’en théorie, la vitesse d’écriture va être multipliée par le nombre des disques. En revanche celle des lectures reste aléatoire… tantôt il me faut successivement aller lire un disque puis l’autre (requête monothreadée) ou bien en parallèle si le plan de requête invoque plusieurs threads…
Si maintenant je créé deux storages avec chacun un fichier et que dans l’un je place les tables et dans l’autre les index des mêmes tables, alors je favoriserait les écritures, mais aussi bien des lectures. En effet, une requête faisant souvent appel à de multiples tables, je peut aller chercher un index sur un storage pendant que le lit des lignes de table sur l’autre storage. Un bon compromis est 2 fichiers de données pour 1 fichiers d’index sachant que grosso modo une base bien modélisée entraine une indexation dont le volume est répartit à peu près de la manière suivante : 2/3 de données pour 1/3 d’index (le quatrième tiers, comme dirait César dans Marius de Pagnol étant réservé au journal des transactions). Enfin, PostGreSQL ne possède qu’un journal de transaction pour toutes les bases, là ou SQL Server en possède un par base + 1 pour la base abritant les objets temporaires (tempdb), et l’on sait que souvent le premier point de contention dans un SGBDR c’est justement le journal des transactions…
De surcroit sur les 3 grands SGBDR, il est possible de pré tailler les fichiers relatifs aux storage de façon à ce qu’aucune opération de croissance n’ai lieu pendant la production. Le gain ainsi obtenu est doublement spectaculaire (lire l’article que j’ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p5859/ms-sql-server/fragmentation-physique-des-fichiers-et-t/) En effet, non seulement on évite de telles opérations hautement couteuses, mais le SGBDR est capable d’auditer le disque lors de la création des fichiers, afin de placer les pages réservées du fichier ainsi constitué au meilleurs endroit du disque en terme de performance d’accès, c’est à dire sur les bords externes de tous les plateaux ! (cela réduit drastiquement les temps d’écriture car le SGBDR regroupe les données à écrire par contiguité géographique). Cette opération peut s’effectuer sur un disque attaché au serveur, comme sur un SAN (pour cela MS SQL Server, par exemple, dispose de routines spéciale pour accéder directement au SAN…).
Maintenant en multipliant et combinant ceci de façon importante, je peut augmenter significativement les performances de mon système.
MAIS… tout ceci n’est intéressant qu’au niveau des lectures physiques qui doivent en principe être très rare dans une SGBDR puisque le cache est là pour stocker les données les plus souvent requises. En sus, la multiplication des fichiers ouverts nuit aux performances. C’est pourquoi il faut adopter un bon compromis, et en cette matière tout dépend de nombreux facteurs dont beaucoup sont lié à la qualité du sous système disque (contrôleur RAID, Niveau de RAID, vitesse de rotation des disques… en étant les principaux paramètres.
Ainsi les SGBDR qui font un fichiers par table n’ont en fait aucun intérêt et les performances se dégradent très vite s’il y a de nombreuses tables.
Pour terminer, des choses aussi subtiles que l’alignement des partitions lors du formatage des disques peut influencer de manière importante sur les performances ! Lisez par exemple l’article de mikedavem sur le sujet :
http://mikedavem.developpez.com/sqlserver/tutoriels/architecture/
Enfin, la question de la mise en cache ne se pose pas en terme d’OS, car ce n’est pas l’OS qui met en cache, mais le SGBDR qui accapare la mémoire pour gérer son propre cache. En effet les OS ne connaissant pas les données SQL et ce qu’il convient de mettre en cache dépend de la nature des données ! En effet, on ne met pas en cache une table, mais des pages, et par exemple pour un index, la page la plus utilisée sera toujours la page racine de l’arbre d’indexation ! Comment un OS pourrait-il le savoir ?
Voyez que les choses ne sont pas si simple, et la virtualisation des serveurs, comme l’utilisation du RAID 5 ou 6 agissent totalement en contraposition à l’ensemble des éléments que je viens de vous donner. Quand à l’arrivée des disques SSD, ceci modifiera énormément les algorithmes en jeu dans les mécanismes de lecture et écriture des SGBDR. Comprenez finalement que les bon SGBDR incorporent un OS interne qui s’occupe en toute indépendance du système, de gérer les fichiers de la base comme il l’entend, pour le maximum de performances (ceci est d’ailleurs aussi vrais pour les threads et la RAM !) à un tel point que supprimer l’OS dans un SGBDR n’est pas une gageure, Oracle l’avait fait avec insuccès il y a quelques années, et Microsoft est en train de le tenter pour 2012 http://www.sqlservercentral.com/articles/humor/62658/.
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
