What have I done?

My blog is down and I don’t know why or what to do.

First, and foremost, thank you, Mark, for allowing me to post on your blog about this.

Some of you may have noticed that my blog, www.nichestorestrategies.com, is currently down. HostGator oh, so kindly blocked the database on this site, without any warning. The email I received from them said,

“This message is to advise you of a temporary block placed on your database. The account/database referenced above was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system’s CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case.

Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.

If you believe you have a solution to this overuse, we are happy to discuss the situation with you and possibly reinstate the database on the server. Otherwise, we will be happy to assist you with the upgrade process if a dedicated server is the most appropriate solution. Thank you, and we look forward to hearing from you shortly.

~~~

Excessive MySQL activity is caused by (a) a long-running process that locks a table, causing other queries to back up, (b) a query that is not optimized ][example: select all from ... and involving a large or complex query], (c) huge table copies/maintenance during peak hours.

NOTE:, the following are just possible fixes or suggestions, and are not endorsed or supported by HostGator. They are included in the hope that they may apply to your situation, and/or help you reduce the amount of resources your SQL queries consume. As always, it’s best to backup any data before making any changes or adjustments.

First and foremost, you may need to optimize your tables. The frequency depends on the size and usage of the database, but most databases would benefit from doing something like this on a yearly basis: a) Enter your phpMyAdmin/MySQL control panel. Click on the database (not the table, the database name), and on the right hand column your tables should be listed. Scroll down till you see the .Check all. link. Click on that link, make sure all database tables are checked and then from the dropdown next to it, and carefully select .Optimize table..

Additionally, adding indexes to your table(s) may improve performance. If you’re not sure what you’re doing, it’s best not to modify any table; caution is recommended. There are various articles (http://www.whenpenguinsattack.com/2006/03/11/optimizing-mysql-tables/).

It may be best to google for something like [Your Software Name] MySQL indexes for suggestions.

WordPress Users: (http://wordpress.org/): It is important to keep up to date with this software; vulnerabilities have been found in past versions. Regardless, installing the WordPress Cache plugin can greatly reduce resource usage and improve performance (http://wordpress.org/extend/plugins/wp-cache/). If you’re not using the current version of WordPress, keep in mind that v2.1+ is much faster than older versions.

| 750375 | root | localhost | | Query | 0 | | show processlist ”

My first thought was, “Huh?” I truly didn’t know what this meant or what I should do. After giving this some thought I remembered that I had setup a cron job and set it to run every 15 minutes. I promptly deleted the cron job and told HostGator what I had done. I also said that I would install the WP-Cache plugin as soon as I had access to the site. Apparently this wasn’t the problem or resolution.

HostGator responded with:

“It was actually caused by MySQL including the line that we previously sent to you:

749151 | xxxxxxxxx | localhost | xxxxxxxxx| Query | 0 | Sending data | SELECT * FROM wp_statpress LIMIT 146400, 100 || 750375 | root | localhost | | Query | 0 | | show processlist”

I responded by saying I’d be happy to try and fix the issue (not that I know how). I also asked why I wasn’t warned of the problem before my database was blocked and my site disabled (note this question does not have an answer). HostGator responded with:

“These databases where disabled to prevent further problems from them. We can enable them locked to your IP so you can work to reduce the problems with them.

www.hostgator.com/ip.html for your current IP.”

I then said, again, that I’d be happy to try and fix the issue but that I didn’t know what to do. I again asked why I wasn’t warned of the problem before my database was blocked and my site disabled (note this question STILL does not have an answer). HostGator responded:

“You need to rewrite your MySQL queries but how to do that or how they should be rewritten is beyond our scope of support.”

So, here I am, with no working site, no way to access they database they want me to fix, and no idea what to do. I am frustrated that they did this without warning me, and now won’t respond to why they did this without warning me. I’m confused because I have no idea what the heck the problem is or how to fix it. I Googled ‘how to rewrite MySQL queries’ but didn’t come up with anything that made sense to me.

My HostGator hosting plan is the Baby Croc.

Do any of you helpful readers have any idea what I did, or how I can fix it? If so, I would be very grateful for any help you can provide.

And, again, my appreciation goes out to Mark for giving me space on his blog to help me resolve this.

Oh, one last thing. I do have another hosting account I could move the database to, and I do have a slightly outdated backup of the database. I can, eventually, try to do move it to the other host, but the backup I have is from 7/14/08 so many comments and/or posts will be missing (shame on me for not having a current backup!) so I’d rather resolve it with HostGator.

Rochelle