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?

This looks like you have the option to show total # of unread topics/posts. You can find this under Display Options. Both of these can cause a pretty high load on the SQL server especially if you have quite a few users that have a lot of unread posts.

It has to go through each forum, checking their last visit, and tallying the number of posts that have been posted since then. What happens is some users just visit a couple forums, while never visiting some of the other ones, so the number that needs to be counted continues to grow.

You can encourage your users to frequently "Mark all as read" to help, but you may just need to turn the feature off.

Well, I guess we'll know within an hour or two if that's the right direction to be heading. I just turned it off - for now.

Thanks for the pointer.

Nope. Same problem even with that option turned off.

As much as I trust Linux (IMHO, better than anything to come out of Redmond, yet not quite Solaris yet), 143 days uptime has me wondering if a reboot might be in order...

When I get to a reboot, I'm just out of ideas...

Could there be other tables I need to "optimize?" I'm still a little light on the lingo with mysql, but could there be a corrupt index or could there be any other problem that's index related? I'm just not used to only seeing one connection to a db bring down an entire app like this.

If I'm reading this correctly, ubb makes only one connection to the db, and then runs all of it's queries through that single connection. Right? Otherwise, somehow, some of the queries/connections are not being reported by "show processlist;"

Are you still seeing this query as the slowdown cause?

select COUNT(t1.POST_ID) as posts, sum(t1.POST_IS_TOPIC) as topics from ubbt_POSTS as t1

That is only run when the options to show total unread topics/posts is turned on.

Yes, basically, when a user requests a page a database connection is made. Every query for the generated page is done using that connection, and when the page is served, the connection is closed.

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.

Dumb question; are you by chance using persistant connections in your ubb config to connect to the mysql db? If so, I'd highly advise turning them off...

Just took a peek at your forum, even registered so I could get a good look at the size of your forum.

It's not all that large, so even having the total # of unread topics/posts turned on should be that bad at all. I was thinking maybe the search could be doing it as sometimes it can lock while doing a large search over all posts, however on the size of your forum, that shouldn't be an issue either.

Are you logging slow queries in your mysql configuration?

Nope. I just checked. We're not. Thanks for the heads up though.Thanks. I love this internet thing. I'm betting it might just catch on one day...Yeah, and that's the thing. I'm pretty new to all of this (baptism by fire I suppose), but I've used ubb in LOTS of other settings and it's never been an issue with much bigger sites. I've just never known what they've had running on the back end. I deal with pretty big iron for my day job, so a 2x2.8GHz P4 w/ 1.5GB RAM config seems awfully slow to me at first glance.

I'm going to have to get back to you on that one.

Now, with all that said, I'm not sure we'll ever know exactly what was the problem. The guy at our ISP ran a bunch of OS updates - including moving us to mysql 5.0.45 and running myisamchk (which was probably the same as optimize table).

So far so good. It very well may have been completely unrelated to ubb.