As many of you noticed, “Alas” was offline for about 16 hours, from late yesterday afternoon to early this morning. We’re back online now, obviously. The rest of this post is a detailed discussion of what went wrong and what I’m planning to do about it.
What happened was a server load problem – a problem which has plagued this blog. But now I think we finally know what the problem is – on a blog that gets as many page views as “Alas” does, you just can’t have a 20-post-long “recently commented” list. As Robert wrote me via email:
Diagnosing your problem: It’s your most-recent-comment plugin.
That query is extremely computationally expensive. (Expense means how much disk and CPU activity it needs in order to run) You’re saying “give me every field in this big honkin, table, and also figure out which record has the latest date, from this huge ass database and with a bunch of logic to process for every row…oh, and run this on two big-tables in conjunction with each other…and then when you’re done with that look at the whole damn result set and put it in order and then subgroup each row by this index field.”
I’m tired just reading it; your plugin is probably asking for this every time someone loads any page on your system. I ran into a similar problem on BNN: the architecture was very programmatically attractive (this is a nice query) but it didn’t scale up well. This query is fantastic for a blog with 2 posts a day and 10 comments apiece but it will put a bullet in the brain of a machine asked to do it 8000 times a day on posts with 300+ comments. (Particularly because inexpensive web hosting computers tend to be cheap POS rack boxes that don’t have a ton of hardware because 99% of the sites don’t need any real processing power to deliver their trickle of hits.)
Easy fix: kill the plugin. Hard fix: Learn or hire WordPress expertise and have someone write you an efficient plugin for big comment sites.
For now, I’ve taken Robert’s “easy fix,” because it was the quickest way to get “Alas” back online.
Here’s a letter I sent to DreamHost support, explaining my future plans:
Hi! I’m working on solving my recent server load problems, which led DreamHost Support to turn my WordPress database off yesterday.
The problem was, I believe, caused by my blog’s “recently commented posts” list. A WordPress expert I talked to suggested to me that such a list, when loaded on every single page view on a popular blog, could cause problems if it calls for more than 10 posts to be listed. My “recently commented posts” list was set to list the 20 posts that comments had recently been left on.
Because my blog emphasizes reader comments and discussion (the blog sometimes get hundreds of reader comments a day), the “recently commented posts” list is essential – without it, posts that still have active discussions will “fall off” the front page and be too hard for readers to find. What I’d like to do is experiment a little, to find a way to restore the “recently commented posts” list that won’t cause an unfair strain on DreamHost’s server load.
My proposal is to restore the “recently commented posts” list on the sidebar – but this time with only 7 posts listed, rather than 20. That should reduce the server load enormously, hopefully eliminating the problem. I’ll also create a separate page with a larger, more detailed listing – but since it’s a separate page, I expect it’ll only be loaded by the relatively small number of very dedicated readers who follow the discussions very closely.
What I’d like is your help in making it possible for me to run this experiment. I could start this at any time that works for you (I suggest 9am pacific time Thursday). What I’d like from DreamHost Support is, if the server load gets too high (and I don’t think it will), rather than shutting me off, email me letting me know there’s a problem. I’ll make sure to monitor my email frequently. Once emailed, I’ll turn the offending plugin off immediately and use the new information to figure out the next step.
This will allow me to try to find a solution without fear of having my blog suddenly turned off by the host; and it will provide you with assurance that if there is a server load issue caused by the plugin, it will be turned off quickly.
The other thing I’d like, if things go wrong and I have to shut the plugin off, is a detailed report from the database regarding server load. This will help my consulting expert figure out what is wrong and how to run my blog while avoiding the problem in the future.
I hope that we can work together to let my blog have the functionality it needs, without causing server load problems. I’ll look forward to your response.
So that’s the plan; I should hear back from DreamHost Support by sometime tomorrow. Let me know what y’all think.
On another subject, I have no idea why the comment preview stopped working once I’d moved to DreamHost. If anyone has a clue, please enlighten me. :-)
Yeah, the “recently commented post” thing is crucial. That’s a toughie.
Would a daily update suffice?
Maybe you could display something like –
and so on. Maybe continue listing any thread that was active within the prior week, updated once a day (midnight)?
Just a thought. Good luck with this.
Another possibility is a table that’s just:
Index, ThreadName, ThreadURL, CommenterName
When someone posts a new comment, have the commenting module kill the lowest-indexed row in that table and write in the new comment row. (So the table is always the same length – say, 30 rows).
Then your most-recent-comments section query can just be
select ThreadName, ThreadURL, CommenterName from TempTable
which is pretty negligible to do.
Do you know how much memory your (shared?) host have? These kinds of big queries can kill a low-end server, very much so without enough RAM.
I haven’t looked at the SQL in this plugin, but lots of optimizing can be done to the database itself and maybe the query.
Robert, the problem with that is that when a particular thread is really hot, it can easily generate 30 comments in just an hour or less, pushing everything else off the “most recent comments” section. That’s why I prefer a “recently commented posts,” instead.
Class, I have no idea how much memory they have, unfortunately. (And yes, I’m on a shared server – it’s the only way I can afford).
Which plug in were you using?
It also occurs to me you might be able to solve the problem you describe by doing something that lets you create a cache file. Conceivably, a plug in could be written writes to a file when a new comment is made. It would put a comment at the top of the list and drop and old one off the bottom. Later, when the blog loads, it just reads the file.
That might be quicker than searching all the comments in the data base.
Then revise Robert’s suggestion so that the comment module checks whether there is already a comment listed for the thread a comment is being added to, in which case the old comment is deleted from the table and replaced with the listing for the new comment. If the new comment is being added to a thread that isn’t on the table, then the module would delete the oldest entry from the table, and add the new comment listing.
You should be able to have exactly the same content as the old recent comments list, but the amount of computer time would be very small, both for adding comments to the recent comments list, and for displaying them.
Charles’ modification makes sense.
I know that when I check the most-recently-commented section, I find it a little tricky to figure out which posts are new to me. I suggested including the commenter name because that’s a lot easier to remember than a number (“were there 232 posts before, or was it 223? better load it and check”). You might also consider a timestamp on the posts, so that the section output would be:
Most Recent Comments
“Republicans are bad”, last comment at 7:22 PM by Robert
“Marriage Equality Now”, last comment at 7:19 PM by Alsis39
…
and so on.
Now get coding!
Here’s a different plug-in than the one I was using, which has the option of using a cache. That might serve the same purpose as what you’re suggesting – depending on how their caching works, I guess. Plus, I really like their format – it lists recent comments organized by post.
I’m definitely going to wait for feedback from Podz before I take any definite steps. And coding things myself won’t happen, alas – if necessary, I’ll try to find the funds to hire someone to do the coding.
Another alternative:
Make the timestamp be the index field, and then just have the plugin list all the commented posts within a certain time period (say, a day). The list might be longer (or shorter if things were quiet) but it would let your once-a-day type visitors see at a glance which posts have been active since their last visit.
(I suddenly have insight into why commercial software projects are always late – because it’s fun for people who don’t have to actually write and test the code to say “hey, you know what else would be a good idea…”)
I was worried that you were going to say that the site was being attacked by spammers or hackers or some such. I’m sorry about the problem with the comment thingie, but I’m glad to hear that it wasn’t something hostile.
As someone who runs technical support teams for a living, I want to thank you for your courteous, well-thought out support request and your colloborative approach to solving the problem.
Seriously. It really, really makes a difference and I can just about guarantee you that the recipient noticed.
In regards to the unanswered question about the comment preview, I think it’s due to a limitation in the way that DreamHost’s configuration (with PHP as a CGI) can handle the URLs for PHP scripts.
I don’t have a way to test it, but I think adding the lines I’ve written here to the top of your site’s “.htaccess” file will solve the problem. This will remap the way of accessing the code that DreamHost doesn’t support into a way it should support.
If you don’t have access to the .htaccess file or want a different solution, you can edit the “live-comment-preview.php” file in your “wp-content/plugins” directory. Changing, on line 185 (about 8 lines from the end of the plugin script), the slash (/) just before “commentPreview.js” to a question mark (?) should accomplish the same thing.
Best of luck with the live preview. I’ve enjoyed it on my site.