Ideas on Integrating Memcached into MySQL Queries
By Duleepa Wijayawardhana on Dec 22, 2008
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 the memcached UDFs so that you can write SQL queries into MySQL/Using the memcached storage engine
- Using MySQL Proxy to interface with Memcached
- 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\*/ ....
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.