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

Preview - Using Amazon Performance Insights

Amazon RDS Performance Insights is a service that monitors your Amazon RDS DB instance load so that you can analyze and troubleshoot your database performance. Amazon RDS Performance Insights is currently available only for use with Aurora (PostgreSQL).

Performance Insights expands on existing Amazon RDS monitoring features to illustrate your database's performance and help you analyze any issues that impact 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 for the Aurora PostgreSQL database engine. If you have more than one database on the DB instance, performance data for all of the databases is aggregated for the DB instance. Database performance data is kept for 24 hours.

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. Session information is sampled by a light weight mechanism every second, then this sampled data is 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 the load in the Average Active Sessions chart is often above the Max CPU line and the primary wait state is CPU, then the system CPU is overloaded and 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 may be bottlenecks or resource contention issues that you should resolve, even if the load does not cross the Max CPU line.

The following video provides an overview of Performance Insights:

Using the Performance Insights Dashboard

The Performance Insights dashboard contains database performance information to help you analyze and troubleshoot performance issues. In 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 view the Performance Insights dashboard in the AWS Management Console, perform the following steps:

  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.

    The dashboard is also available by clicking on 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 last 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, but you can choose to display load as active sessions grouped by SQL queries, hosts, or users instead.


						Filter metrics

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


						Filter metrics

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. Note that the 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. It reflects the load for that item by whatever grouping is currently selected in the Average Active Sessions chart. For example, if the Average Active Sessions chart is grouping by hosts and you are looking at SQL queries in the top load items table, the DB Load by Waits bar reflects the load that query represents on the related host, and is colored-coded to map to the representation of that host in the Average Active Sessions chart.

If the Average Active Sessions chart is grouping by wait states and you are looking at SQL queries in the top load items table, 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, and also what wait states are affecting that query.


						Filter metrics

Analyze 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 by looking 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, and 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(s) 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, as identified by the DB Load by Wait column.

  4. Click on 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 below, IO:XactSync waits are a frequent issue. CPU wait is less, but it is still an important contributor to load. The first 4 roll-up queries in the SQL tab of the top load items table correlate strongly to the first state, so those are the ones you would want to drill into and examine the child queries of, in order to determine how they are contributing to the performance issue. The last 3 roll-up queries are the major contributors to CPU, and would be the queries to investiage if CPU load was an issue.


					Filter metrics

Related Topics