I realize that this is somewhat of a mysql question, but here it goes:
Our whole board is basically frozen at random times because (it seems like it anyway) of this simple ubb query:
select COUNT(t1.POST_ID) as posts, sum(t1.POST_IS_TOPIC) as topics from ubbt_POSTS as t1
Here's the history: Everything's running fine. Suddenly, the board seems frozen and everyone is getting http timeouts. So, I log in and find that mysqld (5.0.22) is at roughly 100% CPU usage. I look at the processlist and the above query is the only thing running in the processlist except my "show processlist" query. The PID does change, but only after 15-20 seconds or so. If I run the query on its own, it takes 1.41 to 1.46 seconds every time under normal circumstances.
Restarting mysqld clears the problem, and everything is running as expected... for a while. Then the problem returns. It does seem to clear itself after 15-20 minutes, but I think that's just because everyone gives up trying to access anything.
I thought it was twiceler, but it wasn't as it's blocked now.
I tried running optimize table on ubbt_POSTS, but that didn't fix it.
There are no cron jobs, and this is 5.0.22, so there are no scheduled tasks from within the db itself.
What am I missing?
