The Basics
As per the MySQL documentation:
"The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined."
To log all the queries, you can set the value of long_query_time to 0. The default value of long_query_time is 10 seconds and min_examined_row_limit is 0.
Enable Slow Query log in MySQL
1. Modify the “mysqld” section of /etc/my.cnf. The following example shows to log queries which takes more than five seconds to “/var/lib/mysql/mysqld-slow.log”:
# vi /etc/my.cnf [mysqld] long_query_time=5 slow_query_log=1 slow_query_log_file=/var/lib/mysql/mysqld-slow.log
2. Restart mysqld service once you are done with the above changes.
# service mysqld restart
Verify
1. Check if the configuration works. Check for the “slow_query_log” parameter (it should be “ON”).
mysql> show variables like '%slow%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/mysqld-slow.log |
+---------------------+--------------------------------+
4 rows in set (0.00 sec)
2. Check for the parameter “long_query_time”, it should have the time as 5 seconds.
mysql> show variables like '%long%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| long_query_time | 5.000000 |
| max_long_data_size | 1048576 |
+--------------------+----------+
2 rows in set (0.00 sec)
3. Verify that the queries are logged (You have to execute few long running queries to see them in slow query log):
mysql> SELECT SLEEP(6); +----------+ | SLEEP(6) | +----------+ | 0 | +----------+ 1 row in set (6.00 sec)
shell> sudo less /var/log/mysql/mysql-slow.log /usr/sbin/mysqld, Version: 8.0.3-rc-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument # Time: 2017-10-15T12:43:55.038601Z # User@Host: root[root] @ localhost [] Id: 7 # Query_time: 2.000845 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1508071435; SELECT SLEEP(6);
Enabling Slow query log Online
You can also enable the slow query log in MySQL instead of restart the mysqld service. To enable slow query log, you can dynamically set slow_query_log=1 and you can set the filename using slow_query_log_file. To specify the log destination, use –log-output:
1. Set the LONG_QUERY_TIME and verify the value.
mysql> SET @@GLOBAL.LONG_QUERY_TIME=5; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@GLOBAL.LONG_QUERY_TIME; +--------------------------+ | @@GLOBAL.LONG_QUERY_TIME | +--------------------------+ | 5.000000 | +--------------------------+ 1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.LONG_QUERY_TIME; +--------------------------+ | @@GLOBAL.LONG_QUERY_TIME | +--------------------------+ | 5.000000 | +--------------------------+ 1 row in set (0.00 sec)
2. Set the slow query file. By default, it would be in the data directory with the hostname-slow log:
mysql> SELECT @@GLOBAL.slow_query_log_file; +---------------------------------+ | @@GLOBAL.slow_query_log_file | +---------------------------------+ | /var/lib/mysql/server1-slow.log | +---------------------------------+ 1 row in set (0.00 sec)
mysql> SET @@GLOBAL.slow_query_log_file='/var/log/mysql/mysql-slow.log'; Query OK, 0 rows affected (0.00 sec)
Verify the new location of the log file:
mysql> SELECT @@GLOBAL.slow_query_log_file; +-------------------------------+ | @@GLOBAL.slow_query_log_file | +-------------------------------+ | /var/log/mysql/mysql-slow.log | +-------------------------------+ 1 row in set (0.00 sec)
3. Enable the slow query log. By default it is disabled as shown below:
mysql> SELECT @@GLOBAL.slow_query_log; +-------------------------+ | @@GLOBAL.slow_query_log | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec)
mysql> SET @@GLOBAL.slow_query_log=1; Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @@GLOBAL.slow_query_log; +-------------------------+ | @@GLOBAL.slow_query_log | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec)
MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
What’s a good process to find and eliminate slow queries in MySQL