Amazon Aurora
User Guide for Aurora (API Version 2014-10-31)

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. You can also 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 DB instance.

    For DB instances with Performance Insights enabled, you can also reach the dashboard by choosing the Sessions item in the list of DB instances. Under Current activity, the Sessions item shows the database load in average active sessions over the last five minutes. The bar graphically shows the load. When the bar is empty, the DB instance is idle. As the load increases, the bar fills with blue. When the load passes the number of virtual CPUs (vCPUs) on the DB instance class, the bar turns red, indicating a potential bottleneck.

    
							Filter metrics

    The following image shows the dashboard for a DB instance.

    
							Enable Performance Insights during DB instance creation with console

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, 5 hours, 24 hours, or 1 week. You can also show all of the data available.

The Performance Insight dashboard automatically refreshes with new data. The refresh rate depends on the amount of data displayed:

  • 5 minutes refreshes every 5 seconds.

  • 1 hour and 5 hours both refresh every minute.

  • 24 hours refreshes every 5 minutes.

  • 1 week refreshes every hour.


					Filter metrics

Performance Insights Dashboard Components

The dashboard is divided into three parts:

  1. Counter Metrics chart – Shows data for specific performance counter metrics.

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

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

Counter Metrics Chart

The Counter Metrics chart displays data for performance counters. The default metrics shown are blks_read.avg and xact_commit.avg. You can choose which performance counters to display by selecting the gear icon in the upper-right corner of the chart.


						Filter metrics

For more information, see Performance Insights Counters.

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 instead to display load as active sessions grouped by SQL queries, hosts, or users.


						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

In the Top Load Items table, you can view the following types of identifiers (IDs) that are associated with SQL statements:

  • SQL ID – An ID that the database uses to uniquely identify a SQL statement.

  • Support SQL ID – A hash value of the SQL ID. This value is only for referencing a SQL ID when you are working with AWS Support. AWS Support doesn't have access to your actual SQL IDs and SQL text.

  • Digest ID – An ID that the database uses to uniquely identify a SQL digest. A SQL digest can contain one or more SQL statements with literals removed and white space standardized. The literals are replaced with question marks (?).

    For Aurora MySQL and Aurora PostgreSQL DB instances, you can use a digest ID to find a specific SQL digest.

  • Support Digest ID – A hash value of the digest ID. This value is only for referencing a digest ID when you are working with AWS Support. AWS Support doesn't have access to your actual digest IDs and SQL text.

In the Top Load Items table, you can open a top statement to view its IDs. The following image shows an open top statement.


						SQL IDs

You can control the IDs that the Top Load Items table shows by choosing the Preferences icon.


						SQL IDs

When you choose the Preferences icon, the Preferences window opens.


						SQL IDs

Enable the IDs that you want to have visible in the Top Load Items table, and choose Save.

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. This combination 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

Viewing More SQL Text in the Performance Insights Dashboard

By default, each row in the Top Load Items table shows 500 bytes of SQL text for each SQL statement. When a SQL statement is larger than 500 bytes, you can view more of the SQL statement by opening the statement in the Performance Insights dashboard. The Performance Insights dashboard can display up to 4,096 bytes for a SQL statement. You can also copy or download the displayed SQL statement.

The limit for SQL text depends on the DB engine. The following limits apply:

  • Aurora MySQL 5.7 – 4,096 bytes

  • Aurora MySQL 5.6 – 1,024 bytes

  • Aurora PostgreSQL – Set by the track_activity_query_size DB instance parameter

For Aurora PostgreSQL DB instances, you can control the limit of the SQL text size by setting the track_activity_query_size DB instance parameter, up to 102,400 bytes. You can use the AWS Management Console to download SQL text up to the limit you set with this parameter. For more information, see Setting the SQL Text Limit for Aurora PostgreSQL DB Instances.

Important

Currently, you can only view and download more SQL text with the AWS Management Console. The AWS Performance Insights CLI and API can return a maximum of 500 bytes of text.

Note

For Aurora MySQL DB instances, viewing more SQL text is not supported in the EU (Stockholm) region.

To view more SQL text in the Performance Insights dashboard

  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 DB instance.

    SQL statements with text larger than 500 bytes look similar to the following image.

    
							SQL statements with large text
  4. Open a SQL statement to view more of the SQL text.

    
							Viewing more SQL text

    The Performance Insights dashboard can display up to 4,096 bytes for each SQL statement.

  5. (Optional) Choose Copy snippet to copy the displayed SQL statement, or choose Download full SQL to download the SQL statement.

    Note

    To copy or download the SQL statement, disable pop-up blockers.

Setting the SQL Text Limit for Aurora PostgreSQL DB Instances

For Aurora PostgreSQL DB instances, you can control the limit for the SQL text that can be shown on the Performance Insights dashboard.

To do so, modify the track_activity_query_size DB instance parameter. On Aurora PostgreSQL version 9.6, the default setting for the track_activity_query_size parameter is 1,024 bytes. On Aurora PostgreSQL version 10 or higher, the default setting for the track_activity_query_size parameter is 4,096 bytes.

You can increase the number of bytes to increase the SQL text size visible in the Performance Insights dashboard. The limit for the parameter is 10,240 bytes. For more information about the track_activity_query_size DB instance parameter, see Run-time Statistics in the PostgreSQL documentation.

To modify the parameter, change the parameter setting in the parameter group that is associated with the Aurora PostgreSQL DB instance.

If the Aurora PostgreSQL DB instance is using the default parameter group, complete the following steps:

  1. Create a new DB instance parameter group for the appropriate DB engine and DB engine version.

  2. Set the parameter in the new parameter group.

  3. Associate the new parameter group with the DB instance.

For information about setting a DB instance parameter, see Modifying Parameters in a DB Parameter Group.