Comments on: MySQL 5.6 – InnoDB Memcached Plugin as a caching layer

By: Oliver Russell

Oliver Russell — Wed, 21 Mar 2018 13:55:25 +0000

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.

By: Sunny

Sunny — Fri, 02 Sep 2016 15:53:11 +0000

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

By: Kevin Farley

Kevin Farley — Wed, 03 Dec 2014 15:02:43 +0000

@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!

By: Sergey

Sergey — Thu, 16 Jan 2014 18:23:49 +0000

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 ?

By: Jules

Jules — Mon, 12 Aug 2013 08:28:26 +0000

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

By: Eduardo Oliveira

Eduardo Oliveira — Thu, 04 Apr 2013 08:07:33 +0000

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

By: Ulf Wendel

Ulf Wendel — Wed, 03 Apr 2013 19:35:47 +0000

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.

By: Mike Benshoof

Mike Benshoof — Tue, 02 Apr 2013 18:56:10 +0000

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.

By: mike morse

mike morse — Tue, 02 Apr 2013 18:48:55 +0000

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

By: Mike Benshoof

Mike Benshoof — Mon, 01 Apr 2013 14:24:02 +0000

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).

By: Hrvoje

Hrvoje — Sat, 30 Mar 2013 10:51:16 +0000

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.

By: Peter Zaitsev

Peter Zaitsev — Fri, 29 Mar 2013 19:50:38 +0000

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.

By: Andy

Andy — Fri, 29 Mar 2013 13:46:00 +0000

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?

By: Jacky Leung

Jacky Leung — Fri, 29 Mar 2013 12:18:07 +0000

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

By: Daniël van Eeden

Daniël van Eeden — Fri, 29 Mar 2013 10:36:37 +0000

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