Un site lent est souvent lié à des requêtes de base de données inefficaces. Le slow query log de MySQL enregistre les requêtes qui dépassent un seuil défini, afin que vous puissiez identifier précisément l’origine du ralentissement. Cet article explique comment activer le log, interpréter son contenu avec des outils comme mysqldumpslow et pt-query-digest, et quelles étapes suivre pour réellement améliorer les requêtes lentes.
Qu’est-ce que le slow query log et quand l’utiliser
Le slow query log est un fichier journal dans lequel MySQL (ou MariaDB) enregistre chaque requête dont la durée dépasse un seuil que vous définissez, appelé long_query_time. Cette fonction est désactivée par défaut, car la journalisation entraîne toujours une légère surcharge. Il est néanmoins très utile de l’activer dès que vous remarquez que les pages se chargent lentement, qu’un CMS comme WordPress ou Magento réagit plus lentement que prévu, ou que votre serveur subit une forte charge CPU alors que le nombre de visiteurs n’a pas augmenté.
Le log indique notamment, pour chaque requête, le temps d’exécution, le nombre de lignes examinées et l’instruction SQL exacte. Vous pouvez ainsi distinguer les pics ponctuels des requêtes structurellement peu performantes, par exemple en raison d’un index manquant ou d’une requête qui parcourt une table entière au lieu d’utiliser un index.
Activer et configurer le slow query log
Sur la plupart des serveurs, vous modifiez la configuration MySQL dans le fichier my.cnf ou via une interface de gestion comme Plesk. Les principaux paramètres sont :
slow_query_log = 1pour activer la journalisationslow_query_log_file = /chemin/vers/slow-query.logpour l’emplacement du fichier journallong_query_time = 1pour journaliser les requêtes de plus d’1 seconde, à adapter selon votre situationlog_queries_not_using_indexes = 1pour également journaliser les requêtes n’utilisant pas d’index, même si elles sont rapides
Après avoir modifié la configuration, redémarrez le service MySQL. Gardez à l’esprit que le log peut grossir rapidement sur un serveur à fort trafic : vérifiez régulièrement sa taille ou configurez une rotation automatique via logrotate.
Lire et analyser le log
Un slow query log brut est difficile à appréhender d’un coup d’œil, surtout en cas de trafic important. Heureusement, certains outils résument le log et mettent en avant les requêtes les plus problématiques.
mysqldumpslow est fourni par défaut et regroupe les requêtes similaires, afin que vous voyiez en un coup d’œil quelle requête revient le plus souvent et dure le plus longtemps en moyenne. Un exemple de commande est mysqldumpslow -s t -t 10 /chemin/vers/slow-query.log, qui affiche les dix groupes de requêtes les plus lents, triés par durée.
pt-query-digest, qui fait partie de Percona Toolkit, offre une analyse plus détaillée, notamment le pourcentage du temps total occupé par un type de requête et des détails sur les lignes examinées par rapport aux lignes retournées. Cet outil est utile si vous souhaitez aller plus loin que ce que propose mysqldumpslow.
Lors de l’analyse, soyez particulièrement attentif aux requêtes présentant un nombre élevé de lignes examinées par rapport au nombre de lignes retournées. C’est souvent le signe d’un index manquant. Des requêtes identiques répétées, provenant par exemple d’un CMS, peuvent également indiquer un plugin ou un module qui interagit de manière inefficace avec la base de données.
Résoudre les requêtes lentes
Une fois identifiées les requêtes problématiques, vient la phase d’optimisation. Cela nécessite souvent une approche sur mesure, mais les étapes ci-dessous constituent un bon point de départ.
Analysez la requête avec EXPLAIN
Placez EXPLAIN devant la requête pour voir quel plan d’exécution MySQL choisit et si un index est utilisé.
Ajoutez les index manquants
Ajoutez des index sur les colonnes fréquemment utilisées dans les clauses WHERE, JOIN et ORDER BY, mais évitez d’indexer à l’excès car cela ralentit les écritures.
Réécrivez les requêtes inefficaces
Évitez SELECT *, limitez les sous-requêtes autant que possible et utilisez LIMIT pour les grands ensembles de résultats.
Vérifiez la maintenance des tables
Exécutez régulièrement OPTIMIZE TABLE sur les tables soumises à de nombreuses mises à jour et suppressions afin de limiter la fragmentation.
Surveillez après modification
Gardez le slow query log actif après une modification pour vérifier que celle-ci a bien eu l’effet escompté.
Au niveau du serveur, il est également utile de disposer d’un stockage rapide et de ressources suffisantes. Sur un serveur équipé de stockage NVMe et de LiteSpeed, vous traitez déjà les requêtes plus rapidement, mais les problèmes structurels de requêtes ne se résolvent que par une bonne indexation et un SQL efficace. Dans Plesk, utilisé notamment pour l’hébergement web de Tandata, vous trouverez des outils de gestion de base de données et pourrez toujours faire appel au support en cas de doute sur l’interprétation d’un log.
Questions fréquentes
Où trouver le fichier slow query log ?
L’emplacement est indiqué dans la configuration MySQL au niveau de slow_query_log_file. Par défaut, il se trouve souvent dans le répertoire de données MySQL, mais sur un hébergement mutualisé l’emplacement peut varier.
Le slow query log affecte-t-il les performances du serveur ?
La journalisation entraîne une légère surcharge, mais avec un seuil raisonnable, cet effet est généralement négligeable par rapport aux avantages d’une meilleure visibilité sur les requêtes lentes.
Quelle est une bonne valeur pour long_query_time ?
Cela dépend de votre application. Pour la plupart des sites, 1 à 2 secondes constituent un bon point de départ, à affiner ensuite selon ce que vous observez dans le log.
Puis-je utiliser le slow query log sur un hébergement mutualisé ?
Cela dépend des droits dont vous disposez sur le serveur. Avec un hébergement géré, le fournisseur peut souvent le consulter ou l’activer pour vous via le panneau de gestion.
Un index résout-il toujours les requêtes lentes ?
Pas toujours. Un index permet de trouver des lignes plus rapidement, mais pour des jointures complexes ou des requêtes mal écrites, réécrire la requête peut s’avérer plus efficace que le simple ajout d’un index.
Conclusion
Le slow query log de MySQL est un outil puissant pour détecter les requêtes lentes avant qu’elles ne ralentissent sensiblement votre site. En l’activant correctement, en l’analysant avec des outils comme mysqldumpslow ou pt-query-digest, puis en ajoutant des index ciblés ou en réécrivant les requêtes, vous améliorez durablement les performances de votre base de données. Combinez cela avec une infrastructure rapide pour un résultat optimal.