July 26, 2014

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:

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

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.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. Baron,

    Which RDS MySQL version is this ? Also is this default settings or did you change something ?
    What instance size did you use ?

    Well I guess your main point with this benchmark is to show how much performance drops down with high number of threads this is why instance size is lesser question.

    I also assume this is default sysbench oltp settings which uses skewed distribution, so we’re not speaking bout uniform access to 88G of data in this case.

  2. Peter, it’s 5.1.50, to match the client’s version of MySQL; they are very careful about upgrades. The RDS instance was a Quadruple Extra Large DB Instance. I used default settings for RDS, and default sysbench oltp settings. The default settings for RDS are pretty reasonable, although in later blog posts I will discuss how they differed from the client’s settings (there are some things like innodb_flush_log_at_trx_commit which vary). Here are the settings, for the record:

  3. Do you have any benchmarks on m1.small instances? There’s a lot of tests on large instances, but no one tested small instances.

  4. No, but if you would like to hire me to do those, I certainly can :-) I don’t think anyone looking for performance is going to size the machine down, so it seems like a very odd thing to benchmark.

Speak Your Mind

*