Hello everybody.

I am aware of the fact that the regular Drupal forum has speed problems as I've read some old posts from no later than 2009, but I’m unable to find more recent info.

I have so far successfully migrated a decently sized vBulletin site over to Drupal 7 using vBulletin to Drupal (works well). I enabled the Advanced Forums module because the users are used to vbulletin and AF kind of imitates that forum set up.

Anyway, I’m developing on my localhost os x 10.9.2 with mysql 5.6.10 and php 5.4.25. I’ve turned off as many modules as I can, leaving those that AF depend on. Also testing here with a basic theme Bartik.

With Advanced Forums enabled the forums the page load always takes a very very long time. It never loads a cached version unless using the browser’s back button.

So with Advance Forums module enabled:

latency when not logged in is around 7 seconds
latency when logged in can be from 8.5 to 10.5 seconds.

Disabled AF, and just the regular Drupal forum module:

latency when not logged in is around 2 seconds
latency when logged in can be from 2.7 to 3 seconds (also not great)

There’s around 35 forum containers, and within any of them anywhere from 10 - 50 topics. This is a lot of stuff for Drupal to sort through on that first page. Altogether I imported over 56000 forum topics, most of them active.

I don’t need it to load a fresh version every time the ‘Forums’ link is clicked, but it shouldn’t even take that long in the first place

I’m wondering if anyone out there has any idea how I can optimize my install for use with Advanced Forum?? Otherwise, a 10 second load will force me to switch to the regular Forums.

thanks

Comments

Samlet9908’s picture

Samlet9908 CreditAttribution: Samlet9908 commented
Title: high latency on large forums » high latency on large advanced forum
podarok’s picture

podarok
🇺🇦 he/him/his
Primary language Ukrainian
Location Ukraine
CreditAttribution: podarok commented
Status: Active » Closed (duplicate)

#2219377: Calculating post counts 20+ seconds on large sites

Samlet9908’s picture

Samlet9908 CreditAttribution: Samlet9908 commented

Ok finally checking back on this after couple of days. Thanks for reply. I've tried all the usual techniques: Authcache/Memcache (which doesn't solve the problem of initial page load), playing with different engine types, using Mysqltuner, logging slow queries. It all comes down to that query in advanced_forum_forum_load that joins so many tables, sifting through a gagillion rows.
I see your patch works on that function so will try it and get back to you.

Samlet9908’s picture

Samlet9908 CreditAttribution: Samlet9908 commented

Here are the results of successful testing. I'm developing on a localhost (Mac OS 10.9) using MySQL 5.6 and php 5.4.

First install these two patches, though the second one is really the one that makes a difference for me:

https://drupal.org/node/2219377
https://drupal.org/node/2222861

I had two installs of Drupal with the same exact forum topics/posts, one using Authcache/Memcache and the other not. I tested these patches on both but used the non-Authcache version for measuring more accurate load times.

The first patch didn’t help ultimately, but the second patch kept query times consistently reasonable.

These patches don’t handle problems with the Active Forums load times. Active Forums is handled by Views, so the main culprit there were queries: views_plugin_pager::execute_count_query and views_plugin_query_default::execute. The latter is the worst offender.

You can relieve this a bit by editing this view in Structure->Views, going to Advanced->Other->Query Settings, and de-selecting ‘Distinct’ . Also the database engine changes I describe below helped with making the views queries work better (or at all).

Both installs have different combinations of InnoDB and MyISAM. In the install with 95% InnoDB tables I had to convert taxonomy_index back to MyISAM.

In the install that is about 50/50 both engine types, the taxonomy_index could be InnoDB but taxonomy_term_hierarchy needed to remain MyISAM.

These discrepancies are probably due to my my.cnf settings and just the way the different tables behave when being joined in a query.

I will update if there's a change in the query behaviors.

Samlet9908’s picture

Samlet9908 CreditAttribution: Samlet9908 commented

I forgot to add that I did the following, which may have helped:

Removed DISTINCT from the query on line 149 on core-overrides in the advanced_forum_forum_load function so it looks like this:
$query->addExpression('COUNT(f.nid)', 'topic_count');

It may have helped to rebuild the comment_node_statistics table (though ultimately not much), which you can do with a devel function in the function of core-overrides.inc
devel_rebuild_node_comment_statistics();