AWS RDSDatabase

How to Enable Slow Query Logs in AWS RDS MySQL

In this blog, we discuss how to enable Slow Query Logs in AWS RDS Mysql. Before starting with steps, let’s understand what is slow query logs and why we need it and what is the use of it.

The slow query log is needed when we want to troubleshoot or identify which all queries taking more time to execute on a database and because of that CPU load spikes on DB instance.

You will not be having access to OS level as it is AWS RDS Cloud Service which called as DaaS (Database as a service). For that AWS RDS provide DB Parameter configuration where you can enable/disable and monitor slow query logs directly through the Amazon RDS console, the Amazon RDS API, the Amazon RDS CLI, or the AWS SDKs.

You can generate the slow query logs by setting parameters in a DB parameter group that you create for your MySQL instance(s).

AWS provides default binary logging format as mixed. You can change it to row-based as a logging format. Amazon RDS supports both the row-based and mixed binary logging formats for MySQL version 5.6.

NOTE: You cannot modify parameter settings for the default DB parameter group, this default group is created by AWS.  You must create your own DB parameter group to change parameter settings from their default value.

Steps to Create a New DB parameter group for your MySQL instanceHow to enable Slow Query Logs in AWS RDS Mysql

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
  2. In the navigation pane, choose Parameter Groups.
  3. Choose the Create Parameter Group. The Create Parameter Group window appears.
  4. Choose the parameter group family.
  5. For Group Name, type the name of the new DB parameter group.
  6. For Description, type a description for the new DB parameter group.
  7. Choose to Create.

Steps to modify DB parameter group

  1. Goto DB Parameter Group
  2. Select the parameter group you want to modify.
  3. Choose edit parameters
  4. Search for Slow_Query_log and enter value 1 — Slow_query_log =1 (default is 0 or no logging)
  5. Long_query_time = 2, which will log queries that take longer than 2 seconds to run.
  6. Choose Save Changes.

Associate your RDS DB instance with the new or modified DB parameter group

  1. In the navigation pane, choose Instances.
  2. Select the DB instance you want to associate with a DB parameter group.
  3. On the Instance Actions menu, choose Modify.
  4. In the Modify DB Instance dialog box, under Database Options, choose the parameter group you want to associate with the DB instance.
  5. Apply changes by rebooting the instance.

Note: Changing this setting does not result in an outage. The parameter group name changes immediately, but the actual parameter changes are not applied until you reboot the instance without failover.

How to view RDS MySQL Logs which is written to FILE

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
  2. In the navigation pane, choose Instances.
  3. Select the details icon next to the DB instance name that has the log file you want to view.
  4. On the DB instance details page, open the Recent Events & Logs view.
  5. For the log, you want to view, choose View.
  6. To return to the list of DB instances, choose Close, and then choose DB Instances at the top of the page.

Check if MySQL logs are written to TABLE by querying the log tables directly:

Note: Log tables keep growing until the respective logging activities are turned off. Enabling table logging increases the amount of data written to the database, which can degrade performance.

 

Read this also:

HOW TO ENABLE GENERAL LOGS AND ERROR LOGS IN AWS RDS
Show More

Related Articles

One Comment

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Close