Thursday, November 27, 2008

Cracking the Tokutek contest with MySQL 5.1

Hello, blog! Long time without posting. Here I am again.
My attention was caught a few days ago by a contest posted by Tokutek.
This is a company that makes a proprietary engine for MySQL. In their contest, they claim that it takes 20 days to insert 1 billion records into a InnoDB table, and that MyISAM is not even up to the task. In their example, InnoDB inserts 404 rows per second and MyISAM died after a few days of inserting 93 rows per second.
It looks farfetched to me. Surely you can do better than that.
Now, I don't have a quad 3.16Ghz Xeon server with 16GB RAM available like the one used by Tokutek, and so I tried with a modest quad 2.7Ghz AMD, 8GB RAM server. The problem is mostly the same. Once your index becomes bigger than the available RAM, you are in the glue. The only difference is that my server was slower.
I didn't try with InnoDB, but I tackled the MyISAM case at once. Using MySQL 5.1, I just added 400 partitions to the contest table, partitioning by range of 50 million records. And I made a simple Perl script that produces INSERT statements with 50,000 rows each.
The result? I inserted the rows into a MyISAM table at an average rate of 8,800 rows per second, with peaks of 25,000 rps. The speed did never drop below 5,000 rps anyway. The whole operation took 31.5 hours. That's much less than 20 days, and surely a hell of a lot less than the projected 167 days that should be needed for MyISAM according to the contest slides.
Should I claim the ridiculous contest prize? (a $100 Starbucks card? Gimme a break!)
No. I just wanted to make a point. The above instructions are enough for anyone savvy to reproduce the case. If the prize changes, and becomes a $2,000 donation to the FSF, for example, I will release the code that I used. In the meantime, enjoy your own tests!

While I was testing, I saw that MySQL released 5.1.30-GA. Great!. I used 5.1.29 for my experiment. Had I known about the release, I would have waited two days. Anyway, if you believe TokuTek benchmarks, using 5.1 partitions will result in a performance 200 times better than the original. There must be something wrong in their method.


knmeister said...

This benchmark contest looks rigged.

Note this:

price = customerid/100 + Random 0-4.99

That makes price/customer values some kind of sparse matrix covering only 0.5% of the phase space. And lo and behold, the three indexes of the table all include price and customerid.

Their "fractal tree" algorithm probably optimizes this particular case... which is cool, but probably not very general, and it reflects poorly trying to sneak this in like this.

knmeister said...

Cool idea with the partitioning btw, that is actually an excellent example of a good use of partitioning.

Peter said...

In general it does not make any sense to have the benchmark defined only in terms of insert speed... In this case evil tricks like 400 partitions will do the job. You need to define benchmark in terms of both inserts and read performance to make sure it matches real life needs.

Also the data distribution in indexes is important too.

arjenAU said...

Yea my calculation after some tests was about 27 hrs.
I didn't have a 5.1 so was doing it without partitions, and that has some practical issues after some time, even optimising various things.

With large multi-row insert statements you want to have a large bulk_insert_buffer_size, and for MyISAM you want to set DELAY_KEY_WRITE=1 in the table so the server doesn't flush index pages to disk after each key write. It'll slow down a bit at some point as groups of pages need to get flushed, but particularly early on you gain a lot.
And with partitioning, you get some additional advantage with this as only a small subset of the index pages are needed as you move from one partition to the next - you may only need about 100 partitions or even less, and fit all index pages of one partition in memory - then you can fly.

But this is just some guru indulgence, the contest as such is uninteresting (not even talking about the silly prize) and does not really say much about Tokutek's expertise on MySQL.

Dave said...

"The Fake Amelia posts about cracking the Tokutek contest with MySQL 5.1:..." -- Log Buffer #125