External storage/Evaluating innodb

Notes from Asher (2012)

I recently completed some benchmarks comparing the existing myisam based external store against an all innodb version of the same. Comparative tests were run on es1001 for myisam and es1003 for innodb.

The downside for innodb is less efficient disk space utilization. The sum of all revisions for all wikis grew from 4.3TB to 7.3TB. I tested the compact as well as the newer dynamic and compressed row formats and stuck with compact. Dynamic improves blob storage under certain schema conditions but used slightly more space for this. Compressed offered savings of just a few percent over compact, with the overhead of compressing and uncompressing pages of data the have already been gzip compressed by mediawiki. We currently have a single shard of ES servers with 10.8TB of usable disk space per node, so the additional space overhead shouldn't be an issue so long as a new shard is deployed in the coming months. A second hardware shard has been ordered which should be online in 2-3 weeks.

Benchmarks were run with read queries in the style of "SELECT blob_text from blobs_clusterNN WHERE blob_id = NNNNN" against the enwiki blobs_cluster22 and blobs_cluster23 tables (well over 1TB of data) where blob id's were generated as random integers between 1 and max(blob_id), in groups of 100k queries. I then ran variable numbers of concurrent query threads, with each thread using a different set of 100k random blob_id's. That was then repeated over 10 runs, with the first started directly after server boot. For the last run, I set long_query_time = 0 in order to capture timing data for every query. Replication was active during all tests for a steady trickle of concurrent writes.

When running with six concurrent read threads, the innodb system completed 6M random queries at an average rate of 440.92/sec while the original got 308.656/sec and finished the test around 90 minutes later. For the first run of 600k after a reboot with cold caches, the innodb system did 387.096/sec vs 286.53/sec for myisam.

Percentiles times for the last 600k queries, in seconds:

Percentile    MyISAM        InnoDB
50            0.014336        0.000083
95            0.036203        0.008352
99            0.053055        0.020705
99.99       0.199825        0.069474

This should dispel as myth the idea that myisam is faster in this particular use case. I think the disk space penalty is a worthy sacrifice in exchange for safer storage and crash recovery alone.

- Asher