Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Idle RDS Instance

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: High (not acceptable risk)
Rule ID: RDS-013

Identify any Amazon RDS database instances that appear to be idle and delete them to help lower the cost of your monthly AWS bill. By default, an RDS instance is considered 'idle' when meets the following criteria (to declare the instance 'idle' both conditions must be true):

  • The average number of database connections has been less than 1 for the last 7 days.
  • The total number of database ReadIOPS and WriteIOPS recorded per day for the last 7 days has been less than 20 on average.

The AWS CloudWatch metrics used to detect idle RDS instances are:

  • DatabaseConnections - the number of RDS database connections in use (Units: Count).
  • ReadIOPS and WriteIOPS - the average number of disk I/O (Input/Output) operations per second (Units: Count/Second).

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.

Sustainability
Cost
optimisation

Idle RDS instances represent a good candidate for reducing your monthly AWS costs. Regularly checking your AWS RDS instances for the number of database connections performed will help you efficiently detect and remove any idle RDS resources from your AWS account in order to avoid accumulating unnecessary charges.

Note 1: Backing up your RDS databases before termination is highly recommended because once these instances are deleted, all their automated backups (snapshots) will be permanently lost.
Note 2: Knowing the role and the owner of an AWS RDS instance before you take the decision to remove it from your account is very important. For this rule Cloud Conformity assumes that your RDS instances are tagged with 'Role' and 'Owner' tags which provide visibility into their usage profile and help you decide whether it's safe or not to terminate these resources.
Note 3: You can change the default threshold for this rule on the Cloud Conformity console and set your own values for the number of database connections, and the total number of ReadIOPS and WriteIOPS for each condition in order to configure the instances idleness.
Note 4: If the RDS database instance selected for the checkup is needed within your application stack, you can suppress (disable) the conformity rule check for the instance from the Cloud Conformity console.


Audit

To identify any idle RDS database instances currently available within your AWS account, perform the following:

Using AWS Console

01 Sign in to the AWS Management Console.

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

03 In the left navigation panel, under RDS Dashboard section, choose Instances.

04 Select the RDS instance that you want to examine.

05 Click on Show Monitoring button from the dashboard top menu and select Show Multi-Graph View to expand the AWS CloudWatch monitoring panel.

06 On the monitoring panel displayed for the selected instance, perform the following actions:

  1. Click on the DB Connections (Count) usage graph thumbnail to open the database connections usage details box. Inside the DB Connections (Count) dialog box, set the following parameters:
    • From the Statistic dropdown list, select Average.
    • From the Time Range list, select Last 1 Week.
    • From the Period dropdown list, select 1 Hour.
    • Once the monitoring data is loaded, verify the number of database connections for the last 7 days. If the average usage (count) has been less than 1, e.g. If the average usage (count) has been less than 1, the selected RDS instance qualifies as candidate for the idle instance. Click the x (close) icon to return to the dashboard.
  2. Click on the Read Operations (Count/Second) usage graph thumbnail to open the instance disk ReadIOPS usage details box. Inside the Read Operations (Count/Second) dialog box, set the following parameters:
    • From the Statistic dropdown list, select Sum.
    • From the Time Range list, select Last 1 Week.
    • From the Period dropdown list, select 1 Hour.
    Once the monitoring data is loaded, verify the total number of Read operations per second for the last 7 days. If the total number of ReadIOPS has been less than 20, e.g. If the total number of ReadIOPS has been less than 20, the selected RDS instance qualifies as candidate for the idle instance. Click the x (close) icon to exit.
  3. Click on the Write Operations (Count/Second) usage graph thumbnail to open the instance WriteIOPS usage details box. Inside the Write Operations (Count/Second) dialog box, set the following parameters:
    • From the Statistic dropdown list, select Sum.
    • From the Time Range list, select Last 1 Week.
    • From the Period dropdown list, select 1 Hour.
    Once the monitoring data is loaded, verify the total number of Write operations per second for the last 7 days. If the total number of WriteIOPS has been less than 20, e.g. If the total number of WriteIOPS has been less than 20, the selected RDS instance qualifies as candidate for the idle instance. Click the x (close) icon to exit and return to the RDS dashboard.

07 Now determine the selected database instance role within the stack and its owner by checking the Role and Owner tags values assigned to the RDS instance in order to decide whether it's safe or not to terminate the resource. To check for the necessary tags, perform the following:

  1. Click the Instance Actions button from the dashboard top menu and select See Details.
  2. On the selected RDS instance details page, in the Tags section, verify the requested tags and their values:
    • Check the Role tag value, available in the Value column, or any Role-like tag value that can provide information about the usage profile of the database instance (e.g. legacy-app-db-test-server) in order to decide if the resource can be terminated or not.
    • Check the Owner tag value, available in the Value column, or any Owner-like tag value that can provide the contact information (name, email, phone number) of the resource owner in order to get the confirmation to terminate or not the selected RDS instance.
    • If all conditions outlined at step no. 6 (a, b + c) and 7 are met, the selected RDS instance is considered "idle" and can be terminated in order to stop incurring charges for this resource.

08 Repeat steps no. 4 – 7 to verify the role, owner, DatabaseConnections, ReadIOPS and WriteIOPS metrics usage within the selected time frame for the rest of the RDS instances provisioned in the current region.

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

Using AWS CLI

01 Run describe-instances command (OSX/Linux/UNIX) using custom query filters to list the identifiers of all the existing RDS instances available in the selected region:

aws rds describe-db-instances
	--region us-east-1
	--output table
	--query 'DBInstances[*].DBInstanceIdentifier

02 The command output should return a table with the requested database names:

-------------------------
|  DescribeDBInstances  |
+-----------------------+
|  cc-mysqldb-staging   |
|  cc-mariadb-ver-2.4   |
|  cc-mariadb-ver-1.9   |
+-----------------------+

03 Run get-metric-statistics command (OSX/Linux/UNIX) to get the statistics recorded by AWS CloudWatch for the DatabaseConnections metric, representing the number of RDS database connections in use. Change the --start-time (start recording date) and --end-time (stop recording date) parameters value to choose your own time frame for recording the DatabaseConnections usage. Also, set the --period parameter value to define the granularity - in seconds - of the returned datapoints, based on your requirements. A period can be as short as one minute (60 seconds) or as long as one day (86400 seconds). The following command example returns the average database connections usage of an AWS RDS instance identified by the the name cc-mysql-database, usage data captured during a 7 days period (set by the --start-time and --end-time command parameters), using 1 hour period as the granularity of the returned datapoints (set by the --period parameter):

aws cloudwatch get-metric-statistics
	--region us-east-1
	--metric-name DatabaseConnections
	--start-time 2016-10-04T13:16:00
	--end-time 2016-10-11T13:16:00
	--period 3600
	--namespace AWS/RDS
	--statistics Average
	--dimensions Name=DBInstanceIdentifier,Value=cc-mysql-database

04 The command output should return the DatabaseConnections usage details requested:

{
    "Datapoints": [
        {
            "Timestamp": "2016-10-04T13:16:00Z",
            "Average": 0.0,
            "Unit": "Count"
        },
        {
            "Timestamp": "2016-10-04T14:16:00Z",
            "Average": 0.011,
            "Unit": "Count"
        },
        {
            "Timestamp": "2016-10-04T15:16:00Z",
            "Average": 0.0125,
            "Unit": "Count"
        },

        ...

        {
            "Timestamp": "2016-10-11T10:16:00Z",
            "Average": 0.02283333333333333,
            "Unit": "Count"
        },
        {
            "Timestamp": "2016-10-11T11:16:00Z",
            "Average": 0.01664,
            "Unit": "Count"
        },
        {
            "Timestamp": "2016-10-11T12:16:00Z",
            "Average": 0.0313333333333333,
            "Unit": "Count"
        }
    ],
    "Label": "DatabaseConnections"
}

If the average number of database connections has been less than 1 for the last 7 days, the selected RDS instance qualifies as candidate for the idle instance.

05 Run again get-metric-statistics command (OSX/Linux/UNIX) to get the statistics recorded by AWS CloudWatch for the ReadIOPS metric, representing the number of Read I/O operations per second. The following command example returns the total number of ReadIOPS used by an AWS RDS instance identified by the the name cc-mysql-database, IOPS usage data captured during a 7 days period (set by the --start-time and --end-time command parameters), using 1 hour period as the granularity of the returned datapoints (set by the --period parameter):

aws cloudwatch get-metric-statistics
	--region us-east-1
	--metric-name ReadIOPS
	--start-time 2016-10-04T13:16:35
	--end-time 2016-10-11T13:16:35
	--period 3600
	--namespace AWS/RDS
	--statistics Sum
	--dimensions Name=DBInstanceIdentifier,Value=cc-mysql-database

06 The command output should return the ReadIOPS usage details requested:

{
    "Datapoints": [
        {
            "Timestamp": "2016-10-04T13:16:35Z",
            "Sum": 0.23400416762398904,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-04T14:16:35Z",
            "Sum": 1.4499758337361044,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-04T15:16:35Z",
            "Sum": 0.0,
            "Unit": "Count/Second"
        },

        ...

        {
            "Timestamp": "2016-10-11T10:16:35Z",
            "Sum": 1.0999450027498625,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-11T11:16:35Z",
            "Sum": 0.0,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-11T12:16:35Z",
            "Sum": 1.1000366678889297,
            "Unit": "Count/Second"
        }
    ],
    "Label": "ReadIOPS"
}

If the total number of ReadIOPS has been less than 20 for the last 7 days, the selected RDS instance qualifies as candidate for the idle instance.

07 Run get-metric-statistics command (OSX/Linux/UNIX) to get the statistics recorded by AWS CloudWatch for the WriteIOPS metric, representing the number of Write I/O operations per second. The following command example returns the total number of WriteIOPS used by an AWS RDS instance identified by the name cc-mysql-database, IOPS usage data captured during a 7 days period (set by the --start-time and --end-time command parameters), using 1 hour period as the granularity of the returned datapoints (set by the --period parameter):

aws cloudwatch get-metric-statistics
	--region us-east-1
	--metric-name WriteIOPS
	--start-time 2016-10-04T13:16:57
	--end-time 2016-10-11T13:16:57
	--period 3600
	--namespace AWS/RDS
	--statistics Sum
	--dimensions Name=DBInstanceIdentifier,Value=cc-mysql-database

08 The command output should return the WriteIOPS usage details requested:

{
    "Datapoints": [
        {
            "Timestamp": "2016-10-04T13:16:57Z",
            "Sum": 0.09999166736105325,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-04T14:16:57Z",
            "Sum": 0.15,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-04T15:16:57Z",
            "Sum": 0.25000416673611225,
            "Unit": "Count/Second"
        },

        ...

        {
            "Timestamp": "2016-10-11T10:16:57Z",
            "Sum": 0.10000333344444814,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-11T11:16:57Z",
            "Sum": 0.2500125006250313,
            "Unit": "Count/Second"
        },
        {
            "Timestamp": "2016-10-11T12:16:57Z",
            "Sum": 0.6833561118703957,
            "Unit": "Count/Second"
        }
    ],
    "Label": "WriteIOPS"
}

If the total number of WriteIOPS has been less than 20 for the last 7 days, the selected RDS instance qualifies as candidate for the idle instance.

09 Run list-tags-for-resource command (OSX/Linux/UNIX) to list the existing tags for the selected RDS instance:

aws rds list-tags-for-resource
	--region us-east-1
	--resource-name arn:aws:rds:us-east-1:123456789012:db:cc-mysql-database

10 The command output should return the tags (key-value pairs) applied to the instance. The Role and Owner tags returned and their values (highlighted) can be used to determine the resource role within the application stack and to contact its owner for more information in order to decide whether the RDS database instance can be terminated or not:

{
	"TagList": [
    	{
        	"Value": "legacy-app-db-test-server",
        	"Key": "Role"
    	},
    	{
        	"Value": "db_ops@cloudconformity.com",
        	"Key": "Owner"
    	},
    	{
        	"Value": "legacy-webapp-db",
        	"Key": "Name"
    	}
	]
}

If the data returned for the steps no. 3 - 10 satisfy the conditions set by the conformity rule (instance role, instance owner, ReadIOPS + WriteIOPS, database connections), the selected RDS instance is considered "idle" and can be terminated in order to reduce AWS RDS usage costs.

11 Repeat steps no. 3 - 10 to verify the role, owner, DatabaseConnections, ReadIOPS and WriteIOPS metrics usage within the specified time frame for the rest of the RDS instances available in the current region.

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

Remediation / Resolution

Option 1: terminate the idle instances. To terminate (delete) any AWS RDS instances that are currently running in idle mode, perform the following commands:

Using AWS Console

01 Sign in to the AWS Management Console.

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

03 In the left navigation panel, under RDS Dashboard section, choose Instances.

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

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

06 On the Delete DB Instance confirmation page, select Yes from the Create final Snapshot dropdown menu and enter a unique name for your last database backup (snapshot) in the Final snapshot name box. Cloud Conformity strongly recommends taking a final snapshot of your database before termination because once the selected instance is deleted the RDS automated backups will no longer be available.

07 Click the Delete button to terminate the instance.

08 Repeat steps no. 4 - 7 to delete any other idle RDS instances provisioned within the current region.

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

Using AWS CLI

01 Run delete-db-instance command (OSX/Linux/UNIX) using the name of the database instance as identifier to terminate the selected RDS idle instance (see Audit section part II to identify the right resource). Cloud Conformity strongly recommends taking a final snapshot of your database before you terminate it as all the automated backups are removed together with the instance. The following command example deletes an RDS database instance named cc-mysqldb-staging and creates a final snapshot of the resource (cc-mysqldb-staging-final-snapshot):

aws rds delete-db-instance
	--region us-east-1
	--db-instance-identifier cc-mysqldb-staging
	--no-skip-final-snapshot
	--final-db-snapshot-identifier cc-mysqldb-staging-final-snapshot

02 The command output should return the metadata of the RDS instance selected for deletion:

{
    "DBInstance": {
        "PubliclyAccessible": true,
        "MasterUsername": "cc_mysql_user",
        "LicenseModel": "general-public-license",
        "VpcSecurityGroups": [
            {
                "Status": "active",
                "VpcSecurityGroupId": "sg-d5e772af"
            }
        ],

        ...

        "DBInstanceStatus": "deleting",
        "EngineVersion": "5.6.27",
        "AvailabilityZone": "us-east-1a",
        "StorageType": "gp2",
        "CACertificateIdentifier": "rds-ca-2015",
        "StorageEncrypted": false,
        "DBInstanceClass": "db.m3.medium",
        "DBInstanceIdentifier": "cc-mysql-database"
    }
}

03 Repeat step no. 1 and 2 to terminate any other idle RDS instances available in the current region.

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

Option 2: disable the rule check. If the selected idle RDS database instance is needed (its role within your application stack is important), you should turn off the conformity rule check for the instance from the Cloud Conformity console.

References

Publication date Oct 13, 2016