UPDATE: DreamHost has ordered me to take down the thing entirely. We’ll just have to do without it until a substitute is found.
Some “Alas” readers may have noticed that comments were down for a while a few days ago. That’s because the “Recently Commented Posts” list on the sidebar was eating too much server time, so the hosting company shut down the comments in self-defense.
I really think some sort of “recent comments” post is essential to the discussions we have on “Alas.” As a stopgap measure, I’ve put the recently commented posts list on a separate page – you can see the link to it near the top of the sidebar (just below the search box). If you want to see what posts have most recently been updated with new comments, click on that link.
Hopefully, since the “recently commented” query will now only be running every time someone goes out of there way to look at it, rather than every single time anyone loads “Alas” or any sub-page, this will reduce the server load and allow us to have a “recently commented” list while I look for a better solution. (On the other hand, maybe the server load will be too high and Dreamhost will shut the whole website down. It’s hard to know for sure.)
Several people have suggested to me a different sort of “recently commented posts” plug-in; one that, instead of doing searches of the (enourmous) comments table every time someone loads a page, automatically wrote to a small table every time a comment was posted, and also deleted the bottom row from the same table. Then the sidebar could just reprint the talble, rather than doing a search of the database. The table would only be updated when people posted new comments, and since it would never be more than 20 or 30 rows in size, it would be very light on the server.
That sounds like a logical solution to me; however, I don’t know anything about coding at all, so oh well.
Read below the fold if you want to see the recent correspondance about the problem between me and my host. If you understand these matters, please feel free to offer advice. If you know how to code and would be interested in taking a commission to write a better “recently commented” plug-in for alas, post or email me and let me know how much you’d charge. :-)
Hello,
I’ve had to disable a mysql query that was running on amptoon that was bringing “juno” to its knees. Here is the query:
# Time: 050825 10:30:57
# User@Host: verbosity[verbosity] @ scipio.dreamhost.com [205.196.218.27]
# Query_time: 36 Lock_time: 0 Rows_sent: 8 Rows_examined: 82479
SELECT alas_posts.*, MAX(comment_date) AS max_comment_date FROM
alas_comments, alas_posts WHERE alas_posts.post_date <= '2005-08-25 10:29:51' AND ( alas_posts.post_status = 'publish' OR alas_posts.post_status = 'sticky' ) AND alas_posts.post_password = '' AND alas_posts.ID = alas_comments.comment_post_ID AND alas_comments.comment_approved = '1' GROUP BY alas_posts.ID ORDER BY max_comment_date DESC LIMIT 0, 8; Unfortunately, I can't just disable a query. So I renamed your alas_comments to alas_comments_disabled_by_dreamhost. (That query is unindexable too. Please learn about the EXPLAIN statement.) Once you've prevented that query from ever running again, you can rename the table back. If you decide to run that query again, we'll have to disable your entire database permanently. Thanks! Jason
Then I wrote back to Jason. Actually, I wrote back to Jason a few times, but once I had a non-angry reply I actually emailed it rather than throwing it away. :-)
I think that I’ve disabled the plug-in that was causing the query, but it’s hard for me to be 100% certain. However, I’m 90% certain that I’ve shut down the right thing.
Rather than permanently disabling my database if the query occurs again (which is a nicer way for you to say, “I’ll permanently kill your website”), please rename the alas_comments file again, and I’ll do my best to get the problem solved. But I really think I have solved the problem, so unless I’ve completely misdiagnosed the problem, it won’t come up at all.
Can you give me an idea about the nature of the problem. For example, is it that the query itself is too horrible to run, or is the problem that it’s running too often (e.g., if the same query ran a few dozen times a day, rather than hundreds of times a day, would that still force you to pull the plug on my website?)
Could you give me any more details about the problem (logs, etc) so I could try and find a way to have my website be fully functional without messing you folks up?
Thanks,
Barry
And, finally, I recieved this response from Jason:
> Rather than permanently disabling my database if the query occurs
> again (which is a nicer way for you to say, “I’ll permanently kill
> your website”), please rename the alas_comments file again, and I’ll
> do my best to get the problem solved. But I really think I have solved
> the problem, so unless I’ve completely misdiagnosed the problem, it
> won’t come up at all.You’ve got it! If you’re making efforts to find it, and you’re basically sure you’ve already nailed it, that sounds great to me. Go ahead and rename it back, and if it pops up again, I’ll rename the table again. I’m sure you’ll understand that I can’t do this ad infinitum.
> Can you give me an idea about the nature of the problem. For example,
> is it that the query itself is too horrible to run, or is the problem
> that it’s running too often (e.g., if the same query ran a few dozen
> times a day, rather than hundreds of times a day, would that still
> force you to pull the plug on my website?)Sure! The query was running a lot (several times a minute? maybe a lot more than that?), it was examining more than 80,000 rows (you should shoot for less than 5000; see the EXPLAIN statement), and quite importantly, it was taking 36 seconds (should take less than one second).
Unfortunately, the logs are difficult to provide to users on a regular basis, as they’re all mixed in together….
Good luck with debugging!
And that’s where it stands.
The plug-in I’m currently using is Customizable Post Listings, which I’m told by an expert doesn’t have a greater server load than the other post-listing plugins available. If anyone out there knows how to modify CPL so that it examines less than 5,000 rows, that would be cool.
I’ll write the rotating-table code for you; shouldn’t be hard. It’ll have to wait a week or so, this coming week is likely to be pretty busy but I can squeeze it in over Labor Day. (Nothing sticks it to the bloated labor movement like working on their holiday!)
You use PHP and MySql, right? Goodie, this’ll be a learning experience.
And I mostly promise that people won’t find neo-libertarian propaganda suddenly popping up in odd places.
If Robert finds he can’t do it, you could hold another fund drive to have Lauren over at Feministe do it.
Most of this stuff is Greek to me, but one thing you can do is scout the forums on WordPress. You can’t always get the computer geeks to offer practical advice–not that they don’t mean well, but a lot of them don’t really get into blogging and therefore maybe don’t know the quick fixes that are easy for non-geeks to use. For instance, we went back and forth with our hosting company who was threatening to shut down Pandagon for bandwidth usage, and it took like three days to find out that all we had to do was change the format so that posts disappear from the main page in three days instead of seven. That cut back our bandwidth usage by 75%. But getting that information was like pulling teeth.
Robert, thank you so much! If you want to do it, I’ll gladly accept your offer. :-)
Of course, I have a lot of fussy suggestions for how it might look better than the previous one did, which I’ll contact you about. :-) Tell me if you ever need a cartoon drawing, I’ll do it for you for free. :-P