Tuning drupal for hi-traffic

Hi, I recently switched from phpbb to drupal. (http://drupal.org/node/44368)

Here is my configuration

- web server: Dual Xeon 3.0Ghz (32bit), 2G RAM, SATA HDD
- db server: same as web server (physically different server)
- software: apache 1.3.34, php 5.1.2, mysql 5.0.18
- total number of nodes: 70,000+
- total number of comments: 330,000+
- total number of users: 25,000+
- Usually there are 150+ anonymous users and 50+ registered users (by checking Who's online block)
- Modules: most core modules (4.7) except archive, book, drupal, legacy, ping with contributed modules (adsense, codefilter, favorite_nodes, google_analytics, logintobaggan, nmoderation, subscription, taxomony_access, urlfilter, votingapi, comment_upload, tagadelic)

The problem was... the site was too slow at first. Especially, the 'view recent' was terribly slow. It took almost 10 seconds to display and showing node/posting comment also took more than 5 seconds.

So we modified the mysql database and tracker.module like this:

ALTER TABLE comments ADD INDEX index_comments_count(pid, status);

This was for speeding up "SELECT COUNT(cid) FROM comments WHERE pid = ? AND status = ?" at comment.module. This really boosted the speed of showing node with comments as well as posting comment.

How about modifying the drupal core like this?

And to speed up the 'view recent', we modified the following part:

$sql = 'SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM {node} n INNER JOIN {users} u ON n.uid = u.uid INNER JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE n.status = 1 ORDER BY last_post DESC';

modified:

$sql = 'SELECT n.nid, n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM {node} n INNER JOIN {users} u ON n.uid = u.uid INNER JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE n.status = 1 ORDER BY last_post DESC';

Is it okay to remove DISTINCT?

And we modified the following part also:

$sql = 'SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM {node} n INNER JOIN {node_comment_statistics} l ON n.nid = l.nid INNER JOIN {users} u ON n.uid = u.uid LEFT JOIN {comments} c ON n.nid = c.nid AND (c.status = %d OR c.status IS NULL) WHERE n.status = 1 AND (n.uid = %d OR c.uid = %d) ORDER BY last_post DESC';
$sql_count = 'SELECT COUNT(DISTINCT(n.nid)) FROM {node} n LEFT JOIN {comments} c ON n.nid = c.nid AND (c.status = %d OR c.status IS NULL) WHERE n.status = 1 AND (n.uid = %d OR c.uid = %d)';
$sql = db_rewrite_sql($sql);
$sql_count = db_rewrite_sql($sql_count);
$result = pager_query($sql, 25, 0, $sql_count, COMMENT_PUBLISHED, $uid, $uid);

modified:

$sql = 'SELECT * FROM ( SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM {node} n, {node_comment_statistics} l, {users} u, {comments} c WHERE c.uid= %d AND n.nid=c.nid AND l.nid = n.nid AND u.uid=n.uid UNION SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM {node} n, {node_comment_statistics} l, {users} u WHERE n.uid = %d AND l.nid = n.nid AND u.uid=n.uid)zz ORDER BY last_post DESC';
$sql_count = 'SELECT COUNT(*) FROM ( SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM {node} n, {node_comment_statistics} l, {users} u, comments c WHERE c.uid=%d AND n.nid=c.nid AND l.nid = n.nid AND u.uid=n.uid UNION SELECT DISTINCT(n.nid), n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, l.comment_count FROM {node} n, {node_comment_statistics} l, {users} u WHERE n.uid = %d AND l.nid = n.nid AND u.uid=n.uid )zz';
$sql = db_rewrite_sql($sql);
$sql_count = db_rewrite_sql($sql_count);
$result = pager_query($sql, 25, 0, $sql_count, $uid, $uid);

How about modifying the drupal core like this?

We also modified mysql configuration so that it does not create 45M+ temporary file at /tmp. (Bigger buffer size for sort/join) But the table lock / system lock still happens quite often.

how we can boost the speed more? If you have any good idea for speeding up drupal site, please share your experience or share the link for previous discussion.

Thanks very much.