MySQL on Amazon RDS part 2: Determining Peak Throughput

This is a continuation of my series of benchmark posts comparing Amazon RDS to a server running on Amazon EC2. Upcoming posts (probably 6 or 8 in total) will extend the scope of the benchmark to include data on our Dell r900 with traditional hard drives in RAID10, and a server in the Joyent cloud. As a reminder, my goal was to run a long-term benchmark and see how the instance performed over time. Can it sustain performance over a several-day period of intense workload? The first step was to determine the number of threads that should be used for the benchmark.

To gauge this, I ran a series of 60-second benchmarks on the RDS server, and extracted the transactions per second from them, then used the peak throughput as my target configuration. The benchmark was sysbench oltp complex, with 400 million rows (88GB of data and indexes, which is larger than memory in all of the servers I benchmarked). Here are the results:

#Threads    #TPS
       8  319.47
      16  496.63
      32  885.46
      64 1254.46
     128  348.32
     256  194.67

With that as a rough guide, I ran another series of 60-second benchmarks, with the following thread counts:

#Threads    #TPS
       1   44.26
       2   78.66
       4  183.47
       8  316.79
      16  527.50
      24  576.44
      32  671.08
      40  796.30
      48 1128.46
      56 1278.88
      64 1333.63
      72 1312.06
      80 1203.37
      88  900.18
      96  733.57
     104  655.50
     112  567.87

The following is a chart of both benchmark runs.

Now, you may ask the question, is a duration of 60 seconds a good way to decide on a number of threads for a benchmark that runs for a long time? I answered that in my previous post on choosing a good benchmark length. The answer is no, 60 seconds is not enough to decide. However, while testing a variety of setups, I actually ran some other thread counts for durations of a few hours at a time. For example, I benchmarked some machines at up to 256 threads for a few hours.

I did not prepare publishable reports on all of the benchmarks I ran. While running the benchmarks, I gathered many gigabytes of performance and diagnostic metrics, and ensuring that it is ready to publish on this blog takes a lot of care. Instead, during the benchmarks I performed live bottleneck analysis, and observed data such as snapshots of the processlist and status counters, and created one-off graphs of various metrics, to determine how the machines and configurations were behaving. Based on my observations (not my formal analysis), I thought that 64 threads was a good balance between over-working some machines and under-working others. We have to find something in the middle so that we can benchmark all systems at the same number of threads, or the benchmarks will not be apples-to-apples.

It is important to note that these are the results I achieved on this RDS server, from this EC2 “driver” machine, at this specific point in time. There will be variations between machines and from time to time, so your benchmark mileage may vary.

Based on these results, I decided to benchmark the system at the RDS server’s peak short-term throughput of 64 threads and see how things went in longer benchmarks. The next blog post in this series will discuss the results.