I mentioned a few weeks ago that Jan Kneschke has posted some MySQL user-defined functions — available here — for communicating with memcache. I want to outline an example of how to automate cache maintenance by calling these functions from triggers. In a big web application, you have critical high-performance code (which, if you are running memcache, is no doubt already cache-aware), but you probably also have a bunch of little scripts for performing sundry administrative tasks. With triggers in the database, you can keep these scripts the way they are, and even run ad hoc SQL statements, without worrying about leaving inconsistent records in memcache.

This all requires running MySQL 5 with dynamically loaded UDF code — but of course you don’t have to upgrade the whole database farm to MySQL 5, just a single replication slave to run the triggers. On this one slave, create AFTER DELETE and AFTER UPDATE triggers that will delete a record from memcache whenever it gets changed or deleted in the database, like this:

CREATE trigger cache_expire_delete AFTER DELETE on table1 FOR EACH ROW DO memcache_delete(”memcache_host:11211″,OLD.pk) ;

Here are the details.
Continue reading “MySQL triggers and memcache”