Ideas on Integrating Memcached into MySQL Queries

There's any number of ways to integrate your application with Memcached to take advantage of Memcached's power. Here's a list of some of them (and because I am most familiar with PHP in this case so that's what I've listed, and by no means is it exhaustive):

  1. Using the PECL PHP Memcached libraries you can write direct queries to Memcached with failover to your SQL queries.
  2. Using the memcached UDFs so that you can write SQL queries into MySQL/Using the memcached storage engine
  3. Using MySQL Proxy to interface with Memcached
  4. Using something from a framework such as Zend_Cache from the Zend Framework (which allows you to use more than one caching system btw.)


So, yes there are many ways to integrate Memcached into your PHP application, so might I suggest one more way.

The problem with the first option above is that your code tends to get littered with memcached calls, with the second options you end up having to modify your server. In many environments such as hosted environments this is not that clean. With the fourth option, you now need to use a framework potentially and potentially you may not want that overhead. The third option of using MySQL Proxy is one of my favourites but let's face it, MySQL Proxy is not GA yet, the available version has stability issues and the memcached scripts I've seen/heard about seem to use memcached as a full on query cache (please do correct me if I am wrong).

My belief is that memcached is a caching solution and it should be used by the developer wherever possible to make the application faster by placing/caching only the data that the developer needs. I also personally want my application to run when memcached is turned off and I want the application to be easy to read. In other words, I want a modification to the SQL query that will both work with memcached and MySQL but gives me control over what I want to save to memcached and what I need to expire/replace etc.

My solution which I tested over the last couple of weeks will only work if you already have the ability to modify/extend your database handler. At MySQL.com, for example, we use Zend Framework but for various reasons, including performance, we actually have our own custom database handler object. Most of my personal sites also do the same; I do not intend to move away from MySQL ;)

I do have to admit, my first crack at the syntax was quite clunky but in chatting with Adam Donnison (who will, by the way, be giving a beginners talk on Memcached at the MySQL Users Conference) we came up with the following.

SELECT /\*INTO MEMCACHED namespace=table key=id\*/ x, y, z FROM table WHERE id=1;


In this case the data will be stored into memcached with a key if table_1 and store an array of x, y and z. In my database handler this will easily parse the query and select from the MySQL database if it is not in memcached and on the way out save it into memcached for the next query.

To round out the queries, I also added support for things like

INSERT /\*REPLACE MEMCACHED namespace=table key=id\*/ ....

and

DELETE /\*EXPIRE MEMCACHED namespace=table key=id\*/ ....

I wanted to see how a real world use of this would work and so I rewrote my session handler for Zend Framework to take this into account and sure enough it works and it works well. Now my code is a lot neater, will always work with MySQL and I can move my memcached code around as I need it.

By rights, my perfect scenario is to now complete a MySQL Proxy script that understands the above and does the above then I could even remove the database handler code that does this all. To be honest though, the performance of this is quite good on my limited tests.

While I have not done it, I would imagine that extending Zend Framework to be able to handle these kind of queries should not be too difficult, nor is it difficult to simply use Zend_Cache into your database handler object and thereby even further enhancing your application's abilities to cache things.

Comments:

Begin Shameless Plug:

Your forgetting the most low level way, hooking memcached directly into the innodb source code, like what we are doing with Waffle Grid (www.wafflegrid.com)

End Shameless Plug:

Posted by Matt Yonkovit on December 22, 2008 at 03:32 AM EST #

Another way to combine memcache and mysql: adodb database abstraction layer. It has memcache (or filesystem) caching support built in :)

We've used it for over a year to great success.

Posted by Barry Hunter on December 22, 2008 at 04:21 AM EST #

but adodb it can't check the data be modified or not.
may i ask Barry how do you do the real time cache ?
when data are modified , the memcached result will expire and re-fetch it .

Posted by johnpupu on December 23, 2008 at 12:46 PM EST #

Hi,

Very interesting!
But something is still unclear to me: Who parses the query:
"SELECT /\*INTO MEMCACHED namespace=table key=id\*/ x, y, z FROM table WHERE id=1;"

Is this your Zend PHP connector? Does it wait for results to return, then puts them in memcached before giving them to you?

If so, do you know if there are implementations for other languages? Or will the mysql drivers support this syntax?

Reagrds

Posted by Shlomi Noach on December 23, 2008 at 05:01 PM EST #

"The same concern might point you in the direction of memcached, and if it does, Dups, the Arctic Dolphin, has some ideas on integrating memcached into MySQL queries."

www.pythian.com/blogs/1435/log-buffer-129-a-carnival-of-the-vanities-for-dbas

Posted by Log Buffer on January 02, 2009 at 07:27 AM EST #

Hi,

This is great news for me, and am excited. Will check deep into php mysql memcached integration and get back soon.

Posted by Php Trivandrum on January 19, 2009 at 03:20 PM EST #

Shlomi Noach: This is my own DB connector, this is not something going into MySQL the product :) This is just a suggestion for possible query structure without having to change SQL very much.

Posted by Duleepa Wijayawardhana on January 19, 2009 at 03:49 PM EST #

can you support your code details? "INSERT /\*REPLACE MEMCACHED namespace=table key=id\*/ ...." I consider it should be "INSERT /\*INTO MEMCACHED namespace=table key=id\*/ ...."

Posted by Steven on February 23, 2009 at 03:12 PM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

This is the blog of Dups... currently I'm one of MySQL's Community Relations Managers for Sun Microsystems, post, contact me, I want to hear from you!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
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
   
       
Today