Site Links
Home
Features
Documentation
Pricing & Order
Members Area
Support Options
UBBDev.com
UBBWiki.com
Who's Online Now
1 registered members (mmkk), 58 guests, and 414 spiders.
Key: Admin, Global Mod, Mod
Member Spotlight
M4TT
M4TT
Canada, SK
Posts: 46
Joined: March 2015
Show All Member Profiles 
Top Posters(30 Days)
Gizmo 14
FREAK 11
M4TT 8
mmkk 5
isaac 4
Ruben 3
Latest Photos
Chinese Buddhist temple.
My buddha beads.
Rendered Walls
Multi-Screen wallpaper
Stockholm Metro
Previous Thread
Next Thread
Print Thread
Query End hang on forum post #251508
12/17/12 07:45 PM
12/17/12 07:45 PM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
OK I know I've mentioned this in several other places but to keep it simple I decided to make a thread just for it. That way it's all in one spot and anyone else having this problem can find it here. I can also better track my (hopefully) progress in solving this.

Here's the issue.

I'm on UBB 7.5.7, PHP 5.4.9, MySQL 5.5.28.3, and FastCGI. These should all be the latest versions. My forum is running quite quickly in general. I get responses like this regularly now -

Generated in 0.809 seconds in which 0.745 seconds were spent on a total of 28 queries. Zlib compression disabled.

So the forum is flying.

However, when I make a new forum post, it hangs. It's not just slow. It locks and stays locked until it reaches whatever timeout value I have set.

I thought changing sql cache size to zero fixed it - it did temporarily. However now it's back to locking again.

I don't believe it's RAM. Here's a screenshot - I still have a quarter of my RAM open. It's not trying to use it. It is simply deadlocked.

[Linked Image]

So this lock happens both with SQL cache on and with it off.

My research up until now indicates it's a sign of two threads colliding and then staying in collision.

I will start testing and researching now to see what I can do to help this. It's happening on all my systems.


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Express Hosting
Express Hosting "We are the official hosting company of UBB.threads. Ask us about our free migration services to migrate your UBB.threads installation."
Re: Query End hang on forum post [Re: BellaOnline] #251509
12/17/12 07:54 PM
12/17/12 07:54 PM
Joined: Apr 2007
Posts: 4,313
SoCal, USA
SD Offline
Carpal Tunnel
SD  Offline
Carpal Tunnel
Joined: Apr 2007
Posts: 4,313
SoCal, USA
it's RAM, trust me... since you don't have enough RAM, it can't hold the indices in memory and has to grab from HDD each time to rewrite it...

so saving a POST, affects the two ugliest indices of all ( full text )

it'll use RAM, then have to give it right back to the other guys vying for 'attention'.. hence why you'll always seem to have spare RAM space..

and like i said before... before you hit the proverbial 'wall', things will be sailing along swimmingly, then slowly degrade, but still be manageable...

then at some point, it is a cascade effect...

.7 secs on queries is ugly slow too.... a healthy board will see .0xx and .00x seconds spent in queries..

2c


Re: Query End hang on forum post [Re: BellaOnline] #251510
12/17/12 08:51 PM
12/17/12 08:51 PM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
Yes I do see response times like this too -

Generated in 0.099 seconds in which 0.048 seconds were spent on a total of 14 queries. Zlib compression disabled.

So I am content with the overall speed.

I know you love the add-RAM solution smile. However, I'm reading reports on the web with people hitting this issue with 32 gig of RAM and higher. So at least some portion of people have this query end hang issue even with far more RAM than I'll be adding.

The hang is always on updating UBB_Topics. Never anything else. Looking at the code, I'm seeing that the code inserts into UBB_Topics and then hard after that it updates UBB_Topics with the last PostID. It's that update that seems to be hanging. And it makes sense to me - MySQL is doing an insert on a row and that row is still being written when it tries to update it.

So here's a question. Do we really need that second update? If we're just setting the post_id and topic_last_post_id, can't we do that in the insert, instead of inserting a row and then immediately updating it? What's the purpose of hitting that same row twice in a row?

Or am I misreading what is happening?


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Re: Query End hang on forum post [Re: BellaOnline] #251511
12/17/12 09:03 PM
12/17/12 09:03 PM
Joined: Apr 2007
Posts: 4,313
SoCal, USA
SD Offline
Carpal Tunnel
SD  Offline
Carpal Tunnel
Joined: Apr 2007
Posts: 4,313
SoCal, USA
yes, that insert is required

and just because it hangs on that query, it's not necessarily the cause, when you are at the 'cascade cliff'

you will find that different 'offending queries' start popping up too..

eventually the two BIG BAD queries will kill you -- showflat and postlist.. ( cfrm to a lesser degree, if you have a TON of forums ) those two, with the DESCending sort and lots of posts/topics and lotsa pagination, will become your bane... smile


analogy: you will find that you will be too busy mopping the floor to turn the faucet off wink

i've seen this play out on too many boards..

it essentially real good, until it gets real bad... there isn't much intervening time in the middle of the two..

ideally: linux server, apache 2.2+ php 5.3.13+, mysql 5.1+ with 4gb ram kicks a windows server butt with the same specs...

so i have given you my advice, based upon 100s of similar scenarios...

you will definitely find google-able good news and maybe you'll have a solution.. but my bet is you are about to hit a wall, if not already 2c


Re: Query End hang on forum post [Re: BellaOnline] #251512
12/17/12 09:32 PM
12/17/12 09:32 PM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
Update. My rebooted server has gone back to hanging again. There's plenty of memory available -

[Linked Image]

Using "show engine innodb status" I get further details -

~~~~~~~~~~~
---TRANSACTION 336FF1, not started flushing log
mysql tables in use 1, locked 1
MySQL thread id 9758, OS thread handle 0xf00, query id 161579 localhost 127.0.0.1 root query end
update ubbt_TOPICS
set TOPIC_VIEWS = TOPIC_VIEWS + 1
where TOPIC_ID = 31148
~~~~~~~~~~~

So it's locked up on the set topic_views line.

That's interesting, because that's not the code I thought was causing the lock.

Yup, did another test. Again it's that same spot.

~~~~~~~~~~~~~

---TRANSACTION 3396A7, not started flushing log
mysql tables in use 1, locked 1
MySQL thread id 10903, OS thread handle 0x94c, query id 177666 localhost 127.0.0.1 root query end
update ubbt_TOPICS
set TOPIC_VIEWS = TOPIC_VIEWS + 1
where TOPIC_ID = 20935


~~~~~~~~~~~~~~~~

OK any thoughts where this code is located?

I need to stay on IIS. I have far too much ASP code to rewrite.

Last edited by BellaOnline; 12/17/12 09:33 PM.

Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Re: Query End hang on forum post [Re: BellaOnline] #251514
12/17/12 09:48 PM
12/17/12 09:48 PM
Joined: Apr 2007
Posts: 4,313
SoCal, USA
SD Offline
Carpal Tunnel
SD  Offline
Carpal Tunnel
Joined: Apr 2007
Posts: 4,313
SoCal, USA
you are going to find it lock in different locations.. see my analogy above...

going to InnoDB also adds a lotta unknowns... i recommend against that too..

you will always have a TON of memory after a reboot.. but just wait 1 hour after a fully running peak time forum wink

nothing more i can do here... i've given you my recommends smile

Re: Query End hang on forum post [Re: BellaOnline] #251516
12/17/12 10:18 PM
12/17/12 10:18 PM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
I hear and understand your theory of tossing on RAM to fix the problem smile.

I need a workaround for this very instant, not for a few weeks when I can get that in place. I can't have zero-postings for several weeks. I'm sure others might appreciate knowing if there are non-RAM solutions for a variety of reasons.

My research on InnoDB indicated that it would give me a speed boost. I found it definitely did on all of my forums. So I am very content with that change.

If that then requires some additional tuning, I am fine with that. Tuning is a part of life no matter what database one chooses.

In researching the specific error I get, I find that many people - even with giant amounts of RAM - hit the Query End error. The indicated solution in this specific case was to make this change to the my.ini file -

innodb_flush_log_at_trx_commit = 0

I have made that change on my lisashea server. I will report back in a few hours. That change seems logical given the reported error.

I'm still looking to find where that snippet of code is being called. I can't find it in the scripts directory.


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Re: Query End hang on forum post [Re: BellaOnline] #251517
12/17/12 10:41 PM
12/17/12 10:41 PM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
I have found the code snippet that routinely hangs during a post operation. It is at line 1091 in /libs/ubbthreads.inc.php -

while(list($views,$view_topic) = $dbh->fetch_array($sth)) {
$query = "
update {$config['TABLE_PREFIX']}TOPICS
set TOPIC_VIEWS = TOPIC_VIEWS + $views
where TOPIC_ID = ?
";
$dbh->do_placeholder_query($query,array($view_topic),__LINE__,__FILE__);
}

This looks like it's doing a loop through an array and doing a series of updates against the topic table. My BellaOnline topic table has 102887 rows in it. I'll note that it is now an InnoDB table, so I absolutely understand this could be an InnoDB specific issue in terms of tuning. That's fine.

So if this operation is looping around and around, continually hitting the large Topic table, it makes sense that it could be locking up during that. That's even "worse" than the situation I thought it was, where it was just an insert-than-update on the Topic table.

I'm curious, what type of a page would a visitor be on where it would "count" as them having seen multiple topics, to then require it to increment the counter on multiple topics all at once? In my forum layouts I see just one topic's posts at a time. I look at the Blue Origami Sweepstakes thread, and then I go look at the Origami Flowers sweepstakes thread. So it would only be updating one post view count at a time.

I.e. just in case this flush_log update doesn't work, I'm looking at what I can temporarily code around so my posting stays functional for the coming weeks until I can examine potential hardware upgrades.


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Re: Query End hang on forum post [Re: BellaOnline] #251527
12/18/12 09:33 AM
12/18/12 09:33 AM
Joined: Jun 2006
Posts: 16,784
Portland, OR; USA
Gizmo Offline
UBB.threads Developer
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 16,784
Portland, OR; USA
Lisa, do you have the unread post indicators ("Show total # of unread posts per forum?" and "Shor total # of unread replies in topics?") enabled by chance? If so they'll definitely slow things down as well...


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.
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!
Re: Query End hang on forum post [Re: BellaOnline] #251546
12/18/12 03:11 PM
12/18/12 03:11 PM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
Gizmo -

I do have those turned on; I was testing to see if I could tune the forum to be quick enough and still have those showing. I was successful - the forums are now zipping along at a quick speed even with those showing.

I'm also pleased to report that changing the flush_log setting stopped the deadlocking. I am no longer getting hangs on posts. It's a day later and all my forums are working quite smoothly.

So the end status, at least for now, is that I have 2 gig RAM and my forums are in gorgeous shape. The draw time on all view pages is the fastest it's ever been. They're faster than my "normal" web pages are. The posting speed is quite fine. So I feel I've proven that 2 gig RAM and IIS is perfectly fine for forums with 54,000 members and 718,000 posts. It's all about the tuning and upgrading smile.


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Re: Query End hang on forum post [Re: BellaOnline] #251553
12/18/12 04:03 PM
12/18/12 04:03 PM
Joined: Jun 2006
Posts: 16,784
Portland, OR; USA
Gizmo Offline
UBB.threads Developer
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 16,784
Portland, OR; USA
Well, the reason I asked was that the query you mentioned being slow is the query that the feature uses... In the future to speed things up I'd highly advise turning that feature 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.
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!
Re: Query End hang on forum post [Re: BellaOnline] #251582
12/20/12 01:02 AM
12/20/12 01:02 AM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
Gizmo -

I'm a bit confused. The update being done is updating the topics table and setting the topic_views value for an entire topic. So it's setting, for an entire topic, how many views have been triggered on it since its creation.

Why would the overall master topic_views value be changed based on whether someone wanted to see their personal count of unread topics? I would think it should only ever be incremented if someone specifically went in to view that topic.

Lisa


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Re: Query End hang on forum post [Re: BellaOnline] #251590
12/20/12 04:01 PM
12/20/12 04:01 PM
Joined: Jun 2006
Posts: 16,784
Portland, OR; USA
Gizmo Offline
UBB.threads Developer
Gizmo  Offline
UBB.threads Developer
Joined: Jun 2006
Posts: 16,784
Portland, OR; USA
I combined two thoughts while on my cellphone; the query is a separate query yes, i meant to post about possible slowdowns on the other thread where you where asking opinions on speeding things up lol


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.
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!
Re: Query End hang on forum post [Re: BellaOnline] #251593
12/20/12 04:48 PM
12/20/12 04:48 PM
Joined: Dec 2003
Posts: 5,822
Lutz,FL
Ruben Offline

Ruben  Offline


Joined: Dec 2003
Posts: 5,822
Lutz,FL
Well you have a warning upfront on the feature:

Show total # of unread posts per forum?
Warning: This can cause excess load and contribute to slow speed.

Caused by multiple users visiting simultaneously
It is just a matter of additional multiple query's running at the same time taking resources each time a user visits.


Blue Man Group


There is no such thing as stupid questions. Just stupid answers
Re: Query End hang on forum post [Re: BellaOnline] #251605
12/21/12 12:27 AM
12/21/12 12:27 AM
Joined: Jun 2006
Posts: 754
BellaOnline Offline OP

old hand
BellaOnline  Offline OP

old hand
Joined: Jun 2006
Posts: 754
Yup I agree completely that the unread post being calculated is going to take time.

For me, right now, my forums are zipping along, so I don't have a need to make that change.

I like to do my forum tuning with all features I want enabled, so I can do my very best to get that to work. I would only start turning off features if I was completely unable to get quick forums with the features I wanted.


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 57,000 members / 800,000 posts
Re: Query End hang on forum post [Re: BellaOnline] #252065
01/14/13 03:00 PM
01/14/13 03:00 PM
Joined: Apr 2007
Posts: 4,313
SoCal, USA
SD Offline
Carpal Tunnel
SD  Offline
Carpal Tunnel
Joined: Apr 2007
Posts: 4,313
SoCal, USA
i'll have to eat some crow here.

Bella was right in many respects, that InnoDB helps with speed.

I still stand by the fact that more RAM can never hurt, however.

From what I've just tested, there are 2 tables that will show an immediate increase in speed, if you convert to InnoDB

ubbt_FORUM_LAST_VISIT and ubbt_USER_DATA

it doesn't hurt to convert the other USER tables too, but they don't have constant r/w access like the 1st two.

given what Bella did and what I've now seen in terms of load/speed impact, we'll be transitioning to InnoDB for many tables where it will help.

Going to InnoDB exclusively probably won't happen til mysql 5.6, where FULLTEXT is addressed ( think search done right, finally )

So, kudos to Bella for blazing the trail and literally doing bleeding edge stuff wink

You deserve to be commended.

I will be posting on my blog a lot of the geek stuff that is additional ( my.cnf stuff ) to make it even better.

Most of that info came from Tom Walsh @ Express Hosting ( highly recommended!! )

If you want hosting done right for your forum(s), Express Hosting is the place to go...

He / They, now their stuff when it comes to cPanel / WHM servers and Linux in general.

</end eating crow mode> laugh


Shout Box
Today's Birthdays
No Birthdays
Recent Topics
Shout Box Sound Effect
by M4TT. 11/29/17 08:28 PM
Ad island
by TGCsanderson. 11/25/17 06:41 PM
Taking to long to connect to DB
by AstroCat. 11/24/17 12:34 PM
I want to update forum but can't find license
by dimaninc. 11/23/17 10:51 AM
Thumbs Up/Rate Individual Posts
by M4TT. 11/22/17 01:11 PM
Forum Statistics
Forums36
Topics35,013
Posts190,528
Members12,045
Most Online978
Jun 24th, 2007
Random Image
Powered by UBB.threads™ PHP Forum Software 7.6.1
(Snapshot build 20171106)