[2.0.x] Tweaks for large forums

[Dog Cow]

wow that's crazy.

Not to mention expensive!

[lanzer]

Hello dcz:

Today Gaia has a about 150 web servers behind our load balancer, and about 20 databases handling different features. Many of our old servers had been either de-commissioned or converted to storage servers. The new servers we're installing are all Opteron servers that are 4 times faster than older machines with the same power requirement. Since the ISP charges a lot for power, it's much more economical for us to upgrade.

We don't really believe in clustering. Rather we changed our database structure and the database abstraction layer to divide up data into seperate physical databases. That way we only need to add more of the same servers when upgrading, and we don't have to spend money on expensive clustering solutions and risk supporting a complex system.

We've just gotten another 20 database servers to replicate the existing ones for redundency. No replication is used for scalability or handling bandwidth. We still use MyISAM for small logs and data that doesn't get updated more than once a second, otherwise everything else is InnoDB.

The session handler had been completely re-written to use the new database structure and to accomidate a whole bunch of Gaia related features. A caching system for gold earning for example. The user id determines which of the 4 database server to use. When we need more performance we will expand that up to 8 and change the session handler to accomidate 8 servers.

The database structure had also changed for the ability to handle threads of unlimited posts. Changes like these involved a few guys working together and a span of months to complete. Once I find the time I'll publish them for the phpbb team. Right now I'm still pulling my hair trying to get the latest features to release.

There are many other nifty things that we're doing, thanks to the new IT guy that joined our team, and many software developed by the guys at Livejournal such as memcache, PerlBal and MogleFS. More details will be posted once the dust settles.

-Lanzer

What would be interesting to know is what is gaiaonline server setup ?

Single server, cluestered ?
Separated sql, with slaves ?
Which db type ?
What is your session trick (30 000 at a time, must be hard to deal with sometimes) ?

And since obviously you tweaked it a lot, did you implement other things than the one demonstarted here?

This may have olready been said somewhere, but I am wondering.

By the way, bravo for gaia, hudge, and still fast.

Thanks to you, nobody can really say phpbb is not a working script (even though you must have changed a lot of things).

++

[dcz]

I am impressed.

And it's good to hear clustering is not the only solution, cause, as you said, it becomes a bit more complex to administrate and maintain.

Anyway, thanks you for sharing, I am very far from going for more than one server per forum so far, but your work while pushing limits demontrates to us all that a hudge success will not be impossible to handle

++

[mgutt]

@ lanzer

i tested a little bit around with this code in viewtopic.php:

Code: Select all

// Pages
$total_pages = ceil($total_replies/$board_config['posts_per_page']);
$on_page = floor($start / $board_config['posts_per_page']) + 1;
if ($start > 100 && ($total_replies / 2) < $start) {
    $reverse = TRUE;
    $last_page_posts = $total_replies - ($board_config['posts_per_page'] * ($total_pages - 1));
}
// Reverse & Post Id's
$p_array = array();
if (isset($reverse)) {
    $limit_string = ($total_pages == $on_page) ? $last_page_posts : ($last_page_posts + ($total_pages - $on_page - 1) * $board_config['posts_per_page'] ).','. $board_config['posts_per_page'];
    $sql = "SELECT p.post_id FROM " . POSTS_TABLE . " p USE INDEX(topic_n_id) WHERE p.topic_id = $topic_id $limit_posts_time ORDER BY p.post_id DESC LIMIT $limit_string";
} else {
    $sql = "SELECT p.post_id FROM " . POSTS_TABLE . " p WHERE p.topic_id = $topic_id $limit_posts_time LIMIT $start, " . $board_config['posts_per_page'];
}
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, "Could not obtain post index information.", '', __LINE__, __FILE__, $sql);
}
$post_index = '';
while ($row = $db->sql_fetchrow($result))
{
   $post_index .= (($post_index != '') ? ', ' : '') . intval($row['post_id']);
}
$db->sql_freeresult($result);
// Pull the data
$sql = "SELECT pt.post_sub_title, u.username, u.user_id, u.user_posts, u.user_from, u.user_firstname, u.user_ebayname, u.user_car, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_gender, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid
   FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . POSTS_TEXT_TABLE . " pt
   WHERE p.post_id in ($post_index)
      AND pt.post_id = p.post_id
      AND u.user_id = p.poster_id
   ORDER BY p.post_time $post_time_order";

sometimes if the users are posting very fast $post_index isn't filled or the generated posts order of $limit_string is wrong. i think its because the time between the both queries.

do you have similar experience?

[Partsking]

There seems to be a problem with this coding.

Code: Select all

$p_array = array();
$sql = "SELECT p.post_id FROM " . POSTS_TABLE . " p WHERE p.topic_id = $topic_id $limit_posts_time LIMIT $start, " . $board_config['posts_per_page'];
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, "Could not obtain post index information.", '', __LINE__, __FILE__, $sql);
}
while (list($p_id) = $db->sql_fetchrow($result)) {
    $p_array[] = $p_id;
}
$post_index = implode(",",$p_array);

The problem comes in when the large query of

Code: Select all

$sql = "SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_warnings, u.user_level, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid,t.topic_poster
      FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . POSTS_TEXT_TABLE . " pt
      WHERE p.post_id in ($post_index)
         AND pt.post_id = p.post_id
         AND u.user_id = p.poster_id
      ORDER BY p.post_time $post_time_order";

only gets ,,,,,, from $post_index

So I get this error:

Code: Select all

Could not obtain post/user information.

DEBUG MODE

SQL Error : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,,,,,,) AND pt.post_id = p.post_id AND u.user_id = p.poster_id ' at line 3

SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_warnings, u.user_level, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid,t.topic_poster FROM phpbb_posts p, phpbb_users u, phpbb_posts_text pt WHERE p.post_id in (,,,,,,,,) AND pt.post_id = p.post_id AND u.user_id = p.poster_id ORDER BY p.post_time ASC

Line : 507
File : viewtopic.php


Could someone post the right coding to get a correct $post_index?

I tried adding this in mine and I'm getting this same error as well. Any suggestions?

[EverettB]

I'm using this on my site without problems

Code: Select all

$p_array = array();
$sql = "SELECT p.post_id
      FROM " . POSTS_TABLE . " p
      WHERE p.topic_id = $topic_id
      $limit_posts_time
      ORDER BY p.post_time $post_time_order
      LIMIT $start, " . $board_config['posts_per_page'];
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, "Could not obtain post index information.", '', __LINE__, __FILE__, $sql);
}
while ($row = $db->sql_fetchrow($result))
{
    $p_array[] = $row['post_id'];
}
$post_index = implode(",",$p_array);

The second SQL call is identical.

[Dog Cow]

Here's a tip if your forums index page is taking too long to load and you've tried everything:

Remove all of the SQL, and just fill in the forums by hand in the template file index_body.tpl

IF you have hidden forums in a special category, you can alter the SQL query that grabs all the cats (the first query, I think) to only get the category with the hidden cat_id (mine is 6).

[dcz]

extreme but very efficient

[koalaii]

I had the same error as Partsking then i use Everett's code and now it works fine.

[Dog Cow]

Here's another somewhat extreme tip that will save you a few queries:

By default, phpBB was setup for templates, and thus queries the database on every page load to get the data.

If you have a forum that only uses one style, you can hard code your forum to that style, thus eliminating at least two queries.

The function and queries to look for are in functions.php and the function is called setup_style

[drathbun]

Nice call on the templates, I had not thought about that. None of my boards use more than one template, so that's an excellent tip for me.

[nidokidos]

sir my site is
www.nidokidos.org

i installed phpbb with a portal mod.

my users are not more then 100 to 150 daily . but my site is eating up server resources. and eat up to 90 percent of server resources. the server admin has warned me and given me time limit of 30 days to move the site on any other server.

please visit my site and suggest me how i can improve it.

i am very much worried , please help me.



Naveed.

[Dog Cow]

I have taken a look at your forums and they are not very large with only 1K users and ~250 posts.

I'm guessing that your heavily modified version of phpBB is slowing everything down.

[EverettB]

If you have a forum that only uses one style, you can hard code your forum to that style, thus eliminating at least two queries.

The function and queries to look for are in functions.php and the function is called setup_style

Good idea.

I have the template data cached to disk using methods mentioned earlier in this thread. This also works very nicely. Here's an excerpt:

Replace

Code: Select all

function setup_style($style)
{
   global $db, $board_config, $template, $images, $phpbb_root_path;

   $sql = "SELECT *
      FROM " . THEMES_TABLE . "
      WHERE themes_id = $style";
   if ( !($result = $db->sql_query($sql)) )
   {
      message_die(CRITICAL_ERROR, 'Could not query database for theme info');
   }

   if ( !($row = $db->sql_fetchrow($result)) )
   {
      // We are trying to setup a style which does not exist in the database
      // Try to fallback to the board default (if the user had a custom style)
      // and then any users using this style to the default if it succeeds
      if ( $style != $board_config['default_style'])
      {
         $sql = 'SELECT *
            FROM ' . THEMES_TABLE . '
            WHERE themes_id = ' . $board_config['default_style'];
         if ( !($result = $db->sql_query($sql)) )
         {
            message_die(CRITICAL_ERROR, 'Could not query database for theme info');
         }

         if ( $row = $db->sql_fetchrow($result) )
         {
            $db->sql_freeresult($result);

            $sql = 'UPDATE ' . USERS_TABLE . '
               SET user_style = ' . $board_config['default_style'] . "
               WHERE user_style = $style";
            if ( !($result = $db->sql_query($sql)) )
            {
               message_die(CRITICAL_ERROR, 'Could not update user theme info');
            }
         }
         else
         {
            message_die(CRITICAL_ERROR, "Could not get theme data for themes_id [$style]");
         }
      }
      else
      {
         message_die(CRITICAL_ERROR, "Could not get theme data for themes_id [$style]");
      }
   }

   $template_path = 'templates/' ;
   $template_name = $row['template_name'] ;


With

Code: Select all

function setup_style($style)
{
   global $db, $board_config, $template, $images, $phpbb_root_path, $phpEx;

   /*
   $sql = "SELECT *
      FROM " . THEMES_TABLE . "
      WHERE themes_id = $style";
   if ( !($result = $db->sql_query($sql)) )
   {
      message_die(CRITICAL_ERROR, 'Could not query database for theme info');
   }

   if ( !($row = $db->sql_fetchrow($result)) )
   {
      // We are trying to setup a style which does not exist in the database
      // Try to fallback to the board default (if the user had a custom style)
      // and then any users using this style to the default if it succeeds
      if ( $style != $board_config['default_style'])
      {
         $sql = 'SELECT *
            FROM ' . THEMES_TABLE . '
            WHERE themes_id = ' . $board_config['default_style'];
         if ( !($result = $db->sql_query($sql)) )
         {
            message_die(CRITICAL_ERROR, 'Could not query database for theme info');
         }

         if ( $row = $db->sql_fetchrow($result) )
         {
            $db->sql_freeresult($result);

            $sql = 'UPDATE ' . USERS_TABLE . '
               SET user_style = ' . $board_config['default_style'] . "
               WHERE user_style = $style";
            if ( !($result = $db->sql_query($sql)) )
            {
               message_die(CRITICAL_ERROR, 'Could not update user theme info');
            }
         }
         else
         {
            message_die(CRITICAL_ERROR, "Could not get theme data for themes_id [$style]");
         }
      }
      else
      {
         message_die(CRITICAL_ERROR, "Could not get theme data for themes_id [$style]");
      }
   }
   $db->sql_freeresult($result);
   */

   // Start caching
   $row = array();
   include($phpbb_root_path . 'cache/theme.' .$phpEx);
   if (sizeof($row) == 0)
   {
      $sql = "SELECT SQL_CACHE *
            FROM " . THEMES_TABLE . "
            WHERE themes_id = $style";
      if( !$result = $db->sql_query($sql) )
      {
         message_die(CRITICAL_ERROR, 'Could not query database for theme info');
      }
      $file_data = '<? ' . '$row = ' . format_array($db->sql_fetchrow($result)) . ' ?>';
      $db->sql_freeresult($result);
      $handle = fopen($phpbb_root_path . 'cache/theme.'.$phpEx, 'w');
      @flock($handle, LOCK_EX);
      fwrite($handle, $file_data);
      @flock($handle, LOCK_UN);
      fclose($handle);
      include($phpbb_root_path . 'cache/theme.' .$phpEx);
   }
   // End caching

   $template_path = 'templates/' ;
   $template_name = $row['template_name'] ;


[Marnboorapa]

error, because there is no this file

cache/theme.'.$phpEx

or that you mean cache/theme.php

no there is no this file in phpbb.