Assessing MySQL Performance Amongst AWS Options – Part Two

See part one of this series here. 

This post is part two of my series “Assessing MySQL Performance Amongst AWS Options”, taking a look at how current Amazon RDS services – Amazon Aurora and Amazon RDS for MySQL – compare with Percona Server with InnoDB and RocksDB engines on EC2 instances. This time around, I am reviewing the total cost of one test run for each database as well as seeing which databases are the most efficient.

First, a quick recap of the 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. And now that we have established the setup, let’s take a look at what we found.

Costs

Now we are getting down to the $’s! First, let’s review the total cost of one test run for each database:

Sorting the costs of one test run in order from cheapest to most expensive we see this order emerge:

1. EC2/gp2 carrying server tests #3 or #4 featuring Percona Server for MySQL [represents the LEAST cost in $’s]
2. RDS/gp2 carrying server test #2, RDS/MySQL
3. EC2/io1 carrying server tests #3 or #4
4. RDS/io1 carrying server test #2, RDS/MySQL
5. RDS/Aurora, server test #1  [GREATEST COST IN $’s]

How does that translate to $’s? Let’s find out how the structure of these costs looks like for every database. Before we study that, though, there are some things to bear in mind:

• Our calculations include only server-side costs
• Per instance, the price we used as a baseline was RESERVED INSTANCE STANDARD 1-YEAR TERM
• For RDS/Amazon Aurora the values for volume size and amount of I/O requests represent real data obtained from CloudWatch metrics (VolumeBytesUsed for used volume space and VolumeReadIOPs+VolumeWriteIOPs for IOPs used) after the test run
• In the case of Percona Server/RocksDB due to LZ4 compression, the database on disk is 5x smaller, so we used a half-sized io1 volume – 350GB vs 700GB for either Percona Server with InnoDB or RDS/MySQL. This still complies with the requirement for io1 volumes to deliver 50 IOPS per GB.
• The duration set for the test run is 30 mins

Our total cost formulas

These are the formulas we used in calculating these costs:

• EC2/gp2, EC2/io1, RDS/gp2, RDS/io1

  • total cost = server instance size cost + allocated volume size cost + requested amount of IOPS cost

• RDS/Amazon Aurora

  • total cost = server instance size cost + allocated volume size cost + actually used amount of I/O cost

The results

Here are our calculations in chart form, you can click on the chart to enlarge it on screen:

One interesting observation here is that, as you can see from the costs structure chart, the most significant part of costs is IO provisioning – either the requested amount of IOPS (EC2/io1 or RDS/io1) or the actually used amount of IOPS (RDS/Aurora). In the former case, the cost is a function of time, and in the latter case, costs depend only on the amount of I/O requests actually issued.

Let’s check how these costs might look like if we provision EC2/io1, RDS/io1 volumes and RDS/aurora storage for one month. From the cost structure, it’s clear that in case of RDS/aurora 4xlarge – db instance performed 51M I/O requests for half an hour. So we effectively got 51000000 (I/O request) / 1800(seconds) ~= 28000 IOPs.

In this way, IO provisioning of 28000 IOPS for EC2/io1 costs 8x less and for RDS/io1 costs 5x less. That means that to be cost-efficient, the throughput of RDS/Aurora should be at least 5x or even 8x better than that of EC2 or RDS with io1 volume.

Conclusion: the IO provisioning factor should be taken into account during your planning of deployments with io1 volumes or RDS/aurora

Efficiency

Now it’s time to review which databases perform the most efficiently by analyzing their transaction/cost ratio:

Below you can find the minimum and maximum prices for 1000 transactions for each of the database servers in our tests, again running from cheapest to most expensive in $ terms:

Some concluding thoughts

• EC2#Percona Server/RocksDB offers the lowest price per 1000 transactions – $0.42 on m5.4xlarge instance with 350GB io1 volume/15000 IOPs
• RDS/MySQL looked to be the most expensive in this evaluation – $22.3 for 1000 transactions – db.m5.large with 700GB io1 volume/15000 IOPs
• Lowest price for each database was obtained on 4xlarge instances, most expensive on large instances.
• IO provisioning is a key factor that impacts run costs
• For both EC2 and RDS gp2/5400GB (~16000 IOPS) is the cost wise choice
• RDS/Aurora – the lowest price per 1000 transactions is $8.29, but that is 4x more expensive than the best price of 1000 transactions for RDS/MySQL, 5x more expensive than for EC2#Percona/InnoDB, and 20x more expensive than for EC2#Percona/RockDB. That means that despite the fact that Amazon Aurora shows very good throughput (actually the best among InnoDB-like engines), it may not be as cost-effective as other options.

One Final Note

When estimating your expenses, you will need to keep in mind that each company is different in terms of what they offer, how they build and manage those offerings, and of course, their pricing structure and cost per transaction. For AWS, you do need to be aware of the expenses of building and managing those things yourself that AWS handles for you; i.e. built into their cost. We can see, however, that in these examples, MyRocks is definitely a cost-effective solution when comparing direct costs.