MySQL 5.6 memcached plugin performance benchmarks

MySQL 5.6 introduces a new feature - you can talk to the InnoDB enginge using the memcached protocol, which is supposed to be faster for simple get/set operations based on a key. For an introduction, refer to this MySQL via Memcache tutorial and the MySQL manual. (The basic idea is that you create a special database in which you describe your tables that should be "exported" to be accessible through the memcached plugin and then enable the plugin itself.)

I didn't find any ballpark figures about how much improvement the new memcached backend might actually offer for applications, so I ran a few benchmarks myself. I compared it to a straightforward way of using an InnoDB table as a key-value store via SQL (either with DELETE/INSERT or REPLACE) and also (just for fun) against a real memcached backend (which of course unlike MySQL offers no persistence). The versions I used specifically were InnoDB 5.6.14 and memcached 1.4.5. The benchmark client wrote about 270000 key-value pairs in random order to the test.demo_test table shipped with the plugin as an example. Each key consisted of 10 and each value of 176 bytes. I also ran another test with 4 such clients running in parallel (on a quad-core AMD Phenom 9650 machine).

I also tested two ways of setting the keys - either with one operation per key or with a batch operation set_multi (this is something offered by the Cache::Memcached::Fast client, not in the memcached protocol, as far as I'm aware).

Note that the DELETE/INSERT approach is not recommended as it is not only slow, but also causes errors and/or deadlocked transactions on collisions (I didn't bother implementing transaction retry).

1 client 4 clients
WRITE ms/op ops/client/s ms/op ops/client/s total ops/s
DELETE+INSERT 0.47875 2089 0.78324 1277 5107 (not safe)
REPLACE 0.28412 3520 0.55597 1799 7195
InnoDB set 0.26271 3806 0.51304 1949 7797
InnoDB set_multi 0.12020 8319 0.47455 2107 8429
memcached set 0.12068 8286 0.11235 8900 35602
memcached set_multi 0.04038 24766 0.14384 6952 27809


SELECT 0.16261 6150 0.13342 7495 29981
InnoDB get 0.09920 10080 0.07527 13286 53142
memcached get 0.06140 16288 0.03447 29010 116040

My conclusion is that the new memcached backend can improve MySQL read performance quite a lot (77% more reads per second) and improve write performance a little (8%) compared to a pre-5.6 approach using SELECT/REPLACE. The percentual speedup for reads may seem big, but it is negligible in absolute terms (retrieving single rows through key lookup was already very fast). So switching to the new backend would only make sense for applications that experience really heavy read loads; it won't improve response time of an average application which is mostly sitting around idle. And of course if need no persistence nor SQL access to your cache tables (which sounds nice for performing bulk updates/deletes as may be), then the good old memcached is a clear winner.

Download my benchmark script and the results in spreadsheet format:

No comments:

Post a Comment