Cron-like data expiration with memcached

Memcached is a RAM variable cache for websites and virtually anything else. It can take slow queries away from MySQL and other physical databases so that sites can handle more load easily.

It was originally developed by the makers of Livejournal but it's used on other popular sites like Facebook and Youtube, and now my Facebook app Pro Racer.

Anyway, at Pro Racer we have a "reset" time at 3 a.m., at which all daily limits are reset using a cron job. This includes a daily limit of 3 for one user to race another. This table in the database quickly grew to over a million rows daily, meaning that checking if you raced another person 3 times already.

Using a query logger, I identified these as a significant load on my database.

What to do?

Memcached, of course! Earlier that day I implemented it for the user history tables (one of the most updated tables in the game) and it resulted in huge gains.

This time, however, it would be more challenging. How would I have the counts all expire at once at 3 a.m.? There's no SQL code to just delete all of those specific cache entries, and wiping the cache would mean a huge load on the server as everyone comes back to race after 3 a.m.

So I happened upon this article about memcached and it solved the problem almost immediately, once I got my brain around what the article was actually saying.

Simply, put it in the key. Calculate the period you're in and put it in the key for that data. Older keys will be replaced as new ones fill up the cache.

In Pro Racer's case, we calculate the timestamp of the next reset (the next time it's going to be 3 a.m.) and add it to the key.

This way, once 3 a.m. passes, that part of the key will change and all of the old keyed values will fade into history.

No more 300ms database queries.