Enabling Slow Query Log in AWS RDS
By default The MySQL slow query logs are disabled in AWS RDS, To enable the same you have to made changes in your DB parameter group. Following are the steps to enable the Slow query logs.
Prerequsites: AWS CLI Tools (You can install it using this link.)
First we will create a parameter group, (this is not necessary to create you can use your default parameter group)
[shell] aws rds create-db-parameter-group –db-parameter-group-name test2 –db-parameter-group-family mysql5.1 –description "testGroup" [/shell]
This command will give you output something like following.
[shell]
{
"DBParameterGroup": {
"DBParameterGroupName": "test2",
"DBParameterGroupFamily": "mysql5.6",
"Description": "testGroup"
}
}
[/shell]
Now we will make our instance to the part of parameter group.
[shell] aws rds modify-db-instance –db-instance-identifier testdbinstance –db-parameter-group-name test2 [/shell]
Now we will modify our parameter group to enable slow query log –
Note : By Default AWS RDS will save slow query log in to the table, so for writing the log in to a file we are changing “log_output” parameter to FILE.
[shell] aws rds modify-db-parameter-group –db-parameter-group-name test2 –parameters "ParameterName=min_examined_row_limit,ParameterValue=100,ApplyMethod=immediate", "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate", "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate", "ParameterName=log_output,ParameterValue=File,ApplyMethod=immediate" [/shell]
The above command will enable slow query log, and start keeping all the queries which are taking more than 1 second to execute, and start saving the same in to a FIle.
If you want to check your db parameter group you can use the following command.
[shell]aws rds describe-db-parameters –db-parameter-group-name test2 [/shell]
That’s it, Now you just need to reboot your db instance and your new db parameter group comes in to effect.
[shell] aws rds describe-db-parameters –db-parameter-group-name test2 [/shell]
To check your slow query log you can use the following command.
[shell] aws rds download-db-log-file-portion –db-instance-identifier testdbinstance –log-file-name slowquery/mysql-slowquery.log –output text [/shell]
