Set the following variables in your MySQL config file:
slow_query_log = 1 (means true) long_query_time = 0 (means 0 seconds) log_queries_not_using_indexes = 1 (means true) log_output = FILE (default is TABLE)
long_query_time to 0 will instruct MySQL to log all queries to the slow query log. Some queries might not be that slow, but if repeated often enough will have a significant impact on the performance of your MySQL database. Setting
long_query_time to 0 will allow you to catch that kind of scenario as well. Let MySQL run for a while, then download pt-query-digest, and run it like so:
$ pt-query-digest slow-query.log > slow-query.log.out
Look at the
slow-query.log.out file to find out which queries are slowing down your MySQL database.
I work as a freelancer, so if you don’t want to do that kind of things yourself or don’t have the time, just drop me a line to hire me.