Surveillance des performances MySQL : Utiliser SHOW STATUS et SHOW PROCESSLIST

Lorsqu'une application basée sur MySQL ralentit, SHOW STATUS et SHOW PROCESSLIST sont les vérifications intégrées les plus rapides que vous puissiez effectuer avant d'ouvrir un tableau de bord. Elles n'expliqueront pas tous les problèmes par elles-mêmes, mais elles répondent à deux questions pratiques : qu'a fait le serveur et que se passe-t-il en ce moment ?

Comprendre l'état du système en temps réel avec SHOW STATUS

La commande SHOW STATUS, souvent utilisée de manière interchangeable avec SHOW GLOBAL STATUS ou SHOW SESSION STATUS, fournit une multitude d'informations sur l'activité du serveur depuis le dernier redémarrage ou depuis le début de la session en cours. Ces variables d'état agissent comme des compteurs, suivant tout, des tentatives de connexion à l'efficacité du cache et aux attentes de verrouillage.

Statut global vs. Statut de session

Lors de l'exécution de cette commande, il est crucial de comprendre la portée :

SHOW GLOBAL STATUS : Affiche les compteurs accumulés depuis le démarrage de l'instance du serveur MySQL. Cela fournit une vue d'ensemble de la santé globale du serveur et des tendances à long terme.
SHOW SESSION STATUS : Affiche les compteurs spécifiques uniquement à la connexion (session) que vous utilisez actuellement. Ceci est utile pour isoler l'impact sur les performances de transactions spécifiques.

Indicateurs clés de performance (KPI) de SHOW GLOBAL STATUS

Bien que SHOW GLOBAL STATUS renvoie des centaines de variables, plusieurs sont critiques pour le triage initial des performances. Vous voudrez généralement diriger la sortie vers grep ou utiliser une clause WHERE pour filtrer ce qui est pertinent.

1. Surveillance des connexions et des threads

Ces variables vous aident à comprendre la charge de connexion :

Nom de la variable	Description
`Threads_connected`	Le nombre de connexions actuellement ouvertes (clients).
`Threads_running`	Le nombre de threads actifs exécutant actuellement des requêtes (devrait généralement être faible).
`Max_used_connections`	Le nombre le plus élevé de connexions simultanées depuis le démarrage du serveur. Utile pour dimensionner `max_connections`.

Exemple : Vérification des connexions actives :

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. Mise en cache et efficacité des requêtes

Si vous utilisez le cache de requêtes hérité (disponible dans les anciennes versions de MySQL, déprécié/supprimé dans les versions plus récentes), ces métriques sont essentielles :

Qcache_hits : Nombre de fois qu'une requête a été servie depuis le cache.
Qcache_lowmem_prunes : Nombre de requêtes qui ont forcé le cache à supprimer des entrées plus anciennes en raison d'un manque de mémoire.

3. Métriques du moteur InnoDB (les plus critiques pour MySQL moderne)

Pour les déploiements modernes utilisant le moteur de stockage InnoDB, surveillez l'activité du pool de tampons :

Innodb_buffer_pool_read_requests : Total des demandes de lecture.
Innodb_buffer_pool_reads : Nombre de lectures physiques à partir du disque (un ratio élevé de lectures physiques par rapport aux demandes indique un besoin d'un pool de tampons plus grand).

Astuce pratique : Pour évaluer rapidement l'efficacité du pool de tampons, calculez le taux de succès : (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

4. Tables temporaires et tris

Ceux-ci indiquent la quantité de traitement interne que MySQL effectue :

Created_tmp_tables : Nombre de tables temporaires en mémoire créées.
Created_tmp_disk_tables : Nombre de tables temporaires qui ont dû être écrites sur le disque (lent).

Si Created_tmp_disk_tables est élevé, vous devrez peut-être augmenter tmp_table_size ou max_heap_table_size.

Diagnostiquer la charge de travail active avec SHOW PROCESSLIST

Alors que SHOW STATUS vous dit ce qui s'est passé, SHOW PROCESSLIST vous dit ce qui se passe en ce moment. Il affiche des informations sur les threads en cours d'exécution dans le serveur, vous permettant d'identifier les requêtes longues ou bloquées.

La structure de la liste des processus

La commande produit plusieurs colonnes, chacune fournissant un contexte sur une connexion active :

Colonne	Description
Id	L'identifiant unique de la connexion (utilisé pour tuer le processus).
User	Le compte utilisateur connecté.
Host	L'hôte d'où provient la connexion.
db	La base de données actuellement utilisée par le thread.
Command	Le type de commande en cours d'exécution (par exemple, Query, Sleep, Connect).
Time	Le nombre de secondes pendant lesquelles le thread est dans son état actuel.
State	L'action spécifique que le thread effectue (par exemple, Sending data, Copying to tmp table).
Info	La déclaration SQL réelle en cours d'exécution (ou tronquée si elle est longue).

Filtrer et interpréter la sortie

Pour les grands systèmes de production, la liste complète des processus peut être écrasante. Il est courant d'utiliser le mot-clé FULL pour s'assurer de voir le texte complet de la requête, puis de filtrer par les colonnes Time ou State.

1. Afficher le texte complet de la commande

Utilisez toujours FULL si vous suspectez des requêtes lentes, car la sortie standard tronque souvent le champ Info :

SHOW FULL PROCESSLIST;

2. Identifier les requêtes bloquantes ou lentes

Surveillez les colonnes Time et Command :

Valeur Time élevée : Toute requête s'exécutant pendant une durée prolongée (par exemple, plus de 10 secondes, selon votre SLA) nécessite une enquête immédiate. Vérifiez la colonne Info correspondante pour voir le SQL.
Command = 'Sleep' : Ces connexions sont inactives mais consomment toujours des ressources. Si elles s'accumulent excessivement, envisagez d'ajuster la variable wait_timeout.
Command = 'Query' : Ce sont des déclarations en cours d'exécution active. Portez une attention particulière à leur State.

3. Identifier les problèmes de verrouillage

Lorsque les requêtes sont bloquées en attendant des ressources, la colonne State l'indique souvent :

Waiting for table metadata lock
Waiting for table lock
Waiting for lock

Si vous voyez de nombreux threads dans un état d'attente, cela signale une contention, généralement causée par une transaction de longue durée qui maintient des verrous dont d'autres ont besoin.

Action : Terminer un processus

Si vous identifiez une requête incontrôlable qui dégrade gravement les performances, vous pouvez la terminer en utilisant la commande KILL suivie de l'Id du processus :

KILL 12345; -- Remplacez 12345 par l'Id réel de la liste des processus

Avertissement : Utilisez KILL avec prudence. Terminer une transaction active peut laisser la base de données dans un état incohérent si la transaction était à mi-chemin d'une opération d'écriture complexe. Essayez toujours d'identifier et d'optimiser la requête en premier si possible.

Combiner les informations de statut et de processus pour le dépannage

Une surveillance efficace de MySQL implique souvent une corrélation entre ces deux commandes :

Vérification initiale : Exécutez SHOW FULL PROCESSLIST. Notez les requêtes à temps élevé ou les connexions excessives.
Vérification du contexte : Examinez le nombre de connexions à l'aide de SHOW GLOBAL STATUS LIKE 'Threads_connected'. Êtes-vous confronté à un afflux ou à une seule mauvaise requête ?
Analyse approfondie : Si une requête spécifique est lente, analysez son impact sur les compteurs de ressources en examinant Innodb_buffer_pool_reads ou les taux de création de tables temporaires pendant que la requête s'exécute (nécessite une comparaison de base).

En vérifiant régulièrement ces sorties dynamiques, vous dépassez les conjectures et appliquez des solutions ciblées pour améliorer la stabilité et la vitesse de MySQL.

Une routine de triage réaliste

Un bon premier passage prend moins d'une minute. Commencez par la liste des processus :

SHOW FULL PROCESSLIST;

Recherchez un tas de requêtes actives, des valeurs Time longues, des attentes de verrouillage et de nombreuses connexions Sleep inactives. Une seule requête de rapport lente est traitée différemment de centaines de connexions Web attendant le même verrou de table.

Ensuite, vérifiez les compteurs de threads :

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connected vous indique combien de clients sont connectés. Threads_running est généralement plus important lors d'un ralentissement car il montre combien de threads travaillent activement. De nombreux clients connectés en veille peuvent être gaspilleurs, mais de nombreux threads en cours d'exécution peuvent signifier que le serveur est sous une réelle pression.

Ensuite, vérifiez si la charge de travail crée des tables temporaires sur disque :

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

Ces compteurs sont cumulatifs depuis le démarrage, donc un seul instantané peut induire en erreur. Prenez deux instantanés à quelques minutes d'intervalle pendant l'incident. Si les tables temporaires sur disque augmentent rapidement, inspectez les requêtes avec GROUP BY, ORDER BY, les grandes jointures, les colonnes de texte ou les index manquants. Augmenter tmp_table_size peut aider dans certains cas, mais une meilleure requête ou un meilleur index est souvent la correction la plus propre.

Examiner la pression InnoDB

La plupart des déploiements MySQL modernes utilisent InnoDB, donc les compteurs InnoDB méritent une attention particulière :

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests compte les demandes de lecture logiques. Innodb_buffer_pool_reads compte les lectures qui ont dû aller sur le disque. Si les lectures physiques augmentent rapidement pendant le trafic normal, le pool de tampons peut être trop petit pour l'ensemble de travail, les requêtes peuvent analyser trop de données, ou un travail par lots peut pousser des pages utiles hors du cache.

Les attentes de verrouillage sont une autre source courante de douleur :

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

L'augmentation des attentes de verrouillage de ligne ne signifie pas automatiquement qu'InnoDB est cassé. Cela signifie généralement que les transactions maintiennent les verrous plus longtemps que prévu par l'application. Recherchez les transactions ouvertes, les mises à jour lentes ou les chemins de code qui démarrent une transaction, appellent des services externes et valident beaucoup plus tard.

Pour plus de détails sur les verrous et les transactions, SHOW ENGINE INNODB STATUS\G peut aider, mais sa sortie est dense. Utilisez-le lorsque la liste des processus montre des attentes de verrouillage et que vous devez identifier le modèle de transaction derrière elles.

Utilisation plus sûre de `KILL`

KILL est utile, mais ce n'est pas un bouton de nettoyage. Si vous tuez une connexion exécutant une grande transaction, MySQL peut avoir besoin d'annuler le travail, et l'annulation peut prendre du temps. Dans certains incidents, c'est toujours la bonne décision, mais prenez la délibérément.

Préférez tuer la requête en premier lorsque votre version de MySQL et vos autorisations le prennent en charge :

KILL QUERY 12345;

Cela tente d'arrêter la déclaration en cours tout en maintenant la connexion active. Si le client se comporte mal ou si la connexion doit disparaître, utilisez :

KILL CONNECTION 12345;

Avant de tuer quoi que ce soit, capturez la ligne de la liste des processus, l'utilisateur, l'hôte, la base de données et le texte SQL. Après l'incident, ce détail vous aide à corriger la source au lieu d'attendre le retour de la même requête.

États courants de la liste des processus et ce qu'ils suggèrent

Sending data ne signifie pas toujours que MySQL envoie des lignes sur le réseau. Cela signifie souvent que le serveur lit, filtre, trie ou prépare des lignes. Si une requête y passe beaucoup de temps, exécutez EXPLAIN sur la déclaration et recherchez les analyses de table, un mauvais ordre de jointure ou des index manquants.

Copying to tmp table ou Creating sort index pointe souvent vers un tri ou un regroupement coûteux. Vérifiez si un index peut prendre en charge le modèle WHERE et ORDER BY. Parfois, la requête fait exactement ce que le produit a demandé, mais elle appartient à un rapport asynchrone plutôt qu'à un chemin de requête.

Waiting for table metadata lock apparaît souvent lorsque DDL et les requêtes normales entrent en collision. Un ALTER TABLE apparemment simple peut attendre derrière une transaction ouverte, tandis que les requêtes ultérieures s'accumulent derrière le DDL en attente. Dans ce cas, tuer le plus ancien bloqueur peut être plus sûr que de tuer chaque requête en attente.

Transformer les compteurs en preuves utiles

Étant donné que les valeurs SHOW STATUS sont principalement des compteurs, les taux sont plus utiles que les nombres bruts. Capturez les mêmes variables deux fois :

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

Attendez soixante secondes, puis exécutez-les à nouveau. La différence vous indique le taux pendant cette minute. C'est la même idée que les tableaux de bord utilisent, mais le faire manuellement est utile lorsque vous n'avez qu'un accès terminal.

Prenez des notes pendant les incidents. "Threads_running est passé de 8 à 90, la liste des processus a montré 70 requêtes en attente de verrou de métadonnées sur orders, et Max_used_connections n'a pas changé" est un diagnostic utile. "MySQL était lent" ne l'est pas.

Quand ces commandes ne suffisent pas

SHOW STATUS et SHOW PROCESSLIST sont des outils de première réponse. Ils ne remplacent pas le journal des requêtes lentes, le schéma de performance, les plans de requête ou les métriques au niveau de l'hôte. Si le même problème revient, activez ou examinez le journal des requêtes lentes et inspectez les pires déclarations avec EXPLAIN.

Pour les pics de connexion récurrents, examinez les paramètres du pool d'applications et le comportement de déploiement. Augmenter max_connections peut faire gagner du temps, mais cela peut aussi permettre au serveur d'accepter plus de travail qu'il ne peut réellement en exécuter. Pour les attentes de verrouillage récurrentes, inspectez les limites de transaction dans l'application. Une transaction qui reste ouverte pendant que le code appelle une API externe peut bloquer des requêtes non liées et donner l'impression que MySQL est plus lent qu'il ne l'est.

Vérifiez également l'hôte. Si la latence du disque est élevée, le CPU est saturé, la mémoire permute ou un voisin bruyant vole des ressources, les compteurs MySQL montreront des symptômes mais pas la cause entière. Un bon diagnostic combine les commandes de base de données avec les métriques système.

SHOW STATUS vous donne des compteurs et un contexte. SHOW FULL PROCESSLIST vous donne la charge de travail en direct. Utilisés ensemble, ils vous aident à faire la différence entre la pression de connexion, une mauvaise requête, la contention de verrouillage, le travail temporaire lourd sur disque et la pression du cache InnoDB.