- Knowledge Base
- Amazon Web Services
- Amazon Relational Database Service
- Performance Insights
Ensure that your Amazon RDS MySQL and PostgreSQL database instances have the Performance Insights feature enabled in order to allow you to obtain a better overview of your databases performance as well as help you to identify potential performance issues. Performance Insights is a performance monitoring tool that helps you to evaluate the load on your MySQL/PostgreSQL databases and determine when and where to take action. The feature allows you to detect performance bottlenecks with an easy-to-understand dashboard that visualizes database load in real time. For example, with Performance Insights feature enabled, when the load of your database is high, you can easily determine the type of bottleneck such as high CPU consumption, lock waits or I/O latency, and see which SQL queries are creating the bottleneck. Performance Insights is currently available for the following database engines: Amazon Aurora (MySQL and PostgreSQL-compatible editions), RDS MySQL, and RDS PostgreSQL.
This rule can help you work with the AWS Well-Architected Framework.
This rule resolution is part of the Conformity Security & Compliance tool for AWS.
efficiency
excellence
The Performance Insights feature provides you instant visibility into the nature of the workloads on your Amazon RDS databases and helps you find the cause of any performance issue found on your databases.
Audit
To determine if your Amazon RDS database instances are using the Performance Insights feature, perform the following operations:
Using AWS Console
01 Sign in to the AWS Management Console.
02 Navigate to Amazon RDS console at https://console.aws.amazon.com/rds/.
03 In the navigation panel, under Amazon RDS, choose Databases.
04 Click on the name (link) of the RDS database instance that you want to examine. The selected instance must have the appropriate database engine listed in the Engine column (i.e. MySQL Community, PostgreSQL, Aurora MySQL or Aurora PostgreSQL).
05 Select the Configuration tab and check the Performance Insights enabled attribute value. If thePerformance Insights enabled value is set to No, the Performance Insights feature is not enabled for the selected Amazon RDS database instance.
06 Repeat steps no. 4 and 5 for each Amazon RDS database instance available within the current AWS region.
07 Change the AWS cloud region from the navigation bar and repeat the Audit process for other regions.
Using AWS CLI
01 Run describe-db-instances command (OSX/Linux/UNIX) with custom query filters to list the names of the Aurora (MySQL and PostgreSQL-compatible), MySQL, and PostgreSQL database instances available in the selected AWS region:
aws rds describe-db-instances --region us-east-1 --output table --query 'DBInstances[?Engine==`mysql` || Engine==`aurora-mysql` || Engine==`aurora-postgresql` || Engine==`postgres`].DBInstanceIdentifier | []'
02 The command output should return a table with the requested database instance names:
--------------------------------- | DescribeDBInstances | +-------------------------------+ | cc-project5-mysql-database | | cc-aurora-postgres-database | +-------------------------------+
03 Run describe-db-instances command (OSX/Linux/UNIX) using the name of the Amazon RDS database instance that you want to examine as the identifier parameter and custom query filters to describe the Performance Insights feature status for the selected instance:
aws rds describe-db-instances --region us-east-1 --db-instance-identifier cc-project5-mysql-database --query 'DBInstances[*].PerformanceInsightsEnabled'
04 The command output should return the feature status (true for enabled, false for disabled):
[ false ]
If the describe-db-instances command output returns false, as shown in the output example above, the Performance Insights feature is not enabled for the selected Amazon RDS database instance.
05 Repeat steps no. 3 and 4 for each Amazon RDS database instance available in the selected AWS region.
06 Change the AWS cloud region by updating the --region command parameter value and repeat the Audit process for other regions.
Remediation / Resolution
To enable the Performance Insights feature for your Aurora, MySQL, and PostgreSQL database instances, perform the following operations:
Using AWS CloudFormation
01 CloudFormation template (JSON):
{ "AWSTemplateFormatVersion": "2010-09-09", "Description": "Enable and Configure the Performance Insights Feature", "Parameters": { "DBInstanceName": { "Default": "mysql-database-instance", "Description": "RDS database instance name", "Type": "String", "MinLength": "1", "MaxLength": "63", "AllowedPattern": "^[0-9a-zA-Z-/]*$", "ConstraintDescription": "Must begin with a letter and must not end with a hyphen or contain two consecutive hyphens." }, "DBInstanceClass": { "Default": "db.m4.large", "Description": "DB instance class/type", "Type": "String", "ConstraintDescription": "Must provide a valid DB instance type." }, "DBAllocatedStorage": { "Default": "20", "Description": "The size of the database (GiB)", "Type": "Number", "MinValue": "20", "MaxValue": "65536", "ConstraintDescription": "Must be between 20 and 65536 GiB." }, "DBName": { "Default": "mysqldb", "Description": "Database name", "Type": "String", "MinLength": "1", "MaxLength": "64", "AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*", "ConstraintDescription": "Must begin with a letter and contain only alphanumeric characters." }, "DBUsername": { "Description": "Master username for database access", "Type": "String", "MinLength": "1", "MaxLength": "16", "AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*", "ConstraintDescription": "Must begin with a letter and contain only alphanumeric characters." }, "DBPassword": { "NoEcho": "true", "Description": "Password for database access", "Type": "String", "MinLength": "8", "MaxLength": "41", "AllowedPattern": "[a-zA-Z0-9]*", "ConstraintDescription": "Must contain only alphanumeric characters." } }, "Resources": { "RDSInstance": { "Type": "AWS::RDS::DBInstance", "Properties": { "DBInstanceIdentifier": { "Ref": "DBInstanceName" }, "DBName": { "Ref": "DBName" }, "MasterUsername": { "Ref": "DBUsername" }, "MasterUserPassword": { "Ref": "DBPassword" }, "DBInstanceClass": { "Ref": "DBInstanceClass" }, "AllocatedStorage": { "Ref": "DBAllocatedStorage" }, "Engine": "MySQL", "EngineVersion": "5.7.36", "EnablePerformanceInsights": true, "PerformanceInsightsRetentionPeriod": 7, "PerformanceInsightsKMSKeyId": "arn:aws:kms:us-east-1:123456789012:key/abcdabcd-1234-1234-1234-abcdabcdabcd" } } } }
02 CloudFormation template (YAML):
AWSTemplateFormatVersion: '2010-09-09' Description: Enable and Configure the Performance Insights Feature Parameters: DBInstanceName: Default: mysql-database-instance Description: RDS database instance name Type: String MinLength: '1' MaxLength: '63' AllowedPattern: ^[0-9a-zA-Z-/]*$ ConstraintDescription: Must begin with a letter and must not end with a hyphen or contain two consecutive hyphens. DBInstanceClass: Default: db.m4.large Description: DB instance class/type Type: String ConstraintDescription: Must provide a valid DB instance type. DBAllocatedStorage: Default: '20' Description: The size of the database (GiB) Type: Number MinValue: '20' MaxValue: '65536' ConstraintDescription: Must be between 20 and 65536 GiB. DBName: Default: mysqldb Description: Database name Type: String MinLength: '1' MaxLength: '64' AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*' ConstraintDescription: Must begin with a letter and contain only alphanumeric characters. DBUsername: Description: Master username for database access Type: String MinLength: '1' MaxLength: '16' AllowedPattern: '[a-zA-Z][a-zA-Z0-9]*' ConstraintDescription: Must begin with a letter and contain only alphanumeric characters. DBPassword: NoEcho: 'true' Description: Password for database access Type: String MinLength: '8' MaxLength: '41' AllowedPattern: '[a-zA-Z0-9]*' ConstraintDescription: Must contain only alphanumeric characters. Resources: RDSInstance: Type: AWS::RDS::DBInstance Properties: DBInstanceIdentifier: !Ref 'DBInstanceName' DBName: !Ref 'DBName' MasterUsername: !Ref 'DBUsername' MasterUserPassword: !Ref 'DBPassword' DBInstanceClass: !Ref 'DBInstanceClass' AllocatedStorage: !Ref 'DBAllocatedStorage' Engine: MySQL EngineVersion: 5.7.36 EnablePerformanceInsights: true PerformanceInsightsRetentionPeriod: 7 PerformanceInsightsKMSKeyId: arn:aws:kms:us-east-1:123456789012:key/abcdabcd-1234-1234-1234-abcdabcdabcd
Using Terraform
01 Terraform configuration file (.tf):
terraform { required_providers { aws = { source = "hashicorp/aws" version = "~> 3.27" } } required_version = ">= 0.14.9" } provider "aws" { profile = "default" region = "us-east-1" } resource "aws_db_instance" "rds-database-instance" { allocated_storage = 20 engine = "mysql" engine_version = "5.7" instance_class = "db.m4.large" name = "mysqldb" username = "ccmysqluser01" password = "ccmysqluserpwd" parameter_group_name = "default.mysql5.7" # Enable and Configure the Performance Insights Feature performance_insights_enabled = true performance_insights_retention_period = 7 performance_insights_kms_key_id = "arn:aws:kms:us-east-1:123456789012:key/abcdabcd-1234-1234-1234-abcdabcdabcd" apply_immediately = true }
Using AWS Console
01 Sign in to the AWS Management Console.
02 Navigate to Amazon RDS console at https://console.aws.amazon.com/rds/.
03 In the navigation panel, under Amazon RDS, choose Databases.
04 Select the Amazon RDS database instance that you want to reconfigure and choose Modify.
05 On the Modify DB instance: <instance-name>
configuration page, perform the following actions:
- In the Additional configuration section, under Performance Insights, select Enable Performance Insights to enable the Performance Insights feature for the selected Aurora/MySQL/PostgreSQL database instance.
- Select the amount of time to retain Performance Insights data from the Retention period dropdown list.
- Select the Amazon KMS master key that you want to use to encrypt potentially sensitive Performance Insights data from the Master key dropdown list. The recorded data is encrypted in transit and at rest.
- Choose Continue and review the configuration changes that you want to apply, available in the Summary of modifications section.
- In the Scheduling of modifications section, perform one of the following actions based on your workload requirements:
- Select Apply during the next scheduled maintenance window to apply the changes automatically during the next scheduled maintenance window.
- Select Apply immediately to apply the changes right away. With this option any pending modifications will be asynchronously applied as soon as possible, regardless of the maintenance window configured for the selected Amazon RDS database instance. Note that any changes available in the pending modifications queue are also applied. If any of the pending modifications require downtime, choosing this option can cause unexpected downtime for your database application.
- Choose Modify DB instance to apply the configuration changes.
06 Repeat steps no. 4 and 5 for each Amazon RDS database instance available in the selected AWS region.
07 Change the AWS cloud region from the navigation bar and repeat the Remediation process for other regions.
Using AWS CLI
01 Run modify-db-instance command (OSX/Linux/UNIX) to enable the Performance Insights feature for the selected Amazon database instance (Aurora, MySQL, or PostgreSQL database). Use the --performance-insights-retention-period command parameter to set the appropriate retention period and the --performance-insights-kms-key-id parameter to specify the KMS master key that you can use to encrypt sensitive Performance Insights data. The following command request example makes use of --apply-immediately parameter to apply the configuration changes asynchronously and as soon as possible. Any changes available in the pending modifications queue are also applied with this request. If any of the pending modifications require downtime, choosing this option can cause unexpected downtime for your database application. If you skip adding the --apply-immediately parameter to the command request, Amazon RDS will apply your changes during the next maintenance window:
aws rds modify-db-instance --region us-east-1 --db-instance-identifier cc-project5-mysql-database --enable-iam-database-authentication --enable-performance-insights --performance-insights-retention-period 7 --performance-insights-kms-key-id arn:aws:kms:us-east-1:123456789012:key/abcdabcd-1234-1234-1234-abcdabcdabcd --apply-immediately
02 The command output should return the configuration metadata for the modified database instance:
{ "DBInstance": { "PubliclyAccessible": true, "MasterUsername": "ccadmin", "MonitoringInterval": 0, "LicenseModel": "general-public-license", "VpcSecurityGroups": [ { "Status": "active", "VpcSecurityGroupId": "sg-0abcd1234abcd1234" }, { "Status": "active", "VpcSecurityGroupId": "sg-abcd1234" } ], "InstanceCreateTime": "2021-05-12T08:00:00.677Z", "CopyTagsToSnapshot": true, "OptionGroupMemberships": [ { "Status": "in-sync", "OptionGroupName": "default:mysql-5-7" } ], "Engine": "mysql", "MultiAZ": false, "DBSecurityGroups": [], "DBParameterGroups": [ { "DBParameterGroupName": "default.mysql5.7", "ParameterApplyStatus": "in-sync" } ], "PerformanceInsightsEnabled": true, "PerformanceInsightsRetentionPeriod": 7, "PerformanceInsightsKMSKeyId": "arn:aws:kms:us-east-1:123456789012:key/abcdabcd-1234-1234-1234-abcdabcdabc", "AutoMinorVersionUpgrade": true, "PreferredBackupWindow": "06:02-06:32", "DBSubnetGroup": { "Subnets": [ { "SubnetStatus": "Active", "SubnetIdentifier": "subnet-abcd1234", "SubnetOutpost": {}, "SubnetAvailabilityZone": { "Name": "us-east-1d" } }, { "SubnetStatus": "Active", "SubnetIdentifier": "subnet-1234abcd", "SubnetOutpost": {}, "SubnetAvailabilityZone": { "Name": "us-east-1e" } }, { "SubnetStatus": "Active", "SubnetIdentifier": "subnet-abcdabcd", "SubnetOutpost": {}, "SubnetAvailabilityZone": { "Name": "us-east-1b" } }, { "SubnetStatus": "Active", "SubnetIdentifier": "subnet-12341234", "SubnetOutpost": {}, "SubnetAvailabilityZone": { "Name": "us-east-1a" } }, { "SubnetStatus": "Active", "SubnetIdentifier": "subnet-abcd1234", "SubnetOutpost": {}, "SubnetAvailabilityZone": { "Name": "us-east-1f" } }, { "SubnetStatus": "Active", "SubnetIdentifier": "subnet-1234abcd", "SubnetOutpost": {}, "SubnetAvailabilityZone": { "Name": "us-east-1c" } } ], "DBSubnetGroupName": "default-vpc-abcdabcd", "VpcId": "vpc-abcdabcd", "DBSubnetGroupDescription": "Created from the AWS Management Console", "SubnetGroupStatus": "Complete" }, "ReadReplicaDBInstanceIdentifiers": [], "AllocatedStorage": 70, "DBInstanceArn": "arn:aws:rds:us-east-1:123456789012:db:cc-project5-mysql-database", "BackupRetentionPeriod": 7, "PreferredMaintenanceWindow": "thu:03:27-thu:03:57", "Endpoint": { "HostedZoneId": "ABCDABCDABCD", "Port": 3306, "Address": "cc-project5-mysql-database.abcdabcdabcd.us-east-1.rds.amazonaws.com" }, "DBInstanceStatus": "available", "IAMDatabaseAuthenticationEnabled": true, "EngineVersion": "5.7.30", "DeletionProtection": true, "AvailabilityZone": "us-east-1a", "DomainMemberships": [], "StorageType": "gp2", "DbiResourceId": "db-ABCDABCDABCDABCDABCDABCDAB", "CACertificateIdentifier": "rds-ca-2019", "StorageEncrypted": true, "AssociatedRoles": [], "DBInstanceClass": "db.t3.medium", "DbInstancePort": 0, "DBInstanceIdentifier": "cc-project5-mysql-database" } }
03 Repeat steps no. 1 and 2 for each Amazon RDS database instance available in the selected AWS region.
04 Change the AWS cloud region by updating the --region command parameter value and repeat the Remediation process for other regions.
References
- AWS Documentation
- Amazon RDS FAQs
- Performance Insights
- Enabling Performance Insights
- Modifying an Amazon RDS DB Instance and Using the Apply Immediately Parameter
- AWS Command Line Interface (CLI) Documentation
- rds
- describe-db-instances
- modify-db-instance
- CloudFormation Documentation
- Amazon Relational Database Service resource type reference
- Terraform Documentation
- AWS Provider
Related RDS rules
- Instance Level Events Subscriptions (Reliability, performance-efficiency, operational-excellence)
- Enable Serverless Log Exports (Security, reliability, performance-efficiency, operational-excellence)
- RDS Event Notifications (Security, reliability, performance-efficiency, cost-optimisation)
- Aurora Database Instance Accessibility (Reliability)