The blog is back…
Once again, excessive CPU on SQL queries. Read on for the pain…
So, first off, Bluehost did tick me off by simply shutting off my whole account. No warning. No FTP access. No cPanel. Nothing. So there was no way to even diagnose what had gone wrong, without calling them. So I call them, and get a recording saying “sorry, we’re closed for the holiday, until 2:30pm.” Closed for Halloween? During the middle of the day? Uh…
At 2:30pm mountain time, i called them, and got access to the logs, PHPMyAdmin, and cPanel. With that, i went to take a look at the logs. And what I saw was this query, over and over and over.
SELECT * , IF( DATE_ADD( link_updated, INTERVAL 120
MINUTE ) >= NOW( ) , 1, 0 ) AS recently_updated, UNIX_TIMESTAMP( link_updated ) AS link_updated_f
FROM wp_links
INNER JOIN wp_term_relationships AS tr ON ( wp_links.link_id = tr.object_id )
INNER JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE 1 =1
AND link_visible = 'Y'
AND (
tt.term_id =17
)
AND taxonomy = 'link_category'
ORDER BY link_name ASC
LIMIT 0 , 30
This query — which is right there on the front page, I suspect — was taking 2 seconds. Putting that in PHPmyAdmin revealed that it pulls up some of the link list. A quick search of WordPress.org revealed this link, which recommended indexing the table. I did so, and also optimized all the tables, and the query time fell to 2/100ths of a second. With that, BlueHost was willing to open up the site again. They also switched me over to FastCGI, so keep an eye out for any stuff that seems like it ought to work and doesn’t.
So we’re back. But this is getting old…
10 Responses to “The blog is back…”
Sorry, the comment form is closed at this time.
I thought you were going to move your website over to the Metaplace servers? I don’t know why you continue to put up with BlueHost’s crap. Any hosted services provider worth their weight in gold wouldn’t have given you as much grief.
Since your blog is mostly text have you considered just hosting it wordpress.com and letting them deal with the hosting issues?
Granted it wouldn’t “flow” with the rest of the site, but maybe you could just grab the rss feeds and display them.
Hrm, one of the sites I’m running currently is using BlueHost. We’ve had some issues in the past too – but nothing as serious as what you’ve been going through (of course, we also don’t get the traffic you do either :-P). This is probably good to know.
Wow, guy, that is ridiculous. With so many great options available out there for reliable hosting, there is no way I would stay with a web host after more than one such incident, if even that. Taking a customer completely offline and leaving them that way, knowing that you will have no support staff available to clear the matter up, is unforgivable. With so many available options I think I’d start shopping around after they shut me down the first time. I’m sure you have your reasons for sticking with them, though.
My $0.02
Inertia.
WordPress doesn’t automatically index the tables they use…? That’s… stupid. Or is this a plugin developer’s incompetence? It’s a blog. Everything should be indexed. :/
Michael Chui:
I don’t know whether that’s true, but there is a great cron plugin called WP-Crontrol. I combine that plugin with WordPress Database Backup, but I’m sure one can write a plugin to index and optimize the database on a regular basis.
To celebrate that the blog is back, I’ve put all your posts from August till today into Wordle:
Hmm.. it ate the link. Try again : http://www.wordle.net/gallery/wrdl/283795/Raph_Koster_Blog
I like how “world”, “game”, and “people” are the same size. 😛