Previous Thread
Next Thread
Print Thread
Hop To
Joined: Sep 2008
Posts: 82
journeyman
journeyman
Joined: Sep 2008
Posts: 82
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?

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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.

Joined: Sep 2008
Posts: 82
journeyman
journeyman
Joined: Sep 2008
Posts: 82
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.

Joined: Sep 2008
Posts: 82
journeyman
journeyman
Joined: Sep 2008
Posts: 82
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;"

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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.

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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.

Joined: Sep 2008
Posts: 82
journeyman
journeyman
Joined: Sep 2008
Posts: 82
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.

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
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...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
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?

Joined: Sep 2008
Posts: 82
journeyman
journeyman
Joined: Sep 2008
Posts: 82
Originally Posted by Gizmo
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...
Nope. I just checked. We're not. Thanks for the heads up though.
Originally Posted by Rick
Just took a peek at your forum, even registered so I could get a good look at the size of your forum.
Thanks. I love this internet thing. I'm betting it might just catch on one day...
Originally Posted by Rick
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.
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.

Originally Posted by Rick
Are you logging slow queries in your mysql configuration?
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.

Joined: Sep 2008
Posts: 82
journeyman
journeyman
Joined: Sep 2008
Posts: 82
Well, so much for that approach. It was pegged at 100% CPU for a coupla hours tonight before I restarted mysqld. (Only this time ubbt_POSTS was marked as needing repairs).

Now to go look up how to turn on logging of slow queries.

Joined: Oct 2006
Posts: 37
G
newbie
newbie
G Offline
Joined: Oct 2006
Posts: 37
I don't mean to interrupt, but we've had similar symptoms on my board for the past few months. Everything had been fine, but now the system hangs every few days (lots of Apache threads waiting). Eventually, it just fixes itself. I don't have the SQL details as bakerzdosen does. I have checked the settings described above and they're set correctly. We have a dedicated server. I don't know SQL commands well enough to try to diagnose when a problem is occurring, though.

I have a large number of posts (over 800,000). Is there some reorg command I could issue?

Joined: Sep 2006
Posts: 9
stranger
stranger
Joined: Sep 2006
Posts: 9
My users have had similar complaints in the last few months. I checked and it seems to be mysqld which is choking. We have a relatively small forum too.

I'm not sure if this helps or not, but it seems to be worse on the main forum page. by clicking on "Forum List", here is an example of the slowness in debug info:

Quote
Generated in 24.950 seconds in which 24.585 seconds were spent on a total of 23 queries. Zlib compression enabled.

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
The front page will normally generate about 9-10 queries if all posts are read. So when you see things like 23 queries total, it means that you probably have full unread post tracking turned on, and the user in question has about 13-14 forums with unread posts. Each one of those queries is pretty expensive so it can add up and slow things down pretty quickly.

Joined: Nov 2006
Posts: 3,095
Likes: 1
Carpal Tunnel
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095
Likes: 1
I've not looked into it but on another site with different software they were actually having a recursive lookup issue that really slammed the board the more users they had.

Hopefully UBB doesn't have that issue in any of it's code but also make sure any home or custom pages you've created don't have that issue.


Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
3 members (rootman, Gizmo, Nightcrawler), 562 guests, and 186 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)