Surveillance des données MySQL : comment mettre en œuvre l'analyse des requêtes, les statistiques et la surveillance de l'exécution de MySQL

MySQL est l'une des bases de données relationnelles open source les plus utilisées. Elle possède d'excellentes fonctionnalités telles que l'efficacité, la fiabilité et la stabilité, elle est donc appréciée par de plus en plus de personnes. Cependant, à mesure que l'échelle des applications continue de croître, la surveillance et l'optimisation correspondantes de MySQL sont devenues des tâches que les gestionnaires ne peuvent ignorer.

La surveillance des données MySQL comprend principalement l'analyse des requêtes, les statistiques et la surveillance de l'exécution de MySQL. Le but de ces tâches est de comprendre l'état de la base de données, de découvrir et de résoudre les problèmes en temps opportun, d'améliorer les performances et la stabilité de la base de données et d'assurer le bon fonctionnement de l'entreprise. Nous présenterons ensuite comment mettre en œuvre ces tâches de surveillance une par une.

1. Analyse des requêtes

L'optimisation des performances de MySQL est indissociable de l'optimisation des requêtes SQL. Comment trouver les goulots d'étranglement des requêtes et l'espace d'optimisation nécessite de s'appuyer sur la technologie d'analyse des requêtes.

1. Utiliser le journal des requêtes lentes

MySQL est livré avec la fonction de journal des requêtes lentes, nous pouvons enregistrer le SQL dont le temps d'exécution dépasse un certain seuil de temps. . Cela nous permet de trouver facilement des instructions SQL qui prennent beaucoup de temps à s'exécuter.

Les étapes sont les suivantes :
(1) Activez la fonction de journal des requêtes lentes de MySQL et modifiez le fichier de configuration MySQL :

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Trouvez les deux lignes suivantes et définissez 'slow_query_log' sur 'on', et spécifiez le seuil de temps d'exécution des requêtes lentes (en secondes), ici il est fixé à 5s :

slow_query_log=ON
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=5

(2) Redémarrez le service MySQL :

sudo service mysql restart

( 3) Vérifiez le journal des requêtes lentes :

sudo tail -f /var/log/mysql/mysql-slow.log

2 Utilisez expliquer pour analyser le plan d'exécution de la requête

Lorsqu'il s'avère que l'efficacité d'exécution de. une certaine requête SQL n'est pas assez élevée, elle doit être optimisée. Comment optimiser nécessite de comprendre le plan d’exécution des requêtes.

MySQL fournit la fonction d'analyse des plans d'exécution des requêtes via « expliquer », ce qui peut nous aider à trouver plus facilement les goulots d'étranglement des requêtes.

La syntaxe est la suivante :

EXPLAIN SELECT ...

Après avoir exécuté cette instruction, MySQL renverra un jeu de résultats, qui contient le plan d'exécution de la requête, y compris l'index utilisé, quelles tables sont utilisées, informations telles que quelles opérations ont été effectuées.

En analysant le plan d'exécution, vous pouvez trouver les goulots d'étranglement des requêtes qui s'exécutent lentement, améliorer les instructions de requête et améliorer l'efficacité des requêtes.

2. Statistiques de données

Les statistiques de données MySQL sont la collecte, l'analyse et l'affichage de divers statuts d'exécution des instances MySQL, comprenant principalement le contenu suivant :

#🎜 🎜#1. Statistiques d'état de l'instance : y compris le nombre de connexions, le nombre de threads, le nombre d'interactions IO avec le système d'exploitation, etc. de l'instance MySQL.

2. Statistiques des instructions SQL : interceptez les instructions SQL exécutées dans l'instance MySQL et comptez certaines informations, telles que le nombre d'exécutions, le temps d'exécution moyen et l'état d'exécution.

3. Statistiques sur l'état de la table : y compris l'analyse de l'état de la table, de la fréquence d'utilisation, de la méthode d'utilisation, etc. sous charge de travail.

Les outils de statistiques de données couramment utilisés par MySQL sont les suivants :

Utilisez la commande show status

MySQL est livré avec la commande show status , cette commande peut compter l'état des différentes ressources que MySQL essaie de demander. Par exemple :

show status like "%thread%";

Cette instruction renverra des informations sur tous les threads, y compris les threads en cours d'exécution et les threads dormants non en cours d'exécution.

Console MySQL

La console MySQL permet de visualiser les performances du système, les paramètres, les threads et l'état des processus.

mysql -uroot -ppassword -e "show status like '%Threads_connected%';show processlistG"

3. Utilisez pt-stalk pour le diagnostic des erreurs

pt-stalk est un outil de diagnostic des erreurs MySQL très pratique qui peut capturer les erreurs en temps réel en fonction des paramètres spécifiés. , la collecte d'informations sur l'état du serveur, etc. peuvent aider les ingénieurs à localiser rapidement la cause de la panne.

pt-stalk --function=status --run-time=60 --dest /var/tmp

3. Surveillance des données

La surveillance des données est le but ultime de la surveillance MySQL. La surveillance de l'état d'exécution de MySQL comprend :

Surveillance du processeur, de la mémoire, des E/S du réseau et des E/S du disque dur.
1. L'état de la requête, le nombre de connexions, l'état du thread, les conflits de verrouillage et tout autre état d'exécution dans l'instance MySQL.

Les outils de surveillance couramment utilisés pour MySQL sont les suivants :

Utilisez la commande top ou la commande htop
1. # 🎜🎜#top La commande est un outil de surveillance des processus dans le système Linux, qui peut réaliser une surveillance complète des processus du système actuel.

top -b -n 1 | grep -E 'Cpu|Mem|mysql'  

La commande htop explique certaines des informations abrégées en haut qui ne sont pas faciles à comprendre. Elle prend également en charge l'affichage des processus en cours en couleur, ce qui facilite la lecture par les humains.

2. Utilisez Nagios pour la surveillance

Nagios est un outil de surveillance open source qui peut surveiller divers appareils tels que les réseaux et les hôtes. En installant le plug-in de surveillance Nagios sur chaque nœud, vous pouvez surveiller le processus MySQL, le processeur, le réseau et d'autres aspects.

3. Utilisez l'outil mysqladmin

mysqladmin est un outil en ligne de commande officiellement fourni par MySQL. Il fournit un grand nombre d'options et de paramètres qui peuvent être utilisés pour contrôler MySQL. le processus et son fonctionnement sont surveillés.

mysqladmin -uroot -p -h127.0.0.1 processlist

Si vous êtes trop paresseux pour exécuter la commande manuellement, vous pouvez utiliser l'outil de surveillance open source Zabbix pour la surveillance :

4. Résumé

#🎜🎜 #Grâce à l'analyse des requêtes et aux statistiques des données, à la surveillance des données et à d'autres moyens, vous pouvez mieux comprendre l'état de fonctionnement des instances MySQL, découvrir et résoudre les problèmes en temps opportun et améliorer les performances et la stabilité de MySQL. MySQL lui-même fournit de nombreux outils intégrés, et il existe également de nombreux choix d'outils plus ciblés. Les responsables peuvent les choisir et les utiliser en fonction de leurs besoins réels et de leurs capacités d'exploitation et de maintenance.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!