Pourquoi faut-il faire attention au nombre d'index SQL

Antonio Gomes-Rodrigues

- Titre : Pourquoi faut-il faire attention au nombre d'index SQL

- Auteur : Antonio Gomes-Rodrigues

- Parution : 28 octobre 2015

- Licence : 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.

Sommaire

- I. Introduction

- II. Plan de test

- III. Mise en place de l'environnement

- IV. Mise en place de la partie Benerator

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

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

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

- VI-B. Mise à jour des statistiques

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

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

- VII-B. Exécution de 10 requêtes SQL Select & Exécution de 10 requêtes SQL Update

- VII-C. Création d'index

- VII-D. Mise à jour des statistiques

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

- VIII. Analyse des résultats du test

- VIII-A. Requêtes Select

- VIII-B. Requêtes Update

- VIII-C. Requêtes Create Index

- VIII-D. Requêtes Update Statistiques

- IX. Conclusion

I - Introduction

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.

II - 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 selection 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ête 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 meilleurs décision 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
Au final on aura.

III - Mise en place de l'environnement

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

IV - 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 conséquent 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.

La première étape consiste à ajouter les librairies 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.

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)
);

DROP SEQUENCE seq_hibernate_id_gen;
DROP TABLE Clients;

V - 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 librairies 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.

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

VI - Mise en place du plan de test avec JMeter - Initialisation

VI-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.

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

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

VI-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

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.

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

SQL&gt; 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.

VII - 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.

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

Ajoutons un
Groupe d'unités
.

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.

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.

VII-B - Exécution de 10 requêtes SQL Select & Exécution de 10 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édian, 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.

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'

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

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.

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

La requête utilisée sera.

UPDATE clients SET telephone_mobile = ?, telephone_fixe = ?, telephone_pro = ?, fax = ? WHERE pays = 'Germany'

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

VII-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.

VII-D - Mise à jour des statistiques

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

VII-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é
.

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é.

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.

# 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.

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)

VIII - 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.

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

VIII-A - Requêtes Select

On remarque 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.

VIII-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.

Les résultats pour les requêtes « petit » sont plus mitigés

. On remarque que

dans l'absolue 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é à la Breizhcamp 2012 . Il faut toujours faire attention aux conseils sur les bonnes pratiques de performance, qui dépendent essentiellement du contexte technique.

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...).

VIII-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).

VIII-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.

IX - 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 utiles, voir indispensables dans certain cas.