Comment activer le journal des requêtes lentes dans MySQL ou MariaDB ?
À mesure que les bases de données se développent et deviennent un composant essentiel des applications web, l’optimisation des performances devient critique. Dans MySQL® et MariaDB, l’un des moyens les plus efficaces de diagnostiquer les goulets d’étranglement est d’activer le journal des requêtes lentes. Ce journal permet d’identifier les requêtes dont l’exécution est trop longue, ce qui vous permet de les optimiser et d’améliorer les performances globales de votre base de données.
Dans ce guide, nous allons expliquer ce qu’est le journal des requêtes lentes, pourquoi il est important et comment l’activer et le configurer dans MySQL et MariaDB.
Qu’est-ce que le journal des requêtes lentes ?
Le journal des requêtes lentes est un fichier journal qui enregistre les requêtes SQL dont l’exécution prend plus d’un certain temps (le seuil). Par défaut, ce seuil est fixé à 10 secondes, mais il peut être personnalisé. Les requêtes qui dépassent cette limite de temps sont enregistrées, avec des informations telles que la durée de la requête, la date et l’heure d’exécution. Il s’agit d’un outil essentiel pour les développeurs et les administrateurs de bases de données, qui leur permet d’identifier et d’optimiser les requêtes qui s’exécutent lentement, contribuant ainsi à améliorer les performances de la base de données.
Pourquoi activer le journal des requêtes lentes ?
L’activation du journal des requêtes lentes permet de
- Identifier les goulots d’étranglement des performances: Les requêtes lentes peuvent avoir un impact significatif sur les performances de votre application. Le journal vous aide à identifier ces requêtes problématiques.
- Optimisation: En examinant les requêtes lentes, vous pouvez identifier les domaines dans lesquels les index, la restructuration des requêtes ou la mise en cache pourraient améliorer les performances.
- Surveillance des performances des requêtes: Pour l’optimisation continue des performances, le journal des requêtes lentes fournit des informations sur la manière dont les temps d’exécution des requêtes évoluent dans le temps.
Comment activer le journal des requêtes lentes dans MySQL ou MariaDB ?
L’activation du journal des requêtes lentes implique la modification du fichier de configuration et l’ajustement de certains paramètres directement dans l’instance MySQL/MariaDB. Voici comment procéder, étape par étape.
Etape 1 : Accéder au fichier de configuration de MySQL/MariaDB
Les fichiers de configuration de MySQL et MariaDB sont généralement situés à l’adresse suivante :
- Pour MySQL: /etc/my.cnf ou /etc/mysql/my.cnf
- Pour MariaDB: /etc/my.cnf.d/server.cnf ou /etc/mysql/mariadb.cnf
Utilisez votre éditeur de texte préféré pour ouvrir le fichier de configuration. Par exemple, avec nano, vous pouvez exécuter la commande suivante :
sudo nano /etc/my.cnf
Étape 2 : Modifier le fichier de configuration
Dans le fichier de configuration, repérez la ligne [mysqld] . Si elle n’existe pas, vous pouvez la créer. Si elle n’existe pas, vous pouvez la créer. Ajoutez ou modifiez les lignes suivantes pour activer le journal des requêtes lentes :
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2- slow_query_log = 1: Active le journal des requêtes lentes.
- slow_query_log_file: Spécifie l’emplacement où le fichier d’enregistrement des requêtes lentes sera sauvegardé. Vous pouvez définir un chemin et un nom de fichier personnalisés, mais assurez-vous que le répertoire dispose des droits d’écriture appropriés pour MySQL/MariaDB.
- long_query_time = 2: Définit le seuil de ce qui est considéré comme une requête “lente”. Dans ce cas, toute requête prenant plus de 2 secondes sera enregistrée. Vous pouvez ajuster cette valeur en fonction de vos besoins en termes de performances.
Étape 3 : Redémarrer le service MySQL/MariaDB
Après avoir enregistré les modifications dans le fichier de configuration, redémarrez le service MySQL ou MariaDB pour que les changements soient pris en compte.
- Pour les systèmes utilisant systemd :
sudo systemctl restart mysqld # For MySQL
sudo systemctl restart mariadb # For MariaDB
- Pour les systèmes utilisant init.d :
sudo service mysqld restart # For MySQL
sudo service mariadb restart # For MariaDB
Étape 4 : Vérifier que le journal des requêtes lentes est activé
Pour vérifier que le journal des requêtes lentes est activé, connectez-vous au client MySQL/MariaDB en exécutant la commande suivante
mysql -u root -p
Une fois connecté, exécutez la commande suivante :
SHOW VARIABLES LIKE 'slow_query_log';
Vous devriez voir quelque chose comme ceci :
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| slow_query_log | ON |
+-----------------+-------+
Cela confirme que le journal des requêtes lentes est activé.
Étape 5 : Analyse du journal des requêtes lentes
Le journal des requêtes lentes enregistre les requêtes dans le fichier spécifié dans la configuration. Pour visualiser le contenu du journal des requêtes lentes, vous pouvez utiliser un visualiseur de texte tel que less, cat ou tail :
sudo less /var/log/mysql/slow-query.log
Vous verrez des entrées similaires à celles qui suivent :
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: root[root] @ localhost []
# Query_time: 4.561529 Lock_time: 0.000115 Rows_sent: 1 Rows_examined: 50000
SET timestamp=1697030723;
SELECT * FROM large_table WHERE column = 'value';Le journal fournit des détails tels que le temps d’exécution de la requête, le temps de verrouillage, le nombre de lignes envoyées et examinées, ainsi que la requête SQL proprement dite.
Configuration optionnelle du journal des requêtes lentes
Il existe des options de configuration supplémentaires que vous pouvez définir pour adapter le journal des requêtes lentes à vos besoins spécifiques.
1. Journaliser les requêtes sans index
Vous pouvez également activer la journalisation des requêtes qui n’utilisent pas d’index, ce qui est souvent une source de problèmes de performance. Ajoutez la ligne suivante à votre configuration MySQL/MariaDB :
log_queries_not_using_indexes = 1
Cette ligne enregistrera toutes les requêtes qui n’utilisent pas d’index, ce qui vous aidera à identifier et à optimiser les requêtes peu performantes.
2. Limiter la taille du fichier journal
Pour éviter que le journal des requêtes lentes ne devienne trop volumineux, vous pouvez effectuer une rotation des journaux ou limiter leur taille. Sur les systèmes Linux, vous pouvez utiliser logrotate pour gérer la taille et la rotation des fichiers journaux MySQL/MariaDB.
Voici un exemple de base pour /etc/logrotate.d/mysql :
/var/log/mysql/slow-query.log {
daily
rotate 7
missingok
notifempty
compress
delaycompress
postrotate
/usr/bin/systemctl reload mysql >/dev/null 2>&1 || true
endscript
}Cette configuration effectue une rotation quotidienne du fichier journal et conserve les sept derniers journaux, afin que le journal des requêtes lentes ne consomme pas trop d’espace disque.
Étape 6 : Désactivation du journal des requêtes lentes
Une fois que vous avez identifié et optimisé vos requêtes lentes, vous pouvez désactiver le journal des requêtes lentes afin de préserver les ressources. Pour ce faire, modifiez à nouveau le fichier de configuration et définissez slow_query_log à 0 :
[mysqld]
slow_query_log = 0Redémarrez le service MySQL ou MariaDB pour appliquer les modifications.
Conclusion
Le journal des requêtes lentes est un outil inestimable pour l’optimisation des performances des bases de données dans MySQL et MariaDB. En enregistrant les requêtes dont l’exécution est trop longue, vous pouvez identifier les goulots d’étranglement et optimiser les performances des requêtes. Que vous gériez une petite base de données ou une application d’entreprise à grande échelle, l’activation du journal des requêtes lentes est un moyen proactif de garantir la rapidité et l’efficacité de vos bases de données.
En suivant les étapes de ce guide, vous devriez être en mesure d’activer, de configurer et d’analyser le journal des requêtes lentes afin d’optimiser efficacement les performances de votre base de données MySQL ou MariaDB.
