Archive for the ‘MySQL’ Category

Memcached plugin with XTRADB Cluster / Galera

23 Mar

Having used memcached plugin for InnoDB in single node installations in the past with satisfacroty results, I wondered if it was possible to create a clustered setup which allows better scaling and provided replication of keys & values from one Memcached to another one, thus allowing more concurrent reads and prevention of the cold cache problem in the event of a restart that’s usual in a sharded cluster. I have been using Couchbase for a while with great results, but wanted to find something that could be made available to legacy applications for instance running versions of PHP that can’t do a native Couchbase client. (No doubt Couchbase does Memcache, but still wanted to check if Galera was up to the task) So I was wondering if it is possible to put something together using XTRADB Cluster / Galera as a back end for Memcached. I found some documentation that explained how to setup such an arrangement for NDB cluster but that makes use of a special memcached plugin for NDB. Couldn’t find a mention of the InnoDB Memached plugin being usable with Galera / XTRADB anywhere, so I decided to try it out myself.

I started up my dev cluster and setup the innodb memcached plugin using the script included with the XTRADB cluster. This creates a InnoDB Memcache config database and creates a test mapping to a table in the test schema called demo_test.


So, the above script caused the database and mapping to be created on all three XTRADB nodes I had in the cluster. I tested a few reads of the test data from all the nodes using memcached port 11211 and they all seemed to respond fairly well. So, I decided to send some SET requests and check if those replicate. So, I ran a SET operation on one of the nodes and the demo_test table got the data, but to my disappointment the other nodes didn’t get it. It appeared as if the Galera layer didn’t know that Memcached plugin had inserted some data into InnoDB. I remembered that there is a variable¬† innodb_api_enable_binlog that controls if the Memcached updates will be written to MySQL Binary log, that defaults to off and I didn’t change it to on.

So, i changed the value of that variable to on, restarted the cluster to make the value effective. Tested reads with memcached and they were fine. Then I ran some writes through memcached and it was still the same. So, I concluded that memcached plugin writing to InnoDB goes unnoticed by the Galera library. I’ll dig into the code to see why that doesn’t work. I wonder if anyone else has had the same experience?

I also intend to upgrade my cluster to a newer version and repeat the tests. I am currently testing on XTRADB cluster 5.6.25 on CentOS 6.7 32 -bit.


The all new MySQL Librarian

13 Aug

MySQL Community just got a great a tool that allows then to systematically share the community knowledge about this amazing open source database server. This is just a short post to mark the introduction of this facility to the public. All you need is a valid user account on and you are done.

You can click here for further details.