Introduction: Storage for MySQL on AWS
MySQL is the most popular open source database. I guesstimate that the number of installs is probably in the millions — which I attribute to the LAMP stack days of the early 2000s. Its popularity makes sense because, after all, it has been around for 28 years which tells you a lot about the stability of the product, the community behind it, and the features it includes.
In the cloud, and specifically in AWS, MySQL is a very common, performant, and stable solution, either via the Aurora MySQL compatibility, RDS, or the multiple vendors that support MySQL.
MySQL, when paired with the right storage, can reach millions of transactions. But choosing the right block storage, especially in the cloud, can be confusing since there are so many options. Add to that, the cost associated with high-end native cloud storage is very high. This is where the Lightbits cloud data platform comes in — built from the ground up to deliver SAN capabilities for the public cloud era.
In this blog, I’ll present the results of benchmark tests that I performed to compare the performance and efficiency of Lightbits storage versus AWS io2.bx for MySQL workloads.
MySQL on AWS Test Setup
In order to compare the cost and performance of storage for MySQL on AWS, we need a common workload or benchmark. In this case, I’ve used sysbench as it has been widely used for many years to test the performance of many types of databases.
On AWS, I’ve set up an R6in.16xlarge instance. This instance provides great value and performance with 64 cores, 512GB of RAM, and 100 Gbps network bandwidth. I’ve used the latest Red Hat Enterprise Linux (RHEL 8.8) AWS-supplied AMI and then later updated all the packages and installed MySQL using the RHEL repository supplied package (8.0.32).
To install the sysbench benchmark, I’ve used the code directly from the git repository and compiled it on the server itself. The git version is newer than the supplied package from RHEL and it supports the variable thread-init-timeout that can extend how long sysbench waits until all threads are ready.
Since we have plenty of cores on the R6in.16xlarge instance, and I didn’t intend to not test the network latency between the “client” (sysbench) and the “server” (MySQL), I ran sysbench on the same instance where the MySQL database ran.
Storage Layout
Since my objective was to compare two resilient storage solutions –Lightbits and io2 Block Express –I laid out 3 volumes from each solution and then created a RAID0 on top of the 3 volumes via mdadm. RAID0 is a very popular option, but one must be careful that the underlying storage must have its own resiliency – or replication – because a stripe-only solution like RAID0 does not offer protection, just performance enhancement.
Database and Workload Setup
As a reminder, the goal of this blog is to compare two types of storage in AWS – Lightbits and EBS – and how they behave when used with MySQL databases. To try and isolate the storage behavior as much as possible we need to reduce the amount of caching the database uses in order to “force” IOs to go directly to the storage subsystem. To achieve this I set the innodb_buffer_pool_size to only 2G.
You can see below other changes I made to MySQL parameters in order to impact performance in general. I placed these options in a file called mysql_perf.cnf in /etc/my.cnf.d/:
[mysqld]
innodb_flush_method=o_direct
innodb_flush_neighbors=0
innodb_io_capacity=15000
innodb_io_capacity_max=25000
innodb_adaptive_hash_index=0
innodb_log_file_size=10G
innodb_page_cleaners=8
innodb_buffer_pool_instances=16
innodb_buffer_pool_size=2G
max_prepared_stmt_count=2000000
max_connections=1000
MySQL using Lightbits Cloud Data Platform on AWS Architecture
MySQL using EBS io2 Block Express Architecture
MySQL on AWS Benchmark Results
In sysbench, like other workload generators, the type of queries you run impacts the type of IOs going to the storage —more select queries equal using more read IOs, and more insert or update queries create a more write IO workload.
Below you can see the results from the 3 types of workloads chosen and the performance difference between using Lightbits storage and EBS’s io2 Block Express.
As you can see, in the “mostly reads” benchmark, Lightbits has almost double the TPS (transactions per second) than what io2 Block express can provide. This behavior also explains the big latency difference between the two storage options.
Cost Comparison
The tests with io2 block express were done with 128k provisioned IOPS per volume (3 volumes, each 300GB in size, to create the RAID0), so the monthly calculation to run the database adds up to around $22,840. This includes the price for the EBS storage and the R6in.16x instance we used for a whole month.
If we replace the io2 block express with one of the modest-sized clusters that you can run Lightbits on (i4i.8xlarge), the price to run the MySQL database comes down to $9,950 per month. That makes Lightbits less than half the cost compared to AWS io2 Block Express, for an annual savings of $154,680 per year.
Summary
Using sysbench, our testing shows Lightbits storage outperforms io2 Block express by almost a 2x factor when running mostly select queries. Lightbits also outperformed io2 Block express with mixed workloads using inserts (new data) or update of existing data.
We’ve also demonstrated that while outperforming EBS’s io2 Block Express, the Lightbits storage solution costs 55% less than io2 Block Express.
