Intermittent slow performance

[Max Loeb]

This has been driving me nuts for months. My site will occasionally seem to "hang" when I try to load a page. I'll get emails from users telling me that "the site is down". But it's not down, it's just taking all day to do queries. This is something that happens intermittently--the page is just as likely to run like a dream as it is to bog down and get stuck in the mud. It's difficult for me to debug because it only seems to happen on the live server--my local setup works just fine.

My first instinct when this became unbearable was to contact my host and ask them what is going on with the server, but they always confidently assure me that the server is fine, mySql is fine, and that it's probably just an infinite loop or something in my code. Not a chance. I have the same code on my local server, and it works without issue.

I've also spent days trying every web page optimization trick in the book, from compressing and caching my javascript files to repairing and optimizing my tables. I've gone over my queries with a fine-tooth comb, but even simply queries that take at worst a fraction of a second to under normal circumstances are showing up in my slow query log with times of up to 20 seconds.

But this problem seems to be outside of all that--every now and then MySQL simply seems to stop working as expected.

Some clues:
- oddly my forums page generally seems to come right up, even when the rest of the site is bogging. I use phpbb3, and the tables live in the same db as everything else (which leads me to believe I'm just doing something dead-stupid somewhere).

- this shouldn't be a traffic issue--I don't generally have more than 2 or three users on the site at once (one or more of which is usually a bot)

- the complexity of the query doesn't seem to be a factor. Simple single-table queries seem to be as prone to bogging as more complex ones with lots of joins and grouping.

Do these symptoms ring any bells with anyone?

Max