Comment activer le journal des requêtes lentes de MySQL

Les requêtes lentes de manière inattendue sont l’un des problèmes de performances MySQL les plus courants. Une requête qui fonctionne de manière acceptable en développement peut échouer lorsqu’elle est stressée par une charge de travail de production.

Les grandes applications peuvent exécuter des centaines de requêtes de base de données uniques chaque fois qu’un point de terminaison est atteint. Cela rend difficile l’identification des requêtes qui causent des retards dans la réponse du serveur. Le journal des requêtes lentes MySQL est une option de débogage qui peut vous aider à identifier les instructions SQL suspectes, fournissant un point de départ pour vos investigations.

Activation du journal des requêtes lentes

Le journal est un mécanisme intégré pour enregistrer les requêtes SQL de longue durée. Les requêtes qui ne se terminent pas dans un délai configuré seront écrites dans le journal. La lecture du contenu du journal vous montre le SQL qui a été exécuté et le temps pris.

La journalisation lente des requêtes est désactivée par défaut. Vous pouvez l’activer sur votre serveur en exécutant la commande suivante depuis un shell d’administration MySQL :

SET GLOBAL slow_query_log_file="/var/log/mysql/mysql-slow.log";
SET GLOBAL slow_query_log=1;

Le changement s’applique immédiatement. Les requêtes lentes seront désormais enregistrées dans /var/log/mysql/mysql-slow.log. Vous pouvez consulter ce fichier périodiquement pour identifier les requêtes peu performantes.

MySQL considère une requête comme « lente » si elle prend plus de 10 secondes à se terminer. Cette limite est généralement trop souple pour les applications Web destinées aux utilisateurs où des réponses quasi instantanées sont attendues. Vous pouvez modifier la limite en réglant le long_query_time variable:

SET GLOBAL long_query_time=1;

La valeur définit la durée minimale des requêtes lentes. Il est important de trouver un équilibre qui convient à votre propre application. Un seuil trop élevé exclura les requêtes qui ont réellement un impact sur les performances. Inversement, des valeurs très faibles peuvent entraîner la capture d’un trop grand nombre de requêtes, créant ainsi un journal excessivement bruyant.

Utiliser le fichier de configuration de MySQL

Vous devez activer le journal des requêtes lentes dans votre fichier de configuration MySQL si vous prévoyez de l’utiliser à long terme. Cela garantira la reprise automatique de la journalisation après le redémarrage du serveur MySQL.

L’emplacement du fichier de configuration peut varier selon la distribution de la plate-forme. C’est généralement à /etc/mysql/my.cnf ou /etc/mysql/mysql.conf.d/mysqld.cnf. Ajoutez les lignes suivantes pour répliquer les paramètres activés dynamiquement ci-dessus :

slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1

Redémarrez MySQL pour appliquer vos modifications :

$ sudo service mysql restart

Le journal des requêtes lentes sera désormais actif à chaque démarrage du serveur MySQL.

Personnalisation du contenu du journal

Le journal n’inclut normalement que les requêtes SQL qui excluent le seuil « lent » et qui ont été soumises par des applications clientes. Cela exclut toutes les opérations administratives lentes qui peuvent se produire, telles que les créations d’index et les optimisations de table, ainsi que les requêtes qui ont le potentiel être lent à l’avenir.

Vous pouvez étendre le journal pour inclure ces informations en apportant les modifications suivantes à votre fichier de configuration :

• log_slow_admin_statements = 1 – Inclut des instructions SQL administratives telles que ALTER TABLE, CREATE INDEX, DROP INDEXet OPTIMIZE TABLE. Ceci est rarement souhaitable car ces opérations sont généralement exécutées pendant les scripts de maintenance et de migration. Néanmoins, ce paramètre peut être utile si votre application effectue également ces tâches de manière dynamique.
• log_slow_replica_statements = 1 – Ce paramètre active la journalisation lente des requêtes pour les requêtes répliquées sur les serveurs répliques. Ceci est désactivé par défaut. Utilisation log_slow_slave_statements à la place pour les versions MySQL 8.0.26 et antérieures.
• log_queries_not_using_indexes = 1 – Lorsque ce paramètre est activé, les requêtes censées récupérer tous les enregistrements de la table ou de la vue cible seront consignées, même si elles n’ont pas exclu le seuil de requête lente. Cela peut aider à identifier quand une requête manque d’un index ou est incapable de l’utiliser. Les requêtes qui ont un index disponible seront toujours consignées si elles manquent de contraintes qui limitent le nombre de lignes extraites.

Les requêtes de journalisation qui n’utilisent pas d’index peuvent augmenter considérablement la verbosité. Il peut y avoir des situations dans lesquelles une analyse complète de l’index est attendue ou nécessaire. Ces requêtes continueront d’apparaître dans le journal même si elles ne peuvent pas être résolues.

Vous pouvez évaluer les requêtes de limite sans index en définissant le paramètre log_throttle_queries_not_using_indexes variable. Cela définit le nombre maximum de journaux qui seront écrits dans une période de 60 secondes. Une valeur de 10 signifie que jusqu’à 10 requêtes seront enregistrées par minute. Après le dixième événement, plus aucune requête ne sera enregistrée jusqu’à l’ouverture de la prochaine fenêtre de 60 secondes.

Interprétation du journal des requêtes lentes

Chaque requête entrant dans le journal des requêtes lentes affichera un ensemble de lignes ressemblant à ce qui suit :

# Time: 2022-07-12T19:00:00.000000Z
# User@Host: demo[demo] @ mysql [] Id: 51
# Query_time: 3.514223  Lock_time: 0.000010  Rows_sent: 5143  Rows_examined: 322216
SELECT * FROM slow_table LEFT JOIN another_table ...

Les lignes commentées au-dessus de la requête contiennent l’heure à laquelle elle s’est exécutée, l’utilisateur MySQL avec lequel le client s’est connecté et des statistiques fournissant la durée et le nombre de lignes envoyées. L’exemple ci-dessus a pris 3,5 secondes et a examiné plus de 320 000 lignes, avant d’en envoyer seulement 5 143 au client. Cela pourrait être une indication que les index manquants poussent MySQL à inspecter trop d’enregistrements.

Vous pouvez éventuellement inclure plus d’informations dans le journal en définissant le paramètre log_slow_extra = 1 variable système dans votre fichier de configuration. Cela ajoutera l’ID de thread, le nombre d’octets reçus et envoyés et le nombre de lignes considérées pour le tri, ainsi que le nombre de requêtes spécifiques à l’instruction qui offrent une visibilité sur la façon dont MySQL a traité la requête.

Le fichier journal doit être traité avec précaution car son contenu sera sensible. Les requêtes sont affichées en entier, sans aucun masquage des valeurs des paramètres. Cela signifie que les données utilisateur seront présentes si vous utilisez le journal des requêtes lentes sur un serveur de production. L’accès doit être limité aux développeurs et aux administrateurs de base de données qui ajustent les instructions SQL.

Journalisation des requêtes et sauvegardes lentes

Une frustration courante avec le journal des requêtes lentes survient lorsque vous utilisez également MySQLDump pour créer des sauvegardes de base de données. De longue durée SELECT * FROM ... des requêtes seront exécutées pour récupérer les données de vos tables et les alimenter dans votre sauvegarde. Ils seront inclus dans le journal des requêtes lentes comme toute autre instruction SQL. Cela peut polluer le journal si vous faites des sauvegardes régulièrement.

Vous pouvez résoudre ce problème en désactivant temporairement le journal des requêtes lentes avant d’exécuter mysqldump. Vous pouvez réactiver le journal une fois la sauvegarde terminée. Ajustez votre script de sauvegarde pour qu’il ressemble à ce qui suit :

#!/bin/bash

# Temporarily disable slow query logging
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=0";

# Run mysqldump
mysqldump -uUser -pPassword --single-transaction databaseName | gzip > backup.bak

# Enable the slow query log again
mysql -uUser -pPassword -e "SET GLOBAL slow_query_log=1"

Cela empêchera l’activité de MySQLDump d’entrer dans le journal des requêtes lentes, ce qui vous permettra de vous concentrer plus facilement sur le SQL exécuté par votre application.

Sommaire

Le journal des requêtes lentes MySQL est l’un des moyens les plus efficaces pour identifier la cause des problèmes de performances. Commencez par estimer le retard que vous rencontrez et utilisez cette valeur comme votre long_query_time. Réduisez la valeur si rien ne s’affiche dans le journal après avoir reproduit le problème.

Le journal des requêtes lentes ne vous dira pas exactement comment corriger le ralentissement. Cependant, la possibilité d’afficher le SQL exact reçu par le serveur vous permet de répéter des instructions peu performantes, puis d’évaluer l’effet des optimisations. L’ajout d’un index ou d’une contrainte manquante peut faire la différence entre une requête qui touche des milliers de lignes et une qui fonctionne avec une poignée.