server load

6 Feb, '07

Guys, sorry for the various downtimes the server has been experiencing. The biggest culprit is the amount of comments being pulled all the time from the database which is overwhelming it.

In the words of tech support:

SELECT comment_post_ID, post_title
FROM (wp_comments LEFT JOIN wp_posts ON (comment_post_ID = ID))
WHERE comment_approved = ‘1’
AND (wp_posts.post_status=’publish’
OR wp_posts.post_status=’static’)
ORDER BY comment_date DESC;

This is returning about 30k rows each time it is run and it appears to be run fairly often.

So I’ve got to think of solutions to alleviate this problem. The first is that I will change all the feeds to be served by Feedburner, including the comment feeds and close the possibility of commenting on any article which is over 6 months old (older articles will be cached, and this will improve the performance too – if a user really needs to comment on an older article, I’m sure they will find a way to do so, like publishing something on their blog and linking back maybe?) Another thing is reducing the size of the “Recent Comments” block which I think is the biggest culprit, as there is no caching mechanism in that module and it is the single thing that is impacting the database mostly (I think) as it is pulling those 30,000 comments every time a page is reloaded.

I’m not an expert at this, so I will continue to find ways to improve the service, so please bear with me in the various technical ups and downs. I promise to minimise them as much as possible and seek professional help too!

If you have any technical suggestions, please let me know.

Filed in: General
Tagged with:

Comments (4)

Trackback URL | Comments RSS Feed

  1. jack says:

    Set your post to show only 10 post on the first page and see what happens.

    It appears you have it set to 20 post now.

  2. mahmood says:

    Thanks Jack, I was thinking that too. I’ve reduced the number of posts Brain’s Latest Comments looks at as the last 10 instead of 20, and display the latest 3 commentors rather than 5.

  3. Shachar says:

    If you have any technical suggestions, please let me know.

    Yes, but unfortunately, not very helpful ones. What you would probably really want to do is switch to MoveableType, which uses static pages for display that are regenerated each time something happens. That will take all of the load clear off. Obviously, that’s not a very easy move.

    Shachar

  4. mahmood says:

    Thanks Shachar, but I don’t want to go through that pain again. I made moves before that cost me time, patience and much higher blood pressure; from PHP-Nuke to PostNuke to Xaraya to WordPress and the road ends here!

Back to Top