Turning On Mysql Slow Query Log

Slow query log is a mechanism in mysql which will track and record queries that take long time to complete, for database administrator to analyze and optmize to increase mysql performance.

To check if this feature is already turned on, we can run below command in mysql console:

mysql> show variables like 'slow_query_log';

We will see something like below if it is already turned on: 

The query will be deemed slow, of the query takes longer than 'long_query_time' to complete. We can also check this using below command:

mysql> show variables like 'slow_query_log';

In this case, our long_query_time is set to 10 seconds:

We can check the location of the slow_query_log_file by using below command:

mysql> show variables like 'slow_query_log_file';

In this example, the log file is located in /bitnami/mysql/data/5653c641c26b-slow.log

The above variables, can be set in the runtime using below commands:

To turn off slow_query_log

mysql> set global slow_query_log = off;

To set long_query_time to 5 seconds

mysql> set long_query_time = 5;

All the changes that we made in mysql console, is only for the current runtime, and would not survive mysql service restart. To make the changes permanent, we need to modify /etc/my.cnf, under [mysqld], like below

[mysqld]

slow_query_log=1

long_query_time=5

slow_query_log_file=/var/log/mysql/slow.log

And then restart mysql

$ sudo systemctl restart mysql