 |
The blog is back…October 31st, 2008 |
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…
You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.
). This is probably good to know.
