Enable slow query log in Mysql

Enable slow query log in Mysql

Check whether the “slow query log” is enabled  in mysql. Access mysql and try to execute the following command.

mysql> show global variables like ‘%slow%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | OFF   |
| slow_launch_time | 2     |
+——————+——-+

The command result shows slow query log is currently disabled in the server.  You have add the following entries in the “/etc/my.cnf”  file in-order to enable “slow query log”.  Place the entries below the  section “mysqld”

[mysqld]
long_query_time=2
log-slow-queries=/var/log/mysql/log-slow-queries.log

You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

#Restart mysql

Check again  whether the “slow query log” is enabled.

mysql> show global variables like ‘%slow%’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+——————+——-+
2 rows in set (0.00 sec)

This steps will log all Mysql queries that took more than 2 seconds to complete the execution.   
===========================================================>

Advertisements

3 thoughts on “Enable slow query log in Mysql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s