The Mystery of RDS Costs

#aws #sql #devops #tutorial

Two weeks ago, I posted my first Dev.to post and shared the Developer's Guide to AWS Costs with this community. Thank you 🙏 to everyone for the feedback and support! If you like what you see, please give us a star and follow along with the project on GitHub.

I started this project for one reason: to get engineers off cost reduction work and back to building great software.

When the business decides 'cloud costs are too high', engineering priorities shift from executing the roadmap to cost reduction. The business functions (finance & accounting) don't know how the cloud works, and the best they can do is tell Engineering to 'fix it'.

The Developer's Guide to AWS Costs is a resource for dev teams, SREs and DevOps to quickly understand their AWS costs using SQL. First, I posted guides for setting up a Cost and Usage Report and EC2 costs. Now, I'm back for more! This week, I added a new guide for developers to analyze their Relational Database Service (RDS) Costs.

Every AWS customer I've worked with uses RDS in some way. Unfortunately, RDS costs can be significantly more challenging to understand than EC2 😭 The complexity is driven by the many different choices a user has to make when provisioning an RDS instance (or not an instance):

7 different database engines (Aurora, MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, RDS Custom)
Single and Multi-AZ deployments options
1yr and 3yr reserved instance contracts
Automated and manual database backups
Data transfer costs
and more…

RDS Cost Analysis

Below are samples from the full guide on Strake's website for how to calculate Single and Multi-AZ instance usage, Data transfer costs to/from the Internet, and the total cost of an RDS instance by Operation and Usage Type.

If you don't have a cost and usage report setup, you'll want to check out the setup guide for details on how to get started.

Calculate On-Demand, Single-AZ Instance Usage

The query below will show single_od_cost by [lineItem/ResourceID], [product/instanceType], and [product/region]. These results will show all RDS instances with a Single-AZ deployment being billed with the on-demand pricing model.

#Existence Cost
##Isolate the Single-AZ, OnDemand, running cost for an RDS Instance.
SELECT
    [lineItem/ResourceID],
    [product/instanceType],
    [product/region],
    round(sum([lineItem/UnblendedCost]), 4) as single_od_cost
FROM CUR
WHERE
    [lineItem/ProductCode] = 'AmazonRDS'
    and [lineItem/LineItemType] is 'Usage'
    and [lineItem/Operation] LIKE 'CreateDBInstance%'
    and [lineItem/UsageType] LIKE '%InstanceUsage:%'
GROUP BY
    [lineItem/ResourceID],
    [product/instanceType],
    [product/region]
ORDER BY
    sum([lineItem/UnblendedCost]);

For the query above, here is some background on the fields being used and what the filter values mean:

[lineItem/ResourceID] - A unique identifier for an RDS instance. You can also use this field in the WHERE clause to narrow the analysis to a single RDS instance.
[product/instanceType] and [product/region] - These are used to identify the instance type and region for each RDS Instance. These are two key variables that determine the pricing of an instance.
[lineItem/ProductCode] - 'AmazonRDS' is the product code for RDS. Use this field to ensure the analysis focuses on RDS costs.
[lineItem/LineItemType] - 'Usage' is the value used to find results for on-demand pricing. Make sure to filter on 'usage' when identifying on-demand costs.
[lineItem/Operation] - The operation helps narrow down the cost categories. For RDS, 'CreateDBInstance' is one of the most common operations to capture existence costs.
[lineItem/UsageType] - For this query, we filtered on '%InstanceUsage%' to capture everything for a single-az RDS deployment.

Calculate On-Demand, Multi-AZ Instance Usage

The query below will show multi_od_cost by [lineItem/ResourceID], [product/instanceType], and [product/region]. These results will show all RDS instances with a Multi-AZ deployment billed with the on-demand pricing model.

#Existence Cost
##Isolate the Multi-AZ, OnDemand, running cost for an RDS Instance.
SELECT
    [lineItem/ResourceID],
    [product/instanceType],
    [product/region],
    round(sum([lineItem/UnblendedCost]), 4) as multi_od_cost
FROM CUR
WHERE
    [lineItem/ProductCode] = 'AmazonRDS'
    and [lineItem/LineItemType] is 'Usage'
    and [lineItem/Operation] LIKE 'CreateDBInstance%'
    and [lineItem/UsageType] LIKE '%Multi-AZUsage:%'
GROUP BY
    [lineItem/ResourceID],
    [product/instanceType],
    [product/region]
ORDER BY
    sum([lineItem/UnblendedCost]);

There is only one difference in the filters for a Multi-AZ query compared to a Single-AZ query:

[lineItem/UsageType] - The values '%Multi-AZUsage' marks an instance as a Multi-AZ deployment.

Calculate Data Transfer for RDS to/from the Internet

Data transfer costs for RDS to/from the internet are charged based on the GB of data being transferred. There is no cost for bringing data from the internet into your AWS environment. There is a tiered pricing model for moving data to the internet, meaning the more data sent, the lower the unit cost.

This query below calculates the data_transfer_cost by filtering the [lineItem/UsageType] to 'DataTransfer-%'. The field data_transfer_gb is also in this query so you can see the amount of data being transferred when there is no cost.

#Utilization
##Data Transfer for RDS to/from the Internet
SELECT
    [lineitem/ResourceID],
    [lineItem/UsageType],
    round(sum([lineItem/UsageAmount]), 4) as data_transfer_gb,
    round(sum([lineItem/UnblendedCost]), 4) as data_transfer_cost
FROM cur
WHERE
    [lineItem/ProductCode] = 'AmazonRDS'
    and [lineItem/LineItemType] IS 'Usage'
    and [lineItem/Operation] IS 'Not Applicable'
    and [lineItem/UsageType] LIKE '%DataTransfer-%'
GROUP BY
    [lineitem/ResourceID],
    [lineItem/UsageType]
ORDER BY
    sum([lineItem/UnblendedCost]);

Calculate Total Cost by RDS Instance by Hour

Once you understand which RDS Instances are costing the most in your environment, you can simply add the [lineItem/UsgaeStartDate] field into your query to track costs over time. To isolate the cost of a single RDS instance, remove the [lineItem/ResourceID] from the SELECT, GROUP BY, and ORDER BY statements and add it to the WHERE statement with a resource ID. Replace the variable insert-rds-are-here with the ARN of your RDS instance.

SELECT
    [lineItem/UsageStartDate],
    [lineItem/LineItemType],
    [lineItem/Operation],
    [lineItem/UsageType],
    round(sum([lineItem/UsageAmount]), 4) as usage_amount,
    [pricing/unit],
    round(sum([lineItem/UnblendedCost]), 4) as unblended_cost,
    round(sum([reservation/EffectiveCost]), 4) as reserved_cost
FROM cur
WHERE
    [lineItem/ProductCode] = 'AmazonRDS'
    and [lineItem/LineItemType] <> 'Tax'
    and [lineItem/ResourceId] is 'insert-rds-arn-here'
GROUP BY
    [lineItem/UsageStartDate],
    [lineItem/LineItemType],
    [lineItem/Operation],
    [lineItem/UsageType],
    [pricing/unit]
ORDER BY
    [lineItem/UsageStartDate],
    [lineItem/LineItemType],
    [lineItem/Operation],
    [lineItem/UsageType],
    sum([lineItem/UnblendedCost]);

Want to see more? You can check out the full guide on the Strake Website. If you have questions or feedback let me know in the comments! ⬇️⬇️
Thanks for reading 😃