Enable slow queries MySQL logs

From EN Ikoula wiki
⧼vector-jumptonavigation⧽ ⧼vector-jumptosearch⧽

fr:Activer les logs de requêtes lentes MySQL en:Enable slow queries MySQL logs es:Permitir consultas lentas de MySQL logs pt:Permitir consultas lentas os logs do MySQL it:Attivare slow query log di MySQL nl:Langzame vragen MySQL logboeken inschakelen de:Langsame Abfragen MySQL Protokolle aktivieren zh:启用慢速查询 MySQL 日志 ar:تمكين الاستعلامات بطيئة سجلات الخلية ja:低速のクエリ MySQL のログを有効にします。 pl:Po wolnych zapytań MySQL logi ru:Включение медленных запросов MySQL журналы ro:Enable lent întrebare MySQL busteni he:לאפשר שאילתות איטי יומני MySQL
This article has been created by an automatic translation software. You can view the article source here.

fr:Activer les logs de requêtes lentes MySQL he:לאפשר שאילתות איטי יומני MySQL ro:Enable lent întrebare MySQL busteni ru:Включение медленных запросов MySQL журналы pl:Po wolnych zapytań MySQL logi ja:低速のクエリ MySQL のログを有効にします。 ar:تمكين الاستعلامات بطيئة سجلات الخلية zh:启用慢速查询 MySQL 日志 de:Langsame Abfragen MySQL Protokolle aktivieren nl:Langzame vragen MySQL logboeken inschakelen it:Attivare slow query log di MySQL pt:Permitir consultas lentas os logs do MySQL es:Permitir consultas lentas de MySQL logs en:Enable slow queries MySQL logs

Introduction

May receive alerts from monitoring about slow mysql queries (slow queries) : ces erreurs sont produites quand le temps d'exécution d'une requête MySQL dépasse le temps alloué défini par la variable MySQL long_query_time. Ces erreurs pouvant être produites par différents facteurs, nous verrons ici comment activer les logs de ces erreurs afin de les analyser et les corriger.

Place

Veuillez noter qu'activer les logs de slow queries peut ralentir votre Server, étant donné que des écritures seront effectuées en même temps que l'exécution des requêtes lentes.

Verify that the logs are not already active

To enone that the logs of slow queries are not already turned on, connect in SSH to your machine and enter the MySQL shell :

mysql -u utilisateur -p
[mot de passe]


Once in the MySQL shell, enter the following command to get the list of all the variables and their values :

show variables;


Navigate to see the variable in the left column "slow_query_log" : sur 'We ', the logs are active. On 'OFF', we have to turn them on.

Slow-queries-1.png

Enable and configure the log slow queries

Always in the MySQL shell, enter the following command :

set global slow_query_log = 'ON';


You can also set a path to the directory where the slow query logs will be stored :

set global slow_query_log_file ='/var/log/mysql/slow-query.log';


Finally, set a minimum time allocated to each request to make sure you have no false positives of slow queries : 10 seconds is a correct value, 20 seconds is recommended if it happens that your Server weaken in the face of the number of queries and visitors (This variable is set to basic 10 seconds) :

set global long_query_time = '20';


Finally, verify that changes were taken into account by the following command :

show variables like '%slow%';


Analyze logs

In the case of monitoring alerts telling you that queries are written as slow, you can now analyze the logs of slow_queries in order to understand the reason for these delays :

# montrera les requêtes en temps réel
tail -f /var/log/mysql/slow-query.log # défini dans notre exemple ci-dessus
# sortie complète du fichier
cat /var/log/mysql/slow-query.log
# naviguer de haut en bas
less /var/log/mysql/slow-query.log


Explanation of the phenomenon

As explained at the beginning of this form, the slow queries are dedfinie by the defined query execution time ; If the query takes longer time limit set by long_query_time to be executed, it is defined as slow querie. The first reason of obtaining long-running queries can be bad the MySQL variable setting "long_query_time" ; a too low maximum time can put some heavy queries directly in slow while they are actually quite normal.

Another reason may be related to using MySQL by an external service ; in the case of a website, for example, bad programming incorporating not auto close connections to MySQL can fill the buffer, making the longer running queries.

A bad configuration of the database can also be the cause of the phenomenon : joins of tables and the markings (key) can generate these errors - for example in the case of a table without a primary key.

Enfin, il ne faut pas exclure l'hypothèse d'une attaque sur votre Server, générant de nombreuses requêtes qui finiront par s'entasser et donc dépasser la limite de temps d'exécution définie par long_query_time.

Conclusion

The logs are now active and should allow you to better analyze slow actions on your database, allowing you to diagnose problems of performance, programming or targeted attacks. In addition, you now have a better visual on actions performed on your Server database.



This article seem useful to you ?

0



You are not allowed to post comments.