ZoliveR wrote: 47?
Last i heard it was 24-25 servers... It's quite astonishing that 47 servers can handle this...
Lanzer wrote: 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.
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);
}
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";
}
}
?>
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'];
}
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'];
}
}
lanzer wrote: 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:Next, slip in this function in a cron job: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); }
I now update the forum table every minute. Works great. (note the lack of error detection, I need to tidy up my code laterCode: 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); } }
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.
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 |
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?
lanzer wrote: This was talen out:It just end up checking to see if the latest post is posted after the user's last visit time. Quick and dirty change.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']; } }
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";
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';
}