Blog

I was working with a client’s ExpressionEngine site this week, one that was having troubles with traffic growth, large traffic spikes, and the database server going down frequently at peak times.

I came up with a partial solution that seems to have helped quite a bit, and might be appropriate for your situation, especially if you have a LOT of entries.

All “entries” on an EE site (articles, sale items, blog entries, etc.), use the channel module when they display data. The channel modules fetches data from the database with a very big MySQL request that also fetches categories, author data, related stories, and more.  Even with careful use of the “disable” parameter, this query is often big.

If the database server could cache that query, it could be much more efficient at handing out that data. And it does try to cache those queries, and MySQL is pretty good at figuring out what to cache and how to cache it. But part of every channel query (unless you turn off dynamic queries, which is rarely possible) is a portion that checks the entry dates, so that future entries AREN’T published, and expired entries AREN’T published. And this time stamp is changed every second, so the query changes every second, and so MySQL can’t cache it.

So what I did, is I modified the timestamp code to be rounded to the nearest 100 seconds. In other words, the channel query for any given page (or portion of a page) will look the same to the database server for a full 100 seconds, instead of changing each second, so it’ll be able to respond with the pre-fetched data much more quickly.  The code looks like this:

$timestamp = (round ($timestamp / 100)) * 100;

Now, there’s already caching IN expressionengine, and this won’t affect that. So that’s good.

How MySQL deals with it when the exp_channel (or any related) table changes, is that it dumps the in-database cache, so you won’t end up with any sort of delay when editing existing entries; they’ll change immediately and that’ll display immediately (because otherwise, editing would be much more difficult because you wouldn’t be able to look at the results of your edit very well).

Also, if you do this, you probably won’t have any sort of delay when posting new entries, because it takes at least a minute to write and publish an entry, and the entry date of an entry is set from when you load the New Entry form, so the query, even if rounded, will likely always return any newly published entry.

And this rounding doesn’t happen at all in the control panel, because entry dates and expiration dates aren’t used to modify what appears in the edit list.

Lastly, I just wanted to say that this solution came about because of feedback and expertise from the folks at Nexcess.net, our preferred Web host, and it makes me feel even more confident recommending them for ExpressionEngine sites.

This is a core code modification, so you’ll have to reapply it when you upgrade.  If you try it, please let me know if you notice any improvement in site performance.  My next step is to make it into an extension that you can add to your site; if you would you be interested in that, let me know.