Databases, Systems & Networks

L’optimisation au niveau de MySQL passe par trois composants, à savoir :

• Optimisation du serveur MySQL
• Optimisation de la base de données
• Optimisation des requêtes

Optimisation de la configuration du serveur MySQL

L’optimisation du serveur peut inclure une multitude d’approches et de méthodes, ce que nous essayons de présenter dans ce qui suit est une introduction relative aux approches de base, à savoir :

• Compilation du serveur
• Tuning des paramètres du serveur
• Tuning d’autres paramètres

Pour faire une bonne optimisation, il faut procéder par une méthodologie empirique à savoir faire les modifications une par une et tester à chaque fois la réaction du système pour avoir une idée sur le rendu.
Une mesure de performance avant et après avoir effectué l’optimisation permet de voir si le système a été optimisé ou pas.

Compilation du serveur

Il est préconisé d’utiliser la version code source du serveur MySQL et de la compiler en prenant en considération les différents paramètres du système à savoir le jeu de caractère à utiliser, le micro-processeur sur lequel il va tourner et d’utiliser un compilateur adapté (ex : pgcc pour les micro-processeurs Pentium).

Tuning des paramètres du serveur

Il est possible d’optimiser le fonctionnement de MySQL en changeant les valeurs des paramètres du serveur.
A titre de rappel pour afficher les paramètres il faut utiliser la commande :

show variables;

Pour voir l’effet des paramètres sur le serveur il faut lancer la commande :

show status;

Il existe de nombreux outils de monitoring permettant de voir les répercutions des changements effectués sur les paramètres sur le serveur MySQL tél que Mytop équivalent à la commande top de linux.
Le fichier my.cnf contient l’ensemble des paramètres à optimiser.
Dans une première étape, il est possible de commencer avec les paramètres gérant la mémoire. Le principe à suivre est que plus le serveur bénéficie de mémoire plus il est rapide, néanmoins, il faut s’assurer que la mémoire est disponible
MySQL contient un ensemble de buffers et cache internes, qu’il est possible de paramétrer l’espace alloué à chacun à partir des variables du fichier my.cnf. Les deux variables les plus importantes sont key_buffer_size et table_cache car elles sont partagées par tous les threads qui tournent sur le serveur et influence de façon considérable les performances.
Un exemple de variables :

• key_buffer_size: mémoire utilisé pour la sauvegardes de indexes MyISAM.
• table_cache: Nombre de tables ouvrables simultanément.
• read_buffer_size: mémoire utilisée pour la sauvegarde des données issues des full scans de tables.
• sort_buffer: mémoire utilisée pour la sauvegarde des données de tables qui seront triées par un ORDER BY.

Tuning d’autres paramètres

Le serveur MySQL est préconisé pour un fonctionnement optimal sur SOLARIS, néanmoins, il est possible de l’optimiser sur les autres OS pour se rapprocher de son rendement idéal.
L’utilisation de RAID-RAID 0 est préconisée pour l’optimisation des opérations de lecture écriture. Ainsi que l’utilisation des disques SCSI plutôt que les IDE.
L’utilisation de réseaux rapides optimise les temps de réponses et optimise la communication entre client/serveur et maître/esclave pour la réplication.

Optimisation de la base de données

Généralement pour l’optimisation des bases de données nous nous tournons vers les bonnes pratiques et les méthodologies de conception de base de données qui permettent de mettre en place des schémas de bases de données performants et normalisés. Néanmoins il faut pour cela :

• Comprendre ce qui est lent au niveau des bases de données
• Faire le bon choix de méthodologie et faire les bons choix conceptuels
• Utiliser les indexes
• Utiliser OPTIMIZE TABLE

Qu’est ce qui ralenti la base de données

Un certain nombre de facteurs sont généralement la cause de la lenteur des bases de données. Nous citons dans ce qui suit les plus fréquents à savoir :

• Pas assez d’indexes : La première cause de lenteur est l’utilisation de tables sans indexes ou sans indexes sur les colonnes concerné par les recherches. Cela ne veux pas dire que toutes les tables doivent avoir des indexes, mais qu’il faut bien étudier les besoins en indexation.
• Utilisation excessive d’indexes : pour optimiser les consultations et recherches les indexes sont la solution, néanmoins, l’augmentation du nombre d’indexes influence les performances au niveau des mises à jour, en effet, lors de la mise à jour d’une table les opérations d’ajout, modification et suppression se répercutent généralement sur les indexes.
• Utilisation privilèges au niveau des tables et des colonnes des tables : à chaque accès MySQL doit vérifier les droits sur les tables et les colonnes des tables ce qui ralenti considérablement les performances.
• Ne pas faire les bons choix au niveau de la conception de la base de données.

Modélisation de la base de données

Utiliser les bonnes pratiques de modélisation et de conception de base de données ainsi que le choix de la méthodologie appropriée permet de mettre en place des bases de données performantes.
Un certain nombre de considérations sont à prendre tel que :

• Choix approprié des types des champs : toujours essayer de choisir les variables les plus adaptées au besoin (exemple pour le stockage d’un nombre ne dépassant pas les 10 chiffres, il est plus judicieux d’utiliser un type TINYINT). Utiliser les champs les plus petits possible permet de charger en mémoire plus de colonnes.
• Utilisation champs à longueur fixe : l’utilisation de longueurs prédéterminée permet d’optimiser l’accès aux colonnes car leurs positions sont prédéfinies. Ceci implique diminuer l’utilisation de VARCHAR, de TEXT et de BLOB (pour TEXT et BLOB, il est préconisé de casser la normalisation du schéma de la base de données et de sauvegarder ces champs dans des tables tierces).
• Augmenter l’utilisation des contraintes NOT NULL quand ce la est possible pour optimiser l’espace de stockage.
• Choisir le bon type pour les tables : MySQL permet d’avoir au niveau d’un même schéma des tables de types différents.
• Faire un bon indexage des tables.

Utiliser les indexes

Un indexe est une table de recherche qui nous permet de trouver rapidement des lignes dans une table. L’indexe permet de détermine la position de l’enregistrement recherché au niveau d’une table.
Si une table n’a pas d’indexe, lors de la recherche tous les enregistrements seront parcourus.
Les indexes au niveau de MySQL sont stockés sous forme de b-trees (arbres binaires), qui représente une structure de données facile et rapide à parcourir.
L’indexe peut inclure une simple ou plusieurs colonnes, l’index sera appelé lors d’une recherche portant sur les colonnes indexées.
Au niveau de MySQL, l’indexation est automatique au niveau des tables ayant des champs avec les contraintes PRIMARY, KEY, UNIQUE.
L’idée de base à retenir est que si une recherche est fréquente et qu’elle incluse une ou plusieurs colonnes, il faut créer l’indexe correspondant pour optimiser le temps de réponse via la commande CREATE INDEX.

Utilisation de la commande OPTIMIZE TABLE

Equivalente à la défragmentation de disque, la commande OPTIMIZE TABLE permet de fragmenter les tables.

Optimisation des requêtes

MySQL permet d’analyser les requêtes et de connaître le temps et le plan d’exécution. Ces information permettent comprendre ce qui rends les requêtes lentes et d’en optimiser l’exécution.

Détecter les requêtes lentes

Pour détecter les requêtes lentes il est possible :

• soit d’observer des lenteurs lors des exécutions et des temps de réponse anormaux
• soit de faire des benchmark : tester les applications pour voir quelles composantes sont les plus lentes.
• soit vérifier le Slow query log : il est possible d’activer cette option au niveau de MySQL paramétrant la variable –log-slow-queries

Une fois que les requêtes lentes détectées, lancer la commande EXPLAIN permet de comprendre l’exécution et donc connaître ou intervenir pour optimiser.