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
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

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

Post by [violet] »

I hope somebody can help me with my ever-expanding forum.

Pruning -- both via auto-prune and the admin panel -- doesn't work. When I try to prune any forum, I wait about 10 to 20 seconds, then get a message:
  • In Mozilla 1.2: "The document contains no data"
  • In Opera 6: "Connection closed by remote server"
  • In Internet Explorer 6: "Cannot find server/The page cannot be displayed"
  • In Netscape Navigator 6.2: no message, it just stops.
The only way I can get pruning to not crap out like this is to enter a number that is higher than the oldest post -- if I do that, it happily reports that 0 posts were pruned.

I've searched the forums and found my problem is similar to (but not exactly the same as) the below: And I posted this, which was a different problem that turned out to have this as its cause: Forums won't load for moderators & admins!

Forum: http://www.nationstates.net/forum/index.php
Approx. 100,000 users, 300,000 posts.
Version 2.0.4
MySQL.
No MODs.
Last edited by [violet] on Wed Jun 11, 2003 11:09 am, edited 1 time in total.
tealnet
Registered User
Posts: 13
Joined: Tue Apr 01, 2003 3:38 am
Location: California

Post by tealnet »

I've got the same problem. I see quite a few posts about this issue, but never a resolution. Our configuration looks very similar to yours. However, I've only got about 50,000 articles and it still doesn't work.

I'm using Win2k/IIS5, MySQL 4.0.12, PHP 4.3.2-RC2.

Erick
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

Post by [violet] »

I've been extensively investigating this issue, and have a lot more information... although I'm still stumped as to the cause.

I managed to extract the pruning code so I could run it from the command line, and found that the initial SELECT query (which gets a list of the topics that need pruning) freezes the entire database.

This is MySQL 3.23.41-log on a Red Hat 7.3 Linux server.

Watching 'top', I see a single mysqld process -- the pruning query -- consuming quite a lot of CPU, but nothing to kill my extra-beefy 4-way dedicated server: there's lots of idle time. But pretty quickly, about 200 other mysqld processes appear -- which are just regular queries from people browsing the forum. They chew up all available memory, and the system starts whacking away at the disk cache.

All the other mysqld processes are designated as 'sleeping' in top and 'Locked' in mysqladmin. They just accumulate, queuing up behind the pruning query, and don't go away until the pruning SELECT query is done.

My max_connections is 300, and I know I can reduce this to stop so much memory being chewed up. But I shouldn't have to: a SELECT query shouldn't freeze the database like this!

Can anyone help debug further? I know very little about MySQL.
Antony
Registered User
Posts: 529
Joined: Wed Feb 12, 2003 9:06 am

Post by Antony »

Yes, [violet] I have now read your topic after your email linking me too it.

And the only thing I can recomend is that you limit the number of new sessions that a user can have, many browsers will create multiple sessions by accident and put much more strain on your server.

Neils, created a modification that is pretty easy to use that will allow you to control this, and you can get it from his development site.
http://mods.db9.dk

Other than that, I'm not sure what else you can do.
tealnet
Registered User
Posts: 13
Joined: Tue Apr 01, 2003 3:38 am
Location: California

Post by tealnet »

I just realized that the pruning was also causing our database to be killed whenever it was run, even successfully. I just pruned about 1000 posts from a forum with about 8000 posts and it was successful, but it killed the database connection.
Antony
Registered User
Posts: 529
Joined: Wed Feb 12, 2003 9:06 am

Post by Antony »

What's your maximum execute time?
tealnet
Registered User
Posts: 13
Joined: Tue Apr 01, 2003 3:38 am
Location: California

Post by tealnet »

It was 60 seconds, but I upped it to 120. The pruning fails before the 120 seconds tho.... just goes to a black background.

Erick
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

Post by [violet] »

I have more information on this problem... hopefully enough now to allow someone with more knowledge than me to spot a solution! :)

This is phpBB's initial pruning query (I have subbed in values for forum_id and post_time):

Code: Select all

SELECT t.topic_id
  FROM phpbb_posts p, phpbb_topics t
  WHERE t.forum_id = 1
    AND t.topic_vote = 0 
    AND t.topic_type <> 2
    AND ( p.post_id = t.topic_last_post_id
           OR t.topic_last_post_id = 0 ) 
    AND p.post_time < 1046000000
When I run this query, either from within phpBB or from the MySQL command line, my database chokes. I get an exploding number of MySQL processes queuing up behind the pruning query, all in the 'Locked' state, as I described above. The pruning query is 'Sending data'. None of the hundreds of locked queries do anything until the pruning query is finished. I haven't ever seen this pruning query complete: I keep having to abort it after it's been grinding away for several minutes.

After experimenting, I found that if I break the query into two at the OR statement -- so I have one query to find topics without replies, and one to find topics with replies -- they both execute without a hitch, in less than a second!

Code: Select all

SELECT topic_id
  FROM phpbb_topics
  WHERE forum_id = 1
    AND topic_type <> 2
    AND topic_vote <> 0
    AND topic_last_post_id = 0
    AND topic_time < 1046000000;

SELECT t.topic_id
  FROM phpbb_posts p, phpbb_topics t
  WHERE t.forum_id = 1
    AND t.topic_type <> 2
    AND t.topic_vote <> 0
    AND p.post_id = t.topic_last_post_id
    AND p.post_time < 1046000000;
So is there a bug (or major inefficiency) in the phpBB pruning query? I can't think of any other reason why my database can process the two queries separately in the blink of an eye, but choke to death on the combined query.
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

Post by [violet] »

I'm renaming this topic to be more accurate given what I've discovered is happening.

I would really appreciate some help here. I keep hearing from people who have the same problem, but nobody knows what to do.

To summarize: the pruning query locks up the entire MySQL database, being listed as "Sending data" while every other query queues up behind it in the "Locked" state.
wonderman
Registered User
Posts: 42
Joined: Tue Mar 05, 2002 2:53 pm

Post by wonderman »

I'm another one with the same problem. I'm affraid those 2 queries need at least one more since stuff also needs to be deleted from SEARCH tables too, otherwise users will get results with blank links...or am I wrong?
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

Post by [violet] »

This is just the first query in a series... if I rewrite the entire pruning function then I need much more than just the above, yes, but I'm hoping someone from phpBB can point out a simpler solution. :)
wonderman
Registered User
Posts: 42
Joined: Tue Mar 05, 2002 2:53 pm

Post by wonderman »

My guess is we'll have to do this by ourselves...I hope I don't destroy anything, the SL dump is 1G big and it was importing it for 7 hours last time....
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

Post by [violet] »

Nobody from phpbb have anything to say on this? Even a hint as to how to debug further would be helpful... :(
wonderman
Registered User
Posts: 42
Joined: Tue Mar 05, 2002 2:53 pm

Post by wonderman »

Violet: i'm not too surprised. Last time I needed help they only responded when I started b*tching about dumb admin design and lack of comments in code, only then they responded and even that was only some reply about what I wrote instead of the problem. As much as I was a fan of phpbb, I like it less and less with all the lack of updates...they're adding new and new features to some 2.2 version which isn't even out yet, but totaly ignoring all the problems in current version.
[violet]
Registered User
Posts: 26
Joined: Sun Jan 05, 2003 12:16 pm
Contact:

Post by [violet] »

A friend investigated this problem for me and has determined that it is a problem with how phpBB relies on MySQL's default locking behaviour.

This page from the MySQL manual -- http://www.mysql.com/doc/en/Table_locking.html -- describes exactly the problem I've been experiencing, whereby an attempt to prune the database locks it up:
MySQL manual wrote: Table locking is, however, not very good under the following senario:
  • A client issues a SELECT that takes a long time to run.
  • Another client then issues an UPDATE on a used table. This client will wait until the SELECT is finished.
  • Another client issues another SELECT statement on the same table. As UPDATE has higher priority than SELECT, this SELECT will wait for the UPDATE to finish. It will also wait for the first SELECT to finish!

This appears to be why pruning locks up the entire database on a high-traffic board.

Some possible solutions, according to the manual, are:
MySQL manual wrote:
  • Try to get the SELECT statements to run faster. You may have to create some summary tables to do this.
  • Start mysqld with --low-priority-updates. This will give all statements that update (modify) a table lower priority than a SELECT statement. In this case the last SELECT statement in the previous scenario would execute before the INSERT statement.
  • You can give a specific INSERT, UPDATE, or DELETE statement lower priority with the LOW_PRIORITY attribute.
  • Start mysqld with a low value for max_write_lock_count to give READ locks after a certain number of WRITE locks.
  • You can specify that all updates from a specific thread should be done with low priority by using the SQL command: SET LOW_PRIORITY_UPDATES=1. See section 5.5.6 SET Syntax.
  • You can specify that a specific SELECT is very important with the HIGH_PRIORITY attribute. See section 6.4.1 SELECT Syntax.

I would like to hear the phpBB team's opinion on this.
Locked

Return to “2.0.x Support Forum”