How to get a MySQL slow query log from AWS RDS

September 30, 2016

It is very important to know which query is slow and cause the slow response. MySQL can output the slow query logs so we can optimize the queries. I’m using the AWS RDS for my database, so I explain how to set up to RDS.

Activate MySQL slow query log

At first, go to AWS RDS dashboard, and go to “Parameter Groups”.

  • slow_query_log

You can set the “slow_query_log” to “1” and save it.

  • long_query_time

Set “long_query_time” as you want. The queries slower than this value will be recorded to the slow query log. Default value is 10 seconds, but I usually set as 1 second.

After that check your DB instance configuration details and the change in the parameter group take effect.

Retrieve the slow query log

AWS RDS keeps the slow query log to the TABLE. I recommend to fetch the logs by using AWS CLI.

Change “YOUR_DB_IDENTIFIER” to your actual db identifier. and run this.

$for i in {0..23}
    do aws rds download-db-log-file-portion \
        --db-instance-identifier YOUR_DB_IDENTIFIER \
        --log-file-name slowquery/mysql-slowquery.log.$i  \
        --output text \
        > slowquery.log
    done

Here I use the AWS CLI, refer to the official AWS document to know more. If you have AWS account more than one, don’t forget to add --profile YOUR_PROFILE_NAME to define which profile you use. Otherwise it returns an error saying DBNotFound.

Summarize the slow queries

It is possible to summarize the slow query log by mysqldumpslow command.

$ mysqldumpslow  -s t slowquery.log > report.log

You can see the summarized slow query logs like this.

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts

Make them one file

It’s more useful to make them one file. You can just run the command and get the summarize file.

slow_query.sh

#!/bin/sh

for i in {0..23}
    do aws rds download-db-log-file-portion \
        --db-instance-identifier YOUR_DB_IDENTIFIER \
        --log-file-name slowquery/mysql-slowquery.log.$i  \
        --output text \
        > slowquery.log
    done

mysqldumpslow  -s t slowquery.log > report.log

$ sh slow_query.sh

Finally we can check the AWS RDS slow query log easily so that we can know the query that have a problem :)

@takp