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

Short description

You can use the following methods to monitor your Amazon RDS for MySQL DB instance:

• Amazon CloudWatch
• Enhanced Monitoring
• RDS Performance Insights
• Slow query logs

To troubleshoot issues or multi-point failures, use one or more of the following monitoring methods for your DB instance.

Resolution

Amazon CloudWatch

Amazon CloudWatch provides metrics of your Amazon RDS for MySQL DB instance. By default, Amazon RDS sends metrics to Amazon CloudWatch every 60 seconds. You can also create a CloudWatch alarm to watch a single Amazon RDS metric over a specific time period.

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

Note: CloudWatch groups metrics by the service namespace, and then by the dimension combinations within each namespace.

1. Open the CloudWatch console.
2. (Optional) To change your AWS Region, choose the Region dropdown list in the navigation bar, and then select your Region. For more information about endpoints, see AWS service endpoints.
3. In the navigation pane, choose Metrics.
4. Choose the RDS metric namespace.
5. Select a metric dimension.
6. (Optional) Sort, filter, or update the display of your metrics:
   To sort your metrics, use the column heading.
   To create a 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

With Enhanced Monitoring in Amazon RDS, you can view operating system metrics for your DB instance.

Note: You must create an IAM role that allows Amazon RDS to communicate with Amazon CloudWatch Logs.

To turn on Enhanced Monitoring, see Setting up and enabling Enhanced Monitoring.

RDS Performance Insights

Note: If you turn on Performance Insights after you create the DB instance, then you must reboot the instance to activate Performance Schema. Performance Schema is off when the parameter value is 0 or 1, or when the Source column is user. When the performance_schema parameter is off, Performance Insights displays a DB load categorized by the list state of the Amazon RDS MySQL process. To activate the performance_schema parameter, see Overview of the Performance Schema for Performance Insights on Amazon RDS for MariaDB or MySQL.

With RDS Performance Insights, you can visualize the database load and filter the load by waits, SQL statements, hosts, or users. You can identify which queries cause issues and view the wait type and wait events for that query.

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

Slow query logs

To activate your slow query log, set the slow_query_log value to 1. The default value is 0, which means that your slow query log is off. A slow query log records any queries that run longer than the number of seconds specified for the long_query_time parameter. The default value for the long_query_time parameter is 10. For example, to log queries that run longer than two seconds, update the long_query_time parameter to 2.

To activate slow query logs for Amazon RDS for MySQL with a custom parameter group, complete the following steps:

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:
   For log_output, if the general log or slow query log is on, update the value to file to write logs to the file system. Amazon RDS rotates log files hourly.
   For long_query_time, update the value to 2 or greater to log queries that run longer than two seconds.
   For slow_query_log, update the value to 1 to activate logging. The default value is 0, which means that logging is off.
8. Choose Save Changes.

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

Related information

MySQL database log files

Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance

How do I troubleshoot and resolve high CPU utilization on my Amazon RDS for MySQL or Aurora MySQL-Compatible DB instance?

How do I activate and monitor logs for an Amazon RDS for MySQL DB instance?