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 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_logsflag 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
2means 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
FILEif you want to be able to export or review logs through the UI. The default is
TABLEmeaning 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!