Il existe deux types de « problèmes de performance » en base de données : ceux que vous pouvez reproduire, et ceux qui disparaissent dès que vous ouvrez un ticket. Le slow query log transforme le second type en premier — car il enregistre ce qui s’est réellement passé, pas ce que quelqu’un jure s’être passé.
Si vous pouvez passer une heure concentrée sur un slow log et ressortir avec un doublement de performance, ce n’est généralement pas parce que vous êtes un magicien. C’est parce que la production a patiemment fait la même opération coûteuse toute la journée, et personne ne l’avait rendue visible.
MySQL vs MariaDB : ce qui diffère réellement pour les slow logs
Sur le papier, MySQL et MariaDB offrent tous deux un slow query log, et vous l’activez de la même manière : fixer un seuil, journaliser les coupables, puis analyser. En pratique, les détails comptent car la « même » requête peut apparaître avec des temps différents, du bruit différent et une visibilité différente selon les valeurs par défaut du moteur, l’instrumentation et le comportement de l’optimiseur.
Ce qui est identique
- Sémantique du slow query log : un enregistrement des instructions dépassant un seuil de temps (ou répondant à d’autres critères comme « n’utilise pas d’index » si vous choisissez cette voie).
- Réglages principaux : slow_query_log, slow_query_log_file, long_query_time, log_queries_not_using_indexes (avec réserves), log_slow_admin_statements.
- Approche d’analyse : regrouper par fingerprint, trier par temps total, puis traquer les pires coupables avec EXPLAIN/EXPLAIN ANALYZE, modifications de schéma/index et réécritures de requêtes.
Ce qui diffère suffisamment pour vous piéger
1) Les différences d’optimiseur et d’exécution apparaissent dans les « requêtes les plus lentes » du slow log. MariaDB et MySQL ont divergé au fil du temps : stratégies d’optimiseur, gestion des tables dérivées et valeurs par défaut peuvent conduire à des plans différents. Cela signifie que vous ne pouvez pas appliquer aveuglément « le correctif utilisé sur MySQL » à MariaDB (ou inversement) sans valider les plans et les estimations de lignes.
2) Les chemins d’instrumentation diffèrent. MySQL 8 s’appuie fortement sur Performance Schema et les vues sys pour l’analyse. MariaDB dispose aussi de Performance Schema, mais dans certains déploiements il est désactivé ou moins exploité ; MariaDB propose également ses propres aides de diagnostic. Dans tous les cas, le slow log est la vérité de base, mais la télémétrie d’accompagnement peut varier.
3) Les valeurs par défaut des versions comptent plus que la marque. Une instance MySQL 5.7 et une MySQL 8.0 peuvent se comporter de façon plus différente que MySQL vs MariaDB dans votre esprit. Même chose pour MariaDB 10.3 vs 10.11. Pour les slow logs, l’important est : avez-vous des horodatages précis, capturez-vous suffisamment de contexte, et pouvez-vous relier une instruction lente à des goulets d’étranglement en ressources ?
Voici une vérité de production inconfortable : la base de données que vous avez est celle que vous devez optimiser. Commencez par les logs, pas par l’idéologie.
Mode d’intervention rapide : trouvez le goulet d’étranglement avant qu’il ne vous trouve
Voici le flux « j’ai 20 minutes avant le prochain appel d’incident ». Ce n’est pas exhaustif. Il est conçu pour trouver rapidement le goulet d’étranglement dominant et vous empêcher de chasser des fantômes.
Première étape : confirmez que le problème vient du temps d’exécution des requêtes, pas des connexions
- Vérifiez si les requêtes sont lentes parce que les requêtes elles-mêmes sont lentes, ou parce que les threads sont saturés, ou parce que vous attendez des verrous.
- Recherchez des symptômes : hausse de « Threads_running », « Questions » qui plafonnent alors que la latence augmente, pics de « Innodb_row_lock_time », ou beaucoup de « Sending data » dans le processlist.
Deuxième étape : identifiez le coupable principal par temps total, pas par la pire requête unique
- Une requête qui prend 1,2s une fois par heure n’est pas votre incident. Une requête qui prend 80ms et s’exécute 50k fois l’est.
- Trier par somme de Query_time dans le rapport du slow log, puis par nombre d’appels.
Troisième étape : classifiez le goulet d’étranglement
Utilisez le motif du coupable pour le ranger dans une catégorie :
- CPU/optimiseur : grand nombre de rows examined, mauvais usage d’index, mauvais ordre de jointure, filesort, tables temporaires.
- E/S : misses du buffer pool, amplification de lecture, tables temporaires sur disque, latence de stockage lente.
- Verrous : temps de verrou élevé, « Waiting for … lock », transactions longues, lignes chaudes.
- Comportement applicatif : requêtes N+1, patterns ORM bavards, pas de pagination, « SELECT * » dans des boucles agressives.
Quatrième étape : choisissez le correctif à risque minimal et rendement maximal
- Créez ou ajustez un index qui correspond au modèle WHERE + JOIN + ORDER BY.
- Réécrivez une requête pour éviter les scans, réduire la largeur des lignes ou pré-agréger.
- Réduisez le périmètre des verrous (transactions plus courtes, ordre d’accès cohérent, meilleurs choix d’isolation).
- Ce n’est qu’ensuite qu’il faut envisager des changements de configuration. Les correctifs de configuration sont réels, mais aussi faciles à appliquer sans comprendre.
Idée paraphrasée (attribuée) : Gene Kim souligne souvent que l’amélioration vient de rendre le travail visible et de réduire le coût d’apprentissage à partir de la production.
Workflow d’une heure avec le slow log pour obtenir de vrais gains
Une heure suffit pour obtenir un gain 2× si vous faites moins « d’analyse » et plus de « triage ». L’objectif n’est pas une compréhension parfaite. L’objectif est une classe de requêtes dominante corrigée en toute sécurité.
Minute 0–10 : assurez-vous que vous journalisez la bonne chose
Commencez par confirmer que le slow log est activé, que le seuil est sensible et que le format du log est analysable. Si le seuil est trop élevé, vous ne verrez pas les requêtes qui tuent par petites touches. S’il est trop bas, vous vous noierez dans le bruit et votre disque protestera.
Seuils recommandés de départ en production :
- long_query_time : 0.1–0.5s pour OLTP, 1–2s pour charges mixtes. Si vous êtes déjà en feu, commencez à 0.2s et ajustez.
- log_queries_not_using_indexes : généralement désactivé au départ. Il produit beaucoup de faux positifs (petites tables, scans légitimes). Activez-le brièvement si vous savez ce que vous faites.
- log_slow_admin_statements : activé, car « ALTER TABLE » peut être l’assassin silencieux.
Minute 10–25 : produisez un rapport top-N et choisissez une victime
Générez un rapport de synthèse (pt-query-digest est le cheval de bataille). Triez par temps total de requête. Choisissez la classe de requêtes en tête qui est (a) fréquente, (b) coûteuse, (c) corrigeable sans réécrire la moitié de l’application.
C’est là que les gens se sabotent : ils choisissent la requête la plus complexe parce qu’elle semble « importante ». Vous voulez la requête ennuyeuse qui s’exécute constamment.
Minute 25–45 : validez avec EXPLAIN ANALYZE et faites un seul index ou réécriture ciblée
Prenez un échantillon représentatif de la requête (pas le pire outlier) et exécutez EXPLAIN et, si disponible, EXPLAIN ANALYZE. Vous cherchez le décalage entre ce que vous pensez se passer et ce qui se passe réellement : scans de table, mauvais ordre de jointure, explosion de rows examined, tables temporaires, filesorts ou usage du mauvais index.
Puis appliquez exactement un changement :
- Créer un index unique (ou ajuster l’ordre d’un index composite).
- Réécrire un prédicat pour le rendre sargable, ex. éviter d’enrouler des colonnes indexées dans des fonctions.
- Ajouter une stratégie LIMIT/pagination, ou scinder une requête en un pré-filtre léger puis une jointure.
Minute 45–60 : prouvez l’amélioration et vérifiez l’absence de régression
Relancez EXPLAIN ANALYZE. Comparez le slow log avant/après (même une courte fenêtre d’échantillonnage aide). Surveillez la latence p95 et les compteurs CPU/IO. Si la requête est devenue plus rapide mais que le système s’est détérioré, vous avez probablement déplacé la charge ailleurs (verrous, temp tables, réplication, disque).
Blague #1 : Le slow query log est le seul collègue qui se souvient de ce qui s’est passé la nuit dernière, et il n’oublie jamais de l’écrire.
Tâches pratiques (commandes, sorties, décisions)
Ci-dessous des tâches pratiques, exécutables. Chacune inclut : une commande, un exemple de sortie, ce que cela signifie et la décision à prendre. Utilisez-les comme une checklist, pas comme un rituel. Le but est de réduire le temps vers la vérité.
Task 1: Confirm slow log is enabled and where it’s writing
cr0x@server:~$ mysql -e "SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'slow_query_log_file'; SHOW VARIABLES LIKE 'long_query_time';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| slow_query_log| ON |
+---------------+-------+
+---------------------+---------------------------+
| Variable_name | Value |
+---------------------+---------------------------+
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+---------------------------+
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 0.200 |
+-----------------+-------+
Ce que cela signifie : La journalisation est active, le seuil est 200ms, le chemin du fichier est connu.
Décision : Si slow_query_log est OFF, activez-le (temporairement si nécessaire). Si long_query_time est 10s, vous n’observez pas votre charge réelle.
Task 2: Enable slow log dynamically (safe, reversible)
cr0x@server:~$ mysql -e "SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.2; SET GLOBAL log_slow_admin_statements = 'ON';"
Ce que cela signifie : Vous avez activé la capture sans redémarrer.
Décision : Faites cela pendant un incident si vous manquez de données. Mais planifiez aussi un changement de configuration plus tard pour persister après un redémarrage.
Task 3: Confirm you’re not accidentally logging everything (file growth sanity check)
cr0x@server:~$ sudo ls -lh /var/log/mysql/slow.log
-rw-r----- 1 mysql adm 1.8G Dec 29 10:12 /var/log/mysql/slow.log
Ce que cela signifie : Le fichier est volumineux. Peut-être que c’est normal. Peut-être qu’il remplit le disque.
Décision : S’il croît trop vite, augmentez légèrement long_query_time ou capturez par échantillonnage aux heures de pointe, puis effectuez une rotation.
Task 4: Rotate the slow log without restarting mysqld
cr0x@server:~$ mysql -e "FLUSH SLOW LOGS;"
Ce que cela signifie : MySQL/MariaDB ferme et rouvre le fichier slow log, permettant à logrotate de faire son travail.
Décision : Utilisez ceci avant de démarrer une fenêtre de capture d’une heure pour que votre jeu de données soit « propre ».
Task 5: Quick skim: top offenders with mysqldumpslow
cr0x@server:~$ mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
Reading mysql slow query log from /var/log/mysql/slow.log
Count: 842 Time=0.38s (319s) Lock=0.00s (0s) Rows=1.0 (842), root[root]@10.0.2.15
SELECT * FROM orders WHERE user_id=N AND status='open' ORDER BY created_at DESC LIMIT N
Count: 55 Time=2.12s (116s) Lock=0.01s (1s) Rows=30000.0 (1650000), app[app]@10.0.3.9
SELECT customer_id, SUM(amount) FROM invoices WHERE created_at >= 'S' GROUP BY customer_id
Ce que cela signifie : La première requête est fréquente et modérément lente ; la seconde est lourde mais moins fréquente. Le temps total compte : 319s vs 116s dans cet échantillon.
Décision : Investiguer le top par temps total d’abord. La requête à 842 appels est probablement le gain 2×.
Task 6: Proper grouping and ranking with pt-query-digest
cr0x@server:~$ pt-query-digest /var/log/mysql/slow.log | sed -n '1,120p'
# 320s total, 842 queries, 0.38s avg, 0.05s 95% 0.89s max
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ============
# 1 0x8F3A1C9E6B0A2C11 319.1240 99.7% 842 0.3790 1.02 SELECT orders
# 2 0xB11D0E2A6F9C0D22 116.6042 36.4% 55 2.1201 1.40 SELECT invoices
# ...
Ce que cela signifie : La requête n°1 domine le temps d’horloge. La corriger vous rapporte le plus gros gain.
Décision : Récupérez l’échantillon exact de requête depuis la section pt-query-digest et faites-le passer à EXPLAIN.
Task 7: Find the exact table/index situation (SHOW CREATE TABLE)
cr0x@server:~$ mysql -e "SHOW CREATE TABLE orders\G"
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`status` varchar(16) NOT NULL,
`created_at` datetime NOT NULL,
`total` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_status` (`status`),
KEY `idx_user` (`user_id`)
) ENGINE=InnoDB
Ce que cela signifie : Il n’y a pas d’index composite correspondant à (user_id, status, created_at). Le tri sur created_at provoque probablement du travail supplémentaire.
Décision : Ajoutez un index composite aligné sur WHERE et ORDER BY.
Task 8: EXPLAIN ANALYZE the slow query (reality check)
cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=123 AND status='open' ORDER BY created_at DESC LIMIT 20\G"
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s) (actual time=210.112..210.140 rows=20 loops=1)
-> Sort: orders.created_at DESC (actual time=210.109..210.114 rows=3200 loops=1)
-> Index lookup on orders using idx_user (user_id=123) (actual time=0.210..205.900 rows=3200 loops=1)
Ce que cela signifie : Il utilise idx_user, puis trie 3200 lignes pour en renvoyer 20. C’est là que le temps est consommé.
Décision : Ajoutez un index pour satisfaire le filtrage et le tri afin que le moteur puisse s’arrêter tôt.
Task 9: Create the index safely (and know what “online” means)
cr0x@server:~$ mysql -e "ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at DESC);"
Query OK, 0 rows affected (12.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
Ce que cela signifie : Index créé. Le temps et le comportement de verrouillage dépendent de la version et des paramètres ; certaines versions le font « en ligne », mais cela consomme toujours des E/S et peut bloquer sur des verrous de métadonnées.
Décision : Exécutez cela pendant une fenêtre de faible trafic si la table est volumineuse. Surveillez les attentes de verrous de métadonnées.
Task 10: Re-run EXPLAIN ANALYZE to verify early exit
cr0x@server:~$ mysql -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id=123 AND status='open' ORDER BY created_at DESC LIMIT 20\G"
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s) (actual time=1.432..1.455 rows=20 loops=1)
-> Index lookup on orders using idx_user_status_created (user_id=123, status='open') (actual time=0.210..1.410 rows=20 loops=1)
Ce que cela signifie : Plus de tri, seulement 20 lignes touchées. Voilà comment vous récupérez de la latence.
Décision : Si le plan n’a pas changé, investiguez : ordre d’index incorrect, incompatibilité de collation/type ou préférence de l’optimiseur.
Task 11: Check for lock contention in the slow log and runtime counters
cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time%'; SHOW GLOBAL STATUS LIKE 'Threads_running';"
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Innodb_row_lock_time | 184223 |
| Innodb_row_lock_time_max | 12000 |
| Innodb_row_lock_waits | 912 |
+----------------------------+--------+
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 48 |
+-----------------+-------+
Ce que cela signifie : Il y a des attentes de verrou significatives. Threads_running est élevé, indiquant une pression de concurrence.
Décision : Si le temps de verrou représente une grosse part de Query_time dans les entrées du slow log, concentrez-vous sur la portée des transactions et les lignes chaudes, pas sur les index.
Task 12: Inspect live queries and spot “Waiting for … lock” quickly
cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;" | sed -n '1,18p'
Id User Host db Command Time State Info
1209 app 10.0.3.9:53122 prod Query 12 Waiting for table metadata lock ALTER TABLE orders ADD COLUMN note varchar(64)
1211 app 10.0.3.7:60111 prod Query 9 Sending data SELECT * FROM orders WHERE user_id=123 AND status='open' ORDER BY created_at DESC LIMIT 20
1220 app 10.0.3.6:42290 prod Query 8 Sending data SELECT * FROM orders WHERE user_id=456 AND status='open' ORDER BY created_at DESC LIMIT 20
Ce que cela signifie : Un ALTER TABLE bloque. Ce n’est pas du réglage de requête lente ; c’est de la gestion de changement.
Décision : Arrêtez le DDL (si c’est sans risque) ou replanifiez-le. Évitez les répétitions avec des pratiques de migration disciplinées.
Task 13: Check InnoDB buffer pool health (are we reading from disk?)
cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+-----------+
| Variable_name | Value |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests | 982341234 |
| Innodb_buffer_pool_reads | 19348231 |
+---------------------------------------+-----------+
Ce que cela signifie : Les lectures depuis le disque sont non négligeables. Le ratio compte plus que le nombre brut ; beaucoup de lectures disque peuvent être acceptables sur de larges jeux de travail, mais cela peut aussi indiquer un buffer pool sous-dimensionné ou des scans problématiques.
Décision : Si l’ensemble de travail tient en mémoire mais que vous lisez beaucoup, vous avez probablement de mauvais index ou des schémas d’accès inefficaces. Si l’ensemble de travail dépasse réellement la mémoire, priorisez l’optimisation des principaux scans et envisagez un dimensionnement mémoire.
Task 14: Check temporary tables and whether they spill to disk
cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Created_tmp_disk_tables | 48291 |
| Created_tmp_tables | 90210 |
+-------------------------+--------+
Ce que cela signifie : Beaucoup de tables temporaires atteignent le disque. Cela corrèle avec des GROUP BY/ORDER BY sur des colonnes non indexées, des lignes larges ou des paramètres mémoire tmp table insuffisants.
Décision : Préférez d’abord des corrections de requêtes et d’index. Si la requête est intrinsèquement lourde, ajustez tmp_table_size/max_heap_table_size prudemment et validez l’impact mémoire.
Task 15: Validate storage latency during the slow window (don’t guess)
cr0x@server:~$ iostat -xz 1 5
Linux 6.5.0 (server) 12/29/2025 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
22.14 0.00 7.12 18.33 0.00 52.41
Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s w_await aqu-sz %util
nvme0n1 322.0 18232.0 0.0 0.00 18.40 56.60 90.0 9120.0 4.10 6.32 78.00
Ce que cela signifie : r_await ~18ms suggère que les lectures de stockage ne sont pas « rapides ». Si votre charge attend des lectures à faible milliseconde, cela contribue directement au temps de requête quand il y a des misses du buffer pool.
Décision : Si la latence est élevée, vous pouvez toujours obtenir un 2× en réduisant les lectures (indexation, meilleur usage de LIMIT). Enquêtez aussi sur des voisins bruyants, une dégradation RAID, des pics fsync et le comportement de checkpointing.
Task 16: Check binary log and replication lag signals (fixes can move pain)
cr0x@server:~$ mysql -e "SHOW MASTER STATUS\G; SHOW SLAVE STATUS\G" | sed -n '1,40p'
*************************** 1. row ***************************
File: mysql-bin.002341
Position: 91822310
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 47
Ce que cela signifie : La réplication est en retard. Des requêtes lourdes ou des DDL peuvent aggraver le retard. Certaines « optimisations » augmentent l’amplification d’écritures (nouveaux index) et ralentissent temporairement la réplication.
Décision : Planifiez les constructions d’index soigneusement et surveillez le retard. Si le retard est critique pour le business, envisagez de limiter les migrations ou d’utiliser des outils de changement de schéma en ligne.
Schémas de slow log qui achètent généralement un 2×
La plupart des gains 2× ne sont pas exotiques. Ils consistent à supprimer du travail accidentel. Voici les motifs qui apparaissent dans les slow logs à travers des flottes MySQL et MariaDB.
1) « ORDER BY … LIMIT » sans index correspondant
C’est le classique : filtrer par certaines colonnes, trier par timestamp, LIMIT 20. Sans index composite qui correspond au filtre + tri, le moteur lit beaucoup de lignes et les trie. Le slow log montre un temps modéré par appel, un comptage massif et un « Rows_examined » beaucoup plus grand que « Rows_sent ».
Correctif : Un index composite avec d’abord les prédicats d’égalité, puis la colonne de tri. Utilisez des index DESC là où c’est supporté et pertinent ; sinon le moteur peut lire en sens inverse dans certains cas, mais ne les supposez pas. Validez avec EXPLAIN ANALYZE.
2) Prédicats non sargables
Si vous avez WHERE DATE(created_at)=… ou WHERE LOWER(email)=…, vous forcez l’optimiseur dans un coin. Il ne peut pas utiliser l’index efficacement parce que vous avez entouré la colonne indexée d’une fonction.
Correctif : Réécrivez en une plage (created_at >= … AND created_at < …) ou stockez des valeurs normalisées dans une colonne générée indexée (selon la version), ou normalisez à l’écriture.
3) Grandes listes IN() et « jointures faites par l’application »
Les slow logs aiment montrer des requêtes comme WHERE id IN (…milliers…). Souvent c’est un pattern ORM ou une tentative de batch qui a dégénéré. Cela peut provoquer un gros overhead de parsing, de mauvais plans et de grandes structures temporaires.
Correctif : Utilisez une table temporaire, une jointure contre une vraie table, ou repensez le pattern d’accès. Si nécessaire, limitez la taille des lots et mesurez.
4) Mises à jour de lignes chaudes et attentes de verrous
Les problèmes de verrouillage ressemblent à une « lenteur aléatoire » jusqu’à ce que vous observiez Lock_time dans les entrées du slow log et des états d’attente dans le processlist. Une seule ligne compteur chaude peut ralentir tout le service.
Correctif : Réduisez la contention : shardez les compteurs, utilisez INSERT … ON DUPLICATE KEY sur des clés distribuées, ou déplacez le chemin critique vers quelque chose conçu pour une forte contention en écriture. Raccourcissez aussi les transactions et assurez un ordre de verrouillage cohérent.
5) GROUP BY qui déborde sur le disque
Si vous agrégerez de larges ensembles et que les tables temporaires débordent sur disque, la latence de stockage devient votre latence de requête. Le slow log montre un grand Query_time avec une utilisation CPU relativement faible, et vos compteurs tmp disk tables augmentent.
Correctif : Ajoutez des index qui supportent l’agrégation, réduisez la largeur des lignes, pré-aggrégez ou déplacez les charges de reporting hors de l’instance OLTP principale.
6) « SELECT * » sur des tables larges
Les lectures de lignes larges coûtent en mémoire, buffer pool, réseau et CPU. Elles sabotent aussi les index couvrants. Le slow log montre des requêtes qui récupèrent des colonnes que l’application n’utilise jamais.
Correctif : Sélectionnez seulement les colonnes nécessaires. Si vous voulez de la vitesse, arrêtez de transporter les meubles quand vous n’avez besoin que des clés.
Trois mini-récits d’entreprise issus du terrain
Mini-récit 1 : L’incident causé par une mauvaise hypothèse
Une entreprise moyenne exploitait une plateforme e‑commerce avec un primaire MySQL et quelques réplicas. L’équipe applicative avait ajouté « commandes récentes » au tableau de bord utilisateur. Cela semblait inoffensif : filtrer par user_id, status, trier par created_at, LIMIT 20. La requête était rapide en staging, parce que staging avait des tables petites et des caches chauds — comme tous les environnements de staging, c’était un mensonge réconfortant.
En production, les latences p95 ont doublé en pointe. L’ingénieur en astreinte a fait l’habituel : scalé les pods applicatifs, redémarré quelques services, regardé la situation empirer. Le CPU sur la BDD montait, mais sans atteindre le plafond. Le disque n’était pas saturé. C’était « mystérieux ».
La mauvaise hypothèse était simple : « LIMIT 20 signifie qu’on ne lit que 20 lignes. » Sans index composite adapté, le moteur lisait des milliers de lignes par utilisateur, les triant, puis retournait 20. C’était la même histoire répétée pour des milliers d’utilisateurs. Le slow query log montrait une requête qui n’était pas individuellement terrifiante — ~350ms — mais qui tournait constamment.
Ils ont ajouté un index composite (user_id, status, created_at). La requête est tombée à quelques millisecondes. L’incident s’est terminé non pas par une session de tuning héroïque, mais par un index et un rappel : SQL ne lit pas dans vos pensées.
L’action de suivi était plus intéressante : ils ont modifié leur checklist de déploiement pour exiger la capture d’un échantillon slow log de 15 minutes après toute fonctionnalité qui ajoute un nouveau chemin de requête. Pas parce qu’ils aiment les processus. Parce qu’ils aiment dormir.
Mini-récit 2 : L’optimisation qui s’est retournée contre eux
Une autre organisation utilisait MariaDB pour un système de facturation. Des requêtes de reporting frappaient le primaire durant les heures d’ouverture. Quelqu’un a proposé un correctif rapide : « On active log_queries_not_using_indexes et on ajoute des index pour tout ce qui apparaît. » La logique semblait nette. Elle a aussi transformé le slow log en une lance d’incendie.
En une journée, ils avaient créé plusieurs nouveaux index, y compris des composites larges sur des colonnes peu sélectives. Les écritures ont ralenti. Le churn du buffer pool a augmenté. Le changement a aussi augmenté le retard de réplication car chaque insert/update devait maintenant maintenir plus de structures d’index. Pendant ce temps, les slow queries ne s’amélioraient guère — parce que les pires coupables étaient des scans complets légitimes sur des partitions mensuelles pour des synthèses financières. Le logging « not using indexes » accusait la base de données de faire exactement ce que la requête demandait.
Le retour de manivelle a été subtil : l’équipe a perdu du temps à courir après des avertissements « index manquant » au lieu d’aborder la forme de la charge. La bonne solution était de déplacer le reporting vers un réplica (ou une voie analytique dédiée), ajouter quelques index ciblés et modifier quelques requêtes pour pré-agréger dans des tables de synthèse.
Ils ont fini par annuler plusieurs index, ce qui est toujours une journée amusante car supprimer des index peut aussi prendre du temps et des E/S. Leçon : ne laissez pas un drapeau de diagnostic devenir une exigence produit.
Blague #2 : Activer toutes les options de journalisation « pour la visibilité » revient à remplacer votre détecteur de fumée par une machine à brouillard.
Mini-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise
Une société SaaS utilisait MySQL 8 pour des charges multi‑tenants. Ils avaient une pratique dont personne ne se vantait : le slow query log était toujours activé à un seuil modéré, rotatif chaque heure et résumé quotidiennement. Pas de drame. Pas de « on l’activera si besoin ». Il était simplement là, capturant la vérité en silence.
Un après-midi, la latence a augmenté. Pas catastrophique, mais perceptible. L’astreinte n’a pas deviné. Ils ont extrait la dernière heure de slow log, exécuté pt-query-digest et vu une nouvelle empreinte de requête apparaître en tête. C’était une jointure générée par un ORM avec un prédicat inattendu sur une colonne à faible cardinalité, provoquant une explosion de lignes.
Parce qu’ils avaient des résumés quotidiens de référence, ils ont pu prouver que la requête était nouvelle et quantifier l’impact sans spéculation. Ils avaient aussi une politique : tout changement d’index nécessitait un EXPLAIN ANALYZE avant/après et un contrôle rapide du retard de réplication. Cette politique n’est pas élégante. Elle est efficace.
Ils ont envoyé une petite réécriture de requête et un index composite étroit. La latence est revenue à la normale. La note post-incident fut courte : « slow log l’a attrapée ; correctif validé ; pas de dommages collatéraux. » C’est le rêve — ennuyeux, correct, reproductible.
Erreurs fréquentes : symptôme → cause racine → correctif
Cette section est volontairement précise. Si vous pouvez mapper un symptôme à une cause racine rapidement, vous récupérez votre vie.
1) Symptom: Slow log shows high Query_time but low Rows_examined
Cause racine : Attente, pas scan — verrous, pression fsync ou stalls réseau. Souvent Lock_time est élevé, ou la latence de stockage spike.
Correctif : Vérifiez Lock_time dans les entrées du slow log, les états de SHOW PROCESSLIST et les compteurs de verrous InnoDB. Pour l’I/O, corrélez avec iostat et le checkpointing. N’ajoutez pas d’index « parce que c’est lent ».
2) Symptom: Queries fast on replica, slow on primary
Cause racine : Le primaire fait du travail supplémentaire : écritures, fsync, churn du buffer pool ou contention de verrous. Possible aussi : matériel différent ou dérive de configuration.
Correctif : Comparez les variables de config et les statistiques du buffer pool. Validez la charge : exécutez-vous des rapports sur le primaire ? Arrêtez cela en priorité.
3) Symptom: Top slow query is a simple SELECT with ORDER BY + LIMIT
Cause racine : Index composite manquant qui correspond au filtre et au tri, provoquant filesort et scan.
Correctif : Ajoutez un index avec les prédicats d’égalité en premier, puis la colonne de tri. Confirmez avec EXPLAIN ANALYZE que le tri disparaît et que les lignes touchées chutent.
4) Symptom: Sudden spike in slow queries after deployment, but only for some users/tenants
Cause racine : Biais des données. Le plan est correct pour les tenants typiques mais catastrophique pour les « gros » clients. Les statistiques peuvent tromper l’optimiseur.
Correctif : Testez avec des tailles de tenants représentatives. Envisagez de meilleurs index, des modifications de requête ou de séparer les gros tenants. Mettez à jour les statistiques et validez les plans sur des cas biaisés.
5) Symptom: Many “Created_tmp_disk_tables” and slow GROUP BY
Cause racine : Tables temporaires qui débordent sur disque à cause d’index insuffisants, lignes larges, grands ensembles de résultats ou limites mémoire.
Correctif : Réduisez la largeur des lignes, ajoutez des index de support, pré-agrégez, et seulement ensuite envisagez de tuner tmp_table_size/max_heap_table_size en budgétisant la mémoire.
6) Symptom: slow log file is huge, analysis is painful, disk usage climbs
Cause racine : long_query_time trop bas pour une activation permanente, ou un flux de requêtes incontrôlé, ou pas de rotation.
Correctif : Implémentez rotation et rétention ; capturez par échantillon pendant les pics ; ajustez le seuil. Utilisez pt-query-digest sur une fenêtre temporelle bornée.
7) Symptom: After adding an index, reads got faster but replication lag worsened
Cause racine : La maintenance d’index additionnels a augmenté le coût des écritures. Sur les réplicas, l’application des événements devient plus lourde.
Correctif : Gardez les index minimaux et ciblés. Ajoutez celui qui correspond à votre coupable principal. Pour les migrations, bridez et surveillez le lag ; envisagez la planification ou le déchargement.
Listes de vérification / plan étape par étape
Checklist: capture one hour of useful slow logs (production-safe)
- Confirmez slow log activé et emplacement du fichier (Task 1).
- Réglez long_query_time sur un seuil utile pour votre charge (Task 2).
- FLUSH SLOW LOGS pour commencer avec un fichier propre (Task 4).
- Capturez pendant une heure représentative de charge, pas pendant un creux.
- Rotatez à la fin (Task 4 à nouveau) pour que l’analyse soit bornée.
- Enregistrez le contexte : volume de trafic, versions, migrations en cours.
Checklist: turn the hour into a ranked action list
- Exécutez pt-query-digest et triez par temps total de réponse (Task 6).
- Choisissez une empreinte de requête en tête, fréquente et corrigeable.
- Récupérez le schéma et les index existants des tables impliquées (Task 7).
- Exécutez EXPLAIN ANALYZE sur un exemple représentatif (Task 8).
- Classifiez le goulet : scanning, tri, temp tables, verrouillage, I/O.
- Proposez un changement minimal : un index ou une réécriture.
- Implémentez prudemment ; surveillez les verrous de métadonnées et le retard de réplication (Tasks 12 et 16).
- Relancez EXPLAIN ANALYZE et comparez le classement dans le slow log après le changement (Task 10 plus digest).
Checklist: safety rails (because you will be tempted)
- N’ajoutez pas des « covering indexes » par défaut. Les index larges gonflent le buffer pool et ralentissent les écritures.
- N’ajustez pas des réglages globaux avant d’avoir identifié une requête comme coupable principal.
- Ne faites pas confiance aux performances de staging si la distribution des données en production diffère.
- N’exécutez pas de DDL en période de pointe sauf si vous avez prouvé le comportement en ligne pour votre version et la forme de la table.
Faits intéressants et contexte historique
Un peu de contexte aide, car les gens répètent des mythes avec assurance, et la production punit cette habitude.
- MariaDB a commencé comme un fork de MySQL après l’acquisition de Sun par Oracle (2009). Le récit « c’est la même chose » était vrai au début ; la divergence est réelle aujourd’hui.
- Le Performance Schema de MySQL a mûri significativement avec les versions majeures. MySQL moderne s’appuie dessus pour le diagnostic ; les déploiements plus anciens utilisaient davantage les slow logs et outils externes.
- Le slow query log précède la mode actuelle de l’observabilité. C’est l’une des plus anciennes fonctionnalités « écrivez simplement la vérité » de l’écosystème MySQL.
- pt-query-digest (Percona Toolkit) est devenu populaire car les slow logs sont verbeux mais pas immédiatement exploitables. Les digests transforment un journal en une liste de tâches classée.
- MySQL a supprimé le legacy Query Cache en 8.0. Il était célèbre pour aider les benchmarks et nuire à la concurrence réelle. Si quelqu’un suggère de l’activer « pour la vitesse », demandez-lui en quelle année il vit.
- InnoDB est devenu le moteur par défaut depuis longtemps, remplaçant MyISAM dans la plupart des déploiements sérieux. Beaucoup de correctifs de slow query supposent des sémantiques transactionnelles et du verrouillage au niveau ligne.
- EXPLAIN ANALYZE est un cadeau relativement récent comparé à EXPLAIN simple. Il mesure l’exécution réelle, ce qui est inestimable quand les estimations de l’optimiseur sont obstinément fausses.
- Les verrous de métadonnées surprennent les équipes car ils ne sont pas des « slow queries » jusqu’à ce qu’ils le soient. Les DDL peuvent bloquer les lectures/écritures et apparaître comme des requêtes applicatives lentes.
FAQ
1) Dois-je utiliser le slow query log ou Performance Schema ?
Utilisez le slow query log comme vérité de terrain pour « ce qui a été lent ». Utilisez Performance Schema pour « pourquoi c’était lent » (attentes, stages, signaux type CPU). Si vous ne pouvez en choisir qu’un, choisissez d’abord le slow log.
2) Quel long_query_time choisir pour de l’OLTP ?
Commencez à 0.2s sur un système chargé si vous voulez des données exploitables rapidement. Si c’est trop bruyant, montez à 0.5s. Si vous le réglez sur 2–10 secondes, vous ne capturerez que les catastrophes, pas les gaspillages chroniques.
3) Est-ce une bonne idée d’activer log_queries_not_using_indexes ?
En permanence : généralement non. En expérience courte et délibérée : parfois. Il signale des scans légitimes et encourage la prolifération d’index. Utilisez-le quand vous savez déjà que les scans sont accidentels, pas quand vous êtes encore en phase de diagnostic.
4) Pourquoi la « même » requête a-t-elle des performances différentes sur MySQL vs MariaDB ?
Différentes décisions d’optimiseur, valeurs par défaut différentes, comportement des statistiques différents et combinaisons moteur/version distinctes. Traitez chaque serveur comme son propre système : validez les plans, ne comptez pas sur la mémoire tribale.
5) Un index peut-il ralentir une requête ?
Oui. L’optimiseur peut choisir un index moins bon, ou la maintenance des index peut ralentir les écritures au point que la latence globale augmente. C’est pourquoi vous vérifiez avec EXPLAIN ANALYZE et surveillez les métriques système, pas seulement le temps d’une requête.
6) Que faire si mon slow log est rempli de statements d’administration comme ALTER TABLE ?
Alors votre goulet est la gestion des changements. Planifiez les DDL, utilisez des techniques en ligne adaptées à votre version et empêchez les migrations surprises en période de pointe. Le correctif est disciplinaire et opérationnel.
7) Comment savoir si mon problème est I/O disque ou requêtes mal faites ?
Corrélez : un Innodb_buffer_pool_reads élevé plus des points de forte latence stockage indiquent une sensibilité I/O. Mais même dans ce cas, le meilleur premier correctif est généralement de réduire les lectures via des index et la forme des requêtes. Une montée en hardware est valable, mais ce n’est pas un substitut à l’arrêt du gaspillage.
8) Comment transformer une heure de logs en un gain 2× de façon fiable ?
Classez par temps total, choisissez un coupable fréquent, validez le plan avec EXPLAIN ANALYZE, appliquez un changement minimal d’index/requête, puis re-mesurez. Évitez le « tuning par vibes » (changements multiples simultanés sans preuve).
9) Activer le slow query log nuit-il aux performances ?
Cela ajoute un overhead, mais pour la plupart des systèmes c’est gérable quand c’est configuré correctement. Le risque plus grand est la croissance disque si vous journalisez trop sans rotation. Si la performance est extrêmement sensible, capturez par échantillonnage aux heures de pointe et rotaez agressivement.
10) Mes slow queries affichent toutes « Sending data » dans le processlist. Et maintenant ?
« Sending data » signifie souvent que la requête lit des lignes et les traite (pas nécessairement envoi réseau). Regardez Rows_examined, l’usage d’index et si vous scannez/triez. Cela peut aussi masquer des attentes I/O lors de la lecture de pages.
Prochaines étapes à faire aujourd’hui
Faites la configuration ennuyeuse une fois, puis accumulez les gains :
- Activez le slow query logging à un seuil sensé et gardez-le activé avec rotation. Faites-en partie intégrante du système, pas un levier d’urgence.
- Automatisez un digest quotidien (même un cron qui lance pt-query-digest et stocke les 20 empreintes principales). Les tendances comptent.
- Adoptez la règle du changement unique en incident : un index ou une réécriture de requête, validé par EXPLAIN ANALYZE et une vérification rapide du lag/locks.
- Faites de « rows examined vs rows returned » une habitude. C’est l’indicateur le plus rapide de travail gaspillé.
- Notez vos 5 empreintes de requêtes principales et traitez-les comme des dépendances de production. Parce que c’en sont.
Si vous voulez le gain 2×, ne cherchez pas des réglages magiques. Ouvrez le slow log, trouvez la classe de requêtes dominante par temps total et supprimez son travail accidentel. La production n’a pas besoin d’héroïsme. Elle a besoin de moins de lectures inutiles.