ISP Claims WP is Spiking System Resources -How?

  • I have a relatively small blog with 260 total blog posts. I have the following 4 plugins loaded:

    – Semiologic CMS
    – Structured Blogging
    – List Posts in Category by Chris McCafferty
    – Akismet

    I have a little over 800 comments and 95 akismet spam entries in the 5 months the blog has been active. I have an average of 320 visits a day to my site.

    My ISP claims that WP is spiking the system resources on the server for very brief periods. They have not been able to isolate the problem, but they have given me one MySQL query they claim could have been problematic. They claim this query took 22 seconds:

    SELECT^M
    comments.*, posts.*^M
    FROM^M
    wp_comments as comments^M
    INNER JOIN^M
    wp_posts as posts^M
    ON posts.ID = comments.comment_post_ID^M
    WHERE^M
    posts.post_date_gmt <= ‘2006-05-17 17:51:00’^M
    AND posts.post_password = ”^M
    AND comments.comment_approved = ‘1’^M
    AND^M
    (^M
    posts.post_status = ‘publish’^M
    OR^M
    posts.post_status = ‘static’^M
    )^M
    AND comments.comment_date >= ‘2006-05-07 00:00:00’^M
    GROUP BY^M
    posts.ID^M
    ORDER BY^M
    comments.comment_date DESC;

    As far as I know, I haven’t modified the comments at all. They (Inmotionhosting.com) are threatening to take down my server unless I fix this problem. Does anyone know if this level of usage would cause a large shared server issues, and more specifically, how can I fix this? Is anyone aware of whether these plugins are system resource intensive?

    Thanks in advance.

Viewing 11 replies - 1 through 11 (of 11 total)
  • Moderator Samuel Wood (Otto)

    (@otto42)

    www.remarpro.com Admin

    If you want my advice, switch hosts. That’s a single inner join on a single column pair with only a few where clauses. If that’s spiking their database server, then they need to a) find somebody with a clue to run their systems and/or b) get better servers.

    Seriously, demand a refund and switch hosts. There’s too many hosts out there to put up with bad ones.

    Thread Starter sfam

    (@sfam)

    I didn’t think I’d modified the comments query at all. Is there anything else for this volume of traffic to a WordPress site that would cause spikes in server resources use?

    I run similar volumes (daily visits) as you do. Way more blocked akismet spam (and an obscene amount of stuff that Bad behavior blocked too). I’m self-hosted on an old athlon 900 with 512MB that never seems to even get close to breaking a sweat.

    I’m no rocket scientist, but something doesn’t seem right. I’m wondering if they grabbed the wrong query to show as an example.

    Using phpMyAdmin, visit the following table’s structure:

    wp_comments

    Look at the list of indexes. Verify that the following field has an index:

    comment_post_ID

    If it doesn’t, click the link to add it.

    Moderator Samuel Wood (Otto)

    (@otto42)

    www.remarpro.com Admin

    Denis: It’s true that having proper indexes matters, but with only 260 posts and 800 comments, lack of an index isn’t going to bring down his server.

    With a real database, I’d readily agree with you. But with a toy like mysql, I can only report that I’ve seen a left join on 200×1000 rows take 10+ seconds on a xeon server, and .2 seconds after I added the index…

    My personal favorite is this one:

    CREATE TABLE s (s smallint);
    INSERT INTO s (s) VALUES (99999);
    SELECT * FROM s;

    DB admins usually turn greenish white after seeing the above three lines return 32767 with no warning or error in non-strict mode.

    I thought I’d add myself to the list of folks getting hassled because their wordpress blog is using up server resources. This is the second time that my website has been suspended because of the blog (this time, I’ve decided to switch hosts because my old host has been nothing but useless). I <i>don’t</i> have a highly commented blog, although there is certainly a lot of spam that attacks the blog (but doesn’t get through, because of a variety of plugins that I use).

    I think this problem had something to do with the new WP update (not that there’s something wrong with the program in general, but rather that something weird happened on my particular blog when I updated). I kept the same theme that I’ve had for about a year and I had only four plugins activated: akismet, autoshutoff, bad-behavior, and tbvalidator.

    I tried getting information from my host about the particular problem, but this is how <i>that</i> email conversation went:
    —
    Me:
    Hello, my site has, yet again, been suspended. Might I ask why? Thanks.
    —
    My Host:
    Hello!
    The site was suspended by the same reason as it was suspended few weeks ago: Your site is suspended because it uses a high amount of server resources. We are not able to host the site in its current state.
    —
    Me:
    Thanks for replying, although I would like more information as to what specifically was causing the problem (do you know what PHP code or query was causing the server resources to be used up?) so that I know what to do to change the site. Simply knowing that it uses server resources isn’t helpful in helping me fix it.
    —
    My Host:
    The problem is with your blog again.
    —

    No shit, shirlock. At that point I decided that the solution was to switch hosts.

    SO…I have no real idea what it was about my blog that caused such a server meltdown. Last time they said it was because of the spam, especially spammers hitting the comments-post.php page. I then installed the various plugins which I assumed helped with that. (It certainly cut down on the bandwidth.) I’ve switched hosts and hope to have things back to normal. I worry, though, that there will be a similar stress on the new ISP. I’ve read elsewhere that there’s a cache plugin that will help with the problem, that I hope to try once my site is back online.

    As I said, I don’t know if this is a problem with the WordPress update, my host being wretched, some wires being crossed somewhere in the blog/plugins/theme, or some healthy combination of all three. I’ll do a clean install of WordPress once I get online with the new host and we’ll see if that takes care of the CPU problem.

    @dennis
    Your sample took around 0.2 seconds without indexing or anything special on my 1.8Ghz Intel Win 2K box.

    I disagree with your evaluation of MySQL as toy database. It is pretty robust and compares favorably with Oracle in terms of performance. Run some real test, I have done it.

    @michellealaska

    I have worked with performance problems on my blogs and solved them. You may find the following articles helpful:
    How To Improve Performance of Highly Commented WordPress Blogs

    MySQL Performance Optimization – A Lesson

    Hi angsuman, thanks for the response. I saw this else somewhere and am definitely planning on implementing some of your suggestions. I just have to get my site back online (and the NEW host also appears to be dragging their feet. *sigh*) before I can tinker with the blog.

    Thanks again!

    @angsuman: I suppose that selecting 32767 where you stored 99999 is not an issue in apps you create. ??

Viewing 11 replies - 1 through 11 (of 11 total)
  • The topic ‘ISP Claims WP is Spiking System Resources -How?’ is closed to new replies.

Tags