Performances comparées des moteurs MyISAM, Aria, InnoDB

Performances comparées des moteurs MyISAM, Aria, InnoDBLes 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
FROM employees AS e
INNER JOIN salaries AS s  
ON e.emp_no=s.emp_no;

13,8510s
13,5478s
20,0623s
15,4437s
5,1007s
3,6155s
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;
(sans Foreign Key)

    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 %';
 

Base de données / , , , , ,