Identify slow MySQL query

Sometimes, MySQL will make your server pain if you have user abusing it. Mick Genie will teach you to identify which MySQL database creating the problem to your server.

Actually MySQL can log all of the those query that taking longer time than certain seconds.

1. Login to the server as root.
2. Open your my.cnf with your editor.

vi /etc/my.cnf

3. In the [mysqld] section, add the line as below.

log-slow-queries = /var/log/mysql-slow.log
long_query_time = 3

this meant that the log will logged to /var/log/mysql-slow.log and the query time is 3 seconds.

4. Save the my.cnf by typing “:wq!” and press enter.
5. Now, change the ownership of the mysql-slow.log file to the mysql owner to allow it to write the log.

chown mysql.root /var/log/mysql-slow.log

6. Restart the MySQL services.

service mysql restart

7. Tail the log to check which database make your server pain.

tail -f /var/log/mysql-slow.log

8. Once identified the problem, modify the /etc/my.cnf again become line as below.

#log-slow-queries = /var/log/mysql-slow.log
#long_query_time = 3

9. Restart the MySQL services again and done.

Published by

Mick Genie

Mick Genie is the founder of and working at ExaBytes Network Sdn Bhd and WPWebHost web host company. He is expertise in Windows and Linux environment especially web hosting related information, tips and trick as well as the IT Information.

One thought on “Identify slow MySQL query”

Comments are closed.