{"id":1811,"date":"2005-08-27T21:00:20","date_gmt":"2005-08-28T04:00:20","guid":{"rendered":"http:\/\/www.amptoons.com\/blog\/archives\/2005\/08\/27\/the-recently-commented-post-list\/"},"modified":"2005-08-27T21:00:20","modified_gmt":"2005-08-28T04:00:20","slug":"the-recently-commented-post-list","status":"publish","type":"post","link":"https:\/\/amptoons.com\/blog\/?p=1811","title":{"rendered":"The Recently Commented Post List"},"content":{"rendered":"

UPDATE<\/strong>: DreamHost has ordered me to take down the thing entirely. We’ll just have to do without it until a substitute is found.<\/em><\/p>\n

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.<\/p>\n

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.<\/p>\n

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.)<\/strike><\/p>\n

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.<\/p>\n

That sounds like a logical solution to me; however, I don’t know anything about coding at all, so oh well.<\/p>\n

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. :-)<\/p>\nLetter number one, from support to me:<\/p>\n

Hello,<\/p>\n

I’ve had to disable a mysql query that was running on amptoon that was bringing “juno” to its knees. Here is the query:<\/p>\n

# Time: 050825 10:30:57<\/p>\n

# User@Host: verbosity[verbosity] @ scipio.dreamhost.com [205.196.218.27]<\/p>\n

# Query_time: 36 Lock_time: 0 Rows_sent: 8 Rows_examined: 82479<\/p>\n

SELECT alas_posts.*, MAX(comment_date) AS max_comment_date FROM
\nalas_comments, alas_posts WHERE alas_posts.post_date <= '2005-08-25\n10:29:51' AND ( alas_posts.post_status = 'publish' OR\nalas_posts.post_status = 'sticky' ) AND alas_posts.post_password = '' AND\nalas_posts.ID = alas_comments.comment_post_ID AND\nalas_comments.comment_approved = '1' GROUP BY alas_posts.ID ORDER BY\nmax_comment_date DESC LIMIT 0, 8;\n\nUnfortunately, 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.)\n\nOnce 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.\n\nThanks!\n\nJason<\/p><\/blockquote>\n

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. :-)<\/p>\n

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.<\/p>\n

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.<\/p>\n

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?)<\/p>\n

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?<\/p>\n

Thanks,<\/p>\n

Barry<\/p><\/blockquote>\n

And, finally, I recieved this response from Jason:<\/p>\n

> Rather than permanently disabling my database if the query occurs
\n> again (which is a nicer way for you to say, “I’ll permanently kill
\n> your website”), please rename the alas_comments file again, and I’ll
\n> do my best to get the problem solved. But I really think I have solved
\n> the problem, so unless I’ve completely misdiagnosed the problem, it
\n> won’t come up at all.<\/p>\n

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.<\/p>\n

> Can you give me an idea about the nature of the problem. For example,
\n> is it that the query itself is too horrible to run, or is the problem
\n> that it’s running too often (e.g., if the same query ran a few dozen
\n> times a day, rather than hundreds of times a day, would that still
\n> force you to pull the plug on my website?)<\/p>\n

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).<\/p>\n

Unfortunately, the logs are difficult to provide to users on a regular basis, as they’re all mixed in together….<\/p>\n

Good luck with debugging!<\/p><\/blockquote>\n

And that’s where it stands.<\/p>\n

The plug-in I’m currently using is Customizable Post Listings<\/a>, 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.<\/pracut>\n","protected":false},"excerpt":{"rendered":"

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. … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[98],"tags":[],"class_list":["post-1811","post","type-post","status-publish","format-standard","hentry","category-site-and-admin-stuff"],"_links":{"self":[{"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1811","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1811"}],"version-history":[{"count":0,"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1811\/revisions"}],"wp:attachment":[{"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1811"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1811"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/amptoons.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1811"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}