Discussion :

[kedare]

Hello.
Je me pose une question, la quelle 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 (par base de données éventuellement) (comme Mysql/InnoDB, SQL Server, Firebird et aussi Oracle il me semble)
- Plein de petits fichiers ou un par table (comme Mysql/MyISAM et Postgresql)

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 ?

[Jester]

Sous linux et windows la taille des fichiers joue peut pour leur mise en cache. Le cache fonctionne sur des pages de 4ko (me semble). En général, on utilise pas le cache de fichier, la base de données a son propre cache. MyIsam est la seule exception de mémoire.

J'ai déjà vu des problèmes sous mysql avec un trop grand nombre de tables. Avoir un fichier ouvert consomme des ressources.

Le mieux c'est de pouvoir faire plusieurs tablespaces que l'on peut mettre sur des disques différents et splitter les données selon (index, data et log).

[SQLpro]

Les SGBDR évolué (DB2, Oracle, SQL Server...) implémente le 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 coupe 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.

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 à stocké 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 tel 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 identique, car il me faut successivement aller lire un disque puis l'autre...
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.

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/p5...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 !

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 subtile 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/sqls.../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 bien entendu les plus persistantes doivent être les plus utiles..., 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é 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/).

A +