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:-
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.

Contact Us On WhatsApp