Tutoriel sur le nombre d'index SQL

Une solution simple pour augmenter les performances au niveau base de données est de maîtriser la gestion des index. Vous avez sûrement lu ou déjà expérimenté l'impact de l'ajout d'un index sur les performances d'une requête.

Malheureusement, il faut bien être sensibilisé sur le nombre d'index sur une table, car ils ont un coût, et en particulier pour les opérations UPDATE, DELETE et INSERT INTO.

Afin de démontrer ce coût, nous allons utiliser le « couteau suisse » de JMeter associé à Benerator.

Avant de commencer, je vous conseille de réviser vos cours sur la gestion des index (comment créer un index, comment il marche…). Attention, car chaque moteur de base de données a ses particularités.

Pour réagir à ce tutoriel, un espace de dialogue vous est proposé sur le forum : 9 commentaires Donner une note à l'article (4.5)

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Plan de test

Afin de démontrer le coût d'un index nous allons faire cent itérations des étapes suivantes :

  • mesurer le temps de réponse d'une requête SQL de sélection des données (select) ;
  • mesurer le temps de réponse d'une requête SQL de modification des données (update) ;
  • créer un index ;
  • mettre à jour les statistiques de la table sur laquelle on réalise nos requêtes SQL.

Ces deux types de requêtes SQL (select et update) nous permettront de montrer l'impact du nombre d'index sur les temps de réponse. Pour chaque type de requête, on réalisera deux requêtes SQL qui modifieront/sélectionneront des volumes de données (90 % et 10 %) différents afin de mesurer l'impact du volume de données.

La mise à jour des statistiques nous permettra d'être sûr que le moteur SQL d'Oracle prenne les meilleures décisions concernant nos requêtes SQL.

Implémenter cette stratégie de test avec JMeter se fera de la manière suivante. Notre plan de test sera le suivant.

Initialisation :

  • création des données en base à l'aide de Benerator ;
  • mise à jour des statistiques de la table nouvellement créée.

Run :

  • Boucler tant qu'il y a des index à créer :

    • exécution de 10 requêtes SQL Select ;
    • exécution de 10 requêtes SQL Update ;
    • création d'index ;
    • mise à jour des statistiques.

Finalement, on aura :

Image non disponible

II. Mise en place de l'environnement

Les tests sont réalisés sur Oracle 11g Express Edition installé sur un Windows Vista 32 bits. La version de JMeter utilisée est la 2.7.

III. Mise en place de la partie Benerator

Une fois l'environnement mis en place, il nous faut un jeu de données pour notre test. Jeu de données qui sera créé par Benerator.

Et comme nous l'avons vu dans un précédent article, il vaut mieux avoir un jeu de données volumineux afin de débusquer le maximum de problèmes.

Malheureusement, le serveur de test n'étant pas un monstre de puissance, nous nous limiterons à :

  • une table de 1100 lignes ;
  • 40 requêtes SQL par itération ;
  • la création de 100 index.

Nous allons implémenter ce schéma relationnel avec seulement une table afin de simplifier nos tests.

Image non disponible

La première étape consiste à ajouter les bibliothèques JDBC Oracle dans le répertoire lib de Benerator.

Maintenant, créons le fichier de configuration permettant de générer le jeu de données.

En suivant mon tutoriel sur Benerator, on aura :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CREATE SEQUENCE seq_hibernate_id_gen START WITH 1;
 
CREATE TABLE Clients (
  id_client INT NOT NULL,
  nom VARCHAR(64),
  prenom VARCHAR(64),
  mail VARCHAR(64),
  sex VARCHAR(64),
  salutation VARCHAR(10),
  telephone_mobile VARCHAR(15),
  telephone_fixe VARCHAR(15),
  telephone_pro VARCHAR(15),
  fax VARCHAR(15),
  numero_de_rue VARCHAR(10),
  rue VARCHAR(256),
  code_postal VARCHAR(12),
  ville VARCHAR(64),
  pays VARCHAR(64),
  date_naissance DATE,
  PRIMARY KEY  (id_client)
);
 
Sélectionnez
1.
2.
DROP SEQUENCE seq_hibernate_id_gen;
DROP TABLE Clients;

IV. Mise en place du plan de test avec JMeter - Configuration du pool de connexions Oracle

La prochaine étape est de créer le plan de test avec Jmeter.

Avant de commencer, ajoutons les bibliothèques JDBC Oracle dans le répertoire lib de JMeter.

Afin de pouvoir exécuter les requêtes JDBC, il nous faut un élément Configuration de connexion JDBC.

Image non disponible

C'est ici que l'on va configurer le pool de connexions vers notre base de données Oracle.

V. Mise en place du plan de test avec JMeter - Initialisation

V-A. Création des données en base

L'initialisation du test est réalisée avec l'élément Groupe d'unités de début qui a été introduit dans la version 2.5 de Jmeter.

Pour l'appel de Benerator qui va nous permettre de peupler notre base de données, on utilise l'élément Appel de processus système, une nouveauté de la version 2.7 de Jmeter.

Image non disponible

On n'oublie pas de tester la réponse avec un élément Assertion Réponse.

Image non disponible

Attention cette méthode ne marche pas avec un test en cluster, car chaque injecteur essayera de remplir la base de données.

V-B. Mise à jour des statistiques

Mettons à jour les statistiques de la table fraîchement créée à l'aide de l'élément Requête JDBC

Image non disponible

Afin de s'assurer que la mise à jour s'est bien passée, on va vérifier avec un élément Assertion Réponse qu'il n'y a pas de code d'erreur Oracle (ORA-) dans la réponse.

Image non disponible

Ceux qui veulent avoir la date exacte de mise à jour des statistiques peuvent utiliser cette requête SQL.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
SQL>
SELECT index_name, to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') FROM DBA_INDEXES WHERE TABLE_NAME = 'CLIENTS' AND owner = 'SYSTEM'; 
 
INDEX_NAME                     TO_CHAR(LAST_ANALYZ 
------------------------------ ------------------- 
SYS_C007059                    07/17/2012 21:49:01

Bien sûr il est possible de les intégrer à JMeter avec l'élément Requête JDBC.

Image non disponible

VI. Mise en place du plan de test avec JMeter - Run

La phase d'initialisation du test est prête, regardons d'un peu plus près le test.

VI-A. Boucler tant qu'il y a des index à créer

Ajoutons un Groupe d'unités.

Image non disponible

L'arrêt du test étant paramétré par le nombre d'index à créer, on choisit « infini » comme nombre d'itérations.

Nous allons utiliser un fichier CSV contenant la liste des commandes de création des index. Ce fichier sera utilisé par l'élément Source de données CSV.

Image non disponible

On laisse le paramètre « Arrêter l'unité à la fin de fichier (EOF)? » à true afin que le test s'arrête lorsqu'il n'y a plus d'index à créer.

VI-B. Exécution de dix requêtes SQL Select et exécution de dix requêtes SQL Update

Maintenant, on ajoute une boucle (élément Contrôleur Boucle) de dix itérations pour chaque requête SQL dont on veut mesurer l'impact des index sur le temps de réponse.

Les dix itérations nous permettent :

  • d'avoir des résultats plus précis (moyenne, médiane, percentile…) ;
  • que les données requêtées soient en mémoire et non sur disque afin de comparer toujours la même chose (on élimine les accès disque de nos mesures).

Pour chaque type de requête, on aura deux requêtes (grand et petit) avec un critère de sélection (clause where) différent.

Cela nous permet de voir l'impact du nombre de modifications à faire sur l'index sur les temps de réponse mesurés.Les requêtes de type « grand » correspondent à un select ou un update de quatre-vingt-dix pour cent de notre table (mille lignes sur les mille cent lignes).« Petit » correspond aux dix pour cent restants.

  • Requête SQL : Select

Dans des éléments Requête JDBC, on ajoute la requête suivante :

 
Sélectionnez
1.
SELECT id_client,nom,prenom,mail,sex,salutation,telephone_mobile,telephone_fixe,telephone_pro,fax,numero_de_rue,rue,code_postal,ville,pays,date_naissance FROM clients  WHERE pays = 'Germany'
Image non disponible

Ne pas oublier de tester la réponse de notre requête.

Image non disponible
  • Requête SQL : Update

Pour être sûr que ce soit un update différent à chaque itération, on ajoute un élément Compteur qui va nous créer un numéro unique.

Ce numéro sera utilisé pour les numéros de téléphone et de fax.

Image non disponible

Ajoutons l'élément Requête JDBC.

La requête utilisée sera :

 
Sélectionnez
1.
UPDATE clients SET telephone_mobile = ?, telephone_fixe = ?, telephone_pro = ?, fax = ? WHERE pays = 'Germany'
Image non disponible

Avec l'ajout du contrôle de la réponse SQL, on aura :

Image non disponible

VI-C. Création d'index

Encore une fois, nous utiliserons l'élément Requête JDBC. La requête SQL de création des index sera directement récupérée du fichier CSV défini précédemment.

Image non disponible
Image non disponible

VI-D. Mise à jour des statistiques

Il suffit de faire une copie de celui de la phase d'initialisation.

Image non disponible

VI-E. Récupération des résultats

Nous allons récupérer les résultats de notre test dans un fichier CSV à l'aide de l'élément Rapport agrégé.

Image non disponible

Dans le fichier CSV, il nous manque les informations comme le nombre d'index et la requête SQL de création de l'index.

Pour obtenir le nombre d'index, l'élément Echantillon BeanShell associé à la fonction __counter sera utilisé.

Image non disponible

Pour la requête SQL de création de l'index, elle est déjà dans la variable ${Create_Index_SQL}.

Afin que ces informations soient écrites dans notre fichier CSV, il faut utiliser la propriété sample_variables du fichier properties de JMeter de la manière suivante :

 
Sélectionnez
1.
2.
3.
# Optional list of JMeter variable names whose values are to be saved in the result data files. 
# Use commas to separate the names. For example: 
sample_variables=iteration_number,Create_Index_SQL

Le fichier CSV sera de la forme suivante :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
timeStamp,elapsed,label,responseCode,responseMessage,threadName,dataType,success,bytes,grpThreads,allThreads,Latency,"iteration_number","Create_Index_SQL"
2012/07/29 11:52:22.150,6686,Create_Data,,,Groupe d'unités de début 1-1,text,true,2399,1,1,0,null,null
2012/07/29 11:52:28.729,144,Update_stat,200,OK,Groupe d'unités de début 1-1,text,true,42,1,1,32,null,null
2012/07/29 11:52:28.972,0,Echantillon BeanShell,200,OK,Groupe d'unités 1-1,text,true,1,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:28.972,16,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,13,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:28.989,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:28.992,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:28.994,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:28.997,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:28.999,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.002,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.004,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.007,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.009,2,Requete_Select_Petit,200,OK,Groupe d'unités 1-1,text,true,17898,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.012,15,Requete_Select_Grand,200,OK,Groupe d'unités 1-1,text,true,184357,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.028,16,Requete_Select_Grand,200,OK,Groupe d'unités 1-1,text,true,184357,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.045,16,Requete_Select_Grand,200,OK,Groupe d'unités 1-1,text,true,184357,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.063,16,Requete_Select_Grand,200,OK,Groupe d'unités 1-1,text,true,184357,1,1,0,1,create index ix_clients_0001 on clients(pays)
2012/07/29 11:52:29.080,15,Requete_Select_Grand,200,OK,Groupe d'unités 1-1,text,true,184357,1,1,0,1,create index ix_clients_0001 on clients(pays)

VII. Analyse des résultats du test

Il ne reste plus qu'à charger notre fichier CSV avec l'outil de notre choix.

Attention les résultats obtenus ne seront pas forcement ceux que vous obtiendrez lors de vos tests, car un certain nombre de paramètres (taille de la mémoire, taille des caches…) peuvent modifier le comportement de notre test.

Comme on peut le voir, de manière globale, plus il y a d'index et plus le temps de réponse des requêtes SQL sera grand.

Image non disponible

Regardons d'un peu plus près les temps de réponse par type de requête.

VII-A. Requêtes Select

On remarque que le nombre d'index n'a pas d'influence sur les temps de réponse. Ce qui est normal, car l'index le plus pertinent est créé dès la première itération et c'est celui-là qui sera utilisé tout au long de notre test par le moteur d'Oracle pour exécuter nos requêtes Select.

Image non disponible

VII-B. Requêtes Update

Comme on peut le voir sur notre graphique, les temps de réponse de nos requêtes Update sur mille lignes (sur les mille cent lignes de notre table) augmentent avec le nombre d'index.

Ce qui peut s'expliquer par le fait que chaque index doit aussi être mis à jour lors de l'update de la table.

Image non disponible

Les résultats pour les requêtes « petit » sont plus mitigés.On remarque que dans l'absolu, il vaut mieux avoir un nombre restreint d'index sur notre table pour ne pas dégrader les temps de réponse. Mais l'augmentation de ces temps de réponse n'est pas régulière.

Ce qui assoit encore une fois la devise « Ne devinez pas, mesurez » que j'ai abordée à la Breizhcamp 2012. Il faut toujours faire attention aux conseils sur les bonnes pratiques de performance, qui dépendent essentiellement du contexte technique.

Image non disponible

Une analyse plus approfondie nous permettrait de connaître la cause de ce comportement (activité i/o différente entre chaque itération, gestion des caches différentes…).

VII-C. Requêtes Create Index

Avec ce graphique, on peut conclure que le temps de réponse de la création d'un index dépend de sa complexité.

Par exemple, la création d'un index composite avec beaucoup d'éléments (itération 61) ou d'un index utilisant une fonction (à partir de l'itération 70) prend plus de temps que la création d'un index simple (index créés lors des premières itérations).

Image non disponible

VII-D. Requêtes Update Statistiques

De même que pour les requêtes update, la durée de la mise à jour des statistiques augmente avec le nombre des index.

Image non disponible

VIII. Conclusion

Comme on a pu le voir, la gestion des index SQL n'est pas aussi simple, car l'ajout d'index n'augmente pas toujours les performances alors que leur maintenance a un coût.

Mes conseils sont :

  • d'apprendre le fonctionnement d'un index;
  • d'appliquer la devise « Ne devinez pas, mesurez ».

Ces deux conseils vous permettront de bien choisir vos index, ainsi que leur nombre. Nombre qui pourra être élevé et engendra un impact positif sur les performances comme j'ai pu le voir lors de certaines de mes missions. Mais avant d'augmenter le nombre d'index, il faut bien penser à faire des tests afin de mesurer si le coût de la maintenance des index est inférieur aux gains obtenus.

Dernier conseil, l'aide d'un administrateur de bases de données peut s'avérer utile, voire indispensable dans certain cas.

IX. Remerciements

Nous tenons à remercier Claude Leloup pour sa relecture de cet article et Laethy pour la mise au gabarit.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2015 Antonio Gomes-Rodrigues. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.