MySQL - enable slow query log
- Slow queries can affect database and server performance.
- The slow query log consists of SQL statements that took more than
long_query_time
seconds to execute. - This greatly simplifies the task of finding inefficient or time-consuming queries.
- By default, the slow query log is disabled
- 1.Login to MySQL, database
abc_prod
shell> mysql -u root -p abc_prod - 2.Enable slow query logmysql> SET GLOBAL slow_query_log = 'ON';
- 3.Check path to log filemysql> SHOW VARIABLES LIKE 'slow_query_log_file';+---------------------+-----------------------------+| Variable_name | Value |+---------------------+-----------------------------+| slow_query_log_file | /var/lib/mysql/db3-slow.log |+---------------------+-----------------------------+1 row in set (0.00 sec)
- 4.Change long query time to 5 seconds - default is 10 secondsmysql> SET GLOBAL long_query_time = 5;
- 5.Logout MySQL session then login again
- 6.Take a small test to ensure slow query log is enablemysql> SELECT SLEEP(10);
- 7.Check sleep on slow query log[email protected]:/home/ubuntu:~$ cat /var/lib/mysql/db3-slow.log/usr/sbin/mysqld, Version: 5.6.35-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sockTime Id Command Argument# Time: 170321 7:15:52# [email protected]: root[root] @ localhost [] Id: 1160# Query_time: 7.000249 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1490080552;SELECT SLEEP(7);# Time: 170321 7:17:31# [email protected]: root[root] @ localhost [] Id: 1161# Query_time: 10.000215 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0use abc_prod;SET timestamp=1490080651;SELECT SLEEP(10);
Most important:
Using
mysqldumpslow
tool for summarize slow query log files[email protected]:/home/ubuntu:~$ mysqldumpslow /var/lib/mysql/db3-slow.log
Reading mysql slow query log from /var/lib/mysql/db3-slow.log
Count: 2 Time=8.50s (17s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
SELECT SLEEP(N)
mysql> SET GLOBAL slow_query_log = 'OFF';
Last modified 3yr ago