Fixed Widget query causing table locks

Jake B.

Jake B.

Well-known member
Affected version
2.1.11
I'm fairly certain this query is coming from a new posts widget, but not 100% certain. Looks like there is no node_id index on xf_thread, and there's also an issue with this portion: portion

Code: 
(`xf_forum_Forum_2`.`find_new` = 1)


This is the full query, with most of the node IDs excluded since it was quite a long list, and makes reading through the query quite a bit more difficult:

Code: 
SELECT `xf_thread`.*, `xf_poll_Poll_1`.*, `xf_forum_Forum_2`.*, `xf_node_Node_3`.*, `xf_permission_cache_content_Permissions_4`.*, `xf_user_User_5`.*, `xf_user_profile_Profile_6`.*, `xf_user_privacy_Privacy_7`.*, `xf_user_option_Option_8`.*, `xf_session_activity_Activity_9`.*, `xf_admin_Admin_10`.*, `xf_thread_user_post_UserPosts_11`.*, `xf_thread_watch_Watch_12`.*, `xf_thread_reply_ban_ReplyBans_13`.*, `xf_post_FirstPost_14`.*
                        FROM `xf_thread`
                        LEFT JOIN `xf_poll` AS `xf_poll_Poll_1` ON (`xf_poll_Poll_1`.`content_type` = 'thread' AND `xf_poll_Poll_1`.`content_id` = `xf_thread`.`thread_id`)
LEFT JOIN `xf_forum` AS `xf_forum_Forum_2` ON (`xf_forum_Forum_2`.`node_id` = `xf_thread`.`node_id`)
LEFT JOIN `xf_node` AS `xf_node_Node_3` ON (`xf_node_Node_3`.`node_id` = `xf_forum_Forum_2`.`node_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_4` ON (`xf_permission_cache_content_Permissions_4`.`content_type` = 'node' AND `xf_permission_cache_content_Permissions_4`.`content_id` = `xf_node_Node_3`.`node_id` AND `xf_permission_cache_content_Permissions_4`.`permission_combination_id` = '42')
LEFT JOIN `xf_user` AS `xf_user_User_5` ON (`xf_user_User_5`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_6` ON (`xf_user_profile_Profile_6`.`user_id` = `xf_user_User_5`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_7` ON (`xf_user_privacy_Privacy_7`.`user_id` = `xf_user_User_5`.`user_id`)
LEFT JOIN `xf_user_option` AS `xf_user_option_Option_8` ON (`xf_user_option_Option_8`.`user_id` = `xf_user_User_5`.`user_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_9` ON (`xf_session_activity_Activity_9`.`user_id` = `xf_user_User_5`.`user_id` AND `xf_session_activity_Activity_9`.`unique_key` = CONCAT(`xf_user_User_5`.`user_id`, ''))
LEFT JOIN `xf_admin` AS `xf_admin_Admin_10` ON (`xf_admin_Admin_10`.`user_id` = `xf_user_User_5`.`user_id`)
LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_11` ON (`xf_thread_user_post_UserPosts_11`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_11`.`user_id` = '450224')
LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_12` ON (`xf_thread_watch_Watch_12`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_12`.`user_id` = '450224')
LEFT JOIN `xf_thread_reply_ban` AS `xf_thread_reply_ban_ReplyBans_13` ON (`xf_thread_reply_ban_ReplyBans_13`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_reply_ban_ReplyBans_13`.`user_id` = '450224')
LEFT JOIN `xf_post` AS `xf_post_FirstPost_14` ON (`xf_post_FirstPost_14`.`post_id` = `xf_thread`.`first_post_id`)
                        WHERE (`xf_thread`.`discussion_type` != 'redirect') AND (`xf_forum_Forum_2`.`find_new` = 1) AND (`xf_thread`.`node_id` IN (11249, 11251, 11253, 11255, 11257, 11259, 11263, 11265, 11267, 11269, 11271, 11273, 11275)) AND (`xf_thread`.`discussion_state` = 'visible')
                        ORDER BY `xf_thread`.`post_date` DESC
LIMIT 20

Explain from the query:

Code: 
mysql> explain SELECT `xf_thread`.*, `xf_poll_Poll_1`.*, `xf_forum_Forum_2`.*, `xf_node_Node_3`.*, `xf_permission_cache_content_Permissions_4`.*, `xf_user_User_5`.*, `xf_user_profile_Profile_6`.*, `xf_user_privacy_Privacy_7`.*, `xf_user_option_Option_8`.*, `xf_session_activity_Activity_9`.*, `xf_admin_Admin_10`.*, `xf_thread_user_post_UserPosts_11`.*, `xf_thread_watch_Watch_12`.*, `xf_thread_reply_ban_ReplyBans_13`.*, `xf_post_FirstPost_14`.*
    ->                         FROM `xf_thread`
    ->                         LEFT JOIN `xf_poll` AS `xf_poll_Poll_1` ON (`xf_poll_Poll_1`.`content_type` = 'thread' AND `xf_poll_Poll_1`.`content_id` = `xf_thread`.`thread_id`)
    -> LEFT JOIN `xf_forum` AS `xf_forum_Forum_2` ON (`xf_forum_Forum_2`.`node_id` = `xf_thread`.`node_id`)
    -> LEFT JOIN `xf_node` AS `xf_node_Node_3` ON (`xf_node_Node_3`.`node_id` = `xf_forum_Forum_2`.`node_id`)
    -> LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_4` ON (`xf_permission_cache_content_Permissions_4`.`content_type` = 'node' AND `xf_permission_cache_content_Permissions_4`.`content_id` = `xf_node_Node_3`.`node_id` AND `xf_permission_cache_content_Permissions_4`.`permission_combination_id` = '42')
    -> LEFT JOIN `xf_user` AS `xf_user_User_5` ON (`xf_user_User_5`.`user_id` = `xf_thread`.`user_id`)
    -> LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_6` ON (`xf_user_profile_Profile_6`.`user_id` = `xf_user_User_5`.`user_id`)
    -> LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_7` ON (`xf_user_privacy_Privacy_7`.`user_id` = `xf_user_User_5`.`user_id`)
    -> LEFT JOIN `xf_user_option` AS `xf_user_option_Option_8` ON (`xf_user_option_Option_8`.`user_id` = `xf_user_User_5`.`user_id`)
    -> LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_9` ON (`xf_session_activity_Activity_9`.`user_id` = `xf_user_User_5`.`user_id` AND `xf_session_activity_Activity_9`.`unique_key` = CONCAT(`xf_user_User_5`.`user_id`, ''))
    -> LEFT JOIN `xf_admin` AS `xf_admin_Admin_10` ON (`xf_admin_Admin_10`.`user_id` = `xf_user_User_5`.`user_id`)
    -> LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_11` ON (`xf_thread_user_post_UserPosts_11`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_11`.`user_id` = '450224')
    -> LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_12` ON (`xf_thread_watch_Watch_12`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_12`.`user_id` = '450224')
    -> LEFT JOIN `xf_thread_reply_ban` AS `xf_thread_reply_ban_ReplyBans_13` ON (`xf_thread_reply_ban_ReplyBans_13`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_reply_ban_ReplyBans_13`.`user_id` = '450224')
    -> LEFT JOIN `xf_post` AS `xf_post_FirstPost_14` ON (`xf_post_FirstPost_14`.`post_id` = `xf_thread`.`first_post_id`)
    ->                         WHERE (`xf_thread`.`discussion_type` != 'redirect') AND (`xf_forum_Forum_2`.`find_new` = 1) AND (`xf_thread`.`node_id` IN (11249, 11251, 11253, 11255, 11257, 11259, 11263, 11265, 11267, 11269, 11271, 11273, 11275)) AND (`xf_thread`.`discussion_state` = 'visible')
    ->                         ORDER BY `xf_thread`.`post_date` DESC
    -> LIMIT 20;
+----+-------------+-------------------------------------------+--------+-------------------------------------------------------------------------------+-------------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                                     | type   | possible_keys                                                                 | key                     | key_len | ref                                                     | rows | Extra                                        |
+----+-------------+-------------------------------------------+--------+-------------------------------------------------------------------------------+-------------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | xf_forum_Forum_2                          | range  | PRIMARY                                                                       | PRIMARY                 | 4       | NULL                                                    |   13 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | xf_node_Node_3                            | eq_ref | PRIMARY                                                                       | PRIMARY                 | 4       | xenforo_import_clean.xf_forum_Forum_2.node_id           |    1 | NULL                                         |
|  1 | SIMPLE      | xf_permission_cache_content_Permissions_4 | eq_ref | PRIMARY                                                                       | PRIMARY                 | 35      | const,const,xenforo_import_clean.xf_node_Node_3.node_id |    1 | Using where                                  |
|  1 | SIMPLE      | xf_thread                                 | ref    | node_id_last_post_date,node_id_sticky_state_last_post,node_id,discussion_type | node_id                 | 4       | xenforo_import_clean.xf_forum_Forum_2.node_id           |  252 | Using where                                  |
|  1 | SIMPLE      | xf_poll_Poll_1                            | eq_ref | content_type_content_id                                                       | content_type_content_id | 31      | const,xenforo_import_clean.xf_thread.thread_id          |    1 | Using where                                  |
|  1 | SIMPLE      | xf_user_User_5                            | eq_ref | PRIMARY                                                                       | PRIMARY                 | 4       | xenforo_import_clean.xf_thread.user_id                  |    1 | NULL                                         |
|  1 | SIMPLE      | xf_user_profile_Profile_6                 | eq_ref | PRIMARY                                                                       | PRIMARY                 | 4       | xenforo_import_clean.xf_user_User_5.user_id             |    1 | NULL                                         |
|  1 | SIMPLE      | xf_user_privacy_Privacy_7                 | eq_ref | PRIMARY                                                                       | PRIMARY                 | 4       | xenforo_import_clean.xf_user_User_5.user_id             |    1 | NULL                                         |
|  1 | SIMPLE      | xf_user_option_Option_8                   | eq_ref | PRIMARY                                                                       | PRIMARY                 | 4       | xenforo_import_clean.xf_user_User_5.user_id             |    1 | NULL                                         |
|  1 | SIMPLE      | xf_session_activity_Activity_9            | eq_ref | PRIMARY                                                                       | PRIMARY                 | 22      | xenforo_import_clean.xf_user_User_5.user_id,func        |    1 | Using where                                  |
|  1 | SIMPLE      | xf_admin_Admin_10                         | eq_ref | PRIMARY                                                                       | PRIMARY                 | 4       | xenforo_import_clean.xf_user_User_5.user_id             |    1 | NULL                                         |
|  1 | SIMPLE      | xf_thread_user_post_UserPosts_11          | eq_ref | PRIMARY,user_id                                                               | PRIMARY                 | 8       | xenforo_import_clean.xf_thread.thread_id,const          |    1 | NULL                                         |
|  1 | SIMPLE      | xf_thread_watch_Watch_12                  | eq_ref | PRIMARY,thread_id_email_subscribe                                             | PRIMARY                 | 8       | const,xenforo_import_clean.xf_thread.thread_id          |    1 | NULL                                         |
|  1 | SIMPLE      | xf_thread_reply_ban_ReplyBans_13          | eq_ref | thread_id,user_id                                                             | thread_id               | 8       | xenforo_import_clean.xf_thread.thread_id,const          |    1 | NULL                                         |
|  1 | SIMPLE      | xf_post_FirstPost_14                      | eq_ref | PRIMARY                                                                       | PRIMARY                 | 4       | xenforo_import_clean.xf_thread.first_post_id            |    1 | NULL                                         |
+----+-------------+-------------------------------------------+--------+-------------------------------------------------------------------------------+-------------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
15 rows in set (0.00 sec)
 
Last edited:
By default, xf_session_activity is a memory table which does full-table locking and when mixed with innodb tables like in that query causes cross-engine transaction/lock handling to be invoked. Which sucks.

This should fix some things;
SQL: 
alter table xf_session_activity engine=innodb

Can you run the output of explain format=json ... before and after doing the table alter?

Explain with json formatting actually outputs more detailed information which makes troubleshooting this sort of thing.
 
Sure thing, before:

Code: 
{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "xf_thread",
            "access_type": "range",
            "possible_keys": [
              "node_id_last_post_date",
              "node_id_sticky_state_last_post",
              "xda_node_id",
              "xda_discussion_state",
              "xda_discussion_type"
            ],
            "key": "xda_node_id",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "rows": 4069,
            "filtered": 100,
            "index_condition": "(`xenforo_import_clean`.`xf_thread`.`node_id` in (11249,11251,11253,11255,11257,11259,11263,11265,11267,11269,11271,11273,11275))",
            "attached_condition": "((`xenforo_import_clean`.`xf_thread`.`discussion_type` <> 'redirect') and (`xenforo_import_clean`.`xf_thread`.`discussion_state` = 'visible'))"
          }
        },
        {
          "table": {
            "table_name": "xf_forum_Forum_2",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.node_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "(`xenforo_import_clean`.`xf_forum_Forum_2`.`find_new` = 1)"
          }
        },
        {
          "table": {
            "table_name": "xf_node_Node_3",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.node_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_permission_cache_content_Permissions_4",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "permission_combination_id",
              "content_type",
              "content_id"
            ],
            "key_length": "35",
            "ref": [
              "const",
              "const",
              "xenforo_import_clean.xf_node_Node_3.node_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "<if>(is_not_null_compl(xf_permission_cache_content_Permissions_4), (`xenforo_import_clean`.`xf_permission_cache_content_Permissions_4`.`content_type` = 'node'), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_poll_Poll_1",
            "access_type": "eq_ref",
            "possible_keys": [
              "content_type_content_id"
            ],
            "key": "content_type_content_id",
            "used_key_parts": [
              "content_type",
              "content_id"
            ],
            "key_length": "31",
            "ref": [
              "const",
              "xenforo_import_clean.xf_thread.thread_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "<if>(is_not_null_compl(xf_poll_Poll_1), (`xenforo_import_clean`.`xf_poll_Poll_1`.`content_type` = 'thread'), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_user_User_5",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_profile_Profile_6",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_privacy_Privacy_7",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_option_Option_8",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_session_activity_Activity_9",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id",
              "unique_key"
            ],
            "key_length": "22",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id",
              "func"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "<if>(is_not_null_compl(xf_session_activity_Activity_9), (`xenforo_import_clean`.`xf_session_activity_Activity_9`.`unique_key` = concat(`xenforo_import_clean`.`xf_user_User_5`.`user_id`,'')), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_admin_Admin_10",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_user_post_UserPosts_11",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "user_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "thread_id",
              "user_id"
            ],
            "key_length": "8",
            "ref": [
              "xenforo_import_clean.xf_thread.thread_id",
              "const"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_watch_Watch_12",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "thread_id_email_subscribe"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id",
              "thread_id"
            ],
            "key_length": "8",
            "ref": [
              "const",
              "xenforo_import_clean.xf_thread.thread_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_reply_ban_ReplyBans_13",
            "access_type": "eq_ref",
            "possible_keys": [
              "thread_id",
              "user_id"
            ],
            "key": "thread_id",
            "used_key_parts": [
              "thread_id",
              "user_id"
            ],
            "key_length": "8",
            "ref": [
              "xenforo_import_clean.xf_thread.thread_id",
              "const"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_post_FirstPost_14",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "post_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.first_post_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        }
      ]
    }
  }
}


After:

Code: 
{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "xf_thread",
            "access_type": "range",
            "possible_keys": [
              "node_id_last_post_date",
              "node_id_sticky_state_last_post",
              "xda_node_id",
              "xda_discussion_state",
              "xda_discussion_type"
            ],
            "key": "xda_node_id",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "rows": 4069,
            "filtered": 100,
            "index_condition": "(`xenforo_import_clean`.`xf_thread`.`node_id` in (11249,11251,11253,11255,11257,11259,11263,11265,11267,11269,11271,11273,11275))",
            "attached_condition": "((`xenforo_import_clean`.`xf_thread`.`discussion_type` <> 'redirect') and (`xenforo_import_clean`.`xf_thread`.`discussion_state` = 'visible'))"
          }
        },
        {
          "table": {
            "table_name": "xf_forum_Forum_2",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.node_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "(`xenforo_import_clean`.`xf_forum_Forum_2`.`find_new` = 1)"
          }
        },
        {
          "table": {
            "table_name": "xf_node_Node_3",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.node_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_permission_cache_content_Permissions_4",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "permission_combination_id",
              "content_type",
              "content_id"
            ],
            "key_length": "35",
            "ref": [
              "const",
              "const",
              "xenforo_import_clean.xf_node_Node_3.node_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "<if>(is_not_null_compl(xf_permission_cache_content_Permissions_4), (`xenforo_import_clean`.`xf_permission_cache_content_Permissions_4`.`content_type` = 'node'), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_poll_Poll_1",
            "access_type": "eq_ref",
            "possible_keys": [
              "content_type_content_id"
            ],
            "key": "content_type_content_id",
            "used_key_parts": [
              "content_type",
              "content_id"
            ],
            "key_length": "31",
            "ref": [
              "const",
              "xenforo_import_clean.xf_thread.thread_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "<if>(is_not_null_compl(xf_poll_Poll_1), (`xenforo_import_clean`.`xf_poll_Poll_1`.`content_type` = 'thread'), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_user_User_5",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_profile_Profile_6",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_privacy_Privacy_7",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_option_Option_8",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_session_activity_Activity_9",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows": 5,
            "filtered": 80,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "<if>(is_not_null_compl(xf_session_activity_Activity_9), ((`xenforo_import_clean`.`xf_session_activity_Activity_9`.`user_id` = `xenforo_import_clean`.`xf_user_User_5`.`user_id`) and (`xenforo_import_clean`.`xf_session_activity_Activity_9`.`unique_key` = concat(`xenforo_import_clean`.`xf_user_User_5`.`user_id`,''))), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_admin_Admin_10",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_user_post_UserPosts_11",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "user_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "thread_id",
              "user_id"
            ],
            "key_length": "8",
            "ref": [
              "xenforo_import_clean.xf_thread.thread_id",
              "const"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_watch_Watch_12",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "thread_id_email_subscribe"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id",
              "thread_id"
            ],
            "key_length": "8",
            "ref": [
              "const",
              "xenforo_import_clean.xf_thread.thread_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_reply_ban_ReplyBans_13",
            "access_type": "eq_ref",
            "possible_keys": [
              "thread_id",
              "user_id"
            ],
            "key": "thread_id",
            "used_key_parts": [
              "thread_id",
              "user_id"
            ],
            "key_length": "8",
            "ref": [
              "xenforo_import_clean.xf_thread.thread_id",
              "const"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_post_FirstPost_14",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "post_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.first_post_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        }
      ]
    }
  }
}

Still seems to be using filesort, and the temporary table as before
 
How the xf_session_activity table is being touched is different, see the "using_temporary_table": true, line and switching to a table scan via Block Nested Loop. This is only pulling ~5 rows out of an inspected 80 vs inspecting 100 rows.

Note; Block Nested Loop join style can be incredibly hit or miss on the performance impact. Often for XenForo changing optimizer settings to disable Block Nested Loop will provide useful speedups.

Otherwise I can recommend my Optimized List Queries add-on which has work-arounds which vastly migrate the effect.
 
Xon said:
Otherwise I can recommend my Optimized List Queries add-on which has work-arounds which vastly migrate the effect.
Click to expand...

I've installed this to see if it has any effect. I've also disabled the new posts widgets, as well as the what's new pages just so it doesn't get hung up when people are requesting things but this query is still being run, just can't figure out where exactly it's coming from
 
Jake B. said:
I've installed this to see if it has any effect. I've also disabled the new posts widgets, as well as the what's new pages just so it doesn't get hung up when people are requesting things but this query is still being run, just can't figure out where exactly it's coming from
Click to expand...
Working out where slow queries are coming from, I strongly recommend;
xenforo.com

Unmaintained - Slow query logger

Generates an error log entry when a slow query is detected. No configuration required. Once it has been running and reporting issues, I recommend disabling the add-on until you have pushed out fixes or understood why the previous logged queries...
xenforo.com xenforo.com

You can run it in a live environment with very little overhead.
 
I'm pretty sure this is actually from the new threads widget. I think it might be representing a portal-like page, as I think it has a particular configuration that's including the first post.

The simplest thing here is likely to be to apply the date limit option that we provide in the widget. We do specifically mention a performance benefit with it, though here's likely to be to suggest a better query execution plan to MySQL. The optimal index here is probably thread.post_date and that does exist. The date limit clause will encourage MySQL to use it.

BTW, there is a thread.node_id index -- MySQL uses left-most prefixes of indexes so any index that starts with node_id
 
Xon said:
Working out where slow queries are coming from, I strongly recommend;
xenforo.com

Unmaintained - Slow query logger

Generates an error log entry when a slow query is detected. No configuration required. Once it has been running and reporting issues, I recommend disabling the add-on until you have pushed out fixes or understood why the previous logged queries...
xenforo.com xenforo.com
Click to expand...

Thanks! I've gotten that installed, will check into it the next time the issue comes up

Mike said:
I'm pretty sure this is actually from the new threads widget. I think it might be representing a portal-like page, as I think it has a particular configuration that's including the first post.
Click to expand...

Just had a look, and I think you're right that it's something to do with new threads rather than new posts - for some reason I remembered reading last post date, not post date but I guess I misread it at one point looking back at the query. However, after checking there is no new thread widget currently configured. We are using the /threads endpoint, however. Would a query like this end up being run there, and if so what could we do to mitigate this issue? IIRC that endpoint already uses a date limiter based on the unread post date option, or something along those lines so I'm guessing it's something else I'm missing somewhere. Is there a way to get debug data (specifically query logs) on REST endpoints similar to what you can get on main frontend pages?
 
The logger add-on came up with this:

Code: 
Exception: Slow query: 211.2297 seconds, /api/threads?order=post_date&page=1 src/addons/SV/SlowQueryLogger/Db/Mysqli/SlowQueryLogAdapter.php:287

Code: 
#0 src/XF/Db/Mysqli/Statement.php(108): SV\SlowQueryLogger\Db\Mysqli\SlowQueryLogAdapter->logQueryCompletion()
#1 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#2 src/XF/Mvc/Entity/Finder.php(1294): XF\Db\AbstractAdapter->query()
#3 src/XF/Api/Controller/Threads.php(36): XF\Mvc\Entity\Finder->fetch()
#4 src/XF/Mvc/Dispatcher.php(350): XF\Api\Controller\Threads->actionGet()
#5 src/XF/Api/Mvc/Dispatcher.php(27): XF\Mvc\Dispatcher->dispatchClass()
#6 src/XF/Mvc/Dispatcher.php(113): XF\Api\Mvc\Dispatcher->dispatchFromMatch()
#7 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop()
#8 src/XF/App.php(2191): XF\Mvc\Dispatcher->run()
#9 src/XF.php(391): XF\App->run()
#10 index.php(16): XF::runApp()
#11 {main}

Code: 
array(4) {
["url"] => string(35) "/api/threads?order=post_date&page=1"
["referrer"] => bool(false)
["_GET"] => array(3) {
["/api/threads"] => string(0) ""
["order"] => string(9) "post_date"
["page"] => string(1) "1"
}
["_POST"] => array(0) {
}
}

Any ideas of what we can do to make this more performant?
 
Last edited:
Ah, looks like it is because of a non-default sort type via API. Not strictly speaking an XF widget.

I think you will need to add a compound index and test again to see if MySQL then uses it;
SQL: 
alter table xf_thread add index node_id_post_date (node_id,post_date);

This should reduce the number of thread entries which are examined. Should have asked for the explain format=json first!
 
Xon said:
Ah, looks like it is because of a non-default sort type via API. Not strictly speaking an XF widget.
Click to expand...

Yeah, initial guess was just based on the query that was hanging up based on the processlist. It is non-default in the sense that it's not the sort applied by default, but it is a sort option included in XenForo by default (just to clarify)

Xon said:
I think you will need to add a compound index and test again to see if MySQL then uses it;
Click to expand...

That didn't seem to have any effect, unfortunately. Still seems to lock up the tables, and

Code: 
{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "xf_forum_Forum_2",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "rows": 13,
            "filtered": 100,
            "attached_condition": "(((`xenforo_import_clean`.`xf_forum_Forum_2`.`find_new` = 1) and (`xenforo_import_clean`.`xf_forum_Forum_2`.`node_id` in (11249,11251,11253,11255,11257,11259,11263,11265,11267,11269,11271,11273,11275))) and (`xenforo_import_clean`.`xf_forum_Forum_2`.`node_id` is not null))"
          }
        },
        {
          "table": {
            "table_name": "xf_node_Node_3",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_forum_Forum_2.node_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_permission_cache_content_Permissions_4",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "permission_combination_id",
              "content_type",
              "content_id"
            ],
            "key_length": "35",
            "ref": [
              "const",
              "const",
              "xenforo_import_clean.xf_node_Node_3.node_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "<if>(is_not_null_compl(xf_permission_cache_content_Permissions_4), (`xenforo_import_clean`.`xf_permission_cache_content_Permissions_4`.`content_type` = 'node'), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_thread",
            "access_type": "ref",
            "possible_keys": [
              "node_id_last_post_date",
              "node_id_sticky_state_last_post",
              "xda_node_id_post_date"
            ],
            "key": "xda_node_id_post_date",
            "used_key_parts": [
              "node_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_forum_Forum_2.node_id"
            ],
            "rows": 229,
            "filtered": 100,
            "attached_condition": "((`xenforo_import_clean`.`xf_thread`.`discussion_type` <> 'redirect') and (`xenforo_import_clean`.`xf_thread`.`discussion_state` = 'visible'))"
          }
        },
        {
          "table": {
            "table_name": "xf_poll_Poll_1",
            "access_type": "eq_ref",
            "possible_keys": [
              "content_type_content_id"
            ],
            "key": "content_type_content_id",
            "used_key_parts": [
              "content_type",
              "content_id"
            ],
            "key_length": "31",
            "ref": [
              "const",
              "xenforo_import_clean.xf_thread.thread_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "<if>(is_not_null_compl(xf_poll_Poll_1), (`xenforo_import_clean`.`xf_poll_Poll_1`.`content_type` = 'thread'), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_user_User_5",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_profile_Profile_6",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_privacy_Privacy_7",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_user_option_Option_8",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_session_activity_Activity_9",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows": 3,
            "filtered": 100,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "<if>(is_not_null_compl(xf_session_activity_Activity_9), ((`xenforo_import_clean`.`xf_session_activity_Activity_9`.`user_id` = `xenforo_import_clean`.`xf_user_User_5`.`user_id`) and (`xenforo_import_clean`.`xf_session_activity_Activity_9`.`unique_key` = concat(`xenforo_import_clean`.`xf_user_User_5`.`user_id`,''))), true)"
          }
        },
        {
          "table": {
            "table_name": "xf_admin_Admin_10",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_user_User_5.user_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_user_post_UserPosts_11",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "user_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "thread_id",
              "user_id"
            ],
            "key_length": "8",
            "ref": [
              "xenforo_import_clean.xf_thread.thread_id",
              "const"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_watch_Watch_12",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY",
              "thread_id_email_subscribe"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "user_id",
              "thread_id"
            ],
            "key_length": "8",
            "ref": [
              "const",
              "xenforo_import_clean.xf_thread.thread_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_thread_reply_ban_ReplyBans_13",
            "access_type": "eq_ref",
            "possible_keys": [
              "thread_id",
              "user_id"
            ],
            "key": "thread_id",
            "used_key_parts": [
              "thread_id",
              "user_id"
            ],
            "key_length": "8",
            "ref": [
              "xenforo_import_clean.xf_thread.thread_id",
              "const"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "xf_post_FirstPost_14",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "post_id"
            ],
            "key_length": "4",
            "ref": [
              "xenforo_import_clean.xf_thread.first_post_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        }
      ]
    }
  }
}

One thing I've just found though is that the last_post_date limitation is excluded when there is any sort being applied in \XF\Api\Controller\Threads::setupThreadFinder

Code: 
    protected function setupThreadFinder(&$filters = [], &$sort = null)
    {
        $threadRepo = $this->repository('XF:Thread');
        $threadFinder = $threadRepo->findThreadsForApi();

        /** @var \XF\Api\ControllerPlugin\Thread $threadPlugin */
        $threadPlugin = $this->plugin('XF:Api:Thread');

        $filters = $threadPlugin->applyThreadListFilters($threadFinder);

        $sort = $threadPlugin->applyThreadListSort($threadFinder);

        if (!isset($filters['last_days']))
        {
            if (!$sort || ($sort[0] == 'last_post_date' && $sort[1] == 'desc'))
            {
                $threadFinder->where('last_post_date', '>', $threadRepo->getReadMarkingCutOff());
            }
        }

        return $threadFinder;
    }

I'm guessing this is what is leading to this issue. I think I'm just going to extend this and try to make sure a date limitation is always applied
 
Adding that index made it a heck of a lot worse. It transposed the node list onto xf_forum.node_id meaning the xda_node_id_post_date index is completely ignored!

Try running (warning can block for a little while!);
SQL: 
optimize table xf_forum;
optimize table xf_thread;
As this can reset a number of the statistics and sometimes thump the optimizer into not being a deoptimizer.
 
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.2).

Change log:
Give MySQL an index hint to improve performance of newest thread API requests (with no other filters)
Click to expand...
There may be a delay before changes are rolled out to the XenForo Community.
 

Similar threads

jeffatrackaid
  • Question Question
XF 2.2 Very slow query causing server issues.
Replies
1
Views
45
Xon
X
K
Fixed New Threads Widget causing Slow Query
Replies
1
Views
1K
XF Bug Bot
XF Bug Bot
K
Not a bug Xenforo Relation returning All rows from left table causing conversation messages to disappear
Replies
3
Views
769
kick
K
D
Server issues: slowdowns and timeout. Need help on what is causing the problem.
Replies
4
Views
7K
WSWD
WSWD
Back
Top Bottom