Slow Query tracing on Amazon RDS MySQL

James Wrubel

Recently I needed to set up slow query logging for an Amazon RDS MySQL instance. It turned out to be harder than I expected and the docs were a bit light on detail and in some cases inaccurate or outdated, so I thought I would write up what we went through in the hope that others find this and avoid their own wacky inflatable-arm-guy moment.

The AWS console makes fools of us all

The first step is to enable slow query logs for your instance. Navigate to the RDS Home and select your instance. Find the Details section and click Modify. Towards the bottom is a section marked Log Exports. Check the Slow query log, but be careful! This option will require a DB restart, so you want to schedule it for a time of low usage.

You would think that would be it, but it’s not. To get logs to export, you need to set a number of database parameters, too. Make a note of the DB Parameter Group that is associated with this instance. Now open the RDS sidebar and select Parameter Groups. Select the associated parameter group.

Here you’re looking for several parameters. The UI on this screen is also confusing, so start by selecting Edit parameters. This will allow you to stage several changes and apply them all at once. These are the relevant parameters:

  • `slow_query_log` — set this to 1 (true). Seems redundant with the export_logs flag but 🤷🏼‍♂️
  • `long_query_time` — this defines the threshold beyond which a query is considered slow. Values are in seconds (the docs have more detail), so 2 means log anything that runs more than 2 seconds. Since the default is 0, nothing will be logged unless you change this.
  • `log_queries_not_using_indexes` — this is optional but might be helpful in tracking down queries that could be optimized by adding db indices.
  • `log_output` — set this to FILE if you want to be able to export or review logs through the UI. The default is TABLE meaning logs go to your database instance. In either event, according to the docs this will store only about 24 hours worth of logs at a time.

In theory, after all of these changes you should start getting slow query logs. This same general process works for General and Audit logs. Hopefully this saves some of you a headache!

James Wrubel

Written by

Dad, husband, proud @umich grad. Former CTO @thinkthrumath, now hacking on @_beergoggles and other things. I 🏊 🚴🏻 🏃🏼 in whatever time’s left.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade