Page 11 of 14
Re: [3.0.x] Tweaks for large forums
Posted: Sun Nov 14, 2010 11:05 am
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;
}
Re: [3.0.x] Tweaks for large forums
Posted: Sun Nov 14, 2010 11:55 am
by Paul
You shouldnt delete that code at all, as it runs for non guests (So regged users) as well
.
Re: [3.0.x] Tweaks for large forums
Posted: Sun Nov 14, 2010 3:45 pm
by 1234homie
Why I shouldnt delete these lines? user_form_salt are updated after users reg on board.
Re: [3.0.x] Tweaks for large forums
Posted: Sun Nov 14, 2010 5:03 pm
by Paul
Because it is used for a security feature for forms. The salt should be updated, and stay the same.
Re: [3.0.x] Tweaks for large forums
Posted: Mon Dec 13, 2010 9:22 pm
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).
Re: [3.0.x] Tweaks for large forums
Posted: Thu Dec 16, 2010 12:22 am
by jeffj
Potentially also:
create index topic_id on phpbb_topics_posted(topic_id);
Re: [3.0.x] Tweaks for large forums
Posted: Thu Dec 16, 2010 5:29 pm
by 1234homie
jeffj wrote:Potentially also:
create index topic_id on phpbb_topics_posted(topic_id);
yeah but.. more indexes = slower INSERT query ;p
Re: [3.0.x] Tweaks for large forums
Posted: Thu Dec 16, 2010 6:27 pm
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.
Re: [3.0.x] Tweaks for large forums
Posted: Thu Dec 16, 2010 8:20 pm
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.
Re: [3.0.x] Tweaks for large forums
Posted: Sat Dec 18, 2010 9:56 pm
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
Re: [3.0.x] Tweaks for large forums
Posted: Mon Dec 20, 2010 7:41 pm
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.
Re: [3.0.x] Tweaks for large forums
Posted: Mon Dec 20, 2010 7:46 pm
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.
Re: [3.0.x] Tweaks for large forums
Posted: Tue Dec 21, 2010 9:25 pm
by 1234homie
sry bad post pls delete
Re: [3.0.x] Tweaks for large forums
Posted: Tue Dec 21, 2010 9:29 pm
by 1234homie
PS. what about topic_posted column in topics_posted table, I think its needless ;p
Re: [3.0.x] Tweaks for large forums
Posted: Mon Jan 03, 2011 12:07 am
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 ?