Activities

July 2014
M T W T F S S
« Jun   Aug »
 123456
78910111213
14151617181920
21222324252627
28293031  

MySQL 5.5 : How do I enable slow query log

Prior to optimize the performance of a busy MySQL Database server, I need to enable all the queries those consume more than 10 second to complete the execution. This server load is high most of the time and being hit on performance issue due to this load.

I’m using the server which have 32 CPU’s and 64GB RAM loaded on it.

Enabling slow query log

1. Create MySQL log file
You may need to create a log file on a drive where you have plenty of storage space avail. This is because this log file may grow rapidly based on the slow qurries running on the server.

[root@567926-db1 ~]#touch /sqldata/mysqllogs/mysql-slow.log
  [root@567926-db1 ~]#chown mysql.mysql /sqldata/mysqllogs/mysql-slow.log

2. Edit the “/etc/my.cnf” file and add the following lines on it.

slow_launch_time=5
 slow_query_log=1
 slow_query_log_file=/sqldata/mysqllogs/mysql-slow.log
 log_queries_not_using_indexes = 0

3. Now restart the MySQL server

[root@rc-025 ~]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@rc-025 ~]#

How do I reset the query timeout value and flush the logs

In certain scenario, this log file is become huge and very hard to operate it for analysis. Also if you need instance query log, you may need to restart the mysql server after flushing the log file.
On MySQL 5.5, there is an option to enable and disable logging facility without restarting the service.

Here we go,

1. First we need to stop the query logging

a. Verify the current status

 mysql> show variables like "slow%";
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_launch_time    | 10                            |
| slow_query_log      | ON                            |
| slow_query_log_file |/sqldata/mysqllogs/mysql-slow.log |
+---------------------+-------------------------------+
3 rows in set (0.00 sec)
mysql>

b. Disable to logging

mysql> set global  slow_query_log = 0;
Query OK, 0 rows affected (0.03 sec)
mysql> set global log_queries_not_using_indexes = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "slow%";
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_launch_time    | 10                            |
| slow_query_log      | OFF                           |
| slow_query_log_file |/sqldata/mysqllogs/mysql-slow.log |
+---------------------+-------------------------------+
3 rows in set (0.00 sec)

c. Truncate the huge log file

[root@fc-db01 ~]# du -sh  /sqldata/mysqllogs/mysql-slow.log
1.1G    /sqldata/mysqllogs/mysql-slow.log
[root@fc-db01 ~]# ls -la /sqldata/mysqllogs/mysql-slow.log
-rwx------ 1 mysql mysql 1146282914 Jul 16 04:48 /sqldata/mysqllogs/mysql-slow.log
[root@fc-db01 ~]# cat /dev/null > /sqldata/mysqllogs/mysql-slow.log
You have new mail in /var/spool/mail/root
[root@fc-db01 ~]# ls -la /sqldata/mysqllogs/mysql-slow.log
-rwx------ 1 mysql mysql 0 Jul 16 04:50 /sqldata/mysqllogs/mysql-slow.log

d. Turn on the logging back

mysql> set global  slow_query_log = 1;
Query OK, 0 rows affected (0.03 sec)
mysql> show variables like "slow%";
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_launch_time    | 10                            |
| slow_query_log      | ON                            |
| slow_query_log_file |/sqldata/mysqllogs/mysql-slow.log |
+---------------------+-------------------------------+
3 rows in set (0.00 sec)
mysql>

Note : In my setting I’m getting not only the query using 10 seconds but also I’m getting some other queries in it. Not sure what is happening there 🙁

In addition the above reported issue, I found the log_queries_not_using_indexes was enabled by default. So I has to turn it of to get the exact slow queries.

mysql> show variables like "log%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log                             | OFF                            |
| log_bin                         | ON                             |
| log_bin_trust_function_creators | OFF                            |
| log_error                       | /sqldata/mysqllogs/mysql-slow.log |
| log_output                      | FILE                           |
| log_queries_not_using_indexes   | ON                             |
| log_slave_updates               | OFF                            |
| log_slow_queries                | OFF                            |
| log_warnings                    | 1                              |
+---------------------------------+--------------------------------+
9 rows in set (0.00 sec)
mysql> set global log_queries_not_using_indexes = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "log%";
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log                             | OFF                            |
| log_bin                         | ON                             |
| log_bin_trust_function_creators | OFF                            |
| log_error                       | /sqldata/mysqllogs/mysql-slow.log |
| log_output                      | FILE                           |
| log_queries_not_using_indexes   | OFF                            |
| log_slave_updates               | OFF                            |
| log_slow_queries                | OFF                            |
| log_warnings                    | 1                              |
+---------------------------------+--------------------------------+
9 rows in set (0.00 sec)
mysql>

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>