# Monitoring DB load with Performance Insights on Amazon RDS **Important** AWS has announced the end-of-life date for Performance Insights: June 30, 2026. After this date, Amazon RDS will no longer support the Performance Insights console experience, flexible retention periods (1-24 months), and their associated pricing. The Performance Insights API will continue to exist with no pricing changes. Costs for the Performance Insights API will appear in your AWS bill with the cost of CloudWatch Database Insights. We recommend that you upgrade any DB instances using the paid tier of Performance Insights to the Advanced mode of Database Insights before June 30, 2026. For information about upgrading to the Advanced mode of Database Insights, see [Turning on the Advanced mode of Database Insights for Amazon RDS](USER_DatabaseInsights.TurningOnAdvanced.md). If you take no action, DB instances using Performance Insights will default to using the Standard mode of Database Insights. With Standard mode of Database Insights, you might lose access to performance data history beyond 7 days and might not be able to use execution plans and on-demand analysis features in the Amazon RDS console. After June 30, 2026 only the Advanced mode of Database Insights will support execution plans and on-demand analysis. With CloudWatch Database Insights, you can monitor database load for your fleet of databases and analyze and troubleshoot performance at scale. For more information about Database Insights, see [Monitoring Amazon RDS databases with CloudWatch Database Insights](USER_DatabaseInsights.md). For pricing information, see [Amazon CloudWatch Pricing](https://aws.amazon.com/cloudwatch/pricing/). Performance Insights expands on existing Amazon RDS monitoring features to illustrate and help you analyze your database performance. With the Performance Insights dashboard, you can visualize the database load on your Amazon RDS DB instance load and filter the load by waits, SQL statements, hosts, or users. For information about using Performance Insights with Amazon DocumentDB, see *[Amazon DocumentDB Developer Guide](https://docs.aws.amazon.com/documentdb/latest/developerguide/performance-insights.html)*. **Topics** + [ # Overview of Performance Insights on Amazon RDS ](USER_PerfInsights.Overview.md) + [ # Turning Performance Insights on and off for Amazon RDS ](USER_PerfInsights.Enabling.md) + [ # Overview of the Performance Schema for Performance Insights on Amazon RDS for MariaDB or MySQL ](USER_PerfInsights.EnableMySQL.md) + [ # Configuring access policies for Performance Insights ](USER_PerfInsights.access-control.md) + [ # Analyzing metrics with the Performance Insights dashboard ](USER_PerfInsights.UsingDashboard.md) + [ # Viewing Performance Insights proactive recommendations ](USER_PerfInsights.InsightsRecommendationViewDetails.md) + [ # Retrieving metrics with the Performance Insights API for Amazon RDS ](USER_PerfInsights.API.md) + [ # Logging Performance Insights calls using AWS CloudTrail ](USER_PerfInsights.CloudTrail.md) + [ # Performance Insights API and interface VPC endpoints (AWS PrivateLink) ](pi-vpc-interface-endpoints.md) # Overview of Performance Insights on Amazon RDS **Important** AWS has announced the end-of-life date for Performance Insights: June 30, 2026. After this date, Amazon RDS will no longer support the Performance Insights console experience, flexible retention periods (1-24 months), and their associated pricing. The Performance Insights API will continue to exist with no pricing changes. Costs for the Performance Insights API will appear in your AWS bill with the cost of CloudWatch Database Insights. We recommend that you upgrade any DB instances using the paid tier of Performance Insights to the Advanced mode of Database Insights before June 30, 2026. For information about upgrading to the Advanced mode of Database Insights, see [Turning on the Advanced mode of Database Insights for Amazon RDS](USER_DatabaseInsights.TurningOnAdvanced.md). If you take no action, DB instances using Performance Insights will default to using the Standard mode of Database Insights. With Standard mode of Database Insights, you might lose access to performance data history beyond 7 days and might not be able to use execution plans and on-demand analysis features in the Amazon RDS console. After June 30, 2026 only the Advanced mode of Database Insights will support execution plans and on-demand analysis. With CloudWatch Database Insights, you can monitor database load for your fleet of databases and analyze and troubleshoot performance at scale. For more information about Database Insights, see [Monitoring Amazon RDS databases with CloudWatch Database Insights](USER_DatabaseInsights.md). For pricing information, see [Amazon CloudWatch Pricing](https://aws.amazon.com/cloudwatch/pricing/). By default, RDS enables Performance Insights in the console create wizard for all Amazon RDS engines. If you have more than one database on a DB instance, Performance Insights aggregates performance data. You can find an overview of Performance Insights for Amazon RDS in the following video. [![AWS Videos](http://img.youtube.com/vi/yOeWcPBT458/0.jpg)](http://www.youtube.com/watch?v=yOeWcPBT458) **Important** The following topics describe using Amazon RDS Performance Insights with non-Aurora DB engines. For information about using Amazon RDS Performance Insights with Amazon Aurora, see [Using Amazon RDS Performance Insights](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PerfInsights.html) in the *Amazon Aurora User Guide*. **Topics** + [ # Database load ](USER_PerfInsights.Overview.ActiveSessions.md) + [ # Maximum CPU ](USER_PerfInsights.Overview.MaxCPU.md) + [ # Amazon RDS DB engine, Region, and instance class support for Performance Insights ](USER_PerfInsights.Overview.Engines.md) + [ # Pricing and data retention for Performance Insights ](USER_PerfInsights.Overview.cost.md) # Database load *Database load (DB load)* measures the level of session activity in your database. `DBLoad` is the key metric in Performance Insights, and Performance Insights collects DB load every second. **Topics** + [ ## Active sessions ](#USER_PerfInsights.Overview.ActiveSessions.active-sessions) + [ ## Average active sessions ](#USER_PerfInsights.Overview.ActiveSessions.AAS) + [ ## Average active executions ](#USER_PerfInsights.Overview.ActiveSessions.AAE) + [ ## Dimensions ](#USER_PerfInsights.Overview.ActiveSessions.dimensions) ## Active sessions A *database session* represents an application's dialogue with a relational database. An active session is a connection that has submitted work to the DB engine and is waiting for a response. A session is active when it's either running on CPU or waiting for a resource to become available so that it can proceed. For example, an active session might wait for a page (or block) to be read into memory, and then consume CPU while it reads data from the page. ## Average active sessions The *average active sessions (AAS)* is the unit for the `DBLoad` metric in Performance Insights. It measures how many sessions are concurrently active on the database. Every second, Performance Insights samples the number of sessions concurrently running a query. For each active session, Performance Insights collects the following data: + SQL statement + Session state (running on CPU or waiting) + Host + User running the SQL Performance Insights calculates the AAS by dividing the total number of sessions by the number of samples for a specific time period. For example, the following table shows 5 consecutive samples of a running query taken at 1-second intervals. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Overview.ActiveSessions.html) In the preceding example, the DB load for the time interval was 2 AAS. This measurement means that, on average, 2 sessions were active at any given time during the interval when the 5 samples were taken. ## Average active executions The average active executions (AAE) per second is related to AAS. To calculate the AAE, Performance Insights divides the total execution time of a query by the time interval. The following table shows the AAE calculation for the same query in the preceding table. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Overview.ActiveSessions.html) In most cases, the AAS and AAE for a query are approximately the same. However, because the inputs to the calculations are different data sources, the calculations often vary slightly. ## Dimensions The `db.load` metric is different from the other time-series metrics because you can break it into subcomponents called dimensions. You can think of dimensions as "slice by" categories for the different characteristics of the `DBLoad` metric. When you are diagnosing performance issues, the following dimensions are often the most useful: **Topics** + [ ### Wait events ](#USER_PerfInsights.Overview.ActiveSessions.waits) + [ ### Top SQL ](#USER_PerfInsights.Overview.ActiveSessions.top-sql) + [ ### Plans ](#USER_PerfInsights.Overview.ActiveSessions.plans) For a complete list of dimensions for the Amazon RDS engines, see [DB load sliced by dimensions](USER_PerfInsights.UsingDashboard.Components.md#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.dims). ### Wait events A *wait event* causes a SQL statement to wait for a specific event to happen before it can continue running. Wait events are an important dimension, or category, for DB load because they indicate where work is impeded. Every active session is either running on the CPU or waiting. For example, sessions consume CPU when they search memory for a buffer, perform a calculation, or run procedural code. When sessions aren't consuming CPU, they might be waiting for a memory buffer to become free, a data file to be read, or a log to be written to. The more time that a session waits for resources, the less time it runs on the CPU. When you tune a database, you often try to find out the resources that sessions are waiting for. For example, two or three wait events might account for 90 percent of DB load. This measure means that, on average, active sessions are spending most of their time waiting for a small number of resources. If you can find out the cause of these waits, you can attempt a solution. Wait events vary by DB engine: + For information about all MariaDB and MySQL wait events, see [Wait Event Summary Tables](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-wait-summary-tables.html) in the MySQL documentation. + For information about all PostgreSQL wait events, see [The Statistics Collector > Wait Event tables](https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-TABLE) in the PostgreSQL documentation. + For information about all Oracle wait events, see [ Descriptions of Wait Events](https://docs.oracle.com/database/121/REFRN/GUID-2FDDFAA4-24D0-4B80-A157-A907AF5C68E2.htm#REFRN-GUID-2FDDFAA4-24D0-4B80-A157-A907AF5C68E2) in the Oracle documentation. + For information about all SQL Server wait events, see [ Types of Waits](https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-2017#WaitTypes) in the SQL Server documentation. **Note** For Oracle, background processes sometimes do work without an associated SQL statement. In these cases, Performance Insights reports the type of background process concatenated with a colon and the wait class associated with that background process. Types of background process include `LGWR`, `ARC0`, `PMON`, and so on. For example, when the archiver is performing I/O, the Performance Insights report for it is similar to `ARC1:System I/O`. Occasionally, the background process type is also missing, and Performance Insights only reports the wait class, for example `:System I/O`. ### Top SQL Where wait events show bottlenecks, top SQL shows which queries are contributing the most to DB load. For example, many queries might be currently running on the database, but a single query might consume 99 percent of the DB load. In this case, the high load might indicate a problem with the query. By default, the Performance Insights console displays top SQL queries that are contributing to the database load. The console also shows relevant statistics for each statement. To diagnose performance problems for a specific statement, you can examine its execution plan. ### Plans An *execution plan*, also called simply a *plan*, is a sequence of steps that access data. For example, a plan for joining tables `t1` and `t2` might loop through all rows in `t1` and compare each row to a row in `t2`. In a relational database, an *optimizer* is built-in code that determines the most efficient plan for a SQL query. For DB instances, Performance Insights collects execution plans automatically. To diagnose SQL performance problems, examine the captured plans for high-resource SQL queries. The plans show how the database has parsed and run queries. To learn how to analyze DB load using plans, see: + Oracle: [Analyzing Oracle execution plans using the Performance Insights dashboard for Amazon RDS](USER_PerfInsights.UsingDashboard.AccessPlans.md) + SQL Server: [Analyzing SQL Server execution plans using the Performance Insights dashboard for Amazon RDS](USER_PerfInsights.UsingDashboard.AccessPlansSqlServer.md) #### Plan capture Every five minutes, Performance Insights identifies the most resource-intensive queries and captures their plans. Thus, you don't need to manually collect and manage a huge number of plans. Instead, you can use the **Top SQL** tab to focus on the plans for the most problematic queries. **Note** Performance Insights doesn't capture plans for queries whose text exceeds the maximum collectable query text limit. For more information, see [Accessing more SQL text in the Performance Insights dashboard](USER_PerfInsights.UsingDashboard.SQLTextSize.md). The retention period for execution plans is the same as for your Performance Insights data. The retention setting is **Default (7 days)**. To retain your performance data for longer, specify 1–24 months. For more information about retention periods, see [Pricing and data retention for Performance Insights](USER_PerfInsights.Overview.cost.md). #### Digest queries The **Top SQL** tab shows digest queries by default. A digest query doesn't itself have a plan, but all queries that use literal values have plans. For example, a digest query might include the text `WHERE `email`=?`. The digest might contain two queries, one with the text `WHERE email=user1@example.com` and another with `WHERE email=user2@example.com`. Each of these literal queries might include multiple plans. When you select a digest query, the console shows all plans for child statements of the selected digest. Thus, you don't need to look through all the child statements to find the plan. You might see plans that aren’t in the displayed list of top 10 child statements. The console shows plans for all child queries for which plans have been collected, regardless of whether the queries are in the top 10. # Maximum CPU In the dashboard, the **Database load** chart collects, aggregates, and displays session information. To see whether active sessions are exceeding the maximum CPU, look at their relationship to the **Max vCPU** line. Performance Insights determines the **Max vCPU** value by the number of vCPU (virtual CPU) cores for your DB instance. One process can run on a vCPU at a time. If the number of processes exceeds the number of vCPUs, the processes start queuing. When queuing increases, database performance decreases. If the DB load is often above the **Max vCPU** line, and the primary wait state is CPU, the CPU is overloaded. In this case, 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 to resolve. This can be true even if the DB load doesn't cross the **Max vCPU** line. # Amazon RDS DB engine, Region, and instance class support for Performance Insights **Important** AWS has announced the end-of-life date for Performance Insights: June 30, 2026. After this date, Amazon RDS will no longer support the Performance Insights console experience, flexible retention periods (1-24 months), and their associated pricing. The Performance Insights API will continue to exist with no pricing changes. Costs for the Performance Insights API will appear in your AWS bill with the cost of CloudWatch Database Insights. We recommend that you upgrade any DB instances using the paid tier of Performance Insights to the Advanced mode of Database Insights before June 30, 2026. For information about upgrading to the Advanced mode of Database Insights, see [Turning on the Advanced mode of Database Insights for Amazon RDS](USER_DatabaseInsights.TurningOnAdvanced.md). If you take no action, DB instances using Performance Insights will default to using the Standard mode of Database Insights. With Standard mode of Database Insights, you might lose access to performance data history beyond 7 days and might not be able to use execution plans and on-demand analysis features in the Amazon RDS console. After June 30, 2026 only the Advanced mode of Database Insights will support execution plans and on-demand analysis. With CloudWatch Database Insights, you can monitor database load for your fleet of databases and analyze and troubleshoot performance at scale. For more information about Database Insights, see [Monitoring Amazon RDS databases with CloudWatch Database Insights](USER_DatabaseInsights.md). For pricing information, see [Amazon CloudWatch Pricing](https://aws.amazon.com/cloudwatch/pricing/). The following table provides Amazon RDS DB engines that support Performance Insights. **Note** For Amazon Aurora, see [Amazon Aurora DB engine support for Performance Insights](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PerfInsights.Overview.Engines.html) in *Amazon Aurora User Guide*. | Amazon RDS DB engine | Supported engine versions and Regions | Instance class restrictions | | --- | --- | --- | | Amazon RDS for MariaDB | For more information on version and Region availability of Performance Insights with RDS for MariaDB, see [Supported Regions and DB engines for Performance Insights in Amazon RDS](Concepts.RDS_Fea_Regions_DB-eng.Feature.PerformanceInsights.md). | Performance Insights isn't supported for the following instance classes: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Overview.Engines.html) | | RDS for MySQL | For more information on version and Region availability of Performance Insights with RDS for MySQL, see [Supported Regions and DB engines for Performance Insights in Amazon RDS](Concepts.RDS_Fea_Regions_DB-eng.Feature.PerformanceInsights.md). | Performance Insights isn't supported for the following instance classes: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Overview.Engines.html) | | Amazon RDS for Microsoft SQL Server | For more information on version and Region availability of Performance Insights with RDS for SQL Server, see [Supported Regions and DB engines for Performance Insights in Amazon RDS](Concepts.RDS_Fea_Regions_DB-eng.Feature.PerformanceInsights.md). | N/A | | Amazon RDS for PostgreSQL | For more information on version and Region availability of Performance Insights with RDS for PostgreSQL, see [Supported Regions and DB engines for Performance Insights in Amazon RDS](Concepts.RDS_Fea_Regions_DB-eng.Feature.PerformanceInsights.md). | N/A | | Amazon RDS for Oracle | For more information on version and Region availability of Performance Insights with RDS for Oracle, see [Supported Regions and DB engines for Performance Insights in Amazon RDS](Concepts.RDS_Fea_Regions_DB-eng.Feature.PerformanceInsights.md). | N/A | ## Amazon RDS DB engine, Region, and instance class support for Performance Insights features The following table provides Amazon RDS DB engines that support Performance Insights features. | Feature | [Pricing tier](https://aws.amazon.com/rds/performance-insights/pricing/) | [Supported regions](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.RegionsAndAvailabilityZones.html#Concepts.RegionsAndAvailabilityZones.Regions) | [ Supported DB engines](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html#Welcome.Concepts.DBInstance) | [Supported instance classes](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.DBInstanceClass.html#Concepts.DBInstanceClass.Types) | | --- | --- | --- | --- | --- | | [SQL statistics for Performance Insights](sql-statistics.md) | All | All | All | All | | [Analyzing Oracle execution plans using the Performance Insights dashboard for Amazon RDS](USER_PerfInsights.UsingDashboard.AccessPlans.md) | All | All | RDS for Oracle | All | | [Analyzing database performance for a period of time](USER_PerfInsights.UsingDashboard.AnalyzePerformanceTimePeriod.md) | Paid tier only | All | RDS for PostgreSQL | All | | [Viewing Performance Insights proactive recommendations](USER_PerfInsights.InsightsRecommendationViewDetails.md) | Paid tier only | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Overview.Engines.html) | All | All | # Pricing and data retention for Performance Insights By default, Performance Insights includes 7 days of performance data history and 1 million API requests per month. You can also purchase longer retention periods. For complete pricing information, see [Performance Insights Pricing](https://aws.amazon.com/rds/performance-insights/pricing/). In the RDS console, you can choose any of the following retention periods for your Performance Insights data: + **Default (7 days)** + ***n* months**, where ***n*** is a number from 1–24 ![\[Choose a retention period for your Performance Insights data.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-retention-periods.png) To learn how to set a retention period using the AWS CLI, see [Turning Performance Insights on and off for Amazon RDS](USER_PerfInsights.Enabling.md). **Note** Stopping a DB instance or Multi-AZ DB cluster with Performance Insights enabled doesn't affect data retention. While a DB instance or Multi-AZ DB cluster is stopped, Performance Insights won't collect any data. # Turning Performance Insights on and off for Amazon RDS **Important** AWS has announced the end-of-life date for Performance Insights: June 30, 2026. After this date, Amazon RDS will no longer support the Performance Insights console experience, flexible retention periods (1-24 months), and their associated pricing. The Performance Insights API will continue to exist with no pricing changes. Costs for the Performance Insights API will appear in your AWS bill with the cost of CloudWatch Database Insights. We recommend that you upgrade any DB instances using the paid tier of Performance Insights to the Advanced mode of Database Insights before June 30, 2026. For information about upgrading to the Advanced mode of Database Insights, see [Turning on the Advanced mode of Database Insights for Amazon RDS](USER_DatabaseInsights.TurningOnAdvanced.md). If you take no action, DB instances using Performance Insights will default to using the Standard mode of Database Insights. With Standard mode of Database Insights, you might lose access to performance data history beyond 7 days and might not be able to use execution plans and on-demand analysis features in the Amazon RDS console. After June 30, 2026 only the Advanced mode of Database Insights will support execution plans and on-demand analysis. With CloudWatch Database Insights, you can monitor database load for your fleet of databases and analyze and troubleshoot performance at scale. For more information about Database Insights, see [Monitoring Amazon RDS databases with CloudWatch Database Insights](USER_DatabaseInsights.md). For pricing information, see [Amazon CloudWatch Pricing](https://aws.amazon.com/cloudwatch/pricing/). You can turn on Performance Insights for your DB instance or Multi-AZ DB cluster when you create it. If needed, you can turn it off later by modifying your DB instance from the console. Turning Performance Insights on and off doesn't cause downtime, a reboot, or a failover. **Note** Performance Schema is an optional performance tool used by Amazon RDS for MariaDB or MySQL. If you turn Performance Schema on or off, you need to reboot. If you turn Performance Insights on or off, however, you don't need to reboot. For more information, see [Overview of the Performance Schema for Performance Insights on Amazon RDS for MariaDB or MySQL](USER_PerfInsights.EnableMySQL.md). The Performance Insights agent consumes limited CPU and memory on the DB host. When the DB load is high, the agent limits the performance impact by collecting data less frequently. ------ #### [ Console ] In the console, you can turn Performance Insights on or off when you create or modify a DB instance or Multi-AZ DB cluster. **Turning Performance Insights on or off when creating a DB instance or Multi-AZ DB cluster** After creating a new DB instance or Multi-AZ DB cluster, Amazon RDS enables Performance Insights by default. To turn off Performance Insights, choose the option **Database Insights – Standard** and deselect the option **Enable Performance Insights**. For more information, see the following topics. + To create a DB instance, follow the instructions for your DB engine in [Creating an Amazon RDS DB instance](USER_CreateDBInstance.md). + To create a Multi-AZ DB cluster, follow the instructions for your DB engine in [Creating a Multi-AZ DB cluster for Amazon RDS](create-multi-az-db-cluster.md). The following screenshot shows the **Performance Insights** section. ![\[Turn on Performance Insights during DB instance or Multi-AZ DB cluster creation with console\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_enabling.png) If you choose **Enable Performance Insights**, you have the following options: + **Retention** (for the Standard mode of Database Insights only) – The amount of time to retain Performance Insights data. The retention setting is **Default (7 days)**. To retain your performance data for longer, specify 1–24 months. For more information about retention periods, see [Pricing and data retention for Performance Insights](USER_PerfInsights.Overview.cost.md). + **AWS KMS key** – Specify your AWS KMS key. Performance Insights encrypts all potentially sensitive data using your KMS key. Data is encrypted in flight and at rest. For more information, see [Changing an AWS KMS policy for Performance Insights](USER_PerfInsights.access-control.cmk-policy.md). **Turning Performance Insights on or off when modifying a DB instance or Multi-AZ DB cluster** In the console, you can modify a DB instance or Multi-AZ DB cluster to manage Performance Insights. **To manage Performance Insights for a DB instance or Multi-AZ DB cluster using the console** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Databases**. 1. Choose a DB instance or Multi-AZ DB cluster, and choose **Modify**. 1. To turn on Performance Insights, select **Enable Performance Insights**. To turn off Performance Insights, choose the option **Database Insights – Standard** and deselect the option **Enable Performance Insights**. If you choose **Enable Performance Insights**, you have the following options: + **Retention** (for the Standard mode of Database Insights only) – The amount of time to retain Performance Insights data. The retention setting is **Default (7 days)**. To retain your performance data for longer, specify 1–24 months. For more information about retention periods, see [Pricing and data retention for Performance Insights](USER_PerfInsights.Overview.cost.md). + **AWS KMS key** – Specify your KMS key. Performance Insights encrypts all potentially sensitive data using your KMS key. Data is encrypted in flight and at rest. For more information, see [Encrypting Amazon RDS resources](Overview.Encryption.md). 1. Choose **Continue**. 1. For **Scheduling of Modifications**, choose Apply immediately. If you choose Apply during the next scheduled maintenance window, your instance ignores this setting and turns on Performance Insights immediately. 1. Choose **Modify instance**. ------ #### [ AWS CLI ] When you use the [create-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html) AWS CLI command, turn on Performance Insights by specifying `--enable-performance-insights` and set `--database-insights-mode` to either `advanced` or `standard`. To turn off Performance Insights, specify `--no-enable-performance-insights` and set `database-insights-mode` to `standard`. You can also specify these values using the following AWS CLI commands: + [create-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster.html) + [modify-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html) + [create-db-instance-read-replica](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance-read-replica.html) + [modify-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) + [restore-db-instance-from-s3](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html) When you turn on Performance Insights in the CLI, you can optionally specify the number of days to retain Performance Insights data with the `--performance-insights-retention-period` option. You can specify `7`, *month* \$1 31 (where *month* is a number from 1–23), or `731`. For example, if you want to retain your performance data for 3 months, specify `93`, which is 3 \$1 31. The default is `7` days. For more information about retention periods, see [Pricing and data retention for Performance Insights](USER_PerfInsights.Overview.cost.md). The following example turns on Performance Insights for `sample-db-cluster` and specifies that Performance Insights data is retained for 93 days (3 months). For Linux, macOS, or Unix: ``` aws rds modify-db-cluster \ --database-insights-mode standard \ --db-cluster-identifier sample-db-instance \ --enable-performance-insights \ --performance-insights-retention-period 93 ``` For Windows: ``` aws rds modify-db-cluster ^ --database-insights-mode standard ^ --db-cluster-identifier sample-db-instance ^ --enable-performance-insights ^ --performance-insights-retention-period 93 ``` If you specify a retention period such as 94 days, which isn't a valid value, RDS issues an error. ``` An error occurred (InvalidParameterValue) when calling the CreateDBInstance operation: Invalid Performance Insights retention period. Valid values are: [7, 31, 62, 93, 124, 155, 186, 217, 248, 279, 310, 341, 372, 403, 434, 465, 496, 527, 558, 589, 620, 651, 682, 713, 731] ``` **Note** You can only toggle Performance Insights for an instance in a DB cluster where Performance Insights is not managed at the cluster level. ------ #### [ RDS API ] When you create a new DB instance using the [CreateDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html) operation Amazon RDS API operation, turn on Performance Insights by setting `EnablePerformanceInsights` to `True`. To turn off Performance Insights, set `EnablePerformanceInsights` to `False` and set `DatabaseInsightsMode` to `standard`. You can also specify the `EnablePerformanceInsights` value using the following API operations: + [CreateDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBCluster.html) (Multi-AZ DB cluster) + [ModifyDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBCluster.html) (Multi-AZ DB cluster) + [ModifyDBInstance](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) + [CreateDBInstanceReadReplica](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstanceReadReplica.html) + [RestoreDBInstanceFromS3](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html) When you turn on Performance Insights, you can optionally specify the amount of time, in days, to retain Performance Insights data with the `PerformanceInsightsRetentionPeriod` parameter. You can specify `7`, *month* \$1 31 (where *month* is a number from 1–23), or `731`. For example, if you want to retain your performance data for 3 months, specify `93`, which is 3 \$1 31. The default is `7` days. For more information about retention periods, see [Pricing and data retention for Performance Insights](USER_PerfInsights.Overview.cost.md). ------ # Overview of the Performance Schema for Performance Insights on Amazon RDS for MariaDB or MySQL The Performance Schema is an optional feature for monitoring Amazon RDS for MariaDB or MySQL runtime performance at a low level of detail. The Performance Schema is designed to have minimal impact on database performance. Performance Insights is a separate feature that you can use with or without the Performance Schema. **Topics** + [ ## Overview of the Performance Schema ](#USER_PerfInsights.EnableMySQL.overview) + [ ## Performance Insights and the Performance Schema ](#USER_PerfInsights.effect-of-pfs) + [ ## Automatic management of the Performance Schema by Performance Insights ](#USER_PerfInsights.EnableMySQL.options) + [ ## Effect of a reboot on the Performance Schema ](#USER_PerfInsights.EnableMySQL.reboot) + [ # Determining whether Performance Insights is managing the Performance Schema ](USER_PerfInsights.EnableMySQL.determining-status.md) + [ # Turn on the Performance Schema for Amazon RDS for MariaDB or MySQL ](USER_PerfInsights.EnableMySQL.RDS.md) ## Overview of the Performance Schema The Performance Schema monitors events in MariaDB and MySQL databases. An *event* is a database server action that consumes time and has been instrumented so that timing information can be collected. Examples of events include the following: + Function calls + Waits for the operating system + Stages of SQL execution + Groups of SQL statements The `PERFORMANCE_SCHEMA` storage engine is a mechanism for implementing the Performance Schema feature. This engine collects event data using instrumentation in the database source code. The engine stores events in memory-only tables in the `performance_schema` database. You can query `performance_schema` just as you can query any other tables. For more information, see [MySQL Performance Schema](https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html) in the *MySQL Reference Manual*. ## Performance Insights and the Performance Schema Performance Insights and the Performance Schema are separate features, but they are connected. The behavior of Performance Insights for Amazon RDS for MariaDB or MySQL depends on whether the Performance Schema is turned on, and if so, whether Performance Insights manages the Performance Schema automatically. The following table describes the behavior. | Performance Schema turned on | Performance Insights management mode | Performance Insights behavior | | --- | --- | --- | | Yes | Automatic | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.EnableMySQL.html) | | Yes | Manual | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.EnableMySQL.html) | | No | N/A | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.EnableMySQL.html) | ## Automatic management of the Performance Schema by Performance Insights When you create an Amazon RDS for MariaDB or MySQL DB instance with Performance Insights turned on, the Performance Schema is also turned on. In this case, Performance Insights automatically manages your Performance Schema parameters. This is the recommended configuration. When Performance Insights manages the Performance Schema automatically, the **Source** of `performance_schema` is `System default`. **Note** Automatic management of the Performance Schema isn't supported for the t4g.medium instance class. If you change the `performance_schema` parameter value manually, and then later want to change to automatic management, see [Turn on the Performance Schema for Amazon RDS for MariaDB or MySQL](USER_PerfInsights.EnableMySQL.RDS.md). **Important** When Performance Insights turns on the Performance Schema, it doesn't change the parameter group values. However, the values are changed on the DB instances that are running. The only way to see the changed values is to run the `SHOW GLOBAL VARIABLES` command. ## Effect of a reboot on the Performance Schema Performance Insights and the Performance Schema differ in their requirements for DB instance reboots: **Performance Schema** To turn this feature on or off, you must reboot the DB instance. **Performance Insights** To turn this feature on or off, you don't need to reboot the DB instance. If the Performance Schema isn't currently turned on, and you turn on Performance Insights without rebooting the DB instance, the Performance Schema won't be turned on. # Determining whether Performance Insights is managing the Performance Schema To find out whether Performance Insights is currently managing the Performance Schema for all supported major engine versions, review the following table. | Setting of performance\$1schema parameter | Setting of the Source column | Performance Insights is managing the Performance Schema? | | --- | --- | --- | | 0 | System default | Yes | | 0 or 1 | Modified | No | In the following procedure, you determine whether Performance Insights is managing the Performance Schema automatically. **To determine whether Performance Insights is managing the Performance Schema automatically** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Parameter groups**. 1. Select the name of the parameter group for your DB instance. 1. Enter **performance\$1schema** in the search bar. 1. Check whether **Source** is the system default and **Value** is **0**. If so, Performance Insights is managing the Performance Schema automatically. In the example shown here, Performance Insights isn't managing the Performance Schema automatically. ![\[Shows that the settings for the performance_schema parameter are modified.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_schema_user.png) # Turn on the Performance Schema for Amazon RDS for MariaDB or MySQL Assume that Performance Insights is turned on for your DB instance or Multi-AZ DB cluster but isn't currently managing the Performance Schema. If you want to allow Performance Insights to manage the Performance Schema automatically, complete the following steps. **To configure the Performance Schema for automatic management** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. Choose **Parameter groups**. 1. Select the name of the parameter group for your DB instance or Multi-AZ DB cluster. 1. Choose **Edit**. 1. Enter **performance\$1schema** in the search bar. 1. Select the `performance_schema` parameter. 1. Choose **Set to default value**. 1. Confirm by choosing **Set values to default**. 1. Choose **Save Changes**. 1. Reboot the DB instance or Multi-AZ DB cluster. **Important** Whenever you turn the Performance Schema on or off, make sure to reboot the DB instance or Multi-AZ DB cluster. For more information about modifying instance parameters, see [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). For more information about the dashboard, see [Analyzing metrics with the Performance Insights dashboard](USER_PerfInsights.UsingDashboard.md). For more information about the MySQL performance schema, see [MySQL Performance Schema](https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html) (for 8.0) and [MySQL Performance Schema](https://dev.mysql.com/doc/refman/8.4/en/performance-schema.html) (for 8.4) in the MySQL documentation. # Configuring access policies for Performance Insights To access Performance Insights, a principal must have the appropriate permissions from AWS Identity and Access Management (IAM). **Note** To use Performance Insights with a customer-managed key, grant users the `kms:Decrypt` and `kms:GenerateDataKey` permissions for your AWS AWS KMS key. Access Performance Insights using these methods: + [Attach the `AmazonRDSPerformanceInsightsReadOnly` managed policy for read-only access](USER_PerfInsights.access-control.managed-policy.md) + [Attach the `AmazonRDSPerformanceInsightsFullAccess` managed policy for access to all operations of the Performance Insights API](USER_PerfInsights.access-control.FullAccess-managed-policy.md) + [Create a custom IAM policy with specific permissions](USER_PerfInsights.access-control.custom-policy.md) + [Configure AWS KMS permissions for encrypted Performance Insights data](USER_PerfInsights.access-control.cmk-policy.md) + [Set up fine-grained access using resource-level permissions](USER_PerfInsights.access-control.dimensionAccess-policy.md) + [Use tag-based access control to manage permissions through resource tags](USER_PerfInsights.access-control.tag-based-policy.md) # Attaching the AmazonRDSPerformanceInsightsReadOnly policy to an IAM principal `AmazonRDSPerformanceInsightsReadOnly` is an AWS managed policy that grants access to all read-only operations of the Amazon RDS Performance Insights API. If you attach `AmazonRDSPerformanceInsightsReadOnly` to a permission set or role, you must also attach the following CloudWatch permissions: + `GetMetricStatistics` + `ListMetrics` + `GetMetricData` With these permissions, the recipient can use Performance Insights with other console features. For more information about CloudWatch permissions, see [Amazon CloudWatch permissions reference](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/permissions-reference-cw.html). For more information about `AmazonRDSPerformanceInsightsReadOnly`, see [AWS managed policy: AmazonRDSPerformanceInsightsReadOnly](rds-security-iam-awsmanpol.md#rds-security-iam-awsmanpol-AmazonRDSPerformanceInsightsReadOnly). # Attaching the AmazonRDSPerformanceInsightsFullAccess policy to an IAM principal `AmazonRDSPerformanceInsightsFullAccess` is an AWS managed policy that grants access to all operations of the Amazon RDS Performance Insights API. If you attach `AmazonRDSPerformanceInsightsFullAccess` to a permission set or role, you must also attach the following CloudWatch permissions: + `GetMetricStatistics` + `ListMetrics` + `GetMetricData` With these permissions, the recipient can use Performance Insights with other console features. For more information about CloudWatch permissions, see [Amazon CloudWatch permissions reference](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/permissions-reference-cw.html). For more information about `AmazonRDSPerformanceInsightsFullAccess`, see [AWS managed policy: AmazonRDSPerformanceInsightsFullAccess](rds-security-iam-awsmanpol.md#rds-security-iam-awsmanpol-AmazonRDSPerformanceInsightsFullAccess). # Creating a custom IAM policy for Performance Insights For users who don't have either the `AmazonRDSPerformanceInsightsReadOnly` or `AmazonRDSPerformanceInsightsFullAccess` policy, you can grant access to Performance Insights by creating or modifying a user-managed IAM policy. When you attach the policy to an IAM permission set or role, the recipient can use Performance Insights. **To create a custom policy** 1. Open the IAM console at [https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/). 1. In the navigation pane, choose **Policies**. 1. Choose **Create policy**. 1. On the **Create Policy** page, choose the **JSON** option. 1. Copy and paste the text provided in the *JSON policy document* section in the *AWS Managed Policy Reference Guide* for [https://docs.aws.amazon.com/aws-managed-policy/latest/reference/AmazonRDSPerformanceInsightsReadOnly.html](https://docs.aws.amazon.com/aws-managed-policy/latest/reference/AmazonRDSPerformanceInsightsReadOnly.html) or [https://docs.aws.amazon.com/aws-managed-policy/latest/reference/AmazonRDSPerformanceInsightsFullAccess.html](https://docs.aws.amazon.com/aws-managed-policy/latest/reference/AmazonRDSPerformanceInsightsFullAccess.html) policy. 1. Choose **Review policy**. 1. Provide a name for the policy and optionally a description, and then choose **Create policy**. You can now attach the policy to a permission set or role. The following procedure assumes that you already have a user available for this purpose. **To attach the policy to a user** 1. Open the IAM console at [https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/). 1. In the navigation pane, choose **Users**. 1. Choose an existing user from the list. **Important** To use Performance Insights, make sure that you have access to Amazon RDS in addition to the custom policy. For example, the `AmazonRDSPerformanceInsightsReadOnly` predefined policy provides read-only access to Amazon RDS. For more information, see [Managing access using policies](UsingWithRDS.IAM.md#security_iam_access-manage). 1. On the **Summary** page, choose **Add permissions**. 1. Choose **Attach existing policies directly**. For **Search**, type the first few characters of your policy name, as shown in the following image. ![\[Choose a Policy\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_attach_iam_policy.png) 1. Choose your policy, and then choose **Next: Review**. 1. Choose **Add permissions**. # Changing an AWS KMS policy for Performance Insights Performance Insights uses an AWS KMS key to encrypt sensitive data. When you enable Performance Insights through the API or the console, you can do either of the following: + Choose the default AWS managed key. Amazon RDS uses the AWS managed key for your new DB instance. Amazon RDS creates an AWS managed key for your AWS account. Your AWS account has a different AWS managed key for Amazon RDS for each AWS Region. + Choose a customer managed key. If you specify a customer managed key, users in your account that call the Performance Insights API need the `kms:Decrypt` and `kms:GenerateDataKey` permissions on the KMS key. You can configure these permissions through IAM policies. However, we recommend that you manage these permissions through your KMS key policy. For more information, see [Key policies in AWS KMS](https://docs.aws.amazon.com/kms/latest/developerguide/key-policies.html) in the *AWS Key Management Service Developer Guide*. **Example** The following example shows how to add statements to your KMS key policy. These statements allow access to Performance Insights. Depending on how you use the KMS key, you might want to change some restrictions. Before adding statements to your policy, remove all comments. **** ``` { "Version":"2012-10-17", "Id" : "your-policy", "Statement" : [ { "Sid" : "AllowViewingRDSPerformanceInsights", "Effect": "Allow", "Principal": { "AWS": [ "arn:aws:iam::444455556666:role/Role1" ] }, "Action": [ "kms:Decrypt", "kms:GenerateDataKey" ], "Resource": "*", "Condition" : { "StringEquals" : { "kms:ViaService" : "rds.us-east-1.amazonaws.com" }, "ForAnyValue:StringEquals": { "kms:EncryptionContext:aws:pi:service": "rds", "kms:EncryptionContext:service": "pi", "kms:EncryptionContext:aws:rds:db-id": "db-AAAAABBBBBCCCCDDDDDEEEEE" } } } ] } ``` ## How Performance Insights uses AWS KMS customer managed key Performance Insights uses customer managed keys to encrypt sensitive data. When you turn on Performance Insights, you can provide an AWS KMS key through the API. Performance Insights creates AWS KMS permissions on this key. It uses the key and performs the necessary operations to process sensitive data. Sensitive data includes fields such as user, database, application, and SQL query text. Performance Insights ensures that the data remains encrypted both at rest and in-flight. ## How Performance Insights IAM works with AWS KMS IAM gives permissions to specific APIs. Performance Insights has the following public APIs, which you can restrict using IAM policies: + `DescribeDimensionKeys` + `GetDimensionKeyDetails` + `GetResourceMetadata` + `GetResourceMetrics` + `ListAvailableResourceDimensions` + `ListAvailableResourceMetrics` You can use the following API requests to get sensitive data. + `DescribeDimensionKeys` + `GetDimensionKeyDetails` + `GetResourceMetrics` When you use the API to get sensitive data, Performance Insights leverages the caller's credentials. This check ensures that access to sensitive data is limited to those with access to the KMS key. When calling these APIs, you need permissions to call the API through the IAM policy and permissions to invoke the `kms:decrypt` action through the AWS KMS key policy. The `GetResourceMetrics` API can return both sensitive and non-sensitive data. The request parameters determine whether the response should include sensitive data. The API returns sensitive data when the request includes a sensitive dimension in either the filter or group-by parameters. For more information about the dimensions that you can use with the `GetResourceMetrics` API, see [DimensionGroup](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DimensionGroup.html). **Examples** The following example requests the sensitive data for the `db.user` group: ``` POST / HTTP/1.1 Host: Accept-Encoding: identity X-Amz-Target: PerformanceInsightsv20180227.GetResourceMetrics Content-Type: application/x-amz-json-1.1 User-Agent: X-Amz-Date: Authorization: AWS4-HMAC-SHA256 Credential=, SignedHeaders=, Signature= Content-Length: { "ServiceType": "RDS", "Identifier": "db-ABC1DEFGHIJKL2MNOPQRSTUV3W", "MetricQueries": [ { "Metric": "db.load.avg", "GroupBy": { "Group": "db.user", "Limit": 2 } } ], "StartTime": 1693872000, "EndTime": 1694044800, "PeriodInSeconds": 86400 } ``` **Example** The following example requests the non-sensitive data for the `db.load.avg` metric: ``` POST / HTTP/1.1 Host: Accept-Encoding: identity X-Amz-Target: PerformanceInsightsv20180227.GetResourceMetrics Content-Type: application/x-amz-json-1.1 User-Agent: X-Amz-Date: Authorization: AWS4-HMAC-SHA256 Credential=, SignedHeaders=, Signature= Content-Length: { "ServiceType": "RDS", "Identifier": "db-ABC1DEFGHIJKL2MNOPQRSTUV3W", "MetricQueries": [ { "Metric": "db.load.avg" } ], "StartTime": 1693872000, "EndTime": 1694044800, "PeriodInSeconds": 86400 } ``` # Granting fine-grained access for Performance Insights Fine-grained access control offers additional ways of controlling access to Performance Insights. This access control can allow or deny access to individual dimensions for `GetResourceMetrics`, `DescribeDimensionKeys`, and `GetDimensionKeyDetails` Performance Insights actions. To use fine-grained access, specify dimensions in the IAM policy by using condition keys. The evaluation of the access follows the IAM policy evaluation logic. For more information, see [Policy evaluation logic](https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_evaluation-logic.html) in the *IAM User Guide*. If the IAM policy statement doesn't specify any dimension, then the statement controls access to all the dimensions for the specified action. For the list of available dimensions, see [https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DimensionGroup.html](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DimensionGroup.html). To find out the dimensions that your credentials are authorized to access, use the `AuthorizedActions` parameter in `ListAvailableResourceDimensions` and specify the action. The allowed values for `AuthorizedActions` are as follows: + `GetResourceMetrics` + `DescribeDimensionKeys` + `GetDimensionKeyDetails` For example, if you specify `GetResourceMetrics` to the `AuthorizedActions` parameter, `ListAvailableResourceDimensions` returns the list of dimensions that the `GetResourceMetrics` action is authorized to access. If you specify multiple actions in the `AuthorizedActions` parameter, then `ListAvailableResourceDimensions` returns an intersection of dimensions that those actions are authorized to access. **Example** The following example provides access to the specified dimensions for `GetResourceMetrics` and `DescribeDimensionKeys` actions. **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "AllowToDiscoverDimensions", "Effect": "Allow", "Action": [ "pi:ListAvailableResourceDimensions" ], "Resource": [ "arn:aws:pi:us-east-1:123456789012:metrics/rds/db-ABC1DEFGHIJKL2MNOPQRSTUV3W" ] }, { "Sid": "SingleAllow", "Effect": "Allow", "Action": [ "pi:GetResourceMetrics", "pi:DescribeDimensionKeys" ], "Resource": [ "arn:aws:pi:us-east-1:123456789012:metrics/rds/db-ABC1DEFGHIJKL2MNOPQRSTUV3W" ], "Condition": { "ForAllValues:StringEquals": { "pi:Dimensions": [ "db.sql_tokenized.id", "db.sql_tokenized.statement" ] } } } ] } ``` The following is the response for the requested dimension: ``` // ListAvailableResourceDimensions API // Request { "ServiceType": "RDS", "Identifier": "db-ABC1DEFGHIJKL2MNOPQRSTUV3W", "Metrics": [ "db.load" ], "AuthorizedActions": ["DescribeDimensionKeys"] } // Response { "MetricDimensions": [ { "Metric": "db.load", "Groups": [ { "Group": "db.sql_tokenized", "Dimensions": [ { "Identifier": "db.sql_tokenized.id" }, // { "Identifier": "db.sql_tokenized.db_id" }, // not included because not allows in the IAM Policy { "Identifier": "db.sql_tokenized.statement" } ] } ] } ] } ``` The following example specifies one allow and two deny access for the dimensions. **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "AllowToDiscoverDimensions", "Effect": "Allow", "Action": [ "pi:ListAvailableResourceDimensions" ], "Resource": [ "arn:aws:pi:us-east-1:123456789012:metrics/rds/db-ABC1DEFGHIJKL2MNOPQRSTUV3W" ] }, { "Sid": "O01AllowAllWithoutSpecifyingDimensions", "Effect": "Allow", "Action": [ "pi:GetResourceMetrics", "pi:DescribeDimensionKeys" ], "Resource": [ "arn:aws:pi:us-east-1:123456789012:metrics/rds/db-ABC1DEFGHIJKL2MNOPQRSTUV3W" ] }, { "Sid": "O01DenyAppDimensionForAll", "Effect": "Deny", "Action": [ "pi:GetResourceMetrics", "pi:DescribeDimensionKeys" ], "Resource": [ "arn:aws:pi:us-east-1:123456789012:metrics/rds/db-ABC1DEFGHIJKL2MNOPQRSTUV3W" ], "Condition": { "ForAnyValue:StringEquals": { "pi:Dimensions": [ "db.application.name" ] } } }, { "Sid": "O01DenySQLForGetResourceMetrics", "Effect": "Deny", "Action": [ "pi:GetResourceMetrics" ], "Resource": [ "arn:aws:pi:us-east-1:123456789012:metrics/rds/db-ABC1DEFGHIJKL2MNOPQRSTUV3W" ], "Condition": { "ForAnyValue:StringEquals": { "pi:Dimensions": [ "db.sql_tokenized.statement" ] } } } ] } ``` The following are the responses for the requested dimensions: ``` // ListAvailableResourceDimensions API // Request { "ServiceType": "RDS", "Identifier": "db-ABC1DEFGHIJKL2MNOPQRSTUV3W", "Metrics": [ "db.load" ], "AuthorizedActions": ["GetResourceMetrics"] } // Response { "MetricDimensions": [ { "Metric": "db.load", "Groups": [ { "Group": "db.application", "Dimensions": [ // removed from response because denied by the IAM Policy // { "Identifier": "db.application.name" } ] }, { "Group": "db.sql_tokenized", "Dimensions": [ { "Identifier": "db.sql_tokenized.id" }, { "Identifier": "db.sql_tokenized.db_id" }, // removed from response because denied by the IAM Policy // { "Identifier": "db.sql_tokenized.statement" } ] }, ... ] } ] } ``` ``` // ListAvailableResourceDimensions API // Request { "ServiceType": "RDS", "Identifier": "db-ABC1DEFGHIJKL2MNOPQRSTUV3W", "Metrics": [ "db.load" ], "AuthorizedActions": ["DescribeDimensionKeys"] } // Response { "MetricDimensions": [ { "Metric": "db.load", "Groups": [ { "Group": "db.application", "Dimensions": [ // removed from response because denied by the IAM Policy // { "Identifier": "db.application.name" } ] }, { "Group": "db.sql_tokenized", "Dimensions": [ { "Identifier": "db.sql_tokenized.id" }, { "Identifier": "db.sql_tokenized.db_id" }, // allowed for DescribeDimensionKeys because our IAM Policy // denies it only for GetResourceMetrics { "Identifier": "db.sql_tokenized.statement" } ] }, ... ] } ] } ``` # Using tag-based access control for Performance Insights You can control access to Performance Insights metrics using tags inherited from the parent DB instance. To control access to Performance Insights operations, use IAM policies. These policies can check the tags on your DB instance to determine permissions. ## How tags work with Performance Insights Performance Insights automatically applies your DB instance tags to authorize Performance Insights metrics. When you add tags to your DB instance, you can immediately use those tags to control access to Performance Insights data. + To add or update tags for Performance Insights metrics, modify the tags on your DB instance. + To view tags for Performance Insights metrics, call `ListTagsForResource` on the Performance Insights metric resource. It will return the tags from the DB instance associated with the metric. **Note** The `TagResource` and `UntagResource` operations return an error if you try to use them directly on Performance Insights metrics. ## Creating tag-based IAM policies To control access to Performance Insights operations, use the `aws:ResourceTag` condition key in your IAM policies. These policies check the tags on yourDB instance. **Example** This policy prevents access to Performance Insights metrics for production databases. The policy denies the `pi:GetResourceMetrics` operation in Performance Insights for any database resource tagged with `env:prod`. ``` { "Version": "2012-10-17", "Statement": [ { "Effect": "Deny", "Action": "pi:GetResourceMetrics", "Resource": "*", "Condition": { "StringEquals": { "aws:ResourceTag/env": "prod" } } } ] } ``` # Analyzing metrics with the Performance Insights dashboard **Important** AWS has announced the end-of-life date for Performance Insights: June 30, 2026. After this date, Amazon RDS will no longer support the Performance Insights console experience, flexible retention periods (1-24 months), and their associated pricing. The Performance Insights API will continue to exist with no pricing changes. Costs for the Performance Insights API will appear in your AWS bill with the cost of CloudWatch Database Insights. We recommend that you upgrade any DB instances using the paid tier of Performance Insights to the Advanced mode of Database Insights before June 30, 2026. For information about upgrading to the Advanced mode of Database Insights, see [Turning on the Advanced mode of Database Insights for Amazon RDS](USER_DatabaseInsights.TurningOnAdvanced.md). If you take no action, DB instances using Performance Insights will default to using the Standard mode of Database Insights. With Standard mode of Database Insights, you might lose access to performance data history beyond 7 days and might not be able to use execution plans and on-demand analysis features in the Amazon RDS console. After June 30, 2026 only the Advanced mode of Database Insights will support execution plans and on-demand analysis. With CloudWatch Database Insights, you can monitor database load for your fleet of databases and analyze and troubleshoot performance at scale. For more information about Database Insights, see [Monitoring Amazon RDS databases with CloudWatch Database Insights](USER_DatabaseInsights.md). For pricing information, see [Amazon CloudWatch Pricing](https://aws.amazon.com/cloudwatch/pricing/). 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 "slice" DB load by dimensions such as wait events or SQL. **Topics** + [ # Overview of the Performance Insights dashboard ](USER_PerfInsights.UsingDashboard.Components.md) + [ # Accessing the Performance Insights dashboard ](USER_PerfInsights.UsingDashboard.Opening.md) + [ # Analyzing DB load by wait events ](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.md) + [ # Analyzing database performance for a period of time ](USER_PerfInsights.UsingDashboard.AnalyzePerformanceTimePeriod.md) + [ # Analyzing queries with the Top SQL tab in Performance Insights ](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.md) + [ # Analyzing top Oracle PDB load ](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.TopPDB.md) + [ # Analyzing execution plans using the Performance Insights dashboard for Amazon RDS ](USER_PerfInsights.UsingDashboard.AnalyzingPlans.md) # Overview of the Performance Insights dashboard The dashboard is the easiest way to interact with Performance Insights. The following example shows the dashboard for a PostgreSQL DB instance. ![\[Filter metrics\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_0b.png) **Topics** + [ ## Time range filter ](#USER_PerfInsights.UsingDashboard.Components.time-range) + [ ## Counter metrics chart ](#USER_PerfInsights.UsingDashboard.Components.Countermetrics) + [ ## Database load chart ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions) + [ ## Top dimensions table ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable) ## Time range filter By default, the Performance Insights dashboard shows DB load for the last hour. You can adjust this range to be as short as 5 minutes or as long as 2 years. You can also select a custom relative range. ![\[Performance Insights relative time\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-relative-time.png) You can select an absolute range with a beginning and ending date and time. The following example shows the time range beginning at midnight on 9/25/24 and ending at 11:59 PM on 9/28/24. ![\[Performance Insights absolute time\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-absolute-time.png) By default, the time zone for the Performance Insights dashboard is Coordinated Universal Time (UTC). You can also choose the local time zone. ![\[Select the local time zone for your Performance Insights dashboard\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-local-time-zone.png) ## Counter metrics chart With counter metrics, you can customize the Performance Insights dashboard to include up to 10 additional graphs. These graphs show a selection of dozens of operating system and database performance metrics. You can correlate this information with DB load to help identify and analyze performance problems. The **Counter metrics** chart displays data for performance counters. The default metrics depend on the DB engine: + MySQL and MariaDB – `db.SQL.Innodb_rows_read.avg` + Oracle – `db.User.user calls.avg` + Microsoft SQL Server – `db.Databases.Active Transactions(_Total).avg` + PostgreSQL – `db.Transactions.xact_commit.avg` ![\[Counter metrics\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/oracle_perf_insights_counters.png) To change the performance counters, choose **Manage Metrics**. You can select multiple **OS metrics** or **Database metrics**, as shown in the following screenshot. To see details for any metric, hover over the metric name. ![\[Filter metrics\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_select_metrics.png) For descriptions of the counter metrics that you can add for each DB engine, see [Performance Insights counter metrics](USER_PerfInsights_Counters.md). ## Database load chart The **Database load** chart shows how the database activity compares to DB instance capacity as represented by the **Max vCPU** line. By default, the stacked line chart represents DB load as average active sessions per unit of time. The DB load is sliced (grouped) by wait states. ![\[Database load\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_2.png) ### DB load sliced by dimensions You can choose to display load as active sessions grouped by any supported dimensions. The following table shows which dimensions are supported for the different engines. | Dimension | Oracle | SQL Server | PostgreSQL | MySQL | | --- | --- | --- | --- | --- | | Host | Yes | Yes | Yes | Yes | | SQL | Yes | Yes | Yes | Yes | | User | Yes | Yes | Yes | Yes | | Waits | Yes | Yes | Yes | Yes | | Plans | Yes | No | No | No | | Application | No | No | Yes | No | | Database | No | No | Yes | Yes | | Session type | No | No | Yes | No | The following image shows the dimensions for a PostgreSQL DB instance. ![\[Filter metrics\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_2b.png) ### DB load details for a dimension item To see details about a DB load item within a dimension, hover over the item name. The following image shows details for a SQL statement. ![\[Database load item details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_2c.png) To see details for any item for the selected time period in the legend, hover over that item. ![\[Time period details for DB load\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_3.png) ## Top dimensions table The Top dimensions table slices DB load by different dimensions. A dimension is a category or "slice by" for different characteristics of DB load. If the dimension is SQL, **Top SQL** shows the SQL statements that contribute the most to DB load. ![\[Top N dimensions\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_4c.png) Choose any of the following dimension tabs. | Tab | Description | Supported engines | | --- | --- | --- | | Top SQL | The SQL statements that are currently running | All | | Top waits | The event for which the database backend is waiting | All | | Top hosts | The host name of the connected client | All | | Top users | The user logged in to the database | All | | Top databases | The name of the database to which the client is connected | PostgreSQL, MySQL, MariaDB, and SQL Server only | | Top applications | The name of the application that is connected to the database | PostgreSQL and SQL Server only | | Top session types | The type of the current session | PostgreSQL only | To learn how to analyze queries by using the **Top SQL** tab, see [Overview of the Top SQL tab](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.md#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL). # Accessing the Performance Insights dashboard Amazon RDS provides a consolidated view of Performance Insights and CloudWatch metrics in the Performance Insights dashboard. To access 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/](https://console.aws.amazon.com/rds/). 1. In the left navigation pane, choose **Performance Insights**. 1. Choose a DB instance. For DB instances with Performance Insights turned on, you can also access the Performance Insights 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\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_0a.png) 1. (Optional) Choose the date or time range in the upper right and specify a different relative or absolute time interval. You can now specify a time period, and generate a database performance analysis report. The report provides the identified insights and recommendations. For more information, see [Creating a performance analysis report in Performance Insights](USER_PerfInsights.UsingDashboard.AnalyzePerformanceTimePeriod.md). ![\[Filter metrics by time interval\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_0c.png) In the following screenshot, the DB load interval is 5 hours. ![\[Set time interval to 5 hours\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_1.png) 1. (Optional) To zoom in on a portion of the DB load chart, choose the start time and drag to the end of the time period you want. The selected area is highlighted in the DB load chart. ![\[DB load for a specified time interval\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_zoom_in.png) When you release the mouse, the DB load chart zooms in on the selected AWS Region, and the **Top *dimensions*** table is recalculated. ![\[Zoom in on the selected DB load\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_zoom_in_b.png) 1. (Optional) To refresh your data automatically, select **Auto refresh**. ![\[Set automatic refresh\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_1b.png) The Performance Insights dashboard automatically refreshes with new data. The refresh rate depends on the amount of data displayed: + 5 minutes refreshes every 10 seconds. + 1 hour refreshes every 5 minutes. + 5 hours refreshes every 5 minutes. + 24 hours refreshes every 30 minutes. + 1 week refreshes every day. + 1 month refreshes every day. # Analyzing DB load by wait events If the **Database load** 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 **Database load** 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 **Database load** chart and see if there are any incidents of database load exceeding the **Max CPU** line. 1. If there is, look at the **Database load** chart and identify which wait state or states are primarily responsible. 1. 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. 1. 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, **log file sync** waits account for most of the DB load. The **LGWR all worker groups** wait is also high. The **Top SQL** chart shows what is causing the **log file sync** waits: frequent `COMMIT` statements. In this case, committing less frequently will reduce DB load. ![\[log file sync errors\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_7.png) # Analyzing database performance for a period of time Analyze database performance with on-demand analysis by creating a performance analysis report for a period of time. View performance analysis reports to find performance issues, such as resource bottlenecks or changes in a query in your DB instance. The Performance Insights dashboard allows you to select a time period and create a performance analysis report. You can also add one or more tags to the report. To use this feature, you must be using the paid tier retention period. For more information, see [Pricing and data retention for Performance Insights](USER_PerfInsights.Overview.cost.md) The report is available in the **Performance analysis reports - new** tab to select and view. The report contains the insights, related metrics, and recommendations to resolve the performance issue. The report is available to view for the duration of Performance Insights retention period. The report is deleted if the start time of the report analysis period is outside of the retention period. You can also delete the report before the retention period ends. To detect the performance issues and generate the analysis report for your DB instance, you must turn on Performance Insights. For more information about turning on Performance Insights, see [Turning Performance Insights on and off for Amazon RDS](USER_PerfInsights.Enabling.md). For the region, DB engine, and instance class support information for this feature, see [Amazon RDS DB engine, Region, and instance class support for Performance Insights features](USER_PerfInsights.Overview.Engines.md#USER_PerfInsights.Overview.PIfeatureEngnRegSupport) In the following sections, you can create, view, add tags, and delete a performance analysis report. **Topics** + [ # Creating a performance analysis report in Performance Insights ](USER_PerfInsights.UsingDashboard.CreatingPerfAnlysisReport.md) + [ # Viewing a performance analysis report in Performance Insights ](USER_PerfInsights.UsingDashboard.ViewPerfAnalysisReport.md) + [ # Adding tags to a performance analysis report in Performance Insights ](USER_PerfInsights.UsingDashboard.ManagePerfAnalysisReportTags.md) + [ # Deleting a performance analysis report in Performance Insights ](USER_PerfInsights.UsingDashboard.DeletePerfAnalysisReport.md) # Creating a performance analysis report in Performance Insights You can create a performance analysis report for a specific period in the Performance Insights dashboard. You can select a time period and add one or more tags to the analysis report. The analysis period can range from 5 minutes to 6 days. There must be at least 24 hours of performance data before the analysis start time. For the region, DB engine, and instance class support information for this feature, see [Amazon RDS DB engine, Region, and instance class support for Performance Insights features](USER_PerfInsights.Overview.Engines.md#USER_PerfInsights.Overview.PIfeatureEngnRegSupport) **To create a performance analysis report for a time period** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the left navigation pane, choose **Performance Insights**. 1. Choose a DB instance. 1. Choose **Analyze performance** in **Database load** section on the Performance Insights dashboard. The fields to set the time period and add one or more tags to the performance analysis report are displayed. ![\[Performance Insights dashboard showing fields to create analysis report\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_CreateAnalysisReport.png) 1. Choose the time period. If you set a time period in the **Relative range** or **Absolute range** in the upper right, you can only enter or select the analysis report date and time within this time period. If you select the analysis period outside of this time period, an error message displays. To set the time period, you can do any of the following: + Press and drag any of the sliders on the DB load chart. The **Performance analysis period** box displays the selected time period and DB load chart highlights the selected time period. + Choose the **Start date**, **Start time**, **End date**, and **End time** in the **Performance analysis period** box. ![\[Performance Insights dashboard with analysis period selected\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_CreateAnalysisRep_TimePeriod.png) 1. (Optional) Enter **Key** and **Value-*optional*** to add a tag for the report. ![\[Performance Insights dashboard with fields to add a new tag\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_CreateAnalysisRep_AddTag.png) 1. Choose **Analyze performance**. A banner displays a message whether the report generation is successful or failed. The message also provides the link to view the report. The following example shows the banner with the report creation successful message. ![\[Analysis report creation successful message banner\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_CreateAnaysisRep_SuccessMsg.png) The report is available to view in **Performance analysis reports - new** tab. You can create a performance analysis report using the AWS CLI. For an example on how to create a report using AWS CLI, see [Creating a performance analysis report for a time period](USER_PerfInsights.API.Examples.md#USER_PerfInsights.API.Examples.CreatePerfAnalysisReport). # Viewing a performance analysis report in Performance Insights The **Performance analysis reports - new** tab lists all the reports that are created for the DB instance. The following are displayed for each report: + **ID**: Unique identifier of the report. + **Name**: Tag key added to the report. + **Report creation time**: Time you created the report. + **Analysis start time**: Start time of the analysis in the report. + **Analysis end time**: End time of the analysis in the report. **To view a performance analysis report** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the left navigation pane, choose **Performance Insights**. 1. Choose a DB instance for which you want to view the analysis report. 1. Scroll down and choose **Performance analysis reports - new** tab in the Performance Insights dashboard. All the analysis reports for the different time periods are displayed. 1. Choose **ID** of the report you want to view. The DB load chart displays the entire analysis period by default if more than one insight is identified. If the report has identified one insight then the DB load chart displays the insight by default. The dashboard also lists the tags for the report in the **Tags** section. The following example shows the entire analysis period for the report. ![\[DB load chart showing entire analysis report period\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_EntireAnalysisRep.png) 1. Choose the insight in the **Database load insights** list you want to view if more than one insight is identified in the report. The dashboard displays the insight message, DB load chart highlighting the time period of the insight, analysis and recommendations, and the list of report tags. The following example shows the DB load insight in the report. ![\[DB load chart showing insight in the report\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_AnalysisRepInsight_chart.png) ![\[Report insight analysis and recommendation section\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_AnalysisRepInsight_Recommendations.png) # Adding tags to a performance analysis report in Performance Insights You can add a tag when you create or view a report. You can add up to 50 tags for a report. You need permissions to add the tags. For more information about the access policies for Performance Insights, see [Configuring access policies for Performance Insights](USER_PerfInsights.access-control.md) To add one or more tags while creating a report, see step 6 in the procedure [Creating a performance analysis report in Performance Insights](USER_PerfInsights.UsingDashboard.CreatingPerfAnlysisReport.md). **To add one or more tags when viewing a report** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the left navigation pane, choose **Performance Insights**. 1. Choose a DB instance. The Performance Insights dashboard appears for the DB instance. 1. Scroll down and choose **Performance analysis reports - new** tab. 1. Choose the report for which you want to add the tags. The dashboard displays the report. 1. Scroll down to **Tags** and choose **Manage tags**. 1. Choose **Add new tag**. 1. Enter the **Key** and **Value - *optional***, and choose **Add new tag**. The following example provides the option to add a new tag for the selected report. ![\[Manage Tags window to add new tags to the report\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_AddTag_ManageTags.png) A new tag is created for the report. The list of tags for the report is displayed in the **Tags** section on the dashboard. If you want to remove a tag from the report, choose **Remove** next to the tag. # Deleting a performance analysis report in Performance Insights You can delete a report from the list of reports displayed in the **Performance analysis reports** tab or while viewing a report. **To delete a report** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the left navigation pane, choose **Performance Insights**. 1. Choose a DB instance. The Performance Insights dashboard appears for the DB instance. 1. Scroll down and choose **Performance analysis reports - new** tab. 1. Select the report you want to delete and choose **Delete** in the upper right. ![\[Performance Insights dashboard to delete with a report selected for deletion\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/PI_DeleteAnalysisRep.png) A confirmation window is displayed. The report is deleted after you choose confirm. 1. (Optional) Choose **ID** of the report you want to delete. In the report page, choose **Delete** in the upper right. A confirmation window is displayed. The report is deleted after you choose confirm. # Analyzing queries with the Top SQL tab in Performance Insights In the Amazon RDS Performance Insights dashboard, you can find information about running and recent queries in the **Top SQL** tab in the **Top dimensions** table. You can use this information to tune your queries. **Topics** + [ ## Overview of the Top SQL tab ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL) + [ # Accessing more SQL text in the Performance Insights dashboard ](USER_PerfInsights.UsingDashboard.SQLTextSize.md) + [ # Viewing SQL statistics in the Performance Insights dashboard ](USER_PerfInsights.UsingDashboard.AnalyzeDBLoad.AdditionalMetrics.AnalyzingSQLLevel.md) ## Overview of the Top SQL tab By default, the **Top SQL** tab shows the 25 queries that are contributing the most to DB load. To help tune your queries, you can analyze information such as the query text and SQL statistics. You can also choose the statistics that you want to appear in the **Top SQL** tab. **Topics** + [ ### SQL text ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.text) + [ ### SQL statistics ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.statistics) + [ ### Load by waits (AAS) ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.Load-by-waits) + [ ### View SQL information ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.SQL-information) + [ ### Choose statistics preferences ](#USER_PerfInsights.UsingDashboard.Components.AvgActiveSessions.TopLoadItemsTable.TopSQL.Preferences) ### SQL text By default, each row in the **Top SQL** table shows 500 bytes of text for each statement. ![\[SQL text\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/sql-text-oracle.png) To learn how to see more than the default 500 bytes of SQL text, see [Accessing more SQL text in the Performance Insights dashboard](USER_PerfInsights.UsingDashboard.SQLTextSize.md). A *SQL digest* is a composite of multiple actual queries that are structurally similar but might have different literal values. The digest replaces hardcoded values with a question mark. For example, a digest might be `SELECT * FROM emp WHERE lname= ?`. This digest might include the following child queries: ``` SELECT * FROM emp WHERE lname = 'Sanchez' SELECT * FROM emp WHERE lname = 'Olagappan' SELECT * FROM emp WHERE lname = 'Wu' ``` To see the literal SQL statements in a digest, select the query, and then choose the plus symbol (\$1). In the following example, the selected query is a digest. ![\[Selected SQL digest\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_4b.png) **Note** A SQL digest groups similar SQL statements, but doesn't redact sensitive information. Performance Insights can show Oracle SQL text as **Unknown**. The text has this status in the following situations: + An Oracle database user other than `SYS` is active but not currently executing SQL. For example, when a parallel query completes, the query coordinator waits for helper processes to send their session statistics. For the duration of the wait, the query text shows **Unknown**. + For an RDS for Oracle instance on Standard Edition 2, Oracle Resource Manager limits the number of parallel threads. The background process doing this work causes the query text to show as **Unknown**. ### SQL statistics *SQL statistics* are performance-related metrics about SQL queries. For example, Performance Insights might show executions per second or rows processed per second. Performance Insights collects statistics for only the most common queries. Typically, these match the top queries by load shown in the Performance Insights dashboard. Every line in the **Top SQL** table shows relevant statistics for the SQL statement or digest, as shown in the following example. ![\[Top SQL\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_4.png) Performance Insights can report `0.00` and `-` (unknown) for SQL statistics. This situation occurs under the following conditions: + Only one sample exists. For example, Performance Insights calculates rates of change for RDS PostgreSQL queries based on multiple samples from the `pg_stat_statements` view. When a workload runs for a short time, Performance Insights might collect only one sample, which means that it can't calculate a rate of change. The unknown value is represented with a dash (`-`). + Two samples have the same values. Performance Insights can't calculate a rate of change because no change has occurred, so it reports the rate as `0.00`. + An RDS PostgreSQL statement lacks a valid identifier. PostgreSQL creates a identifier for a statement only after parsing and analysis. Thus, a statement can exist in the PostgreSQL internal in-memory structures with no identifier. Because Performance Insights samples internal in-memory structures once per second, low-latency queries might appear for only a single sample. If the query identifier isn't available for this sample, Performance Insights can't associate this statement with its statistics. The unknown value is represented with a dash (`-`). For a description of the SQL statistics for the Amazon RDS engines, see [SQL statistics for Performance Insights](sql-statistics.md). ### Load by waits (AAS) In **Top SQL**, the **Load by waits (AAS)** column illustrates the percentage of the database load associated with each top load item. This column reflects the load for that item by whatever grouping is currently selected in the **DB Load Chart**. For more information about Average active sessions (AAS), see [Average active sessions](USER_PerfInsights.Overview.ActiveSessions.md#USER_PerfInsights.Overview.ActiveSessions.AAS). For example, you might group the **DB load** chart by wait states. You examine 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 which wait states are affecting the selected query. ![\[DB load by waits\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_6.png) ### View SQL information In the **Top SQL** table, you can open a statement to view its information. The information appears in the bottom pane. ![\[Top SQL table with literal query selected\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-sql-ids-open.png) The following types of identifiers (IDs) that are associated with SQL statements: + **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. + **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. ### Choose statistics preferences You can control the statistics displayed in the **Top SQL** tab by choosing the **Preferences** icon. ![\[Statistics preferences\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-sql-ids-preferences-icon.png) When you choose the **Preferences** icon, the **Preferences** window opens. The following screenshot is an example of the **Preferences** window. ![\[Preferences window\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-sql-ids-preferences.png) To enable the statistics that you want to appear in the **Top SQL** tab, use your mouse to scroll to the bottom of the window, and then choose **Continue**. For more information about per-second or per-call statistics for the Amazon RDS engines, see the engine specific SQL statistics section in [SQL statistics for Performance Insights](sql-statistics.md) # Accessing more SQL text in the Performance Insights dashboard By default, each row in the **Top SQL** table shows 500 bytes of SQL text for each SQL statement. ![\[500 bytes of SQL\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-top-sql-bytes.png) When a SQL statement exceeds 500 bytes, you can view more text in the **SQL text** section below the **Top SQL** table. In this case, the maximum length for the text displayed in **SQL text** is 4 KB. This limit is introduced by the console and is subject to the limits set by the database engine. To save the text shown in **SQL text**, choose **Download**. **Topics** + [ ## Text size limits for Amazon RDS engines ](#sql-text-engine-limits) + [ # Setting the SQL text limit for Amazon RDS for PostgreSQL DB instances ](USER_PerfInsights.UsingDashboard.SQLTextLimit.md) + [ # Viewing and downloading SQL text in the Performance Insights dashboard ](view-download-text.md) ## Text size limits for Amazon RDS engines When you download SQL text, the database engine determines its maximum length. You can download SQL text up to the following per-engine limits. | DB engine | Maximum length of downloaded text | | --- | --- | | Amazon RDS for MySQL and MariaDB | The length is fixed at 4,096 bytes when the Performance Schema is enabled. If the Performance Schema isn't enabled, the length is fixed at 65,535 bytes. | | Amazon RDS for Microsoft SQL Server | 4,096 characters | | Amazon RDS for Oracle | 1,000 bytes | The **SQL text** section of the Performance Insights console displays up to the maximum that the engine returns. For example, if MySQL returns at most 1 KB to Performance Insights, it can only collect and show 1 KB, even if the original query is larger. Thus, when you view the query in **SQL text** or download it, Performance Insights returns the same number of bytes. If you use the AWS CLI or API, Performance Insights doesn't have the 4 KB limit enforced by the console. `DescribeDimensionKeys` and `GetResourceMetrics` return at most 500 bytes. **Note** `GetDimensionKeyDetails` returns the full query, but the size is subject to the engine limit. # Setting the SQL text limit for Amazon RDS for PostgreSQL DB instances Amazon RDS for PostgreSQL handles text differently. You can set the text size limit with the DB instance parameter `track_activity_query_size`. This parameter has the following characteristics: Default text size On Amazon RDS for PostgreSQL version 9.6, the default setting for the `track_activity_query_size` parameter is 1,024 bytes. On Amazon RDS for PostgreSQL version 10 or higher, the default is 4,096 bytes. Maximum text size The limit for `track_activity_query_size` is 102,400 bytes for Amazon RDS for PostgreSQL version 12 and lower. The maximum is 1 MB for version 13 and higher. If the engine returns 1 MB to Performance Insights, the console displays only the first 4 KB. If you download the query, you get the full 1 MB. In this case, viewing and downloading return different numbers of bytes. For more information about the `track_activity_query_size` DB instance parameter, see [Run-time Statistics](https://www.postgresql.org/docs/current/runtime-config-statistics.html) in the PostgreSQL documentation. To increase the SQL text size, increase the `track_activity_query_size` limit. To modify the parameter, change the parameter setting in the parameter group that is associated with the Amazon RDS for PostgreSQL DB instance. **To change the setting when the instance uses the default parameter group** 1. Create a new DB instance parameter group for the appropriate DB engine and DB engine version. 1. Set the parameter in the new parameter group. 1. 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 in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). # Viewing and downloading SQL text in the Performance Insights dashboard In the Performance Insights dashboard, you can view or download SQL text. **To view more SQL text in the Performance Insights dashboard** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Performance Insights**. 1. Choose a DB instance. 1. Scroll down to the **Top SQL** tab in the Performance Insights dashboard. 1. Choose the plus sign to expand a SQL digest and choose one of the digest's child queries. SQL statements with text larger than 500 bytes look similar to the following image. ![\[SQL statements with large text\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-large-text-1.png) 1. Scroll down to the **SQL text** tab. ![\[SQL information section shows more of the SQL text\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-large-text-2.png) The Performance Insights dashboard can display up to 4,096 bytes for each SQL statement. 1. (Optional) Choose **Copy** to copy the displayed SQL statement, or choose **Download** to download the SQL statement to view the SQL text up to the DB engine limit. **Note** To copy or download the SQL statement, disable pop-up blockers. # Viewing SQL statistics in the Performance Insights dashboard In the Performance Insights dashboard, SQL statistics are available in the **Top SQL** tab of the **Database load** chart. **To view SQL statistics** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the left navigation pane, choose **Performance Insights**. 1. At the top of the page, choose the database whose SQL statistics you want to see. 1. Scroll to the bottom of the page and choose the **Top SQL** tab. 1. Choose an individual statement or digest query. ![\[Viewing metrics for running queries\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_sql.png) 1. Choose which statistics to display by choosing the gear icon in the upper-right corner of the chart. For descriptions of the SQL statistics for the Amazon RDS engines, see [SQL statistics for Performance Insights](sql-statistics.md). The following example shows the statistics preferences for Oracle DB instances. ![\[Preferences for metrics for running queries for Oracle DB instances\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_pref_oracle.png) The following example shows the preferences for MariaDB and MySQL DB instances. ![\[Preferences for metrics for running queries for MariaDB and MySQL DB instances.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_pref_ams.png) 1. Choose Save to save your preferences. The **Top SQL** table refreshes. The following example shows statistics for an Oracle SQL query. ![\[Statistics for a SQL query\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_per_sql_stats_oracle.png) # Analyzing top Oracle PDB load When analyzing the load on an Oracle container DB (CDB), you might want to identify which pluggable databases (PDBs) contribute the most to DB load. You might also want to compare the performance of individual PDBs that are running similar queries to fine tune performance. For more information about Oracle CDBs, see [RDS for Oracle database architecture](oracle-multi-architecture.md). In the Amazon RDS Performance Insights dashboard, you can find information about pluggable databases (PDBs) under **Top PDB** tab in the **Dimensions** tab. For the region, DB engine, and instance class support information for this feature, see [Amazon RDS DB engine, Region, and instance class support for Performance Insights features](USER_PerfInsights.Overview.Engines.md#USER_PerfInsights.Overview.PIfeatureEngnRegSupport). **To analyze Top PDB load in an Oracle CDB** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the left navigation pane, select **Performance Insights**. 1. Choose an Oracle CDB instance. The Performance Insights dashboard appears for the DB instance. 1. In the **Database load (DB load)** section, choose **Pluggable database (PDB)** next to Slice by. The Average active sessions chart shows the PDB with the highest load. The PDB identifiers appear to the right of the color-coded squares. Each identifier uniquely identifies a PDB. ![\[Average active sessions chart for PDB load\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_topPDB_AAS.png) 1. Scroll down to the **Top SQL** tab. In the following example, you can see the same SQL query and the load it drives to multiple PDBs. ![\[Same SQL query load for multiple PDBs\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_topPDB_ex1.png) In the following example, a single PDB is handling higher load than other PDBs in the CDB. ![\[High SQL query load for PDB\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf_insights_topPDB_ex2.png) For more information about Oracle CDBs, see [ CDBs and PDBs](https://docs.oracle.com/en/database/oracle/oracle-database/21/cncpt/CDBs-and-PDBs.html#GUID-FC2EB562-ED31-49EF-8707-C766B6FE66B8). # Analyzing execution plans using the Performance Insights dashboard for Amazon RDS In the Amazon RDS Performance Insights dashboard, you can find information about execution plans for Oracle and SQL Server DB instances. You can use this information to know which plans contribute the most to DB load. To analyze Oracle or SQL Server execution plans, see the following topics. **Analyzing execution plans** + [Analyzing Oracle execution plans using the Performance Insights dashboard for Amazon RDS](USER_PerfInsights.UsingDashboard.AccessPlans.md) + [Analyzing SQL Server execution plans using the Performance Insights dashboard for Amazon RDS](USER_PerfInsights.UsingDashboard.AccessPlansSqlServer.md) ## Overview of analyzing execution plans for Amazon RDS You can use the Amazon RDS Performance Insights dashboard to know which plans contribute the most to DB load for Oracle and SQL Server DB instances. For example, the top SQL statements at a given time might be using the plans shown in the following table. **** | Top SQL | Plan | | --- | --- | | SELECT SUM(amount\$1sold) FROM sales WHERE prod\$1id = 10 | Plan A | | SELECT SUM(amount\$1sold) FROM sales WHERE prod\$1id = 521 | Plan B | | SELECT SUM(s\$1total) FROM sales WHERE region = 10 | Plan A | | SELECT \$1 FROM emp WHERE emp\$1id = 1000 | Plan C | | SELECT SUM(amount\$1sold) FROM sales WHERE prod\$1id = 72 | Plan A | With the plan feature of Performance Insights, you can do the following: + Find out which plans are used by the top SQL queries. For example, you might find out that most of the DB load is generated by queries using plan A and plan B, with only a small percentage using plan C. + Compare different plans for the same query. In the preceding example, three queries are identical except for the product ID. Two queries use plan A, but one query uses plan B. To see the difference in the two plans, you can use Performance Insights. + Find out when a query switched to a new plan. You might see that a query used plan A and then switched to plan B at a certain time. Was there a change in the database at this point? For example, if a table is empty, the optimizer might choose a full table scan. If the table is loaded with a million rows, the optimizer might switch to an index range scan. + Drill down to the specific steps of a plan with the highest cost. For example, the for a long-running query might show a missing a join condition in an equi-join. This missing condition forces a Cartesian join, which joins all rows of two tables. You can perform the preceding tasks by using the plan capture feature of Performance Insights. Just as you can slice queries by wait events and top SQL, you can slice them by the plan dimension. # Analyzing Oracle execution plans using the Performance Insights dashboard for Amazon RDS When analyzing DB load on an Oracle Database, you might want to know which plans are contributing the most to DB load. You can determine which plans are contributing the most to DB load by using the plan capture feature of Performance Insights. **To analyze Oracle execution plans using the console** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Performance Insights**. 1. Choose an Oracle DB instance. The Performance Insights dashboard is displayed for that DB instance. 1. In the **Database load (DB load)** section, choose **Plans** next to **Slice by**. The Average active sessions chart shows the plans used by your top SQL statements. The plan hash values appear to the right of the color-coded squares. Each hash value uniquely identifies a plan. ![\[Slice by plans\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-slice-by-plans.png) 1. Scroll down to the **Top SQL** tab. In the following example, the top SQL digest has two plans. You can tell that it's a digest by the question mark in the statement. ![\[Choose a digest plan\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/top-sql-plans-unselected.png) 1. Choose the digest to expand it into its component statements. In the following example, the `SELECT` statement is a digest query. The component queries in the digest use two different plans. The colors of the plans correspond to the database load chart. The total number of plans in the digest is shown in the second column. ![\[Choose a digest plan\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-digest-plan.png) 1. Scroll down and choose two **Plans** to compare from **Plans for digest query** list. You can view either one or two plans for a query at a time. The following screenshot compares the two plans in the digest, with hash 2032253151 and hash 1117438016. In the following example, 62% of the average active sessions running this digest query are using the plan on the left, whereas 38% are using the plan on the right. ![\[Compare the plans side by side\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-compare-plan.png) In this example, the plans differ in an important way. Step 2 in plan 2032253151 uses an index scan, whereas plan 1117438016 uses a full table scan. For a table with a large number of rows, a query of a single row is almost always faster with an index scan. ![\[Compare the plans side by side\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-table-access.png) 1. (Optional) Choose **Copy** to copy the plan to the clipboard, or **Download** to save the plan to your hard drive. # Analyzing SQL Server execution plans using the Performance Insights dashboard for Amazon RDS When analyzing DB load on a SQL Server Database, you might want to know which plans are contributing the most to DB load. You can determine which plans are contributing the most to DB load by using the plan capture feature of Performance Insights. **To analyze SQL Server execution plans using the console** 1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Performance Insights**. 1. Choose a SQL Server DB instance. The Performance Insights dashboard is displayed for that DB instance. 1. In the **Database load (DB load)** section, choose **Plans** next to **Slice by**. The Average active sessions chart shows the plans used by your top SQL statements. The plan hash values appear to the right of the color-coded squares. Each hash value uniquely identifies a plan. ![\[Slice by plans\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-slice-by-plans-sqlserver.png) 1. Scroll down to the **Top SQL** tab. In the following example, the top SQL digest has three plans. The presence of a question mark in the SQL statement indicates that the statement is a digest. To view the full SQL statement, choose a value in the **SQL statements** column. ![\[Choose a digest plan\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/top-sql-plans-unselected-sqlserver.png) 1. Choose the digest to expand it into its component statements. In the following example, the `SELECT` statement is a digest query. The component queries in the digest use three different execution plans. The colors assigned to the plans correspond to the database load chart. ![\[Choose a digest plan\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-digest-plan-sqlserver.png) 1. Scroll down and choose two **Plans** to compare from **Plans for digest query** list. You can view either one or two plans for a query at a time. The following screenshot compares two plans in the digest. In the following example, 40% of the average active sessions running this digest query are using the plan on the left, whereas 28% are using the plan on the right. ![\[Compare the plans side by side\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-compare-plan-sqlserver.png) In the previous example, the plans differ in an important way. Step 2 in the plan on the left uses an table scan, whereas the plan on the right uses a clustered index scan. For a table with a large number of rows, a query retrieving a single row is almost always faster with a clustered index scan. 1. (Optional) Choose the **Settings** icon on the Plan Details table to customize the visibility and order of columns. The following screenshot shows the Plan Details table with the **Output list** column as the second column. ![\[Customize the visibility and order of columns in the Plan Details table\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pi-plan-fields-sql-server.png) 1. (Optional) Choose **Copy** to copy the plan to the clipboard, or **Download** to save the plan to your hard drive. **Note** Performance Insights displays estimated execution plans using a hierarchical tree table. The table includes the partial execution information for each statement. For more information about the columns in the Plan Details table, see [SET SHOWPLAN\$1ALL](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql) in the SQL Server documentation. To display the full execution information for an estimated execution plan, choose **Download** to download the plan and then upload the plan to SQL Server Management Studio. For more information about displaying an estimated execution plan using SQL Server Management Studio, see [Display an Estimated Execution Plan](https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-the-estimated-execution-plan) in the SQL Server documentation. # Viewing Performance Insights proactive recommendations Amazon RDS Performance Insights monitors specific metrics and automatically creates thresholds by analyzing what levels might be potentially problematic for a specified resource. When the new metric values cross a predefined threshold over a given period of time, Performance Insights generates a proactive recommendation. This recommendation helps to prevent future database performance impact. To receive these proactive recommendations, you must turn on Performance Insights with a paid tier retention period. For more information about turning on Performance Insights, see [Turning Performance Insights on and off for Amazon RDS](USER_PerfInsights.Enabling.md). For information about pricing and data retention for Performance Insights, see [Pricing and data retention for Performance Insights](USER_PerfInsights.Overview.cost.md). To find out the regions, DB engines, and instance classes supported for the proactive recommendations, see [Amazon RDS DB engine, Region, and instance class support for Performance Insights features](USER_PerfInsights.Overview.Engines.md#USER_PerfInsights.Overview.PIfeatureEngnRegSupport). You can view the detailed analysis and recommended investigations of proactive recommendations in the recommendation details page. For more information about recommendations, see [Recommendations from Amazon RDS](monitoring-recommendations.md). **To view the detailed analysis of a proactive recommendation** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, do any of the following: + Choose **Recommendations**. The **Recommendations** page displays a list of recommendations sorted by the severity for all the resources in your account. + Choose **Databases** and then choose **Recommendations** for a resource in the databases page. The **Recommendations** tab displays the recommendations and its details for the selected resource. 1. Find a proactive recommendation and choose **View details**. The recommendation details page appears. The title provides the name of the affected resource with the issue detected and the severity. The following are the components on the recommendation details page: + **Recommendation summary** – The detected issue, recommendation and issue status, issue start and end time, recommendation modified time, and the engine type. ![\[Recommendation details page for proactive recommendation showing the Recommendation summary section in the console\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/RecommendationProactive-RecSummary.png) + **Metrics** – The graphs of the detected issue. Each graph displays a threshold determined by the resource's baseline behavior and data of the metric reported from the issue start time. ![\[Recommendation details page for proactive recommendation showing the Metrics section in the console\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/RecommedationProactive_Metrics.png) + **Analysis and recommendations** – The recommendation and the reason for the suggested recommendation. ![\[Recommendation details page for proactive recommendation showing the Analysis and recommendations section in the console\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/ProactiveRecommendation-AnalysisAndRec.png) You can review the cause of the issue and then perform the suggested recommended actions to fix the issue, or choose **Dismiss** in the upper right to dismiss the recommendation. # Retrieving metrics with the Performance Insights API for Amazon RDS When Performance Insights is turned on, the API provides visibility into instance performance. Amazon CloudWatch Logs provides the authoritative source for vended monitoring metrics for AWS services. Performance Insights offers a domain-specific view of database load measured as average active sessions (AAS). This metric appears to API consumers as a two-dimensional time-series dataset. The time dimension of the data provides DB load data for each time point in the queried time range. Each time point decomposes overall load in relation to the requested dimensions, such as `SQL`, `Wait-event`, `User`, or `Host`, measured at that time point. Amazon RDS Performance Insights monitors your Amazon RDS DB instance so that you can analyze and troubleshoot database performance. One way to view Performance Insights data is in the AWS Management Console. Performance Insights also provides a public API so that you can query your own data. You can use the API to do the following: + Offload data into a database + Add Performance Insights data to existing monitoring dashboards + Build monitoring tools To use the Performance Insights API, enable Performance Insights on one of your Amazon RDS DB instances. For information about enabling Performance Insights, see [Turning Performance Insights on and off for Amazon RDS](USER_PerfInsights.Enabling.md). For more information about the Performance Insights API, see the [Amazon RDS Performance Insights API Reference](https://docs.aws.amazon.com/performance-insights/latest/APIReference/Welcome.html). The Performance Insights API provides the following operations. **** | Performance Insights action | AWS CLI command | Description | | --- | --- | --- | | [https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_CreatePerformanceAnalysisReport.html](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_CreatePerformanceAnalysisReport.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/CreatePerformanceAnalysisReport.html](https://docs.aws.amazon.com/cli/latest/reference/pi/CreatePerformanceAnalysisReport.html) | Creates a performance analysis report for a specific time period for the DB instance. The result is `AnalysisReportId` which is the unique identifier of the report. | | [https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DeletePerformanceAnalysisReport.html](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DeletePerformanceAnalysisReport.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/DeletePerformanceAnalysisReport.html](https://docs.aws.amazon.com/cli/latest/reference/pi/DeletePerformanceAnalysisReport.html) | Deletes a performance analysis report. | | [https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DescribeDimensionKeys.html](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DescribeDimensionKeys.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/describe-dimension-keys.html](https://docs.aws.amazon.com/cli/latest/reference/pi/describe-dimension-keys.html) | Retrieves the top N dimension keys for a metric for a specific time period. | | [https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_GetDimensionKeyDetails.html](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_GetDimensionKeyDetails.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/get-dimension-key-details.html](https://docs.aws.amazon.com/cli/latest/reference/pi/get-dimension-key-details.html) | Retrieves the attributes of the specified dimension group for a DB instance or data source. For example, if you specify a SQL ID, and if the dimension details are available, `GetDimensionKeyDetails` retrieves the full text of the dimension `db.sql.statement` associated with this ID. This operation is useful because `GetResourceMetrics` and `DescribeDimensionKeys` don't support retrieval of large SQL statement text. | | [https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_GetPerformanceAnalysisReport.html](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_GetPerformanceAnalysisReport.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/GetPerformanceAnalysisReport.html](https://docs.aws.amazon.com/cli/latest/reference/pi/GetPerformanceAnalysisReport.html) | Retrieves the report including the insights for the report. The result includes the report status, report ID, report time details, insights, and recommendations. | | [GetResourceMetadata](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_GetResourceMetadata.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/get-resource-metadata.html](https://docs.aws.amazon.com/cli/latest/reference/pi/get-resource-metadata.html) | Retrieve the metadata for different features. For example, the metadata might indicate that a feature is turned on or off on a specific DB instance. | | [https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_GetResourceMetrics.html](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_GetResourceMetrics.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/get-resource-metrics.html](https://docs.aws.amazon.com/cli/latest/reference/pi/get-resource-metrics.html) | Retrieves Performance Insights metrics for a set of data sources over a time period. You can provide specific dimension groups and dimensions, and provide aggregation and filtering criteria for each group. | | [ListAvailableResourceDimensions](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_ListAvailableResourceDimensions.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/list-available-resource-dimensions.html](https://docs.aws.amazon.com/cli/latest/reference/pi/list-available-resource-dimensions.html) | Retrieve the dimensions that can be queried for each specified metric type on a specified instance. | | [ListAvailableResourceMetrics](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_ListAvailableResourceMetrics.html) | [https://docs.aws.amazon.com/cli/latest/reference/pi/list-available-resource-metrics.html](https://docs.aws.amazon.com/cli/latest/reference/pi/list-available-resource-metrics.html) | Retrieve all available metrics of the specified metric types that can be queried for a specified DB instance. | | `[ListPerformanceAnalysisReports](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_ListPerformanceAnalysisReports.html)` | [https://docs.aws.amazon.com/cli/latest/reference/pi/list-performance-analysis-reports.html](https://docs.aws.amazon.com/cli/latest/reference/pi/list-performance-analysis-reports.html) | Retrieves all the analysis reports available for the DB instance. The reports are listed based on the start time of each report. | | `[ListTagsForResource](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_ListTagsForResource.html)` | [https://docs.aws.amazon.com/cli/latest/reference/pi/list-tags-for-resource.html](https://docs.aws.amazon.com/cli/latest/reference/pi/list-tags-for-resource.html) | Lists all the metadata tags added to the resource. The list includes the name and value of the tag. | | `[TagResource](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_TagResource.html)` | [https://docs.aws.amazon.com/cli/latest/reference/pi/tag-resource.html](https://docs.aws.amazon.com/cli/latest/reference/pi/tag-resource.html) | Adds metadata tags to the Amazon RDS resource. The tag includes a name and a value. | | `[UntagResource](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_UntagResource.html)` | [https://docs.aws.amazon.com/cli/latest/reference/pi/untag-resource.html](https://docs.aws.amazon.com/cli/latest/reference/pi/untag-resource.html) | Removes the metadata tag from the resource. | For more information about retrieving time-series metrics and AWS CLI examples for Performance Insights, see the following topics. **Topics** + [ # Retrieving time-series metrics for Performance Insights ](USER_PerfInsights.API.TimeSeries.md) + [ # AWS CLI examples for Performance Insights ](USER_PerfInsights.API.Examples.md) # Retrieving time-series metrics for Performance Insights The `GetResourceMetrics` operation retrieves one or more time-series metrics from the Performance Insights data. `GetResourceMetrics` requires a metric and time period, and returns a response with a list of data points. For example, the AWS Management Console uses `GetResourceMetrics` to populate the **Counter Metrics** chart and the **Database Load** chart, as seen in the following image. ![\[Counter Metrics and Database Load charts\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-api-charts.png) All metrics returned by `GetResourceMetrics` are standard time-series metrics, with the exception of `db.load`. This metric is displayed in the **Database Load** chart. The `db.load` metric is different from the other time-series metrics because you can break it into subcomponents called *dimensions*. In the previous image, `db.load` is broken down and grouped by the waits states that make up the `db.load`. **Note** `GetResourceMetrics` can also return the `db.sampleload` metric, but the `db.load` metric is appropriate in most cases. For information about the counter metrics returned by `GetResourceMetrics`, see [Performance Insights counter metrics](USER_PerfInsights_Counters.md). The following calculations are supported for the metrics: + Average – The average value for the metric over a period of time. Append `.avg` to the metric name. + Minimum – The minimum value for the metric over a period of time. Append `.min` to the metric name. + Maximum – The maximum value for the metric over a period of time. Append `.max` to the metric name. + Sum – The sum of the metric values over a period of time. Append `.sum` to the metric name. + Sample count – The number of times the metric was collected over a period of time. Append `.sample_count` to the metric name. For example, assume that a metric is collected for 300 seconds (5 minutes), and that the metric is collected one time each minute. The values for each minute are 1, 2, 3, 4, and 5. In this case, the following calculations are returned: + Average – 3 + Minimum – 1 + Maximum – 5 + Sum – 15 + Sample count – 5 For information about using the `get-resource-metrics` AWS CLI command, see [https://docs.aws.amazon.com/cli/latest/reference/pi/get-resource-metrics.html](https://docs.aws.amazon.com/cli/latest/reference/pi/get-resource-metrics.html). For the `--metric-queries` option, specify one or more queries that you want to get results for. Each query consists of a mandatory `Metric` and optional `GroupBy` and `Filter` parameters. The following is an example of a `--metric-queries` option specification. ``` { "Metric": "string", "GroupBy": { "Group": "string", "Dimensions": ["string", ...], "Limit": integer }, "Filter": {"string": "string" ...} ``` # AWS CLI examples for Performance Insights In the following sections, learn more about the AWS Command Line Interface (AWS CLI) for Performance Insights and use AWS CLI examples. **Topics** + [ ## Built-in help for the AWS CLI for Performance Insights ](#USER_PerfInsights.API.CLI) + [ ## Retrieving counter metrics ](#USER_PerfInsights.API.Examples.CounterMetrics) + [ ## Retrieving the DB load average for top wait events ](#USER_PerfInsights.API.Examples.DBLoadAverage) + [ ## Retrieving the DB load average for top SQL ](#USER_PerfInsights.API.Examples.DBLoadAverageTop10SQL) + [ ## Retrieving the DB load average filtered by SQL ](#USER_PerfInsights.API.Examples.DBLoadAverageFilterBySQL) + [ ## Retrieving the full text of a SQL statement ](#USER_PerfInsights.API.Examples.GetDimensionKeyDetails) + [ ## Creating a performance analysis report for a time period ](#USER_PerfInsights.API.Examples.CreatePerfAnalysisReport) + [ ## Retrieving a performance analysis report ](#USER_PerfInsights.API.Examples.GetPerfAnalysisReport) + [ ## Listing all the performance analysis reports for the DB instance ](#USER_PerfInsights.API.Examples.ListPerfAnalysisReports) + [ ## Deleting a performance analysis report ](#USER_PerfInsights.API.Examples.DeletePerfAnalysisReport) + [ ## Adding tag to a performance analysis report ](#USER_PerfInsights.API.Examples.TagPerfAnalysisReport) + [ ## Listing all the tags for a performance analysis report ](#USER_PerfInsights.API.Examples.ListTagsPerfAnalysisReport) + [ ## Deleting tags from a performance analysis report ](#USER_PerfInsights.API.Examples.UntagPerfAnalysisReport) ## Built-in help for the AWS CLI for Performance Insights You can view Performance Insights data using the AWS CLI. You can view help for the AWS CLI commands for Performance Insights by entering the following on the command line. ``` aws pi help ``` If you don't have the AWS CLI installed, see [Installing the AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/installing.html) in the *AWS CLI User Guide *for information about installing it. ## Retrieving counter metrics The following screenshot shows two counter metrics charts in the AWS Management Console. ![\[Counter Metrics charts.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-api-counters-charts.png) The following example shows how to gather the same data that the AWS Management Console uses to generate the two counter metric charts. For Linux, macOS, or Unix: ``` aws pi get-resource-metrics \ --service-type RDS \ --identifier db-ID \ --start-time 2018-10-30T00:00:00Z \ --end-time 2018-10-30T01:00:00Z \ --period-in-seconds 60 \ --metric-queries '[{"Metric": "os.cpuUtilization.user.avg" }, {"Metric": "os.cpuUtilization.idle.avg"}]' ``` For Windows: ``` aws pi get-resource-metrics ^ --service-type RDS ^ --identifier db-ID ^ --start-time 2018-10-30T00:00:00Z ^ --end-time 2018-10-30T01:00:00Z ^ --period-in-seconds 60 ^ --metric-queries '[{"Metric": "os.cpuUtilization.user.avg" }, {"Metric": "os.cpuUtilization.idle.avg"}]' ``` You can also make a command easier to read by specifying a file for the `--metrics-query` option. The following example uses a file called query.json for the option. The file has the following contents. ``` [ { "Metric": "os.cpuUtilization.user.avg" }, { "Metric": "os.cpuUtilization.idle.avg" } ] ``` Run the following command to use the file. For Linux, macOS, or Unix: ``` aws pi get-resource-metrics \ --service-type RDS \ --identifier db-ID \ --start-time 2018-10-30T00:00:00Z \ --end-time 2018-10-30T01:00:00Z \ --period-in-seconds 60 \ --metric-queries file://query.json ``` For Windows: ``` aws pi get-resource-metrics ^ --service-type RDS ^ --identifier db-ID ^ --start-time 2018-10-30T00:00:00Z ^ --end-time 2018-10-30T01:00:00Z ^ --period-in-seconds 60 ^ --metric-queries file://query.json ``` The preceding example specifies the following values for the options: + `--service-type` – `RDS` for Amazon RDS + `--identifier` – The resource ID for the DB instance + `--start-time` and `--end-time` – The ISO 8601 `DateTime` values for the period to query, with multiple supported formats It queries for a one-hour time range: + `--period-in-seconds` – `60` for a per-minute query + `--metric-queries` – An array of two queries, each just for one metric. The metric name uses dots to classify the metric in a useful category, with the final element being a function. In the example, the function is `avg` for each query. As with Amazon CloudWatch, the supported functions are `min`, `max`, `total`, and `avg`. The response looks similar to the following. ``` { "Identifier": "db-XXX", "AlignedStartTime": 1540857600.0, "AlignedEndTime": 1540861200.0, "MetricList": [ { //A list of key/datapoints "Key": { "Metric": "os.cpuUtilization.user.avg" //Metric1 }, "DataPoints": [ //Each list of datapoints has the same timestamps and same number of items { "Timestamp": 1540857660.0, //Minute1 "Value": 4.0 }, { "Timestamp": 1540857720.0, //Minute2 "Value": 4.0 }, { "Timestamp": 1540857780.0, //Minute 3 "Value": 10.0 } //... 60 datapoints for the os.cpuUtilization.user.avg metric ] }, { "Key": { "Metric": "os.cpuUtilization.idle.avg" //Metric2 }, "DataPoints": [ { "Timestamp": 1540857660.0, //Minute1 "Value": 12.0 }, { "Timestamp": 1540857720.0, //Minute2 "Value": 13.5 }, //... 60 datapoints for the os.cpuUtilization.idle.avg metric ] } ] //end of MetricList } //end of response ``` The response has an `Identifier`, `AlignedStartTime`, and `AlignedEndTime`. B the `--period-in-seconds` value was `60`, the start and end times have been aligned to the minute. If the `--period-in-seconds` was `3600`, the start and end times would have been aligned to the hour. The `MetricList` in the response has a number of entries, each with a `Key` and a `DataPoints` entry. Each `DataPoint` has a `Timestamp` and a `Value`. Each `Datapoints` list has 60 data points because the queries are for per-minute data over an hour, with `Timestamp1/Minute1`, `Timestamp2/Minute2`, and so on, up to `Timestamp60/Minute60`. Because the query is for two different counter metrics, there are two elements in the response `MetricList`. ## Retrieving the DB load average for top wait events The following example is the same query that the AWS Management Console uses to generate a stacked area line graph. This example retrieves the `db.load.avg` for the last hour with load divided according to the top seven wait events. The command is the same as the command in [Retrieving counter metrics](#USER_PerfInsights.API.Examples.CounterMetrics). However, the query.json file has the following contents. ``` [ { "Metric": "db.load.avg", "GroupBy": { "Group": "db.wait_event", "Limit": 7 } } ] ``` Run the following command. For Linux, macOS, or Unix: ``` aws pi get-resource-metrics \ --service-type RDS \ --identifier db-ID \ --start-time 2018-10-30T00:00:00Z \ --end-time 2018-10-30T01:00:00Z \ --period-in-seconds 60 \ --metric-queries file://query.json ``` For Windows: ``` aws pi get-resource-metrics ^ --service-type RDS ^ --identifier db-ID ^ --start-time 2018-10-30T00:00:00Z ^ --end-time 2018-10-30T01:00:00Z ^ --period-in-seconds 60 ^ --metric-queries file://query.json ``` The example specifies the metric of `db.load.avg` and a `GroupBy` of the top seven wait events. For details about valid values for this example, see [DimensionGroup](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DimensionGroup.html) in the *Performance Insights API Reference.* The response looks similar to the following. ``` { "Identifier": "db-XXX", "AlignedStartTime": 1540857600.0, "AlignedEndTime": 1540861200.0, "MetricList": [ { //A list of key/datapoints "Key": { //A Metric with no dimensions. This is the total db.load.avg "Metric": "db.load.avg" }, "DataPoints": [ //Each list of datapoints has the same timestamps and same number of items { "Timestamp": 1540857660.0, //Minute1 "Value": 0.5166666666666667 }, { "Timestamp": 1540857720.0, //Minute2 "Value": 0.38333333333333336 }, { "Timestamp": 1540857780.0, //Minute 3 "Value": 0.26666666666666666 } //... 60 datapoints for the total db.load.avg key ] }, { "Key": { //Another key. This is db.load.avg broken down by CPU "Metric": "db.load.avg", "Dimensions": { "db.wait_event.name": "CPU", "db.wait_event.type": "CPU" } }, "DataPoints": [ { "Timestamp": 1540857660.0, //Minute1 "Value": 0.35 }, { "Timestamp": 1540857720.0, //Minute2 "Value": 0.15 }, //... 60 datapoints for the CPU key ] }, //... In total we have 8 key/datapoints entries, 1) total, 2-8) Top Wait Events ] //end of MetricList } //end of response ``` In this response, there are eight entries in the `MetricList`. There is one entry for the total `db.load.avg`, and seven entries each for the `db.load.avg` divided according to one of the top seven wait events. Unlike in the first example, because there was a grouping dimension, there must be one key for each grouping of the metric. There can't be only one key for each metric, as in the basic counter metric use case. ## Retrieving the DB load average for top SQL The following example groups `db.wait_events` by the top 10 SQL statements. There are two different groups for SQL statements: + `db.sql` – The full SQL statement, such as `select * from customers where customer_id = 123` + `db.sql_tokenized` – The tokenized SQL statement, such as `select * from customers where customer_id = ?` When analyzing database performance, it can be useful to consider SQL statements that only differ by their parameters as one logic item. So, you can use `db.sql_tokenized` when querying. However, especially when you're interested in explain plans, sometimes it's more useful to examine full SQL statements with parameters, and query grouping by `db.sql`. There is a parent-child relationship between tokenized and full SQL, with multiple full SQL (children) grouped under the same tokenized SQL (parent). The command in this example is the similar to the command in [Retrieving the DB load average for top wait events](#USER_PerfInsights.API.Examples.DBLoadAverage). However, the query.json file has the following contents. ``` [ { "Metric": "db.load.avg", "GroupBy": { "Group": "db.sql_tokenized", "Limit": 10 } } ] ``` The following example uses `db.sql_tokenized`. For Linux, macOS, or Unix: ``` aws pi get-resource-metrics \ --service-type RDS \ --identifier db-ID \ --start-time 2018-10-29T00:00:00Z \ --end-time 2018-10-30T00:00:00Z \ --period-in-seconds 3600 \ --metric-queries file://query.json ``` For Windows: ``` aws pi get-resource-metrics ^ --service-type RDS ^ --identifier db-ID ^ --start-time 2018-10-29T00:00:00Z ^ --end-time 2018-10-30T00:00:00Z ^ --period-in-seconds 3600 ^ --metric-queries file://query.json ``` This example queries over 24 hours, with a one hour period-in-seconds. The example specifies the metric of `db.load.avg` and a `GroupBy` of the top seven wait events. For details about valid values for this example, see [DimensionGroup](https://docs.aws.amazon.com/performance-insights/latest/APIReference/API_DimensionGroup.html) in the *Performance Insights API Reference.* The response looks similar to the following. ``` { "AlignedStartTime": 1540771200.0, "AlignedEndTime": 1540857600.0, "Identifier": "db-XXX", "MetricList": [ //11 entries in the MetricList { "Key": { //First key is total "Metric": "db.load.avg" } "DataPoints": [ //Each DataPoints list has 24 per-hour Timestamps and a value { "Value": 1.6964980544747081, "Timestamp": 1540774800.0 }, //... 24 datapoints ] }, { "Key": { //Next key is the top tokenized SQL "Dimensions": { "db.sql_tokenized.statement": "INSERT INTO authors (id,name,email) VALUES\n( nextval(?) ,?,?)", "db.sql_tokenized.db_id": "pi-2372568224", "db.sql_tokenized.id": "AKIAIOSFODNN7EXAMPLE" }, "Metric": "db.load.avg" }, "DataPoints": [ //... 24 datapoints ] }, // In total 11 entries, 10 Keys of top tokenized SQL, 1 total key ] //End of MetricList } //End of response ``` This response has 11 entries in the `MetricList` (1 total, 10 top tokenized SQL), with each entry having 24 per-hour `DataPoints`. For tokenized SQL, there are three entries in each dimensions list: + `db.sql_tokenized.statement` – The tokenized SQL statement. + `db.sql_tokenized.db_id ` – Either the native database ID used to refer to the SQL, or a synthetic ID that Performance Insights generates for you if the native database ID isn't available. This example returns the `pi-2372568224` synthetic ID. + `db.sql_tokenized.id` – The ID of the query inside Performance Insights. In the AWS Management Console, this ID is called the Support ID. It's named this because the ID is data that AWS Support can examine to help you troubleshoot an issue with your database. AWS takes the security and privacy of your data extremely seriously, and almost all data is stored encrypted with your AWS KMS key. Therefore, nobody inside AWS can look at this data. In the example preceding, both the `tokenized.statement` and the `tokenized.db_id` are stored encrypted. If you have an issue with your database, AWS Support can help you by referencing the Support ID. When querying, it might be convenient to specify a `Group` in `GroupBy`. However, for finer-grained control over the data that's returned, specify the list of dimensions. For example, if all that is needed is the `db.sql_tokenized.statement`, then a `Dimensions` attribute can be added to the query.json file. ``` [ { "Metric": "db.load.avg", "GroupBy": { "Group": "db.sql_tokenized", "Dimensions":["db.sql_tokenized.statement"], "Limit": 10 } } ] ``` ## Retrieving the DB load average filtered by SQL ![\[Filter by SQL chart.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/perf-insights-api-filter-chart.png) The preceding image shows that a particular query is selected, and the top average active sessions stacked area line graph is scoped to that query. Although the query is still for the top seven overall wait events, the value of the response is filtered. The filter causes it to take into account only sessions that are a match for the particular filter. The corresponding API query in this example is similar to the command in [Retrieving the DB load average for top SQL](#USER_PerfInsights.API.Examples.DBLoadAverageTop10SQL). However, the query.json file has the following contents. ``` [ { "Metric": "db.load.avg", "GroupBy": { "Group": "db.wait_event", "Limit": 5 }, "Filter": { "db.sql_tokenized.id": "AKIAIOSFODNN7EXAMPLE" } } ] ``` For Linux, macOS, or Unix: ``` aws pi get-resource-metrics \ --service-type RDS \ --identifier db-ID \ --start-time 2018-10-30T00:00:00Z \ --end-time 2018-10-30T01:00:00Z \ --period-in-seconds 60 \ --metric-queries file://query.json ``` For Windows: ``` aws pi get-resource-metrics ^ --service-type RDS ^ --identifier db-ID ^ --start-time 2018-10-30T00:00:00Z ^ --end-time 2018-10-30T01:00:00Z ^ --period-in-seconds 60 ^ --metric-queries file://query.json ``` The response looks similar to the following. ``` { "Identifier": "db-XXX", "AlignedStartTime": 1556215200.0, "MetricList": [ { "Key": { "Metric": "db.load.avg" }, "DataPoints": [ { "Timestamp": 1556218800.0, "Value": 1.4878117913832196 }, { "Timestamp": 1556222400.0, "Value": 1.192823803967328 } ] }, { "Key": { "Metric": "db.load.avg", "Dimensions": { "db.wait_event.type": "io", "db.wait_event.name": "wait/io/aurora_redo_log_flush" } }, "DataPoints": [ { "Timestamp": 1556218800.0, "Value": 1.1360544217687074 }, { "Timestamp": 1556222400.0, "Value": 1.058051341890315 } ] }, { "Key": { "Metric": "db.load.avg", "Dimensions": { "db.wait_event.type": "io", "db.wait_event.name": "wait/io/table/sql/handler" } }, "DataPoints": [ { "Timestamp": 1556218800.0, "Value": 0.16241496598639457 }, { "Timestamp": 1556222400.0, "Value": 0.05163360560093349 } ] }, { "Key": { "Metric": "db.load.avg", "Dimensions": { "db.wait_event.type": "synch", "db.wait_event.name": "wait/synch/mutex/innodb/aurora_lock_thread_slot_futex" } }, "DataPoints": [ { "Timestamp": 1556218800.0, "Value": 0.11479591836734694 }, { "Timestamp": 1556222400.0, "Value": 0.013127187864644107 } ] }, { "Key": { "Metric": "db.load.avg", "Dimensions": { "db.wait_event.type": "CPU", "db.wait_event.name": "CPU" } }, "DataPoints": [ { "Timestamp": 1556218800.0, "Value": 0.05215419501133787 }, { "Timestamp": 1556222400.0, "Value": 0.05805134189031505 } ] }, { "Key": { "Metric": "db.load.avg", "Dimensions": { "db.wait_event.type": "synch", "db.wait_event.name": "wait/synch/mutex/innodb/lock_wait_mutex" } }, "DataPoints": [ { "Timestamp": 1556218800.0, "Value": 0.017573696145124718 }, { "Timestamp": 1556222400.0, "Value": 0.002333722287047841 } ] } ], "AlignedEndTime": 1556222400.0 } //end of response ``` In this response, all values are filtered according to the contribution of tokenized SQL AKIAIOSFODNN7EXAMPLE specified in the query.json file. The keys also might follow a different order than a query without a filter, because it's the top five wait events that affected the filtered SQL. ## Retrieving the full text of a SQL statement The following example retrieves the full text of a SQL statement for DB instance `db-10BCD2EFGHIJ3KL4M5NO6PQRS5`. The `--group` is `db.sql`, and the `--group-identifier` is `db.sql.id`. In this example, *my-sql-id* represents a SQL ID retrieved by invoking `pi get-resource-metrics` or `pi describe-dimension-keys`. Run the following command. For Linux, macOS, or Unix: ``` aws pi get-dimension-key-details \ --service-type RDS \ --identifier db-10BCD2EFGHIJ3KL4M5NO6PQRS5 \ --group db.sql \ --group-identifier my-sql-id \ --requested-dimensions statement ``` For Windows: ``` aws pi get-dimension-key-details ^ --service-type RDS ^ --identifier db-10BCD2EFGHIJ3KL4M5NO6PQRS5 ^ --group db.sql ^ --group-identifier my-sql-id ^ --requested-dimensions statement ``` In this example, the dimensions details are available. Thus, Performance Insights retrieves the full text of the SQL statement, without truncating it. ``` { "Dimensions":[ { "Value": "SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id", "Dimension": "db.sql.statement", "Status": "AVAILABLE" }, ... ] } ``` ## Creating a performance analysis report for a time period The following example creates a performance analysis report with the `1682969503` start time and `1682979503` end time for the `db-loadtest-0` database. ``` aws pi create-performance-analysis-report \ --service-type RDS \ --identifier db-loadtest-0 \ --start-time 1682969503 \ --end-time 1682979503 \ --region us-west-2 ``` The response is the unique identifier `report-0234d3ed98e28fb17` for the report. ``` { "AnalysisReportId": "report-0234d3ed98e28fb17" } ``` ## Retrieving a performance analysis report The following example retrieves the analysis report details for the `report-0d99cc91c4422ee61` report. ``` aws pi get-performance-analysis-report \ --service-type RDS \ --identifier db-loadtest-0 \ --analysis-report-id report-0d99cc91c4422ee61 \ --region us-west-2 ``` The response provides the report status, ID, time details, and insights. ``` { "AnalysisReport": { "Status": "Succeeded", "ServiceType": "RDS", "Identifier": "db-loadtest-0", "StartTime": 1680583486.584, "AnalysisReportId": "report-0d99cc91c4422ee61", "EndTime": 1680587086.584, "CreateTime": 1680587087.139, "Insights": [ ... (Condensed for space) ] } } ``` ## Listing all the performance analysis reports for the DB instance The following example lists all the available performance analysis reports for the `db-loadtest-0` database. ``` aws pi list-performance-analysis-reports \ --service-type RDS \ --identifier db-loadtest-0 \ --region us-west-2 ``` The response lists all the reports with the report ID, status, and time period details. ``` { "AnalysisReports": [ { "Status": "Succeeded", "EndTime": 1680587086.584, "CreationTime": 1680587087.139, "StartTime": 1680583486.584, "AnalysisReportId": "report-0d99cc91c4422ee61" }, { "Status": "Succeeded", "EndTime": 1681491137.914, "CreationTime": 1681491145.973, "StartTime": 1681487537.914, "AnalysisReportId": "report-002633115cc002233" }, { "Status": "Succeeded", "EndTime": 1681493499.849, "CreationTime": 1681493507.762, "StartTime": 1681489899.849, "AnalysisReportId": "report-043b1e006b47246f9" }, { "Status": "InProgress", "EndTime": 1682979503.0, "CreationTime": 1682979618.994, "StartTime": 1682969503.0, "AnalysisReportId": "report-01ad15f9b88bcbd56" } ] } ``` ## Deleting a performance analysis report The following example deletes the analysis report for the `db-loadtest-0` database. ``` aws pi delete-performance-analysis-report \ --service-type RDS \ --identifier db-loadtest-0 \ --analysis-report-id report-0d99cc91c4422ee61 \ --region us-west-2 ``` ## Adding tag to a performance analysis report The following example adds a tag with a key `name` and value `test-tag` to the `report-01ad15f9b88bcbd56` report. ``` aws pi tag-resource \ --service-type RDS \ --resource-arn arn:aws:pi:us-west-2:356798100956:perf-reports/RDS/db-loadtest-0/report-01ad15f9b88bcbd56 \ --tags Key=name,Value=test-tag \ --region us-west-2 ``` ## Listing all the tags for a performance analysis report The following example lists all the tags for the `report-01ad15f9b88bcbd56` report. ``` aws pi list-tags-for-resource \ --service-type RDS \ --resource-arn arn:aws:pi:us-west-2:356798100956:perf-reports/RDS/db-loadtest-0/report-01ad15f9b88bcbd56 \ --region us-west-2 ``` The response lists the value and key for all the tags added to the report: ``` { "Tags": [ { "Value": "test-tag", "Key": "name" } ] } ``` ## Deleting tags from a performance analysis report The following example deletes the `name` tag from the `report-01ad15f9b88bcbd56` report. ``` aws pi untag-resource \ --service-type RDS \ --resource-arn arn:aws:pi:us-west-2:356798100956:perf-reports/RDS/db-loadtest-0/report-01ad15f9b88bcbd56 \ --tag-keys name \ --region us-west-2 ``` After the tag is deleted, calling the `list-tags-for-resource` API doesn't list this tag. # Logging Performance Insights calls using AWS CloudTrail Performance Insights runs with AWS CloudTrail, a service that provides a record of actions taken by a user, role, or an AWS service in Performance Insights. CloudTrail captures all API calls for Performance Insights as events. This capture includes calls from the Amazon RDS console and from code calls to the Performance Insights API operations. If you create a trail, you can enable continuous delivery of CloudTrail events to an Amazon S3 bucket, including events for Performance Insights. If you don't configure a trail, you can still view the most recent events in the CloudTrail console in **Event history**. Using the data collected by CloudTrail, you can determine certain information. This information includes the request that was made to Performance Insights, the IP address the request was made from, who made the request, and when it was made. It also includes additional details. To learn more about CloudTrail, see the [AWS CloudTrail User Guide](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/). ## Working with Performance Insights information in CloudTrail CloudTrail is enabled on your AWS account when you create the account. When activity occurs in Performance Insights, that activity is recorded in a CloudTrail event along with other AWS service events in the CloudTrail console in **Event history**. You can view, search, and download recent events in your AWS account. For more information, see [Viewing Events with CloudTrail Event History](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/view-cloudtrail-events.html) in *AWS CloudTrail User Guide.* For an ongoing record of events in your AWS account, including events for Performance Insights, create a trail. A *trail* enables CloudTrail to deliver log files to an Amazon S3 bucket. By default, when you create a trail in the console, the trail applies to all AWS Regions. The trail logs events from all AWS Regions in the AWS partition and delivers the log files to the Amazon S3 bucket that you specify. Additionally, you can configure other AWS services to further analyze and act upon the event data collected in CloudTrail logs. For more information, see the following topics in *AWS CloudTrail User Guide:* + [Overview for Creating a Trail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-create-and-update-a-trail.html) + [CloudTrail Supported Services and Integrations](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-aws-service-specific-topics.html#cloudtrail-aws-service-specific-topics-integrations) + [Configuring Amazon SNS Notifications for CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/getting_notifications_top_level.html) + [Receiving CloudTrail Log Files from Multiple Regions](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/receive-cloudtrail-log-files-from-multiple-regions.html) and [Receiving CloudTrail Log Files from Multiple Accounts](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-receive-logs-from-multiple-accounts.html) All Performance Insights operations are logged by CloudTrail and are documented in the [Performance Insights API Reference](https://docs.aws.amazon.com/performance-insights/latest/APIReference/Welcome.html). For example, calls to the `DescribeDimensionKeys` and `GetResourceMetrics` operations generate entries in the CloudTrail log files. Every event or log entry contains information about who generated the request. The identity information helps you determine the following: + Whether the request was made with root or IAM user credentials. + Whether the request was made with temporary security credentials for a role or federated user. + Whether the request was made by another AWS service. For more information, see the [CloudTrail userIdentity Element](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference-user-identity.html). ## Performance Insights log file entries A *trail* is a configuration that enables delivery of events as log files to an Amazon S3 bucket that you specify. CloudTrail log files contain one or more log entries. An *event* represents a single request from any source. Each event includes information about the requested operation, the date and time of the operation, request parameters, and so on. CloudTrail log files aren't an ordered stack trace of the public API calls, so they don't appear in any specific order. The following example shows a CloudTrail log entry that demonstrates the `GetResourceMetrics` operation. ``` { "eventVersion": "1.05", "userIdentity": { "type": "IAMUser", "principalId": "AKIAIOSFODNN7EXAMPLE", "arn": "arn:aws:iam::123456789012:user/johndoe", "accountId": "123456789012", "accessKeyId": "AKIAI44QH8DHBEXAMPLE", "userName": "johndoe" }, "eventTime": "2019-12-18T19:28:46Z", "eventSource": "pi.amazonaws.com", "eventName": "GetResourceMetrics", "awsRegion": "us-east-1", "sourceIPAddress": "72.21.198.67", "userAgent": "aws-cli/1.16.240 Python/3.7.4 Darwin/18.7.0 botocore/1.12.230", "requestParameters": { "identifier": "db-YTDU5J5V66X7CXSCVDFD2V3SZM", "metricQueries": [ { "metric": "os.cpuUtilization.user.avg" }, { "metric": "os.cpuUtilization.idle.avg" } ], "startTime": "Dec 18, 2019 5:28:46 PM", "periodInSeconds": 60, "endTime": "Dec 18, 2019 7:28:46 PM", "serviceType": "RDS" }, "responseElements": null, "requestID": "9ffbe15c-96b5-4fe6-bed9-9fccff1a0525", "eventID": "08908de0-2431-4e2e-ba7b-f5424f908433", "eventType": "AwsApiCall", "recipientAccountId": "123456789012" } ``` # Performance Insights API and interface VPC endpoints (AWS PrivateLink) You can use AWS PrivateLink to create a private connection between your VPC and Amazon RDS Performance Insights. You can access Performance Insights as if it were in your VPC, without the use of an internet gateway, NAT device, VPN connection, or Direct Connect connection. Instances in your VPC don't need public IP addresses to access Performance Insights. You establish this private connection by creating an *interface endpoint*, powered by AWS PrivateLink. We create an endpoint network interface in each subnet that you enable for the interface endpoint. These are requester-managed network interfaces that serve as the entry point for traffic destined for Performance Insights. For more information, see [Access AWS services through AWS PrivateLink](https://docs.aws.amazon.com/vpc/latest/privatelink/privatelink-access-aws-services.html) in the *AWS PrivateLink Guide*. ## Considerations for Performance Insights Before you set up an interface endpoint for Performance Insights, review [Considerations](https://docs.aws.amazon.com/vpc/latest/privatelink/create-interface-endpoint.html#considerations-interface-endpoints) in the *AWS PrivateLink Guide*. Performance Insights supports making calls to all of its API actions through the interface endpoint. By default, full access to Performance Insights is allowed through the interface endpoint. To control traffic to Performance Insights through the interface endpoint, associate a security group with the endpoint network interfaces. ## Availability Performance Insights API currently supports VPC endpoints in AWS Regions that support Performance Insights. For information about Performance Insights availability, see [Supported Regions and DB engines for Performance Insights in Amazon RDS](Concepts.RDS_Fea_Regions_DB-eng.Feature.PerformanceInsights.md) . ## Create an interface endpoint for Performance Insights You can create an interface endpoint for Performance Insights using either the Amazon VPC console or the AWS Command Line Interface (AWS CLI). For more information, see [Create an interface endpoint](https://docs.aws.amazon.com/vpc/latest/privatelink/create-interface-endpoint.html#create-interface-endpoint-aws) in the *AWS PrivateLink Guide*. Create an interface endpoint for Performance Insights using the following service name: If you enable private DNS for the interface endpoint, you can make API requests to Performance Insights using its default Regional DNS name. For example, `pi.us-east-1.amazonaws.com`. ## Creating a VPC endpoint policy for Performance Insights API An endpoint policy is an IAM resource that you can attach to an interface endpoint. The default endpoint policy allows full access to Performance Insights through the interface endpoint. To control the access allowed to Performance Insights from your VPC, attach a custom endpoint policy to the interface endpoint. An endpoint policy specifies the following information: + The principals that can perform actions (AWS accounts, IAM users, and IAM roles). + The actions that can be performed. + The resources on which the actions can be performed. For more information, see [Control access to services using endpoint policies](https://docs.aws.amazon.com/vpc/latest/privatelink/vpc-endpoints-access.html) in the *AWS PrivateLink Guide*. **Example: VPC endpoint policy for Performance Insights actions** The following is an example of a custom endpoint policy. When you attach this policy to your interface endpoint, it grants access to the listed Performance Insights actions for all principals on all resources. ``` { "Statement":[ { "Principal":"*", "Effect":"Allow", "Action":[ "rds:CreatePerformanceAnalysisReport", "rds:DeletePerformanceAnalysisReport", "rds:GetPerformanceAnalysisReport" ], "Resource":"*" } ] } ``` **Example: VPC endpoint policy that denies all access from a specified AWS account** The following VPC endpoint policy denies AWS account `123456789012` all access to resources using the endpoint. The policy allows all actions from other accounts. ``` { "Statement": [ { "Action": "*", "Effect": "Allow", "Resource": "*", "Principal": "*" }, { "Action": "*", "Effect": "Deny", "Resource": "*", "Principal": { "AWS": [ "123456789012" ] } } ] } ``` ## IP addressing for Performance Insights IP addresses enable resources in your VPC to communicate with each other, and with resources over the internet. Performance Insights supports both IPv4 and IPv6 addressing protocols. By default, Performance Insights and Amazon VPC use the IPv4 addressing protocol. You can't turn off this behavior. When you create a VPC, make sure to specify an IPv4 CIDR block (a range of private IPv4 addresses). You can optionally assign an IPv6 CIDR block to your VPC and subnets, and assign IPv6 addresses from that block to RDS resources in your subnet. Support for the IPv6 protocol expands the number of supported IP addresses. By using the IPv6 protocol, you ensure that you have sufficient available addresses for the future growth of the internet. New and existing RDS resources can use IPv4 and IPv6 addresses within your VPC. Configuring, securing, and translating network traffic between the two protocols used in different parts of an application can cause operational overhead. You can standardize on the IPv6 protocol for Amazon RDS resources to simplify your network configuration. For more information about service endpoints and quotas, see [Amazon Relational Database Service endpoints and quotas](https://docs.aws.amazon.com/general/latest/gr/rds-service.html). For more information about Amazon RDS IP addressing, see [Amazon RDS IP addressing](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html#USER_VPC.IP_addressing).