Skip to content

mysql-proxy-cache: a protocol-level mysql cache

Recently I’ve been playing around with MySQL Proxy, a network proxy for MySQL. One cool thing you can do with MySQL Proxy is to specify a Lua script that implements special hooks that expose various parts of the MySQL network protocol. For example, implementing a read_query() function will let you manipulate queries that MySQL has received but hasn’t processed yet. You can do fun things with it like log, manipulate or discard the query, all without having to modify your client applications. 

For fun, I’ve created a mysql-proxy-cache project that will return a cached version of any SELECT queries, if they’ve been executed already. I store cached results in a memcache instance whose keys are md5 hashes of the queries that generated them.

It was pretty fun working on this because it let me learn Lua as well, further adding to my arsenal of programming languages. However, the project is totally alpha and shouldn’t be using in a production environment. Mostly because there’s no way to expire cached items.

In order to supported cache expiration, I’d need to intercept UPDATE/INSERT/DELETE queries and clear the cache if they touch any rows that are in the cache. An easy way out would be to just clear cached items if the queries’ source table(s) were modified, not necessarily their rows, but then that’s exactly the behavior of MySQL’s built in query cache so it wouldn’t be very useful.

Comments 3

  1. Daniel Wolpert wrote:

    Hello Carlo,

    nice and interesting solution. But i have one small question:

    After the cache_timeout expire, what happens when two or more identical queries arrives at the same time (or with a slight misalignment)?

    Will the MySQL-Proxy-Cache all queries execute on the MySQL-DB-Server and store the result in the cache? Or is exactly one querie execute and store the result in the cache?

    Thanks in advance for the answer,

    Daniel

    Posted 02 Sep 2009 at 3:30 pm
  2. Carlo wrote:

    One query would get executed and stored in the cache, and the second query would get its data directly from the cache

    Posted 03 Sep 2009 at 12:38 am
  3. Daniel Wolpert wrote:

    Ok, let me say, the query need 2 seconds to execute. In this time, what happens with the second query? Get its data from the old cache or he is waiting for the first query?

    Posted 03 Sep 2009 at 8:00 am