Qu'est-ce qu'une requête lente ?

Le journal des requêtes lentes enregistre toutes les requêtes SQL dont le temps d'exécution dépasse le seuil défini par le paramètre long_query_time. Ce mécanisme aide les développeurs à identifier et optimiser les performances des requêtes. Par défaut, cette fonctionnalité est désactivée.

Identifier les requêtes SQL lentes

1. Méthode basique avec SHOW PROFILE

La commande SHOW PROFILE fournit des détails sur l'exécution des requêtes, incluant le temps CPU, les opérations d'E/S et les changements de contexte.

Attention : Cette fonctionnalité ajoute une surcharge au serveur et est recommandée uniquement pour le diagnostic en environnement de dévleoppement.

-- Activer le profilage
SET PROFILING = 1;

-- Exécuter une requête
SELECT * FROM utilisateurs;

-- Consulter les profils générés
SHOW PROFILES;

2. Méthode avancée (non recommandée)

-- Vérifier l'état du schéma de performance
SHOW VARIABLES LIKE 'performance_schema';

Activer le journal des requêtes lentes

1. Vérifier l'état d'activation

La varible slow_query_log indique si le journal est actif (valeur OFF par défaut). Le paramètre long_query_time (défaut : 10 secondes) fixe le seuil. Il est conseillé de régler cette valeur à 3 secondes pour les environnements de production.

La variable log_queries_not_using_indexes, activée avec ON, consigne également les requêtes n'utilisant pas d'index.

2. Procédure d'activation

Configuration perssitante (fichier my.cnf) :

[mysqld]
log_output = FILE,TABLE
slow_query_log = ON
long_query_time = 0.5
slow_query_log_file = /var/log/mysql/slow.log

Configuration à chaud (requiert les privilèges SUPER) :

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

3. Format de sortie

MySQL supporte deux formats de journalisation :

• TABLE : Les données sont stockées dans la table mysql.slow_log. Facilite les requêtes analytiques directes via SQL.
• FILE : Les données sont écrites dans un fichier texte sur le disque. Plus adapté à l'archivage et au traitement par des outils externes.

Vérifier le format courant :

SHOW VARIABLES LIKE 'log_output';

Modifier le format :

-- Activer uniquement le fichier
SET GLOBAL log_output = 'FILE';

-- Activer uniquement la table
SET GLOBAL log_output = 'TABLE';

-- Activer les deux formats
SET GLOBAL log_output = 'FILE,TABLE';

4. Validation

Avec un long_query_time de 0.5 seconde, la requête suivante sera journalisée :

-- Simulation d'une requête lente
SELECT SLEEP(1);

Consulter les enregistrements dans la table :

SELECT start_time, query_time, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC;

Pour vider la table : TRUNCATE TABLE mysql.slow_log;

Consulter le fichier journal :

Le chemin est défini par slow_query_log_file. Les lignes du fichier contiennent des métadonnées (horodatage, utilisateur, temps d'exécution) et la requête SQL complète.