Le coût des index inutiles – 2nde partie

27 mai 2011 par stephane

Dans l’article précédent, nous nous étions demandés quelle était la dégradation des performances en écriture quand on ajoute des index. On peut élargir la réflexion en se penchant sur les conditions qui améliorent ou diminuent la vitesse d’écriture dans une table.

Avant de commencer de nouvelles expérimentations, rappelons les conditions du test. La table utilisée a la structure suivante :

CREATE TABLE (
  id int(11) NOT NULL AUTO_INCREMENT,
  col_a varchar(30) NOT NULL DEFAULT '',
  col_b varchar(30) NOT NULL DEFAULT '',
  col_c varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);

et nous cherchons à insérer un millions de lignes à l’aide de la commande LOAD DATA INFILE.

Rappelons aussi que pour améliorer les performances en écriture dans l’absolu, il existe d’autres pistes que nous n’explorerons pas dans cet article :

  • si la table utilise InnoDB ou XtraDB, modifier certaines variables de configuration (innodb_log_file_size,innodb_flush_log_at_trx_commit,
    innodb_adaptive_flushing, etc)
  • si la table utilise MyISAM, modifier d’autres variables de configuration (myisam_sort_buffer_size, bulk_insert_buffer_size)
  • faire des insertions multithreadées
  • désactiver les vérifications de clés étrangères ou de clés uniques

Pour commencer nos test, examinons le comportement de MyISAM. Le paramètre clé est la taille du cache d’index (key_buffer_size), nous faisons donc un essai avec un cache d’index suffisamment grand pour pouvoir contenir tous les index et un essai avec un cache trop petit.

MyISAM, key_buffer_size grand :
Seulement la clé primaire : 4s
Clé primaire + index sur col_a : 6,6s
Clé primaire + index sur col_b : 9,3s
Clé primaire + index sur (col_a,col_b) : 6,8s

MyISAM, key_buffer_size petit :
Seulement la clé primaire : 4s
Clé primaire + index sur col_a : 6,8s
Clé primaire + index sur col_b : 9,3s
Clé primaire + index sur (col_a,col_b) : 6,8s

A comparer avec les résultats obtenus la dernière fois avec InnoDB :
Seulement la clé primaire : 15s
Clé primaire + index sur col_a : 28,5s
Clé primaire + index sur col_a + index sur col_b : 44,5s
Clé primaire + index sur (col_a,col_b) : 36,3s

Très intéressant ! MyISAM se révèle plus performant qu’InnoDB, et de loin.

Attention, ne faisons pas de généralités sur la vitesse de MyISAM ! Ce test se déroule sur un serveur hors production qui n’exécute que les LOAD DATA INFILE. Il est certain que l’écart entre InnoDB et MyISAM ne serait pas aussi important si les insertions étaient multithreadées (verrouillage de toute la table pour MyISAM) et si le serveur recevait également des requêtes en lecture (les écritures seraient peut-être rapides mais les lectures souffriraient sans doute).

De plus, on voit que la taille du cache d’index n’influence quasiment pas le temps de chargement, ce qui est plutôt inattendu. Une explication possible serait que les données et index ne soient pas réellement écrits sur le disque, mais restent dans le cache de l’OS. L’hypothèse semble raisonnable puisqu’un coup d’oeil sur iostat pendant les LOAD DATA INFILE montre que les écritures sur le disque sont nettement plus nombreuses quand la table est en InnoDB.

Faisons donc un autre test avec MyISAM, pour lequel le cache d’index est petit et la quantité de mémoire disponible pour l’OS est trop faible pour pouvoir contenir les données et les index :
Seulement la clé primaire : 6,8s
Clé primaire + index sur col_a : 14s
Clé primaire + index sur col_b : 24s
Clé primaire + index sur (col_a,col_b) : 18s

Les performances sont bien moins bonnes qu’avant, mais restent encore supérieures à celles d’InnoDB !

Concentrons nous maintenant sur InnoDB et voyons l’influence du type de clé primaire sur le temps de chargement. Pour simplifier, mettons-nous toujours dans le cas où le buffer_pool est suffisamment grand pour contenir données et index.

Dans un premier temps, optons pour une clé primaire composite (id,col_c) :
Seulement la clé primaire : 17,8s
Clé primaire + index sur col_a : 40s
Clé primaire + index sur col_b : 72s
Clé primaire + index sur (col_a,col_b) : 51,7s

On voit qu’avoir une clé primaire composite a un impact non négligeable lorsqu’il n’existe pas d’autres index (20% de perte de performance) et que cet impact négatif est encore plus important quand on ajoute des index. Ce résultat était attendu, puisqu’avec InnoDB, chaque index secondaire contient la clé primaire : plus la clé primaire est volumineuse, plus les index secondaires sont volumineux et longs à mettre à jour.

Dans un second temps, supprimons la clé primaire :
Seulement la clé primaire : 14,6s
Clé primaire + index sur col_a : 29,2s
Clé primaire + index sur col_b : 45,5s
Clé primaire + index sur (col_a,col_b) : 38,1s

Les temps obtenus sont proches du cas initial où id est la clé primaire. Comment expliquer ce résultat ? InnoDB a besoin d’une clé primaire pour toutes ses tables. Si vous ne la définissez pas vous-même et qu’il n’existe pas d’index unique n’ayant aucune valeur NULL, InnoDB créé une clé primaire cachée sous la forme d’un entier. On se retrouve donc avec une structure du type suivant :

CREATE TABLE (
  hidden_id int NOT NULL AUTO_INCREMENT,
  id int(11) NOT NULL,
  col_a varchar(30) NOT NULL DEFAULT '',
  col_b varchar(30) NOT NULL DEFAULT '',
  col_c varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (hidden_id)
);

On se retrouve avec la même structure qu’initialement, mais avec une colonne de plus, donc avec une volumétrie un peu plus importante, ce qui explique les résultats similaires mais légèrement inférieurs.

Que pouvons-nous conclure de tous ces test ? Tout d’abord, que le moteur de stockage a une grande influence sur les résultats. Si vous avez le choix du moteur, des tests s’imposent pour déterminer lequel est le meilleur, en ayant soin de prendre en compte la charge réelle de l’application (connexions concurrentes, lectures et écritures). Ensuite, que pour InnoDB, le choix de la clé primaire est critique : la clé primaire doit être la plus compacte possible, sous peine de performances très dégradées. Enfin, que pour MyISAM comme pour InnoDB, il vaut mieux limiter le nombre d’index qui ralentissent considérablement toutes les écritures.

Mots-clefs : , ,

4 commentaires sur “Le coût des index inutiles – 2nde partie”

  1. Cédric dit :

    Bonjour Stéphane, merci pour ces précsions.
    Je me demandais, quelle est la version d’InnoDB utilisée ?

    Pour MyISAM, ça ne me surprend pas étant donné qu’il n’y a pas de gestion des transactions (pas de journaux, pas d’undo…)

  2. stephane dit :

    Hello Cédric,
    Le serveur était en 5.1.54 avec InnoDB plugin 1.0.14.

    Je reste quand même surpris de la différence entre InnoDB et MyISAM, il faudra que je fasse un peu plus de recherches. Moi qui pensais pouvoir me débarrasser de MyISAM en convertissant mes tables de log en InnoDB, je vais y réfléchir à 2 fois :)

  3. Julien dit :

    table de logs => pas d’index, donc cet article n’est pas lié à ta recherche !

  4. stephane dit :

    En fait, si, car j’ai des index dans mes tables de log (en tout cas, celle qui pourrait me poser problème) :)

Laisser une réponse