Superviser et maintenir MySQL : Performance Schema, requêtes lentes et OPTIMIZE

Un MySQL en production qui ralentit progressivement : requêtes passant de 2 ms à 200 ms, connexions qui s'accumulent, tables InnoDB qui grossissent sans raison apparente, c'est le scénario classique quand personne ne surveille. MySQL embarque un système d'instrumentation complet, Performance Schema, sys schema et slow query log, qui permet de diagnostiquer chaque problème, mais encore faut-il savoir quoi regarder et quand agir.

Ce guide suit une démarche observer → identifier → expliquer → corriger. Chaque commande est testée sur un lab réel (MySQL 8.4 LTS, Debian 12, base lab_mysql avec des tables clients, commandes et logs). Vous saurez lire les vues Performance Schema, interpréter un plan d'exécution, détecter les tables fragmentées et utiliser OPTIMIZE TABLE, ANALYZE TABLE et CHECK TABLE au bon moment.

Version utilisée

Ce guide s'appuie sur MySQL 8.4 LTS (avril 2024). Nouveautés notables : EXPLAIN INTO pour stocker les plans dans une variable, mise à jour automatique des histogrammes (AUTO UPDATE), Performance Schema enrichi avec des métriques de latence plus granulaires.

Ce que vous allez apprendre

• Diagnostiquer l'activité en cours avec SHOW PROCESSLIST et performance_schema.processlist
• Identifier les requêtes lentes avec le slow query log et sys.statement_analysis
• Lire un plan EXPLAIN ANALYZE et comprendre ALL, ref, range, Index Scan
• Surveiller InnoDB : buffer pool hit ratio, redo log, transactions et purge lag
• Maintenir les performances avec OPTIMIZE TABLE, ANALYZE TABLE et CHECK TABLE
• Intégrer MySQL dans un monitoring Prometheus/Grafana

Dans quel contexte ?

Vous avez besoin de superviser et maintenir MySQL dans ces situations :

• Votre application ralentit progressivement et vous soupçonnez la base de données
• Vous voulez mettre en place une supervision proactive avant que les problèmes n'apparaissent
• Des requêtes lentes apparaissent dans les logs applicatifs mais vous ne savez pas lesquelles côté base
• Vous devez identifier les requêtes les plus coûteuses pour savoir où optimiser (index manquants, full table scans)
• Vous préparez un plan de maintenance pour une base en production (fragmentation, statistiques obsolètes)
• Vous debuggez un problème de verrous ou de connexions qui s'accumulent

Ce guide ne couvre pas…

Trois sujets voisins ont leur propre page, pour éviter de traiter ici des réglages qui n'ont rien à voir avec l'observation. Consultez-les si votre besoin porte sur le dimensionnement, la sauvegarde ou les droits d'accès plutôt que sur le diagnostic de performance.

Configuration MySQL InnoDB buffer pool, connexions, binary log et SET PERSIST

Sauvegarde et restauration mysqldump, MySQL Shell, XtraBackup et PITR

Sécurisation MySQL caching_sha2_password, TLS, rôles et audit

Prérequis

• MySQL 8.4 installé et le service actif, voir le guide d'installation
• Accès au compte root MySQL
• Connaître les bases du client mysql, voir le guide de prise en main
• Une base avec des données pour observer quelque chose (le lab utilise lab_mysql avec les tables clients, commandes, logs)

La démarche : observer → identifier → expliquer → corriger

Diagnostiquer un problème de performance MySQL suit toujours le même ordre :

1. Observer, que se passe-t-il en ce moment ? (SHOW PROCESSLIST, connexions, verrous)
2. Identifier, quelles requêtes consomment le plus ? (slow query log, sys.statement_analysis)
3. Expliquer, pourquoi cette requête est-elle lente ? (EXPLAIN ANALYZE, plan d'exécution)
4. Corriger, ajouter un index, réécrire la requête, ou maintenir (OPTIMIZE TABLE, ANALYZE TABLE)

Ne sautez pas d'étape. Un OPTIMIZE TABLE lancé à l'aveugle sur une table de production verrouille tout en écriture avec l'algorithme par défaut. Un index ajouté sans avoir lu le plan peut aggraver la situation.

Observer l'activité du serveur

Cette première étape répond à une seule question : que fait le serveur en ce moment ? Toutes les commandes de cette section lisent un état instantané et ne modifient rien, vous pouvez les lancer en production sans risque. Elles se répartissent en deux familles : les vues de connexions, qui montrent qui est là et depuis combien de temps, et les compteurs globaux, cumulés depuis le démarrage du serveur, qu'il faut donc lire comme des tendances et non comme des valeurs instantanées.

SHOW PROCESSLIST et performance_schema.processlist

SHOW PROCESSLIST est le premier réflexe pour voir toutes les connexions actives :

SHOW PROCESSLIST;

+----+-----------------+-----------+-----------+---------+------+------------------------+------------------+
| Id | User            | Host      | db        | Command | Time | State                  | Info             |
+----+-----------------+-----------+-----------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL      | Daemon  |  842 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | lab_mysql | Query   |    0 | init                   | SHOW PROCESSLIST |
+----+-----------------+-----------+-----------+---------+------+------------------------+------------------+

Limitation : SHOW PROCESSLIST tronque la colonne Info à 100 caractères. Utilisez SHOW FULL PROCESSLIST ou la vue Performance Schema pour les requêtes complètes :

SELECT ID,
       USER,
       HOST,
       DB,
       COMMAND,
       TIME AS time_sec,
       STATE,
       LEFT(INFO, 80) AS query
FROM performance_schema.processlist
WHERE COMMAND <> 'Daemon';

Colonne	Signification
Id	Identifiant de la connexion (utilisable avec KILL)
Command	Type d'opération (Query, Sleep, Connect, Daemon)
Time	Secondes depuis le début de l'état courant
State	Étape interne (sending data, executing, Waiting for lock…)
Info	Requête en cours d'exécution

Glissez pour voir

Les connexions Sleep : le problème courant

Compter les connexions par état donne en une requête la santé du pool applicatif. Une majorité de Sleep n'est pas anormale en soi, mais leur nombre doit rester stable dans le temps : s'il monte sans redescendre, les connexions ne sont pas rendues.

SELECT COMMAND, COUNT(*) AS cnt
FROM performance_schema.processlist
GROUP BY COMMAND;

Command	Signification	Action si trop nombreux
Query	Exécute une requête	Normal, vérifier si trop long
Sleep	Connecté, ne fait rien	Pool de connexions mal configuré ou wait_timeout trop élevé
Daemon	Processus interne (event_scheduler)	Normal

Glissez pour voir

Les connexions Sleep s'accumulent

Une application qui ouvre des connexions sans les fermer remplit max_connections avec des connexions Sleep. Réduisez wait_timeout (défaut : 28800 secondes = 8 heures !) pour nettoyer automatiquement :

SET PERSIST wait_timeout = 300; -- 5 minutes
SET PERSIST interactive_timeout = 300;

Tuer une requête ou connexion

L'identifiant à passer est celui de la colonne Id relevée juste avant. La nuance entre les deux formes compte : KILL QUERY interrompt la requête en laissant la session ouverte, KILL CONNECTION ferme la session, ce qui provoque un rollback de la transaction en cours et une erreur côté application.

-- Annuler la requête en cours (connexion reste ouverte)
KILL QUERY 42;

-- Tuer la connexion entière
KILL CONNECTION 42;

-- Synonyme court
KILL 42;

SHOW GLOBAL STATUS : les compteurs clés

SHOW GLOBAL STATUS expose des centaines de compteurs cumulatifs. Voici les plus utiles :

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected', 'Threads_running',
  'Max_used_connections', 'Connections',
  'Slow_queries', 'Questions',
  'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
  'Innodb_row_lock_waits', 'Innodb_row_lock_time'
);

Compteur	Signification
Threads_connected	Connexions actuellement ouvertes
Threads_running	Connexions exécutant une requête en ce moment
Max_used_connections	Pic de connexions simultanées depuis le démarrage
Slow_queries	Nombre total de requêtes lentes détectées
Innodb_buffer_pool_read_requests	Lectures logiques (depuis le buffer pool)
Innodb_buffer_pool_reads	Lectures physiques (depuis le disque)
Innodb_row_lock_waits	Attentes de verrou de ligne
Innodb_row_lock_time	Temps total passé en attente de verrous (ms)

Glissez pour voir

Le buffer pool hit ratio se calcule ainsi :

SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_ratio;

Un ratio supérieur à 99 % est normal pour une charge OLTP. En dessous de 95 %, augmentez innodb_buffer_pool_size, voir le guide de configuration.

SHOW ENGINE INNODB STATUS : le diagnostic instantané

SHOW ENGINE INNODB STATUS\G affiche un rapport complet sur l'état interne d'InnoDB : transactions actives, verrous, buffer pool, I/O, redo log. C'est l'outil de diagnostic le plus riche de MySQL :

SHOW ENGINE INNODB STATUS\G

Les sections clés du rapport :

Section	Ce qu'elle montre
SEMAPHORES	Attentes de verrous internes (mutex, rw-lock)
TRANSACTIONS	Transactions actives, historique, purge
FILE I/O	Threads I/O, opérations en attente
BUFFER POOL AND MEMORY	Taille du pool, pages free/dirty/data, hit rate
ROW OPERATIONS	INSERT/UPDATE/DELETE par seconde
LOG	Position du redo log, séquence LSN
DEADLOCKS	Dernier deadlock détecté par InnoDB

Glissez pour voir

Historique des deadlocks

SHOW ENGINE INNODB STATUS n'affiche que le dernier deadlock. Pour enregistrer tous les deadlocks dans le error log, activez innodb_print_all_deadlocks = ON, indispensable en production pour analyser les deadlocks récurrents.

Le slow query log

Le slow query log enregistre toutes les requêtes dont le temps d'exécution dépasse un seuil. C'est l'équivalent MySQL de la supervision par pg_stat_statements en PostgreSQL, mais sous forme de fichier de log.

Activer le slow query log

SET PERSIST applique le réglage immédiatement et l'écrit dans mysqld-auto.cnf, ce qui le rend permanent sans toucher au fichier de configuration principal ni redémarrer le service. Le fichier de log doit être accessible en écriture au compte système mysql, d'où le choix du répertoire de données.

-- Activer sans redémarrer (SET PERSIST pour survivre au reboot)
SET PERSIST slow_query_log = ON;
SET PERSIST slow_query_log_file = '/var/lib/mysql/slow.log';
SET PERSIST long_query_time = 1; -- seuil en secondes

Vérification :

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

+---------------------+---------------------------+
| Variable_name       | Value                     |
+---------------------+---------------------------+
| slow_query_log      | ON                        |
| slow_query_log_file | /var/lib/mysql/slow.log   |
+---------------------+---------------------------+

+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| long_query_time   | 1.000000 |
+-------------------+----------+

long_query_time : le seuil

Ce seuil, exprimé en secondes, accepte les décimales. Il détermine à lui seul le volume du fichier de log, et un réglage trop bas sur une base chargée peut saturer le disque en quelques heures : commencez haut, puis descendez.

Valeur	Usage
10 (défaut)	Trop élevé, ne capture que les requêtes catastrophiques
1	Bon point de départ pour la production
0.5	Pour un audit approfondi
0	Capture toutes les requêtes, attention à l'espace disque et aux I/O

Glissez pour voir

log_queries_not_using_indexes

Active la capture des requêtes qui ne utilisent aucun index, même si elles sont rapides :

SET PERSIST log_queries_not_using_indexes = ON;

-- Limiter le nombre de logs par minute pour éviter l'explosion
SET PERSIST log_throttle_queries_not_using_indexes = 10;

C'est un excellent moyen de détecter les full table scans sur des tables qui grossiront : une requête rapide aujourd'hui sur 1 000 lignes deviendra lente sur 1 million.

Analyser les logs avec mysqldumpslow

mysqldumpslow agrège les entrées du slow log et les classe par fréquence, temps moyen ou total :

# Top 10 des requêtes par temps total
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# Top 10 par nombre d'appels
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# Top 10 par temps moyen
mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

Count: 12  Time=2.34s (28s)  Lock=0.00s (0s)  Rows=90000.0 (1080000)
  SELECT * FROM logs WHERE action = 'S'

Lecture : cette requête a été exécutée 12 fois, avec un temps moyen de 2,34 s et un temps total de 28 s. C'est la priorité d'optimisation.

Différence avec PostgreSQL

PostgreSQL utilise pg_stat_statements (extension mémoire, statistiques agrégées en temps réel). MySQL utilise le slow query log (fichier sur disque) + mysqldumpslow pour l'analyse. L'équivalent en mémoire côté MySQL est performance_schema.events_statements_summary_by_digest.

Performance Schema

Le slow query log ne voit que ce qui dépasse un seuil, et seulement pour les requêtes. Performance Schema couvre bien plus large et travaille en mémoire, sans écrire sur disque. C'est la source de données sur laquelle s'appuient ensuite le sys schema et la plupart des exportateurs de métriques : comprendre son organisation en instruments et consumers évite de chercher au mauvais endroit.

Principe et activation

Performance Schema est le système d'instrumentation interne de MySQL. Il collecte des statistiques sur tout : requêtes, verrous, I/O, mémoire, threads, étapes internes. Activé par défaut depuis MySQL 5.6, sa consommation mémoire dépend du nombre d'instruments et de consumers activés, vérifiez avec SHOW ENGINE PERFORMANCE_SCHEMA STATUS.

SHOW VARIABLES LIKE 'performance_schema';

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

Les instruments et les consumers

Performance Schema fonctionne en deux couches :

• Instruments : les points de mesure (requêtes, verrous, I/O, mémoire…)
• Consumers : les tables qui stockent les données collectées

-- Nombre d'instruments disponibles
SELECT COUNT(*) FROM performance_schema.setup_instruments;
-- ~1200 instruments

-- Instruments pour les statements (requêtes)
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'statement/sql/%'
LIMIT 5;

+----------------------------+---------+-------+
| NAME                       | ENABLED | TIMED |
+----------------------------+---------+-------+
| statement/sql/select       | YES     | YES   |
| statement/sql/insert       | YES     | YES   |
| statement/sql/update       | YES     | YES   |
| statement/sql/delete       | YES     | YES   |
| statement/sql/create_table | YES     | YES   |
+----------------------------+---------+-------+

Les tables events_statements_*

Performance Schema collecte les requêtes dans plusieurs tables :

Table	Contenu
events_statements_current	Requêtes en cours d'exécution
events_statements_history	Dernières requêtes par thread
events_statements_history_long	Historique étendu (configurable)
events_statements_summary_by_digest	Statistiques agrégées par type de requête (normalisation)
events_statements_summary_by_user_by_event_name	Par utilisateur

Glissez pour voir

sys.statement_analysis : le top des requêtes

La vue sys.statement_analysis est l'équivalent MySQL du top 10 pg_stat_statements de PostgreSQL, prête à l'emploi, sans installation :

SELECT query,
       exec_count,
       avg_latency,
       total_latency,
       rows_sent_avg,
       rows_examined_avg,
       full_scan
FROM sys.statement_analysis
LIMIT 10;

+--------------------------------------+------------+-------------+---------------+---------------+-------------------+-----------+
| query                                | exec_count | avg_latency | total_latency | rows_sent_avg | rows_examined_avg | full_scan |
+--------------------------------------+------------+-------------+---------------+---------------+-------------------+-----------+
| SELECT * FROM `logs` WHERE `action`  |         12 | 2.34 s      | 28.08 s       |          7500 |             90000 | *         |
| SELECT `nom` , `email` FROM `cl ...  |         45 | 120.50 ms   | 5.42 s        |             6 |                 6 |           |
| INSERT INTO `commandes` ( `clie ...  |        200 | 1.20 ms     | 240.00 ms     |             0 |                 0 |           |
+--------------------------------------+------------+-------------+---------------+---------------+-------------------+-----------+

Ce qu'il faut regarder en premier :

• total_latency élevé : la requête qui consomme le plus de temps serveur au total, priorité n°1
• avg_latency élevé + exec_count bas : requête unitairement lente (index manquant ?)
• full_scan = * : la requête fait un full table scan, index manquant
• rows_examined_avg >> rows_sent_avg : MySQL lit beaucoup trop de lignes pour en retourner peu

sys.innodb_buffer_stats_by_table

Cette vue répartit l'occupation du buffer pool table par table, ce qui permet de vérifier que la mémoire profite bien aux tables chaudes. Elle se paie cependant très cher, comme l'indique l'avertissement qui suit le bloc :

SELECT object_schema, object_name,
       allocated, data, pages
FROM sys.innodb_buffer_stats_by_table
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema')
LIMIT 5;

Vue coûteuse à interroger

sys.innodb_buffer_stats_by_table s'appuie sur INFORMATION_SCHEMA.INNODB_BUFFER_PAGE, dont l'interrogation peut avoir un impact mesurable sur les performances. Évitez cette vue en production sauf besoin ponctuel et maîtrisé.

sys schema : les vues prêtes à l'emploi

Le sys schema est une collection de vues, fonctions et procédures qui simplifient l'exploitation de Performance Schema. Installé par défaut depuis MySQL 5.7.

sys.processlist : alternative à SHOW PROCESSLIST

Cette vue reprend les colonnes habituelles et y ajoute des mesures que SHOW PROCESSLIST n'expose pas. Le filtre sur conn_id IS NOT NULL écarte les threads internes du serveur, qui sinon polluent la lecture.

SELECT thd_id, conn_id, user, db,
       command, state, time,
       LEFT(current_statement, 60) AS query,
       trx_latency, lock_latency
FROM sys.processlist
WHERE conn_id IS NOT NULL;

L'avantage par rapport à SHOW PROCESSLIST : la vue ajoute trx_latency (durée de la transaction) et lock_latency (temps passé en attente de verrou).

sys.schema_table_statistics

Cette vue consolide l'activité par table : combien de lignes lues, insérées, modifiées ou supprimées, et quel volume d'entrées-sorties cela représente. C'est le moyen le plus direct de savoir quelles tables travaillent réellement, avant de décider où poser un index ou quoi maintenir.

SELECT table_schema, table_name,
       rows_fetched, rows_inserted, rows_updated, rows_deleted,
       io_read, io_write
FROM sys.schema_table_statistics
WHERE table_schema = 'lab_mysql';

sys.schema_redundant_indexes

Détecte les index redondants (un index qui est un préfixe d'un autre) :

SELECT table_schema, table_name,
       redundant_index_name, redundant_index_columns,
       dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'lab_mysql';

Un index redondant consomme de l'espace et ralentit les écritures (INSERT, UPDATE, DELETE) sans apporter de bénéfice aux lectures. Supprimez-le après vérification.

sys.host_summary et sys.user_summary

Ces deux vues agrègent la même activité selon deux axes différents, l'hôte client et le compte MySQL. La colonne full_scans y est particulièrement parlante : elle désigne l'application ou le compte à qui parler quand les parcours complets de table se multiplient.

-- Par hôte
SELECT host, statements, total_latency, rows_examined, full_scans
FROM sys.host_summary;

-- Par utilisateur
SELECT user, statements, total_latency, rows_examined, full_scans
FROM sys.user_summary;

Analyser les requêtes avec EXPLAIN

Les sections précédentes disent quelle requête coûte cher. EXPLAIN répond à la question suivante : pourquoi. L'outil expose le plan choisi par l'optimiseur, c'est-à-dire l'ordre des accès et les index retenus. Deux variantes coexistent et ne s'utilisent pas dans les mêmes conditions : EXPLAIN seul n'exécute rien et reste sans danger en production, tandis qu'EXPLAIN ANALYZE lance réellement la requête pour mesurer chaque étape.

EXPLAIN : le plan d'exécution

EXPLAIN montre le plan que MySQL prévoit d'utiliser pour exécuter une requête, sans l'exécuter :

EXPLAIN SELECT * FROM logs WHERE action = 'login' AND client_id = 3;

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | logs  | ALL  | NULL          | NULL | NULL    | NULL | 90000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

type = ALL signifie un full table scan : MySQL lit les 90 000 lignes pour en filtrer quelques-unes. C'est le pire cas possible.

EXPLAIN ANALYZE : le plan avec le temps réel

EXPLAIN ANALYZE exécute réellement la requête et mesure le temps de chaque étape :

EXPLAIN ANALYZE SELECT * FROM logs WHERE action = 'login' AND client_id = 3\G

-> Filter: ((logs.action = 'login') AND (logs.client_id = 3))  (cost=9044 rows=900)
    (actual time=0.046..12.532 rows=4985 loops=1)
    -> Table scan on logs  (cost=9044 rows=90000)
        (actual time=0.039..7.284 rows=90000 loops=1)

Lecture : MySQL scanne les 90 000 lignes (Table scan) en 7,3 ms, puis filtre pour ne garder que 4 985 lignes en 12,5 ms au total. L'estimateur prévoyait 900 lignes, 5× moins que la réalité. Les statistiques sont obsolètes.

EXPLAIN FORMAT=JSON et FORMAT=TREE

Le format tabulaire par défaut devient illisible dès qu'il y a des jointures ou des sous-requêtes. FORMAT=TREE restitue la hiérarchie des opérations et se lit de l'intérieur vers l'extérieur ; FORMAT=JSON contient les mêmes informations avec les coûts détaillés, dans une structure destinée aux outils. Le \G en fin de ligne remplace l'affichage en colonnes par un affichage vertical, indispensable ici.

-- Format arbre (plus lisible, recommandé depuis 8.0)
EXPLAIN FORMAT=TREE SELECT * FROM logs WHERE action = 'login'\G

-- Format JSON (détail complet, utile pour les outils)
EXPLAIN FORMAT=JSON SELECT * FROM logs WHERE action = 'login'\G

EXPLAIN INTO (MySQL 8.4+)

MySQL 8.4 introduit EXPLAIN INTO pour stocker le plan dans une variable utilisable ensuite. EXPLAIN INTO exige FORMAT=JSON et n'est pas compatible avec EXPLAIN ANALYZE :

EXPLAIN FORMAT=JSON INTO @plan
SELECT * FROM logs WHERE action = 'login';

SELECT JSON_PRETTY(@plan)\G

Corriger : ajouter un index et comparer

L'index est composite et l'ordre des colonnes suit celui du WHERE : MySQL ne peut exploiter les colonnes d'un index composite que de la gauche vers la droite, un index (client_id, action) ne servirait donc pas une requête qui ne filtre que sur action. La comparaison des deux plans, avant et après, est la seule preuve que l'index sert à quelque chose.

CREATE INDEX idx_logs_action_client ON logs (action, client_id);

EXPLAIN ANALYZE SELECT * FROM logs WHERE action = 'login' AND client_id = 3\G

-> Index lookup on logs using idx_logs_action_client
    (action='login', client_id=3)  (cost=1743 rows=4985)
    (actual time=0.052..2.463 rows=4985 loops=1)

De 12,5 ms (full table scan) à 2,5 ms (index lookup), 5× plus rapide.

Les types d'accès à comprendre

La colonne type de EXPLAIN indique le mode d'accès, du pire au meilleur :

Type	Signification	Performance
ALL	Full table scan, lit toutes les lignes	Pire
index	Full index scan, parcourt tout l'index	Mauvais
range	Scan partiel d'index (BETWEEN, <, >, IN)	Acceptable
ref	Lookup par index non-unique	Bon
eq_ref	Lookup par index unique (jointure PK)	Très bon
const	Lookup par clé primaire, résultat unique	Optimal
system	Table à une seule ligne	Optimal

Glissez pour voir

Règle : si vous voyez ALL sur une table de plus de 10 000 lignes avec un WHERE, c'est un index manquant.

Surveiller InnoDB

Une requête peut être parfaitement indexée et rester lente si le moteur de stockage est sous tension. InnoDB expose quatre zones à surveiller, dans cet ordre d'importance : le cache mémoire, le journal de transactions, la purge des anciennes versions de lignes et, plus marginalement, l'index hash adaptatif. Toutes se lisent dans le même rapport SHOW ENGINE INNODB STATUS, ce qui explique qu'il revienne à chaque section.

Buffer pool : taux de hit et pages dirty

Le buffer pool est le cache mémoire d'InnoDB. C'est la métrique la plus importante pour les performances :

SHOW ENGINE INNODB STATUS\G

Extrait de la section BUFFER POOL AND MEMORY :

Buffer pool size   16384
Free buffers       15130
Database pages     1247
Old database pages 440
Modified db pages  0
Buffer pool hit rate 1000 / 1000

Métrique	Signification
Buffer pool size	Pages totales dans le pool (× 16 Ko)
Free buffers	Pages libres disponibles
Database pages	Pages contenant des données
Modified db pages	Pages dirty (modifiées, pas encore écrites sur disque)
Buffer pool hit rate	1000/1000 = 100 %, parfait. En dessous de 950/1000 (95 %), augmentez le buffer pool

Glissez pour voir

Pour un monitoring automatisé :

SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS logical_reads,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS disk_reads,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') AS dirty_pages,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') AS total_pages;

Redo log : checkpoint et écriture

Le redo log (journal de transactions InnoDB) garantit la durabilité des données. Si le redo log se remplit plus vite que les checkpoints ne le libèrent, MySQL ralentit :

SHOW ENGINE INNODB STATUS\G

Section LOG :

Log sequence number          79456832
Log buffer assigned up to    79456832
Log buffer completed up to   79456832
Log written up to            79456832
Log flushed up to            79456832
Last checkpoint at           79456832

Si Log sequence number est très en avance sur Last checkpoint at, le redo log est sous pression, les écritures pourraient être ralenties.

-- Vérifier la taille du redo log
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_redo_log_capacity | 104857600 |
+--------------------------+-----------+

100 Mo par défaut en MySQL 8.4. Pour une base avec beaucoup d'écritures, augmentez à 256 Mo ou 512 Mo :

SET PERSIST innodb_redo_log_capacity = 268435456; -- 256 Mo

Transactions : historique et purge lag

InnoDB maintient un historique de transactions pour le MVCC (lectures cohérentes sans verrou). Si l'undo log grossit, la purge est en retard :

SHOW ENGINE INNODB STATUS\G

Section TRANSACTIONS :

Trx id counter 12345
Purge done for trx's n:o < 12340 undo n:o < 0 state: running
History list length 23

Métrique	Signification	Seuil d'alerte
History list length	Transactions en attente de purge	> 10 000

Glissez pour voir

Un History list length qui augmente indique que le thread de purge ne suit pas le rythme des écritures. Causes possibles : requêtes SELECT très longues qui maintiennent une vue MVCC ancienne, ou innodb_purge_threads insuffisant.

Adaptive hash index : utile ou pas ?

L'adaptive hash index (AHI) est un mécanisme automatique d'InnoDB qui crée un index hash en mémoire pour accélérer les lookups fréquents :

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+

En MySQL 8.4, l'AHI est désactivé par défaut (il était activé en 8.0). Si vous l'avez activé manuellement et voulez vérifier s'il est utile, consultez SHOW ENGINE INNODB STATUS section INSERT BUFFER AND ADAPTIVE HASH INDEX :

• hash searches/s élevé et non-hash searches/s faible : l'AHI est efficace, gardez-le
• hash searches/s faible ou 0 : l'AHI consomme de la mémoire pour rien, désactivez-le

-- Activer pour tester (dynamique, pas de redémarrage)
SET PERSIST innodb_adaptive_hash_index = ON;

-- Désactiver si le monitoring ne montre pas de bénéfice
SET PERSIST innodb_adaptive_hash_index = OFF;

Maintenance des tables

Observer ne suffit pas : certaines dégradations se corrigent par des opérations de maintenance explicites, que MySQL ne déclenche pas tout seul. Trois commandes couvrent les besoins courants, et elles n'ont ni le même coût ni le même effet. ANALYZE TABLE rafraîchit des statistiques et reste léger, OPTIMIZE TABLE reconstruit la table et demande de l'espace disque, CHECK TABLE se contente de vérifier sans jamais réparer. Sauvegardez avant toute opération de reconstruction.

ANALYZE TABLE : mettre à jour les statistiques

L'optimiseur de requêtes MySQL utilise des statistiques sur la distribution des données pour choisir le meilleur plan. Si ces statistiques sont obsolètes (après un gros chargement ou des suppressions massives), l'optimiseur fait de mauvais choix.

ANALYZE TABLE lab_mysql.logs;

+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| lab_mysql.logs  | analyze | status   | OK       |
+-----------------+---------+----------+----------+

ANALYZE TABLE est généralement rapide sur InnoDB, mais il prend un read lock pendant l'analyse. Planifiez-le de préférence hors pic si la table est très sollicitée en écriture. Exécutez-le après tout chargement massif.

Statistiques persistantes par défaut

Depuis MySQL 8.0, innodb_stats_persistent = ON par défaut : les statistiques sont stockées sur disque et survivent au redémarrage. Avant MySQL 8.0, les stats étaient recalculées à chaque ouverture de table.

Histogrammes (MySQL 8.0+)

Les histogrammes donnent à l'optimiseur une vision détaillée de la distribution des valeurs dans une colonne, pas seulement la cardinalité. Particulièrement utile pour les colonnes à distribution non uniforme :

ANALYZE TABLE lab_mysql.logs UPDATE HISTOGRAM ON action, client_id;

+-----------------+---------+----------+----------------------------------------------+
| Table           | Op      | Msg_type | Msg_text                                     |
+-----------------+---------+----------+----------------------------------------------+
| lab_mysql.logs  | histogram | status | Histogram statistics created for column 'action'. |
| lab_mysql.logs  | histogram | status | Histogram statistics created for column 'client_id'. |
+-----------------+---------+----------+----------------------------------------------+

Vérifier les histogrammes créés :

SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
       JSON_EXTRACT(HISTOGRAM, '$.\"histogram-type\"') AS type,
       JSON_EXTRACT(HISTOGRAM, '$.\"number-of-buckets-specified\"') AS buckets
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'lab_mysql';

MySQL 8.4 : mise à jour automatique des histogrammes

MySQL 8.4 introduit la mise à jour automatique des histogrammes (AUTO UPDATE). Lorsque les statistiques persistantes InnoDB sont recalculées, les histogrammes marqués AUTO UPDATE sont rafraîchis automatiquement. Créez-les avec :

ANALYZE TABLE lab_mysql.logs UPDATE HISTOGRAM ON action AUTO UPDATE;

OPTIMIZE TABLE : récupérer l'espace

Après de nombreux DELETE ou UPDATE, les tables InnoDB conservent l'espace libre dans leurs pages. OPTIMIZE TABLE reconstruit la table et les index pour récupérer l'espace :

OPTIMIZE TABLE lab_mysql.logs;

+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| lab_mysql.logs  | optimize | note     | Table does not support optimize, doing recreate + analyze instead  |
| lab_mysql.logs  | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE pour InnoDB

Le message "Table does not support optimize" est normal pour InnoDB. MySQL exécute en interne un ALTER TABLE ... FORCE suivi d'un ANALYZE TABLE, ce qui équivaut à une reconstruction complète. L'opération crée une copie temporaire de la table, donc :

• Prévoyez de l'espace disque libre : environ la taille de la table
• L'opération pose un verrou metadata mais permet les lectures/écritures DML pendant la reconstruction (Online DDL)
• Sur une table de plusieurs Go, l'opération peut durer longtemps

CHECK TABLE et mysqlcheck

CHECK TABLE vérifie l'intégrité d'une table :

CHECK TABLE sur InnoDB

Sur une table InnoDB volumineuse, CHECK TABLE peut bloquer d'autres threads. En cas de corruption, il peut aussi provoquer l'arrêt du serveur pour éviter la propagation de l'erreur. Pour vérifier intégralement un fichier .ibd hors ligne, utilisez plutôt innochecksum.

CHECK TABLE lab_mysql.clients, lab_mysql.commandes, lab_mysql.logs;

+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| lab_mysql.clients    | check | status   | OK       |
| lab_mysql.commandes  | check | status   | OK       |
| lab_mysql.logs       | check | status   | OK       |
+----------------------+-------+----------+----------+

En ligne de commande, mysqlcheck permet de vérifier, optimiser et analyser en masse :

# Vérifier toutes les tables d'une base
mysqlcheck -u root -p lab_mysql

# Optimiser toutes les tables
mysqlcheck -u root -p --optimize lab_mysql

# Analyser toutes les tables de toutes les bases
mysqlcheck -u root -p --all-databases --analyze

Intégrer MySQL dans le monitoring

Tout ce qui précède se lance à la main, au moment où le problème est déjà là. La supervision continue vise l'inverse : détecter la dégradation avant l'appel des utilisateurs. Le principe est simple, un exportateur interroge périodiquement les mêmes vues Performance Schema, Prometheus stocke l'historique et Grafana affiche les tendances. Reste à choisir les métriques et les seuils, qui font l'objet des trois sections suivantes.

Métriques Prometheus (mysqld_exporter)

Pour une supervision continue, installez mysqld_exporter (Prometheus) :

# Créer un utilisateur dédié pour l'exporter
mysql -u root -p -e "
  CREATE USER 'exporter'@'localhost'
    IDENTIFIED BY 'motdepasse_exporter'
    WITH MAX_USER_CONNECTIONS 3;
  GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
"

Le fichier de configuration .my.cnf pour l'exporter :

[client]
user=exporter
password=motdepasse_exporter

Métriques essentielles exposées par mysqld_exporter :

Métrique Prometheus	Source MySQL	Seuil d'alerte
mysql_global_status_threads_connected	Threads_connected	> 80 % de max_connections
mysql_global_status_slow_queries	Slow_queries	Augmentation soudaine
mysql_global_status_innodb_buffer_pool_reads	Innodb_buffer_pool_reads	Hit ratio < 95 %
mysql_global_status_innodb_row_lock_waits	Innodb_row_lock_waits	> 0 de manière soutenue
mysql_slave_status_seconds_behind_master	Seconds_Behind_Source	> 30 secondes

Glissez pour voir

Grafana dashboards

Le dashboard Grafana le plus populaire pour MySQL : MySQL Overview (ID 7362 sur grafana.com). Il affiche le buffer pool hit ratio, les connexions, les QPS, le lag de réplication et les métriques InnoDB.

Alertes critiques à configurer

Une alerte n'a d'intérêt que si quelqu'un peut agir dessus, d'où la colonne de criticité. Les entrées marquées Critique justifient un réveil de nuit : saturation des connexions, réplication cassée ou disque plein aboutissent tous à une indisponibilité. Les Warning se traitent en heures ouvrées et se lisent comme des dérives, pas comme des incidents.

Alerte	Condition	Criticité
Connexions proches de la limite	Threads_connected > 80 % max_connections	Critique
Buffer pool hit ratio faible	< 95 % pendant 5 min	Warning
Lag de réplication	Seconds_Behind_Source > 60 s	Critique
Réplication cassée	Replica_IO_Running = No ou Replica_SQL_Running = No	Critique
Slow queries en hausse	+50 % par rapport à la baseline	Warning
Espace disque	< 20 % libre sur le datadir	Critique
Deadlocks fréquents	> 10 par heure	Warning

Glissez pour voir

Dépannage

Ce tableau reprend les huit situations qui amènent le plus souvent à ouvrir ce guide. Chaque ligne suit la démarche du début de page : le symptôme est ce que vous constatez, la cause probable est ce qu'il faut confirmer avec les commandes d'observation, et la solution ne s'applique qu'après cette confirmation.

Symptôme	Cause probable	Solution
Requête bloquée par un lock	Transaction longue non commitée	Identifier avec SELECT * FROM sys.innodb_lock_waits\G, tuer la transaction bloquante
Buffer pool hit ratio < 95 %	innodb_buffer_pool_size trop petit	Augmenter à 50-70 % de la RAM disponible
Slow queries en augmentation soudaine	Index supprimé ou statistiques obsolètes	ANALYZE TABLE, vérifier les index avec sys.schema_redundant_indexes
"Too many connections"	Pool non fermé ou wait_timeout trop élevé	Réduire wait_timeout, utiliser un connection pooler (ProxySQL)
Table qui grossit après DELETE/UPDATE	Espace non récupéré au niveau fichier	OPTIMIZE TABLE (relâche l'espace)
EXPLAIN montre ALL sur une grosse table	Index manquant sur les colonnes filtrées	Ajouter un index composite sur les colonnes du WHERE
History list length qui augmente	Thread de purge en retard, SELECT très longs	Identifier les transactions longues, augmenter innodb_purge_threads
Deadlocks fréquents	Ordre d'accès aux tables/lignes incohérent	Toujours accéder dans le même ordre, transactions courtes, innodb_print_all_deadlocks = ON

Glissez pour voir

À retenir

• SHOW PROCESSLIST (ou sys.processlist) est le premier réflexe : qui est connecté, que fait-il, depuis combien de temps.
• sys.statement_analysis identifie les requêtes coûteuses : total_latency, full_scan, rows_examined vs rows_sent, l'équivalent MySQL de pg_stat_statements.
• EXPLAIN ANALYZE est le seul moyen fiable de comprendre pourquoi une requête est lente, regardez type = ALL (full scan) et rows examined >> rows sent.
• Le slow query log capture les requêtes lentes sur disque, activez log_queries_not_using_indexes pour détecter les full scans avant qu'ils ne posent problème.
• Le buffer pool hit ratio (>99 % en OLTP) est la métrique InnoDB la plus critique, si elle chute, augmentez innodb_buffer_pool_size.
• ANALYZE TABLE met à jour les statistiques de l'optimiseur, exécutez-le après tout chargement massif. Les histogrammes améliorent l'estimation pour les distributions non uniformes.
• OPTIMIZE TABLE reconstruit la table InnoDB et récupère l'espace, nécessite de l'espace disque temporaire, préférez les heures creuses.
• CHECK TABLE vérifie l'intégrité sans réparer, premier diagnostic en cas de corruption suspectée.
• En production, installez mysqld_exporter + Grafana pour une supervision continue : buffer pool, connexions, lag de réplication, slow queries.
• L'alerte la plus importante : réplication cassée (Replica_IO_Running = No), toute minute sans réplication est une minute de données non protégées.

Prochaines étapes

Configurer MySQL InnoDB buffer pool, connexions, binary log, les réglages qui impactent les performances

Sécuriser MySQL caching_sha2_password, TLS, rôles dédiés et audit

Sauvegarde et restauration mysqldump, MySQL Shell, XtraBackup et PITR, sauvegarder avant de maintenir

Réplication MySQL GTID, semi-synchrone, Group Replication, haute disponibilité

×

📖 Voir la documentation →