Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Using Amazon RDS Performance Insights

Amazon RDS Performance Insights monitors your Amazon RDS DB instance load so that you can analyze and troubleshoot your database performance. Amazon RDS Performance Insights is currently available for use with Amazon Aurora PostgreSQL and Amazon RDS PostgreSQL version 10.

Performance Insights expands on existing Amazon RDS monitoring features to illustrate your database's performance and help you analyze any issues that affect it. With the Performance Insights dashboard, you can visualize the database load and filter the load by waits, SQL statements, hosts, or users. Performance Insights is on by default in the console create wizard for the Aurora PostgreSQL and RDS PostgreSQL database engines. If you have more than one database on the DB instance, performance data for all of the databases is aggregated for the DB instance.

The central metric for Performance Insights is DB Load, which represents the average number of active sessions for the database engine. An active session is a connection that has submitted work to the database engine and is waiting for a response from it. For example, if you submit a SQL query to the database engine, the database session is active while the database engine is processing that query.

By combining DB Load with wait event data you can get a complete picture of the state for an active session. For a complete list of all PostgreSQL wait events, see PostgreSQL Wait Events. Wait events vary by database engine. For a list of the most commonly used wait events for Aurora PostgreSQL, see Amazon Aurora PostgreSQL Events.

Session information is collected, aggregated, and displayed in the dashboard as the Average Active Sessions chart. The Average Active Sessions chart displays the Max CPU value as a line, so you can see if active sessions are exceeding it or not. The Max CPU value is determined by the number of vCPU (virtual CPU) cores for your DB instance.

If you find that the load in the Average Active Sessions chart is often above the Max CPU line and the primary wait state is CPU, the system CPU is overloaded. In these cases, you might want to throttle connections to the instance, tune any SQL queries with a high CPU load, or consider a larger instance class. High and consistent instances of any wait state indicate that there might be bottlenecks or resource contention issues that you should resolve, even if the load does not cross the Max CPU line.

You can find an overview of Performance Insights in the following video.

Enabling Performance Insights

To use Performance Insights, you must enable it on your DB instance.

AWS Management Console

You can use the console to enable Performance Insights when you create a new DB instance. You can also modify a DB instance to enable Performance Insights.

Enabling Performance Insights with the Console When Creating a DB Instance

When you create a new DB instance, Performance Insights is enabled when you choose Enable Performance Insights in the Performance Insights section.

To create a DB instance, follow the instructions for your DB engine in Creating an Amazon RDS DB Instance. The following image shows the Performance Insights section.


							Enable Performance Insights during DB instance creation with console

You have the following options when you choose Enable Performance Insights:

  • Retention – The amount of time to retain Performance Insights data. Choose either 7 days (the default) or 2 years.

  • Master key – Specify your AWS Key Management Service (AWS KMS) key. Performance Insights encrypts all potentially sensitive data using your AWS KMS key. Data is encrypted in flight and at rest. For more information, see Encrypting Amazon RDS Resources.

Enabling Performance Insights with the Console When Modifying a DB Instance

You can modify a DB instance to enable Performance Insights using the console.

To enable Performance Insights for a DB instance using the console

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. Choose Instances.

  3. Choose the DB instance that you want to modify, and choose Modify in Instance actions.

  4. In the Performance Insights section, choose Enable Performance Insights.

    You have the following options when you choose Enable Performance Insights:

    • Retention – The amount of time to retain Performance Insights data. Choose either 7 days (the default) or 2 years.

    • Master key – Specify your AWS Key Management Service (AWS KMS) key. Performance Insights encrypts all potentially sensitive data using your AWS KMS key. Data is encrypted in flight and at rest. For more information, see Encrypting Amazon RDS Resources.

  5. Choose Continue.

  6. For Scheduling of Modifications, choose one of the following:

    • Apply during the next scheduled maintenance window – Wait to apply the Performance Insights modification until the next maintenance window.

    • Apply immediately – Apply the Performance Insights modification as soon as possible.

  7. Choose Modify instance.

CLI

When you create a new DB instance using the create-db-instance AWS CLI command, Performance Insights is enabled when you specify --enable-performance-insights.

You can also specify the --enable-performance-insights value using the following AWS CLI commands:

The following procedure describes how to enable Performance Insights for a DB instance using the AWS CLI.

To enable Performance Insights for a DB instance using the AWS CLI

  • Call the modify-db-instance AWS CLI command and supply the following values:

    • --db-instance-identifier – The name of the DB instance.

    • --enable-performance-insights

    The following example enables Performance Insights for sample-db-instance.

    For Linux, OS X, or Unix:

    aws rds modify-db-instance \ --db-instance-identifier sample-db-instance \ --enable-performance-insights

    For Windows:

    aws rds modify-db-instance ^ --db-instance-identifier sample-db-instance ^ --enable-performance-insights

When you enable Performance Insights, you can optionally specify the amount of time, in days, to retain Performance Insights data with the --performance-insights-retention-period option. Valid values are 7 (the default) or 731 (2 years).

The following example enables Performance Insights for sample-db-instance and specifies that Performance Insights data is retained for two years.

For Linux, OS X, or Unix:

aws rds modify-db-instance \ --db-instance-identifier sample-db-instance \ --enable-performance-insights \ --performance-insights-retention-period 731

For Windows:

aws rds modify-db-instance ^ --db-instance-identifier sample-db-instance ^ --enable-performance-insights ^ --performance-insights-retention-period 731
API

When you create a new DB instance using the CreateDBInstance action Amazon RDS API action, the Performance Schema is enabled when you set EnablePerformanceInsights to True.

You can also specify the EnablePerformanceInsights value using the following API actions:

When you enable Performance Insights, you can optionally specify the amount of time, in days, to retain Performance Insights data with the PerformanceInsightsRetentionPeriod parameter. Valid values are 7 (the default) or 731 (2 years).

Using the Performance Insights Dashboard

The Performance Insights dashboard contains database performance information to help you analyze and troubleshoot performance issues. On the main dashboard page, you can view information about the database load, and drill into details for a particular wait state, SQL query, host, or user.

Opening the Performance Insights Dashboard

To see the Performance Insights dashboard, use the following procedure.

To view the Performance Insights dashboard in the AWS Management Console

  1. Open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose Performance Insights.

  3. Choose a DB instance. The Performance Insights dashboard is displayed for that instance.

    You can also reach the dashboard by choosing the Current Activity widget in the instance listing.

    
							Filter metrics
    
							Filter metrics

By default, the Performance Insights dashboard shows data for the last 60 minutes. You can modify it to display data for the last 5 minutes, 60 minutes, 6 hours, or 24 hours.


					Filter metrics

Performance Insights Dashboard Components

The dashboard is divided into two parts:

  1. Average Active Sessions chart – Shows how the database load compares to DB instance capacity as represented by the Max CPU line.

  2. Top load items table – Shows the top items contributing to database load.

Average Active Sessions Chart

The Average Active Sessions chart shows how the database load compares to DB instance capacity as represented by the Max CPU line. By default, load is shown as active sessions grouped by wait states. You can also choose to display load as active sessions grouped by SQL queries, hosts, or users instead.


						Filter metrics

To see details for any item for the selected time period in the legend, hover over that item on the Average Active Sessions chart.


						Filter metrics

Top Load Items Table

The Top Load Items table shows the top items contributing to database load. By default, the top SQL queries that are contributing to the database load are shown. Queries are displayed as digests of multiple actual queries that are structurally similar but that possibly have different parameters. You can choose to display top wait states, hosts, or users instead.


						Filter metrics

The percentage of the database load associated with each top load item is illustrated in the DB Load by Waits column. This column reflects the load for that item by whatever grouping is currently selected in the Average Active Sessions chart. Take the case where the Average Active Sessions chart is grouping by hosts and you are looking at SQL queries in the top load items table. In this case, the DB Load by Waits bar reflects the load that query represents on the related host. Here it's colored-coded to map to the representation of that host in the Average Active Sessions chart.

For another example, suppose that the Average Active Sessions chart is grouping by wait states and you are looking at SQL queries in the top load items table. In this case, the DB Load by Waits bar is sized, segmented, and color-coded to show how much of a given wait state that query is contributing to. It also shows what wait states are affecting that query.


						Filter metrics

Analyzing Database Load Using the Performance Insights Dashboard

If the Average Active Sessions chart shows a bottleneck, you can find out where the load is coming from. To do so, look at the top load items table below the Average Active Sessions chart. Choose a particular item, like a SQL query or a user, to drill down into that item and see details about it.

DB load grouped by waits and top SQL queries is the default Performance Insights dashboard view, because this is the combination that typically provides the most insight into performance issues. DB load grouped by waits shows if there are any resource or concurrency bottlenecks in the database. In this case, the SQL tab of the top load items table shows which queries are driving that load.

Your typical workflow for diagnosing performance issues is as follows:

  1. Review the Average Active Sessions chart and see if there are any incidents of database load exceeding the Max CPU line.

  2. If there is, look at the Average Active Sessions chart and identify which wait state or states are primarily responsible.

  3. Identify the digest queries causing the load by seeing which of the queries the SQL tab on the top load items table are contributing most to those wait states. You can identify these by the DB Load by Wait column.

  4. Choose one of these digest queries in the SQL tab to expand it and see the child queries that it is composed of.

For example, in the dashboard following, IO:XactSync waits are a frequent issue. CPU wait is less, but it still contributes to load.

The first four roll-up queries in the SQL tab of the top load items table correlate strongly to the first state. Thus, those are the ones to drill into and examine the child queries of. You do so to determine how they are contributing to the performance issue.

The last three roll-up queries are the major contributors to CPU. These are the queries to investigate if CPU load is an issue.


					Filter metrics

Additional User Interface Features

You can use other features of the Performance Insights user interface to help analyze performance data.

Click-and-Drag Zoom In

In the Performance Insights interface, you can select a small portion of the load chart and zoom in on the detail.


				Zoom in

To zoom in on a portion of the load chart, choose the start time and drag to the end of the time period you want. When you do this, the selected area is highlighted. When you release the mouse, the load chart zooms in on the selected region, and the Top N table is recalculated.

Pause and Zoom Out

In the upper-right corner of the load chart, you can find the Pause and Zoom out tools.


				Pause and zoom out

When you choose Pause, the load chart stops autorefreshing. When you choose Pause again, the chart resumes autorefreshing.

When you choose Zoom out, the load chart zooms out to the next largest time interval.

Related Topics