How to configure memcached for mysql innodb?

Question

Currently we have a website running on a Centos 6.5 webserver with Direct Admin. APC was configured in the past and is working nicely, but some reading suggested to also implement memcached to cache some static tables (like for instance menu's).

As of MySQL 5.6, the innoDB tables are compatible with a mysql memcache deamon, so I started off following this guide: http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-installing.html. The config script is ran and the deamon is installed.

However the Drupal memcache plugin does not see memcache is running. That module is obviously checking for the php memcached deamon, while my deamon is already running in mysql.

Since both extensions are called memcached.so, we strongly have the feeling this is the same thing. Are there actually two different things and does Drupal not support the InnoDB memcached deamon, do I need both (php extension to access the mysql extension?), or was this supposed to be working and did we something wrong?

Update

The status report showed something like "not running", but one of my colleagues has installed the PHP PECL extension now and it seems to be working. But then I still don't understand what the MySQL innodb deamon plugin does. Is it not needed, or does it improve database access even more then the PHP extension would?

Answer 1

The Memcached interface to InnoDB is a feature of MySQL to support the memcached protocol, yet with InnoDB as the back-end storage. It seems like lot of people have been confused by what this means, so I'll try to explain.

Whereas a real memcached daemon stores data in memory, MySQL stores data persistently in an InnoDB table. PHP applications can read and write data using the memcached extension, as if they're using a standard memcached in-memory store. However, they are really reading and writing rows from the InnoDB table.

This is somewhat slower than standard memcached, because it has the overhead of writing to disk is greater than accessing memory. But it's somewhat faster than using SQL to read and write those rows, because it skips the complexity of the SQL parser and query optimizer.

That's really the new feature in MySQL: to bypass SQL, and give access directly to the InnoDB storage engine through a simple, but familiar interface. They chose memcached on the theory that many developers would be familiar with it and have tools and language support for it already.

The InnoDB memcached interface is similar to the earlier experimental plugin called HandlerSocket developed in 2010. http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

Here's a Percona blog that shows tests of the relative speeds of InnoDB memcached versus SQL queries: http://www.percona.com/blog/2013/03/29/mysql-5-6-innodb-memcached-plugin-as-a-caching-layer/

---

Re your question in comments:

You might be misunderstanding. The data is never in Memcached. There is no automatic synchronization between MySQL and Memcached. The only thing is that MySQL is mimicking the API and protocol of Memcached. There's no reason that they did this, except to make the API familiar to developers.

When you use the "Memcached API for InnoDB" you're connecting your application to a port listened to by the mysqld daemon process. Your requests on this connections read and write rows directly in the InnoDB storage engine. There's no Memcached instance in between.