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.
