John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

How to change the slow query log threshold on RDS

19th August 2021 By John McCormack 1 Comment

turtles on a log

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.

rds parameters slow_query_log
rds parameters long_query_time

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

Filed Under: front-page, MySQL

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023