The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. If you want to enable slow query log on your Amazon MySQL RDS instance, you need to be aware of 2 things :-
- To be able to enable and disable slow query log on the RDS instance. Make sure you disable it after a few mins of enabling it. It’s not advised to keep it enabled for long, especially on production servers.
- To be able to view the slow queries once you have enabled the log. As of MySQL 5.1.6, the destination of the slow query log can be a file or a table or both.
You are going to need to edit the parameter “slow_query_log” under the DB parameter group of your RDS instance. A RDS instance would usually be setup with a default DB parameter group e.g. if you are running Mysql 5.5.x. your DB parameter group would be called default.mysql5.5 and so on. I believe AWS won’t allow you to modify a default DB parameter group, so it’s best to create one of your own based on one of the default ones and use that for your DB instance. Once your RDS instance is running off your custom parameter group, you can then go ahead and start modifying parameters in it.
Since not too long ago, this could only be done via the command line. But now, this can be done via the AWS Management console as well. You need to be in the DB Parameter Groups section in the AWS RDS Console. Find your Parameter group and click on it. Then click on the ‘Edit Parameters’ button.
Once in the edit mode, find the “slow_query_log” parameter. You might have to scroll down towards the bottom. Change this value to 1 if you are enabling it and 0 if you are disabling it. No other values would be accepted here.
Don’t forget to reboot your RDS instance after this change and you are done.
Once you’ve enabled the slow query log, MySQL will start logging the slow queries to a file or a table or both based on your configuration. There is actually a table called “slow_log” in the “mysql” database. This is where the slow queries get written to if the log is enabled. The default value for long_query_time variable is 10 seconds, so any query taking over this threshold will get logged to the mysql.slow_log table.
At this point, you can browse the mysql.slow_log table in your database browser of choice, whether that’s phpMyAdmin or Sequel Pro or anything else.
Happy query optimising 🙂