The Geek Diary

Configuring mysqld to log slow queries

by

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 : How To Find the Slowest Queries
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

You May Also Like