MySQL can log the queries which are taking longer than X seconds .
This feauture is disabled by default.
If you want to enable this option, proceed with the following steps:-
1) Login to the server via ssh.
2) Open the file /etc/my.cnf and put the following entries:-
[root@localhost ~]#vi /etc/my.cnf
Locate the [mysqld] section and add the following entries:-
===================
log_slow_queries=/var/log/mysql_slow_queries.log
long_query_time = 2
===================
Here I will be logging the queries to /var/log/mysql-slow.log which are taking longer then 2 seconds.
3)Create the the log file.
[root@localhost ~]# touch /var/log/mysql_slow_queries.log
4) Change the ownership of the file /var/log/mysql_slow_queries.log
[root@localhost ~]#chown mysql.root /var/log/mysql_slow_queries.log
5) Restart mysql
[root@localhost ~]#service mysql restart
6) Check the slow queries using the following command:-
[root@localhost ~]#tail -f /var/log/mysql_slow_queries.log
Identify the slow query. Then optimise it or remove it.
After finding the slow queries, comment the lines log_slow_queries=/var/log/mysql_slow_queries.log in /etc/my.cnf and restart the MySQL service. The MySQL slow queries may slow the server a bit.
