Yup - still having problems. I turned the option off (CP -> Display options -> Primary -> Forum Display options -> Show total # of unread replies in topics? ) and yet was still having the site basically unaccessible 6-7 hours later. It was obvious that the option was unchecked because the unread posts were not listed as they were before.
I'm REALLY ashamed to admit to this hack, but here's where we are now: I'm headed out for the weekend and won't be able to babysit until Sunday night. So, I've got mysqld restarting every half-hour. Things are better now, but users still reported some slowness last night.
I rebooted the server, but that didn't have any effect.
I really can't tell you that that query is definitely the cause of the problem, but I can say that it was running during the slowdowns. However, that could just be that that was the slowest of the processes and the only one I was able to "catch" by running "show processlist;".
As a side note, the number of locks (table_locks_immediate) sky rockets during our periods of slowness. Right now (period of moderate to high activity for us) I saw a peak right around 25k locks, but that number dropped to 1100 shortly after the automatic restart and has been climbing since. That number shoots up to around (well, just under) 1 million when the system's hammered. I have no idea if that's relevant, but
Right this moment, we have literally 2-3 times the number of users showing up in "who's online" than we had last night during a slowdown, so I don't think the server's getting overloaded by any means.
Thanks for the info about connections. I'm not reading things wrong then...
Frankly, I'm baffled. We did just to a major upgrade/migration back in June to ubb7. However, we hadn't seen problems until last week, so I don't know if that's relevant either.
