A Big Bag of phpBB Speed-up Tips
Here's a bunch of tips and suggestions on how to make phpBB run faster, especially if you're on an already slow-ish server, or if you are encountering problems. Actually, most of these tips apply to any PHP/MySQL application. This guide is written assuming you are on MySQL 4 or 5 and PHP 4 or 5. If you don't have the skills to apply these techniques yourself, you should ask someone who does.
1.) Optimize your SQL Queries
Basically, there are three things that slow down a forum: 1- people posting and signing up, 2- the database server taking too long to handle requests, and 3- the PHP parser slowing down on possibly bloated code.
Now, the first factor we'll ignore, but as for SQL queries, you should really read through this Tweaks for large forums topic and check out most of the posts by Lanzer, drathbun, pppjunk, R45, Dog Cow, da_badtz_one and others. You should also
READ the MySQL manual BEFORE attempting to modify queries, tables, or write your own MODs which access the database. If you do this, you will be in the 5% of MOD authors who actually do so.
Second, you should install some sort of query timer, either using MySQL's slow query logs, or using the built-in timer which is mentioned earlier in this topic. Basically, any query which takes longer than about 3 or 4 tenths of a second is bad news, and should require attention. Also, any page which has more than 15 queries should be looked at.
Having the right indices is also imperative for queries to run their fastest. The difference is like trying to find a certain page in a book by counting each page yourself, versus having the page numbers already and quickly flipping to the correct page. It's that big. Any column which appears in a WHERE, AND, ORDER BY, or GROUP BY clause should either have an index, or it should be removed.
Sometimes you can use PHP in place of an column index. This is a hybrid of SQL and PHP performance improving. Let's say your member list skips inactive users:
Code: Select all
$sql = 'SELECT username FROM ' . USERS_TABLE . " WHERE user_active = 1';
If you don't have an index on user_active, then this query will take a long time. Plus, you shouldn't just go out and add tons of indices because that slows down the INSERT and UPDATE queries. Instead, alter the query and use this PHP code:
Code: Select all
$sql = 'SELECT username, user_active FROM ' . USERS_TABLE;
$result = $db->sql_query($sql);
while ($user_row = $db->sql_fetchrow($result))
{
if (!$user_row['user_active'])
{
continue;
}
else
{
$template->assign_block_vars('memberlist', array('USERNAME' => $user_row['username']));
}
}
What this method does is the same thing as the first query, except for you are using PHP to skip over the inactive users, versus using the MySQL query to do it. This isn't terribly clever, but most people wouldn't think to do this. Above all, it's is a valuable time-saver for un-indexed columns!
A lot of MODifications which add new tables to your database are not written by experienced users of MySQL, and so have the wrong indices created, or are lacking them completely. Compare the MOD's queries to its table schema and see what needs to be added/removed.
2.) Optimize your database and database server
If you have tweaked all the slow queries and are still getting some slow-downs, there are three last things to do: A is to change storage engines, B is to partition your tables, and C is to optimize your database server and the machine it's running on.
A - If you notice that the posting process and viewtopic.php page both seem to really slow down when a lot of users are active, run the SHOW PROCESSLIST command and look for queries which are in the Locked state. Mostly they will affect the following tables: posts, posts_text, users, topics, and forums. If your PM system gets heavy use, then include the two PM tables as well.
These tables normally use the MyISAM storage engine which uses table-level locking. This means that if one query locks the table, then all the other queries are locked out until the first one is finished. This can really slow down your forum and annoy the users. Instead, switch these tables to InnoDB, which uses row-level locking. Suddenly, those locked queries will vanish! Make sure your database server has enough RAM before you start converting to InnoDB, though.
Temporary data such as sessions should be stored in HEAP tables.
B- Partition your tables. The standard phpBB 2 database contains exactly 30 tables. If yours has more, then you must have installed MODs. If yours has less, then you probably know what you're doing...
Anyway, if your database gets to the point where you have over 80 tables, you should consider moving some of the tables to their own database. For example, if you installed the gallery or the blog mod, you could move all those tables to their own database. Having less tables in a database is better.
You can also split up the columns within the tables themselves, such as how the posts are stored in two tables. The end result is that your tables are smaller, fit better in the cache, and are served straight from RAM.
C- Optimize your database server. First of all, make sure you have the right my.cnf file, which contains all the settings for RAM allocation, table cache, buffers, and other options. You can refer to the my-huge.cnf file, or you can refer to the example that Lanzer posted in the Tweaks for large forums topic. Also, your database server computer should be equipped with enough RAM to handle all the databases. You should have at least 1 GB of RAM to get the best performance. Having enough RAM means more data can be stored in the lightning fast electric memory, versus being stored and retrieved on the (comparatively) much slower disk system. The end result is faster query response time. A 64-bit processor operating at 1GHz or better, as well as 64-bit MySQL, Apache, and whatever OS you're using will also help.
The next thing to check are your hard drives. You should have at least two, but the more the better. You should have at minimum one for the OS, and one for the databases. If you have more, you can run RAID 0 and replicate the data for a speed boost. You could also set one drive to store all logs on. The point here is to decrease seek time and movement of the drive head. Since the drive is just storing logs, that should be sequential data, and so the disk mechanism does not have to skip all over the platters, tthus saving time. Your databases should also reside on the fastest drives possible: 10,000 RPM or better. You could also consider having all your MyISAM tables on one disk, and the InnoDB tables on another. If the tables come from the same database, you can use symlinks to connect them together.
3.) Cache data
This is a big deal, especially since phpBB 2 has no built-in caching system, but phpBB 3 does. My advice: take the ACM (cache) system from phpBB 3 and bring it over to version 2. It's not too hard to do so, and the changes needed are minimal. Caching also saves RAM, because instead of MySQL using RAM to query the DB and return the results, and then PHP using RAM to process the resultset, you skip the MySQL and just use PHP.
Once you have query caching for things such as board config, total posts, total topics, total users, ranks, smileys, and other data which doesn't change much, you can lift some load off the database server, and decrease page loading time.
MySQL also has its own query cache which you should take a look at.
4.) Mod your MODs
Unless you run a straight-laced, serious discussion board, your forum probably has some MODifications installed which you most likely did not write. The problem with most all of these MODs, especially the ones which access the database, is that they are generally written to give the most features without thinking about the impact on forum speed. If you have a small forum, there is no problem. But if your forum gets bigger and more popular, these extra MODs and code will slow you down.
My general advice for MODs is this:
- Make sure you know how the MOD will work and that you can benefit from it.
- If it's a MOD you are writing, make sure to keep all the tips in this guide in mind, and follow phpBB Coding Standards. Make sure the code for the MOD, as well as any database queries, are only executed when explicitly needed. If your MOD queries the database for data which doesn't change so often, then cache it.
- If it's a MOD you didn't write, then look over all the code carefully, especially if it did not come from the Official MOD Archive. You probably shouldn't blindly follow the install instructions. The MOD authors can't know what kind of forum you have and how popular it is, so they write for everyone and what's easy for them. This often means that code or queries for the MOD are executed on every page, even if the MOD isn't used on every page. Again, on a small forum this is no big deal. On a large forum, it is.
- Keep track of language files. Almost every MODification adds some new language strings, and almost always they are added to the lang_main.php. After awhile, lang_main gets bigger and bigger and slows down your forum. Unless the MOD has less than 10 or so new language strings, put them in their own file and include() it only when necessary.
- Get rid of features you don't need or use. If you didn't write the MOD, then it may have some features you'll never use. Remove them and save time. Even phpBB 2 itself may have some features you don't use.
5.) Slim down your template(s)
If your forum has just one template, you can save 1 or 2 queries on every page load by removing the two queries in functions.php that handle the theme/template data.
Also, take a look at the size and complexity of your template files. If your overall_header.tpl is 11,000 bytes or so in size, check to make sure you've removed all the CSS from it. Check over all your biggest template files and see if you can trim them down. Remember: the bigger the file, the more time and memory your forum will require.
6.) Invest in a new template engine
If you are still using the standard phpBB 2 template.php file, then you are not using the fastest template engine available. Try the file caching one in the contrib/ directory.
If you are using eXtreme Styles (XS) Template engine, beware: it weighs in at 58,000 bytes, and is probably one of the biggest include files you have. Compare that to the 15,171 bytes of the contrib/ file caching template engine. If you don't need the huge array of features that XS supplies, and you want to cut down page loading times, try out the Speedy Templates v0.1.6 MOD. Not only is it around 1.5-2 times faster than the standard phpBB 2 engine and XS, but it will save you around 100-250 KB of RAM as well as a tenth or so of a second. Less memory used, means less load on the server and then more people can browse your forum without slow-down. I just recently switched from a stripped-down version of XS to the Speedy templates. Before, I had never seen a page on my site take less than 1.2 MB or so to parse. Now, many pages on my site typically take around 900-1000 KB. That's a big change!
If you really want to go to the extreme, you can craft your own template engine by using PHP. Now, when you are not caching the template, you are essentially running three big loops. For example, let’s look at viewing a topic. You are first looping through all the posts, then putting the variables into the template. So there’s one loop. Then when you run the pparse() method, there’s another loop that scans through _Every_ single line of the template file and looks for variables and switches, then it has too fill all those in with the variables which were assigned. That’s about 2 or 3 loops, depending on how you look at it. Plus there’s a bunch of other template variables and switches which need to go in.
All of this translates to enormous echo() statements and if/else switches and is then eval()’d to get to the browser. When you assign a var like this: $template->assign_var(’USERNAME, $username); all that is done is to store that in RAM, then put out that raw $username var right on the template! When using a caching system, all these echo() statements and what-not are stored on disk so you save exactly one step, whereas there are about two others. Without caching, well, then you have the big job every time.
Basically, it does a bunch of big loops plus stores at least one huge array in RAM, plus the entire contents of the template file are stored twice in RAM. All of those K’s add up.
My thought is to get rid of all this stuff, and put the PHP code right in the template files. Thus, the templates are included and evaluated as PHP, similar to a cached template. There still would be some manner of template from code separation, but not so much as before.
Variables like the Username would be go in like this:
Code: Select all
<span class="gen"><?php echo($username) ?></span>
and loops would be like so:
Code: Select all
<?php for ($i=0;$i < $count = count($user_row); $i++)
{ ?>
<tr><td class="row1"><span class="gen"><?php echo($user_row[$i]['username'] ?></span></td></tr>
<?php } ?>
This changes the <!-- BEGIN --> and <!-- END --> markers/switches with real PHP code.
Basically, you would run the PHP inline with the HTML. Of course all the code in viewtopic.php and elsewhere would have to be adjusted, but I think even somewhat simple changes such as I have suggested would yield a big speed-up since there's no templating layer to have to muck through.
7.) Enable GZip or other output compression
Go to your Admin panel, then Board Config. If the 'Enable GZip compression' isn't set to Yes, then do that now. Not only will you save bandwidth, but you will also save memory and time since the PHP pages are compressed and take up less space. You increase the work load of your server a bit, but you still get positive results.
Also consider installing the Apache mod_deflate.
8.) Optimize your PHP file sizes
This isn't something that people talk about so often. Mostly forum tuning has seemed to focus on SQL query performance, but there are other methods as well.
Once you've got your queries running in milliseconds, your data cached, and your templates ripped and optimized, the final frontier for forum speed-ups is to further decrease RAM (memory) usage and trim down your PHP files.
The first places to start are your language and includes files, especially functions.php Take a look at how many bytes they take up. That's a factor in how much RAM your server will have to use to display a single forum page. If you can cut down your files, you can decrease RAM, server load, and parse time.
As mentioned earlier in section 4, if your lang_main.php is bloated with hundreds of extra lang strings added by MODs, see what you can do to split them out into their own files. Basically, if the lang string isn't used on every, or even most pages, then it does not belong in lang_main.php
That same theory goes for functions.php. This file, which is big enough to begin with, becomes the unfortunate dumping ground for MODs' functions. Same with the language files, if a function isn't used on most or every page, then cut it out and put it into its own file.
If you're really desperate, remove the comment lines, especially the big multiline ones. You'll save a few K of disk space, and milliseconds of parse time.
I did these same file optimizations on my own site and saved around 20-30 KB of RAM. It doesn't seem like a whole lot, but it still matters because pages load faster and can support more users online at the same time.
9.) Optimize the PHP code itself
Once you've optimized the size of your PHP files, you can go at them and further trim them down. Most MODs out there, especially the ones which are not in the Official phpBB 2 MOD Archive, are NOT there for a reason: they are poorly written and do not follow the phpBB Coding Standards.
If you still wish to use these MODs, look over all the code and check for things such as:
- SQL queries within loops
- Double quotes where there should be single quotes
- Loops which could be optimized or eliminated
- Improper template or SQL usage
- Static queries which aren't cached
- Poorly chosen or missing table indices
- Any other code which does not follow the phpBB Coding Standards.
If you have the knowledge and know-how to clean up others' code on your forum, then you should do it. You have noting to loose but some of your time, and everything to gain in return!
Sometimes, or even a lot of time depending on the state of the code, optimizing the PHP code will result in files which take up less disk space. True story: just last night I took a look at the relative time function I wrote back in 2006. It takes times and computes them to 31 seconds ago, 2 minutes ago, 5 months ago, etc. Relative times. Anyway, I optimized it to work more efficiently and, surprise! It went from 1,700 bytes down to 1,300 bytes. Yes, that's miniscule amount, just 400 bytes, but I can rest assured knowing that that function is infinitesimally faster (and smaller).
Also, install a query counter and keep the number of queries executed per page to a minimum. The optimal page uses 10 or less queries. Any page which has more than 15 queries has a problem and should be looked at.
10.) Speed up the search process
While phpBB's built-in forum search is great for small to medium-sized boards, it really starts to break down when your forum approaches 400,000 or more posts, and that is simply due to the sheer size of the search tables-- you could easily have 1 million rows for half as many physical posts. Posting and editing will start to slow down, as will executing any sort of search.
There are a plethora of techniques for improving the search, from optimizing the built-in search system, to using MySQL Fulltext, to even using the Sphinx search engine. Rather than spend a lot of time rehashing all these methods, I refer you first to look through the phpBB Tweaks for large forums topic, which is littered with many good tips. Also, drathbun maintains an excellent in-depth blog where he has posted a series of technical documents explaining the search system and how to improve it:
http://phpbbdoctor.com/blog/category/phpbb/search/ Lastly, elsewhere on the phpBB forums and web site there are discussions and MODs for using MySQL fulltext, as well as the Sphinx search engine.
Conclusion:
Following all these tips are guaranteed to speed up your forum, or any other PHP/MySQL site. You could take a site which renders pages in 1 second and transform it into pages which take .1 second.
Further reading:
20 Examples of Bad phpBB MODs