Discussion :

[lepierot]

Bonjour,

Je voudrais savoir si dans un environnement VMWARE le fait de separer les index et les data respectivement dans des tablespaces differents et sur des disques differents cela peut ameliorer sensiblement les performances des requetes et si oui de quel ordre ?

Merci

[SQLpro]

Cette séparation n'avait d'intérêt qu'il y a fort longtemps et ne concerne pas directement les performances. En effet, il y a 20 ans, les disques coutaient cher. On utilisait donc des disques de haute qualité pour les tables et de moindre qualité pour les index. En effet en cas de perte des disques sur lesquels reposent les index il suffit de le recréer ailleurs. Mais on ne peut pas recréer les données magiquement en cas de perte des tables ! C'était donc une raison strictement économique.... et peu axée sur les performances.... les données (tables et index) étant écrites de manière asynchrone.
Seule, les lectures physiques (en principe rares...) pourrait avoir un intérêt à faire l'objet d'une séparation physiques pour faire des remontées en parallèle. Mais ceci suppose plutôt une architecture physique que logique (VM...)

A +

[pachot]

Bonjour,

Je voudrais savoir si dans un environnement VMWARE le fait de separer les index et les data respectivement dans des tablespaces differents et sur des disques differents cela peut ameliorer sensiblement les performances des requetes et si oui de quel ordre ?

Merci

Non, et ça n'a rien à voir avec VMware. Pour les performances, il est toujours préférable de répartir les données sur un maximum de disques. Une session ne va jamais lire en même temps l'index et la table. C'est en général: lire 1 bloc d'index, puis lire plusieurs blocs de tables référencés par 'entrée d'index. Par contre, elle peut faire plusieurs I/O parallèles sur la table. Plus il y a de disques derrière et plus ce sera rapide.

[Michel.Priori]

Bonjour,

Merci messieurs de vos réponses.
Cela fait longtemps que j'essaie de faire comprendre que la séparation des données et des index est un leurre.

Il est évident que si on fait les tests à moitié, on peut voir les perfs s'améliorer.
En général le scénario est le suivant :
- on part d'une base où table et index sont dans le même tablespace (TS) /groupe de fichier (GF)
- on ne réorganise jamais aucune données (index, heap, IOT, ...)
- un "expert" arrive et conseille de séparer les tables des index dans des TS différents
- plus assez de place pour créer le TS => ajout de disque
Lors du transfert on bénéfice de la ré-écriture des index (réorganisation des données) + d'un disque => amélioration des perfs. Je valide.
Le vrai test à faire consiste à garder le même nombre de fichiers répartis sur les mêmes disques. Ainsi on teste l'incidence des TS/GF sur les perfs. Tout autre test ne fera que mélanger plusieurs composantes.
Mais c'est plus cher, on n'a pas l'architecture ni le temps pour ça => création d'un mythe.

L'ajout de fichier peut, en soi, être un facteur d'amélioration des perfs si le système peut allouer efficacement les thread supplémentaires.
Trop peu ou trop de fichiers, peut être pire que mieux.
Jamais trouvé l'algo de résolution optimal;
Microsoft recommande, pour Tempdb, de créer autant de fichiers que de cœurs avec un maximum fixé à 8.

Du point de vue des perfs, je retiens que les critères importants sont :
- si possible dédier des SPIN disques pour
. les redo / journal
. TS temporary et UNDO / tempdb
car ils agissent en tant que facteurs limitants lors des montées en charge
- répartir les TS en plusieurs fichiers qui s'appuient sur différents SPIN disque
car lors les données sont montées du disque vers la mémoire lors de la demande de lecture - si elles ne sont pas déjà en RAM (les écritures sont asynchrones ; moins sensible)

La conversation sur la création des TS est largement mieux comprise par les DBA Oracle que ceux de SQL server : je vois souvent des fichiers .mdf de plusieurs centaines de Go. Le best-of reste les demandes pour passer les partitions en GPT pour pouvoir avoir de "gros fichiers".

La création des GF est importante pour le RESTORE, donc, l'administration.
Et ce n'est pas parce que c'est pour l'administration que ce n'est pas important !

[lepierot]

Bonjour,

Sur une base de donnee 12c, certaines requetes etaient longies, les data et les index etaient dans le meme Table Space (data). Il a créé un second TS (Index) et les performances se sont ameliorées. Cela vient de quoi alors ?

Merci

[Michel.Priori]

Bonjour ,

Sur une base de donnee 12c, certaines requetes etaient longues, les data et les index etaient dans le meme Table Space (data). Il a créé un second TS (Index) et les performances se sont ameliorées.

Premièrement, tout dépend de ce qui a été fait après la création du TS :
Est-ce que seuls les nouveaux index ont été créés dans ce nouveau TS ?
Est-ce que certains (voire tous) index existants ont été déplacés vers le nouveau TS ?
** si oui, avec quelle syntaxe ?
Est-ce que ce fut la seule action entre les 2 périodes ?

Vu que vous avez constaté une amélioration des performances c'est que :
1- vous avez mis en place un audit (ou un relevé de compteurs) avant pour le comparer avec l'audit d'après migration
2- vous avez pointé les requêtes qui ont
..a- le plus plus bénéficié
..b- le moins bénéficié

Note : si vous constatez des requêtes qui régressent cela devient tout à fait intéressant pour l'analyse du changement.

3- vous avez, je l'espère, gardé des anciens explains plan pour pouvoir les comparer aux nouveaux (là encore si le plan d'exécution change cela veut dire quelque chose)

Vous annoncez que la base est en 12c.
Il existe un certain nombre d'actions automatisées par défaut dans cette version (statistiques, ...)
Avez vous créé la base avec l'assistant ou via un script ?
Comment les données sont arrivées ? export-import, migration, données fraiches ?

Tout ça pour dire que faire des analyses d'une situation sans pouvoir avoir accès aux informations nécessaires est délicat voire douteux, et, vous comprendrez que je fais devoir en rester à des considérations généralistes pour répondre à votre question.

Comme je l'ai indiqué dans le post que vous avez (presque) mis en commentaire, le simple fait d'ajouter un fichier, même sans utiliser des ressources disques distinctes (sur un SAN on parle de SPIN distincts), peut aider le jeux des thread, et donc, les performances.
C'est à peu près la seule chose que je peux dire sans prendre de risque vu le manque d'informations sur l'environnement précis.

Du coup à moi de vous questionner ; Mes questions sont :
Qu'elles sont, pour vous, en tant que DBA, les hypothèses sur le phénomène observé ?
Une fois les hypothèses énoncées, quels sont les tests que vous avez menés pour les infirmer ou les conforter ?

[lepierot]

Bonjour ,
En fait nous avons migré d'une base 8i vers une base 12c. La base 8i etait sur vieil AIX 4.3 avec des TS differents pour les data et les index et sur des disques distintcs.
Quand nous avons Exporter les donnes de la base 8i puis importé vers la base 12c (dans un seul TS) les utilisateurs se sont plaints de traitements qui etaient trop longs, beaucoup plus long qu'avec la base 8i.
Du coup nous avons supprimes puis recree les index dans un TS supplementaires et les choses sont rentrees dans l'ordre.

Nous n'avons pas fait d'audit.

Merci

[Michel.Priori]

Bonsoir et merci de votre retour

En fait nous avons migré d'une base 8i vers une base 12c. La base 8i etait sur vieil AIX 4.3 avec des TS differents pour les data et les index et sur des disques distintcs.
Quand nous avons Exporter les donnes de la base 8i puis importé vers la base 12c (dans un seul TS) les utilisateurs se sont plaints de traitements qui etaient trop longs, beaucoup plus long qu'avec la base 8i.

C'est étrange car en général passer de la 8i vers la 12c + changement de matériel (vieil AIX 4.3) aurait dû améliorer les perfs.
Pouvez-vous nous en dire plus sur le matériel source et destination ?

Est-ce que l'instance a été avec l'assistant ?
Les TS sont en 'extent management auto' ?

Comment les index ont été transportés ?

Du coup nous avons supprimes puis recree les index dans un TS supplementaires et les choses sont rentrees dans l'ordre.

L'ajout du TS n'a pu se faire qu'avec l'ajout de fichiers.
Combien, sur quel support, etc ...

Est-ce que les 2 TS ont les mêmes propriété ?

Nous n'avons pas fait d'audit.

Ça c'est embêtant.
A posteriori on ne sait pas dire quels sont les éléments fiables, pour peu que les dev aient profité de l'interruption pour pousser des correctifs, on ne sait plus que dire.

[SQLpro]

Bonjour,

Sur une base de donnee 12c, certaines requetes etaient longies, les data et les index etaient dans le meme Table Space (data). Il a créé un second TS (Index) et les performances se sont ameliorées. Cela vient de quoi alors ?

Merci

En déplaçant vos index, vous les avez sans aucun doute défragmentés, d’où le léger gain.....

A +

[Michel.Priori]

Re bonsoir,

A ce stade le test qui mettrait tout le monde d'accord serait de créer un 3ieme Ts de 2 fichiers sur les mêmes dispositifs de stockage que les fichiers actuels et de tout transférer vers le nouveau TS.

Note: attention à la notion "Big file" / "small file" ; un TS ne sait alloué que 2^32 blocs ; pour les TS small files on peut créer 2^10 fichiers de 2^22 alors que dans les TS de type big file on ne peut créer qu'1 fichier mais qui peut contenir 2^32 blocs ; mettre petit dans grand c'est simple, l'inverse est plus complexe...

En prenant les dispositions qui s'imposent avant migration et après migration, on pourra déterminer les réglages qui ont des effets de levier et s'en servir à bon escient.

Est-ce que le jeu en vaut la chandelle ?
A mon sens oui.
On pourra s'en servir pour faire varier le nombre de fichiers par TS tout en les spécialisant (données chaudes/froides, tables partitionnées, ...)
Mais surtout, reprendre la main sur la logique du fonctionnement global de son architecture en validant les hypothèses par des tests (et non pas par des croyances dans la bonne parole d'un spécialiste, même s'il a raison ; on fait de la technique, pas de la magie !)