Debian 13 : journal des requêtes lentes MySQL — trouvez la requête qui vous tue en silence

Pourquoi le journal des requêtes lentes reste gagnant en 2025

MySQL s’est doté de beaucoup d’instrumentation : Performance Schema, vues du schéma sys, traces de l’optimiseur, digests de requêtes, et suffisamment de réglages pour construire un cockpit. Pourtant le journal des requêtes lentes reste le moyen le plus rapide de relier « les utilisateurs sont mécontents » à « voici le SQL qui le provoque ». Ce n’est pas parfait. Ce n’est pas exhaustif. C’est juste utile de façon fiable.

Voici ce qu’il fait mieux que la plupart des tableaux de bord :

• Il capture la réalité sous charge. Pas la requête que vous aimeriez exécuter, mais celle que vous avez réellement lancée quand tout partait en vrille.
• Il est peu coûteux à démarrer. Activez-le, recueillez des échantillons, et passez à autre chose. Performance Schema peut être plus lourd si vous ne savez pas exactement ce que vous activez.
• C’est une preuve portable. Un fichier de journal lent est quelque chose que vous pouvez remettre à un collègue, archiver avec un ticket d’incident et comparer entre versions.
• Il transforme les plaintes vagues en suspects classés. Temps total, nombre d’exécutions, lignes examinées, temps d’attente de verrou—suffisant pour savoir où creuser.

Et oui, il peut aussi devenir un dépotoir de requêtes « lentes » qui sont en réalité acceptables. C’est pourquoi vous ajustez les seuils et l’interprétez comme un opérateur : vous vous souciez du débit, de la latence de queue, et de la contention des ressources, pas seulement d’un nombre appelé « Query_time ».

Blague 1 : Le journal des requêtes lentes, c’est comme un suivi du temps au travail : c’est embarrassant jusqu’à ce que vous voyiez vraiment où est passé votre temps.

Une citation à garder en tête (idée paraphrasée) : « L’espoir n’est pas une stratégie », souvent entendue dans les cercles d’opérations ; traitez le travail de performance de la même façon—mesurez d’abord, agissez ensuite.

Faits intéressants et un peu d’histoire (pour ne plus répéter les mêmes erreurs)

Vous n’avez pas besoin de trivia pour administrer des bases, mais quelques faits concrets expliquent pourquoi certains réglages existent et pourquoi certaines « solutions évidentes » échouent sans cesse.

1. Le journal des requêtes lentes précède l’observabilité moderne. C’était l’une des premières méthodes « intégrées » pour identifier les douleurs liées aux requêtes sans profileurs externes.
2. « Lent » est un choix de politique, pas une vérité universelle. Une requête à 200 ms est lente pour un endpoint de connexion mais rapide pour un rapport nocturne.
3. MySQL peut journaliser des requêtes qui n’utilisent pas d’index. Le drapeau log_queries_not_using_indexes existe parce que les scans de table complets sont une manière fiable de provoquer des pannes surprises.
4. Le journal peut inclure des statements administratifs. Avec log_slow_admin_statements, vous capturerez des DDL ou opérations de maintenance « innocents » qui bloquent la prod.
5. InnoDB a changé la nature du « lent ». Depuis qu’InnoDB est devenu le moteur par défaut, de nombreux ralentissements sont passés de la CPU aux attentes I/O et aux comportements de verrouillage.
6. Le cache de requêtes a été supprimé dans MySQL 8.0. Toute une époque de « on a réglé ça en mettant en cache » est terminée, et tant mieux—l’invalidation du cache de requêtes était source de chaos.
7. La réplication peut faire réapparaître les requêtes lentes deux fois. Une instruction lente sur le primaire l’est souvent aussi sur les replicas ; de plus les replicas peuvent prendre du retard et aggraver la douleur.
8. La réplication en mode row-based a rendu la notion de « même statement » moins pertinente. Les problèmes de performance se cachent parfois dans la logique des triggers ou des effets secondaires plutôt que dans le texte brut du SELECT.
9. MySQL moderne expose des résumés basés sur des digests. Performance Schema peut agréger par motifs normalisés ; le journal lent montre des exemples concrets que vous pouvez reproduire.

Playbook de diagnostic rapide (premier/deuxième/troisième)

Ceci est l’ordre qui identifie le coupable rapidement, sans empirer votre panne. L’objectif n’est pas « optimiser MySQL ». L’objectif est « arrêter l’hémorragie ».

Premier : décidez si vous êtes CPU-bound, I/O-bound ou lock-bound

• Si la CPU est saturée : attendez-vous à de mauvais plans, des indexes manquants, de gros tris, ou trop de requêtes concurrentes.
• Si le disque est saturé : attendez-vous à des scans, de grosses lectures, des manques dans le buffer pool, ou des checkpoints/pressions de flush.
• Si les threads attendent : attendez-vous à des verrous, des problèmes de metadata lock, des transactions longues, ou des lignes chaudes.

Deuxième : activez ou vérifiez le journal lent, puis capturez une fenêtre propre

Ne taillez pas un fichier vieux d’une semaine et ne devinez pas. Faites une rotation, puis capturez 5–15 minutes pendant la douleur. Vous voulez une fenêtre temporelle serrée qui correspond aux symptômes.

Troisième : classez par temps total, pas par « pire requête unique »

La requête qui a tourné 20 secondes une fois est spectaculaire. La requête qui tourne 120 ms 4 000 fois par minute est celle qui vous bouffe silencieusement. Classez par sum(Query_time) et par count, puis examinez des exemples.

Quatrième : vérifiez avec EXPLAIN et les statistiques réelles des handlers

Les journaux lents racontent ce qui s’est passé. EXPLAIN raconte ce que MySQL a prévu de faire. Les deux peuvent se tromper isolément. Combinez-les.

Activer la journalisation des requêtes lentes sur Debian 13 (sans se faire mal)

Debian 13 exécute typiquement MySQL 8.0-ish ou MariaDB selon votre choix. La mécanique est similaire, mais les emplacements des fichiers de configuration et les valeurs par défaut des paquets diffèrent. Je vais supposer Oracle MySQL (paquet mysql-server) avec systemd ; les mêmes étapes s’appliquent majoritairement à MariaDB avec des noms de service et variables légèrement différents.

Où la configuration vit généralement

• /etc/mysql/my.cnf inclut d’autres fichiers.
• /etc/mysql/mysql.conf.d/mysqld.cnf est l’endroit courant pour les réglages serveur.
• /var/log/mysql/ est un répertoire de journaux courant, mais vérifiez les permissions et les profils AppArmor.

Config minimale raisonnable pour le slow-log

Ne vous compliquez pas la vie. Commencez par un enregistrement dans un fichier, un seuil modéré, et évitez de tout journaliser sur un système chargé.

cr0x@server:~$ sudo editor /etc/mysql/mysql.conf.d/mysqld.cnf
...add or adjust...
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0.2
log_queries_not_using_indexes = OFF
log_slow_admin_statements = ON
log_slow_replica_statements = OFF

Pourquoi ces choix :

• long_query_time=0.2 est assez agressif pour attraper la « mort par mille coupures » sans tout journaliser (en général). Ajustez ensuite.
• log_queries_not_using_indexes=OFF au départ car cela peut noyer les journaux de scans innocents sur de petites tables.
• log_slow_admin_statements=ON car le DDL en ligne et les commandes admin peuvent paralyser la production.

Recharger en toute sécurité

Changer les réglages du slow log est souvent dynamique, mais n’en faites pas une hypothèse. Utilisez SET PERSIST où c’est approprié, et validez avec SHOW VARIABLES.

Tâches pratiques : commandes, sorties et décisions (12+)

Voici les gestes d’opérateur qui fonctionnent à 2 h du matin. Chacun inclut ce que la sortie signifie et quelle décision prendre ensuite.

Task 1: Confirm which server you’re running (MySQL vs MariaDB) and version

cr0x@server:~$ mysql --version
mysql  Ver 8.0.36-0+deb13u1 for Linux on x86_64 (MySQL Community Server - GPL)

Ce que cela signifie : Vous savez quelles variables et fonctionnalités s’appliquent. MySQL 8 dispose de SET PERSIST ; MariaDB diffère.

Décision : Utilisez la syntaxe MySQL 8 et attendez-vous à ce que Performance Schema soit disponible par défaut.

Task 2: Check if slow logging is currently enabled

cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'slow_query_log';"
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

Ce que cela signifie : Vous n’avez actuellement aucune piste médico-légale.

Décision : Activez-le immédiatement (dynamiquement si possible), puis capturez une courte fenêtre pendant la charge.

Task 3: Enable slow log dynamically (immediate, not after restart)

cr0x@server:~$ mysql -e "SET GLOBAL slow_query_log = ON;"
...Query OK, 0 rows affected...

Ce que cela signifie : Les nouvelles requêtes lentes seront journalisées immédiatement.

Décision : Définissez ensuite le chemin du fichier de log, vérifiez qu’il est inscriptible, puis réglez le seuil.

Task 4: Set the slow log file location and verify MySQL can write it

cr0x@server:~$ mysql -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';"
...Query OK, 0 rows affected...

cr0x@server:~$ sudo install -o mysql -g adm -m 0640 /dev/null /var/log/mysql/mysql-slow.log

cr0x@server:~$ sudo ls -l /var/log/mysql/mysql-slow.log
-rw-r----- 1 mysql adm 0 Dec 30 01:12 /var/log/mysql/mysql-slow.log

Ce que cela signifie : Le fichier existe avec une propriété raisonnable. MySQL ne tombera pas silencieusement s’il ne peut pas ouvrir le log.

Décision : Si la propriété est incorrecte, corrigez-la avant de chasser des « pas de requêtes lentes » fantômes.

Task 5: Set long_query_time for triage

cr0x@server:~$ mysql -e "SET GLOBAL long_query_time = 0.2; SHOW VARIABLES LIKE 'long_query_time';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 0.200 |
+-----------------+-------+

Ce que cela signifie : Les requêtes plus lentes que 200 ms sont journalisées.

Décision : Pendant un incident, baissez ce seuil (par ex. 0.1) si vous avez besoin de plus d’échantillons. Ensuite, augmentez-le pour réduire le bruit.

Task 6: Rotate the slow log to isolate a time window

cr0x@server:~$ mysql -e "FLUSH SLOW LOGS;"
...Query OK, 0 rows affected...

cr0x@server:~$ sudo ls -lh /var/log/mysql/mysql-slow.log*
-rw-r----- 1 mysql adm  12K Dec 30 01:15 /var/log/mysql/mysql-slow.log
-rw-r----- 1 mysql adm 1.8M Dec 30 01:12 /var/log/mysql/mysql-slow.log.1

Ce que cela signifie : Vous avez créé un fichier « courant » propre. L’ancien est archivé avec un suffixe.

Décision : Capturez les prochaines 5–15 minutes de douleur et analysez uniquement cette fenêtre.

Task 7: Confirm MySQL is actually writing entries

cr0x@server:~$ sudo tail -n 5 /var/log/mysql/mysql-slow.log
# Time: 2025-12-30T01:16:19.123456Z
# User@Host: app[app] @ 10.0.2.41 []  Id:  9123
# Query_time: 0.412  Lock_time: 0.000  Rows_sent: 20  Rows_examined: 250000
SET timestamp=1735521379;
SELECT * FROM orders WHERE user_id=84721 ORDER BY created_at DESC LIMIT 20;

Ce que cela signifie : La journalisation fonctionne. Cet extrait sent déjà le « index manquant pour ORDER BY + filtre ».

Décision : N’optimisez pas à partir d’une seule ligne. Agrégez ensuite.

Task 8: Quick aggregation with mysqldumpslow

cr0x@server:~$ sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 184  Time=0.29s (52s)  Lock=0.00s (0s)  Rows=20.0 (3680), app@app[10.0.2.41]
  SELECT * FROM orders WHERE user_id=N ORDER BY created_at DESC LIMIT N

Count: 21  Time=1.10s (23s)  Lock=0.02s (0s)  Rows=1.0 (21), app@app[10.0.2.55]
  UPDATE inventory SET qty=qty-N WHERE sku='S'

Ce que cela signifie : La première requête consomme 52 secondes au total dans cette fenêtre à cause d’un grand nombre d’exécutions. La seconde a un temps par requête plus élevé mais un total inférieur.

Décision : Corrigez d’abord le plus gros consommateur de temps total sauf si le second provoque des verrous ou une défaillance en cascade.

Task 9: Install and run pt-query-digest for real ranking

cr0x@server:~$ sudo apt-get update
...output...

cr0x@server:~$ sudo apt-get install -y percona-toolkit
...output...

cr0x@server:~$ sudo pt-query-digest /var/log/mysql/mysql-slow.log | sed -n '1,80p'
# 2025-12-30T01:20:44
# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ====
#    1 0xA1B2C3D4E5F6A7B8  52.1230 65.0%   184 0.2833  0.12 SELECT orders
#    2 0x1122334455667788  23.0041 28.7%    21 1.0954  0.03 UPDATE inventory
#    3 0x9988776655443322   3.9002  4.9%    97 0.0402  0.01 SELECT sessions

Ce que cela signifie : Vous avez maintenant une liste classée par temps de réponse total. Les empreintes de requêtes évitent le bruit des « mêmes requêtes avec des IDs différents ».

Décision : Récupérez la section complète du rapport pour le rang 1 et utilisez-la pour reproduire et EXPLAIN.

Task 10: Get schema and index context for the top query

cr0x@server:~$ mysql -e "SHOW CREATE TABLE orders\G" | sed -n '1,60p'
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `status` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB

Ce que cela signifie : Il n’y a pas d’index composite sur (user_id, created_at). La requête filtre par user_id et ordonne par created_at—cas classique.

Décision : Planifiez un index qui correspond au motif d’accès : (user_id, created_at DESC) en MySQL 8 (DESC supporté), ou (user_id, created_at) qui aide souvent aussi.

Task 11: Validate the execution plan (EXPLAIN) and what it’s scanning

cr0x@server:~$ mysql -e "EXPLAIN SELECT * FROM orders WHERE user_id=84721 ORDER BY created_at DESC LIMIT 20\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 248901
     filtered: 10.00
        Extra: Using where; Using filesort

Ce que cela signifie : Scan complet de table (type: ALL) plus filesort. Voilà votre I/O et votre CPU.

Décision : Ajoutez le bon index, puis relancez EXPLAIN pour confirmer son utilisation, et mesurez avant/après.

Task 12: Add the index carefully (online where possible) and watch impact

cr0x@server:~$ mysql -e "ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);"
...Query OK, 0 rows affected...

Ce que cela signifie : L’index est créé. Selon la taille de la table et les paramètres MySQL, cela peut prendre du temps et consommer de l’I/O. Sur de grandes tables, faites-le pendant une fenêtre de maintenance ou utilisez des outils de changement de schéma en ligne.

Décision : Validez immédiatement le nouveau plan et vérifiez si le volume du slow log diminue. Si vous avez de la réplication, vérifiez le retard des replicas.

Task 13: Re-run EXPLAIN to confirm plan improvement

cr0x@server:~$ mysql -e "EXPLAIN SELECT * FROM orders WHERE user_id=84721 ORDER BY created_at DESC LIMIT 20\G"
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ref
possible_keys: idx_user_created
          key: idx_user_created
      key_len: 8
          ref: const
         rows: 20
        Extra: Using index condition

Ce que cela signifie : MySQL utilise maintenant l’index et s’attend à ~20 lignes, pas ~250k. Le filesort a disparu.

Décision : Surveillez la latence et l’I/O. Si ça s’est amélioré, vous avez probablement trouvé « la » requête. Continuez pour le prochain coupable.

Task 14: Check whether the “slow” time was actually lock time

cr0x@server:~$ grep -E "Query_time|Lock_time" -n /var/log/mysql/mysql-slow.log | head
2:# Query_time: 0.412  Lock_time: 0.000  Rows_sent: 20  Rows_examined: 250000
8:# Query_time: 1.905  Lock_time: 1.723  Rows_sent: 1  Rows_examined: 1

Ce que cela signifie : Le second échantillon a passé la plupart du temps à attendre des verrous, pas à s’exécuter.

Décision : Arrêtez d’« optimiser » le texte SQL et cherchez qui tient les verrous : transactions longues, lignes chaudes ou metadata locks.

Task 15: Identify current blockers and lock waits

cr0x@server:~$ mysql -e "SHOW PROCESSLIST;" | sed -n '1,20p'
Id	User	Host	db	Command	Time	State	Info
9123	app	10.0.2.41:51022	prod	Query	2	Sending data	SELECT * FROM orders WHERE user_id=84721 ORDER BY created_at DESC LIMIT 20
9150	app	10.0.2.55:53488	prod	Query	35	Waiting for row lock	UPDATE inventory SET qty=qty-1 WHERE sku='ABC-123'
9201	admin	localhost	prod	Query	120	Starting alter table	ALTER TABLE inventory ADD COLUMN last_checked datetime

Ce que cela signifie : Vous avez des attentes de verrou sur des lignes et un DDL en cours. Ce DDL peut impacter la concurrence selon l’algorithme et les metadata locks.

Décision : Si vous êtes en mode incident, envisagez d’arrêter le DDL ou de le déplacer hors pic, et corrigez le comportement transactionnel des mises à jour d’inventaire.

Task 16: Check InnoDB buffer pool pressure (I/O vs memory)

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_read_requests      | 987654321  |
| Innodb_buffer_pool_reads              | 12345678   |
+---------------------------------------+------------+

Ce que cela signifie : Les lectures depuis le disque sont non négligeables. Un taux élevé de Innodb_buffer_pool_reads signifie que vous manquez le cache et payez en I/O.

Décision : Si le buffer pool est trop petit, augmentez-le (prudemment). Mais éliminez d’abord les requêtes scan-intensives ; sinon vous ne ferez que mettre en cache vos erreurs.

Lire les journaux lents comme un SRE, pas comme un voyant

Une entrée du journal lent est une petite histoire : qui l’a lancée, combien de temps elle a duré, ce qu’elle a retourné et combien elle a examiné. L’astuce est de comprendre quelle partie de cette histoire est exploitable.

Champs qui comptent (et ce qu’ils essaient de vous dire)

• Query_time : temps horloge. Inclut attente CPU, I/O, verrous et parfois délais d’ordonnancement. C’est votre symptôme, pas votre diagnostic.
• Lock_time : temps passé à attendre des verrous table/row (selon le moteur). S’il est élevé, votre « requête lente » peut être du SQL rapide coincé derrière une transaction plus lente.
• Rows_examined : combien de travail a été fait. Beaucoup de rows_examined avec peu de rows_sent indique souvent un index manquant.
• Rows_sent : combien de données vous avez renvoyées. Si c’est énorme, la requête fait peut-être ce pour quoi elle a été conçue, mais la conception est erronée (endpoints bavards, exports gigantesques).
• User@Host : identifie la source de la charge. Cela vous dit souvent quel service doit corriger le problème.
• Timestamp : permet de corréler avec des déploiements, des cron jobs, ou des pics de trafic.

Les trois archétypes de « qui vous tue silencieusement »

Vous verrez ces cas en boucle :

1. Fréquence élevée, temps modéré : une requête qui n’est pas terrible mais qui tourne constamment. La corriger améliore la CPU et la latence en queue. Le meilleur ROI.
2. Faible fréquence, très long : rapports, jobs batch et « une grosse requête ». Souvent acceptable si programmée ; inacceptable si déclenchée par des utilisateurs.
3. Dominé par les verrous : requêtes qui ont l’air lentes parce qu’elles étaient bloquées. Ici, corrigez la portée des transactions, le comportement d’isolation, ou les points chauds de données.

Où les opérateurs se font piéger

Le journal des requêtes lentes est littéral. Il enregistre qu’une requête a duré 1,2 seconde. Il n’enregistre pas pourquoi. Le pourquoi peut être :

• un pic de latence du système de fichiers,
• un flush de checkpoint,
• une contention mutex,
• la saturation I/O d’un replica,
• ou une seule session qui tient un verrou parce qu’elle a ouvert une transaction et est partie prendre un café.

Ce dernier cas arrive plus souvent que quiconque ne veut l’admettre.

Blague 2 : Une transaction longue, c’est comme laisser le micro-ondes de la cuisine du bureau allumé—tout le monde le remarque, personne ne sait qui l’a fait.

Outils : mysqldumpslow, pt-query-digest et compagnons

Vous pouvez beaucoup faire avec un simple grep et de la patience, mais vous avez mieux à faire que normaliser manuellement du SQL. Utilisez les outils. Sachez ce en quoi ils excellent.

mysqldumpslow : rapide et sale

Il est fourni avec MySQL et est bon pour un premier tri. Il regroupe les requêtes par une normalisation grossière (remplace nombres et chaînes). C’est suffisant pour « quel est le principal coupable maintenant ? » Ce n’est pas idéal pour une analyse approfondie sur de nombreuses sources ou pour des variantes complexes d’instructions.

pt-query-digest : triage niveau production

Le digest de Percona Toolkit est l’outil de référence. Il fingerprinte les requêtes, agrège par temps total et variance, et imprime des échantillons représentatifs. Il est volontiers opinionné : il vous pousse à vous concentrer sur ce qui compte.

Si vous avez un environnement de production stable, pensez à un job planifié qui digère les slow logs et stocke des résumés quelque part. Pas parce que vous aimez les rapports, mais parce que les incidents sont plus courts quand vous connaissez déjà les suspects habituels.

Quand Performance Schema aide

Le journal lent est réactif : il vous parle des requêtes qui ont dépassé un seuil. Performance Schema peut être proactif : il peut montrer les statements en haut du tableau par temps total même s’ils ne sont pas « lents » individuellement.

Mais si vous êtes déjà en douleur et que vous ne connaissez pas votre overhead actuel, n’activez pas une douzaine d’interrupteurs d’instrumentation en plein incident. Votre travail est de restaurer le service d’abord, pas de devenir une plateforme de télémétrie en solo.

Modes de défaillance : quand les journaux lents mentent (ou au moins induisent en erreur)

Le journal des requêtes lentes n’a pas tort. Il est juste incomplet. Voici les façons courantes dont il vous fait courir après le mauvais lapin.

1) Seuil trop élevé, et le vrai coupable n’apparaît jamais

Si long_query_time est à 2 secondes, vous manquerez la requête à 150 ms qui tourne 1 000 fois par seconde. Cette requête peut saturer votre CPU sans jamais être « lente ».

Correction : abaissez temporairement long_query_time pendant l’investigation ou utilisez Performance Schema pour classer par temps total.

2) Seuil trop bas, et vous vous noyez dans le bruit

Si vous mettez long_query_time à 0.01 sur un système chargé, vous risquez de générer des logs plus vite que vous ne pouvez les écrire. Félicitations, vous venez d’inventer un déni de service basé sur les logs.

Correction : échantillonnez sur de courtes fenêtres. Faites pivoter les logs. Analysez des tranches petites. Relevez le seuil ensuite.

3) Les attentes de verrou font paraître des requêtes innocentes coupables

Un simple UPDATE peut afficher Query_time: 2.0 avec Lock_time: 1.9. Le SQL n’est pas lent ; le modèle de concurrence est cassé.

Correction : trouvez le bloqueur (processlist, InnoDB lock waits), raccourcissez les transactions, réduisez les lignes chaudes, ou changez les patterns d’écriture.

4) La requête lente est un symptôme d’un effondrement I/O, pas la cause

Quand la latence du stockage grimpe, tout ralentit. Le journal montrera un défilé de requêtes « lentes » et vous blâmerez la mauvaise requête.

Correction : vérifiez la santé et la latence du stockage. Cherchez les manques du buffer pool et les scans ; puis inspectez la plateforme (disque, RAID, virtualisation, voisins bruyants).

5) L’emplacement du journal n’est pas inscriptible, et vous obtenez du silence

MySQL ne peut pas toujours écrire là où vous lui avez dit, surtout avec des profils AppArmor. Vous penserez ne pas avoir de requêtes lentes. Vous en avez. Vous n’avez simplement pas le journal.

Correction : assurez-vous que le fichier existe, que la propriété est correcte, et vérifiez le journal d’erreurs MySQL pour des erreurs d’ouverture de fichier.

Erreurs communes : symptôme → cause racine → correction

1) « La CPU est saturée, mais le slow log est vide »

Symptôme : CPU élevée, QPS élevé, utilisateurs qui se plaignent, le slow log contient peu ou rien.

Cause racine : long_query_time trop élevé ; votre tueur est « rapide » mais fréquent.

Correction : baissez temporairement long_query_time (par ex. 0.1–0.2), faites pivoter les logs, et/ou utilisez Performance Schema pour classer par temps total.

2) « Tout est lent après avoir activé log_queries_not_using_indexes »

Symptôme : Disque occupé, volume de logs explose, MySQL semble pire.

Cause racine : Vous avez journalisé chaque petit scan sur de petites tables et créé une amplification d’écriture.

Correction : désactivez-le, puis utilisez des digests pour identifier les quelques scans avec beaucoup de rows_examined ou un temps total élevé.

3) « On a ajouté un index et la performance a empiré »

Symptôme : Les écritures ralentissent, la réplication prend du retard, le buffer pool tourne plus.

Cause racine : Le nouvel index a augmenté le coût des écritures et l’empreinte mémoire ; vous avez optimisé une voie de lecture sans considérer le volume d’écritures.

Correction : validez le bénéfice pour la lecture vs le coût pour les écritures. Envisagez un index couvrant plus restreint, réduisez la largeur de l’index, ou repensez la requête pour l’éviter.

4) « Les requêtes lentes sont toutes le même SELECT, mais seulement parfois »

Symptôme : Même statement apparaît avec des Query_time variables.

Cause racine : les valeurs de paramètres produisent une sélectivité différente ; le plan est instable ; les stats sont obsolètes ; ou vous voyez des pics de contention de verrous.

Correction : comparez Rows_examined entre échantillons, lancez EXPLAIN avec des paramètres représentatifs, mettez à jour les statistiques, et enquêtez sur les verrous si Lock_time augmente.

5) « La requête la plus lente est un rapport ; le site meurt »

Symptôme : Une grosse requête de reporting domine le slow log, la latence applicative explose.

Cause racine : ressources partagées. Le rapport sature l’I/O ou le buffer pool, au détriment de la charge OLTP.

Correction : déplacez le rapport sur un replica, planifiez-le hors-pointe, ajoutez de l’isolation des ressources, ou pré-agrégez. N’« optimisez » pas en ajoutant des indexes au hasard sur des tables OLTP en production en premier.

6) « Les requêtes ont ralenti juste après une migration de schéma »

Symptôme : Le temps de verrou et les états d’attente augmentent ; le slow log montre des statements admin.

Cause racine : DDL qui a déclenché des metadata locks ou des rebuilds de grande table ; la migration a croisé le trafic de pointe.

Correction : utilisez des stratégies de changement de schéma en ligne, exécutez les migrations durant des fenêtres contrôlées, et journalisez les slow admin statements pour pouvoir prouver la causalité la prochaine fois.

Trois mini-histoires d’entreprise issues du terrain

Incident : la mauvaise supposition que « les replicas ne comptent pas »

Une équipe exploitait une base transactionnelle chargée avec un primaire et deux replicas. Les replicas étaient « pour les lectures et les backups », et cette phrase est devenue une excuse pour ignorer totalement la performance des replicas. Le primaire était surveillé de près ; les replicas traités comme des secours froids.

Puis une nouvelle fonctionnalité a été déployée : l’application a commencé à router une partie des lectures de profils utilisateurs vers les replicas. Pas toutes les lectures. Juste certaines. Le déploiement était progressif, ce qui a rendu la chose plus difficile : personne n’a vu un effondrement net. On a vu une augmentation lente de la latence p95 et quelques « timeouts aléatoires » qui ne corrélaient pas avec les métriques du primaire.

Le journal des requêtes lentes sur le primaire avait l’air inoffensif. Sur les replicas, il criait. Un SELECT particulier avec un ORDER BY apparemment inoffensif tournait juste en dessous du seuil de lenteur sur le primaire grâce au cache chaud, mais il dépassait constamment le seuil sur les replicas parce que leurs buffer pools étaient froids et leurs disques plus lents.

La mauvaise supposition était qu’une requête « qui passe sur le primaire » était correcte partout. Ce n’était pas le cas. Les replicas avaient des caractéristiques I/O différentes, une forme de charge différente, et un job de backup qui faisait des pics de lectures chaque heure. Les journaux lents l’ont prouvé : même empreinte de requête, distribution de Query_time différente.

La correction n’a pas été héroïque : ajouter l’index composite manquant, augmenter la taille du buffer pool des replicas, et planifier les backups en dehors du nouveau pattern de trafic. La leçon a été retenue : les replicas sont de la production aussi, sans excuses.

Optimisation qui a mal tourné : l’index qui a mangé le chemin d’écriture

Un service lié à la finance avait une table de mouvements de grand livre. Les lectures étaient fréquentes, mais les écritures constantes. Un ingénieur a vu une entrée du slow log pour une requête filtrant par (account_id, created_at) et a fait l’« évident » : ajouter un gros index composite qui incluait aussi une large colonne varchar pour « couvrir » complètement la requête.

La lecture s’est améliorée. C’était magnifique en staging. En production, le chemin des écritures a commencé à vaciller. La latence des inserts a augmenté, la réplication a pris du retard, et le buffer pool est devenu moins efficace. Le slow log a commencé à montrer d’autres requêtes : pas le SELECT original, mais des inserts et updates qui prenaient maintenant plus de temps parce que chaque écriture devait maintenir une structure d’index plus lourde.

L’équipe a passé une journée à se disputer pour savoir si l’index « aurait dû » aider. Il a aidé la lecture. Il a aussi changé l’économie de toute la table. InnoDB ne vous donne pas des indexes gratuitement ; il vous les facture en amplification d’écriture et mémoire, à chaque opération.

La correction a été chirurgicale. Ils ont remplacé l’index couvrant large par un index composite plus étroit, puis réécrit la requête pour ne récupérer que les colonnes nécessaires. Cela a réduit le bloat d’index et restauré le débit d’écriture. Ils ont aussi ajouté une règle : toute proposition d’index nécessite une discussion explicite sur le coût en écriture et un plan de rollback.

Pratique ennuyeuse mais correcte qui a sauvé la mise : journalisation par fenêtre temporelle et rotation

Une plateforme SaaS avait un souci récurrent : une fois tous les quelques jours, le système « semblait lent » pendant environ dix minutes. Rien de dramatique, juste assez pour déclencher des tickets de support. C’était le genre de problème qui fait perdre des semaines parce qu’il est intermittent et que tout le monde a une théorie.

L’ingénieur d’astreinte a fait quelque chose d’inélégant : il a ajouté une étape au playbook pour flush et faire pivoter le slow log dès le début de l’incident, puis capturer exactement 10 minutes de données. Pas « le fichier de log d’hier soir », pas « peut-être vers 3 h ». Une fenêtre nette.

Après deux incidents, le pattern est apparu. Ce n’était pas une requête unique ; c’était un job batch qui tournait toutes les quelques heures et générait une rafale de requêtes modérées. Individuellement elles allaient ; ensemble elles saturaient l’I/O et faisaient basculer le churn du buffer pool. Le slow log, quand il est découpé sur la bonne fenêtre, montrait un ensemble d’empreintes cohérent apparaissant uniquement pendant l’événement.

La correction a été aussi ennuyeuse : déplacer la charge batch sur un replica et ajouter un rate limiting sur le job. Les tickets de support ont chuté. Personne n’a eu de trophée. La production est devenue plus calme, ce qui est la forme de romance la plus proche que connaissent les SRE.

Checklists / plan pas à pas

Quand vous êtes en incident (30–60 minutes)

1. Classifiez le goulot : CPU, I/O ou verrous. Ne devinez pas ; vérifiez.
2. Confirmez que le slow log est activé et inscriptible : s’il n’écrit pas, réparez cela d’abord.
3. Fixez un seuil de triage : long_query_time autour de 0.1–0.5 selon la charge.
4. Flush/pivotez les logs : isolez une fenêtre propre pendant la douleur.
5. Digérez et classez : utilisez pt-query-digest si disponible ; sinon mysqldumpslow.
6. Choisissez le principal coupable par temps total : sauf si lock_time indique un problème de blocage.
7. EXPLAIN avec de vrais paramètres : confirmez scan vs utilisation d’index, filesort, tables temporaires.
8. Corrigez minimalement : ajoutez ou ajustez un index, ou réécrivez une voie de requête. Ne refactorez pas l’univers.
9. Re-mesurez : pivotez le log à nouveau, comparez les principaux coupables avant/après.
10. Documentez l’empreinte : le pattern normalisé de la requête est ce qui compte pour la récurrence.

Après l’incident (le lendemain)

1. Définissez la config persistante : utilisez le fichier de config ou SET PERSIST pour ne pas perdre les réglages au redémarrage.
2. Décidez de la rétention des logs : les slow logs grossissent ; pivotez via logrotate, expédiez vers un stockage central si besoin.
3. Établissez des seuils alignés SLO : choisissez un long_query_time qui capture la latence visible par l’utilisateur, pas le bruit.
4. Faites la baseline des empreintes de requêtes : vos « top 20 » normaux sont un puissant système d’alerte précoce.
5. Mettez en place une habitude de revue d’index : chaque nouvel index a un coût en écriture et en mémoire ; traitez-le comme de la planification de capacité.

Garde-fous opérationnels (prévenir les auto-sabotages futurs)

• N’activez jamais « journaliser tout » en permanence sur un primaire chargé.
• Gardez les slow logs sur un disque local à latence prévisible, pas sur un filesystem réseau.
• Rendez les migrations de schéma observables : journalisez les slow admin statements et suivez les metadata lock waits.
• Apprenez aux équipes à lire Rows_examined comme une facture : si c’est énorme, quelqu’un paye.

FAQ

1) Dois-je journaliser dans un fichier ou dans une table ?

Fichier, pour la plupart des systèmes de production. Journaliser dans une table peut créer de la contention, gonfler la charge du data dictionary, et compliquer la rétention. N’utilisez la journalisation en table que si vous avez un pipeline contrôlé et que vous avez testé l’overhead.

2) Quelle valeur pour long_query_time est bonne ?

Commencez autour de 0.2–0.5 secondes pour les services OLTP, puis ajustez selon le volume et les SLO. Pendant l’investigation, abaissez-le brièvement pour capturer plus d’échantillons. Ne le mettez pas à 0 sauf si vous aimez remplir des disques.

3) Pourquoi je vois beaucoup de Rows_examined mais peu de Rows_sent ?

C’est généralement un index manquant ou mal adapté, ou une requête qui ne peut pas utiliser l’index à cause de fonctions, conversions implicites, ou ordre de prédicats inapproprié. Confirmez avec EXPLAIN ; cherchez type: ALL ou Using filesort.

4) Le slow log montre un UPDATE très long, mais EXPLAIN a l’air OK. Et maintenant ?

Vérifiez Lock_time. Si celui-ci est élevé, votre UPDATE était bloqué. Trouvez la transaction bloquante, raccourcissez la portée des transactions, et évitez les designs de ligne chaude où de nombreuses sessions mettent à jour la même ligne.

5) Activer le journal des requêtes lentes va-t-il nuire à la performance ?

Un peu d’overhead, oui—surtout lié à l’écriture des logs. Avec des seuils raisonnables et une journalisation fichier sur disque local, c’est généralement acceptable. Le vrai risque vient des réglages trop agressifs et d’un volume massif de logs.

6) Comment attraper les requêtes « pas lentes mais trop fréquentes » ?

Abaissez temporairement long_query_time et digérez par temps total, ou utilisez les résumés de statements de Performance Schema pour classer par latence totale. Le slow log seul est basé sur un seuil par conception.

7) Pourquoi mes slow logs sont vides même si slow_query_log=ON ?

Causes courantes : chemin du fichier non inscriptible, AppArmor qui refuse les écritures, seuil trop élevé, ou la charge est dominée par des requêtes rapides et des attentes de verrou qui ne franchissent pas votre seuil. Vérifiez les logs d’erreurs pour des problèmes d’ouverture de fichier et validez le chemin actif avec SHOW VARIABLES.

8) Puis-je utiliser les slow logs pour trouver automatiquement des index manquants ?

Vous pouvez obtenir de bons indices, pas des certitudes. De fortes valeurs de Rows_examined et Using filesort pointent vers des opportunités d’index. Mais la conception d’un index demande de comprendre le mix lecture/écriture, la cardinalité, et les patterns de requêtes. Le slow log vous dit où regarder ; vous devez encore réfléchir.

9) Et les replicas — doivent-ils avoir des réglages différents pour le slow log ?

Souvent oui. Les replicas peuvent subir une journalisation et une instrumentation plus profondes car ils ne sont pas le goulot d’écriture principal. Mais si votre application lit depuis les replicas, traitez-les comme de la production à part entière et surveillez-les de la même façon.

10) Combien de temps conserver les slow logs ?

Conservez-en assez pour couvrir l’investigation d’incident et la détection de régression—souvent quelques jours à une paire de semaines, selon le volume. Pivotez agressivement et digérez des résumés si vous avez besoin de tendances plus longues sans stockage massif.

Conclusion : que faire ensuite, demain matin

Si votre serveur MySQL sur Debian 13 a l’air de mourir en silence, arrêtez de deviner. Activez le journal des requêtes lentes avec un seuil raisonnable, pivotez pour capturer une fenêtre propre, digérez par temps total, et corrigez le principal coupable avec des preuves : EXPLAIN, changements d’index qui correspondent aux motifs d’accès, et validation après le changement.

Puis rendez-le routinier. Gardez la journalisation lente disponible, exécutez des digests pendant les incidents, et traitez les modifications d’index comme des changements de production—avec un plan de rollback et une conscience du coût en écriture. La « requête tueuse silencieuse » est rarement astucieuse. Elle est généralement ennuyeuse, répétée et coûteuse. Ce qui explique exactement pourquoi elle vous échappe.