Objectif :

Regarder les performances d’un serveur mysql sur un serveur donné, en fesant varier quelques paramêtres

Protocole Général :

Script de test

Script php,
Ecriture d’une petite classe qui permet de générer des requettes sql, pour mysql :

• nombre de champ concerné variables
• contenu des champs aléatoire
• champ de type différents (int, char, datetime …)
• non multi-thread, simplement un script.

Server

• Debian 2.6.26
• Xeon X3210 (4X2.13 Ghz)
• Hdd Sata2 500Gb partition ext3
• load average 0.00 – avant les tests 😉
• Mysql 5.0.51a-24+lenny2 (configuration par défaut)

Test : Insert et Index

Protocole du test

Table avec 5 champs :

• un id auto increment
• int
• datetime
• varchar 10
• varchar 100

Précisions du tests :
Itérations 100 000,
Ce n’est pas un bulk insert,
Il n’y a pas d’autre requettes sur la table,
Table en MyISAM.
Résultats : Table initiale vide / Table initiale avec 1Millions d’entrée

Test 1 : only PK

Seulement la pk a un index :
AVG 0.074 ms / 0.074 ms,
Slowest 76 ms / 62 ms
Query/s : 13513 / 13441

Test 2 : full index

3 indexs, un global sur chaque champ, un sur la date, et un fulltext sur le varchar 100 :
AVG 0.624 ms / 2.328 ms
Slowest 2632 ms / 45658 ms
Query/s : 1604 / 429

Test 3 : fulltext

PK + index full text :
AVG 0.082 ms / 1.111 ms
Slowest 120 ms / 1356 ms
Query/s : 12165 / 900

Test 4 : multi index

PK + un index global sur les 4 champs
AVG 0.103 ms / 1.1634 ms
Slowest 273.259 ms / 1330 ms
Query/s : 9704 / 612

Analyse :

Avoir comme index une simple primary key donne d’excellente performance et la taille de la table n’impacte pas

Les indexes impactent vraiment les performances quand la table augmente. 1 000 000 d’enregistrement fait très mal au performance (de 75 à 95% de perte) !

Fulltext est relativement performant et se comporte comme un index normal.

Mettre plusieurs index à un impact très fort quelque soit le nombre d’éléments, soit perte de 88% de performance par rapport au test, avec une simple clef primaire.
Un seul index a un impact plus limité de 10% à 30% seulement.
A tester: Est ce que la perte du a chaque index se cumule ?

La perte de performance du test avec 3 index à tendance à s’atténuer si il y a beaucoup d’éléments. Soit ici une perte de 75% avec 1M rows, contre une perte 95% pour un index normal qui a beaucoup d’éléments.

A faire : Il faudrait que tester la taille idéale des tables a ne pas dépasser pour garder des performance d’insertion convenable.

Conclusion :

N’utilisez des index que si ils sont utiles pour les tables qui subissent des insertions.

Des tables de tailles raisonnable peuvent avoir un index et garde d’excellente performance.

Si vous avez plusieurs index et/ou une table de grande taille, évitez de trop inserrer.

Sur une table avec beaucoup d’enregistrement, ne vous privez pas d’un index qui détruirai vos performance en lecture, ce n’est pas si grave.

N’oubliez pas que en MyISAM l’insert lock toute la table, plus il est bref et moins le lock sera long…

A faire: Comparer avec des bulks insert et trouvez la taille des bulks inserts qui lock le moins la table en fonction du nombre d’index et d’enregistrement dans la table en question (et en fesant varier le parametre « bulk insert buffer size ») !