[2.0.x] Tweaks for large forums

The 2.0.x discussion forum has been locked; this will remain read-only. The 3.0.x discussion forum has been renamed phpBB Discussion.
User avatar
Dog Cow
Registered User
Posts: 2507
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Re: [2.0.x] Tweaks for large forums

Post by Dog Cow »

Ah, ok, that makes sense now. I will work on that.
User avatar
qspypl
Registered User
Posts: 149
Joined: Wed Dec 20, 2006 7:15 pm

Re: [2.0.x] Tweaks for large forums

Post by qspypl »

Quite good summaring Dog Cow.

Btw im curious what method of including cache'd data from file to variable is fastest:
1.
ob_start();
require_once( 'cache/users.php');
$users = ob_get_contents();
ob_end_clean();

$total_users = $users;
2.
$file = implode('', file('cache/users.php'));
$total_users = $file;
3.
$file = @fopen ( "cache/users.php" , "r" );
$total_users = fgets ( $file , 255 );
fclose ($file);
:mrgreen:
User avatar
Dog Cow
Registered User
Posts: 2507
Joined: Fri Jan 28, 2005 12:14 am
Contact:

20 Examples of poorly Coded MODs

Post by Dog Cow »

This is only slightly relevant to this topic, but I have put together this document: 20 Examples of Bad phpBB MODs

Thanks to MartectX for suggesting it.
qspypl wrote: Btw im curious what method of including cache'd data from file to variable is fastest:
I have read that error-suppression using @ will slow down execution a tiny bit. Otherwise, I would test it by putting timers around each block of code and running it about 50 times then averaging the results.
User avatar
DavidMJ
Former Team Member
Posts: 314
Joined: Thu Aug 04, 2005 12:50 am
Location: New York

Re: [2.0.x] Tweaks for large forums

Post by DavidMJ »

qspypl wrote:Quite good summaring Dog Cow.

Btw im curious what method of including cache'd data from file to variable is fastest:
1.
ob_start();
require_once( 'cache/users.php');
$users = ob_get_contents();
ob_end_clean();

$total_users = $users;
2.
$file = implode('', file('cache/users.php'));
$total_users = $file;
3.
$file = @fopen ( "cache/users.php" , "r" );
$total_users = fgets ( $file , 255 );
fclose ($file);
:mrgreen:
file_get_contents is the fastest way to read a file into a variable. the "@" symbol does indeed hurt perf as it basically sets the error level twice (once before and once after) which is not so fast.
arod-1
Registered User
Posts: 1327
Joined: Mon Sep 20, 2004 1:33 pm

Re: 20 Examples of poorly Coded MODs

Post by arod-1 »

Dog Cow wrote:This is only slightly relevant to this topic, but I have put together this document: 20 Examples of Bad phpBB MODs

Thanks to MartectX for suggesting it.
excellent idea, and in general good document.
however, i have a couple of small comments:
you have a paragraph that reads:
Improper SQL Usage
$sql = "select * from phpbb_shops where shopname='".addslashes($row['shop'])."' and shoptype!='special' and shoptype!='admin_only'";

First of all, a constant should be defined for the table name, second of all, mysql_real_escape_string() should be used in favor of addslashes(), and third, the correct symbol for "not equals" is <>. This third point is even mentioned in the phpBB Coding guidelines-- read it!
absolutely agree regarding the table name. however, using "mysql_real_escape_string()" assumes mysql as the backend, which should not be assumed, imho.
i believe you want to use $db->sql_escape()
regarding your 3rd point: you are right. interestingly enough, mysql also supports " != " as "not equal", but this is not standard syntax, and may not be supported by other backends.

comment #2:
Querying on un-indexed columns
#
# Pull user items into an ARRAY to use later
#
$sql = "SELECT *
FROM " . USER_ITEMS_TABLE . "
WHERE user_id = {$userdata['user_id']}
AND worn = 0
AND no_give = 0";

This query will run OK with a small table, but on tables with more than 1,000 rows, you should either add a multi-column index, or even better, remove the AND clauses, then skip the 'worn' and 'no_give' items when building the array.
again, i do not think you are right here.
if one of the values is indexed, the db backend will use this search criteria first (well, i know it for a fact only regarding mysql, but i'm sure all other backends behave similarly. if not, those backends are not suitable for a large board anyway), and will use the other tests only on the values returned from the 1st test.
iow, it doesn't really matter how many rows are in the table, only how many rows have the user_id searched for. if there is a reasonable limit to the number of items owned by a user, then i think this query is fine.
(of course, both your comment and mine assume that the table is indexed on user_id. if it isn't, then it should be...).
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
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [2.0.x] Tweaks for large forums

Post by 3Di »

Dog Cow wrote:This is only slightly relevant to this topic, but I have put together this document: 20 Examples of Bad phpBB MODs
As a matter of fact none (afair) of this MODs are into the MODDB, though. ;)
Which MODs were these?
In order, these MODs are:
1- Activity Plus v1.1.0 by aUsTiN
2- Activity Plus v1.1.0 by aUsTiN
3- Arcade v2.2.0 by dEfEndEr
4- Quest MOD v2.5.0 by Nuladion
5- Nulavatar v1.3.1 by Nuladion
6- BB DB Admin v1.0.0 by aUsTiN
7- Arcade v2.2.0 by dEfEndEr
8- Photo Album v2.x by Smartor, et al
9- Shop v2.6.0 by Zarath
10- Shop v2.6.0 by Zarath
11- Shop v3.1.0 by Zarath
12- Referral Plus v1.4.0 by MentalMaze
13- Bank v2.0.1 by Zarath
14- Bank v2.0.1 by Zarath
15- ADR v0.4.4 by Seteo-Bloke
16- Loewen Enterprise Donation MOD v1.0.1 by sandodo
17- Loewen Enterprise Donation MOD v1.0.1 by sandodo
18- Knowledge Base v0.7.6 beta by wGEric
19 - Lottery v2.1.0 by Zarath
20- ADR v0.4.4 by Seteo-Bloke
end OT.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: [2.0.x] Tweaks for large forums

Post by drathbun »

3Di wrote:As a matter of fact none (afair) of this MODs are into the MODDB, though. ;)
He said that at the very beginning of his document...
Dog Cow wrote:All of these are real examples from real MODs which you can download at any place other than the Official phpBB MOD archive
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [2.0.x] Tweaks for large forums

Post by 3Di »

drathbun wrote:
3Di wrote:As a matter of fact none (afair) of this MODs are into the MODDB, though. ;)
He said that at the very beginning of his document...
Dog Cow wrote:All of these are real examples from real MODs which you can download at any place other than the Official phpBB MOD archive
Yes, I downloaded it and read before to post, I assumed he wrote they could be d/l also at other places , not only phpbb.com.
Am I wrong? ..
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: [2.0.x] Tweaks for large forums

Post by drathbun »

I believe that he meant to say that none of the MODs he reviewed are located here at phpbb.com. They're all from other sources only.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [2.0.x] Tweaks for large forums

Post by 3Di »

drathbun wrote:I believe that he meant to say that none of the MODs he reviewed are located here at phpbb.com. They're all from other sources only.
Thanks. I stand corrected and apologise then. :|
Last edited by 3Di on Tue Aug 19, 2008 11:10 pm, edited 1 time in total.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: [2.0.x] Tweaks for large forums

Post by drathbun »

3Di wrote:Thanks. I apologise then. :|
No worries. :)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
User avatar
Dog Cow
Registered User
Posts: 2507
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Re: [2.0.x] Tweaks for large forums

Post by Dog Cow »

Wow... where'd all you guys come from? :shock:
3Di wrote: Yes, I downloaded it and read before to post, I assumed he wrote they could be d/l also at other places , not only phpbb.com.
Am I wrong? ..
Some are in the MODs in development forum, some are at phpbbhacks.com, and others are at the MOD author's personal site(s). Basically, I just took a look at the various MODs I have laying around my Mac and I spent about 2 hours looking through them. I really like trying out new features and new MODs, it's just that most of them I don't end up using. There are many others besides the 20 mentioned in the doc. :?
arod-1 wrote: comment #2:
Querying on un-indexed columns
#
# Pull user items into an ARRAY to use later
#
$sql = "SELECT *
FROM " . USER_ITEMS_TABLE . "
WHERE user_id = {$userdata['user_id']}
AND worn = 0
AND no_give = 0";

This query will run OK with a small table, but on tables with more than 1,000 rows, you should either add a multi-column index, or even better, remove the AND clauses, then skip the 'worn' and 'no_give' items when building the array.
again, i do not think you are right here.
if one of the values is indexed, the db backend will use this search criteria first (well, i know it for a fact only regarding mysql, but i'm sure all other backends behave similarly. if not, those backends are not suitable for a large board anyway), and will use the other tests only on the values returned from the 1st test.
iow, it doesn't really matter how many rows are in the table, only how many rows have the user_id searched for. if there is a reasonable limit to the number of items owned by a user, then i think this query is fine.
(of course, both your comment and mine assume that the table is indexed on user_id. if it isn't, then it should be...).
Well, it's still querying on un-indexed columns, so MySQL has to read data off the disk. That's a slow-down right there. Only the user_id is defined as primary key. Sure, if your site is small, that's no problem; otherwise I think this query is worthy of fix-up.

Thanks for the comments! I thought I'd have to wait until tomorrow to see maybe one or two replies. And don't forget-- this isn't the most important document, the one on the previous page is more useful.
Last edited by Dog Cow on Tue Aug 19, 2008 11:33 pm, edited 1 time in total.
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: [2.0.x] Tweaks for large forums

Post by 3Di »

Dog Cow wrote:Wow... where'd all you guys come from? :shock:
3Di wrote: Yes, I downloaded it and read before to post, I assumed he wrote they could be d/l also at other places , not only phpbb.com.
Am I wrong? ..
Some are in the MODs in development forum, some are at phpbbhacks.com, and others are at the MOD author's personal site(s). Basically, I just took a look at the various MODs I have laying around my Mac and I spent about 2 hours looking through them. I really like trying out new features and new MODs, it's just that most of them I don't end up using. There are many others besides the 20 mentioned in the doc. :?
Yes, they are not in the MODDB, definitely. ;)
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
Dog Cow
Registered User
Posts: 2507
Joined: Fri Jan 28, 2005 12:14 am
Contact:

Re: [2.0.x] Tweaks for large forums

Post by Dog Cow »

3Di wrote: Yes, they are not in the MODDB, definitely. ;)
Yep, you are quite right!

Did you read the previous document, the Big Bag of phpBB speed-up Tips? It's on page 79. I think your confusion is coming from not having read that. This 20 Examples document is actually just a add-on which I composed because someone had suggested it.
All of these are real examples from real MODs which you can download at any place other than the Official phpBB MOD archive. All of these flaws are present in the most recent major version listed of the MOD.
User avatar
qspypl
Registered User
Posts: 149
Joined: Wed Dec 20, 2006 7:15 pm

Re: [2.0.x] Tweaks for large forums

Post by qspypl »

performance of include, include_once, require, require_once:
http://blog.arh.cc/index.php?/archives/ ... n-PHP.html

found benchmark like this but not sure if its valuable, my question is this, if we should remove number of files in phpbb by remove'ing include'ing for example:
open file
index.php

find
include($phpbb_root_path . 'extension.inc');

replace it by
$phpEx = "php";
$starttime = 0;

delete file extension.inc
We can hardcore db/mysql4.php in include/db.php instead of include too if ofcourse it will give notice'able speed gain. I read somwhere, when you use'ing opcode cachce, then include'ing of files is used only when recompile.

PS
DavidMJ thx for answer, in the end i used another idea, cron save'ing cached files not in text format which require one of these methods, but saveing them like this: <?php \$total_users='text'; ?> and then they must be only included, not readed to variable, they are already in variable.

UPDATE
Here is completly diffirent thing, proof of concept below.
We are trying to obtain last post_id of specific forum. Orginal query is like this:
mysql> SELECT MAX(post_id)
FROM phpbb_posts
WHERE forum_id = 6 ;
+--------------+
| MAX(post_id) |
+--------------+
| 4150580 |
+--------------+
1 row in set (3.44 sec)
I changed it to:
mysql> SELECT post_id
FROM phpbb_posts
WHERE forum_id = 6
ORDER BY post_id DESC
LIMIT 0,1;
+---------+
| post_id |
+---------+
| 4150313 |
+---------+
1 row in set (3.81 sec)
Server is busy so beyond coma miliseconds vary, but this two queries are almost equal... so no gain still... how about remove'ing WHERE clause and then we can use advantage of index! ;)
mysql> SELECT post_id, forum_id
FROM phpbb_posts
ORDER BY post_id DESC
LIMIT 0,10;
+---------+----------+
| post_id | forum_id |
+---------+----------+
| 4150601 | 6 |
| 4150600 | 9 |
| 4150599 | 6 |
| 4150598 | 6 |
| 4150597 | 5 |
| 4150596 | 40 |
| 4150595 | 6 |
| 4150594 | 6 |
| 4150593 | 6 |
| 4150592 | 21 |
+---------+----------+
10 rows in set (0.08 sec)
Here we are, from ~3.5 sec to ~0.08 sec. Now only get biggest post_id with forum_id by php :). How we can be sure to obtain forum_id wanted by us in result set? (6 in this example) We can use several methods, one is to grab biggeer result set, then we probably hit our needed forum_id:
mysql> SELECT post_id, forum_id
FROM phpbb_posts
ORDER BY post_id DESC
LIMIT 0,1000;
+---------+----------+
| post_id | forum_id |
+---------+----------+
| 4150684 | 15 |
| 4150683 | 17 |
| 4150682 | 17 |
| 4150681 | 17 |
| 4150680 | 6 |
| 4150679 | 15 |
| 4150678 | 17 |
| 4150677 | 15 |
| 4150676 | 11 |
| 4150675 | 8 |
| 4150674 | 8 |
| 4150673 | 15 |
[MORE MORE MORE ]
| 4150537 | 6 |
| 4150536 | 6 |
| 4150535 | 15 |
+---------+----------+
1000 rows in set (0.01 sec)
Yeah instant (i got query cache off) and so much more chance of getting forum_id = 6; But we want be 100% sure to get it because this is the point of this query execution, so little php work, if no forum_id = 6 in result set, LIMIT += 1000, execute query again till forum_id = 6 or even set limit = 10000

3000 rows in set (0.03 sec)
10000 rows in set (0.03 sec) (index power)
So this is it, thanks for attention.
Locked

Return to “2.0.x Discussion”