Analyse des requêtes lentes dans MySQL / MariaDB

Analyse des requêtes lentes dans MySQL / MariaDBLors 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;

Base de données  / Audit MariaDB Audit MySQL MariaDB MySQL 

Commentaires

Quel est l’intérêt/avantage de faire enregistrer dans une table les requêtes lentes, et donc de potentiellement charger un peu plus le moteur, pourquoi ne pas utiliser un fichier directement ? Obtiendrait-on plus d’information ?

@Seboss666

Ce ne sont que des INSERT et donc de l’écriture. Le problème de la lenteur dans les moteurs de bases de données relationnelles est à 95% liée à du SELECT et donc à de la lecture. ;+)

Laisser un commentaire

(requis)

(requis)