Les requêtes lentes sont les tueuses silencieuses des performances de bases de données. Une seule requête non optimisée exécutée des milliers de fois par jour peut mettre à genoux un serveur MySQL autrement sain. Le slow query log de MySQL est votre première ligne de défense — il capture chaque requête dépassant un seuil de temps, vous fournissant les données brutes nécessaires pour identifier et éliminer les goulots d’étranglement. Combiné avec des outils d’analyse comme mysqldumpslow et pt-query-digest, vous pouvez systématiquement trouver, prioriser et corriger les pires requêtes de votre charge de travail.
Prérequis
- MySQL 5.7+ ou MySQL 8.0 (les commandes fonctionnent sur les deux ; différences mineures notées)
- Accès root ou administratif au serveur MySQL
- Accès shell au serveur exécutant MySQL
- Percona Toolkit installé (pour
pt-query-digest) - Compréhension basique du SQL et des fichiers de configuration MySQL
Activation du Slow Query Log
Le slow query log est désactivé par défaut dans la plupart des installations MySQL. Vous pouvez l’activer de façon permanente via la configuration ou temporairement en temps d’exécution.
Configuration Permanente (my.cnf)
Ajoutez ces lignes à votre fichier de configuration MySQL, généralement /etc/mysql/my.cnf ou /etc/my.cnf :
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_output = FILERedémarrez MySQL pour appliquer :
sudo systemctl restart mysqlConfiguration en Temps Réel (Sans Redémarrage)
Activez le slow query log sans redémarrer le serveur :
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';Vérifiez les paramètres :
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';Sortie attendue :
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+-------------------------------+Options de log_output
MySQL supporte trois destinations de sortie :
- FILE — écrit dans le fichier spécifié par
slow_query_log_file(par défaut, recommandé) - TABLE — écrit dans la table
mysql.slow_log(interrogeable mais ajoute du overhead) - NONE — désactive la journalisation malgré
slow_query_log=1
Utilisez FILE en production. L’option TABLE ajoute un overhead d’écriture mesurable et fait croître le schéma mysql de manière imprévisible.
Configuration des Paramètres du Log
long_query_time
Ce seuil détermine quelles requêtes sont enregistrées. La valeur par défaut de 10 secondes est bien trop élevée pour la plupart des charges de travail.
-- Enregistrer les requêtes de plus de 0.5 secondes
SET GLOBAL long_query_time = 0.5;
-- Enregistrer TOUTES les requêtes (utile pour de courtes audits, pas en production)
SET GLOBAL long_query_time = 0;Valeurs de départ recommandées :
| Environnement | long_query_time | Justification |
|---|---|---|
| Développement | 0 | Capture tout pour analyse |
| Staging | 0.1 | Détecte les requêtes >100ms |
| Production | 1.0 | Focus sur les requêtes clairement lentes |
| Agressif | 0.5 | Équilibre entre bruit et couverture |
log_queries_not_using_indexes
Ce paramètre enregistre les requêtes effectuant des scans complets de table, indépendamment du temps d’exécution :
SET GLOBAL log_queries_not_using_indexes = 1;C’est extrêmement précieux pour détecter les requêtes qui sont rapides maintenant mais qui se dégraderont au fur et à mesure que les tables grossiront. Cependant, cela peut générer beaucoup d’entrées de log sur des bases de données avec de nombreuses petites tables.
min_examined_row_limit
Filtrez les requêtes triviales en exigeant un nombre minimum de lignes examinées :
SET GLOBAL min_examined_row_limit = 1000;Cela empêche le log de se remplir avec des recherches rapides sur une seule ligne qui n’utilisent pas d’index.
Analyse avec mysqldumpslow
mysqldumpslow est livré avec MySQL et ne nécessite aucune installation supplémentaire. Il parse le slow query log, abstrait les valeurs littérales et regroupe les requêtes similaires.
Utilisation de Base
# Top 10 requêtes par temps total
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Top 10 requêtes par fréquence
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# Top 10 requêtes par temps moyen
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
# Filtrer les requêtes correspondant à un motif
mysqldumpslow -s t -t 10 -g "orders" /var/log/mysql/slow.logOptions de Tri
| Flag | Trie par |
|---|---|
-s t | Temps total |
-s c | Fréquence (nombre) |
-s at | Temps moyen |
-s l | Temps de verrouillage |
-s al | Temps moyen de verrouillage |
-s r | Lignes examinées |
-s ar | Moyenne de lignes examinées |
Exemple de Sortie
Count: 1523 Time=2.45s (3731s) Lock=0.00s (1s) Rows=245.3 (373534), root[root]@localhost
SELECT * FROM orders WHERE customer_id = N AND status = 'S' ORDER BY created_at DESC LIMIT N;Cela indique que la requête a été exécutée 1 523 fois, avec une moyenne de 2,45 secondes chacune, examinant 245 lignes par exécution et consommant 3 731 secondes au total.
Limitations
mysqldumpslow est utile pour des vérifications rapides mais présente des limitations significatives :
- Pas d’analyse de percentiles (P95, P99)
- Pas de distribution des temps de réponse
- Ne peut pas lire les logs au format binaire ou TABLE
- Options limitées de filtrage et de reporting
Analyse Approfondie avec pt-query-digest
pt-query-digest du Percona Toolkit est le standard de l’industrie pour l’analyse des slow query logs. Il fournit des informations bien plus détaillées que mysqldumpslow.
Installation
# Debian/Ubuntu
sudo apt-get install percona-toolkit
# RHEL/CentOS
sudo yum install percona-toolkit
# Depuis les sources
wget https://www.percona.com/downloads/percona-toolkit/LATEST/tarball/percona-toolkit-LATEST.tar.gz
tar xzf percona-toolkit-LATEST.tar.gz
cd percona-toolkit-* && perl Makefile.PL && make && sudo make installUtilisation de Base
# Rapport complet
pt-query-digest /var/log/mysql/slow.log
# Rapport des dernières 24 heures
pt-query-digest --since '24h' /var/log/mysql/slow.log
# Filtrer par base de données
pt-query-digest --filter '$event->{db} eq "myapp"' /var/log/mysql/slow.log
# Sortie vers un fichier
pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txtStructure du Rapport
La sortie comprend trois sections :
1. Résumé Général
# 2.1M QPS, 1.2x concurrency, 45% of time in query
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3731s 100ms 45s 2.45s 4.12s 1.33s 1.85s
# Lock time 1s 0 15ms 10us 22us 38us 8us
# Rows sent 373534 0 1000 245 480.0 180.3 198.0
# Rows examine 2.1M 0 50000 1382 3200 2100 8502. Profil (Classement des Requêtes)
# Rank Query ID Response time Calls R/Call Item
# ==== ================================ ============== ====== ======= ====
# 1 0xE77769C62EF669AA1A6... 1800.0 48.2% 1523 1.1819 SELECT orders
# 2 0xA4B9D8C1F3E5A7B2C9... 950.3 25.5% 892 1.0654 SELECT products
# 3 0xF1C2D3E4A5B6C7D8E9... 480.1 12.9% 3201 0.1500 SELECT users3. Détail par Requête — pour chaque requête classée, vous obtenez l’empreinte complète, la distribution des temps, les recommandations EXPLAIN et un exemple de requête avec valeurs littérales.
mysqldumpslow vs pt-query-digest vs MySQL Enterprise Monitor vs PMM
| Fonctionnalité | mysqldumpslow | pt-query-digest | MySQL Enterprise Monitor | Percona PMM |
|---|---|---|---|---|
| Coût | Gratuit (inclus) | Gratuit (open source) | Licence commerciale | Gratuit (open source) |
| Installation | Aucune | Percona Toolkit | Serveur dédié | Docker/VM |
| Monitoring en temps réel | Non | Non | Oui | Oui |
| Analyse historique | Basique | Excellente | Excellente | Excellente |
| Fingerprinting | Basique | Avancé | Avancé | Avancé |
| Analyse percentiles | Non | Oui (P95, P99) | Oui | Oui |
| Dashboard visuel | Non | Non | Oui | Oui (Grafana) |
| Intégration EXPLAIN | Non | Partielle | Oui | Oui |
| Alertes | Non | Non | Oui | Oui |
| Idéal pour | Vérifications rapides | Analyse approfondie | Équipes enterprise | Équipes voulant une UI gratuite |
Optimisation des Requêtes Lentes
Une fois les requêtes les plus lentes identifiées, suivez cette approche systématique.
Étape 1 : EXPLAIN de la Requête
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;Colonnes clés à examiner :
- type : Doit être
ref,rangeouconst. ÉvitezALL(scan complet de table) - key : Quel index MySQL a choisi.
NULLsignifie qu’aucun index n’a été utilisé - rows : Lignes estimées examinées. Des chiffres élevés indiquent des index manquants
- Extra : Surveillez
Using filesortetUsing temporary
Avec MySQL 8.0, utilisez EXPLAIN ANALYZE pour les statistiques réelles d’exécution :
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;Étape 2 : Ajouter les Index Manquants
À partir de la sortie EXPLAIN, créez des index composites couvrant les clauses WHERE et ORDER BY :
-- Couvre WHERE customer_id AND status, ORDER BY created_at
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);Règles de conception d’index :
- Les colonnes d’égalité en premier (
customer_id,status) - Les colonnes de plage/tri en dernier (
created_at) - Incluez les colonnes du SELECT pour un covering index
- Évitez la sur-indexation — chaque index ralentit les écritures
Étape 3 : Réécrire les Patterns Problématiques
Réécritures courantes améliorant les performances :
-- MAUVAIS : Sous-requête exécutée par ligne
SELECT * FROM orders WHERE customer_id IN (
SELECT id FROM customers WHERE region = 'US'
);
-- BON : JOIN s'exécute une fois
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'US';
-- MAUVAIS : SELECT * récupère des colonnes inutilisées
SELECT * FROM orders WHERE id = 42;
-- BON : Sélectionne uniquement les colonnes nécessaires
SELECT id, status, total, created_at FROM orders WHERE id = 42;
-- MAUVAIS : Fonction sur colonne indexée empêche l'utilisation de l'index
SELECT * FROM orders WHERE YEAR(created_at) = 2025;
-- BON : Scan par plage utilise l'index
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';Scénario Concret
Vous gérez la base de données d’une plateforme e-commerce. Les utilisateurs signalent que les pages produits chargent lentement pendant les heures de pointe (14h-18h), mais le reste du site fonctionne bien. Les métriques serveur montrent des pics CPU coïncidant avec les plaintes, mais la mémoire et les I/O disque sont normaux.
Étape 1 : Activez le slow query log avec long_query_time=0.5 pendant les heures de pointe.
Étape 2 : Après 2 heures de journalisation, exécutez pt-query-digest :
pt-query-digest --since '2h' /var/log/mysql/slow.logÉtape 3 : Le rapport révèle que la requête principale consomme 62% du temps total :
SELECT p.*, c.name as category_name,
(SELECT AVG(rating) FROM reviews WHERE product_id = p.id) AS avg_rating,
(SELECT COUNT(*) FROM reviews WHERE product_id = p.id) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY created_at DESC LIMIT 20;Étape 4 : EXPLAIN montre que les sous-requêtes corrélées s’exécutent une fois par ligne de produit (2 400 fois par chargement de page). Corrigez en réécrivant avec un JOIN :
SELECT p.*, c.name as category_name,
COALESCE(r.avg_rating, 0) AS avg_rating,
COALESCE(r.review_count, 0) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
SELECT product_id, AVG(rating) as avg_rating, COUNT(*) as review_count
FROM reviews GROUP BY product_id
) r ON r.product_id = p.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY p.created_at DESC LIMIT 20;Étape 5 : Ajoutez un index composite :
CREATE INDEX idx_products_category_active_created
ON products (category_id, active, created_at DESC);Résultat : Le temps de requête passe de 2,4 secondes à 12 millisecondes. Le chargement des pages revient à la normale.
Pièges et Cas Particuliers
- La rotation des logs est essentielle. Sans rotation, le slow log peut consommer tout l’espace disque disponible. Utilisez
logrotateou la commandeFLUSH SLOW LOGSde MySQL avec un cron job. - Surveillance de l’espace disque. Configurer
long_query_time=0sur un serveur chargé peut générer des gigaoctets de données de log par heure. Surveillez toujours l’utilisation du disque en réduisant le seuil. - Retard de réplication dû à la journalisation. Sur les réplicas avec
log_slow_replica_statements=1(MySQL 8.0.26+), la journalisation des requêtes répliquées peut augmenter le retard. SurveillezSeconds_Behind_Source. - Inondation par log_queries_not_using_indexes. Les petites tables de référence (pays, statuts) déclencheront ce flag même si les scans complets sur des tables de 50 lignes sont optimaux. Utilisez
min_examined_row_limitpour les filtrer. - Prepared statements. Par défaut, les prepared statements ne sont pas enregistrés. Configurez
log_slow_extra=1(MySQL 8.0.14+) pour capturer des statistiques supplémentaires. - Les changements en temps réel ne persistent pas. Les modifications SET GLOBAL sont perdues au redémarrage. Mettez toujours à jour
my.cnfen plus des commandes en temps réel.
Dépannage
Le fichier du slow query log n’est pas créé : Vérifiez que MySQL a les permissions d’écriture sur le répertoire de logs :
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysqlLe log montre zéro requêtes malgré slow_query_log=ON :
Vérifiez que le long_query_time n’est pas configuré trop haut :
SELECT @@global.long_query_time;S’il affiche 10.000000, les requêtes doivent dépasser 10 secondes pour être enregistrées. Réduisez à 1.
pt-query-digest rapporte “No events processed” :
Le fichier de log peut être vide ou le format non reconnu. Assurez-vous que log_output=FILE (pas TABLE). Vérifiez les permissions :
ls -la /var/log/mysql/slow.log
file /var/log/mysql/slow.logmysqldumpslow affiche “Permission denied” :
L’outil a besoin d’un accès en lecture au fichier de log. Exécutez avec sudo ou ajoutez votre utilisateur au groupe mysql :
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.logLes requêtes apparaissent dans le slow log mais EXPLAIN montre une exécution rapide :
Cela signifie généralement une contention de verrous. Vérifiez le champ Lock_time dans l’entrée du log. Des temps de verrouillage élevés indiquent des problèmes de verrouillage au niveau table ou ligne, pas des problèmes d’optimisation de requêtes.
Résumé
- Activez le slow query log avec
slow_query_log=1et configurezlong_query_timeà 1 seconde ou moins - Utilisez
log_queries_not_using_indexespour détecter les requêtes qui se dégraderont avec la croissance des données - Commencez avec
mysqldumpslowpour une analyse rapide ; passez àpt-query-digestpour une analyse de niveau production - Concentrez-vous sur le temps total (fréquence × temps moyen) plutôt que sur la seule exécution la plus lente
- Utilisez
EXPLAINetEXPLAIN ANALYZE(MySQL 8.0) pour comprendre les plans d’exécution avant de créer des index - Concevez des index composites avec les colonnes d’égalité en premier et les colonnes de plage/tri en dernier
- Réécrivez les sous-requêtes corrélées en JOINs pour des améliorations d’un ordre de grandeur
- Implémentez toujours la rotation des logs et surveillez l’espace disque en utilisant le slow query log en production