Comment activer le journal des requêtes lentes dans MySQL et MariaDB

Le journal des requêtes lentes est une fonctionnalité de diagnostic intégrée à MySQL et MariaDB qui enregistre chaque instruction SQL dont le temps d’exécution dépasse un seuil configurable. Il capture la durée des requêtes, le temps de verrouillage, les lignes examinées, les lignes envoyées et le texte SQL complet — offrant aux administrateurs de bases de données et aux développeurs une piste d’audit précise, basée sur des fichiers, de chaque requête qui dégrade les performances de l’application.

L’activer est l’une des actions les plus efficaces que vous puissiez entreprendre lors du réglage des performances de la base de données. Contrairement aux outils de surveillance génériques, le journal des requêtes lentes identifie précisément les instructions responsables de la latence, ce qui le rend indispensable pour l’optimisation des index, la restructuration des requêtes et la planification de capacité sur n’importe quel serveur — d’un environnement VPS Hosting mono-locataire à un cluster de bases de données dédié multi-nœuds.

Pourquoi le journal des requêtes lentes va au-delà de la surveillance de base

La plupart des équipes ont recours à EXPLAIN ou SHOW PROCESSLIST de manière réactive, après que les utilisateurs signalent des lenteurs. Le journal des requêtes lentes fonctionne de manière proactive : il accumule des preuves sur des heures ou des jours de trafic réel, capturant les contrevenants intermittents qui n’apparaissent jamais lors d’une fenêtre d’inspection manuelle.

Les principaux avantages opérationnels incluent :

• Isolation des goulots d’étranglement — distingue les analyses complètes de tables liées au CPU des problèmes de contention de verrous en utilisant les ratios Query_time vs. Lock_time
• Analyse des lacunes d’index — l’indicateur log_queries_not_using_indexes identifie chaque requête effectuant une analyse complète, quelle que soit son temps d’exécution brut
• Détection de régression — la comparaison des instantanés de journaux avant et après un déploiement révèle si le nouveau code a introduit des modèles de requêtes plus lents
• Preuves pour la planification de capacité — les valeurs Rows_examined qui sont plusieurs ordres de grandeur supérieures à Rows_sent indiquent des index manquants ou mal utilisés, qui s’aggravent sous charge

MySQL vs. MariaDB : comparaison des fonctionnalités du journal des requêtes lentes

Les deux moteurs partagent la même infrastructure de journal des requêtes lentes héritée de MySQL 5.1, mais MariaDB l’a étendue de plusieurs façons significatives.

Les options log_slow_verbosity et log_slow_rate_limit dans MariaDB sont particulièrement précieuses dans les environnements de production à haut débit où la journalisation de chaque requête lente deviendrait elle-même une charge pour les performances.

Étape 1 : Localiser le fichier de configuration

MySQL et MariaDB lisent leur configuration depuis différents chemins par défaut selon la distribution et la méthode d’installation.

MySQL :

/etc/my.cnf (basé sur RPM : RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu avec le paquet mysql-server)

MariaDB :

/etc/my.cnf.d/server.cnf (basé sur RPM)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (anciennes configurations Debian)

Si vous n’êtes pas sûr du fichier actif, interrogez le processus en cours d’exécution :
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
Cela affiche la liste ordonnée exacte des fichiers que le démon lit au démarrage, y compris les répertoires !includedir.
Ouvrez le fichier de configuration principal avec votre éditeur préféré :
sudo nano /etc/my.cnf
Étape 2 : Ajouter les directives du journal des requêtes lentes à [mysqld]
Tous les paramètres du journal des requêtes lentes appartiennent à la section [mysqld]. Si la section n’existe pas, créez-la en haut du fichier.
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
Description des paramètres :

slow_query_log = 1 — active la fonctionnalité ; définissez sur 0 pour désactiver sans supprimer le bloc
slow_query_log_file — chemin absolu vers le fichier journal ; l’utilisateur du processus MySQL/MariaDB (mysql) doit avoir un accès en écriture au répertoire parent
long_query_time = 1 — seuil en secondes, accepte des valeurs décimales (par ex., 0.5 pour 500 ms) ; la valeur par défaut de 10 secondes est presque toujours trop permissive pour les applications web
log_queries_not_using_indexes — journalise les requêtes d’analyse complète indépendamment de long_query_time ; combinez avec min_examined_row_limit pour supprimer le bruit des petites tables
min_examined_row_limit — une requête doit examiner au moins ce nombre de lignes avant d’être qualifiée pour la journalisation sous log_queries_not_using_indexes ; empêche les recherches triviales sur une seule ligne de polluer le journal
log_slow_admin_statements — capture les opérations au niveau du schéma qui bloquent les tables et sont fréquemment négligées comme sources de latence

Ajouts spécifiques à MariaDB valant la peine d’être activés en production :
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain ajoute le plan d’exécution de l’optimiseur directement dans chaque entrée du journal, éliminant le besoin de relancer EXPLAIN manuellement après coup — un gain de temps considérable lors du diagnostic de requêtes qui n’apparaissent que sous les modèles de charge de production.
Étape 3 : Créer le fichier journal et définir les permissions
Si le répertoire cible n’existe pas, créez-le et attribuez la propriété avant de redémarrer le service. Ignorer cette étape est l’une des raisons les plus courantes pour lesquelles le journal des requêtes lentes échoue silencieusement à s’activer.
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
Sur les systèmes avec SELinux en mode d’application (RHEL, CentOS, AlmaLinux), le contexte du fichier doit également être défini correctement :
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
L’échec à définir le contexte SELinux correct provoque le démarrage réussi du démon mais l’omission silencieuse d’écriture dans le fichier journal — un cas particulier frustrant qui ne produit aucune erreur évidente dans /var/log/messages.
Étape 4 : Redémarrer le service de base de données
Appliquez les modifications de configuration en redémarrant le service. Sur les distributions basées sur systemd (la norme sur tout serveur Linux moderne) :
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
Sur les anciens systèmes basés sur init.d :
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
Après le redémarrage, vérifiez que le service a démarré correctement :
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Toute mauvaise configuration dans my.cnf empêchera le démarrage et apparaîtra dans la sortie du journal.
Étape 5 : Activer le journal des requêtes lentes à l’exécution (sans redémarrage)
Pour les serveurs de production où un redémarrage est perturbateur, MySQL et MariaDB prennent en charge l’activation dynamique du journal des requêtes lentes via SET GLOBAL. Les modifications effectuées de cette façon prennent effet immédiatement mais ne persistent pas après un redémarrage du service à moins d’être également écrites dans my.cnf.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
C’est l’approche correcte pour les diagnostics d’urgence sur un système en production — activez-le, capturez un échantillon de 15 à 30 minutes pendant les heures de pointe, puis désactivez-le sans toucher au fichier de configuration ni redémarrer le démon.
Étape 6 : Vérifier la configuration
Connectez-vous au client MySQL ou MariaDB :
mysql -u root -p
Puis exécutez une correspondance de modèle sur la table des variables système :
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Sortie attendue pour une instance correctement configurée :
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
Vous pouvez également confirmer que le journal est en cours d’écriture en vérifiant le compteur de requêtes lentes :
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Ce compteur s’incrémente chaque fois qu’une requête dépasse long_query_time, que la journalisation dans un fichier soit active ou non — utile pour confirmer que des requêtes lentes se produisent réellement avant de passer du temps à analyser un fichier journal vide.
Étape 7 : Lire et interpréter le journal brut
Utilisez tail pour surveiller le journal en temps réel pendant un test de charge ou une fenêtre de trafic de pointe :
sudo tail -f /var/log/mysql/slow-query.log
Une entrée de journal typique ressemble à ceci :
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
Ce que chaque champ vous indique :

Query_time — temps d’exécution total en secondes mesuré à l’horloge murale
Lock_time — temps passé à attendre les verrous de table ou de ligne ; un ratio élevé de Lock_time par rapport à Query_time indique une contention, pas un index manquant
Rows_sent — lignes renvoyées au client
Rows_examined — lignes que le moteur de stockage a analysées pour produire le résultat ; un ratio de Rows_examined / Rows_sent supérieur à 100:1 est un signal fort d’un index manquant ou peu sélectif
Bytes_sent — présent dans la verbosité étendue de MariaDB ; utile pour identifier les requêtes renvoyant des ensembles de résultats inutilement volumineux

Dans l’exemple ci-dessus, la requête a examiné 847 293 lignes pour en retourner 1. L’ajout d’un index sur customer_email réduirait Rows_examined à environ 1, réduisant le temps d’exécution de 4,5 secondes à moins d’une milliseconde.
Étape 8 : Analyser le journal avec mysqldumpslow et pt-query-digest
La lecture du fichier journal brut est impraticable à grande échelle. Deux outils agrègent et classent les requêtes lentes par impact total.
Utilisation de mysqldumpslow (fourni avec MySQL/MariaDB)
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow normalise les paramètres des requêtes (en remplaçant les valeurs littérales par N ou S) afin que les requêtes structurellement identiques avec des valeurs de paramètres différentes soient regroupées — essentiel pour identifier les modèles à haute fréquence.
Utilisation de pt-query-digest (Percona Toolkit — recommandé pour la production)
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest produit un rapport classé montrant l’empreinte de chaque requête, le temps d’exécution total, le temps moyen, le nombre d’appels et la distribution par percentile. Il est nettement plus puissant que mysqldumpslow et constitue l’outil standard utilisé par les DBA professionnels pour l’analyse des journaux lents.
Étape 9 : Configurer la rotation des journaux avec logrotate
Sans rotation, le journal des requêtes lentes croît indéfiniment. Sur un serveur occupé avec long_query_time défini à 1 seconde, le fichier peut atteindre plusieurs gigaoctets en quelques jours.
Créez une configuration logrotate dédiée :
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
Explication des directives clés :

rotate 14 — conserve 14 jours d’archives compressées ; ajustez en fonction de votre budget disque et de vos exigences d’audit
compress / delaycompress — compresse les fichiers pivotés avec gzip, mais retarde la compression d’un cycle pour éviter de compresser un fichier que le démon pourrait encore avoir ouvert
postrotate — exécute mysqladmin flush-logs après la rotation, ce qui signale au démon de fermer le descripteur du fichier journal actuel et d’en ouvrir un nouveau ; sans cela, MySQL/MariaDB continue d’écrire dans le fichier renommé jusqu’au prochain redémarrage

Forcez une rotation manuelle pour tester la configuration :
sudo logrotate -f /etc/logrotate.d/mysql-slow
Étape 10 : Désactiver le journal des requêtes lentes lorsqu’il n’est plus nécessaire
La journalisation continue des requêtes lentes à un seuil bas (par ex., 0,5 seconde) sur un serveur à fort trafic ajoute une surcharge d’E/S mesurable. Désactivez-le une fois que vous avez collecté suffisamment de données :
Via le fichier de configuration (persistant) :
[mysqld]
slow_query_log = 0
Puis redémarrez le service :
sudo systemctl restart mysqld   # or mariadb
Via la variable d’exécution (immédiat, non persistant) :
SET GLOBAL slow_query_log = 'OFF';
La méthode d’exécution est préférable pendant les heures de production — elle prend effet en quelques millisecondes sans aucune interruption de service.
Avancé : Utilisation de performance_schema comme complément
Le journal des requêtes lentes capture les requêtes qui dépassent un seuil de temps. La table performance_schema events_statements_summary_by_digest capture des statistiques agrégées pour chaque modèle de requête distinct, quelle que soit la durée d’exécution. Utiliser les deux ensemble donne une image complète.
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
Cette requête identifie les 10 modèles de requêtes les plus chronophages sur l’ensemble de l’historique des instructions — y compris les requêtes rapides qui s’exécutent des millions de fois et dominent collectivement le temps CPU, ce que le journal des requêtes lentes ne capturerait jamais.
Considérations relatives à l’environnement d’hébergement
Le seuil optimal long_query_time dépend fortement du rôle du serveur et de son profil de ressources :

Environnements d’hébergement partagé — généralement pas d’accès direct à my.cnf ; utilisez SET GLOBAL si le fournisseur d’hébergement accorde le privilège SUPER ou SYSTEM_VARIABLES_ADMIN, ou demandez l’accès au journal lent via le panneau de contrôle
Environnements VPS — l’accès root complet signifie un contrôle total sur tous les paramètres de configuration ; une installation VPS avec cPanel expose les paramètres du journal des requêtes lentes via l’éditeur de configuration MySQL de WHM, qui écrit directement dans my.cnf

• Serveurs dédiés — sur un Serveur Dédié exécutant une base de données à fort trafic, envisagez de définir long_query_time aussi bas que 0.1 secondes et d’utiliser log_slow_rate_limit (MariaDB) ou un échantillonnage au niveau de l’application pour contrôler le volume du journal
• Charges de travail analytiques accélérées par GPU — sur les nœuds GPU Hosting exécutant des requêtes analytiques sur de grands ensembles de données, un seuil de 5 à 10 secondes peut être approprié car les requêtes analytiques de longue durée sont un comportement attendu plutôt qu’un défaut

Si votre pile applicative inclut un frontend web géré via un Panneau de contrôle VPS, la corrélation des horodatages du journal des requêtes lentes avec les horodatages du journal d’accès HTTP de votre application est une méthode efficace pour relier la latence de la base de données à des requêtes spécifiques côté utilisateur.

Matrice de décision pratique : choisir le bon seuil

Liste de contrôle technique et points clés

Avant de clore une investigation sur les requêtes lentes, vérifiez chacun des points suivants :

• La section [mysqld] dans my.cnf contient slow_query_log = 1, un chemin slow_query_log_file valide et un long_query_time approprié pour votre profil de trafic
• Le fichier journal et son répertoire parent appartiennent à l’utilisateur système mysql avec des permissions d’écriture ; sur les systèmes SELinux, le contexte du fichier est défini sur mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' confirme slow_query_log = ON et le chemin de fichier correct après le redémarrage du service
SHOW GLOBAL STATUS LIKE 'Slow_queries' affiche un compteur non nul et croissant, confirmant que des requêtes qualifiantes se produisent réellement
log_queries_not_using_indexes est activé et associé à min_examined_row_limit pour éviter que des recherches triviales sur une seule ligne n’inondent le journal
log_slow_admin_statements est activé pour capturer ALTER TABLE, OPTIMIZE TABLE et les opérations DDL similaires qui sont des sources courantes de verrous de table inattendus
Une configuration logrotate est en place avec un hook postrotate qui appelle mysqladmin flush-logs

• Vous avez exécuté pt-query-digest ou mysqldumpslow pour agréger le journal et identifié les 3 à 5 premières requêtes par temps d’exécution total
• Chaque requête identifiée a été analysée avec EXPLAIN (ou EXPLAIN ANALYZE dans MySQL 8.0+) et des index appropriés ont été ajoutés ou la logique de requête restructurée
• Le journal des requêtes lentes a été désactivé ou long_query_time augmenté après la fin du cycle d’optimisation pour minimiser la surcharge d’E/S continue

FAQ

L’activation du journal des requêtes lentes affecte-t-elle les performances de la base de données ?

À un seuil de 1 seconde ou plus sur une charge de travail de production typique, la surcharge est négligeable — généralement inférieure à 1 % du temps d’exécution total des requêtes. La surcharge ne devient mesurable que lorsque long_query_time est défini en dessous de 0,1 seconde ou lorsque log_queries_not_using_indexes est activé sur un schéma avec de nombreuses petites tables non indexées. Utilisez log_slow_rate_limit (MariaDB) ou augmentez min_examined_row_limit pour atténuer cela.

Puis-je activer le journal des requêtes lentes sans redémarrer MySQL ou MariaDB ?

Oui. Utilisez SET GLOBAL slow_query_log = 'ON' et SET GLOBAL long_query_time = 1 depuis n’importe quelle session client MySQL avec le privilège SUPER ou SYSTEM_VARIABLES_ADMIN. La modification prend effet immédiatement. Écrivez les mêmes valeurs dans my.cnf pour les rendre persistantes après les redémarrages.

Quelle est la différence entre Query_time et Lock_time dans le journal des requêtes lentes ?

Query_time est le temps total écoulé à l’horloge murale depuis que le serveur a reçu la requête jusqu’à l’envoi de la dernière ligne au client. Lock_time est la portion de ce total passée à attendre l’acquisition de verrous de table ou de ligne. Une requête avec Lock_time proche de Query_time est un problème de contention de verrous, pas un problème d’index — la solution implique la conception des transactions ou la réduction de la portée des verrous, pas l’ajout d’index.

Pourquoi mon fichier journal des requêtes lentes est-il vide même si slow_query_log = ON ?

Les causes les plus courantes sont : (1) aucune requête n’a encore dépassé long_query_time — vérifiez avec SHOW GLOBAL STATUS LIKE 'Slow_queries' ; (2) le chemin du fichier journal n’existe pas ou l’utilisateur mysql manque de permission d’écriture ; (3) sur les systèmes SELinux, le contexte du fichier est incorrect ; (4) la variable slow_query_log_file pointe vers un chemin différent du fichier que vous inspectez — confirmez avec SHOW VARIABLES LIKE 'slow_query_log_file'.

Comment trouver la requête la plus dommageable dans le journal des requêtes lentes ?

Exécutez pt-query-digest et triez par R/Call (lignes examinées par appel) ou Response time (temps cumulatif total). La requête en tête du classement Response time consomme le plus de temps de base de données agrégé et devrait être la première cible pour l’analyse EXPLAIN et l’optimisation des index. Si pt-query-digest n’est pas disponible, utilisez mysqldumpslow -s t -t 1 pour extraire la requête avec le temps total le plus élevé.