[3.0.x] Tweaks for large forums

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Scam Warning
DeadEye686
Registered User
Posts: 52
Joined: Sun Dec 22, 2002 1:29 am

Re: [3.0.x] Tweaks for large forums

Post by DeadEye686 »

Well, I guess I should have tried this first before bothering y'all — I created an index on topic_id, post_approved, and post_time, and boom: both queries are using a proper index rather than index_merge and not having to filesort. So if anyone with a large board is having the same problem, give that a shot.
eteanga
Registered User
Posts: 13
Joined: Sun Mar 07, 2010 10:32 am

Re: [3.0.x] Tweaks for large forums

Post by eteanga »

I'm getting a slow query on the generation of the list of topics (phpBB 3.0.7-PL1). The query is using filesort. It's resulting in several-second page loads of the view forum page. This is similar to fishpant's report earlier in this thread.

The slow query is:

Code: Select all

	SELECT t.topic_id
	FROM phpbb3_topics t
	WHERE t.forum_id = 1
		AND t.topic_type IN (0, 1)
		AND t.topic_approved = 1		
	ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
	LIMIT 28;
It's using filesort (formatted for forum display):

Code: Select all

possible_keys
forum_id,forum_id_type,topic_approved,forum_appr_last,fid_time_moved

key
fid_time_moved

len
3

Extra
Using where; Using filesort

Rows
97958
Is this query standard?

Any pointers on how to optimise the query?
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: [3.0.x] Tweaks for large forums

Post by A_Jelly_Doughnut »

The problem is that MySQL is choosing the wrong key. The fid_time_moved key is of very low cardinality.

As far as fixing it, I'm not sure what causes MySQL to do this. One fix that would work for you is to alter the query and force a certain key usage: http://dev.mysql.com/doc/refman/4.1/en/index-hints.html You'd probably want "forum_id_type" for this query.

The query in question is around line 425 of viewforum.php.

The better fix, of course, would be to discover why the query optimizer is choosing the wrong key. :)
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
rxu
Extensions Development Team
Posts: 3401
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: [3.0.x] Tweaks for large forums

Post by rxu »

A_Jelly_Doughnut wrote:The better fix, of course, would be to discover why the query optimizer is choosing the wrong key.
This would involve analysis of the table keys structure in conjunction with query structure using article How MySQL Uses Indexes :)

A kind of try:
possible keys are only those which has leftmost (or single) prefixes involved into WHERE clause and AND levels, those columns are forum_id and topic_approved.
Then MySQL optimizer attempts to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.
OR:
because the query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway to much more quickly find the few rows.

Obviously, fid_time_moved suits for both cases since it's prefixes (forum_id, topic_last_post_time) are used in WHERE clause and in ORDER BY clause.

Just a guess.
User avatar
Dog Cow
Registered User
Posts: 2500
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Re: [3.0.x] Tweaks for large forums

Post by Dog Cow »

Indices should be built such that columns with the greatest number of duplicated values are left-most.

For example, if one had an index on forum, topic, and post IDs, one would want the index to appear in that order.
eteanga wrote:
The slow query is:

Code: Select all

	SELECT t.topic_id
	FROM phpbb3_topics t
	WHERE t.forum_id = 1
		AND t.topic_type IN (0, 1)
		AND t.topic_approved = 1		
	ORDER BY t.topic_type DESC, t.topic_last_post_time DESC
	LIMIT 28;
Any pointers on how to optimise the query?
Remove the clause "AND t.topic_approved = 1" and instead use PHP to filter that out.

If such an index on (forum_id, topic_type, topic_last_post_time) does not exist, then make it and see what happens.

If one adds topic_id to the index (one would put it at the end, since it's unique) then MySQL can satisfy this query entirely from using the index, which is a valuable bonus if the index is in RAM.
Moof!
Mac GUI Vault: Retro Apple II & Macintosh computing archive.
Inside Allerton bookMac GUIMac 512K Blog
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: [3.0.x] Tweaks for large forums

Post by evepowr »

Anyone work on removing the Inactive users part from the main admin page. It takes forever to load it.

SELECT *
FROM phpbb_users
WHERE user_type = 1
ORDER BY user_inactive_time DESC
LIMIT 10

SELECT * FROM phpbb_users WHERE user_type = 1 ORDER BY user_inactive_time DESC LIMIT 10
Id Select Type Table Type Possible Keys Key Key Len Ref Rows Extra
1 SIMPLE phpbb_users ref user_type user_type 1 const 46874 Using where; Using filesort

Before: 5.97781s | After: 62.00491s | Elapsed: 56.02709s
arod-1
Registered User
Posts: 1327
Joined: Mon Sep 20, 2004 1:33 pm

Re: [3.0.x] Tweaks for large forums

Post by arod-1 »

evepowr wrote:Anyone work on removing the Inactive users part from the main admin page. It takes forever to load it.

SELECT *
FROM phpbb_users
WHERE user_type = 1
ORDER BY user_inactive_time DESC
LIMIT 10

SELECT * FROM phpbb_users WHERE user_type = 1 ORDER BY user_inactive_time DESC LIMIT 10
Id Select Type Table Type Possible Keys Key Key Len Ref Rows Extra
1 SIMPLE phpbb_users ref user_type user_type 1 const 46874 Using where; Using filesort

Before: 5.97781s | After: 62.00491s | Elapsed: 56.02709s
how about a small experiment - try to index user_inactive_time ?
something like this (this is from memory - you might want to verify that it actually work)
you can add the index, measure the timing, and if it doesnt help, remove the index:
to add:

Code: Select all

alter table phpbb_users add index user_inactive_time (user_inactive_time);
to remove:

Code: Select all

alter table phpbb_users drop index user_inactive_time; 
standard disclaimer:
backup your db and files before you do anything.
absolutely no guarantee.
if you do what i advise and it blows in your face, all you'll hear from me is: "ah... sorry, i guess"
User avatar
1234homie
Registered User
Posts: 439
Joined: Fri Sep 26, 2008 3:17 pm

Re: [3.0.x] Tweaks for large forums

Post by 1234homie »

arod-1 wrote:
evepowr wrote:Anyone work on removing the Inactive users part from the main admin page. It takes forever to load it.

SELECT *
FROM phpbb_users
WHERE user_type = 1
ORDER BY user_inactive_time DESC
LIMIT 10

SELECT * FROM phpbb_users WHERE user_type = 1 ORDER BY user_inactive_time DESC LIMIT 10
Id Select Type Table Type Possible Keys Key Key Len Ref Rows Extra
1 SIMPLE phpbb_users ref user_type user_type 1 const 46874 Using where; Using filesort

Before: 5.97781s | After: 62.00491s | Elapsed: 56.02709s
how about a small experiment - try to index user_inactive_time ?
something like this (this is from memory - you might want to verify that it actually work)
you can add the index, measure the timing, and if it doesnt help, remove the index:
to add:

Code: Select all

alter table phpbb_users add index user_inactive_time (user_inactive_time);
to remove:

Code: Select all

alter table phpbb_users drop index user_inactive_time; 
jep its works :D
evepowr
Registered User
Posts: 85
Joined: Sun Jan 04, 2004 6:54 pm
Contact:

Re: [3.0.x] Tweaks for large forums

Post by evepowr »

Yea it works just fine. Thanks.
jeffj
Registered User
Posts: 11
Joined: Fri Nov 12, 2010 3:21 am

Re: [3.0.x] Tweaks for large forums

Post by jeffj »

Few tweaks -

In session.php, the code that checks to see if it's time to recreate the user_form_salt is executed at session setup time. On forums with a large number of anonymous users, the query that checks to see if the current user is already logged in is rather expensive. If you don't allow anonymous posting, skipping that block of code can be a big time saver:

Also, if your forum doesn't use REGEX for IP bans, switching the SQL statement from using <> '' to an explicit list of IP addresses can make better use of indexes and consequently scales better under high visitor load. This is easiest to accomplish by breaking the complex WHERE into a simple WHERE and a second UNION'd query.
DoubleTea
Registered User
Posts: 4
Joined: Sat Nov 13, 2010 4:21 pm

Re: [3.0.x] Tweaks for large forums

Post by DoubleTea »

jeffj: please, can you post your own code of session.php, or what exactly to change/remove ?

Thank you very much.
User avatar
1234homie
Registered User
Posts: 439
Joined: Fri Sep 26, 2008 3:17 pm

Re: [3.0.x] Tweaks for large forums

Post by 1234homie »

You should delete these lines if your guests doesn't need to recreate form_salt (they cant posting):

Code: Select all

			$sql = 'SELECT COUNT(session_id) AS sessions
					FROM ' . SESSIONS_TABLE . '
					WHERE session_user_id = ' . (int) $this->data['user_id'] . '
					AND session_time >= ' . (int) ($this->time_now - (max($config['session_length'], $config['form_token_lifetime'])));
			$result = $db->sql_query($sql);
			$row = $db->sql_fetchrow($result);
			$db->sql_freeresult($result);

			if ((int) $row['sessions'] <= 1 || empty($this->data['user_form_salt']))
			{
				$this->data['user_form_salt'] = unique_id();
				// Update the form key
				$sql = 'UPDATE ' . USERS_TABLE . '
					SET user_form_salt = \'' . $db->sql_escape($this->data['user_form_salt']) . '\'
					WHERE user_id = ' . (int) $this->data['user_id'];
				$db->sql_query($sql);
			}
Also, if your forum doesn't use REGEX for IP bans, switching the SQL statement from using <> '' to an explicit list of IP addresses can make better use of indexes and consequently scales better under high visitor load. This is easiest to accomplish by breaking the complex WHERE into a simple WHERE and a second UNION'd query.
Example please ;]
DoubleTea
Registered User
Posts: 4
Joined: Sat Nov 13, 2010 4:21 pm

Re: [3.0.x] Tweaks for large forums

Post by DoubleTea »

Thanks 1234homie. :)
Danielx64
Registered User
Posts: 1369
Joined: Wed Nov 04, 2009 5:51 am
Location: In a server room in Australia
Name: Daniel
Contact:

Re: [3.0.x] Tweaks for large forums

Post by Danielx64 »

The other day I found that it was possable to cut time down by getting your prosilver style after you view it in a webpage and put it in a new css file and save it in your forum root folder. Then you just need to replace the style link to link to the new style.
Please note that I will not be porting any of my mods to phpBB 3.1. Sorry for the inconvenience this may cause.
Image
Danielx64
Registered User
Posts: 1369
Joined: Wed Nov 04, 2009 5:51 am
Location: In a server room in Australia
Name: Daniel
Contact:

Re: [3.0.x] Tweaks for large forums

Post by Danielx64 »

Please note that I will not be porting any of my mods to phpBB 3.1. Sorry for the inconvenience this may cause.
Image
Post Reply

Return to “phpBB Discussion”