How to avoid SUSPENDED

[jah]

Ok, I know the topic of SUSPENDED pages has been revisited a bunch of times, but I want to know more. I want to know how to avoid them so I don't have to plunk down a bunch of money to find a better hosting company.

I'm running two WordPress blogs. They're both cached, and together they get about 1200 visits and about 6000 hits a day. I've got nothing particularly heavy running - I turned off everything with ajax, for instance.

I've looked at the cpu_exceeded_logs. Not surprisingly, the longest processes seem to be mysql queries. But that's where I get lost.

How do I know how to fix mysql? I can look at the mysql_slow_queries files, but I get nothing out of them. How do I tell how to fix my problem?

And why can't BH just kill the processes before they give the errors? How do other hosting companies handle this problem?

[jah]

Oh, one more thing. I just noticed that in the last two days, one of the blogs went from about 5k hits a day to about four times that, while the number of visits didn't change. Anybody know what's up with that? Is somebody trying to knock the blog offline? DoS attack?

[Yanyan]

Spiders/Bots.

They crawl ur site and they do it in masses.

[jah]

So nobody knows the answer to my questions?

Am I going to have to switch hosting companies?

[WomenFly]

One way to optimize your MySQL database is listed here: http://www.bluehostforum.com/showthread.php?p=33095#post33095

The other questions you raised are good. Try and be patient, this is a user forum.

[jah]

One way to optimize your MySQL database is listed here: http://www.bluehostforum.com/showthread.php?p=33095#post33095

The other questions you raised are good. Try and be patient, this is a user forum.
Ah. Thank you.

[charlesgan]

IMO.. the script must be well optimised.
eg.. if hosting forum site, i will choose vbulletin.
used smf before and they really use more cpu usage.

my point is, script optimazation.
second will be indexing your database

[jah]

I am just running a blog (well, two blogs). I don't know about script optimization.

And I'm assuming by indexing the database, you mean caching?

I tried that, but I'm still getting hit with this.

[jah]

It amazes me how little BH is willing to work on this. And yes, I realize this is a user-to-user forum.

It's just obvious BH is taking the easiest approach - just block an account when something goes over. No working with the user. You deal with it; it's your problem.

[areidmtm]

BH is not responsible for your scripts and what you put on it. If you put something on it that is not optimized for a shared hosting environment, then yes that would be your problem and responsibility, not BH. It's assumed that when you buy a hosting service that you know what you're doing. All BH does is provide you with the means of storing a website, they don't build it for you and fix your scripts.

And no, indexing your tables does not mean caching. Indexing is adding an index key to the tables so that the database can search faster. Search google on how to do this with either a remote MySQL GUI or phpmyadmin.

[jah]

But the other companies that I'm aware of contact users when there are problems. They try to help instead of just throwing up an error page.

And that's what I'm saying here. Ok, I can search google for what you said, but how do I read the tmp logs to figure out what the exact problem is?

[areidmtm]

I guarantee you that other hosting companies are not going to contact EVERY customer about one of their scripts failing. That is not realistic. Like I said, it's not the hosting companies responsibility to tell you that your script is not working and how to fix it. That's yours.

Post your logs

[jah]

This is the log for my first suspension yesterday:
--------------------------------------------------------------------------------
CPU Exceeded Log For Wed May 16 10:04:35 2007

Wed May 16 10:04:25 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/style.css HTTP/1.1
Wed May 16 10:04:25 2007: used 0.03 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/plugins/wp-ajax-edit-comments/js-includes/prototype.compressed.js HTTP/1.1
Wed May 16 10:04:29 2007: used 0.09 seconds of cpu time for /usr/bin/php index.php
Wed May 16 10:04:32 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/img/fbhead-r.gif HTTP/1.1
Wed May 16 10:04:32 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/img/fbbody-r.gif HTTP/1.1
After that, I turned off the ajax edit comments, hoping that might help. But then I got hit twice more yesterday. This is the last time yesterday. I'm posting it because it's shorter:


Wed May 16 14:45:40 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /feed HTTP/1.1
Wed May 16 14:45:50 2007: used 0.01 seconds of cpu time for HTTP Request: www.jonathanhickman.com : GET /feed/ HTTP/1.1
Wed May 16 14:46:49 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /2006/07/ HTTP/1.0
Wed May 16 14:46:53 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /2003/05/ HTTP/1.0
Wed May 16 14:47:53 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/js/pngfix.js HTTP/1.1
Wed May 16 14:47:54 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/img/comment.gif HTTP/1.1
Wed May 16 14:47:54 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/img/sbbody-l.gif HTTP/1.1
Wed May 16 14:48:03 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-admin/ HTTP/1.1
Wed May 16 14:48:03 2007: used 2.00 seconds of cpu time for [[mysql query]]
Wed May 16 14:48:04 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : POST /wp-admin/index-extra.php?jax=incominglinks HTTP/1.1
Wed May 16 14:48:04 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : POST /wp-admin/index-extra.php?jax=devnews HTTP/1.1
Wed May 16 14:48:06 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-includes/js/tw-sack.js?ver=1.6.1 HTTP/1.1
Wed May 16 14:48:09 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-includes/js/list-manipulation-js.php?ver=20070118 HTTP/1.1
Wed May 16 14:48:10 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-admin/cat-js.php?ver=20070118 HTTP/1.1
Wed May 16 14:48:37 2007: used 2.00 seconds of cpu time for [[mysql query]]
Wed May 16 14:48:42 2007: used 2.00 seconds of cpu time for [[mysql query]]
Wed May 16 14:48:47 2007: used 0.01 seconds of cpu time for HTTP Request: www.jonathanhickman.com : GET /2007/05/04/thank-goodness-my-neighbors-are-lazy-too/ HTTP/1.0
Wed May 16 14:48:51 2007: used 3.61 seconds of cpu time for /usr/bin/php post.php
Wed May 16 14:49:58 2007: used 56.00 seconds of cpu time for [[mysql query]]
Wed May 16 14:50:00 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : POST /wp-admin/post.php HTTP/1.1
Wed May 16 14:50:03 2007: used 0.19 seconds of cpu time for jonathd4
Wed May 16 14:50:03 2007: used 0.22 seconds of cpu time for jonathd4
Wed May 16 14:50:04 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET / HTTP/1.1
Wed May 16 14:50:04 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-includes/js/autosave-js.php?ver=20070116 HTTP/1.1
Wed May 16 14:50:05 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/img/csbhead-r.gif HTTP/1.1
Wed May 16 14:50:05 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/img/csbhead-l.gif HTTP/1.1
Wed May 16 14:50:05 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-content/themes/rounded-v2/img/fbbody-r.gif HTTP/1.1
Wed May 16 14:50:10 2007: used 0.02 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-includes/js/list-manipulation-js.php?ver=20070118 HTTP/1.1
Wed May 16 14:50:11 2007: used 0.01 seconds of cpu time for HTTP Request: katieallisongranju.com : GET /wp-admin/upload.php?style=inline&tab=upload&post_id=-1179348603&_wpnonce=8f80f1c866 HTTP/1.1
Wed May 16 14:50:13 2007: used 0.26 seconds of cpu time for /usr/bin/php wp-cron.php

[jah]

I don't know what kicked it over on the first one. The second one it looked like the mysql query. But maybe I'm reading it wrong and several things did it?

And I don't know what slow mysql logs to refer to to find what the problem queries were, or how to translate them once I find them.

[areidmtm]

you're going to have to post the mysql_slow_quires log

[Falcon4]

Turning off AJAX (if you can turn that "off"?) is almost absolutely certainly going to increase your CPU load, not decrease it. The whole purpose of AJAX is to lessen the amount of data needing to go back and forth between user and server, by not having to re-generate and re-send the entire page every time you do something. It'd be a good idea to turn it back on.

And I also have to say, Bluehost should *really* assign some people to work here in the forum. Many people have problems they would like to get multiple peoples' input on, instead of a short, definitive, and often incorrect reply by the Bluehost guys (e.g. it's not login.bluehost.com's fault, it's mine). If we could actually have some Bluehost real support guys here, I'm sure people would be a lot happier. I like the forum structure a lot more than the "support" structure anyway =P

[jah]

you're going to have to post the mysql_slow_quires log
Yesterday there were 24. How do I know which one to look at/which one to post? This is part of my problem.

[jah]

Turning off AJAX (if you can turn that "off"?) is almost absolutely certainly going to increase your CPU load, not decrease it. The whole purpose of AJAX is to lessen the amount of data needing to go back and forth between user and server, by not having to re-generate and re-send the entire page every time you do something. It'd be a good idea to turn it back on.
Really? I always thought ajax was hard on servers.

This plugin was to allow users to edit their comments, so turning it off just took away their ability to do that. It's really a shame, because they should be able to, but my first priority is having the page visible.

[jah]

I just got hit 3 more times in thirty minutes.

I never see this on any other host. You can say "no host is going to" this or that, but WP is a popular blogging platform, and it's popular because it's easy. I doubt too many people have to put up with this kind of stuff. I never see the suspended pages, and I'm doubting that's because they're doing lots of backend stuff.

This sucks.

[areidmtm]

then go somewhere where they allow you to run insufficient script.

[jah]

Well, really I wasn't wanting to move hosts. I was wanting help with why I'm having this error. I merely said that I don't think most hosts handle it this way, but since this one does, you'd think they'd be more helpful about it.

Now, I'm trying to figure out which script is causing the problem. Like I said, I've got a ton of slow mysql logs, how do I know which one to look at/post?

[Basil]

Well..
Wed May 16 14:49:58 2007: used 56.00 seconds of cpu time for [[mysql query]]
This is obviously the problem, but you're going to have to match that to the other logs.

Also I'm convinced bluehost doesn't calculate cpu usage correctly, but I don't really care enough to try and prove it.

[jah]

Obviously, in that case. But I don't know how to tell what the query was.

And if you look at the first one, the longest query is 9 seconds. So I guess that one was a combination. How do I figure out what caused it that time?

[klamation]

I'd suggest checking your mysql_slow_queries folder for a file stamped at the time you had the error. A quick look over your account and you'll probably notice certain queries that take drastically longer time than others. Having such things as a search feature could take up a lot of time, especially if you have a lot of metadata for your blog entries.

[Early Out]

Also I'm convinced bluehost doesn't calculate cpu usage correctly, .....Didn't I read a thread a while back in which someone discovered that if they turned off awstats on their account, their CPU problems vanished? In other words, awstats was slowing down other things, and those other things were then regarded as "taking too long." Someone should tell BH to optimize the code they're running!

[Dormouse]

I never see this on any other host. You can say "no host is going to" this or that, but WP is a popular blogging platform, and it's popular because it's easy. I doubt too many people have to put up with this kind of stuff. I never see the suspended pages, and I'm doubting that's because they're doing lots of backend stuff.

You need to remember that the Bluehost system has lots of small suspensions. Other hosts tend to let things run on for longer, but then suspend the account and require improvements for it to be re-instated.

[aceofspades]

i got suspended for "copyrighted material" ouch.

[jah]

Didn't I read a thread a while back in which someone discovered that if they turned off awstats on their account, their CPU problems vanished? In other words, awstats was slowing down other things, and those other things were then regarded as "taking too long." Someone should tell BH to optimize the code they're running!
Yeah, some people found that awstats caused a suspension when it refreshed, but it only refreshes, what, once a day? So that only causes a daily suspension, not a 3-4 times a day suspension.

[jah]

I'd suggest checking your mysql_slow_queries folder for a file stamped at the time you had the error. A quick look over your account and you'll probably notice certain queries that take drastically longer time than others. Having such things as a search feature could take up a lot of time, especially if you have a lot of metadata for your blog entries.
This is what I got from that method:
# Thu May 17 14:34:02 2007
# Query_time: 79 Lock_time: 0 Rows_sent: 5 Rows_examined: 1929934
use jonathd4_wrdp5;
SELECT meta_key
FROM wp_postmeta
GROUP BY meta_key
ORDER BY meta_id DESC
LIMIT 30



# Thu May 17 14:57:23 2007
# Query_time: 41 Lock_time: 0 Rows_sent: 7 Rows_examined: 1929942
use jonathd4_wrdp5;
SELECT meta_key
FROM wp_postmeta
GROUP BY meta_key
ORDER BY meta_id DESC
LIMIT 30




# Thu May 17 15:42:07 2007
# Query_time: 114 Lock_time: 0 Rows_sent: 7 Rows_examined: 1929942
SELECT meta_key
FROM wp_postmeta
GROUP BY meta_key
ORDER BY meta_id DESC
LIMIT 30

# Thu May 17 15:42:07 2007
# Query_time: 19 Lock_time: 17 Rows_sent: 10 Rows_examined: 3488
use jonathd4_wrdp5;
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(post_date)='2004' AND MONTH(post_date)='2' AND (post_type = 'post' AND (post_status = 'publish')) ORDER BY post_date DESC LIMIT 0, 10

# Thu May 17 15:42:08 2007
# Query_time: 101 Lock_time: 98 Rows_sent: 7599 Rows_examined: 15198
use jonathd4_wrdp5;
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN(798,797,796,795,794,793,792,791,790,789) ORDER BY post_id, meta_key

# Thu May 17 15:42:08 2007
# Query_time: 98 Lock_time: 95 Rows_sent: 7599 Rows_examined: 15198
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN(798,797,796,795,794,793,792,791,790,789) ORDER BY post_id, meta_key

# Thu May 17 15:42:08 2007
# Query_time: 114 Lock_time: 111 Rows_sent: 7599 Rows_examined: 15198
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN(798,797,796,795,794,793,792,791,790,789) ORDER BY post_id, meta_key



Anybody know how to translate that? Are they searches?

[Basil]

Okay, your 'wp_postmeta' table is huge. Two million rows actually.

You need to clean this table up.

I can't say how because I don't know what's in it.

[jah]

What is it?

[jah]

Okay, your 'wp_postmeta' table is huge. Two million rows actually.

You need to clean this table up.

I can't say how because I don't know what's in it.
Couldn't it just be huge because it's 5 years of blogging?

[Pethens]

There's lots of useful information in your slow queries: columns that probably need to be indexed, queries that should be optimized. I suggest posting your slow queries at the Wordpress developer forums.

[jah]

I think I've figured out the problem. And yes, it's going to sound negative.

When I imported this blog a few weeks ago from blogger, I did it via xml. I posted about it at the time, because BH wouldn't allow a real import. It kept freezing up and I kept having to reload. When I filed a ticket, they simply said good luck, and at least I was making some progress.

Well, I think all those stops and starts caused serious problems. I just did the same thing but with the new blogger import in WP 2.2, and the blog is much cleaner and doesn't have any 100+ MB databases.

Of course I'm not really sure where to go from here, but at least now I know.

[klamation]

I think I've figured out the problem. And yes, it's going to sound negative.

When I imported this blog a few weeks ago from blogger, I did it via xml. I posted about it at the time, because BH wouldn't allow a real import. It kept freezing up and I kept having to reload. When I filed a ticket, they simply said good luck, and at least I was making some progress.

Well, I think all those stops and starts caused serious problems. I just did the same thing but with the new blogger import in WP 2.2, and the blog is much cleaner and doesn't have any 100+ MB databases.

Of course I'm not really sure where to go from here, but at least now I know.

I wish I knew why you think BH doesn't allow a real import. If you could get your blogger site to export as SQL, you could have imported it into a database just fine, assuming it used a format Wordpress recognises. But, what's done is done, and now you have almost two million rows of postmeta that get searched whenever someone tries to search your website. Check with Wordpress to see how to disable this.

[GoogleBot]

Going back to: "How to avoid SUSPENDED" Wordpress is a well known publishing platform, however WordPress is often maligned as a resource-hungry blog platform. I am using Pligg, which I have previously tested several times on at least 2 "free servers" and i never got such CPU problems, nevertheless Pligg uses more resources than Wordpress.

The "grey screen of death" appears when I download a mysql backup.

Regards :)

[jah]

I wish I knew why you think BH doesn't allow a real import. If you could get your blogger site to export as SQL, you could have imported it into a database just fine, assuming it used a format Wordpress recognises. But, what's done is done, and now you have almost two million rows of postmeta that get searched whenever someone tries to search your website. Check with Wordpress to see how to disable this.
At the time, a few weeks ago, I had a topic on here (to which I got rude replies), I had discussions on WP forums, a separate forum on migrations, and had several tickets with BH. Everyone was pointing fingers at other people, and I think it was ultimately BH's fault (mixed with WP not having a great import tool, although they do now).

BH doesn't allow a WP to WP import via the import mechanism. You can do it via mysql, but that didn't do me any good since I didn't have (that I'm aware of) mysql access. If you try importing an xml file via WP, you first run into size problems - BH won't allow files that big that way. I got them to fix that. But then it saw it as a long process that it had to kill. So it imported 0-5 posts ata time. I did 5 years worth of posts that way, and it blew up the blog.

Apparently no one saw that consequence.

So although it wasn't entirely BH's fault, no other host that I'm aware of would've caused that problem.

[Basil]

Wow, you could have gzipped the backup and imported it through phpmyadmin, which is under mysql databases under cPanel.

If your backup was more than 50mb gzipped then you have some real problems with your database.

[jah]

You can gzip with blogger?

[jah]

I searched the whole internet, asked multiple people, asked multiple forums, created tickets, and no one knew of a better way to do it.

I kid you not, BH said something very similar to "good luck with that."

[Basil]

I don't know, I've never used 'blogger' before, this (http://catsutorials.catsudon.org/?p=15) evidently has instructions on how to export data, which I assume you could just gzip and upload and import or something I don't know.

Either way it's not really the host's job to tell you how to do that, all they do is provide space and software for you to use.

[jah]

But the traditional way of doing it is with xml file, and BH is the only host I'm aware of that won't allow you to import xml.

[jah]

Oh, and I don't think that method works anymore due to changes in blogger. But now WP has its own, much superior importer.

[Falcon4]

Um, XML is trash. SQL files are the standard for backing up SQL data. Hackish and trashy, but considerably universal...

Basically, if a hosted program has import and export functions, it always will, no matter where you host it. The program is the same no matter where you go, unless you're going from a new version on the backed-up site to an older version on the new site (this one). Then the old version may not have the capability. But in any possible case, Bluehost isn't "not allowing" you to import XML junk.

[jah]

Sorry I'm late at replying to this.

XML may be trash, but it's the only way I'm aware of exporting a wordpress.com blog.

BH doesn't allow large imports without getting some settings changed, and that's not uncommon. But once you are allowed to import, BH times out. That's how they ultimately don't allow an xml import - not because they "don't allow it" but because it times out.

So they're not saying I can't do it in theory, they just make it impossible to do it in reality.

[klamation]

As far as importing into your database goes, I would assume the issue is the limitations of phpMyAdmin, not specifically BH "allowing" you or not. There are multiple tools out there that let you remote administer a database, including EMS SQL Manager 2005 Lite for MySQL which I've used to access my own databases (it's free!)

I take it the issue is no longer importing the database, but how to avoid the huge mysql queries that suspend your account for minutes at a time. I would suggest using something like EMS SQL Manager (or some other remote mySQL program) to clean up your database.