Le journal des requêtes Mysql doit-il être désactivé ? Que sont les « journaux du serveur », comment afficher les journaux du serveur. Qu'est-ce qu'un journal de requêtes lent dans MySQL

Le profilage des requêtes MySQL est une technique utile pour analyser les performances globales des applications basées sur des bases de données. Lors du développement d'applications de taille moyenne à grande, des centaines de requêtes sont généralement réparties sur une grande base de code, et la base de données traite de nombreuses requêtes par seconde. Sans profilage des requêtes, il devient très difficile de déterminer l'emplacement et les causes de l'événement. goulots d'étranglement applications. Ce didacticiel décrit quelques techniques utiles de profilage de requêtes utilisant les outils intégrés de MySQL.

Journal des requêtes lentes MySQL

Le journal des requêtes lentes MySQL (ou journal des requêtes lentes) est un journal dans lequel MySQL envoie des requêtes lentes et potentiellement problématiques.

Cette fonctionnalité est fournie avec MySQL mais est désactivée par défaut. MySQL détermine quelles requêtes doivent être incluses dans ce journal à l'aide de variables spéciales qui vous permettent de profiler la requête en fonction des exigences de performances de l'application. En règle générale, les requêtes dont le traitement est plus long et les requêtes dont les index sont incorrects sont enregistrées dans ce journal.

Variables de profilage

Les variables de base du serveur pour configurer le journal des requêtes lentes MySQL sont :

slow_query_log global
slow_query_log_file global
long_query_time global/session
log_queries_not_using_indexes global
min_examined_row_limit global/session

slow_query_log – une variable booléenne pour activer ou désactiver le journal des requêtes lentes.

slow_query_log_file – chemin absolu du fichier journal des requêtes. Le répertoire de fichiers doit appartenir à l'utilisateur mysqld et disposer des autorisations de lecture et d'écriture appropriées. Le démon mysql sera très probablement démarré en tant que mysql, mais pour être sûr, exécutez la commande dans un terminal Linux :

ps-ef | grep bin/mysqld | couper -d" " -f1

La sortie affichera l'utilisateur actuel et l'utilisateur mysqld.

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

• long_query_time – temps en secondes pour vérifier la longueur de la requête. Si la valeur est 5, toutes les demandes dont le traitement prend plus de 5 secondes seront enregistrées.
• log_queries_not_using_indexes – Une valeur booléenne qui détermine si les requêtes qui n'utilisent pas d'index doivent être enregistrées. Lors de l'analyse, ces requêtes sont importantes.
• min_examined_row_limit – définit le nombre minimum de lignes à analyser. Avec une valeur de 1 000, toutes les requêtes qui analysent moins de 1 000 lignes seront ignorées.

Les variables du serveur MySQL peuvent être définies dans le fichier de configuration MySQL ou dynamiquement en utilisant interface utilisateur ou en ligne de commande MySQL. Si des variables sont définies dans le fichier de configuration, elles persisteront au redémarrage du serveur, mais le serveur doit être redémarré pour les activer. Le fichier de configuration MySQL se trouve généralement dans /etc/my.cnf ou /etc/mysql/my.cnf. Pour trouver le fichier de configuration, saisissez (vous devrez peut-être étendre votre recherche à d'autres répertoires racine) :

trouver /etc -name mon.cnf
trouver /usr -name mon.cnf

Une fois que vous avez trouvé le fichier de configuration, ajoutez les variables requises à la section :

….
journal des requêtes lentes = 1
fichier-log-slow-query = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-requêtes-n'utilisant pas d'index

Pour que les modifications prennent effet, vous devez redémarrer le serveur. Si les modifications doivent être activées immédiatement, définissez les variables de manière dynamique :

mysql> SET GLOBAL slow_query_log = "ON" ;
mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";
mysql> SET GLOBAL log_queries_not_using_indexes = "ON" ;
mysql> SET SESSION long_query_time = 1 ;
mysql> SET SESSION min_examined_row_limit = 100 ;

Pour vérifier les valeurs des variables :

mysql> AFFICHER LES VARIABLES GLOBALES COMME "slow_query_log" ;
mysql> AFFICHER LES VARIABLES DE SESSION COMME "long_query_time" ;

L'un des inconvénients de la modification dynamique des variables MySQL est que les variables seront perdues au redémarrage du serveur. Par conséquent, toutes les variables importantes qui doivent être enregistrées doivent être ajoutées au fichier.

Générer une requête de profilage

Vous êtes désormais familiarisé avec les paramètres du journal des requêtes lentes. Essayez de générer des données de requête pour le profilage.

Note: L'exemple donné ici a été exécuté sur une instance MySQL en cours d'exécution sans aucun journal de requêtes lentes configuré. Ces requêtes de test peuvent être exécutées via Interface graphique ou ligne de commande MySQL.

Lors de la surveillance du journal des requêtes lentes, il est utile d'ouvrir deux fenêtres de terminal : une connexion pour l'envoi Instructions MySQL, et le second sert à afficher le journal des demandes.

Connectez-vous au serveur MySQL à l'aide de la console en tant qu'utilisateur disposant des privilèges SUPER ADMIN. Pour commencer, créez une base de données et une table de test, ajoutez-y des données factices et activez la journalisation lente des requêtes.

Note: Idéalement, il est préférable d'exécuter cet exemple dans un environnement sans aucune autre application utilisant MySQL pour éviter d'encombrer le journal des requêtes.

$> mysql -u -p
mysql> CREATE DATABASE profile_sampling ;

mysql> USE profile_sampling ;

mysql> Utilisateurs CREATE TABLE (id TINYINT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(255)) ;

mysql> INSÉRER DANS les utilisateurs (nom) VALEURS ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr."),("Marie"),("Saul "),("Gustavo"),("Hector"),("Mike");

mysql> SET GLOBAL slow_query_log = 1 ;

mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";

mysql> SET GLOBAL log_queries_not_using_indexes = 1 ;

mysql> SET long_query_time = 10 ;

mysql> SET min_examined_row_limit = 0 ;

Vous disposez maintenant d’une base de données de test et d’une table avec quelques données. Le journal des requêtes lentes est activé. Nous avons délibérément fixé le temps de traitement des demandes à un niveau élevé et désactivé la vérification du nombre de lignes. Pour afficher le journal, saisissez :

cd /var/log/mysql
ls-l

Pour l'instant, il ne devrait pas y avoir de journal des requêtes lentes dans le dossier, car ce moment il n'y a eu aucune demande. Si un tel journal existe déjà, cela signifie que la base de données a déjà rencontré des requêtes lentes depuis que vous avez activé la prise en charge du journal des requêtes lentes. Cela peut fausser les résultats de cet exemple. Revenez à l'onglet MySQL et exécutez :

mysql> USE profile_sampling ;
mysql> SELECT * FROM utilisateurs WHERE id = 1;

La requête exécutée récupère simplement les données et utilise l'index de la première clé de la table. Cette requête était rapide et utilisait un index, elle n'est donc pas enregistrée dans le journal des requêtes lentes. Revenez au répertoire et assurez-vous qu'aucun journal de requêtes n'a été créé. Revenez maintenant à la fenêtre MySQL et exécutez :

mysql>

Cette requête n'utilise pas d'index. Maintenant, quelque chose comme ceci devrait apparaître dans le journal /var/log/mysql/localhost-slow.log :

# Heure : 140322 13:54:58

utilisez profile_sampling ;
SET horodatage = 1395521698 ;

Encore un exemple. Augmentez le nombre minimum de lignes à analyser et envoyez une requête comme celle-ci :

mysql> SET min_examined_row_limit = 100 ;
mysql> SELECT * FROM utilisateurs WHERE nom = "Walter" ;

Les données ne seront pas ajoutées au journal car moins de 100 lignes ont été analysées lors de la requête.

Note: Si les données n'ont pas été ajoutées au journal, vous devez vérifier plusieurs facteurs. Vérifiez d'abord les autorisations du répertoire dans lequel le journal est créé. Il doit appartenir à l'utilisateur/groupe mysqld et disposer des privilèges chmod 755. Vous devez ensuite vérifier s'il existe d'autres paramètres de requête lente sur le serveur qui remplacent vos paramètres. Réinitialisez les valeurs par défaut pour supprimer toutes les variables de requête lente du fichier de configuration et redémarrez le serveur. Vous pouvez également définir dynamiquement les variables globales sur leurs valeurs par défaut. Si vous apportez des modifications de manière dynamique, déconnectez-vous et reconnectez-vous à MySQL pour mettre à jour les paramètres.

Analyse des données de profilage de requête

Considérez les données suivantes :

# Heure : 140322 13:54:58
#Utilisateur@Hôte : root@localhost
# Query_time : 0,000303 Lock_time : 0,000090 Rows_sent : 1 Rows_examined : 10
utilisez profile_sampling ;
SET horodatage = 1395521698 ;
SELECT * FROM utilisateurs WHERE nom = "Jesse" ;

Cette entrée affiche :

• Temps d'exécution de la requête
• Qui l'a envoyé
• Combien de temps a-t-il fallu pour traiter la demande ?
• Longueur
• Combien de lignes ont été renvoyées
• Combien de lignes ont été analysées

Ceci est utile car toute demande qui viole les exigences de performances spécifiées dans les variables se retrouve dans le journal. Cela permet à un développeur ou à un administrateur de retrouver rapidement les demandes qui ne fonctionnent pas. De plus, les données de profilage des requêtes peuvent vous aider à déterminer les circonstances qui entraînent de mauvaises performances de votre application.

Utiliser mysqldumpslow

Le profilage peut être inclus dans les applications basées sur des bases de données pour garantir un flux de données modéré.

À mesure que la taille du journal augmente, il devient difficile d'analyser toutes les données et les requêtes problématiques peuvent facilement s'y perdre. MySQL propose un outil appelé mysqldumpslow, qui permet d'éviter ce problème en divisant le journal des requêtes lentes. Le binaire est lié à MySQL (sous Linux), vous pouvez donc simplement exécuter la commande :

mysqldumpslow -t 5 -s dans /var/log/mysql/localhost-slow.log

La commande peut accepter divers paramètres pour personnaliser sa sortie. L'exemple ci-dessus affichera les 5 principales requêtes triées par durée moyenne de requête. Ces chaînes sont plus lisibles et sont également regroupées par requête.

Nombre : 2 Temps = 68,34 s (136 s) Verrouillage = 0,00 s (0 s) Lignes = 39892974,5 (79785949), root@localhost
SELECT PL.pl_title, P.page_title
DE la page P
Liens vers les pages INNER JOIN PL
SUR PL.pl_namespace = P.page_namespace
OÙ P.page_namespace = N
…

La sortie affiche les données suivantes :

• Nombre : combien de fois la demande a été enregistrée.
• Temps : temps moyen et total de la demande (entre parenthèses).
• Verrouillage : temps de verrouillage de la table.
• Lignes : nombre de lignes renvoyées.

La commande exclut les valeurs numériques et de chaîne, de sorte que les requêtes identiques avec des conditions WHERE différentes sont traitées de la même manière. L'outil mysqldumpslow élimine le besoin de consulter constamment le journal des requêtes lentes, vous permettant à la place d'effectuer des requêtes régulières. contrôles automatiques. Les options de la commande mysqldumpslow vous permettent d'exécuter des expressions complexes.

Répartition de la demande

Un autre outil de profilage à garder à l’esprit est l’outil de répartition des requêtes complexes. Il vous permet d'identifier les requêtes problématiques dans le journal des requêtes lentes et de les exécuter dans MySQL. Vous devez d'abord activer le profilage, puis exécuter la requête :

mysql> SET SESSION profilage = 1 ;
mysql> USE profile_sampling ;
mysql> SELECT * FROM utilisateurs WHERE nom = "Jesse" ;
mysql> AFFICHER LES PROFILS ;

Une fois le profilage activé, SHOW PROFILES affichera un tableau qui associe le Query_ID à l'expression SQL. Recherchez le Query_ID correspondant à la requête en cours d'exécution et exécutez la requête suivante (remplacez # par votre Query_ID) :

mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

La commande renverra un tableau :

SEQ	ÉTAT	DURÉE
1	départ	0.000046
2	vérification des autorisations	0.000005
3	ouvrir les tables	0.000036
…	…	…

STATE est une étape du processus d'exécution de la requête et DURATION est le temps nécessaire pour terminer cette étape en secondes. Ce n'est pas très outil utile, mais cela peut aider à déterminer quelle partie de l'exécution de la requête entraîne le plus de latence.

Note Remarque : Cet outil ne doit pas être utilisé dans un environnement de production.

Performances lentes du journal des requêtes

Il ne reste plus qu'à comprendre comment la lenteur du journal des requêtes affecte les performances. En général, il est sûr d'exécuter des journaux de requêtes lents dans un environnement de production ; Ni le processeur ni les E/S ne devraient être affectés. Cependant, vous devez disposer d'une stratégie de surveillance de la taille du journal afin de garantir que le journal ne devienne pas trop volumineux pour le système de fichiers. De plus, lors de l'exécution d'un journal de requêtes lent dans un environnement de production, vous devez définir long_query_time sur 1 ou supérieur.

Conclusion

Un journal de requêtes lent peut vous aider à identifier les requêtes problématiques et à évaluer les performances globales des requêtes. Dans le même temps, le développeur peut avoir une compréhension détaillée du fonctionnement de l'application. Requêtes MySQL. L'outil mysqldumpslow vous permet de gérer les journaux de requêtes lentes et de les intégrer facilement dans votre processus de développement. En identifiant les requêtes problématiques, vous pouvez optimiser le traitement des requêtes pour améliorer les performances.

Mots clés:

Les journaux d'événements constituent le premier et le plus simple outil permettant de déterminer l'état du système et d'identifier les erreurs. Il existe quatre journaux principaux dans MySQL :

• Journal des erreurs— journal des erreurs standard collecté pendant l'exécution du serveur (y compris le démarrage et l'arrêt) ;
• Journal binaire— un journal de toutes les commandes de modification de la base de données, nécessaires à la réplication et aux sauvegardes ;
• Journal des requêtes générales— journal des requêtes principal ;
• Journal des requêtes lentes— journal des requêtes lentes.

Journal des erreurs

Ce journal contient toutes les erreurs survenues lors de l'exécution du serveur, y compris les erreurs critiques, ainsi que les arrêts et démarrages du serveur et les avertissements. C'est par là que vous devriez commencer en cas de panne du système. Par défaut, toutes les erreurs sont affichées sur la console (stderr), vous pouvez également enregistrer les erreurs dans syslog (par défaut sur Debian) ou dans un fichier journal séparé :

Log_error=/var/log/mysql/mysql_error.log

# Les erreurs seront écrites dans mysql_error.log

Nous vous recommandons de garder ce journal activé pour identifier rapidement les erreurs. Et pour comprendre ce que signifie telle ou telle erreur, MySQL dispose de l'utilitaire perror :

Shell> perror 13 64 Code d'erreur du système d'exploitation 13 : autorisation refusée Code d'erreur du système d'exploitation 64 : la machine n'est pas sur le réseau

# Explique la signification des codes d'erreur

Journal binaire (alias binaire)

Toutes les commandes de modification de la base de données sont enregistrées dans le journal binaire, utile pour la réplication et la récupération.

Il s'allume comme ceci :

Log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 500 M

# Indique l'emplacement, la durée de vie et taille maximum déposer

Veuillez noter que si vous n'allez pas faire évoluer le système et implémenter la tolérance aux pannes, il est préférable de ne pas activer le journal binaire. Cela consomme beaucoup de ressources et réduit les performances du système.

Journal des demandes

Ce journal contient toutes les requêtes SQL reçues et des informations sur les connexions client. Peut être utile pour l'analyse et l'optimisation des index, ainsi que pour identifier les requêtes erronées :

Fichier_log_général = /var/log/mysql/mysql.log journal_général = 1

# Inclut le journal et indique l'emplacement du fichier

Vous pouvez également l'activer/désactiver pendant que le serveur MySQL est en cours d'exécution :

SET GLOBAL general_log = "ON" ; SET GLOBAL general_log = "OFF" ;

# Vous n'avez pas besoin de redémarrer le serveur pour l'utiliser

Journal des requêtes lent

Le journal est utile pour identifier les requêtes lentes, c'est-à-dire inefficaces. En savoir plus dans cet article.

Affichage des journaux

Pour afficher les journaux sur Debian (Ubuntu), vous devez exécuter :

# Queue du journal des erreurs -f /var/log/syslog #Queue du journal des requêtes -f /var/log/mysql/mysql.log # Enregistrer les demandes lentes tail -f /var/log/mysql/mysql-slow.log

# Si les journaux ne sont pas spécifiés séparément, ils se trouvent dans /var/lib/mysql

Rotation des journaux

N'oubliez pas de compresser (archiver, faire pivoter) les fichiers journaux afin qu'ils prennent moins de place sur le serveur. Pour ce faire, utilisez l'utilitaire logrotation en éditant le fichier de configuration /etc/logrotate.d/mysql-server:

# - J'ai tout mis dans un seul bloc et j'ai ajouté des scripts partagés, pour que mysql n'obtienne # flush-logs"d qu'une seule fois. # Sinon, les journaux binaires augmenteraient automatiquement de n fois chaque jour. # - Le journal des erreurs est obsolète, les messages vont maintenant dans syslog./var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log(rotation quotidienne 7 manquantok créer 640 mysql adm compresser les scripts partagés postrotate test -x /usr/bin/mysqladmin || exit 0 # Si cela échoue, vérifiez debian.conf ! MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf" if [ -z "`$MYADMIN ping 2>/dev/null`" ]; alors # Vraiment pas de mysqld ou plutôt un utilisateur debian-sys-maint manquant ? # Si cela se produit et ne constitue pas une erreur, veuillez signaler un bug. #si ps cax | grep -q mysqld; alors si killall -q -s0 -umysql mysqld ; puis quittez 1 fi sinon $MYADMIN flush-logs fi endscript )

# Compresse et archive les journaux nécessaires, nettoie les fichiers

Journal DDL

MySQL maintient également un journal du langage des données. Il collecte des données à partir d'opérations telles que DROP_TABLE et ALTER_TABLE. Le journal est utilisé pour récupérer des échecs survenus lors de ces opérations. Le journal DDL est un fichier binaire et n'est pas destiné à être lu par l'utilisateur, donc ne le modifiez pas et ne le supprimez pas.

Le plus important

Activez toujours le journal des erreurs, utilisez le journal des requêtes pour vérifier la connexion de l'application à la base de données, vérifiez les requêtes et le fonctionnement. Le journal des requêtes lentes est utile pour optimiser les performances de MySQL.

Concept

Journaux du serveur (fichiers journaux, journal du serveur)- les fichiers stockés sur le serveur qui contiennent des informations système du serveur, ainsi que la journalisation de toutes les données possibles sur le visiteur de la ressource Web.

Les journaux sont utilisés par les administrateurs système pour analyser les visiteurs, étudiant les comportements de certains groupes d’utilisateurs, ainsi que l’obtention de diverses informations les concernant, telles que : le navigateur utilisé, l’adresse IP, les données sur la localisation géographique du client et bien plus encore. En plus de l'analyse, vous pouvez ainsi vous renseigner sur l'accès non autorisé au site, savoir plus précisément qui l'a réalisé exactement et transférer les données relatives à cette affaire aux autorités compétentes.

Les données du fichier journal, dans leur forme pure, ne seront pas compréhensibles pour les utilisateurs ordinaires, qui ne verront dans tout cela qu'un ensemble de caractères dans un ordre incompréhensible. Mais pour administrateurs système et les développeurs Web, c'est un texte très lisible et des informations très utiles.

Séquence d'événements

Chaque fois qu'un client accède à une ressource Web, plusieurs événements sont déclenchés à la fois, dont nous parlerons de la séquence.

1. Faire une demande de page. Lorsque vous saisissez une adresse dans la ligne du navigateur ou lorsque vous suivez un lien Web actif, par exemple à partir d'une page de résultats d'un moteur de recherche, le navigateur recherche et se connecte au serveur sur lequel se trouve la page et en fait la demande. Parallèlement, il transmet les informations suivantes au serveur :
- Adresse IP de l'ordinateur client qui demande la page (si vous utilisez un serveur proxy, l'adresse IP de votre proxy) ;
- adresse de la page Internet demandée par l'utilisateur (adresse IP) ;
- l'heure et la date exactes auxquelles la demande a été faite ;
- des données sur l'emplacement réel du client (si un serveur proxy est utilisé, alors l'adresse proxy réelle) ;
- des informations sur le navigateur utilisé par le client (nom, version, etc.) ;
- des données sur la page Web à partir de laquelle le client a transféré.

2. Transfert des données demandées. Les données demandées (page web, fichiers, cookies, etc.) sont transférées du serveur vers l’ordinateur de l’utilisateur.

3. Écrivez dans le journal du serveur. Après tout, une entrée de journal apparaît, qui indique toutes les données apparues au cours des deux derniers événements. Il s'agit de toutes les informations envoyées dans le premier paragraphe, ainsi que des informations sur les données transmises.

Comment afficher les journaux du serveur

Les fichiers journaux sont stockés dans un fichier accès.log quel que soit le type de serveur web que vous utilisez (Apache, Nginx, proxy squid, etc.) Ce fichier est document texte, sur chaque ligne duquel est écrit un appel. Formats d'enregistrement dans accès.log beaucoup, mais le plus populaire est combiné, dans lequel l'entrée a la forme et la séquence suivantes :

Code : %h %l %u %t \"%r\" %>s %b \"%(Référent)i\" \"%(User-Agent)i\"
Où:

%h- l'adresse hôte/IP à partir de laquelle la demande a été faite ;
%t- l'heure de la requête au serveur et le fuseau horaire du serveur ;
%r- version, contenu et type de demande ;
%s- Code d'état HTTP ;
%b- le nombre d'octets envoyés par le serveur ;
%(Référent)- URL source de la demande ;
%(Agent utilisateur)- En-tête HTTP, avec des informations sur la requête (application cliente, langue, etc.) ;
%(Hôte)- le nom de l'hôte virtuel auquel vous accédez.

Une fois terminée, cette ligne ressemble à ceci :

127.0.0.1 - - "GET /index.php HTTP/1..0 (compatible ; MSIE 7.0 ; Windows NT 5.1)"

La lecture manuelle des journaux prendra beaucoup de temps et d’efforts. Par conséquent, les webmasters expérimentés utilisent un logiciel spécial appelé « Log File Analyzers ». Ils analysent toutes les données, assez difficiles à lire pour les humains, et produisent des données structurées. Il s'agit de programmes tels que : Analogique, WebAnalizer, Webalizer, Awstats, Webtrends, etc. Types de spécial logiciel beaucoup, parmi eux il y a comme programmes payants, et libre. Par conséquent, je suis sûr que chacun trouvera quelque chose à son goût.

Où trouver les journaux du site

Si vous disposez d'un hébergement régulier, vous devrez probablement écrire à votre hébergeur et lui demander des journaux. De plus, bien souvent, vous pouvez les demander via le panneau d'hébergement. Différents hébergeurs le font différemment. Par exemple, pour faire une demande à mon hébergeur, il suffit de cliquer sur page d'accueil panneaux :

Si vous avez accès à dossiers système serveur, vous pouvez trouver les journaux sur /etc/httpd/logs/access_log dans 99 cas sur 100.

Journal des erreurs error.log

Journal d'erreur- un fichier dans lequel sont également conservés les logs. Mais pas les visiteurs, mais les erreurs survenues sur le serveur. Comme c'est le cas avec accès.log, chaque ligne du fichier est responsable d'une erreur survenue. L'enregistrement est effectué en tenant compte d'informations telles que : la date et l'heure exactes de l'erreur, l'adresse IP à laquelle l'erreur a été émise, le type d'erreur, ainsi que la raison de son apparition.

Conclusion

Les journaux sont un outil assez puissant et informatif avec lequel travailler. Mais de nos jours, ils sont remplacés par des outils tels que Yandex.Metrica, Google Analytics, etc., nous facilitant ainsi la vie. Cependant, si vous envisagez de développer, de grandir et d'apprendre quelque chose de nouveau, je vous recommande certainement de mieux connaître ce sujet.

Requêtes de profilage dans Mysql utilisé pour évaluer les performances de votre application. Lorsque vous développez des applications de moyenne à grande taille, vous devez gérer des centaines de requêtes réparties dans votre code et exécutées chaque seconde. Sans techniques de profilage de requêtes, il peut être très difficile de déterminer la cause de la baisse des performances de votre application.

Qu’est-ce que le journal des requêtes lentes dans MySQL ?

MySQL Slow Query Log - un journal qui signale les requêtes lentes et potentiellement problématiques. MySQL prend en charge cette fonctionnalité par défaut, mais elle est désactivée. En définissant certaines variables du serveur, nous pouvons spécifier les requêtes qui nous intéressent. Le plus souvent, nous avons besoin de requêtes qui nécessitent un certain temps pour être exécutées ou de requêtes qui ne traitent pas correctement les index.

Définition des variables de profilage

Principales variables de configuration du journal des requêtes :

Slow_query_log G slow_query_log_file G long_query_time G/S log_queries_not_using_indexes G min_examined_row_limit G/S

Commentaire: G - variables globales, S - variables système

• slow_query_log - valeur booléenne incluant le journal
• slow_query_log_file - chemin absolu vers le fichier journal. Le propriétaire du répertoire doit être un utilisateur mysqld, et le répertoire doit disposer des autorisations de lecture et d'écriture appropriées. Le plus souvent, le démon MySQL s'exécute en tant qu'utilisateur mysql.

Pour vérifier, exécutez les commandes suivantes :

Ps-ef | grep bin/mysqld | couper -d" " -f1

Le résultat de la commande vous donnera le nom de l'utilisateur actuel et de l'utilisateur mysqld. Exemple de configuration du répertoire /var/log/mysql :

Cd /var/log sudo mkdir mysql sudo chmod 755 mysql sudo chown mysql:mysql mysql

• long_query_time - temps en secondes pour vérifier la durée de la requête. Par exemple, avec une valeur de 5, toute requête durant plus de 5 secondes sera journalisée.
• log_queries_not_using_indexes - valeur booléenne, permet d'enregistrer les requêtes qui n'utilisent pas d'index. De telles requêtes sont très importantes dans l’analyse.
• min_examined_row_limit - spécifie la valeur minimale du nombre de lignes de données à analyser. Une valeur de 1 000 ignorera les requêtes renvoyant moins de 1 000 lignes de valeurs.

Ces variables peuvent être définies dans le fichier de configuration MySQL, dynamiquement via l'interface graphique MySQL ou la ligne de commande MySQL. Si les variables sont spécifiées dans le fichier de configuration, le serveur les installera au prochain démarrage. Généralement, ce fichier se trouve dans /etc, /usr, /etc/my.cnf ou /etc/mysql/my.cnf. Voici les commandes pour rechercher le fichier de configuration (vous devez parfois étendre la recherche à d'autres répertoires racine) :

Rechercher /etc -name mon.cnf rechercher /usr -nom mon.cnf

Lorsque vous trouvez le fichier, ajoutez les variables requises dans la section :

; ... slow-query-log = 1 slow-query-log-file = /var/log/mysql/localhost-slow.log long_query_time = 1 log-queries-not-using-indexes ; aucun sens n'est nécessaire ici

Les modifications ne prendront effet qu'au prochain démarrage de MySQL ; si vous devez modifier dynamiquement les paramètres, utilisez d'autres méthodes pour définir les variables :

Mysql> SET GLOBAL slow_query_log = "ON" ; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = "ON" ; mysql> SET SESSION long_query_time = 1 ; mysql> SET SESSION min_examined_row_limit = 100 ;

Vous pouvez vérifier les valeurs des variables comme suit :

Mysql> AFFICHER LES VARIABLES GLOBALES COMME "slow_query_log" ; mysql> AFFICHER LES VARIABLES DE SESSION COMME "long_query_time" ;

Le principal inconvénient d'une installation dynamique est que les valeurs seront perdues au démarrage du système. Il est recommandé de spécifier les paramètres importants dans la configuration MySQL.

La note: Syntaxe pour le réglage dynamique des paramètres via Commande SET et l'utilisation du fichier de configuration est légèrement différente, par exemple slow_query_log / slow-query-log . Vous trouverez une description complète de la syntaxe dans la documentation officielle du SGBD. Le format Option-File est utilisé pour le fichier de configuration, System Variable Name - noms de variables lors de la définition dynamique des valeurs.

Génération de données pour le profilage des requêtes

Nous avons passé en revue les principaux points de mise en place du profilage, nous allons maintenant créer les requêtes qui nous intéressent. Cet exemple a été utilisé sur un Serveur MySQL sans aucun paramètre de journal préliminaire. Des exemples de requêtes peuvent être lancés à la fois via l'interface graphique MySQL et les outils de commande du SGBD. Lors de la surveillance du journal des requêtes lentes, il est courant d'ouvrir deux fenêtres avec une connexion : une pour exécuter des requêtes, l'autre pour afficher le journal.

$> mysql -u -p mysql> CREATE DATABASE profile_sampling; mysql> USE profile_sampling ; mysql> Utilisateurs CREATE TABLE (id TINYINT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(255)) ; mysql> INSÉRER DANS les utilisateurs (nom) VALEURS ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr."),("Marie"),("Saul "),("Gustavo"),("Hector"),("Mike"); mysql> SET GLOBAL slow_query_log = 1 ; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = 1 ; mysql> SET long_query_time = 10 ; mysql> SET min_examined_row_limit = 0 ;

Nous avons maintenant une base de données avec des données de test. Nous avons lancé le profilage, mais nous avons délibérément fixé le temps de réponse et le nombre de lignes à un niveau réduit. Pour afficher le journal, utilisez la commande :

Cd /var/log/mysql ls -l

En théorie, le fichier journal ne devrait pas encore exister, puisque nous n'avons pas effectué de requêtes dans la base de données. S'il existe, cela signifie que le profilage a été configuré précédemment, ce qui peut fausser les résultats de l'exemple. Exécutez dans la console :

Mysql> UTILISER profile_sampling ; mysql> SELECT * FROM utilisateurs WHERE id = 1;

Notre requête utilise l'index de clé primaire de la table. La demande est traitée très rapidement à l'aide de l'index, elle ne doit donc pas être reflétée dans le journal. Veuillez noter que le fichier journal n'aurait pas dû être créé.

Maintenant, procédez comme suit :

Mysql> SELECT * FROM utilisateurs WHERE nom = "Jesse" ;

Ici, nous n'avons pas utilisé d'index. Nous devrions maintenant voir cette requête dans le journal :

Sudo cat /var/log/mysql/localhost-slow.log # Heure : 140322 13:54:58 # Utilisateur@Hôte : root @ localhost # Query_time : 0.000303 Lock_time : 0.000090 Rows_sent : 1 Rows_examined : 10 utiliser profile_sampling ; SET horodatage = 1395521698 ; SELECT * FROM utilisateurs WHERE nom = "Jesse" ;

Regardons un autre exemple. Relevez la barre sur le nombre de lignes dans la réponse et exécutez la requête suivante :

Mysql> SET min_examined_row_limit = 100 ; mysql> SELECT * FROM utilisateurs WHERE nom = "Walter" ;

La demande ne sera pas reflétée dans le journal, puisque nous n'avons pas dépassé 100 lignes dans la réponse à la demande.

La note: Si les données ne sont pas affichées dans le journal, vous devez tout d'abord prendre en compte les facteurs suivants. Le premier concerne les droits sur le répertoire où le fichier journal est stocké. Le groupe et l'utilisateur doivent correspondre à l'utilisateur mysqld, les droits doivent être chmod 755. Deuxièmement, le profilage peut avoir été configuré plus tôt. Supprimez toutes les valeurs de variables de profilage existantes du fichier de configuration et redémarrez le serveur ou définissez les variables de manière dynamique. Si vous avez utilisé la méthode dynamique, vous quitterez et vous reconnecterez à la console MySQL.

Analyse des données de profilage des requêtes

Considérez l'exemple ci-dessus :

# Heure : 140322 13:54:58 # Utilisateur@Hôte : root @ localhost # Query_time : 0,000303 Lock_time : 0,000090 Rows_sent : 1 Rows_examined : 10 utiliser profile_sampling ; SET horodatage = 1395521698 ; SELECT * FROM utilisateurs WHERE nom = "Jesse" ;

Ici, nous voyons :

• Heure à laquelle la demande a été lancée
• L'utilisateur qui a fait la demande
• Demandes d'heures d'ouverture
• Durée de verrouillage
• Nombre de lignes sélectionnées
• Nombre de lignes analysées

Ces données sont très utiles, car grâce à elles, nous pouvons trouver et éliminer la cause du ralentissement du système. De plus, un développeur ou un administrateur MySQL pourra toujours voir les requêtes problématiques et je tiens à souligner que les trouver ici est beaucoup plus rapide qu'en étudiant le code de l'application. Avec le profilage à long terme, vous pouvez surveiller les conditions de fonctionnement à basse vitesse.

Utiliser mysqldumpslow

Le journal enregistre en permanence des données et, en règle générale, il écrit beaucoup plus que ce qui y est lu. À grande taille log, sa lecture devient problématique. MySQL inclut un outil appelé mysqldumpslow qui permet de maintenir l'intégrité des journaux. Le programme lui-même est combiné avec MySQL (sur Systèmes Linux). Pour l'utiliser, suivez la commande nécessaire et transmettez-lui le chemin d'accès au fichier journal :

Sudo mysqldumpslow -t 5 -s dans /var/log/mysql/localhost-slow.log

Un certain nombre de paramètres vous aident à personnaliser le résultat de la commande. Dans l'exemple ci-dessous, nous verrons les cinq dernières demandes triées par durée moyenne. En conséquence, la lecture du journal devient beaucoup plus pratique. (sortie modifiée pour afficher les valeurs réelles du journal) :

Nombre : 2 Temps = 68,34 s (136 s) Verrouillage = 0,00 s (0 s) Lignes = 39892974,5 (79785949), root@localhost SELECT PL.pl_title, P.page_title FROM page P INNER JOIN pagelinks PL ON PL.pl_namespace = P.page_namespace OÙ P.page_namespace = N ...

Ce que nous voyons :

• Count - nombre d'occurrences de la demande dans le journal
• Temps - temps moyen et total de la demande
• Verrouillage - temps de verrouillage de la table
• Lignes - Nombre de lignes sélectionnées

La commande exclut les données de requête numériques et de chaîne, ce qui signifie que les requêtes avec la même clause WHERE seront considérées comme identiques. Grâce à cet outil, vous n’avez pas besoin de consulter constamment le journal. En raison du grand nombre de paramètres de commande, vous pouvez trier la sortie comme vous le souhaitez. Il existe également des développements tiers dotés de fonctionnalités similaires, par exemple pt-query-digest.

Répartition de la demande

Vous devriez faire attention à un autre outil qui vous permet de décomposer des requêtes complexes. Le plus souvent, vous devez extraire une requête du journal, puis l'exécuter directement dans la console MySQL. Vous devez d'abord activer le profilage, puis exécuter la requête :

Mysql> SET SESSION profilage = 1 ; mysql> USE profile_sampling ; mysql> SELECT * FROM utilisateurs WHERE nom = "Jesse" ; mysql> AFFICHER LES PROFILS ;

Après avoir activé le profilage, SHOW PROFILES affichera un tableau reliant Query_ID et l'expression SQL. Recherchez le Query_ID correspondant et exécutez la requête suivante (remplacez # par votre Query_ID) :

Mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;

Exemple de sortie :

DURÉE DE L'ÉTAT SEQ 1 démarrage 0,000046 2 vérification des autorisations 0,000005 3 ouverture des tables 0,000036

ÉTAT- une étape du processus d'exécution d'une requête, DURÉE- durée de l'étape en secondes. Cet outil n'est pas utilisé très souvent, mais il peut parfois s'avérer extrêmement utile pour déterminer la cause du ralentissement des performances des requêtes.

Description détaillée des colonnes :

Description détaillée des étapes :

La note: Cet outil ne doit pas être utilisé en mode production serveur, sauf pour analyser des requêtes spécifiques.

Performances lentes du journal des requêtes

La dernière question est de savoir dans quelle mesure le profilage affecte les performances du serveur dans son ensemble. En mode production du serveur, vous pouvez utiliser une telle journalisation en toute sécurité ; elle ne devrait affecter ni le processeur ni les E/S. Cependant, il convient de prêter attention à la taille du fichier journal : il ne doit pas devenir prohibitif. Je voudrais également noter par expérience que définir la valeur de la variable long_query_time sur 1 seconde ou plus.

Important: Vous ne devez pas utiliser l'outil de profilage - SET profiling = 1 - pour enregistrer toutes les demandes, c'est-à-dire Il n'est pas recommandé d'utiliser la variable general_log en mode produit et sous de fortes charges.

Conclusion

Le profilage des requêtes peut vous aider beaucoup à isoler la requête problématique et à évaluer les performances globales. Le développeur peut également étudier le fonctionnement des requêtes MySQL de son application. L'outil mysqldumpslow vous aide à afficher et à traiter les journaux de requêtes. Après avoir identifié les requêtes problématiques, il ne reste plus qu'à les régler pour des performances maximales.