Editions ENI Livres | Vidéos | e-Formations
Outils de surveillance
Introduction
Lorsqu’une application passe en production, une nouvelle tâche incombe à l’administrateur : celle de surveiller que la base de données fonctionne correctement. Cette responsabilité implique d’être capable de détecter au plus vite qu’une erreur survient, de manière à s’assurer que le système est toujours disponible, mais aussi de vérifier que la montée en charge de l’application est bien tolérée par la base de données. Idéalement, pour le cas où l’application a du succès et augmente progressivement en taille, l’administrateur doit être capable de savoir si la base de données peut tenir la charge et combien de temps la configuration en cours peut répondre aux demandes de l’application.
La surveillance de la base de données s’articule donc autour de deux domaines : un système d’alerte qui se déclenche dès que la base ne fonctionne pas comme prévu ou ne fonctionne plus du tout et un système de graphe qui permet de voir l’évolution au cours du temps d’indicateurs liés à la bonne santé de la base.
Nous verrons dans ce chapitre les données que MySQL met à notre disposition pour effectuer ces tâches, ainsi que quelques outils existants qui vous feront gagner...
Accès aux métadonnées
1. Commandes spécifiques MySQL
a. Commandes SHOW
Vous pouvez avoir accès aux métadonnées, c’est-à-dire aux informations sur la manière dont sont structurées en particulier les bases, les tables ou les colonnes, avec un certain nombre de commandes spécifiques utilisant le mot-clé SHOW. Parmi les commandes SHOW les plus utiles, vous trouverez :
-
SHOW SCHEMAS/SHOW DATABASES : liste les bases de données de l’instance.
-
SHOW TABLES : liste les tables d’une base de données.
-
SHOW COLUMNS : donne les informations sur la structure d’une table.
-
SHOW KEYS (ou SHOW INDEX) : donne les index d’une table.
Pour SHOW COLUMNS et SHOW KEYS, vous devez préciser la table sur laquelle vous souhaitez obtenir des informations en précisant une clause FROM :
mysql> USE sakila
Database changed
mysql> SHOW COLUMNS FROM film_text;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| film_id | smallint(6) | NO | PRI | NULL | |
| title | varchar(255) | NO | MUL | NULL | |
| description | text | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+-------+ SHOW FULL COLUMNS est une variante de SHOW COLUMNS indiquant en plus le jeu de caractères, l’interclassement (voir le chapitre Optimisation pour une explication de ces notions) et les privilèges possibles de chaque colonne :
mysql> SHOW COLUMNS FROM City LIKE 'C%'\G
*************************** 1. row ***************************
Field: CountryCode
Type: char(3)
Null: NO
Key:
Default:
Extra:
mysql> SHOW FULL COLUMNS FROM City LIKE 'C%'\G
***************************...Outils de base pour la surveillance
1. SHOW PROCESSLIST
SHOW PROCESSLIST permet à tout utilisateur ayant le droit SUPER de voir l’activité de l’ensemble des clients connectés au serveur. Cette commande vous donne beaucoup d’informations sur le trafic que reçoit votre serveur. Vous verrez quels sont les clients connectés au moment de l’exécution de SHOW PROCESSLIST, les requêtes en cours d’exécution et les requêtes verrouillées. Vous trouverez aussi l’identifiant de chaque connexion (colonne ID), ce qui vous permettra d’utiliser la commande KILL si vous avez besoin d’arrêter en urgence l’exécution d’une requête.
L’affichage se fait en colonnes, comme le montre l’exemple suivant :
mysql> SHOW PROCESSLIST;
+----+------+-----------+-------+---------+------+--------
+---------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+-------+---------+------+--------
+---------------------------+
| 56 | root | localhost | world | Sleep | 19 | | NULL
|
| 57 | root | localhost | world | Query | 12 | Locked | select
count(*) from City |
| 58 | root | localhost | NULL | Query | 0 | NULL | SHOW
PROCESSLIST |
+----+------+-----------+-------+---------+------+--------
+---------------------------+ Si vous n’avez pas le droit SUPER, SHOW PROCESSLIST ne vous montrera pas l’activité des autres clients.
Vous pouvez employer la variante SHOW FULL PROCESSLIST, dont la seule différence est de ne pas tronquer l’affichage si les lignes sont trop longues.
Vous pouvez également utiliser la table PROCESSLIST de la base information_schema à la place de SHOW PROCESSLIST :
mysql> SELECT * FROM information_schema.PROCESSLIST;
+----+------+-----------+-------+---------+------+-----------
+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE |
INFO ...Performance Schema
1. Rôle
Performance Schema est un mécanisme d’instrumentation proposé depuis MySQL 5.5, qui permet de collecter de nombreuses informations sur le comportement du serveur pendant son fonctionnement. Les données sont stockées dans une base dédiée appelée performance_schema et sont interrogeables par des requêtes SQL classiques.
Performance Schema reçoit des évolutions majeures à chaque nouvelle version de MySQL, il est donc important d’avoir les considérations suivantes en tête :
-
À partir de MySQL 5.6, Performance Schema est activé par défaut. La baisse de performances est limitée et l’instrumentation nettement plus riche qu’avec MySQL 5.5. Les possibilités sont même tellement vastes qu’installer le schéma sys (voir plus loin dans cette section) est fortement conseillé pour exploiter plus facilement les informations fournies. Cependant, si vous ne prévoyez pas d’utiliser les statistiques de Performance Schema, il est possible de désactiver la fonctionnalité pour regagner un peu de mémoire et un peu de performances.
-
À partir de MySQL 5.7, le schéma sys est installé par défaut, ce qui rend nettement plus simple l’exploitation des données de Performance Schema. Une des autres évolutions marquantes est l’apparition de tables pour surveiller la réplication, car la traditionnelle commande SHOW SLAVE STATUS est de moins en moins adaptée aux nouvelles possibilités (réplication parallèle, réplication multisource).
-
Le changement majeur avec MySQL 8.0 est l’ajout d’index qui permettent...
Identification des problèmes de requêtes
1. Requêtes lentes
Sans surprise, l’identification des requêtes lentes se fait principlament grâce au journal des requêtes lentes, en général en capturant toutes les requêtes pendant un certain temps (une heure, par exemple) avec l’option long_query_time = 0. La difficulté est ensuite de pouvoir interpréter les informations des données capturées. Le meilleur outil pour cette tâche est pt-query-digest dont vous allez voir les principales fonctionnalités dans la suite de cette section.
Depuis MySQL 5.6, il existe une solution bien plus rapide : performance_schema, ou plus exactement plusieurs vues de la base sys. Toutes les vues suivantes peuvent vous aider à trouver des requêtes mal optimisées :
mysql> show tables like 'statement%';
+---------------------------------------------+
| Tables_in_sys (statement%) |
+---------------------------------------------+
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
+---------------------------------------------+ Le nom de chaque vue est explicite. Si par exemple vous cherchez à identifier les requêtes créant des tables temporaires, vous regarderez la vue statements_with_temp_tables. Si vous cherchez les requêtes parcourant des tables entières, vous regarderez du côté de la vue statements_with_full _table_scans. Et il sera toujours utile de regarder statement_analysis, pour laquelle vous pourrez trier les requêtes avec la colonne total_latency, ce qui vous donnera une liste des requêtes les plus coûteuses.
Vérifiez toujours que les consommateurs suivants sont activés (ils le sont par défaut) :
mysql> select * from performance_schema.setup_consumers where name
like...Outils de surveillance du système
1. Cacti
Cacti est un outil de surveillance disponible pour UNIX/Linux et Windows, qui vous permet d’obtenir des graphes sur divers paramètres de votre serveur MySQL via une interface Web. Cacti est très simple à installer et à configurer, ce qui est très appréciable si vous avez besoin de construire rapidement une solution graphique montrant l’évolution au cours du temps de la performance de votre serveur.
Cacti fonctionne avec un système de modèles (templates) et vous pourrez très facilement trouver sur Internet des modèles pour MySQL. Un très bon choix est de télécharger les modèles mis à disposition par Percona à l’adresse suivante : https://www.percona.com/software/mysql-tools/percona-monitoring-plugins
2. Grafana
Cacti est un projet qui date, et vous le ressentirez particulièrement au niveau de l’interface d’affichage des graphiques qui est complètement dépassée. Grafana (http://www.grafana.com) est nettement plus moderne et vous permet de visualiser n’importe quel type de séries de données. Vous pouvez également rassembler vos graphes dans des pages spéciales pour par exemple avoir directement sous les yeux la consommation CPU de tous vos serveurs.
Un outil très intéressant reposant en partie sur Grafana et développé encore une fois par Percona est PMM (Percona Monitoring and Management), qui propose toute une série de graphiques bien adaptés à la surveillance de MySQL ainsi qu’une interface de visualisation des requêtes lentes. La prise en main peut être compliquée à cause de la profusion d’informations, mais avec un peu d’habitude, l’outil fournit des informations très précieuses.
Une démonstration est disponible à l’adresse https://pmmdemo.percona.com/graph et la documentation peut être consultée à l’adresse https://www.percona.com/doc/percona-monitoring-and-management/index.html.
3. Nagios
Nagios est un système de supervision très complet et fréquemment utilisé. Il permet de surveiller de multiples paramètres sur vos serveurs et peut déclencher des alertes sur l’interface, par SMS ou par courriel...
