Scaling IO-Bound Workloads for MySQL in the Cloud – part 2

Scaling IO-Bound Workloads for MySQL in the Cloud – part 2

PREVIOUS POST
NEXT POST

This post is a followup to my previous article https://www.percona.com/blog/2018/08/29/scaling-io-bound-workloads-mysql-cloud/

In this instance, I want to show the data in different dimensions, primarily to answer questions around how throughput scales with increasing IOPS.

A recap: for the test I use Amazon instances and Amazon gp2 and io1 volumes. In addition to the original post, I also tested two gpl2 volumes combined in software RAID0. I did this for the following reason: Amazon cap the single gp2 volume throughput to 160MB/sec, and as we will see from the charts, this limits InnoDB performance.

Also, a reminder from the previous post: we can increase gp2 IOPS by increasing volume size (to the top limit 10000 IOPS), and for io1 we can increase IOPS by paying per additional IOPS.

Scaling with InnoDB

So for the first result, let’s see how InnoDB scales with increasing IOPS.

There are a few interesting observations here: InnoDB scales linearly with additional IOPS, but it faces a throughput limit that Amazon applies to volumes.

So besides considering IOPS, we should take into account the maximal throughout of volumes.

In the second chart we compare InnoDB performance vs the cost of volumes:

It’s interesting to see here the slope for gp2 volumes is steeper than for io1 volumes. This means we can get a bigger increase in InnoDB performance per dollar using gp2 volumes, but only until we reach the IOPS and throughput limits that are applied to gp2 volumes.

Scaling with MyRocks

And here’s the similar chart but for MyRocks:

Here we can also see that MyRocks scales linearly, showing identical results on gp2 and io1 volumes. This means that running on gp2 will be cheaper. Also, there is no plateau in throughput, as we saw for InnoDB, which means that MyRocks uses less IO throughput.

And the chart for the cost of running MyRocks:

This charts also shows that it is cheaper to run on gp2 volume but only while it provides enough IOPS. I assume that using two gp2 volumes would allow me to double the throughput. (I did not run the test for MyRocks using two volumes)

Conclusions

  • Both MyRocks and InnoDB can scale (linearly) with additional IOPS on gp2 and io1 Amazon volumes.
  • Take into account that IOPS is not the only factor to consider. There is also throughput limit, which affects InnoDB results, so for further scaling you might need to use multiple volumes.
PREVIOUS POST
NEXT POST

Share this post

Comments (4)

  • Nils Reply

    Which instance type did you use? Something that can be confusing is that Amazon quotes EBS throughput for instance types (in mbit/s) and throughput for the different disk types (in mb/s).

    September 26, 2018 at 10:29 am
  • Mark Callaghan Reply

    My pitch for MyRocks has been better write endurance and less space used — which are important with SSD. The better write efficiency also boosts throughput with disks, but that is less important for OLTP. Thanks for making clear that better write efficiency also boost throughput with cloud storage.

    September 26, 2018 at 10:48 am
    • Vadim Tkachenko Reply

      Mark,

      Correct, the message I am trying to delivery is that MyRocks shows the better throughput on limited cloud resources, but also it is more cost efficient comparing to InnoDB

      September 26, 2018 at 8:01 pm

Leave a Reply