How do I monitor the performance of my Amazon RDS for MySQL DB instance?

Last updated: 2021-07-16

I want to monitor the performance of my Amazon Relational Database Service (Amazon RDS) for MySQL DB instance. What's the best way to do this?

Short description

There are several ways that you can monitor your Amazon RDS for MySQL DB instance:

To troubleshoot any issues or multi-point failures, it's a best practice to monitor your DB instance using a variety of these monitoring methods.

Resolution

Amazon CloudWatch

Amazon CloudWatch provides real-time metrics of your Amazon RDS for MySQL database instance. By default, Amazon RDS metrics are automatically sent to Amazon CloudWatch every 60 seconds. You can also create a usage alarm to watch a single Amazon RDS metric over a specific time period.

To monitor Amazon RDS metrics with Amazon CloudWatch, perform the following steps:

Note: Metrics are first grouped by the service namespace, and then by the various dimension combinations within each namespace.

1.    Open the Amazon CloudWatch console.

2.    (Optional) Update your AWS Region. From the navigation bar, choose the AWS Region where your AWS resources exist. For more information, see Regions and endpoints.

3.    In the navigation pane, choose Metrics.

4.    Choose the RDS metric namespace.

5.    Select a metric dimension.

6.    (Optional) Sort, filter, update the display of your metrics:
To sort your metrics, use the column heading.
To create graph view of your metric, select the check box next to the metric.
To filter by resource, choose the resource ID, and then choose Add to search.
To filter by metric, choose the metric name, and then choose Add to search.

Enhanced Monitoring (within 1-5 seconds of granularity interval)

When you use Enhanced Monitoring in Amazon RDS, you can view real-time metrics of an operating system that your DB instance runs on.

Note: You must create an AWS Identity Access Management (IAM) role that allows Amazon RDS to communicate with Amazon CloudWatch Logs.

To enable Enhanced Monitoring in Amazon RDS, perform the following steps:

1.    Scroll to the Monitoring section.

2.    Choose Enable enhanced monitoring for your DB instance or read replica.

3.    For Monitoring Role, specify the IAM role that you created.

4.    Choose Default to have Amazon RDS create the rds-monitoring-role role for you.

5.    Set the Granularity property to the interval, in seconds, between points when metrics are collected for your DB instance or read replica. The Granularity property can be set to one of the following values: 1, 5, 10, 15, 30, or 60.

RDS Performance Insights

Note: If Performance Insights is manually enabled after creating the DB instance, a reboot instance is required to enable Performance Schema. Performance Schema is disabled when the parameter is set to "0" or "1" or the Source column for the parameter is set to "user". When the performance_schema parameter is disabled, Performance Insights displays a DB load that is categorized by the list state of the Amazon RDS MySQL process. To enable the performance_schema parameter, use reset performance_schema parameter.

When you use RDS Performance Insights, you can visualize the database load and filter the load by waits, SQL statements, hosts, or users. This way, you can identify which queries are causing issues and view the wait type and wait events associated to that query.

You can enable Performance Insights for Amazon RDS for MySQL in the Amazon RDS console.

Slow query logging

You can enable your slow query log by setting the slow_query_log value to "1". (The default value is "0", which means that your slow query log is disabled.) A slow query log records any queries that run longer than the number of seconds specified for the long_query_time metric. (The default value for the long_query_time metric is "10".) For example, to log queries that run longer than two seconds, you can update the number of seconds for the long_query_time metric to a value such as "2".

To enable slow query logs for Amazon RDS for MySQL using a custom parameter group, perform the following:

1.    Open the Amazon RDS console.

2.    From the navigation pane, choose Parameter groups.

3.    Choose the parameter group that you want to modify.

4.    Choose Parameter group actions.

5.    Choose Edit.

6.    Choose Edit parameters.

7.    Update the following parameters:
log_output: If the general log or slow query log is enabled, update the value to "file" for write logs to the file system. Log files are rotated hourly.
long_query_time: Update the value to "2" or greater, to log queries that run longer than two seconds or more.
slow_query_log: Update the value to "1" to enable logging. (The default value is "0", which means that logging is disabled.)

8.    Choose Save Changes.

Note: You can modify the parameter values in a custom DB group, but not a default DB parameter group. If you're unable to modify the parameter in a custom DB parameter group, check whether the Value type is set to "Modifiable". For information on how to publish MySQL logs to an Amazon CloudWatch log group, see Publishing MySQL logs to Amazon CloudWatch Logs.