A common practice to offload traffic from MySQL 5.6 is to use a caching layer to store expensive result sets or objects. Some typical use cases include:
- Complicated query result set (search results, recent users, recent posts, etc)
- Full page output (relatively static pages)
- Full objects (user or cart object built from several queries)
- Infrequently changing data (configurations, etc)
In pseudo-code, here is the basic approach:
1 2 3 4 5 6 7 8 9 | data = fetchCache(key) if (data) { return data } data = callExpensiveFunction(params) storeCache(data, key) return data |
Memcached is a very popular (and proven) option used in production as a caching layer. While very fast, one major potential shortcoming of memcached is that it is not persistent. While a common design consideration when using a cache layer is that “data in cache may go away at any point”, this can result in painful warmup time and/or costly cache stampedes.
Cache stampedes can be mitigated through application approaches (semaphores, pre-expiring and populating, etc), but those approaches are more geared towards single key expiration or eviction. However, they can’t help overall warmup time when the entire cache is cleared (think restarting a memcache node). This is where a persistent cache can be invaluable.
Enter MySQL 5.6 with the memcached plugin…
As part of the standard MySQL 5.6 GA distribution, there is a memcached plugin included in the base plugin directory (/usr/lib64/mysql/plugin/libmemcached.so) that can be stopped and started at runtime. In a nutshell, here is how one would start the memcached plugin:
mysql> install plugin daemon_memcached soname 'libmemcached.so';
In an effort to not re-invent the wheel, here is a link to the full documentation for setting up the plugin:
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-setup.html
As a quick benchmark, I ran some batches of fetch and store against both a standard memcached instance and a minimally tuned MySQL 5.6 instance running the memcached plugin. Here are some details about the test:
- Minimal hardware (vBox instances on MacBook Pro)
- Centos 6.4
- Single core VM
- 528M RAM
- Host-Only network
- 1 Box with http/php, 1 box with memcache or mysql started
- PHP script
- Zend framework
- libmemcached PECL module
- Zend_Cache_Backend_Libmemcached
Here is the rough code for this benchmark:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | // Identical config/code for memcached vs InnoDB $frontendOpts = array( 'caching' => true, 'lifetime' => 3600, 'automatic_serialization' => true ); $memcacheOpts = array( 'servers' =>array( array( 'host' => '192.168.57.51', 'port' => 11211, 'weight' => 1, ) ), 'client' => array( 'compression' => true, ), ); $cache = Zend_Cache::factory('Core', 'Libmemcached', $frontendOpts, $memcacheOpts); $timer->start(); for ($i = 0; $i < 100000; $i++) { $cache->save(new Object(), "key_$i"); } $totalTimeStore = $timer->stop(); $avgTimeStore = $totalTimeStore / 100000; $timer->start(); for ($i = 0; $i < 10; $i++) { for ($i = 0; $i < 100000; $i++) { $obj = $cache->load("key_$i"); } } $totalTimeFetch = $timer->stop(); $avgTimeFetch = $totalTimeFetch / 1000000; |
While this benchmark doesn’t show any multi-threading or other advanced operation, it is using identical code to eliminate variation due to client libraries. The only change between runs is on the remote server (stop/start memcached, stop/start plugin).
As expected, there is a slowdown for write operations when using the InnoDB version. But there is also a slight increase in the average fetch time. Here are the raw results from this test run (100,000 store operations, 1,000,000 fetch operations):
Standard Memcache:
Storing [100,000] items:
60486 ms total
0.60486 ms per/cmd
0.586 ms min per/cmd
0.805 ms max per/cmd
0.219 ms range per/cmdFetching [1,000,000] items:
288257 ms total
0.288257 ms per/cmd
0.2843 ms min per/cmd
0.3026 ms max per/cmd
0.0183 ms range per/cmd
InnoDB Memcache:
Storing [100,000] items:
233863 ms total
2.33863 ms per/cmd
1.449 ms min per/cmd
7.324 ms max per/cmd
5.875 ms range per/cmdFetching [1,000,000] items:
347181 ms total
0.347181 ms per/cmd
0.3208 ms min per/cmd
0.4159 ms max per/cmd
0.0951 ms range per/cmd
InnoDB MySQL Select (same table):
Fetching [1,000,000] items:
441573 ms total
0.441573 ms per/cmd
0.4327 ms min per/cmd
0.5129 ms max per/cmd
0.0802 ms range per/cmd
Keep in mind that the entire data set fits into the buffer pool, so there are no reads from disk. However, there is write activity stemming from the fact that this is using InnoDB under the hood (redo logs, etc).
Based on the above numbers, here are the relative differences:
- InnoDB store operation was 280% higher (~1.73 ms/op)
- InnoDB fetch operation was 20% higher (~.06 ms/op)
- MySQL Select showed 27% increase over InnoDB fetch (~.09 ms/op)
- This replaced $cache->load() with $db->query(“SELECT * FROM memcached.container WHERE id=’key_id'”);
- id is PK of the container table
While there are increases in both operations, there are some tradeoffs to consider:
- Cost of additional memcached hardware
- Cost of operations time to maintain an additional system
- Impact of warmup time to application
- Cost of disk space on database server
Now, there are definitely other NoSQL options for persistent cache out there (Redis, Couchbase, etc), but they are outside the scope of this investigation and would require different client libraries and benchmark methodology.
My goal here was to compare a transparent switch (in terms of code) and experiment with the memcache plugin. Even the use of HandlerSocket would require coding changes (which is why it was also left out of the discussion).
With mysqlnd it can even be used without directly using the memcached API, it can be configured to translate simple SELECT queries to memcached.
http://www.php.net/manual/en/intro.mysqlnd-memcache.php
There are few questions about this new features that I wish you can answer me.
I am not able to find any information how memcached cache time expiry being apply into MySQL 5.6 memcached. e.g. if i want to store a cache object for only 12 hours. In memcached side I can set cache life time as 12hours. Can this apply to the MySQL innodb memcache? i.e. will the cache being delete off innodb table after 12hours or they remain permanent? for doing cache clearing off table do i need to do it on SQL side.
Also can you also do a comparison between performance of 5.6 memcached innodb write/read vs 5.6 innodb write/read with no memcached layer? Currently I am considering any possible usage of this new feature and so far I think it may able to apply on session codes.
Thanks
1) What about SQL INSERT? How does it compare to InnoDB Memcache storing?
2) The gain in performance from SQL SELECT to InnoDB Memcache fetching seems very small. Bypassing SQL parsing and opening/closing tables only gains 20% in performance? Why? In comparison Handlersocket does similar things as the memcache plugin and managed to become several times faster than the SQL interface.
3) Can you also benchmark Handlersocket and see how it compares to Innodb memcache plugin?
Mike,
I would point out something else. For many applications the memcache is used as a cache because of SQL overhead to begin with. In some cases indeed we have complex objects cached, in others what is cached is basically result of simple lookup queries. This means in number of cases using memcached access may simply allow you to get the data from database directly bypassing the need of complicated cache setup.
To check it though you need to test how it scales with high concurrency – besides raw performance not suffering badly from large number of concurrent request is another memcache advantage.
According to
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html
there are some caching settings to play with:
“Table cache_policies specifies whether to use InnoDB as the data store of memcached (innodb_only), or to use the traditional memcached engine as the backstore (cache-only), or both (caching). In the last case, if memcached cannot find a key in memory, it searches for the value in an InnoDB table.”
I’d like to see the results with all settings.
Thanks for the feedback everyone! There are indeed several different options that can impact the performance of the plugin and I plan to work on a follow up post showing differences with these options. These include the cache_policy, the r/w_batch_size and others. While they do impact performance, they can also have an impact on behavior so that is something that needs to be considered.
I’ll also highlight some of the differences between this plugin and HandlerSocket (as it was the mentioned a few times). I left it out of this post as I was looking at drop in replacements (both in terms of functionality and code).
Hrvoje, I would like to see that as well, in my mind, the cache settings allow for two distinctly different modes of operation and use cases. ‘innodb_only’ appears to be handlersocket like, where it’s simply a nosql protocol that accesses existing innodb buffer pool and real time data (good for quick simple lookups where the SQL overhead is significant as Peter mentioned), whereas ‘cache-only’ seems to be the traditional memcached behavior, where a cache of data is created, derived from the db (good for all the use cases mentioned at the top of the article, if it makes sense to have them on the same box). ‘caching’ covers both (although favors traditional mode as it goes to the cache first). The default is ‘innodb_only’ which is something to keep in mind.
Mike, what setting did you use for your benchmark?
based on this, it seems important to have a clear picture why/how you are using these settings related to the use case of your application..
For the benchmark above, I used the defaults of:
innodb_only
r/w_batch_size = 1
I don’t have the full numbers off hand, but definitely saw major performance improvements (more on the write side, but it was on read as well) increasing the batch sizes and changing to “caching” from “innodb_only” when experimenting with different settings.
@Mike – I agree entirely that it depends on the use case of your application, the level of persistence you need from your cache (i.e. does it matter if I set batch size to 100 and lose 99 entries from cache?), etc.
More simplistic Memcache vs. Memcache observations: http://blog.ulf-wendel.de/2013/toying-with-peclmysqlnd_memcache-and-mysql-5-6-memcache-innodb/
HandlerSocket vs Memcache is hard to compare as one would compare apples and oranges, means different server version. HS does not compile with MySQL 5.6 (yet). If you compare HS @ 5.5 with InnoDB Memcache @ 5.6 you should see roughly the same performance with simplistic PHP command line tests. However, apples and oranges – think of features… see also http://de.slideshare.net/nixnutz/nosql-in-mysql
Independent YCSB results would be interesting but require significant time to do.
20% sounds too few to me, I made a benchmark of HandlerSocket no in the same conditions (not all data fit buffer pool) and the gains were around 65% with one thread and 3 times more with 10 threads: https://github.com/entering/miscellaneous/blob/master/benchmarks/handler-socket.md
I don’t understand very well yours tests.
How do you do for calculate all this (min,max and range) .Because I would like do the same things with API JAVA memcached
0.586 ms min per/cmd
0.805 ms max per/cmd
0.219 ms range per/cmd
Thanks
So I’m using Percona 5.6.15.
Please correct me if I’m wrong, but to start use InnoDB Memcached plugin I need to have Memcached service running on default port with some defind memory (like 512 MB) and switch on by command: install plugin daemon_memcached soname “libmemcached.so”; ?
After MySQL restart this plugin should be working ? Nothing else ?
What about Query_Cache ? Should I turn it off ? query_cache_type = 0 ?
@Mike, you mentioned in your post a follow up series of tests with a more tuned stack, and also comparison with handlersocket, I don’t see a link though, were you able to perform them, and if so, what’s the link?
Thanks!
Hi Mike, I thinking about implementing this plugin but the question is is there a way to limit the amount of RAM used by memcached. In a classic server I can define the max amount of RAM the memecached will use, and if the limit is reached data in memcache are deleted. How is it handled by mysql, I can’t find anyway to forbid the memcache part of the data to suck all the RAM.
Kind regards
You can configure Memcached with MySQL and PHP without using any plugin. It is not really that difficult if you a right guide. Like this one that explains how configure Memcached with MySQL (https://www.cloudways.com/blog/memcached-with-php/ ) and PHP.