Running TPC-C on MySQL/RDS

I recently came across a TPC-C benchmark results held on MySQL based RDS databases. You can see it here. I think the results may bring light to many questions concerning MySQL scalability in general and RDS scalability in particular. (For disclosure, I'm working for ScaleBase where we run an internal scale out TPC-C benchmark these days, and will publish results soon).

TPC-C

TPC-C is a standard database benchmark, used to measure databases. The database vendors invest big bucks in running this test, and showing off which database is faster, and can scale better.

It is a write intensive test, so it doesn’t necessarily reflect the behavior of the database in your application. But it does give some very important insights on what you can expect from your database under heavy load.

The Benchmark Process

First of all, I have some comments for the benchmark method itself.

1. Generally - the benchmarks were held in an orderly fashion and in a rather methodological way – which increases the credibility of the results.
2. The benchmark generator client was tpcc-mysql which is an open-source implementation provided by Percona. It’s a good implementation, although I think DBT-2, provided by Apache, is more widely used.
3. Missing is the mix weights of the different TPC-C transactions in the benchmark.  The TPC-C benchmark contains 6 types of transactions (“new order”, “delivery”, “stock query”, etc.) and the mix weights are important.
4. The benchmark focused on throughput, not latency. I think that although TPC-C is mostly about throughput (transactions per minute) it’s recommended to address the latency (“avg response time” for example) as well. 
5. TPC-C “number of warehouses” determines the size of the database being tests. Number of warehouses ranged between 1 and 32 in this benchmark. In MBs we’re talking 100MB-3GB. That’s usually a small database, and I would be interested in seeing how the benchmark ranks with 1000 warehouses (around 100 GB) or even more.
6. The entire range RDS instance types were examined, starting from small to quadruple Extra Large (4XL). Some very interesting results turned out, which will be the focus of this blog post.

Results Analysis

The benchmark results are surprising.

1. With hardly any dependency on the database size, MySQL reaches its optimal throughput at around 64 concurrent users. Anything above that causes throughput degradation.
2. Throughput is improving as machines get stronger. However, there is a sweet-spot, a point where adding hardware doesn’t help performance. The sweet spot is around the XL machine, which reaches a throughput of around 7000 tpm. 2XL and 4XL machines don’t improve this limit.

It would seem that the Scale-Up scalability approach is rather limited, and bounded. And unfortunately, has some rather close bounds.

So, what’s the bottleneck?

• CPU is unlikely candidate, as CPU power doubles on 2XL and quadruple on 4XL machines.
• I/O is also an unlikely candidate. Since machines memory (RAM) doubles on 2XL and quadruple on 4XL machines. More RAM means more database cache buffers, thus reduced I/O.
• Concurrency? Number of concurrent users? Well we saw that optimal throughput is achieved with around 64 concurrent sessions on the database. See figure 4b in the benchmark report. While with 1 user the throughput 1,000 transactions per user, with 256 users it drops to 1 transaction per user!

Results Explanation

It definitely weird that when more parallel users are added, we get performance degradation – a lower throughput than the one we have with fewer parallel users.

Well, the bottleneck is the database server itself. A database is a complex sophisticated machine. With tons of tasks to perform in each and every millisecond it runs. For each query, the database needs to parse, optimize, find an execution plan, execute it, and manage transaction logs, transaction isolation and row level locks.

Consider the following example. A simple update command needs an execution plan to get the qualifying rows to update and then, reading those rows, lock each and every row. If the command updates 10k rows – this can take a while. Then, each update is executed on the tables, on each of the relevant indexes of that table, and also written to the transaction log file. And that’s for just a simple update.

Or another example - A complex query takes a long time to run. Even 1 second is a lot of time in a highly loaded database. During its run, rows from source table(s) of that query are updated (or added, or deleted). The database must maintain “Isolation level” (see our isolation level blog). Meaning that the user’s result must be the snapshot of the query as it was when the query started. New values in a row that was updated after the query began, even if committed, should not be part of the result. Rather, the database should go and find the “old snapshot” of the row, meaning the way the row looked at the beginning of the query. InnoDB stores information about old versions of rows in its tablespace. This information is stored in a data structure called a rollback segment. And if we get back to the previous example – here’s another task the database has to do – update the rollback segment.

We must remember, every command issued to the database, actually generates dozens of recursive commands inside the database engine. When sessions or users concurrency goes up, load inside the database engine increases exponentially. The database is a great machine, but it has its limits.

So What Do We Do?

So, we understand the database is crowded, and has hardware sweet-spots, which complicates the Scale Up solution – which is expensive, and doesn’t give the required performance edge. Even specialized databases have their limit, and while the sweet-spot changes, every database has one.

There are allot of possible solutions to this problem – adding a caching layer is a must, to decrease the number of database hits, and any other action that can reduce the number of hits on the database (like NoSQL solutions) is welcomed.

But the actual database solution is scale-out. Instead of 1 database engine, we’ll have several, say 10. Instead of 128 concurrent users or even 256 concurrent users (that according to the TPC-C benchmark bring worst results), we’ll have 10 databases with 26 users on each, and each database can reach 64 users (up to 640 concurrent users). And of course, if needed, we can add more databases to handle the increased scale.

It will be interesting to see how scale out solutions for databases handle TPC-C. 

Comments

[Jamie]

There is an interesting paper going in the same direction but for a different benchmark (TPC-W) published in Eurosys 2011 you might give it a look here http://eurosys2011.cs.uni-salzburg.at/pdf/eurosys2011-salomie.pdf

[mxx]

Since this test was done in a 'public cloud' where one doesn't know or control the environment, wouldn't it be prudent to do multiple tests at various times/days to make sure those are consistent and accurate results?

[Liran Zelkha]

Hi mxx, I agree - probably the TPCC test should have run in various different times (or maybe even on different regions)

[Fred Holahan]

An excellent research paper on the topic of database scalability, entitled "OLTP through the looking glass, and what we found there", can be viewed here http://hstore.cs.brown.edu/publications/. Briefly, the paper examines the breakdown and instruction count of various database activities for the TPC-C's New Order transaction. The results are highly illuminating - less than 10% of the benchmarked RDBMS's total instruction count was applied to "useful work" (i.e., actually processing transactions). The remaining instructions were consumed by overhead activities such as buffer management, latching, locking and logging.

The researchers conclude that, unless one removes all of the overhead-producing components identified above, the performance and scale of the RDBMS can only be incrementally improved. Thus, enhancing index performance or even running the entire database in RAM may help incrementally, but won't deliver significant gains gains (hence, running MySQL on SSDs won't deliver big wins). And adding a scale-out layer on top of a cluster of "traditional" database instances only masks the fundamental issues while increasing operational complexity.

New SQL products like VoltDB (disclosure: I am an employee) aim to deliver magnitude RDBMS performance and scaling improvements without giving up popular benefits like SQL semantics and transaction consistency. These new products strip away traditional RDBMS overheads and exploit modern hardware and scale-out infrastructures. An example benchmark of VoltDB can be found here: http://www.mysqlperformanceblog.com/2011/02/28/is-voltdb-really-as-scalable-as-they-claim/ (it's not based on TPC-C, but you may find it helpful all the same).

[Liran Zelkha]

Hi Fred,

Thanks for the comment. As I said in the post - it will be very interesting to see how scale-out architectures handle TPCC - the "ultimate" RDBMS benchmark (well, until something more fitting scale-out architectures come along)