Assessing MySQL Performance Amongst AWS Options – Part One

With such a wide range of options available for running MySQL based servers in Amazon cloud environments, how do you choose? There’s no doubt it’s a challenge. In this two-part series of blog posts, we’ll try to draw a fair and informative comparison based on well-established benchmark scenarios – at scale.

In part one we will discuss the performance of the current Amazon RDS services – Amazon Aurora and Amazon RDS for MySQL – and compare it with the performance of Percona Server with InnoDB and RocksDB engines. And in part two we will go over costs and efficiencies to look for. All this information is necessarily number-heavy, but hopefully, you’ll enjoy the experiments!

Evaluation scenario

The benchmark scripts

For these evaluations, we use the sysbench/tpcc LUA test with a scale factor of 500 warehouses/10 tables. This is the equivalent of 5000 warehouses of the official TPC-C benchmark.

Amazon MySQL Environments

These are the AWS MySQL environments under analysis:

• Amazon RDS Aurora
• Amazon RDS for MySQL with the InnoDB storage engine
• Percona Server for MySQL with the InnoDB storage engine on Amazon EC2
• Percona Server for MySQL with the RocksDB storage engine on Amazon EC2

Technical Setup – Server

These general notes apply across the board:

• AWS region us-east-1(N.Virginia) was used for all tests
• Server and client instances were spawned in the same availability zone
• All data for tests were prepared in advance, stored as snapshots, and restored before the test
• Encryption was not used

And we believe that these configuration notes allow for a fair comparison of the different technologies:

• AWS EBS optimization was enabled for EC2 instances
• For RDS/Amazon Aurora only a primary DB instance was created and used
• In the case of RDS/MySQL, a single AZ deployment was used for RDS/MySQL
• EC2/Percona Server for MySQL tests were run with binary log enabled

Finally, here are the individual server configurations per environment:

Server test #1:Amazon RDS Aurora

• Database server: Aurora MySQL 5.7
• DB instances: r5.large, r5.xlarge, r5.2xlarge, r5.4xlarge
• volume: used ~450GB(>15000 IOPS)

Server test #2:Amazon RDS for MySQL with InnoDB Storage Engine

• Database server: MySQL Server 5.7.25
• RDS instances: db.m5.large, db.m5.xlarge, db.m5.2xlarge, db.m5.4xlarge
• volumes(allocated space):
  • gp2: 5400GB(~16000 IOPs)
  • io1: 700GB(15000 IOPs)

Server test #3:Percona Server for MySQL with InnoDB Storage Engine

• Database server: Percona Server 5.7.25
• EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge
• volumes(allocated space):
  • gp2: 5400GB(~16000 IOPs)
  • io1: 700GB(15000 IOPs)

Server test #4:Percona Server for MySQL with RocksDB using LZ4 compression

• Database server: Percona Server 5.7.25
• EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge
• volumes(allocated space):
  • gp2: 5400GB(~16000 IOPs)
  • io1: 350GB(15000 IOPs)

Technical Setup – Client

Common to all tests, we used an EC2 instance: m5.xlarge

So, now that we have established the setup, let’s take a look at what we found.

Disk space consumption for sysbench/tpcc 5000 warehouses

Since disk space costs money, you’d want to see how the raw data occupied space in each environment. There’s a clear “winner” there: Percona Server for MySQL with RocksDB storage engine. Of course, there are lots more factors to consider.

The performance tests

For the performance tests, we used this set up as common to all:

• number of threads: 128
• duration of each test run: 30 mins

Then, per server, we worked through individual tests as follows:

Server tests

Server test #1: RDS/Amazon Aurora

• create cluster
• restore cluster instance from db snapshot

Server test #2: RDS/MySQL

• restore instance from db snapshot

Server tests #3 and #4: Percona Server for MySQL on EC2

• create EC2 instance as server
• create volume from snapshot and attach to server instance

Client test

• create EC2 instance as client
• start test

Throughput

First of all, then, let’s review throughput results for every tested database. In case things aren’t complicated enough, at this stage we’re also taking into account whether io1 or gp2 volume type has an effect on the outcomes. AWS offers this comparison of io1 and gp2.

Server test #1:RDS/Amazon Aurora

Aurora scales almost linearly from large to 4xlarge, notably outperforms Percona Server/InnoDB and RDS/MySQL but still behind RocksDB more than 2x times

Server test #2:RDS/MySQL

• for all types of instances, we saw almost no difference in results between io1 and gp2 volumes
• in these tests, RDS/MySQL shows the lowest throughput from all databases

Server test #3:Percona Server for MySQL with InnoDB 

• up to 4xlarge instance results for gp2 volume slightly better(~10%) than for io1. On 4xlarge instance where the size of instance allows having a large enough innodb buffer pool that from one side helps to reduce the amount of reads as a notable amount of data will be already cached and from other side that helps to improve writing/flushing. As and in case of RocksDB it looks like writes operations are much more efficient(better latency) on io1 volumes and that helps to get better results with io1 volumes.
• overall results for Percona Server/InnoDB is on 10-15% better than for RDS/MySQL but notably lower than for RDS/Aurora (in 2x-2.5x times) and RocksDB(5x times)

Server test #4:Percona Server for MySQL with RocksDB

• large/xlarge – results are on par for gp2 and io1 volumes, starting from 2xlarge instance results with io1 volume continue scales linearly while gp2 stays almost on the same level
• RockDB shows best results across all types of instances and outperforms Percona Server/InnoDB and RDS/MySQL in 5x times and RDS/Aurora 2-2.5 times

In the next post in this series, I will take a look at the total cost of one test run for each database, as well as review which databases perform most efficiently by analysis transaction cost ratio.