Oracle Blog

Blog for dups

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

Using the PECL PHP Memcached libraries you can write direct queries to Memcached with failover to your SQL queries.

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

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.