Your submission was sent successfully! Close

  1. Blog
  2. Article

Adam Israel
on 22 October 2015

MySQL performance tuning between disk types


We sat down with the intent to benchmark various flavors of MySQL (Oracle MySQL, MariaDB, and Percona) on Amazon EC2 in order to measure the difference between the three on identical hardware, backed by spinning rust and SSD storage. The wheels on that bus came off quickly, though, as we realized there was no way to run these tests in a repeatable fashion.

The MySQL charm – a small collection of code that models how software should be installed and configured – didn’t have the ability to move MySQL’s data directory away from the default /var/lib/mysql, and in the instance type we were testing with, the SSD was allocated to /mnt.

Our focus shifted to benchmarking the model, rather than the hardware. We can scale up the hardware, but it’s not all that useful unless the software is capable of taking advantage of it.

The Setup

Cloud: Amazon EC2

Instance type: m3.2xlarge

  • 8 cores
  • 2600 CPU power
  • 30720M ram
  • 32G root-disk
  • EBS vs SSD

We chose to run the OLTP.lua benchmark included with sysbench provided by Percona.

This benchmark is a more realistic test of database performance, testing a workload across multiple tables. For our purposes, we chose to allocate 25 tables with 1M rows each before each series of tests.


When originally trying to benchmark different flavours of MySQL (Oracle MySQL, Percona, and MariaDB) I noticed that things that should have made a difference in performance showed negligible increase. Things like switching from spinning rust to SSD made seemingly no difference, so I dug into what the Juju charm’s tuning-level was doing.

“Safest” vs “Fast”

It’s often easier (and cheaper) to throw hardware at a bottleneck and call it a day. In my experience, though, this is a losing battle. Bottlenecks shift. Fix one logjam and another appears. Poorly tuned software always trumps hardware.

With that in mind, we looked at how we could test various configurations in a repeatable fashion. Luckily, the charm offers three built-in levels of tuning: safest, fast, and unsafe. The idea behind this is to give people larger knobs to twiddle with without inundating with configuration options. We decided to skip ‘unsafe’ and focus on ‘safest’ and ‘fast’. Switching from ‘safest’ (the default) to ‘fast’ breaks full ACID compliance – changing innodb_flush_log_at_trx_commit from 1 to 2, and sync_binlog from 1 to 0 – potentially losing up to a second worth of transaction if the operating system were to crash or the machine loses power.

This is key; there are plenty of options you could tweak for speed but you probably shouldn’t. The “fast” option is a compromise on data safety, and should only be used if you understand the risks. Still, it’s worth comparing the two so the performance impact can be understood.


By default, the MySQL datadir is located in /var/lib/mysql. When using an instance type that includes SSD block storage, though, you need to relocate the datadir under /mnt. In order to do that, I added a configuration option to the MySQL charm to support this relocation. The modified charm is available here.

Innodb and disk space, oh my.

Sysbench’s usual pattern is to prepare (iow: insert a bunch of test data), run, and cleanup (drop the tables). The first iterations of my testing prepared and cleaned up the test data between every run iteration. I feel like this provides a better test story, but it presented two issues. First, the time spent to create and drop the test data is significantly longer than running the test itself. Second, I ran out of disk space, unrecoverable, thanks to /var/lib/mysql/ibdata1. This may suggesting an issue with sysbench itself not properly cleaning up. To work around the issue, though, I added options to the mysql-benchmark benchmark’s oltp action, so that the test data can be prepared and cleaned up once instead of on every iteration.

The Results


Threads Tuning Level Avg. Time per request (ms) 95th Percentile Latency (ms) Transactions/sec
16 Safest 43.13 47.57 406.84
16 Fast 45.42 21.12 350.94
32 Safest 72.99 69.03 357.02
32 Fast 85.54 70.85 377.25
64 Safest 182.37 321.28 349.00
64 Fast 194.34 398.50 333.37


Threads Tuning Level Avg. Time per request (ms) 95th Percentile Latency (ms) Transactions/sec
16 Safest 70.67 96.48 226.96
16 Fast 15.24 15.81 1057.48
32 Safest 133.85 182.97 239.77
32 Fast 23.63 36.92 1347.88
64 Safest 259.57 339.11 245.91
64 Fast 52.89 67.02 1191.11


Threads Tuning Level Avg. Time per request (ms) 95th Percentile Latency (ms) Transactions/sec
16 Safest 75.76 129.64 211.04
16 Fast 10.55 16.62 1503.85
32 Safest 123.19 153.89 259.40
32 Fast 23.28 25.36 1372.31
64 Safest 259.39 360.86 245.97
64 Fast 50.02 63.28 1274.63

Magical Unicorns chasing rainbows

We’ve seen that MySQL performance relies heavily on disk throughput. I can’t think of a better test of that raw number than running dd against the block devices themselves. These raw numbers represent the peak of what the block device is capable of.

22.8 MB/s 131 MB/s 386 MB/s

We see almost a 575% increase in performance by switching from EBS to SSD, and a 294% increase from SSD to IOPS (a whopping 1,692% improvement over EBS). That’s awesome.

These numbers should represent the peak throughput of each device, so our ideal benchmark of MySQL (or any service) could be compared to this as a comparison of efficiency.

It’s hard to draw a conclusion about how this translates to performance in MySQL, but we do see a decreases in the average time per request between EBS and SSD, and very little change between SSD and IOPS. # Caveat

The m2 instance automatically formatted and mounted the SSD block storage to /mnt, but the i2 instance did not. See this SO question for more information.


We saw impressive improvements upgrading from EBS to SSD. We expected a similar jump in performance when upgrading from SSD to IOPS, based on the results of dd, but that didn’t happen, suggesting we moved disk io and we’re hitting other bottlenecks in the OS, MySQL or its configuration.

While these results are Amazon EC2-specific, our code can be run in any cloud, including your own hardware. We encourage you to try these benchmarks on Microsoft Azure, Google Cloud Platform, Rackspace, OVH, or Scaleway. The permutations of clouds and disk options becomes quick complex, which is why we’re taking the time to make these sorts of benchmarks generic and consumable by everyone. For more information on how to submit these tests, see this page.


Originally posted at

Related posts

Michael C. Jaeger
4 September 2023

Efficiency redefined: Discover the power of software operators during Operator Day, co-located at KubeCon North America 2023

Charms Article

The 7th Operator Day is a virtual event and will take place on the first day of the KubeCon week, Monday, 6 November 2023. The event is entirely virtual. You can dial in from anywhere and attend. You can register at the KubeCon NA 2023 conference or directly at the event page. ...

Michael C. Jaeger
22 June 2023

Adopting a low-ops approach with software operators

Charms Article

How enterprise DevOps teams can automate repetitive tasks while staying in control  Low-ops means minimising the effort for operating applications by automating as many tasks as possible. However, given the maximum automation, how can an SRE or DevOps engineer stay in control? Doesn’t automating tasks and being in control sound like a par ...

Michael C. Jaeger
6 January 2022

Application composability and the shipping container

Charms Article

The previous blog post talked about the composability of applications. The key element for composing applications is defining the relations between application elements. And supporting relations is one of the advantages of the Charmed Operator Framework – including its runtime, the Charmed Operator Lifecycle Manager. The shipping containe ...