Bug in SQL pruning code? (Was: Pruning fails!)

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Thecko
Registered User
Posts: 5
Joined: Mon Jun 02, 2003 5:39 pm

Post by Thecko »

The pruning code is missing a quite vital indexed field comparison to speed up the initial select of what posts to prune ... add the following to the code and those users on high load boards specifically may see a slight speed increase :wink:

AND p.topic_id=t.topic_id
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

Post by [violet] »

That's amazing!

I added the line you suggest above and suddenly the pruning query runs in 1 second. Using phpBB's version, it takes several minutes -- at least, that's how long it runs before it chokes up my database, spawns hundreds of mysql processes and tries to crash my machine.

Thank you so much for that suggestion!

So here's a grand summary:

phpBB 2.0.4's pruning code generates an inefficient SQL query which can take several minutes or more to run on a high-traffic system. MySQL's locking behaviour means that slow SELECT queries on frequently-updated tables choke up the entire database, so users browsing the forum get time out errors in their browser, which causes them to try again, which causes even more queries to queue up. If the database is large, the spawning mysql processes consume all available memory and send the server into disk-swapping, slowing things down even more.

The solution:

Code: Select all

# 
#-----[ OPEN ]------------------------------------------ 
# 
includes/prune.php        
        
# 
#-----[ FIND ]------------------------------------------ 
# 
        $sql = "SELECT t.topic_id
                FROM " . POSTS_TABLE . " p, " . TOPICS_TABLE . " t
                WHERE t.forum_id = $forum_id
                        $prune_all
                        AND ( p.post_id = t.topic_last_post_id
                                OR t.topic_last_post_id = 0 )";

# 
#-----[ REPLACE WITH ]----------------------------------
#         
        $sql = "SELECT t.topic_id
                FROM " . POSTS_TABLE . " p, " . TOPICS_TABLE . " t
                WHERE t.forum_id = $forum_id
                        $prune_all
                        AND p.topic_id = t.topic_id
                        AND ( p.post_id = t.topic_last_post_id
                                OR t.topic_last_post_id = 0 )";
For the record, my stats:
4-way Pentium 2GHz, 1GB RAM, mostly devoted to forum
phpBB 2.0.4
Red Hat Linux 7.3
MySQL 3.23.41-log
About 85,000 users
About 700,000 posts
Database size: 440MB
Thecko
Registered User
Posts: 5
Joined: Mon Jun 02, 2003 5:39 pm

Post by Thecko »

Yep ... that's pretty much similar to problems i've been having with my site...

To combat mysql's locking behaviour we have modified the phpBB tables to run out of an InnoDB tablespace, which locks are row-level rather than table-level - while this has improved the situation for us we are still randomly getting lockups where lots of mysql processes are spawned.... I've not tracked down what else is causing it yet but it's going to take time now, I think we've nearly sorted the main culprits.

Our solution now is to run a simple cron script every minute, if the number of mysql process is >90 (far too many even for a busy period) then it will setup an ipchains rule (port 80 is previously forwarded to it's own chain) that will forward port 80 requests over to port 8080, on which there is a seperate apache process running/listening which only displays a temporary holding page, this gives the currently running php & mysql processes to calm down and finish doing what they're doing ... once the number of processes drops to below 30 then the httpd chain is set to a default accept and the site's running again.

Very, very messy - but we've tried lots of different ways to try and manage the periods where things go out of control, and this is the most elegant solution we've used so far.
wonderman
Registered User
Posts: 42
Joined: Tue Mar 05, 2002 2:53 pm

Post by wonderman »

Thanks Violet and Thecko!!! :)
netmikey
Registered User
Posts: 152
Joined: Sun Jun 09, 2002 3:14 am
Location: South Florida
Contact:

Post by netmikey »

[violet] is god
User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Post by beggers »

I also made this change and it worked fine. I manually pruned a 65,000 message board and it only took about a second.

I'm still not clear on one thing, though. If I turn on auto-pruning, how does it know what settings to use? I'm afraid to switch it on because I have no idea what will happen! :roll:

Bryan
User avatar
dylants
Registered User
Posts: 6
Joined: Wed Sep 24, 2003 10:04 am
Location: Bath, England

Post by dylants »

I know it is quite a while since this post was last active, but we experienced a similar problem so we used the distinct keyword to reduce the number of posts that were being returned in the select statement:

$sql = "SELECT DISTINCT t.topic_id
FROM " . POSTS_TABLE . " p, " . TOPICS_TABLE . " t
WHERE t.forum_id = $forum_id
$prune_all
AND ( p.post_id = t.topic_last_post_id
OR t.topic_last_post_id = 0 )";

This mod had the effect of suddenly making an operation that normally timed out to one that succeeded in just a few seconds.
User avatar
dylants
Registered User
Posts: 6
Joined: Wed Sep 24, 2003 10:04 am
Location: Bath, England

Post by dylants »

beggars - as for auto pruning, the settings are in the administration panel under Forum Admin, Management. Select edit on each of the forums that have a lot of traffic. At the foot of the page there is a section for auto pruning, we set 60 days in here (7 days for very very busy, off topic, forums), with checking every 1 day. You will also have to enable pruning on the main configuration page.

Then a point to note - auto pruning is not what you think - it requires the action of an admin or moderator browsing a forum for the pruning to take place. If checking is set to every 1 day then the forum won't be pruned for another day.

If the browsing by an admin or mod results in a timeout then you will need to add the mod that is mentioned earlier in this thread. If that still results in a timeout then do some manual pruning (also in the administration panel)

If all goes pear shaped you can switch auto-pruning off globally in the configuration panel (General Admin - configuration).

Hope that helps
User avatar
dylants
Registered User
Posts: 6
Joined: Wed Sep 24, 2003 10:04 am
Location: Bath, England

Post by dylants »

beggars - no point in auto-pruning those forums that are read only (such as AUP's), you'll only lose the posts and have to replace them from elsewhere.
juanchi
Registered User
Posts: 89
Joined: Tue Apr 09, 2002 11:09 pm

Post by juanchi »

I hope the phpbb team fix this for realese 2.2, cause 2.0.6 still have the problem.
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Post by A_Jelly_Doughnut »

In 2.2, pruning will need to be re-written anyway because of the changes to how topics and stuff work...although it may not be a complete rewrite...
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
kwnah
Registered User
Posts: 88
Joined: Fri Oct 24, 2003 2:16 pm
Contact:

Post by kwnah »

so is the problem being solve in 2.0.6 ??
bucky716
Registered User
Posts: 212
Joined: Tue Sep 09, 2003 3:19 am
Location: Buffalo, NY USA
Contact:

Post by bucky716 »

old thread, yet.. you are a god!!! Thanks for this update!!!!
kwnah
Registered User
Posts: 88
Joined: Fri Oct 24, 2003 2:16 pm
Contact:

Post by kwnah »

bucky716 wrote: old thread, yet.. you are a god!!! Thanks for this update!!!!


you sounds like me is a old thread digger.
bucky716
Registered User
Posts: 212
Joined: Tue Sep 09, 2003 3:19 am
Location: Buffalo, NY USA
Contact:

Post by bucky716 »

nah, i'd actually been trying to find an answer to this for a while.
Locked

Return to “2.0.x Support Forum”