MySQL Benchmarking: A Matchup of AWS SSD Drives
Recently, Amazon Web Services [AWS] began offering different types of EBS drives. Apart from the magnetic EBS drive, there are now two types of Amazon AWS SSD drives: an EBS General Purpose SSD drive and a Provisioned IOPS SSD drive.
We thought it would be interesting to perform an ssd drives benchmark for these two drives for MySQL storage.
For the MySQL benchmarking, we used Sysbench. The setup instructions and methodology for running the benchmark are explained here in this post titled Using Sysbench to benchmark MySQL. Our setup for these benchmarks is similar to that of our previous post, except that the storage used is EBS instead of instance store.
Here are the specifications of the system under test:
-
Machine: AWS m3.large instance (64 Bit, paravirtual, 2 vCPUs, 7.5GB RAM)
-
OS: Ubuntu 14.04 LTS (3.13.0-24-generic)
-
MySQL Version: 5.5.35
-
Sysbench Version: 0.4.12
EBS Magnetic Drive
Using a magnetic drive provides the lowest cost per GB compared to all other types of EBS storage drives. Magnetic volumes are at approximately 100 IOPS on average, with an ability to burst to hundreds of IOPS.
We ran both benchmarks with and without the optimizations applied. The optimization applied is explained in detail here. Following are the results of the benchmarks run on the EBS magnetic drive. We see here that TPS deteriorates when the table size is increased. For small-sized tables, we see that TPS is really good and comparable to EBS SSD-backed drives.
Provisioned IOPS (SSD) EBS Drives
Here, the EBS drive comes with a consistent IOPS. Even though they cost slightly more than a general purpose SSD, they are recommended for I/O intensive workloads, like databases. Provisioned AWS EBS IOPS volumes support up to 30 IOPS per GB. For purposes of our benchmarking, we used an EBS drive of 20GB, and we got an IOPS of 600.
Here are the results of the benchmarks. We found that performance is consistent even for larger-sized tables when optimization is used. If an Amazon EBS drive of higher volume is used, say 100GB, then we can achieve an approximate IOPS of 3000.
Another metric that we found very interesting is the 95 percentile of the response time. Simply put, it means that out of all the samples 95% of the response time is equal to or less than the 95-percentile value.
Here is a comparison.
We recognize that there is a dramatical increase in the response value when the number of rows in the table reaches 50 million, which results in approximately 12GB of disk space.
EBS General Purpose (SSD) Volumes
For the EBS General Purpose drive, we repeated the benchmarking experiments that were applied for the Provisioned IOPS drive as explained in the previous section.
We found that for the first few iterations the performance was very good, even much better than the EBS with the Provisioned IOPS drive. But, in later iterations, the performance drastically deteriorated.
The CPU utilization showed approximately 45% of CPU near the end of experiment. The results indicate that there must be some kind of disk I/O throttling involved.
Here are the results of transactions per seconds (TPS). We can see that for the table of 50000 rows, the TPS is drastically reduced after the fifth iteration when no optimization is used. When optimization is used, the TPS is very low after the fourth iteration.
Following is a graph of TPS before and after we started observing deterioration in performance.
There you have it. A good comparison of the two AWS EBS drives. We want to learn what you think. Share your experiences using the General Purpose EBS and Provisioned IOPS EBS drives by dropping a comment below.
