Enabling slow query log on Amazon RDS

On February 16, 2014, in AWS, by Anuj Gakhar

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

  1. 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.
  2. 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.

edit_parameters_rds_console

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.

slow_query_log_rds_console

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.

phpmyadmin_slow_log

Happy query optimising 🙂

Tagged with:  

5 Responses to Enabling slow query log on Amazon RDS

  1. Sankalp says:

    afaik restarting the rds instance is not necessary since slow query logging is dynamically enable-able while mysql is running.

  2. Amol says:

    How can I set logs to store only in file?

  3. Alex Dijkstra says:

    ” It’s not advised to keep it enabled for long, especially on production servers”
    why do think so?
    it seems like a good thing to do to me. perhaps 10seconds for slow_log time is too short, better have it like 20-50 seconds, so you’re sure only real problem-queries get logged.

    • Anuj Gakhar says:

      Hi Alex, the only reason I said that was because it makes the server slow. A few seconds or a minute – depends on what’s enough for you to log slow queries. On a site with huge traffic, few seconds would be enough, on others, a few minutes perhaps.

Leave a Reply to Alex Dijkstra Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: