MySQL Slow Query Log : trouver les requêtes coûteuses

Quand ton serveur de jeu ou ton application web commence à ralentir, le coupable est généralement une poignée de requêtes SQL coûteuses, et les repérer à l'œil nu est impossible. C'est précisément là que le MySQL slow query log prouve son utilité : il enregistre discrètement chaque requête qui dépasse un seuil que tu définis, pour que tu travailles sur des données plutôt que sur des suppositions. Dans cet article, je détaille comment activer le log correctement, choisir les bons réglages et extraire systématiquement tes requêtes les plus coûteuses.

Qu'est-ce que le slow query log au juste ?

Le slow query log est une fonctionnalité de MySQL qui enregistre toute instruction SQL durant plus de long_query_time secondes. « Lent » est relatif : une recherche de personnage à 0,5 seconde sur un serveur Metin2 est une catastrophe, alors que 2 secondes sur un écran de rapport peuvent être acceptables. Chaque ligne stocke le temps de la requête, le temps de verrou, les lignes examinées et les lignes renvoyées. La vraie force réside dans ces métadonnées : très souvent, le problème n'est pas la requête elle-même mais le fait qu'elle parcourt des millions de lignes pour n'en renvoyer que dix.

Activer le log : configuration persistante

L'approche la plus robuste consiste à écrire dans my.cnf (sur Debian/Ubuntu, généralement /etc/mysql/mysql.conf.d/mysqld.cnf). Ajoute ceci sous le bloc [mysqld] :

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

Puis redémarre le service : sudo systemctl restart mysql. Voici le rôle de chaque option :

• long_query_time — le seuil en secondes. Il accepte les décimales, tu peux donc écrire 0.5.
• log_queries_not_using_indexes — journalise toute requête n'utilisant pas d'index, même rapide. Précieux en développement, mais cela peut générer beaucoup de bruit.
• min_examined_row_limit — ignore les requêtes examinant moins de lignes que ce nombre, ce qui évite que les petites tables encombrent le log.

L'activer à chaud, sans redémarrage

Si tu ne peux pas redémarrer un serveur de production, MySQL te permet de modifier la plupart de ces variables à l'exécution. Connecte-toi en root et lance :

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Une réserve : long_query_time est lu par session, donc seules les nouvelles connexions prendront la valeur en compte ; ton pool de connexions existant continue d'utiliser l'ancien seuil. De plus, les changements faits avec SET GLOBAL sont perdus au redémarrage — pour la persistance, il te faut toujours my.cnf.

Extraire les requêtes les plus coûteuses : pt-query-digest

Lire le log brut à la main est un supplice ; la même requête se répète des centaines de fois. L'outil pt-query-digest de Percona Toolkit normalise ces lignes (en remplaçant les valeurs littérales par ?) et regroupe les requêtes identiques sous une seule « empreinte ». Installation sur Debian/Ubuntu :

sudo apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log > rapport.txt

Le rapport classe les requêtes par temps total passé en tête — une requête de 50 ms exécutée 200 fois par seconde compte autant qu'une requête de 5 secondes exécutée une seule fois. Pour chaque groupe, tu obtiens le nombre d'appels, le temps total et moyen, et un exemple de requête. En général, les trois premières requêtes représentent l'essentiel de la charge ; concentre-toi là plutôt que d'optimiser à l'aveugle.

Lire une requête : EXPLAIN et index

Une fois le coupable trouvé, préfixe-le par EXPLAIN pour voir le plan de MySQL :

EXPLAIN SELECT * FROM player WHERE account_id = 4210 ORDER BY level DESC;

Les champs à surveiller dans la sortie :

• type — si tu vois ALL, c'est un balayage complet de table, ce qui est mauvais ; vise ref ou range.
• key — s'il vaut NULL, aucun index n'est utilisé.
• rows — le nombre estimé de lignes que MySQL prévoit de parcourir ; s'il dépasse largement ton résultat, le problème est ici.
• Extra — Using filesort ou Using temporary signale un coût supplémentaire pour le tri ou le regroupement.

Dans l'exemple ci-dessus, ajouter un index sur account_id fait s'effondrer le balayage : CREATE INDEX idx_player_account ON player (account_id);. Pour des colonnes souvent filtrées ensemble, envisage un index composite — mais chaque index inutile alourdit les écritures, alors mesure avant d'ajouter.

Garder le log sous contrôle

Le slow query log grossit avec le temps ; en production, tu dois le faire tourner (rotate). La plupart des distributions fournissent /etc/logrotate.d/mysql-server, et sinon une règle simple suffit. Une fois le diagnostic terminé, désactive log_queries_not_using_indexes ; cette option peut remplir le fichier de requêtes petites mais sans index. Sur les serveurs chargés où l'I/O disque est précieux, n'active le log que pendant l'investigation, et garde long_query_time à un seuil raisonnable (par exemple 1) le reste du temps.

Questions fréquentes

Le slow query log ralentit-il les performances ?

Avec un seuil raisonnable, l'impact est négligeable, car seules les requêtes qui le dépassent sont écrites. Le vrai coût vient avec log_queries_not_using_indexes activé : sur un serveur à fort trafic, cela peut vite gonfler le fichier et les écritures disque. Garde-le désactivé en dehors du diagnostic.

À quelle valeur régler long_query_time ?

Commence à 1 seconde. Si rien n'est capturé, abaisse progressivement (0.5, puis 0.2). Une valeur trop basse remplit le log de bruit ; l'objectif est de voir les requêtes les plus coûteuses, pas toutes les requêtes.

Puis-je journaliser dans une table plutôt qu'un fichier ?

Oui, avec log_output = 'TABLE' les entrées vont dans la table mysql.slow_log et peuvent être interrogées en SQL. Mais la sortie fichier est bien plus pratique avec pt-query-digest, donc un fichier est préférable dans la plupart des cas.

Si ton serveur ralentit et que tu ne sais pas par où commencer, nous pouvons activer le slow query log et interpréter ensemble ton premier rapport pt-query-digest. Pour un audit de performance MySQL et une optimisation des requêtes, contacte-moi.