1.6 million forum messages = extremely slow
I've installed Drupal 4.7.x, used the phorum converter to migrate my Phorum 5.1 data over and Drupal takes forever to load any of the forum pages...
300,000 records in the node table, 1.6 million in the comments table. This is on a dedicated server with a pretty fast RAID 1 and 1.5GB RAM. Phorum 5.1 on the same server loads all pages always in less than a second. I realize that Drupal isn't optimized to deliver super fast forums, but I'm seeing page load times of 22 seconds for the main forum list and 5-8 pages for forum topics.
I'd love to use as many of the core modules as possible (forum included), but if this is how it's going to perform then I think I'll need to look into the phorum_integration module instead.
Does anyone else have any experience with getting forums of a similar size to perform well?
I'm eager to use all the other modules available to me with Drupal, but it's looking like I'll need to abandon the forum module for performance reasons (I was willing to live with the forum module feature-wise).
Thanks in advance.
You are writing on one....
It sounds like a badly coded module to me. Some modules have very bad SQL and other logic. I have seen modules that simply load ALL nodes in an array and then filter out the ones they need in a while or foreach loop!
You should best switch off contributed modules one a time and see if the problem goes away. Drupal should be able to handle the load you describe w/o much problem.
Bèr
---
Professional | Personal
only running core modules
I'm only running the core modules needed to run a forum.
I ran mysqlcheck and the benefit is not measurable. This was essentially a fresh load of the database so perhaps things were already as optimized as they would be.
Honestly, the slow responsiveness of the drupal.org site and the forums in particular lead me to believe that my experience is the norm. I briefly turned on the devel module to view the query times and there appear to be a handful of queries that take a long time to execute on each forum page. I guess I could play around with the indexes, but I would imagine that all the necessary indexes would already have been tested, optimized and configured to provide good performance.
I've turned on the devel
I've turned on the devel module and I've attached the query runtime for my forum list page. All the time is spent pulling up the last_comment_timestamp.
I ran an explain on the slow queries: The first is on my smallest forum (query take 0.6 seconds):
mysql> explain SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid 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 AND n.type='forum' AND tn.tid = 5 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1;+----+-------------+-------+--------+---------------------------------------------------+---------+---------+----------------------------------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------------------------+---------+---------+----------------------------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | tn | ref | PRIMARY,nid,tid | PRIMARY | 4 | const | 15859 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | n | ref | PRIMARY,node_type,status,uid,node_status_type,nid | nid | 4 | zcar_drupal.tn.nid | 1 | Using where |
| 1 | SIMPLE | u1 | eq_ref | PRIMARY | PRIMARY | 4 | zcar_drupal.n.uid | 1 | Using where; Using index |
| 1 | SIMPLE | ncs | eq_ref | PRIMARY | PRIMARY | 4 | zcar_drupal.n.nid | 1 | |
| 1 | SIMPLE | u2 | eq_ref | PRIMARY | PRIMARY | 4 | zcar_drupal.ncs.last_comment_uid | 1 | Using where |
+----+-------------+-------+--------+---------------------------------------------------+---------+---------+----------------------------------+-------+-----------------------------------------------------------+
The next is on my largest forum (take 5.6 seconds)
mysql> explain SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid 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 AND n.type='forum' AND tn.tid = 1 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1;+----+-------------+-------+--------+---------------------------------------------------+---------+---------+----------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------------------------+---------+---------+----------------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | tn | ref | PRIMARY,nid,tid | tid | 4 | const | 166004 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | n | ref | PRIMARY,node_type,status,uid,node_status_type,nid | nid | 4 | zcar_drupal.tn.nid | 1 | Using where |
| 1 | SIMPLE | u1 | eq_ref | PRIMARY | PRIMARY | 4 | zcar_drupal.n.uid | 1 | Using where; Using index |
| 1 | SIMPLE | ncs | eq_ref | PRIMARY | PRIMARY | 4 | zcar_drupal.n.nid | 1 | |
| 1 | SIMPLE | u2 | eq_ref | PRIMARY | PRIMARY | 4 | zcar_drupal.ncs.last_comment_uid | 1 | Using where |
+----+-------------+-------+--------+---------------------------------------------------+---------+---------+----------------------------------+--------+----------------------------------------------+
Notice that this query doesn't mention that it's using the index for the term_node join. The only thing that's changed here appears to tbe number of rows it is working with. Why would it use the index for one, and not the other?
Full query log to follow:
Executed 20 queries in 10086.29 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted.Page execution time was 10112.06 ms.
ms # where query
0.09 1 drupal_lookup_path SELECT COUNT(pid) FROM url_alias
0.14 1 module_list SELECT name, filename, throttle, bootstrap FROM system WHERE type = 'module' AND status = 1 ORDER BY weight ASC, filename ASC
0.37 1 cache_get SELECT data, created, headers, expire FROM cache WHERE cid = 'menu:1:en'
0.19 1 list_themes SELECT * FROM system WHERE type = 'theme'
0.11 1 taxonomy_get_tree SELECT t.tid, t.*, parent FROM term_data t INNER JOIN term_hierarchy h ON t.tid = h.tid WHERE t.vid = 1 ORDER BY weight, name
0.11 1 node_access_view_all_nodes SELECT COUNT(*) FROM node_access WHERE nid = 0 AND ((gid = 0 AND realm = 'all')) AND grant_view >= 1
0.09 1 forum_get_forums SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.nid = r.nid WHERE n.status = 1 AND n.type = 'forum' GROUP BY r.tid
596.23 1 forum_get_forums SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid 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 AND n.type='forum' AND tn.tid = 5 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1
5615.16 1 forum_get_forums SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid 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 AND n.type='forum' AND tn.tid = 1 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1
861.8 1 forum_get_forums SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid 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 AND n.type='forum' AND tn.tid = 2 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1
1664.62 1 forum_get_forums SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid 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 AND n.type='forum' AND tn.tid = 3 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1
1136.69 1 forum_get_forums SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS last_comment_name, ncs.last_comment_uid FROM node n INNER JOIN users u1 ON n.uid = u1.uid INNER JOIN term_node tn ON n.nid = tn.nid 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 AND n.type='forum' AND tn.tid = 4 ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1
1.07 1 taxonomy_get_vocabulary SELECT v.*, n.type FROM vocabulary v LEFT JOIN vocabulary_node_types n ON v.vid = n.vid WHERE v.vid = 1 ORDER BY v.weight, v.name
158.43 1 _forum_topics_unread SELECT COUNT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 5 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1172593911 AND h.nid IS NULL
13.08 1 _forum_topics_unread SELECT COUNT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 1 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1172593911 AND h.nid IS NULL
12.25 1 _forum_topics_unread SELECT COUNT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 2 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1172593911 AND h.nid IS NULL
12.81 1 _forum_topics_unread SELECT COUNT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 3 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1172593911 AND h.nid IS NULL
12.66 1 _forum_topics_unread SELECT COUNT(n.nid) FROM node n INNER JOIN term_node tn ON n.nid = tn.nid AND tn.tid = 4 LEFT JOIN history h ON n.nid = h.nid AND h.uid = 1 WHERE n.status = 1 AND n.type = 'forum' AND n.created > 1172593911 AND h.nid IS NULL
0.2 1 block_list SELECT * FROM blocks WHERE theme = 'bluemarine' AND status = 1 ORDER BY region, weight, module
0.21 1 system_region_list SELECT * FROM system WHERE type = 'theme' AND name = 'bluemarine'
may want to have a look at
may want to have a look at this handbook page on server optimization: http://drupal.org/node/2601
Optimize the database?
Maybe you could try optimizing the database. Assuming that you're using MySQL, it would be something like: mysqlcheck -o -a drupal_database_name
Steve
Query cache helps a lot
Forums have been slow since before 4.5 when I started on Drupal. Enabling query cache in mysql helps a lot. In my case it still wasn't enough, so I added another table called forum_statistics that caches the comment counts and most recent posts. Now forums are super fast for my 4.6 install (191396 comments). Unfortunately the 4.7 and 5.0 forums haven't changed much. I'm going to have to re-merge my hackish code with core when I upgrade.
I had just dropped my sns
I had just dropped my sns site become to a blog.
-------------------------------------
My drupal site: http://life.j0575.com,j0575