The problem is/was due to the Last 10 Updated Diaries query which was run at least every 2 minutes (or more if it happened to take too long to run and someone hit reload). For some reason it suddenly blew up in the last few days to the point where MySQL could no longer cope with it at all. The query basically never returned (indeed, it took the whole box down with it). The machine slowly crawled to a death.
I have done a LOT of digging through the MySQL documentation and found that the ORDER BY could be significantly (and I mean significantly) improved. The query now runs in (according to MySQL) 0.00secs instead of running practically forever…
We’ll see whether it really is the culprit – I think its actually just the worst of a bunch of badly optimized queries, but it was certainly the worst.
I’ll keep you posted.