Stuck on improving page load for large number of threads

[John917]

One of my nodes have 200,000+ threads and page loads are taking more than 5 seconds sometimes.


Debug shows runtime of 4.5 seconds for xf_thread.

I do have a large dedicated server and I feel like I can fix the load time with proper mysql tuning.
CPU : 4c/8t - 3.8 GHz/4.2 GHz
Memory : 64 GB
SSD storage

Any advice on where I should start? Anyone with large number of threads can share their mysql config?

Also posting something within a thread takes just as long.

 

[MySiteGuy]

Please share the query that is doing this. The number of threads is usually not a cause. I've worked on boards with over a million and they were snappy. Huge threads with thousands of replies each, however, can take their toll but it's not the only possible cause.

 

[John917]

Please share the query that is doing this. The number of threads is usually not a cause. I've worked on boards with over a million and they were snappy. Huge threads with thousands of replies each, however, can take their toll but it's not the only possible cause.

Thanks for that. Can you tell me exactly how much of the log you will need? The page is quite long but I can get you the full log for xf_threads. Will get this to you when I get to my desk.

 

[MySiteGuy]

You can send just the one where the query takes over 4 seconds, less there are others there taking a bit of time.

 

[John917]

Here you go @MySiteGuy. Appreciate the help.

1. SELECT xf_thread., xf_deletion_log_DeletionLog_1., xf_user_User_2., xf_user_LastPoster_3., xf_thread_read_Read_4., xf_thread_user_post_UserPosts_5., xf_thread_watch_Watch_6.*
   FROM xf_thread
   LEFT JOIN xf_deletion_log AS xf_deletion_log_DeletionLog_1 ON (xf_deletion_log_DeletionLog_1.content_type = 'thread' AND xf_deletion_log_DeletionLog_1.content_id = xf_thread.thread_id)
   LEFT JOIN xf_user AS xf_user_User_2 ON (xf_user_User_2.user_id = xf_thread.user_id)
   LEFT JOIN xf_user AS xf_user_LastPoster_3 ON (xf_user_LastPoster_3.user_id = xf_thread.last_post_user_id)
   LEFT JOIN xf_thread_read AS xf_thread_read_Read_4 ON (xf_thread_read_Read_4.thread_id = xf_thread.thread_id AND xf_thread_read_Read_4.user_id = '1')
   LEFT JOIN xf_thread_user_post AS xf_thread_user_post_UserPosts_5 ON (xf_thread_user_post_UserPosts_5.thread_id = xf_thread.thread_id AND xf_thread_user_post_UserPosts_5.user_id = '1')
   LEFT JOIN xf_thread_watch AS xf_thread_watch_Watch_6 ON (xf_thread_watch_Watch_6.thread_id = xf_thread.thread_id AND xf_thread_watch_Watch_6.user_id = '1')
   WHERE (xf_thread.node_id = 122) AND ((xf_thread.discussion_state IN ('visible', 'deleted', 'moderated'))) AND (xf_thread.sticky = 0)
   ORDER BY xf_thread.last_post_date DESC
   
   LIMIT 30 OFFSET 502380
   Run Time: 4.359199
   

   Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
   SIMPLE	xf_thread	ref	node_id_last_post_date,node_id_sticky_state_last_post,node_id_sticky_state_vote_score	node_id_last_post_date	4	const	254842	Using where
   SIMPLE	xf_deletion_log_DeletionLog_1	eq_ref	PRIMARY	PRIMARY	31	const,site.xf_thread.thread_id	1	Using where
   SIMPLE	xf_user_User_2	eq_ref	PRIMARY	PRIMARY	4	site.xf_thread.user_id	1
   SIMPLE	xf_user_LastPoster_3	eq_ref	PRIMARY	PRIMARY	4	site.xf_thread.last_post_user_id	1
   SIMPLE	xf_thread_read_Read_4	eq_ref	user_id_thread_id,thread_id	user_id_thread_id	8	const,site.xf_thread.thread_id	1
   SIMPLE	xf_thread_user_post_UserPosts_5	eq_ref	PRIMARY,user_id	PRIMARY	8	site.xf_thread.thread_id,const	1
   SIMPLE	xf_thread_watch_Watch_6	eq_ref	PRIMARY,thread_id_email_subscribe	PRIMARY	8	const,site.xf_thread.thread_id	1

   1. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
   2. XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1392
   3. XF\Mvc\Entity\Finder->fetch() in src/XF/Pub/Controller/Forum.php at line 249
   4. XF\Pub\Controller\Forum->actionForum() in src/addons/Scandals/PrefixSearch/XF/Pub/Controller/Forum.php at line 100
   5. Scandals\PrefixSearch\XF\Pub\Controller\Forum->actionForum() in src/addons/SV/StandardLib/XF/Pub/Controller/Forum.php at line 21
   6. SV\StandardLib\XF\Pub\Controller\Forum->actionForum() in src/XF/Mvc/Dispatcher.php at line 352
   7. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 259
   8. XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 115
   9. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 57
   10. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2353
   11. XF\App->run() in src/XF.php at line 524
   12. XF::runApp() in index.php at line 20

[LIST=1]
[*]SELECT `xf_thread`.*, `xf_deletion_log_DeletionLog_1`.*, `xf_user_User_2`.*, `xf_user_LastPoster_3`.*, `xf_thread_read_Read_4`.*, `xf_thread_user_post_UserPosts_5`.*, `xf_thread_watch_Watch_6`.*
FROM `xf_thread`
LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_1` ON (`xf_deletion_log_DeletionLog_1`.`content_type` = 'thread' AND `xf_deletion_log_DeletionLog_1`.`content_id` = `xf_thread`.`thread_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_LastPoster_3` ON (`xf_user_LastPoster_3`.`user_id` = `xf_thread`.`last_post_user_id`)
LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_4` ON (`xf_thread_read_Read_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_4`.`user_id` = '1')
LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_5` ON (`xf_thread_user_post_UserPosts_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_5`.`user_id` = '1')
LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_6` ON (`xf_thread_watch_Watch_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_6`.`user_id` = '1')
WHERE (`xf_thread`.`node_id` = 122) AND ((`xf_thread`.`discussion_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_thread`.`sticky` = 0)
ORDER BY `xf_thread`.`last_post_date` DESC

LIMIT 30 OFFSET 502380
Run Time: 4.359199
[TABLE]
[TR]
[TH]Select Type[/TH]
[TH]Table[/TH]
[TH]Type[/TH]
[TH]Possible Keys[/TH]
[TH]Key[/TH]
[TH]Key Len[/TH]
[TH]Ref[/TH]
[TH]Rows[/TH]
[TH]Extra[/TH]
[/TR]
[TR]
[TD]SIMPLE[/TD]
[TD]xf_thread[/TD]
[TD]ref[/TD]
[TD]node_id_last_post_date,node_id_sticky_state_last_post,node_id_sticky_state_vote_score[/TD]
[TD]node_id_last_post_date[/TD]
[TD]4[/TD]
[TD]const[/TD]
[TD]254842[/TD]
[TD]Using where[/TD]
[/TR]
[TR]
[TD]SIMPLE[/TD]
[TD]xf_deletion_log_DeletionLog_1[/TD]
[TD]eq_ref[/TD]
[TD]PRIMARY[/TD]
[TD]PRIMARY[/TD]
[TD]31[/TD]
[TD]const,site.xf_thread.thread_id[/TD]
[TD]1[/TD]
[TD]Using where[/TD]
[/TR]
[TR]
[TD]SIMPLE[/TD]
[TD]xf_user_User_2[/TD]
[TD]eq_ref[/TD]
[TD]PRIMARY[/TD]
[TD]PRIMARY[/TD]
[TD]4[/TD]
[TD]site.xf_thread.user_id[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIMPLE[/TD]
[TD]xf_user_LastPoster_3[/TD]
[TD]eq_ref[/TD]
[TD]PRIMARY[/TD]
[TD]PRIMARY[/TD]
[TD]4[/TD]
[TD]site.xf_thread.last_post_user_id[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIMPLE[/TD]
[TD]xf_thread_read_Read_4[/TD]
[TD]eq_ref[/TD]
[TD]user_id_thread_id,thread_id[/TD]
[TD]user_id_thread_id[/TD]
[TD]8[/TD]
[TD]const,site.xf_thread.thread_id[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIMPLE[/TD]
[TD]xf_thread_user_post_UserPosts_5[/TD]
[TD]eq_ref[/TD]
[TD]PRIMARY,user_id[/TD]
[TD]PRIMARY[/TD]
[TD]8[/TD]
[TD]site.xf_thread.thread_id,const[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIMPLE[/TD]
[TD]xf_thread_watch_Watch_6[/TD]
[TD]eq_ref[/TD]
[TD]PRIMARY,thread_id_email_subscribe[/TD]
[TD]PRIMARY[/TD]
[TD]8[/TD]
[TD]const,site.xf_thread.thread_id[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[/TABLE]
[LIST=1]
[*]XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
[*]XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1392
[*]XF\Mvc\Entity\Finder->fetch() in src/XF/Pub/Controller/Forum.php at line 249
[*]XF\Pub\Controller\Forum->actionForum() in src/addons/Scandals/PrefixSearch/XF/Pub/Controller/Forum.php at line 100
[*]Scandals\PrefixSearch\XF\Pub\Controller\Forum->actionForum() in src/addons/SV/StandardLib/XF/Pub/Controller/Forum.php at line 21
[*]SV\StandardLib\XF\Pub\Controller\Forum->actionForum() in src/XF/Mvc/Dispatcher.php at line 352
[*]XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 259
[*]XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 115
[*]XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 57
[*]XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2353
[*]XF\App->run() in src/XF.php at line 524
[*]XF::runApp() in index.php at line 20
[/LIST]
[/LIST]

 

[MySiteGuy]

I'm going to take this to the conversation system in a few hours after I return from a dinner date. I didn't want you to think in the meantime I was ignoring you!

 

[John917]

I'm going to take this to the conversation system in a few hours after I return from a dinner date. I didn't want you to think in the meantime I was ignoring you!

Hey appreciate that! Dinner dates important! Haha no worries.