01
Run describe-clusters command (OSX/Linux/UNIX) using custom query filters to list the IDs of all AWS Redshift clusters created in the selected region:
aws redshift describe-clusters
--region us-east-1
--output table
--query 'Clusters[*].ClusterIdentifier'
02
The command output should return a table with the requested cluster IDs:
------------------------
| DescribeClusters |
+----------------------+
| cc-staging-cluster |
| cc-sandbox-cluster |
+----------------------+
03
Run get-metric-statistics command (OSX/Linux/UNIX) to get the statistics recorded by CloudWatch for the CPUUtilization metric representing the CPU usage of the selected Redshift cluster. The following command example returns the average CPU utilization for an AWS Redshift cluster identified by the ID cc-staging-cluster, usage data captured during a 7-day time range, using 1 hour time frame as the granularity of the returned datapoints:
aws cloudwatch get-metric-statistics
--region us-east-1
--metric-name CPUUtilization
--start-time 2017-03-05T17:29:41
--end-time 2017-03-12T17:29:41
--period 3600
--namespace AWS/Redshift
--statistics Average
--dimensions Name=ClusterIdentifier,Value=cc-staging-cluster
04
The command output should return the cluster CPU usage details requested:
{
"Datapoints": [
{
"Timestamp": "2017-03-05T17:29:41Z",
"Average": 12.2085,
"Unit": "Percent"
},
{
"Timestamp": "2017-03-05T18:29:41Z",
"Average": 11.033499999999999995,
"Unit": "Percent"
},
{
"Timestamp": "2017-03-05T19:29:41Z",
"Average": 11.10425,
"Unit": "Percent"
},
...
{
"Timestamp": "2017-03-12T15:29:41Z",
"Average": 1.430999999999999993,
"Unit": "Percent"
},
{
"Timestamp": "2017-03-12T16:29:41Z",
"Average": 0.92833333333333333,
"Unit": "Percent"
},
{
"Timestamp": "2017-03-12T17:29:41Z",
"Average": 0.52783333333333333,
"Unit": "Percent"
}
],
"Label": "CPUUtilization"
}
If the average CPU usage data returned is less than 60%, the selected AWS Redshift cluster qualifies as candidate for the underused cluster.
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 Amazon Redshift cluster identified by the name cc-staging-cluster, IOPS usage data captured during a 7-day time period, using 1 hour time range as the granularity of the returned datapoints:
aws cloudwatch get-metric-statistics
--region us-east-1
--metric-name ReadIOPS
--start-time 2017-03-05T17:44:09
--end-time 2017-03-12T17:44:09
--period 3600
--namespace AWS/Redshift
--statistics Sum
--dimensions Name=ClusterIdentifier,Value=cc-staging-cluster
06
The command output should return the ReadIOPS usage details requested:
{
"Datapoints": [
{
"Timestamp": "2017-03-05T17:44:09Z",
"Sum": 0.4293539505765276,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-05T18:44:09Z",
"Sum": 0.4000329652228976,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-05T19:44:09Z",
"Sum": 0.4001483344299335,
"Unit": "Count/Second"
},
...
{
"Timestamp": "2017-03-12T15:44:09Z",
"Sum": 0.0000557761644715,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-12T16:44:09Z",
"Sum": 0.133804686450845,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-12T17:44:09Z",
"Sum": 0.087927411198773,
"Unit": "Count/Second"
}
],
"Label": "ReadIOPS"
}
If the total number of ReadIOPS has been less than 100 in the last 7 days, the selected Redshift cluster qualifies as candidate for the underutilized cluster.
07
Run get-metric-statistics command (OSX/Linux/UNIX) to get the statistics recorded by Amazon 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 Redshift cluster identified by the name cc-staging-cluster, usage data captured during a 7-day time range, using 1 hour time period as the granularity of the returned datapoints:
aws cloudwatch get-metric-statistics
--region us-east-1
--metric-name WriteIOPS
--start-time 2017-03-05T17:51:13
--end-time 2017-03-12T17:51:13
--period 3600
--namespace AWS/Redshift
--statistics Sum
--dimensions Name=Name=ClusterIdentifier,Value=cc-staging-cluster
08
The command output should return the WriteIOPS usage details requested:
{
"Datapoints": [
{
"Timestamp": "2017-03-05T17:51:13Z",
"Sum": 0.1293539505765276,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-05T18:51:13Z",
"Sum": 0.2000329652228976,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-05T19:51:13Z",
"Sum": 0.00083333333333333,
"Unit": "Count/Second"
},
...
{
"Timestamp": "2017-03-12T15:51:13Z",
"Sum": 0.0,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-12T16:51:13Z",
"Sum": 0.0,
"Unit": "Count/Second"
},
{
"Timestamp": "2017-03-12T17:51:13Z",
"Sum": 0.0,
"Unit": "Count/Second"
}
],
"Label": "WriteIOPS"
}
If the total number of WriteIOPS has been less than 100 within the past 7 days, the selected Redshift instance qualifies as candidate for the underused database instance.
If the usage data returned at steps no. 3 - 8 satisfy the conditions set by the conformity rule, the selected Redshift cluster is considered "underutilized" and should be resized in order to reduce your AWS Redshift usage costs.
09
Repeat steps no. 3 – 8 to verify the CPU, ReadIOPS and WriteIOPS metrics usage data recorded within the selected time range for the rest of the Redshift clusters provisioned in the current region.
10
Change the AWS region by updating the --region command parameter value and repeat steps no. 1 - 9 to perform the entire audit process for other regions.