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.