Many of you may have noticed that this site was down for a few days earlier this week.  It caused me a bit of panic, but now that the issue is resolved and the site is back up, I understand what happened.  Even better, I understand (for the most part) how I can prevent this from happening again.

Monday morning I received an email from HostGator (I currently have their Baby Croc plan).  The email was a bit cryptic (to me, at least):

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.

749151 | XXXXXXXXXX| localhost | XXXXXXXXXX | Query   | 0    | Sending data | SELECT * FROM wp_statpress LIMIT 146400, 100 |

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

Clearly there was a problem, but what it was or what I needed to do was beyond me.  This is when panic set in.  Fortunately for me, Mark at TheNicheStoreBuilder was kind enough to let me write a post about this and publish it on his blog.  With the help of Mark and his readers I was able to figure out (ok, I did not do any figuring, I was told) the problem was a plugin I had installed on this site.   It was the last bit of information from HostGator’s that showed the problem (see the red fonted text above to see what I am referring to).

The Problem was a Plugin

Turns out, the offending plugin, wp-statpress, was creating a huge drain on HostGator’s resources.  That was why they blocked the database, which, in turn, caused this site to go down (it is powered by the database they blocked). 

How I Resolved the Plugin Problem

Now that I knew the cause of the problem (the wp-statpress plugin) I needed to do two things:

• Delete the plugin from my site
• Delete the database table referrencing this plugin

First, I opened my FTP client.  I was shocked to see that all my site’s files and folders were gone.  In their place were a huge number of core files.  I thought all my files were gone.

There were not really gone.

When I logged in to my hosting account’s cPanel and used the File Manager I was able to see that the files were still where they belonged.  I suspect that HostGator did something to somehow prevent the files from being seen with an FTP client, though I do not know this for sure.

Now that I could see the files, I navigated my way to the plugins folder and deleted the wp-statpress plugin.  Step one was done.

Step two was a bit trickier.  HostGator blocked my database, which meant it was no longer in my cPanel.  I sent an email to HostGator’s technical support and said I had removed the offending plugin, and asked for access to the blocked database so I could remove the offending table.  Within minutes I had access to the database.  

The very first thing I did was backup the database.  Then I went into the phpMyAdmin, located the database for this site, found the table for the wp-statpress plugin and deleted it. 

Initially I was going to add screenshots and include instructions for what I did, but since doing anything to a database is risky business I have decided not to do this.  I do not want to be responsible for any mucked up databases.  If you run into this same problem and what I’ve said makes sense to you, please back up your database before you do anything to it.

Initially I attempted to resolve this issue through HostGator’s email technical support.  This did not work very well.  Ultimately, I used their online chat, which was great.  If you run into a similar problem I highly recommend you try online chat before using email support.

So, now you know how a single plugin crippled my site.  Apparently, stat plugins are very likely to do exactly what this one did.  Use them with care, if at all.

How I will Prevent this from Happening Again

I now know that the wp-statpress plugin created a huge number of records in my database.  I didn’t pay close enough attention to it before I deleted the table, but I recall that this plugin had created hundreds of thousands of records. 

What I will do in the future is regularly check my wordpress databases, via phpMyAdmin, to make sure none of the installed plugins are created an excessive number of records.  If I see this happening I will delete the offending plugin before my site is shutdown.

Here is where to look for excessive records:

Be aware that Build A Niche Store (aka BANS) sites may create several thousand records but these do no seem to be an issue for HostGator.  Rather, it seems to be WordPress related plugins that tend to cause problems.

A Few Final Words

Regarding the core files created from this, I asked HostGator if I need to keep these or if I can delete them.  I was told it is ok to delete them.  I gladly did so, as there were almost 2,000 core files created.

When this happened I was upset that HostGator did not notify me before they shut down my site.  One of Mark’s readers explained a very plausible reason for why they did this.  He said (shared here with his permission):

“Have you ever had your site start running really slowly and have things timed out? If so odds are you called, emailed, or chatted with the hosting company about it.

Well, if one account on the server takes a huge load of the processing power, it can affect all of the other users on that server. So in Rochelle’s case, she may have created a problem for thousands of others sharing her server.

In turn, it may have caused 100 calls, emails, or chat sessions from affected users wondering why their Cat site is not loading right.

At 10 a month profits get eaten up quickly by the hosts in customer service when these things happen, as you can well imagine. So instead of waiting for the person creating the problem, they address it immediately to cut down on the support costs. So Rochelle was the one affected generating a few support calls but the rest of the users on her server slept well not worrying about there service.

Now I have to admit, when I first started out, I had a site that was trashing the servers due to following a big story. And I was mad as hell, and a little scared, when they pulled the plug on me.

But now I understand where the hosting guys are coming from. Just last month one of my sites, not BANS unfortunately, got linked to on the front page of MSN for 4 hours. Well, 15,000 visitors and 4 hours later, my site was neatly shut down by Hostgator.

I screwed the pooch for everyone else.

But all it took was one phone call to HG explaining what happened and that the link was off MSN now, and I was up and running again in a few minutes.

To bring it all together, the tech guys at Hostgator are not trying to be merciless and cruel when they shut you down for using too many resources. They are instead recognizing that if you are using a shared plan and abuse it (intentionally or accidentally), they have to stop you before you ruin the experience for everyone else, and create 100 more calls from those you have affected.”

Rochelle