Les requêtes lentes et inattendues 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 sollicité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. Il est donc difficile d'isoler les 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 de la requête lente Journal
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” ; s'il faut plus de 10 secondes pour 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 définissant la variable long_query_time :
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 un journal excessivement bruyant.
Utilisation du 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. Il se trouve généralement dans /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 “lent” seuil et ont été soumis par des applications client. 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 susceptibles d'être lentes à l'avenir.
Vous pouvez étendre le journal pour inclure ces informations en apporter les modifications suivantes à votre fichier de configuration :
- log_slow_admin_statements = 1– Inclut des instructions SQL administratives telles que ALTER TABLE, CREATE INDEX, DROP INDEX et 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éplicas. Ceci est désactivé par défaut. Utilisez plutôt log_slow_slave_statements pour les versions 8.0.26 et antérieures de MySQL.
- 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'excluent pas 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 pour lesquelles un index est disponible seront toujours enregistrées si elles manquent de contraintes limitant le nombre de lignes extraites.
L'enregistrement des requêtes qui n'utilisent pas d'index peut 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 la variable log_throttle_queries_not_using_indexes. 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éter le journal des requêtes lentes
< p>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 sous 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 indiquer que des index manquants poussent MySQL à inspecter trop d'enregistrements.
Vous pouvez éventuellement inclure plus d'informations dans le journal en définissant la variable système log_slow_extra = 1 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 prises en compte 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 et sauvegardes lentes des requêtes
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. Des requêtes SELECT * FROM … de longue durée seront exécutées pour récupérer les données de vos tables et les alimenter dans votre sauvegarde. Elles seront incluses dans le journal des requêtes lentes comme toute autre instruction SQL. Cela peut polluer le journal si vous effectuez 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 # Désactiver temporairement la journalisation des requêtes lentes mysql -uUser -pPassword -e “SET GLOBAL slow_query_log=0” ; # Exécutez mysqldump mysqldump -uUser -pPassword –single-transaction databaseName | gzip > backup.bak # Activez à nouveau le journal des requêtes lentes mysql -uUser -pPassword -e “SET GLOBAL slow_query_log=1”
Cela gardera l'activité MySQLDump hors du journal des requêtes lentes, ce qui facilitera la concentration sur le SQL qui ;s exécuté par votre application.
Résumé
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 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 résoudre 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.
LIRE LA SUITE
- › 10 fonctionnalités du casque VR Quest que vous devriez utiliser
- &rsaquo ; Examen de la chaise de jeu Vertagear SL5000 : confortable, réglable, imparfaite
- &rsaquo ; Quelle est la meilleure distance de visionnage de la télévision ?
- › Android 13 est sorti : nouveautés et date de disponibilité
- &rsaquo ; 6 choses qui ralentissent votre Wi-Fi (et que faire à leur sujet)
- &rsaquo ; Comment ajouter des visualisations Winamp à Spotify, YouTube, etc.