How to change the slow query log threshold on RDS

What is the slow query log

Before we discuss how to change the slow query log threshold on RDS, let’s quickly establish what the slow query log is.

The slow query log will record all queries which are above the threshold level. The default value is 10 (seconds) but you can set it higher or lower depending on your requirements. It is useful for finding slow queries and allows you to pick out candidates for tuning.

If you set the threshold too low, it can increase I/O overhead on your instance and use a lot of valuable disk space. If you set it too high, it might not capture enough useful information.

AWS Web console

If you are doing this change as a one off, it might be simpler to just use the AWS web console.

1. Log into AWS
2. Select RDS
3. Find out which parameter group your instance is in
   1. Select instance then click configuration.
   2. Scroll down to see the parameter group
   3. It will be a hyperlink so just click on it
4. In the parameter group page, use the search box and search for slow_query_log
   1. Ensure it is set to 1
   2. If not, click edit parameters and change the value to 1.
5. Change search box to long_query_time
   1. Set it to any value between 1 and 31536000
   2. Save changes

AWS CLI

aws rds modify-db-parameter-group --db-parameter-group-name "primary-mysql-5point7" --parameters "ParameterName='long_query_time',ParameterValue=1,ApplyMethod=immediate" --profile dev
# If you don't have multiple profiles set, leave out --profile dev.

AWS PowerShell

$HashArguments = @{
    DBParameterGroupName = "mysql5point7-monster-param-grp-ci"
    Parameter=@{ParameterName="long_query_time";ParameterValue="10";ApplyMethod="Immediate"}
    Profilename = "Dev"
    }
Edit-RDSDBParameterGroup @HashArguments

No restart needed

Changing either slow_query_log or long_query_time parameters can both be safely done without the need to restart your instance. This is because RDS defines them as Dynamic parameters. Take note when changing parameters that are defined as Static as they will require a restart. This either means short but immediate downtime or waiting until your next maintenance window.

Further reading

I used some of these links to help me gather the information I needed to write this blog post.

1. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Modifying
2. https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html
3. https://docs.aws.amazon.com/powershell/latest/reference/items/Edit-RDSDBParameterGroup.html

Featured image by icsilviu from Pixabay