Depuis quelques temps, un serveur qui héberge quelques petits sites s’est mis à monter régulièrement en charge, sans augmentation de trafic, ni changements applicatifs. J’ai laissé traîner les choses, ne sachant pas d’où venait le souci.
Il aura fallu cinq minutes de travail et l’utilisation de la commande shell top, de la directive de configuration MySQL log_slow_queries et de la commande SQL EXPLAIN pour régler le problème de lenteur des sites.
Tout d’abord, un top a confirmé mes craintes : c’est bien le serveur MySQL qui était en cause, prenait beaucoup de mémoire RAM et de ressources processeur. Il y avait donc des requêtes SQL qui avaient besoin d’être optimisées.

J’ai édité le fichier de configuration de MySQL (/etc/mysql/my.cnf sous Linux Debian) pour y activer l’option log-slow-queries en modifiant les trois lignes suivantes :

Je le répète, long_query_time = 2 signifie qu’on doit logger toutes les requêtes qui mettent plus de deux secondes à s’exécuter. Après un redémarrage du serveur (cette option ne peut malheureusement pas être changée à chaud), j’ai attendu la montée en charge suivante pour que MySQL (grâce à long_query_time) enregistre sagement toutes les requêtes mettant plus de 2 secondes à s’exécuter (log_query_time est exprimé en secondes et doit être un nombre entier compris entre 1 et 10), et celles qui n’utilisaient aucun index, dans le fichier /var/log/mysql-slow.log

Voici ce que j’y ai trouvé :

Quasiment 20.000 lignes examinées (la totalité de la table) pour une requête qui en ramènera seulement 5, c’est beaucoup.

Je me suis connecté à phpMyAdmin, et j’ai utilisé la commande explain, en rajoutant simplement le mot EXPLAIN devant ma requête SQL :

La commande MySQL EXPLAIN permet de voir, notamment, les clés (index, primary, uniques…) utilisées pour aller chercher les résultats d’une requête, ainsi que le nombre de lignes parcourues.
Voici quel a été le résultat de la commande Explain :

J’ai ajouté un “ID_artiste = 50″ dans la clause WHERE, qui n’a rien changé. Explain renvoyait toujours “NULL” dans les Possible keys concernant la table Chanson. Après vérification, en effet, il n’y avait aucun index sur ce champ de la table, pourtant souvent utilisé dans mes clauses WHERE. Un ALTER TABLE `chanson` ADD INDEX(`artiste_ID`) plus tard, la création de l’index sur le champ de la base de données concernée a permis de changer le résultat de l’EXPLAIN sur la même requête SQL :

Plus que 76 lignes lues pour renvoyer la réponse, la requête n’apparaitra très certainement plus dans le slow_query_log.

En recommençant l’opération (toujours avec Explain) sur toutes les requêtes qui se trouvaient dans le fichier de log, j’ai trouvé plusieurs points à améliorer pour accroître les performances du serveur MySQL. Finis, les problèmes de montée en charge !

Pour en savoir plus :
Documentation officielle MySQL de la syntaxe de EXPLAIN

Ce post vous a été utile ? Re-Twittez le ! ReTwittez ce post

MySQL explain, log_slow_queries, long_query_time, MySQL, optimisation, sql