Expiring memcached using mysql proxy when an update occurs?

Question

Hi guys,

I have mysql Proxy running which takes a query, performs an md5 on it, and caches the result into a memcached DB. the problem occurs when an update happens in the rails app that would invalidate that cache. Any ideas on how to invalidate all of the proper keys in the cache at that time?

Answer 1

The core of the problem, is you don't know what the key is since it is md5 generated.

However, you can mitigate the problem by not storing data for that query.

You query may look like this "SELECT my_data.* FROM my_data WHERE conditions"

However, you can reduce the redudeancy of data by use this query instead

SELECT my_data.id FROM my_data WHERE conditions

Which is then followed up by

Memcache.mget( ids )

This won't prohibit the return on data that no longer matches the conditions, but may mitigate returning stale data.

--

Another option is to look into using namespaces: See here:

http://code.google.com/p/memcached/wiki/NewProgrammingTricks#Namespacing

You can namespace all of your major queries. You won't be able to delete the keys, but you can change the key version id, which will in effect expire your data.

Logistically messy, but you could use it on a few bad queries.

--

lastly, you could store those queries in a different memcache server and flush on a more frequent basis.