phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Discovered something helpful for posting to PHPBB

Goto page 1, 2  Next
 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Wed May 12, 2010 8:56 pm 
Post subject: Discovered something helpful for posting to PHPBB

We are now over 45 million posts and we have received some complaints from some posters with many posts that the posting process was taking a long time.

We found the query of selecting the MAX post time for a specific post id from the posts table was the problem child here.

Added an index on the poster_id, post_time in the posts table and this solved this issue.

Hope this helps anyone who might still be experiencing this problem.

_________________
http://www.jlaforums.com
Back to top
Slackervaara
Board Member



Joined: 01 Jan 2009

Posts: 70



PostPosted: Thu May 13, 2010 6:48 am 
Post subject: Re: Discovered something helpful for posting to PHPBB

Very interesting. How many posts had those who got problems at posting?
Back to top
Acaria
Board Member



Joined: 20 Feb 2009

Posts: 238



PostPosted: Thu May 13, 2010 7:29 am 
Post subject: Re: Discovered something helpful for posting to PHPBB

Do note you may run into many more problems with relations to your high post count. No web forum is tested with such a massive database, as it's simply improbably for any developer to just add millions of posts to a database to make sure it all functions.
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Thu May 13, 2010 1:25 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

Slackervaara wrote:
Very interesting. How many posts had those who got problems at posting?


The unacceptable delay in that particular query started happening for a user around the 20,000 post count. But I think it is not entirely related to the user's post count but more a combination of user post count & total board post count.

Now with the new index, the delay has been eliminated.

_________________
http://www.jlaforums.com
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Thu May 13, 2010 1:28 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

Acaria wrote:
Do note you may run into many more problems with relations to your high post count. No web forum is tested with such a massive database, as it's simply improbably for any developer to just add millions of posts to a database to make sure it all functions.


Oh, isn't this so true. PHPBB2 in it's original form was not designed to handle what we are doing. But since '04 we have made significant changes. For those of you in the same boat - we found the tweaks for large boards post by Lanzer (of Gaia) on the PHPBB website a great "starting" point for optimizing phpbb. Lanzer's suggestions are great even for small phpbb boards as long as they are applied properly.

_________________
http://www.jlaforums.com
Back to top
Slackervaara
Board Member



Joined: 01 Jan 2009

Posts: 70



PostPosted: Fri May 14, 2010 5:26 am 
Post subject: Re: Discovered something helpful for posting to PHPBB

JLA wrote:
The unacceptable delay in that particular query started happening for a user around the 20,000 post count. But I think it is not entirely related to the user's post count but more a combination of user post count & total board post count.

Now with the new index, the delay has been eliminated.


I have not yet noted this problem, but I have one member with 20000 posts and a couple of members close to that. However, total posts on the board is only 220000.
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Fri May 14, 2010 5:43 am 
Post subject: Re: Discovered something helpful for posting to PHPBB

Slackervaara wrote:
JLA wrote:
The unacceptable delay in that particular query started happening for a user around the 20,000 post count. But I think it is not entirely related to the user's post count but more a combination of user post count & total board post count.

Now with the new index, the delay has been eliminated.


I have not yet noted this problem, but I have one member with 20000 posts and a couple of members close to that. However, total posts on the board is only 220000.


Yes, since your total board post count is lower the query should be faster.

Before it was having to sort through 45 million posts to find all the posts by user # XXX and then sort through all of that user's post (20,000+ to find the latest post) since there was not a proper indexes in the posts table and then find the last time that user posted. By indexing all the posts by user and time this query was able to take advantage of that index.

It might even help on a smaller board so maybe you can give it a try and time the queries with the high post count users before and after. Would be willing to bet you might see some improvements

_________________
http://www.jlaforums.com
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 621
Location: Spain


flag
PostPosted: Fri May 14, 2010 7:21 am 
Post subject: Re: Discovered something helpful for posting to PHPBB

Quote:
By indexing all the posts by user and time...

Here comes the big n00b question: icon_mrgreen.gif

How is this done exactly?

_________________
phpBB2 will never die, I hope!
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Fri May 14, 2010 2:12 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

dogs and things wrote:
Quote:
By indexing all the posts by user and time...

Here comes the big n00b question: icon_mrgreen.gif

How is this done exactly?


It depends on what tool you use to access your MYSQL database - but basically create a new index on the "posts" table with that index having the (in this order) the columns "poster_id" and "post_time"

_________________
http://www.jlaforums.com
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 621
Location: Spain


flag
PostPosted: Fri May 14, 2010 4:03 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

I use Mysqldumper,

Looking at the posts_table I see there is an existing index that looks like:
Code:
   Index-Name   Typ   Duplicates allowed   Cardinality   Spalten
   1.   PRIMARY   BTREE   no   50967   post_id
   2.   forum_id   BTREE   yes   22   forum_id
   3.   topic_id   BTREE   yes   5096   topic_id
   4.   poster_id   BTREE   yes   1887   poster_id
   5.   post_time   BTREE   yes   50967   post_time


Should I remove this index and replace it with one like you suggest?

_________________
phpBB2 will never die, I hope!
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Fri May 14, 2010 4:33 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

dogs and things wrote:
I use Mysqldumper,

Looking at the posts_table I see there is an existing index that looks like:
Code:
   Index-Name   Typ   Duplicates allowed   Cardinality   Spalten
   1.   PRIMARY   BTREE   no   50967   post_id
   2.   forum_id   BTREE   yes   22   forum_id
   3.   topic_id   BTREE   yes   5096   topic_id
   4.   poster_id   BTREE   yes   1887   poster_id
   5.   post_time   BTREE   yes   50967   post_time


Should I remove this index and replace it with one like you suggest?


I would suggest leaving all your existing indexes and creating a new index (call it whatever you like) on the two columns I previously mentioned

_________________
http://www.jlaforums.com
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 621
Location: Spain


flag
PostPosted: Fri May 14, 2010 5:02 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

So it should look something like this?
Code:
Indexes of table `phpbb_posts`
   Index-Name   Typ   Duplicates allowed   Cardinality   Spalten
   1.   PRIMARY   BTREE   no   50971   post_id
   2.   forum_id   BTREE   yes   21   forum_id
   3.   topic_id   BTREE   yes   5097   topic_id
   4.   poster_id   BTREE   yes   1887   poster_id
   5.   post_time   BTREE   yes   50971   post_time
   6.   new_index   BTREE   yes   1887   poster_id
   7.   new_index   BTREE   yes   50971   post_time


Do you have duplicate indexes for poster_id and post_time too?

_________________
phpBB2 will never die, I hope!
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Fri May 14, 2010 5:32 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

dogs and things wrote:
So it should look something like this?
Code:
Indexes of table `phpbb_posts`
   Index-Name   Typ   Duplicates allowed   Cardinality   Spalten
   1.   PRIMARY   BTREE   no   50971   post_id
   2.   forum_id   BTREE   yes   21   forum_id
   3.   topic_id   BTREE   yes   5097   topic_id
   4.   poster_id   BTREE   yes   1887   poster_id
   5.   post_time   BTREE   yes   50971   post_time
   6.   new_index   BTREE   yes   1887   poster_id
   7.   new_index   BTREE   yes   50971   post_time


Do you have duplicate indexes for poster_id and post_time too?


No, it needs to be a single index on (2) columns - not two separate indexes.

_________________
http://www.jlaforums.com
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 621
Location: Spain


flag
PostPosted: Fri May 14, 2010 5:38 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

In phpMyAdmin it looks like this, two columns in one index.
What puzzles me is the fact that poster_id and post_time now have two indexes, on for each and a shared one.



indexes.jpg
 Description:
 Filesize:  23.34 KB
 Viewed:  562 Time(s)

indexes.jpg



_________________
phpBB2 will never die, I hope!
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 298
Location: U.S.A


flag
PostPosted: Fri May 14, 2010 5:57 pm 
Post subject: Re: Discovered something helpful for posting to PHPBB

Yes, that is true (about the separate indexes)

The query we were speaking about will use the dual column index while other queries might use the single column index.

Again, if you do some query time test on users that have high post counts you should see a difference with and without the new index

This sort of approach with multiple column indexes can help speed up phpbb in several place if they are applied correctly and your board needs them. Lanzer had a couple of good ones he talked about in his tweaks for large boards thead on the phpbb site

_________________
http://www.jlaforums.com
Back to top
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT
Goto page 1, 2  Next
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0236 seconds using 17 queries. (SQL 0.0026 Parse 0.0027 Other 0.0184)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo