Performances comparées des moteurs MyISAM, Aria, InnoDB
1 avril 2018
Les tests ont été exécutés sur un serveur Linux Mageia 6 disposant de 4 Go et de MariaDB 10.1, s’exécutant dans VirtualBox 5.1.28 sous Windows Server 2016. J’ai utilisé pour ce test la base d’exemples test_db, installée trois fois dans trois schémas différents et motorisée respectivement en Aria, MyISAM et InnoDB. La motorisation InnoDB est la seule à bénéficier de l’usage des contraintes d’intégrité référentielle (FK). En revanche, les 3 bases disposent d’index sur le champ empno de salaries qui sert à la mise en relation avec la table employees. La 2e série de mesures a été faite le 1 avril 2018.
Configuration générale
J’ai affecté aux trois motorisations 768 Mo pour la mise en cache des blocs d’index, en ajoutant les directives suivantes au fichier /etc/my.cnf :
aria_pagecache_buffer_size = 768M key_buffer_size = 768M innodb_log_buffer_size = 768M
Concernant le Query Cache, je l’ai configuré comme suit dans le fichier /etc/my.cnf :
query_cache_size = 768M query_cache_type = 1 query_cache_limit = 768M query_cache_min_res_unit=0
Activation de performance_schema
Afin de disposer des temps d’exécution des requêtes, il m’a fallu disposer des indicateurs retournés par l’activation du module performance_schema en ajoutant les lignes suivantes au fichier /etc/my.cnf :
performance_schema=ON performance-schema-instrument='stage/%=ON' performance-schema-consumer-events-stages-current=ON performance-schema-consumer-events-stages-history=ON performance-schema-consumer-events-stages-history-long=ON
Temps d’exécution des requêtes
J’ai utilisé la requête suivante pour obtenir le temps moyen d’exécution de 5 requêtes consécutives :
SELECT digest_text, avg_timer_wait/1000000000000 FROM performance_schema.events_statements_summary_by_digest WHERE digest_text LIKE '%employees_%';
| Query Cache |
Requête | MyISAM | Aria | InnoDB |
| Non | SELECT emp_no, salary FROM salaries |
1,5328s | 2,1589s | 2,8021s |
| Oui | SELECT emp_no, salary FROM salaries |
1,4985s | 1,9815s | 2,6115s |
| Non | SELECT e.first_name, e.last_name, s.salary FROM employees AS e INNER JOIN salaries AS s ON e.emp_no=s.emp_no; |
18,9258s 14,0603s |
20,5226s 15,0092s |
5,6875s 3,3870s |
| Oui |
SELECT e.first_name, e.last_name, s.salary |
13,8510s 13,5478s |
20,0623s 15,4437s |
5,1007s 3,6155s |
| Non |
SELECT e.first_name, e.last_name, s.salary |
3,0692s | ||
| Oui | SELECT e.first_name, e.last_name, s.salary FROM employees AS e INNER JOIN salaries AS s ON e.emp_no=s.emp_no; (sans Foreign Key) |
4.7412s |
Enseignements
La motorisation la plus lente est Aria. Elle a toutefois l’avantage, par rapport à MyISAM, de permettre la récupération complète des données en cas de crash . Pour le verrouillage sur ligne, il faudra attendre Aria 2.0.
MyISAM – et c’était prévisible – est plus rapide sur la lecture d’une seule table. InnoDB est le plus performant dès lors qu’il y a des jointures à réaliser. En environnement professionnel, il est rare d’utiliser des requêtes sans jointure ! Sur un hébergement constitué de sites WordPress, j’ai, au doigt mouillé, divisé par 4 environ le temps global cumulé d’exécution des requêtes.
SELECT SUM(sum_timer_wait)/1000000000000 AS secondes FROM performance_schema.events_statements_summary_by_digest UNION SELECT SUM(sum_timer_wait)/1000000000000 AS secondes FROM performance_schema.events_statements_summary_by_digest WHERE digest_text LIKE '%SELECT%' UNION SELECT SUM(sum_timer_wait)/1000000000000 AS secondes FROM performance_schema.events_statements_summary_by_digest WHERE digest_text LIKE '%SELECT% JOIN %';