Open menu
-->

Enable AWS RDS Performance Insights

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
Performance
efficiency
Operational
excellence
Reliability

Risk level: Low (generally tolerable level of risk)

Ensure that your AWS RDS MySQL and PostgreSQL database instances have 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. AWS RDS 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 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), AWS RDS MySQL and AWS RDS PostgreSQL.

AWS Relational Database Service (RDS) 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 those databases.

Audit

To determine if your Amazon RDS database instances are using Performance Insights feature, perform the following:

Using AWS Console

01 Sign in to AWS Management Console.

02 Navigate to RDS dashboard at https://console.aws.amazon.com/rds/.

03 In the left navigation panel, under Amazon RDS, click Instances.

04 Select the RDS database instance that you want to examine and click on the resource name (link) available in the DB instance column. The selected instance must have the database engine, available in the Engine column, set to MySQL, Aurora MySQL or PostgreSQL.

05 Within Details panel section, in the Performance Insights category, check the Performance Insights enabled configuration attribute value. If the attribute value is set to No, the Performance Insights feature is not enabled for the selected Amazon RDS database instance.

06 Repeat step no. 4 and 5 to determine the Performance Insights feature status for other AWS RDS instances created in the selected region.

07 Change the AWS region from the navigation bar and repeat the process for other regions.

Using AWS CLI

01 Run describe-db-instances command (OSX/Linux/UNIX) using custom query filters to list the identifiers of all RDS 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` || Engine==`aurora-mysql` || Engine==`aurora-postgresql` || Engine==`postgres`].DBInstanceIdentifier | []'

02 The command output should return a table with the requested RDS identifiers (names):

---------------------------
|   DescribeDBInstances   |
+-------------------------+
|  cc-mysql-upgraded-db   |
|  cc-postgresql-db       |
|  cc-aurora-mysql-db     |
+-------------------------+

03 Run describe-db-instances command (OSX/Linux/UNIX) using the name of the database instance that you want to examine as identifier and custom query filters to describe Performance Insights feature status for the selected Amazon RDS resource:

aws rds describe-db-instances
	--region us-east-1
	--db-instance-identifier cc-mysql-upgraded-db
	--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 IAM Database Authentication feature is not enabled for the selected AWS RDS database instance.

05 Repeat step no. 3 and 4 to check the Performance Insights feature status for other AWS RDS database instances available within the selected region.

06 Change the AWS region by updating the --region command parameter value and repeat steps no. 1 – 5 to perform the audit process for other regions.

Remediation / Resolution

To enable Amazon RDS Performance Insights for your existing Aurora, MySQL and PostgreSQL-based database instances, perform the following actions:

Using AWS Console

01 Sign in to AWS Management Console.

02 Navigate to RDS dashboard at https://console.aws.amazon.com/rds/.

03 In the left navigation panel, under Amazon RDS, click Instances.

04 Select the RDS database instance that you want to reconfigure (see Audit section part I to identify the right RDS resource).

05 Click the Instance Actions button from the dashboard top menu and select Modify.

06 On the Modify DB Instance: <instance-identifier> page, within Performance Insights section, perform the following actions:

  1. Select Enable Performance Insights option to activate Performance Insights feature for the selected Aurora/MySQL/PostgreSQL database instance.
  2. From Retention period dropdown list, select the amount of time to retain Performance Insights data based on your needs.
  3. From Master key dropdown list, select the AWS KMS master key that you want to use for encrypting potentially sensitive Performance Insights data. The data is encrypted in flight and at rest.

07 Click Continue to continue the reconfiguration process.

08 In the Summary of modifications section, review the configuration changes that you want to apply to your database instance.

09 Within Scheduling of modifications section, perform one of the following actions based on your application availability requirements:

  1. Select Apply during the next scheduled maintenance window to apply the changes automatically during the next scheduled maintenance window.
  2. 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 setting for this 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 application.

10 Click Modify DB Instance to save your configuration changes.

11 Repeat steps no. 4 – 10 to enable Performance Insights for other Amazon RDS database instances available in the current region.

12 Change the AWS region from the navigation bar and repeat the process for other regions.

Using AWS CLI

01 Run modify-db-instance command (OSX/Linux/UNIX) to enable Amazon RDS Performance Insights feature for the selected database instance (see Audit section part II to identify the right RDS resource). The following command example make use of --apply-immediately parameter to apply the configuration changes asynchronously, 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 application. If you add --no-apply-immediately parameter to the command request, Amazon RDS service will apply your changes during the next maintenance window:

aws rds modify-db-instance
	--region us-east-1
	--db-instance-identifier cc-mysql-upgraded-db
	--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 AWS RDS database instance:

{
    "DBInstance": {
        "PubliclyAccessible": true,
        "Engine": "mysql",
        "MultiAZ": false,
        "LatestRestorableTime": "2018-10-16T10:12:36Z",
        "EngineVersion": "5.7.23",
        "IAMDatabaseAuthenticationEnabled": true,
        "PerformanceInsightsEnabled": true,
        "PerformanceInsightsRetentionPeriod": 7,
        "PerformanceInsightsKMSKeyId": "arn:aws:kms:us-east-1:123456789012:key/abcdabcd-1234-1234-1234-abcdabcdabc",

        ...

        "AutoMinorVersionUpgrade": true,
        "AllocatedStorage": 130,
        "BackupRetentionPeriod": 7,
        "DBInstanceStatus": "available",
        "DeletionProtection": true,
        "AvailabilityZone": "us-east-1c",
        "CACertificateIdentifier": "rds-ca-2015",
        "DBInstanceClass": "db.m5.large",
        "DBInstanceIdentifier": "cc-mysql-upgraded-db"
    }
}

03 Repeat step no. 1 and 2 to enable Performance Insights feature for other AWS RDS database instances available within the current region.

04 Change the AWS region by updating the --region command parameter value and repeat the remediation process for other regions.

References

Publication date Oct 29, 2018