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. (more…)