MySQL Copying to Tmp Tables Causing Server Overload

We’re hosted on a VPS. Server logs say we get about 200k visits/500k page views per month. Site was imported from Blogger into WP in March 2005. It’s a political site so subject to traffic spikes depending on the news.

The last two spikes have cause server overloads. The first one, five days ago, caused the database server to go offline persistently. Here’s how the hostco tech described the problem then:

“I was about to send an abuse ticket to you and then noticed that you opened a ticket with our support. Your system currently kills performance for all accounts on the node and this activity needs to be resolved right away. I was monitoring your system for about last 5-10 minutes and found that a) your MySQL is dying due to huge amount of (broken) queries it receivess and b) load is caused by queries that start from:

SELECT DISTINCT * FROM pr_posts WHERE 1=1 AND post_date_gmt <= ‘2007-10-28 21:00:59’ AND (post_stat…

Here is what kills your MySQL operations…”

And then he pasted in this slice of MySQL codes:

Oct. 28 Slice

I searched the Codex and Google but really did not/do not understand the problem well enough to frame the question accurately. I saw some situations here that looked similar but nothing definitive.

So my solution was to delete all plugins and upgrade to WP 2.3.1. That solved part of the problem. (The most recent addition had been the AddThis plugin. The only plugin active now is Akismet.)

Two days later we had another large spike in traffic. This time the server started running very slowly but never fully crashed.

Hostco tech says:

“It might be a good idea to get in touch with WordPress developers / support / community to find out what and how needs to be done to improve performance of the (at least) following database queries:

SELECT DISTINCT YEAR(post_date) AS year, MONTH(post_date) AS month, count(ID) as posts FROM pr_p…

and

SELECT t.*, tt.*, tr.object_id FROM pr_terms AS t INNER JOIN pr_term_taxonomy AS tt ON tt.term_id =…

Usually “Copying to tmp table” happens when database structure doesn’t correspond to structure of the sql query and as such MySQL is forced to create a temporary table, copy data there and perform requested sorting / grouping on data copied into the temporary table. Such operation is very CPU and disk I/O intensive and during high load may render your site(s) / application(s) pretty much unusable.”

He sent this slice:

Oct. 30 Slice

Here’s roughly my level of expertise: Started working in web dev in ’95, mostly in creative. In the early days I was creative dir for a company that built custom apps and hosted client sites. Small shop, very hands on, but Windows only.

Left web dev in ’01 but since ’05 have installed a dozen or so WP blogs for clients and friends. Never had any problems that I couldn’t solve by reading here or elsewhere.

However, I’m just shy of ignorant about MySQL, and these slices might as well have been translated from Greek into Korean.

Any guidance would be much appreciated.