Sometimes you need to add or remove ressources like CPUs or RAM to your databases to handle more or less concurrent operations. This is called scaling. Due to a migration from MySQL to Aurora in my company, I benchmarked vertical scaling delay on MySQL and Aurora and I will detail these results in this article.
Vertical vs Horizontal scaling
There are two ways to scale a database :
- Vertical scale means adding more compute ressources to one instance. For example if need more CPU on my database and I transform a db.t4g.medium to a db.t4g.large database instance, I’m doing vertical scaling.
- Horizontal scale means adding more instances. For example if need more CPU on my database db.t4g.medium, I add another db.t4g.medium instance. I’m doing horizontal scaling.
Multi-master issue
Scaling horizontally database read replicas (slave) is easy due to bin_log mechanism. Your read replicas will simply read updates from files located on your master database.
Scaling horizontally write instances (multi-master) is extremely complex due to conflicts that occur between concurrent changes and replication lags. Don’t forget that SQL databases were designed to run on a single server with relatively small data volumes.
Handling a multi-master was definitely not an option for us because of this complexity. This is why from time to time for specific events (TV Shows, marketing operations …) we need to scale vertically our master database to support an unusual load.
Vertical scaling RDS
The way of vertical scaling on RDS MySQL is different from that on RDS Aurora. Indeed, on RDS MySQL you need to modify and restart your instance to apply the scale. On RDS Aurora, you can use the Failover mechanism to promote a reader instance as a writer instance.
Let’s say we want to scale our db.t4g.medium writer instance to a db.t4g.large instance :
Create a reader db.t4g.large with the “Tier 0” failover parameter (saying to Aurora that this reader have the highest priority for failover) :
Failover current db.t4g.medium writer :
Our new db.t4g.large instance has been promoted writer after after a few seconds :
Mesure uptime of a MySQL Database
Vertical scaling RDS MySQL or RDS Aurora necessarily causes server downtime. For RDS MySQL the server needs to restart and for RDS Aurora the failover mechanism is not instantaneous.
Let’s create and run a bash script which tries to do a database insertion every second to measure the uptime :
#!/bin/bash
# Create a test database
mysql -h YOUR_DB_HOST -u YOUR_DB_USER -pYOUR_DB_PASSWORD -e "drop database if exists test; create database test; create table test.test (id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id));"
# UPTIME Test
for ((n=0;n<600;n++))
do
echo $(date);
MYSQL_PWD="YOUR_DB_PASSWORD" mysql -h YOUR_DB_HOST -u YOUR_DB_USER -e "INSERT INTO test.test (name) VALUES ('john doe');"
sleep 1
done
RDS Aurora vs RDS MySQL vertical scaling benchmark results
Here are the average results I observed :
| RDS AURORA | RDS MYSQL | |
| Vertical scaling downtime | 6 seconds | 175 seconds |
As you can see the difference is very important!