Low performance with large topics

[benfromaix]

Hi everyone,

We have some issues with IPB over VERY large topics.

On our board, users have created some topics with more than 40k messages (2000 pages). Problem is : these topics are unable to be consulted.

Actually, the first pages are easy to access, but if you try to access the latest pages, loading-time takes dozens of seconds and causes high CPU on database server.

Here is one of the queries catched during the "long" load :

 SELECT p.*,m.member_id as mid,m.name,m.member_group_id,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title as member_title, m.warn_level, m.warn_lastwarn, m.members_display_name, m.members_seo_name, m.member_banned, m.has_gallery, m.has_blog, m.members_bitoptions,m.mgroup_others,pp.*,w.wl_id,pc.*,cca.*,ccb.cache_content as cache_content_sig, ccb.cache_updated as cache_updated_sig FROM ibf_posts p  LEFT JOIN ibf_members m ON ( m.member_id=p.author_id ) 
 LEFT JOIN ibf_profile_portal pp ON ( m.member_id=pp.pp_member_id ) 
 LEFT JOIN ibf_members_warn_logs w ON ( w.wl_content_app='forums' and w.wl_content_id1=p.pid ) 
 LEFT JOIN ibf_pfields_content pc ON ( pc.member_id=p.author_id ) 
 LEFT JOIN ibf_content_cache_posts cca ON ( cca.cache_content_id=p.pid ) 
 LEFT JOIN ibf_content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id )   WHERE p.topic_id=63064 AND  p.queued=0  ORDER BY p.pid asc LIMIT 42180,20  

The LIMIT clause seems to be the problem. In cases of "LIMIT 0,20", request goes within a few milliseconds.

Is there any way of optimizing this behaviour? Or just IPB is not compatible with very (too) large topics?

Thanks,

Best regards,

Gaëtan

[GreenLinks]

The issue seems to be related to your MySQL configuration. We have no such issues with large topics

[Ryan Ashbrook]

I would recommend upgrading - this is a known issue in previous versions of IP.Board and has since been fixed.

[benfromaix]

Unfortunately, I'm afraid our MySQL configuration is fine :

- Large buffers

- 16G innodb_buffer

- Large file limit

- Query cache enabled

- Large heap/tmp table size

We have a 24 cores Xeon E5 CPU + 32 GB memory

I would doubt our issue is coming from MySQL side.

Gaëtan

[maddog107_merged]

I would recommend upgrading - this is a known issue in previous versions of IP.Board and has since been fixed.

What he said, there is the actual code fix somewhere in this forum as I remember changing it by hand until it was officially rolled into the newer releases.

[Ryan Ashbrook]

It's in an archived KB Article: http://www.invisionpower.com/support/kb/_/large-topics-causing-high-load-r4

I would definitely recommend going the upgrade route instead, though.

[Grumpy]

- Large heap/tmp table size

IPB behaves worse with large tmp table sizes.

Oh and I have number of topics in 1000+ pages too, but I have no issues with them... Running latest version. Though, I don't remember having issues with them during 3.3 either.