Need help optimizing a setup with 10K forums.

[GX93117]

Hi, I am trying to accomplish something very specific. The project I'm working on requires about 10,000 forums. I know thats odd, but try to accept that it must be setup that way.

I've done multiple install on multiple hardware and software configuration and it seems the web server is the bottleneck, but I can't figure out why. I've done every optimization I could find in the admin control panel and moved to an NGINX webserver and installed memcached and APC on it and still found little improvement in the webserver performance. Even on a 4Ghz 8 core with 24 Gigs or ram it still takes about 10 seconds to show the forum index and to go into sub forums. The index only shows about 30 forums with the rest of the 10000+ hidden in different sub forums which are not shown due to disable subforums settings in the ACP to reduce html output. While requesting a page, TOP peaks up to 99% for a few seconds for httpd when on apache or php-fpm when on NGINX.

I'm pretty sure its not a DB issues. I see no load on the separate DB server and the debug queries all run very fast. I don't think its a webserver buffer issue. I don't output all the forums at once so the html is relatively little. I did have to increase the php memory limit to 256M so whatever it's doing requires alot of memory I've turned off every feature from reputation, signatures, friends, users online, minify js & css, etc. I even turned on performance mode.

What could be loading the webservers so much? Is it something to do with the item marking? Thousands of item marking keys show up in the debug. Is there a way to completely turn off item marking? I only see options to switch it between a database system and a cookie method. Is item marking completely turned off in performance mode? How can I completely turn it off without going into performance mode. I don't require marking new items in what I'm doing. I haven't done any conclusive test but once its starts spitting out the html its fast, its just it takes a while before it does anything. It feels like its crunching something in the background before it does anything. I've been at this for weeks and it looks like i have modify to the ipb core for this use case. Any idea on what else to optimize and look into. Thanks.


Heres my debug output from forum index.

Time Now: Apr 05 2012 07:55 PM Last Visit: Today, 07:23 PM Exec. Time: 9.7550 Load: -- Queries: 7 queries Gzip: GZIP Enabled

PSDebug Messages
Updating MEMBER session: 45a4819eadd1ed327bf97c20b146db59
Gateway file confirmed: html
Skin set not found, setting default. Using set #1
Setting current module to: forums and current section to:
Item Marking Key Created! 400d1e9cf505ab403e3c65d9bacd1a9f
Item Marking Key returned! 1a2d135fa26e09b2982080e299ed58e1
Plus 10000 more Item Marking Keys but I removed them

IPSMember Cache Actions
ADDED: Member ID: 1 with tables pfields_content,profile_portal,groups key (df28af09ab8ba146fbf4f722a6e192c4)
ADDED: Member ID: 2 with tables profile_portal key (18d3a8f2b1b55e71cf3f6923844645a2)

(35) Included Files
/www/test/index.php
/www/test/initdata.php
/www/test/admin/sources/base/ipsRegistry.php
/www/test/admin/sources/base/ipsController.php
/www/test/admin/sources/base/core.php
/www/test/admin/sources/base/ipsMember.php
/www/test/admin/extensions/coreVariables.php
/www/test/conf_global.php
/www/test/ips_kernel/classDbMysql.php
/www/test/ips_kernel/classDb.php
/www/test/ips_kernel/classDbMysqliClient.php
/www/test/cache/furlCache.php
/www/test/admin/applications/forums/extensions/coreVariables.php
/www/test/admin/sources/classes/session/publicSessions.php
/www/test/admin/applications/forums/extensions/coreExtensions.php
/www/test/admin/sources/classes/class_localization.php
/www/test/admin/sources/classes/class_public_permissions.php
/www/test/admin/applications/forums/app_class_forums.php
/www/test/admin/applications/forums/sources/classes/forums/class_forums.php
/www/test/admin/sources/classes/itemmarking/classItemMarking.php
/www/test/admin/sources/classes/output/publicOutput.php
/www/test/admin/sources/classes/output/formats/coreOutput.php
/www/test/admin/sources/classes/output/formats/html/htmlOutput.php
/www/test/cache/lang_cache/1/core_public_global.php
/www/test/admin/applications/forums/modules_public/forums/defaultSection.php
/www/test/admin/applications/forums/modules_public/forums/boards.php
/www/test/cache/lang_cache/1/forums_public_boards.php
/www/test/admin/sources/classes/class_reputation_cache.php
/www/test/cache/skin_cache/cacheid_1/skin_global.php
/www/test/cache/skin_cache/cacheid_1/skin_boards.php
/www/test/admin/sources/classes/ads.php
/www/test/admin/sources/classes/member/status.php
/www/test/hooks/boardIndexSideBarAdCode_f41ab79c776b845f5e313dd06a631cb3.php
/www/test/hooks/boardIndexRecentTopics_c553aa1c81c1859280a5bd54fad70ed8.php
/www/test/cache/lang_cache/1/forums_public_topic.php

Loaded Caches
TOTAL: 0bytes

Loaded Classes In ipsRegistry::getClass()

FORM and GET Input
module =
section =
request_method = get
f = 0

SKIN, MEMBER & TASK Info
set_id = 1
set_name = IP.Board
set_key = default
set_parent_id = 0
set_parent_array = a:0:{}
set_child_array = a:0:{}
set_permissions = *
set_is_default = 1
set_author_name = Invision Power Services, Inc
set_author_url =
set_image_dir = master
set_emo_dir = default
set_css_inline = 1
set_css_groups = a:15:{s:3:"1.1";a:2:{s:9:"css_group";s:15:"calendar_select";s:12:"css_position";s:1:"1";}s:3:"1.2";a:2:{s:9:"css_group";...
set_added = 1331132599
set_updated = 1333600254
set_output_format = html
set_locked_uagent =
set_hide_from_list = 0
set_minify = 0
set_master_key = root
set_order = 0
set_by_skin_gen = 0
set_skin_gen_data =
_userAgents =
_youCanUse = 1
_gatewayExclude =
_skincacheid = 1
_csscacheid = css_1
Next task = Today, 06:07 PM
Time now = Today, 07:29 PM
Timestamp Now = 1333650574
MEMBER: last_visit: 1333650191 / Today, 07:23 PM
MEMBER: uagent_key: chrome
MEMBER: uagent_type: browser
MEMBER: uagent_version: 18

Loaded PHP Templates
skin_global, skin_boards
skin_global_1, skin_boards_1
core_public_global, forums_public_boards, forums_public_topic

Queries Used

SELECT * FROM cache_store WHERE cs_key IN ( 'systemvars','login_methods','vnums','app_cache','navigation_tabs','module_cache','hooks','useragents','useragentgroups','skinsets','outputformats','skin_remap','group_cache','settings','lang_data','banfilters','stats','badwords','bbcode','mediatag','profilefields','rss_output_cache','rss_export','attachtypes','multimod','moderators','announcements','report_cache','report_plugins','ranks','reputation_levels','chatting','birthdays','calendar_events','calendars','support_staff','support_departments','support_statuses','donation_goals','blog_stats','blogmods','idm_cats','idm_mods' )
SELECT * FROM sessions WHERE id='45a4819eadd1ed327bf97c20b146db59'
SELECT m.*, m.member_id as my_member_id,p.*,pp.*,g.*,ccb.cache_content FROM members m LEFT JOIN pfields_content p ON ( p.member_id=m.member_id ) LEFT JOIN profile_portal pp ON ( pp.pp_member_id=m.member_id ) LEFT JOIN groups g ON ( g.g_id=m.member_group_id ) LEFT JOIN content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id ) WHERE m.member_id=1
SELECT * FROM core_item_markers WHERE item_member_id=1
SELECT f.*,p.*,itemmarking.* FROM forums f LEFT JOIN permission_index p ON ( p.perm_type='forum' AND p.app='forums' AND p.perm_type_id=f.id ) LEFT JOIN core_item_markers itemmarking ON ( itemmarking.item_member_id=1 AND itemmarking.item_app='forums' AND itemmarking.item_app_key_1=f.id )
SELECT * FROM skin_cache WHERE cache_set_id=1 AND cache_type IN ('css', 'replacements')
SELECT m.*, m.member_id as my_member_id,pp.*,ccb.cache_content FROM members m LEFT JOIN profile_portal pp ON ( pp.pp_member_id=m.member_id ) LEFT JOIN content_cache_sigs ccb ON ( ccb.cache_content_id=m.member_id ) WHERE m.member_id IN (2)
SELECT t.tid, t.title as topic_title, t.title_seo, t.start_date, t.starter_id, t.starter_name, t.moved_to, t.views, t.posts,m.*,pp.* FROM topics t LEFT JOIN members m ON ( m.member_id=t.starter_id ) LEFT JOIN profile_portal pp ON ( m.member_id=pp.pp_member_id ) WHERE t.tid IN (2,1)
SHUTDOWN: UPDATE members SET login_anonymous='0&1',last_activity=1333650562 WHERE member_id=1

[stoo2000]

Run this in MySQL CLI / PhpMyAdmin

EXPLAIN SELECT f.*,p.*,itemmarking.* FROM forums f LEFT JOIN permission_index p ON ( p.perm_type='forum' AND p.app='forums' AND p.perm_type_id=f.id ) LEFT JOIN core_item_markers itemmarking ON ( itemmarking.item_member_id=1 AND itemmarking.item_app='forums' AND itemmarking.item_app_key_1=f.id )

See what that comes back with.

[GX93117]

Thanks, this is what i got. Btw, right now I have about 7K forums since I'm tinkering with it.

The normal SELECT returns this:
Showing rows 0 - 29 ( 7,455 total, Query took 0.0146 sec)


The EXPLAIN SELECT returns this:
Your SQL query has been executed successfully

id | select_type | table       | type | possible_keys        | key          | key_len | ref                               |  rows  | Extra

1  | SIMPLE      | f           | ALL  | NULL                 | NULL         | NULL    | NULL                              |  7455  |

1  | SIMPLE      | p           | ref  | perm_index,perm_type | perm_index   | 38      | const,ipb_default_test.f.id       |  1     |

1  | SIMPLE      | itemmarking | ref  | marker_index         | marker_index | 265     | const,const,ipb_default_test.f.id |  2     |

[Luis Manson]

not sure about this, but what if you load just a categry?
im really curious about what could you be doing to need so many forums!

[GX93117]

not sure about this, but what if you load just a categry?

im really curious about what could you be doing to need so many forums!

I can't really say, but for example if I were to create a message board for every college and university i would need a few thousand.

[Grumpy]

I'm actually noticing this issue myself. My forum count is bit over 4k now stuff are getting slower. I'm running probably better spec than above, so I still load in like <2 sec pretty easily...

My current foreseeable solution is to reorganize my forum to reduce forum count. But, I'd honestly prefer not to.

Like the OP, I get couple thousand item markings when debug is turned on which is likely the cause of the slow downs (in my guess).

I get pretty much same result as the OP from the explain except lower numbers. And I guess the last row is different because this is a live account.

1 SIMPLE f ALL NULL NULL NULL NULL 4263

1 SIMPLE p ref perm_index,perm_type perm_index 38 const,<snipped name>.f.id 1

1 SIMPLE itemmarking ref marker_index marker_index 261 const,const 2911

Item marker would also grow so much that it often runs to the mysql query limit (which I've raised to like 10MB).

But, I'm still running 3.2, so I can't say how it will perform in 3.3 which is supposed to have revamped markers.

[Rhett]

What disk configuration are you running?

Which Raid and what spec drives? if you want to speed up reading of the data (which is what you need here), your going to need a fast setup,

[GX93117]

What disk configuration are you running?

Which Raid and what spec drives? if you want to speed up reading of the data (which is what you need here), your going to need a fast setup,

Hi Rhett, I haven't deployed it on a fast raid drive yet since I'm still in development. So far I tried it on my local machine which has a 10K rpm WD velociraptor drive. All my other test have been on amazon using their EBS (elastic block storage) as I was trying different server configs. But I didn't consider the disk configuration to be the bottleneck with a fresh install and a bunch of empty forums. Can you shed some light on why it might be disk related? Trying to understand what is so intensive on a setup with no content but many forums. Memory swapping to disk already?

Anyhow, I haven't ruled out if item marking is the cause, but I'm also wondering if it could be permissions related. I see alot of permissions checks going on...maybe checking each forum permission group and display status is what eats up the cpu...?

[Grumpy]

I have it deployed on a raid 1 intel ssd servers. Whatever it is doing due to high forum count, but communication between php and mysql is quite crazy... see this

ifstat

       eth0                eth1

 KB/s in  KB/s out   KB/s in  KB/s out

  485.46   2605.09  22506.88   6135.54

  485.72   3286.57  29723.96   4312.28

  463.50   3957.59  29106.33   5680.18

  405.18   3473.15  29981.20   3612.52

  495.55   4630.61  20046.35   2930.90

My eth1 is local connection (php <-> mysql <-> memcached <-> sphinx communication) and this is from one of the web servers. eth0 is my external (Internet).

I'll probably delve into this more myself at some other point when I'm not as busy.

[GX93117]

I think I found the cause. There is a function called getForumList() in /admin/applications/forums/sources/classes/forums/class_forums.php. It does a sql JOIN that gets the entire content of the forums and permission_index table so it returns a very large recordset when you have thousands of forums. It does this even though you are not showing every forum on a page. However the SQL runs pretty fast, the problem it loads the entire record set into an array and does a bunch of things I don't understand (my php not that great). This is consistant with why I am seeing only a large load on webserver and why I had to raise the php memory limit past 128MB.

Playing with the SQL by changing the select * to just grab fewer columns does speed it up quite a bit. I think this is mainly because the php has less data to deal with, not because SQL is quicker by having to returning less data. I also commented out the some of the other things in that function such as the sorting, seo updates and something about bitwise (whats that for?) and it only improved the speed by a small amount.

All I can tell is when the function loads a smaller number of forums, the page is extremely fast and the memory usage is very small. It seems to be related to this line:
$forums_list[ $fr['id'] ] = $fr;

[Luis Manson]

maybe you should do a bug report on this, if we all can have an improvement

[GX93117]

I have a SOLUTION!!! Major speed increase for what I was doing.

What I did was modify the get getForumList() function to only load the forum id's relavent to the page the user is looking at versus originally ALWAYS getting ALL the forums in the system no matter where you are. Basically just modify the SQL query for the different cases:

if on main index...select forums id on main index.
if on category/subf-forum index.....select category/subf-forum ids.
if viewing an individual forum.....just select that forum id.
if in admin control panel....select them all as originally done.

So far it looks like everything works and very fast and page memory size reduced significantly. Haven't thoroughly tested it yet so not sure what the consequences of not loading all the forums are...Just glad this may be the solution!

[Luis Manson]

thanks for keeping this updated, im no good at SQL would you share the original and new query your site would run to compare on my site if i would have any noticeable improvement? i dont have those many forums, but who knows...

[Nevo]

You have the same problem i do with one of my IP.Board's. No matter how many resources the server hosting it has, the loadspeed remains slow. As proof, i replaced the forums table with another one and the loadspeed was dramatically different! This narrowed it down to the amount of forums so I converted the database to other Bulletin Content Management Systems and the result was the same with so many thousands of forums. The point is, you can't have so many forums otherwise your board index and forums application will be slow.

[Grumpy]

I have a SOLUTION!!! Major speed increase for what I was doing.

What I did was modify the get[color=#b22222] getForumList()[/color] function to only load the forum id's relavent to the page the user is looking at versus originally ALWAYS getting ALL the forums in the system no matter where you are. Basically just modify the SQL query for the different cases:

if on main index...select forums id on main index.

if on category/subf-forum index.....select category/subf-forum ids.

if viewing an individual forum.....just select that forum id.

if in admin control panel....select them all as originally done.

So far it looks like everything works and very fast and page memory size reduced significantly. Haven't thoroughly tested it yet so not sure what the consequences of not loading all the forums are...Just glad this may be the solution!

I should try this out too when I have some time. I'll post some feedback as well.

[Grumpy]

Woot~~

Okay, I just tried this.

I have modified the public function getForumList() so that only its children, parent and itself of the current forum is loaded by creating additional queries and requirements.
With >4k forums, I'm seeing drastic results.

forenote on stats: I'm running on multiple machines and right now is one of least traffic time. So, to start with is quite low. Additionally, the IP.Content (which is not affected by this) is majority of my traffic. NOT my forums.

Previously:
Load avg: 0.91
Index page load time: < 2s

New:
Load avg: 0.56
Index page load time: < 500ms

I'm pretty sure with low forum count, my changes will perform lot worse.

And the code...

-- removed -- *had bug

I would only recommend this change to anyone with over several hundred forums. Otherwise, you're just increasing query count significantly.
The code can be made more efficient... but it's the first version. :P

Update: Seems there was a bug with it... removed the code...