Scaling IO-Bound Workloads for MySQL in the CloudVadim Tkachenko
Is increasing GP2 volumes size or increasing IOPS for IO1 volumes a valid method for scaling IO-Bound workloads? In this post I’ll focus on one question: how much can we improve performance if we use faster cloud volumes? This post is a continuance of previous cloud research posts:
To recap, in Amazon EC2 we can use gp2 and io1 volumes. gp2 performance can be scaled with size, i.e for gp2 volume size of 500GB we get 1500 iops; size 1000GB – 3000 iops; and for 3334GB – 10000 iops (maximal possible value). For io1 volumes we can “buy” throughput up to 30000 iops.
So I wanted to check how both InnoDB and RocksDB storage engines perform on these volumes with different throughput.
I will use the same datasize that I used in Saving With MyRocks in The Cloud, that is sysbench-tpcc, 50 tables, 100W each, about 500GB datasize in InnoDB and 100GB in RocksDB (compressed with LZ4).
Volumes settings: gp2 volumes from 500GB (1000GB for InnoDB) to 3400GB with 100GB increments (so each increment increases throughput by 300 iops); io1 volumes: 1TB in size, iops from 1000 to 30000 with 1000 increments.
Let’s take look at the results. I will use a slightly different format than usual, but hopefully it represents the results better. You will see density throughout the plots—a higher and narrower chart represents less variance in the throughput. The plot represents the distribution of the throughput.
Results on GP2 volumes:
It’s quite interesting to see how the result scales with better IO throughput. InnoDB does not improve its throughput after gp2 size 2600GB, while MyRocks continues to scale linearly. The problem with MyRocks is that there is a lot of variance in throughput (I will show a one second resolution chart).
Results on IO1 volumes
Here MyRocks again shows an impressive growth as as we add more IO capacity, but also shows a lot of variance on high capacity volumes.
Let’s compare how engines perform with one second resolution. GP2 volume, 3400GB:
IO1 volume, 30000 iops:
So for MyRocks there seems to be periodical background activity, which does not allow it to achieve a stable throughput.
Raw results, if you’d like to review them, can be found here: https://github.com/Percona-Lab-results/201808-rocksdb-cloudio
If you are looking to improve throughput in IO-bound workloads, either increasing GP2 volumes size or increasing IOPS for IO1 volumes is a valid method, especially for the MyRocks engine.
You May Also Like
Raw MySQL query logs contain a lot of information about query execution, which can be useful for performance analysis and other purposes. Read Percona CEO Peter Zaitsev’s blog for a primer on the benefits of analyzing raw MySQL log files.
A non-responsive or low-performance database can act as a roadblock to meeting your business goals. Our solution brief, Always Online, Critical Website with Percona XtraDB Cluster, outlines a way to ensure database replication and multi-master capabilities to improve and protect the availability of your data.