Analyse des requêtes lentes dans MySQL / MariaDB
5 mars 2019
Lors d’un audit d’une base de données MySQL / MariaDB, il faut être en capacité d’analyser les requêtes qui consomment le plus de temps.
Fixer le seuil de déclenchement des logs
En matière d’administration de bases de données, sont, en général, considérées comme lentes les requêtes d’une durée supérieure à une seconde. C’est d’ailleurs le seuil minimal pris en charge par MySQL / MariaDB.
SET GLOBAL long_query_time=1;
Activer la consignation des requêtes lentes dans la table mysql.slow_log
Vous pouvez au choix envoyer vos requêtes dans le fichier /var/lib/mysql/$(hostname)-slow.log ou dans la table mysql.slow_log. Là, je choisis le mode table :
SET GLOBAL log_output='TABLE'; SET GLOBAL slow_query_log=1;
Adjonction d’autres requêtes
Par défaut, ni les requêtes relatives à l’administration, ni les requêtes n’utilisant pas d’index ne sont consignées dans les requêtes lentes. Vous pouvez toutefois les historiciser :
SET GLOBAL log_slow_admin_statements=1; SET GLOBAL log_queries_not_using_indexes=1;
Visualiser les requêtes lentes
Vous pouvez visualiser la durée moyenne de l’exécution des requêtes :
SELECT db AS base_de_donnees, user_host AS utilisateur_machine, sql_text AS commande_sql, AVG(query_time) AS duree_secondes FROM mysql.slow_log GROUP BY db, user_host, sql_text;
Exporter les données de la table mysql.slow_log
Vous pouvez exporter les données de la table mysql.slow_log dans un fichier CSV.
SELECT * FROM mysql.slow_log INTO OUTFILE '/var/lib/mysql/slow_log.csv' FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n';
Arrêt de l’enregistrement et purge des données de la table
Une fois l’analyse terminée, vous pouvez arrêter l’enregistrement et purger les données.
SET GLOBAL log_output='NONE'; SET GLOBAL slow_query_log=0; TRUNCATE TABLE mysql.slow_log;