Ecommerce DEV - Performance des moteurs de stockage MYSQL

avr 12 2006

Performance des moteurs de stockage MYSQL

Programmation - Base de données
Ecrit par Kamal AOUDA
12-04-2006
Performance des moteurs de stockage MYSQL Dans cet article nous présenterons rapidement l’architecture de MySQL en polarisant l’attention sur les moteurs de stockage et leur impact sur les performances. Figure 58: Architecture du serveur MySQL [230] * Dans la figure 58, la couche supérieure offre des services relatifs à la gestion des connexions, de l’authentification et de la sécurité. * La couche intermédiaire contient les fonctionnalités communes du SGBDR (logique permettant le parsing des instructions SQL, procédures stockées, cache, utilitaires d’optimisation…). * La troisième couche contient les moteurs de stockage qui permettent de sauvegarder les données et de les rendre persistantes. C’est cette couche qui nous intéresse dans cet article. * L’interface entre la deuxième et la troisième couche est une API indépendante du moteur de stockage utilisé. Elle se compose d’une vingtaine d’instructions de bas niveau qui permettent d’agir directement sur le contenu des moteurs de stockage (exemple récupérer d’une table l’enregistrement qui a une clé primaire donnée, commencer une transaction…). Les moteurs de stockage ne communiquent pas les un avec les autres et ne traitent pas les instructions SQL. Ils se contentent de répondre aux appels qu’ils reçoivent de la couche intermédiaire. Comme le montre la figure 58, MySQL contient plusieurs moteurs de stockage qui peuvent être transactionnels ou non transactionnels. * MyISAM est le moteur par défaut de MySQL. Il s’agit d’un moteur non transactionnel qui a été initialement conçu avec l’hypothèse que les applications font majoritairement appel à des opérations de lecture. * Le moteur HEAP (appelé aussi MEMORY) propose des tables stockées en mémoire. Le moteur MERGE, qui n’apparaît pas sur la figure 58, a été ajouté dans la version 3.23.25. Il permet de fusionner des tables MyISAM identiques dans une seule table. A l’instar de MyISAM ces deux moteurs sont également non transactionnels. * Le moteur InnoDB gère des tables transactionnelles. Il a été introduit dans la version 3.23. * Le moteur NDB clustered gère les tables réparties sur plusieurs serveurs. Il a été introduit dans la version 4.1.2. * Les tables BDB, qui n’apparaissent pas sur la figure 58, sont stockées sur le disque en deux fichiers. Le premier, qui a l’extension .frm, stocke la définition de la table. Le deuxième a l’extension .db et contient les données et les index. Lors de la création d’une table, il faut indiquer à MySQL quel moteur de stockage utiliser. Cela se fait par le biais des instructions ENGINE ou TYPE comme le montre l’exemple suivant: CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY; Lorsque le moteur de stockage n’est pas précisé ou n’est pas activé, c’est le type par défaut qui est utilisé (MyISAM). Pour changer le type d’une table déjà créée, il faut utiliser la commande ALTER TABLE, comme le montre l’exemple suivant : ALTER TABLE t ENGINE = MYISAM; ALTER TABLE t TYPE = BDB; Il est important de rappeler que les tables non transactionnelles sont plus rapides à traiter que les tables transactionnelles. De plus elles utilisent moins d’espace disque et moins de mémoire pour les mises à jour. Notons par ailleurs que : * Les tables MyISAM sont optimisées pour supporter des mixes composés de 90% d’opérations de lecture. Elles donnent de bonnes performances tant que le ratio read to write reste en deçà de ce pourcentage . * Les tables MERGE utilisent massivement les pointeurs de fichiers. Par exemple une table MERGE qui couvre 10 autres tables et qui est utilisée par 10 personnes consomme 1010 + 10 pointeurs de fichiers (10 fichiers de données et 10 utilisateurs avec 10 fichiers d'index). Les opérations de lecture sont très lentes avec les tables de type MERGE (pour lire une clé donnée le gestionnaire MERGE doit parcourir tous les fichiers d'index des tables sous-jacentes pour vérifier lequel est le plus proche de la clé recherchée). * Les tables HEAP sont conservées en mémoire. Elles sont très rapides mais elles ne persistent pas en cas de plantage ou de redémarrage du serveur. * Avec un index hash sur une table HEAP et un haut degré de duplication les suppressions sont plus lentes. Le facteur de ralentissement est proportionnel au degré de duplication (ou inversement proportionnel à la cardinalité). Notons cependant que depuis la version 4.1, MySQL supporte les indexes BTREE qui permettent de remédier à cette lenteur. __________________________________________ [230] Source: Jeremy Zawodny, Derek J. Balling, High Performance MySQL, O'Reilly, April 2004, ISBN: 0-596-00306-4