Ceci est une traduction de d’un post de Jonathan Lewis sur son blog – la quatrième et dernière partie d’une série de quatre sur la fragmentation (original en anglais). Il est conseillé de lire avant: Fragmentation – Introduction, Fragmentation – Disque et Tablespace, Fragmentation – Table

Fragmentation Index

La fragmentation en extents multiples et la fragmentation due à ASSM que j’ai décrit dans la note précédente à propos des tables s’appliquent aussi aux indexes, bien sûr, et nous importe de la même manière, c’est à dire presque jamais. Lorsque les gens parlent de fragmentation d’index, ils pensent en général au problème des blocs avec un faible taux de remplissage (sparsely populated blocks) qui est aussi un phénomène que j’ai décrit à propos de la fragmentation des tables, mais il y a quelques différences entre une table et un index, que nous allons examiner tout de suite.
Il est intéressant de considérer aussi un autre sens possible pour la fragmentation d’un index, que nous allons aussi examiner: c’est l’effet de bord de la division d’un bloc feuille (leaf block splitting) qui fait que des blocs qui sont logiquement à la suite se retrouvent physiquement dispersés.

Nous allons commencer avec une suppression en masse, et étudier les mêmes cas représentatif que nous avons vu à propos des tables (c’est à dire 20% des blocs ayant 100% de leurs ligne supprimées, et 100% des blocs ayant 20% de leur lignes supprimées). Et quand on fait ça, il faut garder à l’esprit que la suppression dans un index est différente de la suppression dans une table, d’où une différence de comportement dans ce qui suit.
Lorsqu’une transaction supprime une ligne d’une table (DELETE) la ligne est réduite à un stub de quelques octets, avant que ne se fasse le commit, et elle peut réutiliser immédiatement l’espace libéré dans le bloc de la table. Mais lorsqu’une transaction supprime une ligne d’un index, il doit laisser en place l’entrée d’index entière, et la marquer comme supprimée. Elle ne peut pas réutiliser l’espace immédiatement, mais cela doit attendre que le commit soit fait.

Une autre différence majeure entre une table et un index est le fait que dans un index chaque entrée a sa place et doit aller au bon endroit. Ce qui fait que lorsqu’un bloc d’index a de l’espace libre, sans être complètement vide, il n’y a que les lignes qui correspondent exactement à cette partie de l’index (à cette plage de valeurs) qui peuvent réutiliser cet espace.

De plus, lorsqu’un bloc feuille devient complètement vide, il reste toujours chaîné dans la même position de la structure de l’index, même s’il est aussi référencé par la freelist. (Mon hypothèse là dessus est qu’il est probablement plus facile de gérer les problèmes de lecture cohérente – read consistency – mais cela peut aussi être lié à des problèmes de rollback et au coût de la modification de 3 pointeurs dans la structure de l’index.) Ce qui veut dire que si on a un index qui a eu une large suppression des valeurs les plus basses, alors une requête qui demande la valeur minimum va devoir faire un range scan d’un grand nombre de blocs vides avant de trouver le bloc feuille qui contient une donnée présente. C’est pour cela qu’il faut toujours penser à faire un ALTER INDEX … COALESCE sur un index lorsqu’on supprime fréquemment les premières entrées. Et de manière plus générique, même si c’est moins courant, lorsque on un supprime un grand nombre de valeurs consécutives n’importe où dans l’index.

Dans le cas plus général d’un suppression en masse, on peut se retrouver avec un espace libre important dans tous les blocs feuilles et, contrairement à l’espace libre des tables, on ne peut pas faire en sorte qu’Oracle le réutilise en choisissant une valeur idéale pour PCTUSED puisque ce paramètre n’a pas de sens pour un index. Donc, dans le cas des index, la question que l’on doit se poser est: à quel point cet espace libre a un impact sur l’application.

Les considérations habituelles s’appliquent ici, bien sûr: un plus gros volume à sauvegarder lors des backup et plus de blocs à garder en buffer cache. Mais nous devons voir si le fait d’avoir un grand nombre de blocs feuilles faiblement remplis n’a pas un impact plus direct et plus significatif sur les performances.
La réponse est dépendante de l’application, bien sûr. Mais en général un index est utilisé pour lister les valeurs clés et les regrouper dans un faible espace. Et en gardant cela à l’esprit, on peut voir que le plus gros du travail de la plupart des requêtes est passé à aller voir les lignes de la table après avoir récupéré un certain nombre de valeurs clés dans l’index. Par conséquent, le travail supplémentaire venant du fait qu’il y a une grand quantité d’espace libre dans les blocs feuilles de l’index n’est qu’une petite fraction du travail total de la requête. Et on peut donc décider de ne pas dépenser des ressources à réorganiser les index sauf s’ils sont vraiment très faiblement remplis. (Un index B-Arbre typique avec une arrivée aléatoire va tourner avec un taux d’utilisation de 70%, donc 30% d’espace libre, dans les blocs feuilles. Je ne suis pas particulièrement inquiet des performances d’un index avant que le taux d’utilisation ne passe en dessous de 50%, sauf si j’ai une preuve que cet index contribue significativement au temps d’exécution d’un ensemble de requêtes critiques.)

Il y a cependant deux autres problèmes de ‘fragmentation’ spécifiques aux index, et qui n’existent pas avec les tables.

Le premier est le fait qu’on ne met pas à jour une entrée d’index: on supprime l’entrée correspondant à l’ancienne valeur, et on ajoute la nouvelle entrée correspondant à la nouvelle valeur. Si ces mises à jours se font de manière aléatoire, alors il n’y a aucun des problèmes associés aux suppressions de masse. Mais si il y a un pattern de modification lié au temps, par exemple si vous avez un index sur une colonne ‘dernière_modification’ alors vous pouvez vous retrouver avec le pire effet d’un index partiellement rempli. Dans un cas comme celui-ci, vous allez supprimer (lentement) des entrées vers le début de l’index pour les insérer tout à la fin. Et l’espace libéré par les suppression ne sera jamais réutilisé puisque les lignes ne peuvent pas êtres modifiées dans le passé. En plus, si vous continuez à modifier des lignes du passé vers le futur, vous continuez à visiter des blocs faiblement remplis. Et si c’est un système transactionnel où les utilisateurs modifient une ou deux lignes à chaque fois, la recherche de l’entrée et la mise à jour dans l’index peut prendre une proportion significative du travail effectué par chaque requête d’update. Vous devez au moins être au courant de ce type d’activité afin de prévoir comment en mesurer l’impact sur les performances et adopter une stratégie pour y faire face.

Le deuxième type de fragmentation propre aux index, pour lequel le terme de fragmentation semble le plus approprié, vient des divisions des blocs feuilles (leaf block split). Si vous voulez ajouter une entrée dans un bloc feuille qui est plein, alors Oracle doit trouver un bloc vide quelque part, y déplacer à peu près la moitié des données du bloc courant, puis lier ce bloc à sa bonne place dans la structure de l’index. Par consequent, les blocs qui sont ‘logiquement’ adjacents ne sont pas nécessairement ‘physiquement’ adjacents. Cela veut dire que lorsque vous faites un index range scan assez large (ou un index full scan) vous vous retrouvez à faire beaucoup de lectures aléatoires de blocs.

C’est ici que SQL Server (et probablement sybase et peut-être DB2) entrent en jeu. La manière dont SQL Server gère l’espace libre pour les tables non clusterisées (heap tables) n’est pas très efficace. Donc c’est presque un article de foi (voire un dogme) que toutes les tables dans SQL Server doivent être construites en index cluster (clustered indexes), ce qui veut dire dans les termes Oracle, que toutes les tables sont des IOT (index organized tables tables organisées en index). Si vous avez essayé de regrouper (cluster) vos données, et y avez réfléchi soigneusement et délibérément, alors les divisions des blocs feuilles (leaf block split) détruisent votre effort de garder ensemble les données liées. Il n’est donc pas surprenant que les DBA qui ont une expérience en SQL Server (et Sybase et DB2) soient si favorables à l’idée de reconstruire les index fréquemment. Si vous reconstruisez un index cluster, vous ramenez les enregistrements là où vous voulez. Heureusement, cela ne nécessite pas de reconstruire tous les autres indexes de la table puisque, comme les index secondaires des IOT d’Oracle, les autres indexes en SQL Server utilisent la clé unique (ou rendue unique) comme identifiant de la ligne.

Pour Oracle, ce type de fragmentation ne pose généralement pas de problème, à condition qu’il concerne des index B-arbre standard, vu que, comme c’est précisé plus haut, la plupart des requêtes passent la plus grande partie de leur temps à visiter la table. Mais le cas de SQL Server donne une indication du cas où vous devrez considérer plus sérieusement les effets de la ‘fragmentation’ et le besoin de reconstruire les index. Si, en tant que DBA Oracle vous avez crée une table en IOT, alors vous aviez probablement une bonne raison de faire ce choix, et il s’agissait probablement de s’assurer que les données qui arrivent dans un certain ordre sont stockées dans un autre ordre, afin de garder ensemble des données qui sont liées.

Si vous avez crée une IOT pour garder les données regroupées, alors les divisions de blocs feuilles vont amener les données à être un peu dispersées. Avant de s’inquiéter, il faut étudier l’importance de cette dispersion, et le bénéfice, peut-être marginal, d’y faire quelque chose. Pour illustrer cela, imaginez que vous ayez une requête sur une table IOT importante qui récupère 200 lignes de 200 octets. Comme sur une table normale cela aurait demandé à lire 200 blocs différents dispersés aléatoirement, vous avez décidé d’implémenter la table en IOT. Si l’on prends le cas le pire pour le divisions de blocs feuilles (50/50 avec aucun re-remplissage) alors les 200 lignes vont aller dans la IOT avec environ 20 lignes par blocs sur un total de 10 blocs feuilles. A cause du moment où chaque division de bloc feuille a lieu, on peut penser que ces 1à blocs vont finir dispersés assez aléatoirement dans tout le segment d’index. Si vous reconstruisez l’index, vous allez pouvoir compacter les données sur seulement 5 blocs, et ces 5 blocs vont souvent être adjacents dans le segment plutôt que dispersés. Et ce regroupement ca probablement faire que vous aurez un petit bénéfice de performance si l’index range scan doit aller sur disque. Note: SQL Server travaille avec des tailles d’extents de 8 blocs de 8 KB et le logiciel de base de donnée peut coopérer avec le système d’exploitation pour négocier une lecture en avance (readahead) de l’extent complet dans ce genre de situation. Cet ensemble de détails font que la réorganisation d’index dans ces circonstances et plus bénéfique sur SQL Server que ce cerait le cas sur Oracle.

Après avoir passé un peu de temps à réfléchir à ce genre de scénario, il est plus facile de comprendre comment évaluer le bénéfice que vous pouvez tirer de la réorganisation d’une IOT. Quelle est la rédution du nombre total de visites de blocs que vous aurez à faire ? Combien de ces visites seraient des I/O disque distincts ? Quel serait le bénéfice du point de vue d’Oracle, du système d’exploitation, des drivers hardware, capable d’implémenter un readahead qui réduit le temps de lecture de ces blocs ? Gardez à l’esprit la remarque importante que j’ai fait dans l’article sur la fragmentation de disque: même si deux blocs aparaissent comme adjacent du point de vue des fichiers Oracle, l’introduction du stripping, et du load balancing peuvent faire que ces deux blocs sont sur des disques différents.

Fin de la série