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 | |
READ | ||||||
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