benfromaix Posted January 27, 2014 Share Posted January 27, 2014 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 Link to comment Share on other sites More sharing options...
GreenLinks Posted January 27, 2014 Share Posted January 27, 2014 The issue seems to be related to your MySQL configuration. We have no such issues with large topics Link to comment Share on other sites More sharing options...
Ryan Ashbrook Posted January 27, 2014 Share Posted January 27, 2014 I would recommend upgrading - this is a known issue in previous versions of IP.Board and has since been fixed. Link to comment Share on other sites More sharing options...
benfromaix Posted January 27, 2014 Author Share Posted January 27, 2014 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 Link to comment Share on other sites More sharing options...
maddog107_merged Posted January 27, 2014 Share Posted January 27, 2014 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. Link to comment Share on other sites More sharing options...
Ryan Ashbrook Posted January 27, 2014 Share Posted January 27, 2014 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. Link to comment Share on other sites More sharing options...
Grumpy Posted January 28, 2014 Share Posted January 28, 2014 - 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.