We need a better benchmark

Boy am I sick or what, maybe I should see a shrink or something.  What’s the first thing that runs through my head when I wake up on a Sunday morning?  Is it thoughts of family, the days events, happy thoughts or something pleasant non technical?  No.  Its that we need a better benchmark suite for MySQL and the open source database community as a whole.  Right now the staple of our benchmarking diet is sysbench and dbt2.  While both have served their purpose they are a bit long in the tooth and do not do a good job of truly capturing what end users are doing out their.  I mean sysbench uses a single table!   Where’s the join love.  Has anyone outside of a PT expert successfuly gotten dbt2 installed?  probably not because its a pain in the rear.   Plus what the hell does box A got a TPM of 5000 vs box B’s TPM of 6000 mean?  Seriously to a client the only thing it tells is in certain OLTP like tests box B is better.  But if the client is putting together a CMS system maybe box B is better….  you just don’t know.

This is all free flow right now, I am writing as I am thinking of it, so excuse the mess.

What do we need?  A benchmark that covers the majority of the MySQL communities activities, I think we can all agree that the vast majority of MySQL are web applications.  So lets focus their.  Potentially lets base the benchmark around the big 4 cms, blogs, forums, and ecommerce.  I think this benchmark should include as many common real-world scenarios as possible.  A benchmark built for benchmarking what we see day in and day out.   The benchmark while initially could run local on the db box, but eventually  should have a web client to stress the web/app servers with database calls ( no rendering pages, just select and return data to stress php, jdbc, perl, etc ).  This is important because their is overhead with each web/app config that’s going to effect the real-world perform of the system…   The ultimate end product would be pages loaded per second.  We could customize the output to show something like :

Main Page Load: 100,000 Loads , 25 queries per load, 15 pages loaded per second
Product Listings: 50,000 Loads, 15 queries per load, 7 pages loaded per second
Forum Posts: 150,000 Loads, 5 queries per load, 50 pages loaded per second
———————–
Total Pages Loads: 300,000 , average 25 pages loaded per second.

Then break down queries maybe something like this:


Complex Self Joins: 100,000 executions , average runtime 1.5 seconds
Simple Primary Key Lookups: 200,000 executions , average runtime 0.15 seconds
Aggregate Queries: 50,000 executions , average runtime 3.15 seconds

This would certainly be helpful in tracking down performance bottlenecks in certain parts of the code. Plus we could look at more targeted benchmarks… hmmmm I need to ponder this more.

This entry was posted in mysql, performance, rant. Bookmark the permalink.

7 Responses to We need a better benchmark

  1. Xaprb says:

    Have you taken a look at Yasufumi’s tcpe benchmark? Does it address any of your needs?

  2. matt says:

    Baron,

    nope I have not… thanks for pointing it out I will take a look. Have not been able to find it, you wouldn’t happen to have a link handy.

  3. Mark Callaghan says:

    I don’t know about tpce, but here is tpcc-mysql from Yasufumi — https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql. This should replace dbt2 and is much simpler to setup/install. I gave up on dbt2.

    I have a patch that makes sysbench use predicates on secondary indexes in addition to the PK index. Otherwise it only uses the PK index. Send me email to get it.

    The wisconsin benchmark is distributed with MySQL. It needs to be run for a larger dataset to be useful. I haven’t looked at the queries in the MySQL version, but there are at least two queries in the official version that must be rewritten to work efficiently in MySQL because they do joins on non-indexed columns.

    It would be nice to have something with a more complex query workload.

  4. Xuekun says:

    A web2.0 workload would be better, since the vast majority of MySQL are web applications, :-).

    tpcc and tpce is still traditional OLTP workload, which in the same category with dbt2.

  5. Mark Callaghan says:

    What is a web 2.0 workload?

  6. Xaprb says:

    It’s a benchmark with a wet floor reflection.

  7. neel says:

    Yes! More workloads the better

    The thing about sysbench is that some transactions are really light weight, and you end up testing the network performance than server performance 🙂

    @baron, do you what license the tpcc-mysql is released under?

Comments are closed.