[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
jeffj
Registered User
Posts: 11
Joined: Fri Nov 12, 2010 3:21 am

Re: [3.0.x] Tweaks for large forums

Post by jeffj »

1234homie wrote: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 ;]
You don't need to delete the code, you can work around it with a conditional (if($this->data['user_id'] !== ANONYMOUS).

For the IP ban, this block builds a UNION query with one or more IP addresses. You have to do some deeper modifications, but it should be a hint in the right direction (for various reasons, I won't just copy/paste the final code - chances are, if you get the traffic to need this type of change, you've got someone around sufficiently capable of making this change for you).

Code: Select all

                if($user_id !== false && $user_ips !== false) {
                        $sql .= ' UNION SELECT ban_ip, ban_userid, ban_email, ban_exclude, ban_give_reason, ban_end
                                FROM ' . BANLIST_TABLE . '
                                WHERE ';

                        if($user_email === false) {
                                $where2 = " ban_email = '' AND ";
                        }
                        else {
                                $where2 = " ban_email = '" . $user_email . "' ";
                        }

                        if(is_array($user_ips)) {
                                $ban_ip_block = ' ( ';
                                                for($jj = 0; $jj < count($user_ips); $jj++) {
                                                if($jj > 0) $ban_ip_block .= ' OR ';
                                                $ban_ip_block .= "ban_ip = '" . $user_ips[$jj] . "' ";
                                                }
                                                $ban_ip_block .= ' ) ';
                        }
                        else {
                                $ban_ip_block = " ( ban_ip = '$user_ips' ) ";
                        }

                        $sql .= $where2 . $ban_ip_block;
                }
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 26847
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: [3.0.x] Tweaks for large forums

Post by Paul »

You shouldnt delete that code at all, as it runs for non guests (So regged users) as well ;).
Knock knock
Race condition
Who's there?

My BlogMy Photosmy phpBB Extensionscustom phpBB work & Development
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 »

Why I shouldnt delete these lines? user_form_salt are updated after users reg on board.
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 26847
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: [3.0.x] Tweaks for large forums

Post by Paul »

Because it is used for a security feature for forms. The salt should be updated, and stay the same.
Knock knock
Race condition
Who's there?

My BlogMy Photosmy phpBB Extensionscustom phpBB work & Development
jeffj
Registered User
Posts: 11
Joined: Fri Nov 12, 2010 3:21 am

Re: [3.0.x] Tweaks for large forums

Post by jeffj »

2 more changes (maybe bugfixes) for large forums:

Add index to phpbb_sessions on session_ip (used during banning by IP, full table scan otherwise)
Add index to phpbb_topics_track on topic_id (the fact that it's part of a composite primary key is not sufficient, full table scans otherwise while deleting or moving threads).
jeffj
Registered User
Posts: 11
Joined: Fri Nov 12, 2010 3:21 am

Re: [3.0.x] Tweaks for large forums

Post by jeffj »

Potentially also:
create index topic_id on phpbb_topics_posted(topic_id);
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 »

jeffj wrote:Potentially also:
create index topic_id on phpbb_topics_posted(topic_id);
yeah but.. more indexes = slower INSERT query ;p
jeffj
Registered User
Posts: 11
Joined: Fri Nov 12, 2010 3:21 am

Re: [3.0.x] Tweaks for large forums

Post by jeffj »

1234homie wrote:
jeffj wrote:Potentially also:
create index topic_id on phpbb_topics_posted(topic_id);
yeah but.. more indexes = slower INSERT query ;p
A slightly slower INSERT when a new topic is posted vs a full table scan on topic delete. I don't know how big or busy your sites are, but a table scan on delete pretty much ruins performance for dbs doing ~1200-1500 queries per second.

If you have a busy forum where topics are locked but not deleted, it's a nonissue.
If you have a busy forum where topics are deleted often, it's a major issue.
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 »

1234homie wrote:
jeffj wrote:Potentially also:
create index topic_id on phpbb_topics_posted(topic_id);
yeah but.. more indexes = slower INSERT query ;p
And update, and delete.
Moof!
Mac GUI Vault: Retro Apple II & Macintosh computing archive.
Inside Allerton bookMac GUIMac 512K Blog
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 »

If you have a busy forum where topics are locked but not deleted, it's a nonissue.
If you have a busy forum where topics are deleted often, it's a major issue.
yep but if your users write 100 posts per minute, update == 15 seconds... I dont recommend it ;p
jeffj
Registered User
Posts: 11
Joined: Fri Nov 12, 2010 3:21 am

Re: [3.0.x] Tweaks for large forums

Post by jeffj »

1234homie wrote:
If you have a busy forum where topics are locked but not deleted, it's a nonissue.
If you have a busy forum where topics are deleted often, it's a major issue.
yep but if your users write 100 posts per minute, update == 15 seconds... I dont recommend it ;p
Again, your mileage may vary, but if your users are posting more than once a second, it's pretty obvious that you'll be deleting or otherwise moderating at least some of those posts, and when it comes time to delete that thread, it'll be a fun table scan, and while that runs, the other thousand or so queries per second will be severely hampered, your site will slow to a crawl, and your moderator control panel will time out for about 10-20 minutes while the series of DELETEs runs. Our forum has something like 3M users, 2.6M posts, and the phpbb_topics_posted table is almost 2M rows. Doing a full scan on phpbb_topics_posted every time you delete a thread sounds like a problem to me. In our case, the 3 indexes I just suggested made the MCP useable for the first time in months. If your moderators aren't having a problem, don't add the index, you won't offend me.
jeffj
Registered User
Posts: 11
Joined: Fri Nov 12, 2010 3:21 am

Re: [3.0.x] Tweaks for large forums

Post by jeffj »

PS: the index on phpbb_topics_track is more important than phpbb_topics_posted, anyway - table should be bigger, but it receives far more frequent writes.

Interestingly, this is the first table that broke when we tried to dual-master the phpbb installation without modifications, the composite primary key caused conflicts when the same PK (user_id,topic_id) was inserted on both masters before replication could occur. Had this table and phpbb_topics_posted used an auto-incrementing PK and a standard composite KEY for (user_id,topic_id), this index wouldn't be required and I think dual-masters mysql replication would be much closer to working without heavily modifying the phpbb code itself.
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 »

sry bad post pls delete
Last edited by 1234homie on Sun Aug 28, 2011 5:01 pm, edited 1 time in total.
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 »

PS. what about topic_posted column in topics_posted table, I think its needless ;p
DoubleTea
Registered User
Posts: 4
Joined: Sat Nov 13, 2010 4:21 pm

Re: [3.0.x] Tweaks for large forums

Post by DoubleTea »

1234homie wrote:PS. what about topic_posted column in topics_posted table, I think its needless ;p
Really ? Can I delete this column or it will cause some problems ?
Post Reply

Return to “phpBB Discussion”