Jangro.com

When Memcached Doesn’t Cut It - Website Caching Part II

We use memcached on lots of sites. The beautiful thing is, once you’ve got a bunch of servers running memcached, it’s really easy to cache queries and save some serious database hits.

We use it sometimes when we probably shouldn’t. Or at least we should be caching a little differently. Case in point, our costume site, Costumzee.com on which we’ve recently been enjoying a large amount of traffic.

We did a redesign a few weeks ago, and right after the launch, during the busiest times of the day (evening), the database server was grinding to a halt, no more available connections, tmp tables all over, database deadlock, CPU load through the roof. Nightmare.

Here’s the CPU load on that server for the past two weeks. I’ve marked the times of the new design and when I implemented a new caching method.

The cause was a newly designed page that had several fairly heavy queries on it. They were all cached in memcache, but it wasn’t really helping. First, there are a lot of unique queries, thousands of them. Second, the data coming back is pretty big — big for storing in RAM anyway. Third, the data on the page changes only once a day, so the data could potentially be cached for up to 24 hours, depending on the time of day.

This situation cries out for a different sort of cache. More permanent memory for hours, not seconds or minutes.

I can’t simply implement page caching on this site as there is user-specific information on every page. So portions of pages would have to be cached. It’s possible, but not something I currently have in my bag of tricks. Further, caching to files is complicated because this site runs on two front-end servers behind a load balancer. If I employ file caching, I’d have to somehow share these files between the two servers. It’s possible with NFS mount, or the like, but again, not something I had quickly at my fingertips.

Since I was in a bit of a panic as traffic was increasing by about 25% per day, I had to go with what I knew: PHP and MySQL.

These queries that were bogging down the server were doing joins across several very large tables. The killer was that they were fulltext searches, sorted by relevance, and this sorting was causing tmp tables to be created. Once the data came back, there really wasn’t THAT much of it. So I implemented a way to cache the query result data structures to a database table designed for the purpose. So, now, once a page is viewed, the data structures (database result objects) are stored in this cache table indexed by the id of the page.

Here’s the trimmed down pseudo code that I use for this. The database is a very simple structure with fields for the primary key, data, and cached data.


$sql = "select * from page_cache where 1 and page_name = '$page_name' ";
$result = db_query($sql);
if ($cache_data = mysql_fetch_object($result)) {
// we have cached data, use it...
$page_cached = 1;
$product_array = unserialize($cache_data->product_list);
}

if (!$page_cached) {
// get the data from the database using the heavy queries
// code omitted. Data ends up in $product_array
// save the data to the cache
$page_cache = serialize($product_array);
$sql = "replace into page_cache set page_name = '$page_name', cache_date = '$now', product_list = '$page_cache'";
db_query($sql);
}

The magic is in the PHP serialize and unserialize commands. These commands will take any complex data structure, like a database result object and put it in a text format that can be stored in a text field and retrieved later. The unserialize command turns this text back into the data structure.

Since my data refreshes once a day, I don’t expire this cached data after a certain amount of time (though I easily could). Instead, I simply wipe it all clean right after the database is updated. By the time I do, I’ve cached tens of thousands of pages, and hundreds of megabytes of data.

And as you can see from the graph above, the server is now barely breaking a sweat, even during the busiest times of day. Further, the days after the “episode” saw traffic much greater than the days that had the problem. In fact, if you look closely, you can see that load is now lower than before the redesign.

So, if you’ve got a dynamic site, keep an eye on your database and server load. And ask yourself the question, do you really need to get fresh data with every page view? The answer to that question is probably “no”, in which case you might want to look at some method of caching.

If you enjoyed this post, please subscribe to my RSS feed