# Monitoring Amazon Aurora databases with CloudWatch Database Insights Monitor the database load (DB Load) for your fleet of Amazon Aurora DB instances with Database Insights. DB Load measures the level of session activity in your database. You can use Database Insights to analyze and troubleshoot the performance of your Amazon Aurora databases at scale. With Database Insights, you can visualize the DB Load on your fleet of databases and filter the load by waits, SQL statements, hosts, or users. By default, RDS enables the Standard mode of Database Insights for your Amazon Aurora databases. When you turn on the Advanced mode of Database Insights for a DB cluster, RDS enables Database Insights for every DB instance in the cluster. For information about using Database Insights in the Amazon CloudWatch console, see [CloudWatch Database Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/Database-Insights.html) in the *Amazon CloudWatch User Guide*. ## Pricing For information about pricing, see [Amazon CloudWatch Pricing](https://aws.amazon.com/cloudwatch/pricing/). **Topics** + [Pricing](#USER_Database-Insights-pricing) + [Amazon Aurora DB engine, Region, and instance class support for Database Insights](USER_DatabaseInsights.Engines.md) + [Turning on the Advanced mode of Database Insights for Amazon Aurora](USER_DatabaseInsights.TurningOnAdvanced.md) + [Turning on the Standard mode of Database Insights for Amazon Aurora](USER_DatabaseInsights.TurningOnStandard.md) + [Configuring your database to monitor slow SQL queries with Database Insights for Amazon Aurora](USER_DatabaseInsights.SlowSQL.md) + [Considerations for Database Insights for Amazon Aurora](USER_DatabaseInsights.Considerations.md) # Amazon Aurora DB engine, Region, and instance class support for Database Insights The following table provides Amazon Aurora DB engines that support Database Insights. | Amazon Aurora DB engine | Supported engine versions and Regions | Instance class restrictions | | --- | --- | --- | | Amazon Aurora MySQL-Compatible Edition | For more information on version and Region availability of Database Insights with Aurora MySQL, see [Performance Insights with Aurora MySQL](Concepts.Aurora_Fea_Regions_DB-eng.Feature.PerfInsights.md#Concepts.Aurora_Fea_Regions_DB-eng.Feature.PerfInsights.amy). | Database Insights has the following engine class restrictions: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_DatabaseInsights.Engines.html) | | Amazon Aurora PostgreSQL-Compatible Edition | For more information on version and Region availability of Database Insights with Aurora PostgreSQL, see [Performance Insights with Aurora PostgreSQL](Concepts.Aurora_Fea_Regions_DB-eng.Feature.PerfInsights.md#Concepts.Aurora_Fea_Regions_DB-eng.Feature.PerfInsights.apg). | Not applicable | | Aurora PostgreSQL Limitless Database | For more information about using Database Insights with Aurora PostgreSQL Limitless Database, see [Monitoring Aurora PostgreSQL Limitless Database with CloudWatch Database Insights](limitless-monitoring.cwdbi.md). | Not applicable | Database Insights supports Amazon Aurora Serverless v2. ## Amazon Aurora DB engine, Region, and instance class support for Database Insights features The following table provides Amazon Aurora DB engines that support Database Insights features. | Feature | [Pricing tier](https://aws.amazon.com/rds/performance-insights/pricing/) | [Supported regions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Concepts.RegionsAndAvailabilityZones.html#Concepts.RegionsAndAvailabilityZones.Regions) | Supported DB engines | [Supported instance classes](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Concepts.DBInstanceClass.html#Concepts.DBInstanceClass.Types) | | --- | --- | --- | --- | --- | | [SQL statistics for Performance Insights](sql-statistics.md) | All | All | All | All | | [Analyzing database performance for a period of time](USER_PerfInsights.UsingDashboard.AnalyzePerformanceTimePeriod.md) | Paid tier only | All | All | All except db.serverless (Aurora Serverless v2) | | [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/AuroraUserGuide/USER_DatabaseInsights.Engines.html) | All | All except db.serverless (Aurora Serverless v2) | ## Amazon Aurora Region support for Database Insights Aurora supports Database Insights in the following AWS Regions. + US East (N. Virginia) + US East (Ohio) + US West (N. California) + US West (Oregon) + Africa (Cape Town) + Asia Pacific (Hong Kong) + Asia Pacific (Hyderabad) + Asia Pacific (Jakarta) + Asia Pacific (Malaysia) + Asia Pacific (Melbourne) + Asia Pacific (Mumbai) + Asia Pacific (Osaka) + Asia Pacific (Seoul) + Asia Pacific (Singapore) + Asia Pacific (Sydney) + Asia Pacific (Tokyo) + Canada (Central) + Canada West (Calgary) + Europe (Frankfurt) + Europe (Ireland) + Europe (London) + Europe (Milan) + Europe (Paris) + Europe (Spain) + Europe (Stockholm) + Europe (Zurich) + Israel (Tel Aviv) + Middle East (Bahrain) + Middle East (UAE) + South America (São Paulo) + AWS GovCloud (US-East) + AWS GovCloud (US-West) # Turning on the Advanced mode of Database Insights for Amazon Aurora To turn on the Advanced mode of Database Insights for Amazon Aurora, use the following procedures. ## Turning on the Advanced mode of Database Insights when creating a DB cluster Turn on the Advanced mode of Database Insights when creating a database for Amazon Aurora. ------ #### [ Console ] In the console, you can turn on the Advanced mode of Database Insights when you create a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Advanced mode of Database Insights when creating a 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 **Create database**. 1. In the **Database Insights** section, select **Advanced mode**. Then, choose the following options: + **Retention** – The amount of time to retain Performance Insights data. The retention period must be 15 months for the Advanced mode of Database Insights. + **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 Aurora resources](Overview.Encryption.md). 1. Choose **Create database**. ------ #### [ AWS CLI ] To turn on the Advanced mode of Database Insights when creating a DB cluster, call the [create-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster.html) AWS CLI command and supply the following values: + `--database-insights-mode advanced` to turn on the Advanced mode of Database Insights. + `--engine` – The database engine for the DB cluster. + `--db-cluster-identifier` – The identifier for the DB cluster. + `--enable-performance-insights` to turn on Performance Insights for Database Insights. + `--performance-insights-retention-period` – The retention period for data for your DB cluster. To turn on Database Insights, the retention period must be at least 465 days. The following example enables the Advanced mode of Database Insights when creating a DB cluster. For Linux, macOS, or Unix: ``` aws rds create-db-cluster \ --database-insights-mode advanced \ --engine aurora-postgresql \ --db-cluster-identifier sample-db-identifier \ --enable-performance-insights \ --performance-insights-retention-period 465 ``` For Windows: ``` aws rds create-db-cluster ^ --database-insights-mode advanced ^ --engine aurora-postgresql ^ --db-cluster-identifier sample-db-identifier ^ --enable-performance-insights ^ --performance-insights-retention-period 465 ``` ------ #### [ RDS API ] To turn on the Advanced mode of Database Insights when you create a DB cluster, specify the following parameters for your [CreateDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `advanced` + `EnablePerformanceInsights` to `True` + `PerformanceInsightsRetentionPeriod` to at least 465 days ------ ## Turning on the Advanced mode of Database Insights when modifying a DB cluster Turn on Database Insights when modifying a database for Amazon Aurora. Modifying a DB cluster to enable the Advanced mode of Database Insights doesn't cause downtime. **Note** To enable Database Insights, each DB instance in a DB cluster must have the same Performance Insights and Enhanced Monitoring settings. ------ #### [ Console ] In the console, you can turn on the Advanced mode of Database Insights when you modify a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Advanced mode of Database Insights when modifying a 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 cluster, and choose **Modify**. 1. In the **Database Insights** section, select **Advanced mode**. Then, choose the following options: + **Retention** – The amount of time to retain Performance Insights data. The retention period must be 15 months for the Advanced mode of Database Insights. + **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 Aurora 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 database ignores this setting and turns on the Advanced mode of Database Insights immediately. 1. Choose **Modify cluster**. ------ #### [ AWS CLI ] To turn on the Advanced mode of Database Insights when modifying a DB cluster, call the [modify-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html) AWS CLI command and supply the following values: + `--database-insights-mode advanced` to turn on the Advanced mode of Database Insights. + `--db-cluster-identifier` – The identifier for the DB cluster. + `--enable-performance-insights` to turn on Performance Insights for Database Insights. + `--performance-insights-retention-period` – The retention period for data for your DB cluster. To turn on the Advanced mode of Database Insights, the retention period must be at least 465 days. The following example enables the Advanced mode of Database Insights when modifying a DB cluster. For Linux, macOS, or Unix: ``` aws rds modify-db-cluster \ --database-insights-mode advanced \ --db-cluster-identifier sample-db-identifier \ --enable-performance-insights \ --performance-insights-retention-period 465 ``` For Windows: ``` aws rds modify-db-cluster ^ --database-insights-mode advanced ^ --db-cluster-identifier sample-db-identifier ^ --enable-performance-insights ^ --performance-insights-retention-period 465 ``` ------ #### [ RDS API ] To turn on the Advanced mode of Database Insights when you modify a DB cluster, specify the following parameters for your [ModifyDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `advanced` + `EnablePerformanceInsights` to `True` + `PerformanceInsightsRetentionPeriod` to at least 465 days ------ # Turning on the Standard mode of Database Insights for Amazon Aurora To turn on the Standard mode of Database Insights for Amazon Aurora, use the following procedures. ## Turning on the Standard mode of Database Insights when creating a DB cluster Turn on the Standard mode of Database Insights when creating a database for Amazon Aurora. ------ #### [ Console ] In the console, you can turn on the Standard mode of Database Insights when you create a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Standard mode of Database Insights when creating a 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 **Create database**. 1. In the **Database Insights** section, select **Standard mode**. Then, choose from the following options to turn Performance Insights on or off: + To turn off Performance Insights, deselect **Enable Performance Insights**. + To turn on Performance Insights, select **Enable Performance Insights**. To configure Performance Insights, specify the following options: + **Retention** – The amount of time to retain Performance Insights data. The retention period must be at least 7 days. + **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 Aurora resources](Overview.Encryption.md). 1. Choose **Create database**. ------ #### [ AWS CLI ] To turn on the Standard mode of Database Insights when creating a DB cluster, call the [create-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-cluster.html) AWS CLI command and supply the following values: + `--database-insights-mode standard` to turn on the Standard mode of Database Insights. + `--engine` – The database engine for the DB cluster. + `--db-cluster-identifier` – The identifier for the DB cluster. + `--enable-performance-insights` or `--no-enable-performance-insights` to turn Performance Insights on or off. If you specify `--enable-performance-insights`, you must also specify the `--performance-insights-retention-period` – The retention period for data for your DB cluster. The retention period must be at least 7 days. The following example enables the Standard mode of Database Insights and Performance Insights when creating a DB cluster. For Linux, macOS, or Unix: ``` aws rds create-db-cluster \ --database-insights-mode standard \ --engine aurora-postgresql \ --db-cluster-identifier sample-db-identifier \ --enable-performance-insights \ --performance-insights-retention-period 7 ``` For Windows: ``` aws rds create-db-cluster ^ --database-insights-mode standard ^ --engine aurora-postgresql ^ --db-cluster-identifier sample-db-identifier ^ --enable-performance-insights ^ --performance-insights-retention-period 7 ``` The following example enables the Standard mode of Database Insights and disables Performance Insights when creating a DB cluster. For Linux, macOS, or Unix: ``` aws rds create-db-cluster \ --database-insights-mode standard \ --engine aurora-postgresql \ --db-cluster-identifier sample-db-identifier \ --no-enable-performance-insights ``` For Windows: ``` aws rds create-db-cluster ^ --database-insights-mode standard ^ --engine aurora-postgresql ^ --db-cluster-identifier sample-db-identifier ^ --no-enable-performance-insights ``` ------ #### [ RDS API ] To turn on the Standard mode of Database Insights when you create a DB cluster, specify the following parameters for your [CreateDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `standard` + `EnablePerformanceInsights` to `True` or `False`. If you set `EnablePerformanceInsights` to `True`, you must set `PerformanceInsightsRetentionPeriod` to at least 7 days. ------ ## Turning on the Standard mode of Database Insights when modifying a DB cluster Turn on Standard mode of Database Insights when modifying a database for Amazon Aurora. Modifying a DB cluster to enable the Standard mode of Database Insights doesn't cause downtime. **Note** To enable Database Insights, each DB instance in a DB cluster must have the same Performance Insights and Enhanced Monitoring settings. ------ #### [ Console ] In the console, you can turn on the Standard mode of Database Insights when you modify a DB cluster. Settings for Database Insights apply to all DB instances in your DB cluster. **To turn on the Standard mode of Database Insights when modifying a 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 cluster, and choose **Modify**. 1. In the **Database Insights** section, select **Standard mode**. Then, choose from the following options: + To turn off Performance Insights, deselect **Enable Performance Insights**. + To turn on Performance Insights, select **Enable Performance Insights**. To configure Performance Insights, specify the following options: + **Retention** – The amount of time to retain Performance Insights data. The retention period must be at least 7 days. + **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 Aurora 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 database ignores this setting and turns on the Standard mode of Database Insights immediately. 1. Choose **Modify cluster**. ------ #### [ AWS CLI ] To turn on the Standard mode of Database Insights when modifying a DB cluster, call the [modify-db-cluster](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster.html) AWS CLI command and supply the following values: + `--database-insights-mode standard` to turn on the Standard mode of Database Insights. + `--db-cluster-identifier` – The identifier for the DB cluster. + `--enable-performance-insights` or `--no-enable-performance-insights` to turn Performance Insights on or off. If you specify `--enable-performance-insights`, you must also specify the `--performance-insights-retention-period` – The retention period for data for your DB cluster. The retention period must be at least 7 days. The following example enables the Standard mode of Database Insights and enables Performance Insights when modifying a DB cluster. For Linux, macOS, or Unix: ``` aws rds modify-db-cluster \ --database-insights-mode standard \ --db-cluster-identifier sample-db-identifier \ --enable-performance-insights \ --performance-insights-retention-period 7 ``` For Windows: ``` aws rds modify-db-cluster ^ --database-insights-mode standard ^ --db-cluster-identifier sample-db-identifier ^ --enable-performance-insights ^ --performance-insights-retention-period 7 ``` The following example enables the Standard mode of Database Insights and disables Performance Insights when modifying a DB cluster. For Linux, macOS, or Unix: ``` aws rds modify-db-cluster \ --database-insights-mode standard \ --db-cluster-identifier sample-db-identifier \ --no-enable-performance-insights ``` For Windows: ``` aws rds modify-db-cluster ^ --database-insights-mode standard ^ --db-cluster-identifier sample-db-identifier ^ --no-enable-performance-insights ``` ------ #### [ RDS API ] To turn on the Standard mode of Database Insights when you modify a DB cluster, specify the following parameters for your [ModifyDBCluster](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBCluster.html) Amazon RDS API operation. + `DatabaseInsightsMode` to `standard` + `EnablePerformanceInsights` to `True` or `False`. If you set `EnablePerformanceInsights` to `True`, you must set `PerformanceInsightsRetentionPeriod` to at least 7 days. ------ # Configuring your database to monitor slow SQL queries with Database Insights for Amazon Aurora To monitor slow SQL queries for your database, you can use the **Slow SQL Queries** section in the Database Insights dashboard. Before configuring your database to monitor slow SQL queries, the **Slow SQL Queries** section is blank. For more information about monitoring slow SQL queries in the Database Insights dashboard, see [Viewing the Database Instance Dashboard for CloudWatch Database Insights](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/Database-Insights-Database-Instance-Dashboard.html) in the *Amazon CloudWatch User Guide*. To configure your database to monitor slow SQL queries with Database Insights, complete the following steps: 1. Enable log exports to CloudWatch Logs. 1. Create or modify the DB cluster parameter group for your DB cluster. For information about configuring log exports, see [Publishing database logs to Amazon CloudWatch Logs](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.html#USER_LogAccess.Procedural.UploadtoCloudWatch) in the *Amazon Aurora User Guide*. To create or modify your DB cluster parameter group, see the following topics. + [Creating a DB cluster parameter groupin Amazon Aurora](USER_WorkingWithParamGroups.CreatingCluster.md) + [Modifying parameters in a DB cluster parameter groupin Amazon Aurora](USER_WorkingWithParamGroups.ModifyingCluster.md) ------ #### [ Amazon Aurora MySQL ] To configure your Amazon Aurora MySQL DB cluster to monitor slow SQL queries, you can use the following parameter combination as an example: + `slow_query_log` – set to `1` + `long_query_time` – set to `1.0` + `log_output` – set to `FILE` This is one possible configuration. For a comprehensive guide to MySQL slow query log parameters and additional configuration options, see the [MySQL documentation for the slow query log](https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html). ------ #### [ Amazon Aurora PostgreSQL ] To configure your Amazon Aurora PostgreSQL DB cluster to monitor slow SQL queries, you can use the following parameter combination as an example. Note that setting these parameters might reduce the performance of your DB cluster. + `log_min_duration_statement` – set to `1000` + `log_statement` – set to `none` + `log_destination` – set to `stderr` This is one possible configuration. For a comprehensive guide to PostgreSQL logging parameters and additional configuration options, see the [PostgreSQL documentation for logging configuration](https://www.postgresql.org/docs/current/runtime-config-logging.html). ------ **Note** For Aurora MySQL, you can configure the parameter `long_query_time` with 1‐microsecond granularity. For example, you can set this parameter to `0.000001`. Depending on the amount of queries on the DB instance, the value of the parameter `long_query_time` can reduce performance. Start with the value `1.0`, and adjust it based on your workload. When you set this parameter to `0`, Database Insights logs all queries. For information about Aurora MySQL and Aurora PostgreSQL logs, see the following. + [AuroraMySQL database log files](USER_LogAccess.Concepts.MySQL.md) + [Aurora PostgreSQL database log files](USER_LogAccess.Concepts.PostgreSQL.md) # Considerations for Database Insights for Amazon Aurora Following are considerations for Database Insights for Amazon Aurora. + You can't manage Database Insights for a DB instance in a DB cluster. + To enable the Advanced mode of Database Insights, you must enable Performance Insights and set the Performance Insights retention period to at least 465 days (15 months). There is no additional cost to set the Performance Insights retention period to 15 months besides the cost of Database Insights. For information about pricing for Database Insights, see [Amazon CloudWatch Pricing](https://aws.amazon.com/cloudwatch/pricing/). + To enable Database Insights, each DB instance in a DB cluster must have the same Performance Insights and Enhanced Monitoring settings. + Modifying a DB cluster to enable either mode of Database Insights doesn't cause downtime.