Surveillance des performances MySQL : Utilisation de SHOW STATUS et SHOW PROCESSLIST

Diagnostiquer les goulots d'étranglement de performance et comprendre l'état de votre base de données MySQL sont des compétences fondamentales pour tout administrateur ou développeur. Les requêtes lentes, les inondations de connexions ou une utilisation inattendue des ressources peuvent gravement affecter les performances de l'application. Heureusement, MySQL fournit des commandes intégrées et facilement accessibles pour fournir des informations immédiates en temps réel. Cet article explore en profondeur deux des commandes les plus cruciales pour le diagnostic des performances : SHOW STATUS et SHOW PROCESSLIST.

En maîtrisant ces outils, vous acquérez la capacité d'analyser les connexions actives, d'examiner les compteurs globaux du serveur et de déterminer précisément où les ressources de votre système sont consommées.

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

La commande SHOW STATUS, souvent utilisée de manière synonyme avec SHOW GLOBAL STATUS ou SHOW SESSION STATUS, fournit une multitude d'informations concernant l'activité du serveur depuis le dernier redémarrage ou depuis le début de la session actuelle. 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. 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 l'état général du serveur et des tendances à long terme.
SHOW SESSION STATUS : Affiche les compteurs spécifiques à la connexion (session) que vous utilisez actuellement. Ceci est utile pour isoler l'impact des 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 un premier diagnostic des performances. Vous voudrez généralement rediriger la sortie vers grep ou utiliser une clause WHERE pour filtrer par pertinence.

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 maximum 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, obsolète/supprimé dans les plus récentes), ces métriques sont essentielles :

Qcache_hits : Nombre de fois où une requête a été servie depuis le cache.
Qcache_lowmem_prunes : Nombre de requêtes qui ont provoqué l'expulsion d'entrées plus anciennes du cache en raison d'un manque de mémoire.

3. Métriques du moteur InnoDB (le plus critique 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 : Nombre total de requêtes de lecture.
Innodb_buffer_pool_reads : Nombre de lectures physiques depuis le disque (un ratio élevé de lectures physiques par rapport aux requêtes indique un besoin d'un pool de tampons plus grand).

Conseil 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

Ces indicateurs montrent la quantité de traitement interne effectuée par MySQL :

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 disque (lent).

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

Diagnostic de 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 resté dans son état actuel.
State	L'action spécifique effectuée par le thread (par exemple, Sending data, Copying to tmp table).
Info	L'instruction SQL réelle exécutée (ou tronquée si longue).

Filtrage et interprétation de 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. Affichage du 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. Identification des requêtes bloquantes ou lentes

Surveillez les colonnes Time et Command :

Valeur Time élevée : Toute requête exécutée pendant une durée prolongée (par exemple, plus de 10 secondes, en fonction de 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 instructions en cours d'exécution. Portez une attention particulière à leur State.

3. Identification des problèmes de verrouillage

Lorsque les requêtes restent 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 détenant des verrous dont d'autres ont besoin.

Action : Terminer un processus

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

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

Attention : Utilisez KILL avec prudence. La terminaison d'une transaction active pourrait 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.

Combinaison des 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 toute requête prenant beaucoup de temps ou les connexions excessives.
Vérification du contexte : Examinez le nombre de connexions à l'aide de SHOW GLOBAL STATUS LIKE 'Threads_connected'. Est-ce une inondation ou juste une 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 référence).

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.

Conclusion

Les commandes SHOW STATUS et SHOW PROCESSLIST sont les outils de première ligne pour le diagnostic MySQL en temps réel. SHOW STATUS fournit le contexte historique et les métriques de compteur nécessaires pour ajuster la configuration du serveur, tandis que SHOW PROCESSLIST fournit l'instantané immédiat requis pour traiter les urgences actuelles ou les exécutions de longue durée. L'utilisation régulière de ces commandes est essentielle pour maintenir un environnement de base de données performant.