Ce petit article est un mémo sur les principaux moteurs de MySQL.
Il existe de nombreux moteurs de stockage pour MySQL, ils ont leurs points forts et leurs points faibles, mais en les combinant ont devrait pouvoir obtenir quelque chose de très efficace.
Les principaux moteurs sont :
MyISAM :
C’est le moteur le plus souvent utilisé car par défaut, adapté aux sites WEB.
Avantages :
- efficace en lecture
- recherche en FULL-TEXT
- les clefs sont mises en cache
- permet de lire et d’écrire en même temps dans une table sans problématique de verrouillage
- très simple à sauvegarder (copie de fichiers)
Inconvénients :
- ne gère pas les clés étrangères
- ne gère pas les transactions
InnoDB :
C’est le moteur le plus complet, adapté aux applications critiques.
Avantages :
- il gère les transactions
- il gère les clefs étrangères
- très configurable
- sauvegardes à chaud
- gère très bien les gros volumes de donnée
- très efficace en écriture
Inconvénients :
- assez consommateur CPU et en mémoire
Memory :
Les données sont stockées en mémoire, adapté pour des hautes performances.
Avantages :
- très rapide (en théorie)
Inconvénients :
- les données sont en mémoire, donc volatiles
- ne peut pas être transformé en un autre type (via alter)
- nécessite beaucoup de mémoire
BlackHole :
C’est un trou noir, il ne stock rien, adapté pour de la réplication en écriture (via les logs binaires).
Avantages :
- les mêmes que /dev/null
Inconvénients :
- mal documenté
Archives :
Idéal pour les logs
Avantages :
- les données sont compressées
- très rapide en écriture
Inconvénients :
- la suppression de données n’est pas possible directement
- ne supporte pas les index
Voici pour les moteurs dont je vois immédiatement un intérêt pour mes usages, je laisse pour le moment le moteur NDB (moteur utilisé pour le mode cluster) de coté.
Tests
J’ai fait une petite série de tests, l’injection d’1 million de lignes sur une petite VM.
J’ai gardé la configuration par défaut de Debian, exception faite de max_heap_table_size=1677721600 (pour le moteur memory).
Le test avec BlackHole sert de référence.
Première série
Avec ID (INT)/Nom(VARCHAR)/Prenom(VARCHAR) sans index
- BlackHole : 9.2sec/0Mo
- MyISAM : 10.3sec/264.2Mo
- InnoDB : 17.2sec/204Mo
- Memory : 10.8sec/269.2Mo (en ram)
- Archives : 11.9sec/5.4Mo
Seconde série
Avec ID (INT)/Nom(VARCHAR)/Prenom(VARCHAR) avec un index sur ID
- BlackHole : 9.2sec/0Mo
- MyISAM : 13.1sec/275.2Mo (dont 9.8Mo d’index)
- InnoDB : 17.3sec/205Mo
- Memory : 12.1sec/270Mo (en ram)
- Archives : 11.9sec/5.4Mo (il n’y a pas de notion d’index)
Mon test présente un gros goulot d’étranglement (lié à la VM) qui limite les moteurs Memory et BlackHole, je ne les ai laissé que pour trace.
En complément et à titre de comparaison sur la volumétrie avec des données réelles, j’ai repris une table d’un peu plus de 20 millions de lignes :
- InnoDB : 1Go
- Archives : 80Mo