Enable slow queries MySQL logs

This article has been created by an automatic translation software. You can view the article source here.

Introduction | _. _. May receive alerts from monitoring about slow mysql queries | _. _. Verify that the logs are not already active | _. _. To ene that the logs of slow queries are not already turned on, connect in SSH to your machine and enter the MySQL shell | _. _. Once in the MySQL shell, enter the following command to get the list of all the variables and their values set | _. _. Navigate to see the variable in the left column | _. _. | _. _. on: _! _ | We ', the logs are active. On: _! _ | OFF', we have to turn them on. | _. _. These errors are produced when running a MySQL query exceeds the allotted time defined by the variable MySQL | _. _. 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. | _. _. . These errors can be produced by different factors, we see here how to activate the logs for these errors in order to analyze and correct them.

Place: _! _ | Please note that activate the logs of slow queries can slow your | _. _. given that entries will be made at the same time as running the slow queries. (slow queries) : Enable and configure the slow queries log | _. _. Always in the MySQL shell, enter the following command: _! _ | You can also set a path to the directory where the slow query logs will be stored | _. _. Finally, set a minimum time allocated to each request to make sure you have no false positives of slow queries | _. _. seconds is a correct value | _. _. seconds is recommended if it happens that your | _. _. weaken in the face of the number of queries and visitors | _. _. This variable is defined basic to | _. _. seconds | _. _. Finally, verify that changes were taken into account by the following command: _! _ | 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 | _. _. 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 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. long_query_timeA bad configuration of the database can also be the cause of the phenomenon | _. _. joins of tables and the markings | _. _. | _. _. can generate these errors: _! _ | for example in the case of a table without a primary key.

Finally, we should not exclude the hypothesis of an attack on your | _. _. generating many queries that will eventually pile up and so exceed the limit defined by long_query_time execution time.

: _! _ | 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 | _. _. database. ServerThis article seem useful to you

==

mysql -u utilisateur -p
[mot de passe]

:

show variables;

"slow_query_log" : sur ' '

==

:

set global slow_query_log = 'ON';

:

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

: 10  20  Server  ( 10 ) :

set global long_query_time = '20';

:

show variables like '%slow%';

==

:

# 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

==

;  "long_query_time" ; 


; 


:  (key)  - 

Server

Conclusion

Server 

?

0

Vote

Sort by date Sort by score

Enable comment auto-refresher

You are not allowed to post comments.