Author |
Message |
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: Wed May 12, 2010 4: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
|
Posted: Thu May 13, 2010 2: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
|
Posted: Thu May 13, 2010 3: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: 451 Location: U.S.A
|
Posted: Thu May 13, 2010 9:25 am 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: 451 Location: U.S.A
|
Posted: Thu May 13, 2010 9:28 am 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
|
Posted: Fri May 14, 2010 1: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: 451 Location: U.S.A
|
Posted: Fri May 14, 2010 1: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: 628 Location: Spain
|
Posted: Fri May 14, 2010 3: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:
How is this done exactly?
_________________ phpBB2 will never die, I hope! |
|
Back to top |
|
|
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: Fri May 14, 2010 10:12 am 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:
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: 628 Location: Spain
|
Posted: Fri May 14, 2010 12: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: 451 Location: U.S.A
|
Posted: Fri May 14, 2010 12: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: 628 Location: Spain
|
Posted: Fri May 14, 2010 1: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: 451 Location: U.S.A
|
Posted: Fri May 14, 2010 1: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: 628 Location: Spain
|
Posted: Fri May 14, 2010 1: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.
Description: |
|
Filesize: |
23.34 KB |
Viewed: |
1307 Time(s) |
|
_________________ phpBB2 will never die, I hope! |
|
Back to top |
|
|
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: Fri May 14, 2010 1: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 |
|
|
|