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 rencontre 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 ensemble de graphiques 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 MariaDB 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

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 
*************************** 1. row *************************** ...

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 MariaDB 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, mais ces évolutions ne sont pas toujours répercutées sur MariaDB. Il est donc important d’avoir les considérations suivantes en tête :

Avec MariaDB 5.5, vous désactiverez en général Performance Schema (ce qui est fait par défaut) car l’instrumentation reste pauvre, mais surtout la baisse de performances est significative.
MariaDB 10.0 et 10.1 ont reçu les évolutions de MySQL 5.6. La baisse de performances est beaucoup plus limitée et l’instrumentation nettement plus riche. 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 recommandé de désactiver la fonctionnalité...

Identification des problèmes de requêtes

1. Requêtes lentes

Sans surprise, l’identification des requêtes lentes se fait principalement 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 du Percona Toolkit.

La manière la plus simple d’invoquer l’outil est de lancer le script avec pour seul paramètre le nom du journal des requêtes lentes :


$ pt-query-digest mysql-slow.log

Attention, pt-query-digest va consommer 100 % d’un processeur pendant tout le temps de l’analyse. Ne l’utilisez pas sur vos serveurs de production !

Vous obtiendrez une liste de requêtes, triées de la plus coûteuse à la moins coûteuse en termes de temps total d’exécution :


# Profile  
# Rank Query ID             Response time Calls   R/Call  Item  
# ==== ================= ================ ===== ========  ================  
#    1 0x67A347A2812914DF  4082.0000 23.8%   339  12.0413 SELECT SYNC_HISTO 
#    2 0x488E7BAC017768E8  2801.0000 16.4%    80  35.0125 SELECT SLIDE_?  PRODUIT_?  
#    3 0x5ECCFCA304C3072E  2236.0000 13.1%   347   6.4438 SELECT SLIDE_?  
#    4 0xFCBC9AC2F4997DCC  1945.0000 11.4%    80  24.3125 SELECT CONTENU_? PRODUIT_?  
#    5 0x5F139E3C468E7A22  1522.0000  8.9%   200   7.6100 SELECT information_schema.tables

Puis, pour chaque requête, vous aurez de nombreuses statistiques :


# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x67A347A2812914DF at byte 429889 
# This item is included in the report because it matches --limit.  
#              pct   total     min     max     avg     95%  stddev median  
# Count         17     339  
# Exec time     23   4082s      3s    136s  ...

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 MariaDB 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/MariaDB. 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 ancien, 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.org) 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.

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 si un paramètre sort d’une gamme de valeurs prédéfinies. Une interface web permet de consulter les écrans de surveillance.

Le principal écueil de l’utilisation de Nagios est sa configuration, qui a rebuté plus d’un administrateur système. Des communautés existent cependant autour de ce logiciel, ce qui pourra vous être utile si vous éprouvez des difficultés à mettre en place cet outil.

Notez que Nagios est modulaire et accepte l’utilisation de plug-ins pour enrichir ses fonctionnalités. Nagios Exchange est le site centralisant tous ces plug-ins (http://exchange.nagios.org).