[2.0.x] Tweaks for large forums

[xkevinx]

OMG(OH MY GOD!!!)!!!

Gaia has 170669061 articles posted, with 71184261 in storage
We have 906098 registered users
Most users ever online was 19624 on Fri Aug 27, 2004 1:38 pm

WoW!
AMAZING!(Now you must be proud of yourself!) An amazing website.
I can't even think of a server that strong!

Sorry for writing like this but -OMG.
Cya

Actually There are now 47 Servers that run gaia I Believe

[ZoliveR]

47?
Last i heard it was 24-25 servers... It's quite astonishing that 47 servers can handle this...

[Darth Wong]

Does anyone have any feedback to offer on which tweaks offered the greatest perceived performance gain?

[xkevinx]

47?
Last i heard it was 24-25 servers... It's quite astonishing that 47 servers can handle this...

After getting the servers to work right it was then time to prepare all the new servers. We now have a total of 47 servers, with many specialized to offload the main database and speed up the overall service. As we continue ot roll out new features, my time is mostly focused on background tasks related to stability, bug fixes, and optimization.

[ZoliveR]

Mhh ok
Thanks for your quoting

[xkevinx]

Hi Lanzer,
Im xFusionx on Gaia and i was wondering did you make any changes your ACP weather it be to speed up or change it around. Cause you must have many Catagories in there now and many controls. I would guess you took off the who is online thing inthe admin index. Would be way to long to load. Cant wait for Gaia Fishing.

Kevin

[nazon]

...just out of curiosity how long does it take to load the "who is online?" But I agree.. a very cool board indeed

[Rayquazza]

It has been removed in index.php, only leaving the moderators and administrators. It has also been removed in viewforum.php, the "users browsing this forum".

[elitelinux]

Tweak for large boards, as implemented first on Linux Forums

This tweak eliminates the "SELECT * FROM config" query that is run on every page load.

Read and understand the entire hack before implementing it.

Here goes....


Open config.php, add the following code at the end of the file:

Code: Select all

$board_config_file = "/unix/path/to/your/forum/board_config.php"; // (YOU SHOULD MODIFY the ***PATH***. Filename IS board_config.php here.)

if( file_exists($board_config_file) && is_readable($board_config_file) )
{
        require_once($board_config_file);
}


Save, close.

Create this new file "generate_board_config.php", it will be run as a cron job and dump the contents of your config table to a file that can be included by your forum code at runtime. You will proberly want to set this to run as an hourly cron job.

You may need to include a #!/usr/bin/php line at the top. (use "which php" to work out the correct path/file to use!).

Code: Select all

<?
require "/unix/path/to/your/forum/config.php"; // (YOU SHOULD MODIFY the PATH. Filename is **config.php**)

$conn = mysql_connect($dbhost, $dbuser, $dbpasswd) or die("Failed to connect to database");
mysql_select_db($dbname, $conn) or die("Failed to select db");

$query = "SELECT config_name, config_value FROM config";
$res = mysql_query($query, $conn) or die(mysql_error($conn));


$contents = "<?\n";
while( $row = mysql_fetch_object($res) )
{
        $config_name = $row->config_name;
        $config_value = $row->config_value;

        $contents .= "\$board_config['{$config_name}'] = '{$config_value}';\n";
}
$contents .= "?>";


mysql_close($conn);

if( strlen($contents) )
{
        if (is_writable($board_config_file))
        {
                if (!$handle = fopen($board_config_file, 'w'))
                {
                        echo "Cannot open file ($board_config_file)";
                        exit;
                }

                if (fwrite($handle, $contents) === FALSE)
                {
                        echo "Cannot write to file ($board_config_file)";
                        exit;
                }

                fclose($handle);
        }
        else
        {
                echo "The file $board_config_file is not writable";
        }
}

?>


Execute it once manually, check that board_config.php in your forum directory was created and is filled with the contents of your config table. It should look like a load of php code.


Now open common.php, Find:

Code: Select all

        $sql = "SELECT *
                FROM " . CONFIG_TABLE;
        if( !($result = $db->sql_query($sql)) )
        {
                message_die(CRITICAL_ERROR, "Could not query config information", "", __LINE__, __FILE__, $sql);
        }

        while ( $row = $db->sql_fetchrow($result) )
        {
                $board_config[$row['config_name']] = $row['config_value'];
        }


Replace with:

Code: Select all

if( count($board_config) == 0 )
{
        $sql = "SELECT *
                FROM " . CONFIG_TABLE;
        if( !($result = $db->sql_query($sql)) )
        {
                message_die(CRITICAL_ERROR, "Could not query config information", "", __LINE__, __FILE__, $sql);
        }

        while ( $row = $db->sql_fetchrow($result) )
        {
                $board_config[$row['config_name']] = $row['config_value'];
        }
}


This will make sure it loads the data from your config table if for any reason (eg, $board_config_file not readable / doesnt exist / is empty ) the board_config array could not be filled.

Save, close.
Done.

once your happy its working, dont forget to configure it as a cron job.

Also note, if you make a change to your board configuration on the "configuration" page of the phpbb admin panel, you will manually need to re-run this script for the changes to take effect (or wait until its triggered via cron again).

This may not deliver a great direct performance boost, but it will take load off your database server. If your database is overloaded, you will proberly appriciate this.

EG: If you have 200 page requests a minute for example, this will eliminate 200 queries on your database every minute. Over the hour (60*200) this elimimates 1200 SQL queries on your database. Ok their small queries... but queries none the less. This transfers work from your db server to your http server.

Comments, suggestions welcome, note that I offer no warrenty that it will work on your board. Also note it is beyond the scope of this post to explain to you how to use cron. Those who want to know more about cron might like to read this tutorial

have fun

Jason

[arod-1]

newbe question:
i noticed that phbb sucks at searches (no offence intended).
quite a few of the tweaks in this thread deal with search.

now, this topic (i mean, archiving and searching in general, not specifically in phpbb) receive a lot of cycles in the last coulpa years, what with google and everything.

wouldnt it make sense to offload the indexing and searching task to a pro?

what i mean is, there are many open source search engines, quite a few of them use php.

wouldnt it make sense to create a mod that use one of those "professional" engines? i mean, i have a lot of respect to the people here, (yes, i mean you, lanzer), but there are good chances that a project that concentrate solely on archiving and searching technology will get better results, using less cycles.

if this topic was discussed before, its ok to flame me. i admit i only read the first and last two pages of this topic. i am going now to read pages 3-12...


edit:
i am now at page 5, and i see that bartVB talk about just such option. soooooo:
did anyone try it?
any chances of making a mod out of it?
just curious.

[arod-1]

on Dec 01, 2003 9:29 am

Second problem I came across is the updating of the forum table. As there are 250,000+ posts a day, the forum table gets updated every time a post is made. That's not a big deal, the problem is that since the forum table is updated constantly, all the forum permissions cannot be cached by MySQL through the MYSQL_CACHE feature, as contents within a cache will drop every time the table is updated. As forum permission is something that's queried on every single pageview. Having it cached will give terrific performance increase. Also, with less table updates I can move the forum table back to MyISAM which will be much faster on reads than InnoDB.

The first step is to disable the updating of forum_last_post_id. The code is in functions_post.php

disable:

Code: Select all

        $sql = "UPDATE " . FORUMS_TABLE . " SET $forum_update_sql WHERE forum_id = $forum_id";
        if (!$db->sql_query($sql))
        {
            message_die(GENERAL_ERROR, 'Error in posting', '', __LINE__, __FILE__, $sql);
        }

Next, slip in this function in a cron job:

Code: Select all

$sql = "SELECT forum_id, forum_last_post_id from " . FORUMS_TABLE;
$result = $db->sql_query($sql);
while (list($forum_id,$forum_last_post_id) = $db->sql_fetchrow($result) ) {
    $sql2 = "SELECT topic_last_post_id AS last_post FROM ".TOPICS_TABLE." WHERE forum_id = $forum_id ORDER BY topic_last_post_id DESC LIMIT 1";
    $result2 = $db->sql_query($sql2);
    $last_post = $db->sql_fetchfield("last_post", 0, $result2);
    if ( $last_post > $forum_last_post_id ) {
        $sql3 = "UPDATE " . FORUMS_TABLE . " SET forum_last_post_id = $last_post WHERE forum_id = $forum_id";
        $db->sql_query($sql3);
    }
}

I now update the forum table every minute. Works great. (note the lack of error detection, I need to tidy up my code later

Was tempted to make a single query to grab all the latest post_id, but anything involving the GROUP BY statment takes forever to run.

there is probably something i dont see, but please, humor me:
issue you describe here, relate to the fact that a very read-busy table needs to be written to just to update counters.
wouldnt it make more sense to create a new table that will hold the counters only?
something like:

Code: Select all

forum_table_counters:
+--------------------+-----------------------+------+-----+---------+-------+
| Field              | Type                  | Null | Key | Default | Extra |
+--------------------+-----------------------+------+-----+---------+-------+
| forum_id           | smallint(5) unsigned  |      | PRI | 0       |       |
| forum_posts        | mediumint(8) unsigned |      |     | 0       |       |
| forum_topics       | mediumint(8) unsigned |      |     | 0       |       |
| forum_last_post_id | mediumint(8) unsigned |      | MUL | 0       |       


this table will be written to for each post, thus freeing the forum_table from frequent updates.

[lanzer]

Hello arod:

there is probably something i dont see, but please, humor me:
issue you describe here, relate to the fact that a very read-busy table needs to be written to just to update counters.
wouldnt it make more sense to create a new table that will hold the counters only?

As the database was seeing a performance issue of having far too many unnecessary writes to the table, splitting the table up would still leave the database occupied with updates and isn't a long term solution. Also, if the amount of read far exceed the number of writes, splitting a table up means much more table joins and sectors to seek, causing more performance hits than gains.

On a side note:

Having live update of who last posted on the forum level became irrelevant when the field is updated every second. Even for a small forum it's useful to know if a forum has new posts since last visit, though who last posted in the forum just isn't that practical of an info.

Hi elitelinux:

Storing static data in the hard disk is definitely one of the first things to do for enhancing performance. In the middle of the thread I have a little function for storing theme, auth access, smilies, or just any multi-dimentional arrays to local disk, when a specified timeout occures it'll query the database for an update.

Unfortunately for me a lot of dynamic info such as on-line user count, message count, and system time is obtained from the config query so I can't have it cached. Soon those info will be moved to a memory cache server to offload the database though. (been trying to do that for months now, too busy)

Hi Kevin:

The who's on-line list is kept in a hash table so bringing them up takes no time. (forgot which page the info was posted) Though with a list that occupies the screen it's a rather unnecessary feature. The list will be used for non-forum related features later actually.

[sander marechal]

This was talen out:

Code: Select all

if ( $userdata['session_logged_in'] )
   {
      $sql = "SELECT t.forum_id, t.topic_id, p.post_time
         FROM " . TOPICS_TABLE . " t, " . POSTS_TABLE . " p
         WHERE p.post_id = t.topic_last_post_id
            AND p.post_time > " . $userdata['user_lastvisit'] . "
            AND t.topic_moved_id = 0";
      if ( !($result = $db->sql_query($sql)) )
      {
         message_die(GENERAL_ERROR, 'Could not query new topic information', '', __LINE__, __FILE__, $sql);
      }

      $new_topic_data = array();
      while( $topic_data = $db->sql_fetchrow($result) )
      {
         $new_topic_data[$topic_data['forum_id']][$topic_data['topic_id']] = $topic_data['post_time'];
      }
   }


It just end up checking to see if the latest post is posted after the user's last visit time. Quick and dirty change.

I ended up using this trick in a slightly different way. This hack makes you loose the exact info if you read all topics in the forum. Even if you read them all, the forum will still say 'new messages'. For gargantuan size boards this is ofcourse no problem at all but on my board there are a couple of important forums that are not busy while the rest of the forum is.

I simply created a new field in the topics table that holds the last timestamp a message was posted. The above SQL query was changed to this:

Code: Select all

      $sql = "SELECT t.forum_id, t.topic_id, t.topic_last_post_time
         FROM " . TOPICS_TABLE . " t
         WHERE t.topic_last_post_time > " . $userdata['user_lastvisit'] . "
         AND t.topic_moved_id = 0";


The topic_last_post_time field is updated in the update_post_starts() function in functions_post.php:

Code: Select all

   else if ($mode != 'poll_delete')
   {
      $forum_update_sql .= ", forum_last_post_id = $post_id" . (($mode == 'newtopic') ? ", forum_topics = forum_topics $sign" : "");
      $topic_update_sql = "topic_last_post_id = $post_id, topic_last_post_time = " . time() . (($mode == 'reply') ? ", topic_replies = topic_replies $sign" : ", topic_first_post_id = $post_id");
   }
   else
   {
      $topic_update_sql .= 'topic_vote = 0';
   }

The SQL execution time on the index.php for this bit dropped from 0.25 seconds to 0.002 seconds. I think it's a nice compromise between lanzer's hack and the standard code.

Note: The topic_last_post_time can also be used in viewforum.php if you don't show the last poster's username and it can be used in the 'mark forum read' function as well.

[johntp]

I am blown away by that forum...it is the most unique and original design I have seen. I am not big into tweaking or messing with my boards -- they are small boards no real need to go nuts, but I was curious about you main forums page. Did you just design that form from scratch? The layout creates a great uncluttered look and feel. Would love to find something like that I could use.

Great original look!

[xkevinx]

My gosh so this is what runs this humungo site. What Datacenter did you end up moving to. And do you like it so far.
http://www.gaiaonline.com/forum/viewtop ... #169022338