Cutting RDS costs in half

Alejandro Roca — Tue, 25 Jan 2022 12:36:59 +0000

During our AWS cost reduction efforts at BYHOURS, one story has been particularly relevant and we want to share it with anyone who might find it helpful.

Our database solution of choice is Relational Database Service (RDS), one of the oldest and best-known AWS services. It allows for rapid growth without having to worry about database scaling, as well as providing standardized configuration and ease of upgrading.

However, RDS and its pricing model can be cumbersome and costly if not well optimized. Thus, RDS was our most expensive bill a month ago.

The story begins, prompted by AWS itself, we are about to switch our database instances to instances with Graviton2 processors, as it brings "up to 52% better price/performance".

I will now go on to explain each of the steps we took to make this and other cost-optimizing actions, along with links to the corresponding documentation.

Upgrading MySQL 5.7 to 8.0

Having MySQL as database engine, at least version 8.0.17 is needed to get Graviton2 instances support. So, coming from version 5.7, we had to perform a major upgrade which, as of today, is to version 8.0.23. The AWS documentation indicates the steps to perform the upgrade, as well as the considerations to take into account. The process can take up to 1 hour and produce a downtime of 10-20 minutes, depending on the database size. Nevertheless, there is a way to minimize the downtime.

Some documentation highlights on the process:

Minor version upgrades can be set to automatic.
Major version upgrades will require an OS update of the DB instances, if available.
The upgrade process generates 2 snapshots: before and after the upgrade.
To reduce the downtime in production, the upgrade can be done in a read replica of the production instance and when everything is ready, promote the read replica to a standalone DB instance.
RDS will automatically run prechecks before applying a DB major engine upgrade.

After the MySQL upgrade in the staging environment, we identified an error that brought down the system:

SQLSTATE[HY000] [2054] Server sent charset unknown to the client. Please, report to the developers

This error seems to be related to a PHP bug which has been fixed in versions 7.0.19, 7.1.5 and 7.2.0 onwards, but not in version 5.6 which we use for compatibility. The bug is caused by a change in the MySQL 8.0 defaults in the character_set_* parameters.

The solution we found was to associate a new RDS parameter group changing the character_set_server and character_set_database parameters back to utf8. Props to @kmoberg :

MySQL charset (255) unknown to the client in MySQL and AWS RDS

Karl Mathias Moberg for AWS Community Builders ・ Mar 20 '21 ・ 2 min read

#aws #mysql #rds

Migrating to Graviton2 based DB instances

Once we have the correct MySQL version, we can easily modify the instance type to an arm based Graviton2 instance. Steps are again described in the documentation of the process, which causes a downtime of about 2-3 minutes.

At this point, we could compare the price/performance for the DB instances before and after the change.

In our case, just changing the M5 instances we were using to their M6g corresponding instances let us save 11.2% in price per hour costs.

In terms of performance, we analized the CPUUtilization metric for a day, normalized by Input/Output operations Per Second (IOPS), finding an average improvement of 9.14% in mean CPU usage.

If you want to see a detailed comparison between Intel and Graviton2 based instances, I hardly recommend the benchmark done by Sven Bayer.

In short, under normal conditions you should expect a price/performance improvement of around 20%, far from the 52% maximum identified by AWS.

Downsizing overprovisioned instance types

These results left us cold, since we expected to obtain a higher optimization. However, they opened the door to continue reviewing the configuration of the database instances.

This is how, thanks to the performance gains and a better distribution of reporting and cron jobs, we could change de DB instance for one with a lower size. This has a special impact on Multi-AZ instances.

Another important change we made is the use of T4g instances in the staging environment databases. For this, we had to upgrade the MySQL version from 8.0.23 to 8.0.25.

Storage configuration changes

The choice of storage type is something to consider when configuring database instances. In our case, all databases had a "Provisioned IOPS" storage type. This is a storage type that ensures a series of IOPS on a continuous basis, indicated for databases that have a real usage load, i.e. for databases in the production environment. However, this requires paying for the number of IOPS provisioned and having a minimum number of IOPS determined by the disk space allocated to the instance.

Therefore, we changed the storage type to "General Purpose SSD" for staging environment databases. In addition, we reduced the number of provisioned IOPS to the production databases, after analyzing their metrics.

Finally, the assigned storage for staging instances was oversized. The storage costs in RDS are minimal, but taking advantage of the inertia we were carrying, we reduced the storage for these instances. To do this, we did the following:

Create a new instance with the desired storage.
Rename the old instance to a different name.
Rename the new instance to the original name of the old instance.

Recap

As a reward for getting this far, I am including a summary of the changes made and the impact each of them had on the RDS bill:

DB staging (-86.58%):

MySQL version: 5.7.33 → 8.0.25
Instance type: db.t2.large → db.t4g.medium
Storage:
- type: Provisioned IOPS → General Purpose SSD Storage
- size: 500 GiB → 20 GiB
- Provisioned IOPS: 2000 → 0

DB staging read replica (-86.58%):

MySQL version: 5.7.33 → 8.0.25
Instance type: db.t2.large → db.t4g.medium
Storage:
- type: Provisioned IOPS → General Purpose SSD Storage
- size: 500 GiB → 20 GiB
- Provisioned IOPS: 2000 → 0

DB production (Multi-AZ) (-45.31%):

MySQL version: 5.7.33 → 8.0.25
Instance type: db.m5.2xlarge → db.m6g.xlarge
Storage:
- Provisioned IOPS: 2000 → 1500

DB production read replica (-16.25%):

MySQL version: 5.7.33 → 8.0.25
Instance type: db.m5.xlarge → db.m6g.xlarge
Storage:
- Provisioned IOPS: 2000 → 1500

The final cost reduction of our RDS bill is 51.28%, so you might find some of these tips useful for your use case.

DEV Community: Alejandro Roca