Site disabled by Hosting Company: MySQL usage spikes

We just cut over to a new design for our blog last night, as well as upgraded from 1.5a6 to 1.5 Strayhorn.

Sometime this afternoon, the hosting provider shut the site down because the MySQL usage from the new installation had driven the load on that server to over 40.

I disabled the only plugins I had installed and active, auto-close-comments and Bot-Check, but that hasn’t helped.

So I’ve had to abandon the new site relaunch, and go back to the old CSS using 1.5a6

The admins at the provider are saying that it looks like poorly written SQL code is the culprit, and they emailed me a list of processes that are causing the problems, and their comments on them:

First message:
example of show processlist – there are lots of these that show up
simultaneously – generally the “Copying to tmp table” should be avoided
or limited at much as possible

| 196 | Query | 2
| Copying to tmp table | SELECT cat_ID, cat_name, category_nicename,
category_description,
COUNT(wp_post2cat.post_id) AS cat_count
FROM wp_categories
INNER JOIN wp_post2cat ON (cat_ID = category_id)
INNER JOIN wp_posts ON (ID = post_id)
WHERE post_status = ‘publish’
AND post_date_gmt < ‘2005-03-18 20:48:04’ AND cat_ID <>
1
GROUP BY category_id |
| 197 | Query | 1
| Copying to tmp table | SELECT cat_ID, cat_name, category_nicename,
category_description,
COUNT(wp_post2cat.post_id) AS cat_count
FROM wp_categories
INNER JOIN wp_post2cat ON (cat_ID = category_id)
INNER JOIN wp_posts ON (ID = post_id)
WHERE post_status = ‘publish’
AND post_date_gmt < ‘2005-03-18 20:48:05’ AND cat_ID <> 1
GROUP BY category_id |
| 198 | Query | 2
| Copying to tmp table | SELECT cat_ID, cat_name, category_nicename,
category_description,
COUNT(wp_post2cat.post_id) AS cat_count
FROM wp_categories
INNER JOIN wp_post2cat ON (cat_ID = category_id)
INNER JOIN wp_posts ON (ID = post_id)
WHERE post_status = ‘publish’
AND post_date_gmt < ‘2005-03-18 20:48:04’ AND cat_ID <> 1
GROUP BY category_id |

Second message:
other possible culprit – “distinct” is also something to be very careful with

| 113 | Query | 0 | Copying to tmp table | SELECT DISTINCT YEAR(post_date) AS year,
MONTH(post_date) AS month, count(ID) as posts FROM wp_posts WHERE
post_date < ‘2005-03-18 12:56:58’ AND post_status = ‘publish’ GROUP BY
YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC |

| 114 | Query | 1 | Copying to tmp table | SELECT DISTINCT YEAR(post_date) AS year,
MONTH(post_date) AS month, count(ID) as posts FROM wp_posts WHERE
post_date < ‘2005-03-18 12:56:57’ AND post_status = ‘publish’ GROUP BY
YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC |

| 115 | Query | 0 | Copying to tmp table | SELECT DISTINCT YEAR(post_date) AS year,
MONTH(post_date) AS month, count(ID) as posts FROM wp_posts WHERE
post_date < ‘2005-03-18 12:56:58’ AND post_status = ‘publish’ GROUP BY
YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC |

| 116 | Query | 0 | Sorting result | SELECT * FROM ibf_topics WHERE forum_id=10 and
approved=1 and (last_post > 1105995418 OR pinned=1) ORDER BY pinned
desc, last_post DESC LIMIT 0,30|

| 117 | Query | 0 | Copying to tmp table | SELECT DISTINCT YEAR(post_date) AS year,
MONTH(post_date) AS month, count(ID) as posts FROM wp_posts WHERE
post_date < ‘2005-03-18 12:56:58’ AND post_status = ‘publish’ GROUP BY
YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC |

| 118 | Query | 0 | Copying to tmp table | SELECT DISTINCT YEAR(post_date) AS year,
MONTH(post_date) AS month, count(ID) as posts FROM wp_posts WHERE
post_date < ‘2005-03-18 12:56:58’ AND post_status = ‘publish’ GROUP BY
YEAR(post_date), MONTH(post_date) ORDER BY post_date DESC |

Any help here would be greatly appreciated