How do I Improve Drupal 7 Forum Performance?

Question

I am working on a website that has 60k+ forum topics and it is extremelly slow. The problem is that the core forum module uses the following query

SELECT ncs.last_comment_timestamp AS last_comment_timestamp
, ncs.last_comment_uid AS last_comment_uid
, CASE ncs.last_comment_uid WHEN 0 THEN ncs.last_comment_name ELSE u2.name END AS last_comment_name FROM node n
INNER JOIN users u1 ON n.uid = u1.uid
INNER JOIN forum f ON n.vid = f.vid AND f.tid = '22'
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
INNER JOIN users u2 ON ncs.last_comment_uid = u2.uid WHERE  (n.status = '1') ORDER BY last_comment_timestamp DESC LIMIT 1 OFFSET 0

I saw that this query was the problem because after running the top command I saw that mysql was consuming 150%+ CPU so I ran a show full processlist that showed that the query above was always in the following state

Copying to tmp table    SELECT ncs.last_comment_timestamp AS last_comment_timestamp, ncs.last_comment_uid AS last_comment_uid, CASE ncs.last_comment_uid WHEN 0 THEN ncs.last_comment_name ELSE u2.name END AS last_comment_name\nFROM \nnode n\nINNER JOIN users u1 ON n.uid = u1.uid\nINNER JOIN forum f ON n.vid = f.vid AND f.tid = '22'\nINNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid\nINNER JOIN users u2 ON ncs.last_comment_uid = u2.uid\nWHERE  (n.status = '1') \nORDER BY last_comment_timestamp DESC\nLIMIT 1 OFFSET 0

The result of the explain command is

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  f       ref     PRIMARY,tid     tid     4       const   30030   Using index; Using temporary; Using filesort
1       SIMPLE  n       eq_ref  PRIMARY,vid,node_status_type,uid        vid     4       drupal.f.vid    1       Using where
1       SIMPLE  ncs     eq_ref  PRIMARY,last_comment_uid        PRIMARY 4       drupal.n.nid    1
1       SIMPLE  u2      eq_ref  PRIMARY PRIMARY 4       drupal.ncs.last_comment_uid     1       Using where
1       SIMPLE  u1      eq_ref  PRIMARY PRIMARY 4       drupal.n.uid    1       Using where; Using index

Any ideas what to do to improve the performance here ?

Thanks

Answer 1

If you remove statistics then things should fly. From my understanding drupal statistics does not scale well

Answer 2

Rather vague question that likely is very system specific, but the first thing you can try is tuning your database server. If you were running D6, then you could try using DB Tuner, but apparently this doesn't have a D7 version.

A lot of the module is based on MySQL Tuner, which will examine a live database and give suggestions on parameters to adjust.

I would also read the resources in the answers to https://drupal.stackexchange.com/questions/1715/what-would-the-optimal-mysql-configuration-for-a-drupal-7-site-be

Answer 3

The ORDER BY forces MySQL to store the selected rows in an internal temporary table to sort them before returning the result set. If this result set is more than MySQL can handle in memory, the temporary table has to be stored on disk. Obviously, this severely impacts performance.

The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. You could try and increase these values in my.cnf and see if it speeds execution up. mysqltuner will give you valuable hints here.

It looks to me that the statement goes through a lot of (database) effort only to return a single result row. Optimizing it could yield a significant performance gain. Unfortunately, I don't have the time just now.