Enabling the slow query log

MySQL reports how much time it took to execute a query.

Step 1: Enable slow log query, set a slow log query log file, and set the log_query_time in my.cnf.
Edit the MySQL configuration file.

vi /etc/my.cnf

 

In the [mysqld] section add/edit the following variables

long_query_time = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysqld.slow.query.log

We are setting the log_query_time variable to 0. Any MySQL query taking more than 0 seconds will be logged.

 

Step 2: Create the file /var/log/mysqld.slow.query.log and set the system user mysql as the owner.

touch /var/log/mysqld.slow.query.log
chown mysql.mysql /var/log/mysqld.slow.query.log

 

Step 3: Restart MySQL server

/etc/init.d/mysqld restart

 

Step 4: Watch the slow query log using the tail command.

tail -f /var/log/mysqld.slow.query.log

 

A sample entry on my server looks like this:

[root@localhost ~]# tail -f /var/log/mysqld.slow.query.log 
SET timestamp=1255345490;
SELECT `privilege`.* FROM `privilege` WHERE ( name = 'view service invoices"') LIMIT 1;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000273  Lock_time: 0.000104 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
SELECT * FROM organization_details;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000048  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
# administrator command: Quit;

If you observe the log entry, the last query took 0.000048 seconds.