Large Article forum being extremely slow

ivp

Active member
Affected version
2.2.9
Opening Article forum having Preview display style consisting of around 50,000 articles is painfully slow.

If change Display style from Preview to Standard, it opens instantly.

Had to change "Thread list date limit" to 1 year to speed it up. So instead of 1636, pagination shows 16 pages.

Here is the slow query (8 seconds):
Code:
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`.*, `xf_post_FirstPost_7`.*, `xf_user_User_8`.*, `xf_user_option_Option_9`.*, `xf_user_profile_Profile_10`.*, `xf_user_privacy_Privacy_11`.*, `xf_permission_combination_PermissionCombination_12`.*, `xf_session_activity_Activity_13`.*, `xf_reaction_content_Reactions_14`.*, `xf_bookmark_item_Bookmarks_15`.*
            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` = '14')
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` = '14')
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` = '14')
LEFT JOIN `xf_post` AS `xf_post_FirstPost_7` ON (`xf_post_FirstPost_7`.`post_id` = `xf_thread`.`first_post_id`)
LEFT JOIN `xf_user` AS `xf_user_User_8` ON (`xf_user_User_8`.`user_id` = `xf_post_FirstPost_7`.`user_id`)
LEFT JOIN `xf_user_option` AS `xf_user_option_Option_9` ON (`xf_user_option_Option_9`.`user_id` = `xf_user_User_8`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_10` ON (`xf_user_profile_Profile_10`.`user_id` = `xf_user_User_8`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_11` ON (`xf_user_privacy_Privacy_11`.`user_id` = `xf_user_User_8`.`user_id`)
LEFT JOIN `xf_permission_combination` AS `xf_permission_combination_PermissionCombination_12` ON (`xf_permission_combination_PermissionCombination_12`.`permission_combination_id` = `xf_user_User_8`.`permission_combination_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_13` ON (`xf_session_activity_Activity_13`.`user_id` = `xf_user_User_8`.`user_id` AND `xf_session_activity_Activity_13`.`unique_key` = CONCAT(`xf_user_User_8`.`user_id`, ''))
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_14` ON (`xf_reaction_content_Reactions_14`.`content_type` = 'post' AND `xf_reaction_content_Reactions_14`.`content_id` = `xf_post_FirstPost_7`.`post_id` AND `xf_reaction_content_Reactions_14`.`reaction_user_id` = '14')
LEFT JOIN `xf_bookmark_item` AS `xf_bookmark_item_Bookmarks_15` ON (`xf_bookmark_item_Bookmarks_15`.`content_type` = 'post' AND `xf_bookmark_item_Bookmarks_15`.`content_id` = `xf_post_FirstPost_7`.`post_id` AND `xf_bookmark_item_Bookmarks_15`.`user_id` = '14')
            WHERE (`xf_thread`.`node_id` = 397) AND ((`xf_thread`.`discussion_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread`.`sticky` = 0)
            ORDER BY `xf_thread`.`last_post_date` DESC

It executes almost instantly when remove:
Code:
ORDER BY `xf_thread`.`last_post_date` DESC

Explain shows:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLExf_threadrangenode_id_last_post_date,node_id_sticky_state_last_post,node_id_sticky_state_vote_scorenode_id_sticky_state_last_post6NULL94091Using index condition; Using where; Using temporary; Using filesort
1SIMPLExf_deletion_log_DeletionLog_1eq_refPRIMARYPRIMARY31const,forum.xf_thread.thread_id1Using where
1SIMPLExf_user_User_2eq_refPRIMARYPRIMARY4forum.xf_thread.user_id1
1SIMPLExf_user_LastPoster_3eq_refPRIMARYPRIMARY4forum.xf_thread.last_post_user_id1
1SIMPLExf_thread_read_Read_4eq_refuser_id_thread_id,thread_iduser_id_thread_id8const,forum.xf_thread.thread_id1
1SIMPLExf_thread_user_post_UserPosts_5eq_refPRIMARY,user_idPRIMARY8forum.xf_thread.thread_id,const1
1SIMPLExf_thread_watch_Watch_6eq_refPRIMARY,thread_id_email_subscribePRIMARY8const,forum.xf_thread.thread_id1
1SIMPLExf_post_FirstPost_7eq_refPRIMARYPRIMARY4forum.xf_thread.first_post_id1
1SIMPLExf_user_User_8eq_refPRIMARYPRIMARY4forum.xf_post_FirstPost_7.user_id1Using where
1SIMPLExf_user_option_Option_9eq_refPRIMARYPRIMARY4forum.xf_user_User_8.user_id1Using where
1SIMPLExf_user_profile_Profile_10eq_refPRIMARYPRIMARY4forum.xf_user_User_8.user_id1Using where
1SIMPLExf_user_privacy_Privacy_11eq_refPRIMARYPRIMARY4forum.xf_user_User_8.user_id1Using where
1SIMPLExf_permission_combination_PermissionCombination_12eq_refPRIMARYPRIMARY4forum.xf_user_User_8.permission_combination_id1Using where
1SIMPLExf_session_activity_Activity_13eq_refPRIMARYPRIMARY22forum.xf_user_User_8.user_id,func1Using where
1SIMPLExf_reaction_content_Reactions_14eq_refcontent_type_id_user_id,content_type_id_reaction_date,reaction_user_id_reaction_date,reaction_user_idcontent_type_id_user_id35const,forum.xf_post_FirstPost_7.post_id,const1Using where
1SIMPLExf_bookmark_item_Bookmarks_15rangeuser_id_content_type_content_id,content_type_content_iduser_id_content_type_content_id31NULL1Using where; Using join buffer (flat, BNL join)
 
The bookmarks join has this;
Code:
Using where; Using join buffer (flat, BNL join)

The forum will likely be fast for guest users, while not for logged in users. I'ld also recommend checking with a non-admin user as there will be less joins and the chance of MySQL optimizer doing something dumb should be lower.

Similar to this bug report:

You can run this SQL without restarting mysql:
SQL:
set global join_cache_level=0;
And see what performance is like.

My free add-on 'Optimized List Queries' may also help if you set the better thread threshold to '0'
 
Modern versions of MariaDB can be really bad at joining from a very large table to a very small table.

XenForo will likely need to convert some of the joins into separate queries after the main query, ie bookmarks to migrate this in the future.
 
set global join_cache_level=0; raises server load, had to revert to default value. Note: having multiple databases on MariaDB, not XenForo only.

This issue should be probably addressed on XenForo side, since join_cache_level=2 is default on MariaDB 10.4+:
 
join_cache_level=0 with optimizer_use_condition_selectivity=1 should only improve performance for XenForo 2.x, but it does depend on what other usages of the database is being done.

Sadly XF doesn't make it easy without code edits to make it to configure those set commands per connection.
 
This started happening after migration from MariaDB 10.3 to 10.6.
This might go beyond MariaDB 10.4 experiences Xon had in the thread on my forums at https://community.centminmod.com/threads/anyone-using-mariadb-10-4-8-on-live-and-busy-site.18327/. If you check MariaDB Jira bug tracker on regressions/optimizer there's still quite a few for MariaDB 10.4 and above and it's one reason why I've stuck to MariaDB 10.3 for my usage and defaults for my Centmin Mod LEMP stack users for now. AFAIK, MariaDB 10.6, is meant to be their LTS version too with the longest support cycle.
 
The bookmarks join has this;
Code:
Using where; Using join buffer (flat, BNL join)

The forum will likely be fast for guest users, while not for logged in users. I'ld also recommend checking with a non-admin user as there will be less joins and the chance of MySQL optimizer doing something dumb should be lower.

Similar to this bug report:

You can run this SQL without restarting mysql:
SQL:
set global join_cache_level=0;
And see what performance is like.

My free add-on 'Optimized List Queries' may also help if you set the better thread threshold to '0'

Looking at the performance gains of @ivp, I'm in a similar situation, but I'm not using MariaDB. I'm on MySQL 5.7.38-log. Any recommendations? From what I read,

SQL:
set global join_cache_level=0;

is a MariaDB variable. Is there a similar solution for MySQL? The forum is quite big, and it's blazing fast for Guests, but very slow for Registered Users, so I think I'm in a similar situation. I'm also currently using Optimized List Queries but unfortunately it didn't help much, so any help would be GREATLY appreciated, thank you ♥
 
Last edited:
Top Bottom