XF 2.2 Profile page Sql query hangs after upgrading XF 2.2.3

egerci

Member
Hi,
After updating XenForo 2.2.3, I started getting the following issue.

1612955800799.png

I've been trying to find a clue for about a week, but haven't been able to find out when and how this error was triggered.

I disabled all the addons I used. The result did not change. After some time, Queries remain in the status of sending data. And xenforo does not give an error in the control panel. As I said I can't tell you how often this situation happens. Usually it is less frequent at night.I encounter this situation every 2-3 hours during the day and in busy times.

When I kill the query on the sql server, the ACP gives the following error.
Commands out of sync; you can't run this command now.

Query is :
SQL:
SELECT `xf_profile_post`.*, `xf_user_User_1`.*, `xf_reaction_content_Reactions_2`.*, `xf_sv_content_mentions_Mentions_3`.*, `xf_user_ProfileUser_4`.*, `xf_user_privacy_Privacy_5`.*
FROM `xf_profile_post`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_profile_post`.`user_id`)
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_2` ON (`xf_reaction_content_Reactions_2`.`content_type` = 'profile_post' AND `xf_reaction_content_Reactions_2`.`content_id` = `xf_profile_post`.`profile_post_id` AND `xf_reaction_content_Reactions_2`.`reaction_user_id` = '1024023')
LEFT JOIN `xf_sv_content_mentions` AS `xf_sv_content_mentions_Mentions_3` ON (`xf_sv_content_mentions_Mentions_3`.`content_type` = 'profile_post' AND `xf_sv_content_mentions_Mentions_3`.`content_id` = `xf_profile_post`.`profile_post_id`)
LEFT JOIN `xf_user` AS `xf_user_ProfileUser_4` ON (`xf_user_ProfileUser_4`.`user_id` = `xf_profile_post`.`profile_user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_5` ON (`xf_user_privacy_Privacy_5`.`user_id` = `xf_user_ProfileUser_4`.`user_id`)
WHERE (`xf_profile_post`.`message_state` <> 'moderated') AND (`xf_profile_post`.`message_state` <> 'deleted')
ORDER BY `xf_profile_post`.`post_date` DESC
LIMIT 200


EXPLAIN as :


idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLExf_profile_postALLNULLNULLNULLNULL323485Using where; Using temporary; Using filesort
1SIMPLExf_user_User_1eq_refPRIMARYPRIMARY4kk_forum_xf.xf_profile_post.user_id1
1SIMPLExf_reaction_content_Reactions_2eq_refcontent_type_id_user_id,
content_type_id_reaction_date,content_id,
reaction_user_id
content_type_id_user_id35const,
kk_forum_xf.xf_profile_post.profile_post_id,
const
1Using where
1SIMPLExf_sv_content_mentions_Mentions_3eq_refPRIMARYPRIMARY31const,kk_forum_xf.xf_profile_post.profile_post_id1Using where
1SIMPLExf_user_ProfileUser_4ALLPRIMARYNULLNULLNULL718901Using where; Using join buffer (flat, BNL join)
1SIMPLExf_user_privacy_Privacy_5eq_refPRIMARYPRIMARY4kk_forum_xf.xf_user_ProfileUser_4.user_id1Using where


My configuration is as follows:
Nginx 1.18.0
Ubuntu 18.04
Mariadb 10.5.8
Php 7.4.15
Redis 6.0.10
phpredis 5.3.2

@Xon , I think you can comment on this.

Thanks
 
Last edited:

Xon

Well-known member
Can you give the actual query rather than that typed out one? There is a bunch of typo's which make exploring what that query is doing on various MySQL versions very hard

Reproducing this;
SQL:
explain
SELECT `xf_profile_post`.*, `xf_user_User_1`.*, `xf_reaction_content_Reactions_2`.*, `xf_sv_content_mentions_Mentions_3`.*, `xf_user_ProfileUser_4`.*, `xf_user_privacy_Privacy_5`.*
FROM `xf_profile_post`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_profile_post`.`user_id`)
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_2` ON (`xf_reaction_content_Reactions_2`.`content_type` = 'profile_post' AND `xf_reaction_content_Reactions_2`.`content_id` = `xf_profile_post`.`profile_post_id` AND `xf_reaction_content_Reactions_2`.`reaction_user_id` = '7')
LEFT JOIN `xf_sv_content_mentions` AS `xf_sv_content_mentions_Mentions_3` ON (`xf_sv_content_mentions_Mentions_3`.`content_type` = 'profile_post' AND `xf_sv_content_mentions_Mentions_3`.`content_id` = `xf_profile_post`.`profile_post_id`)
LEFT JOIN `xf_user` AS `xf_user_ProfileUser_4` ON (`xf_user_ProfileUser_4`.`user_id` = `xf_profile_post`.`profile_user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_5` ON (`xf_user_privacy_Privacy_5`.`user_id` = `xf_user_ProfileUser_4`.`user_id`)
WHERE (`xf_profile_post`.`message_state` <> 'moderated') AND (`xf_profile_post`.`message_state` <> 'deleted')
ORDER BY `xf_profile_post`.`post_date` DESC
LIMIT 500

Code:
+------+-------------+-----------------------------------+--------+--------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------------------------+-------+-----------------------------+
| id   | select_type | table                             | type   | possible_keys                                                                        | key                     | key_len | ref                                                        | rows  | Extra                       |
+------+-------------+-----------------------------------+--------+--------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------------------------+-------+-----------------------------+
|    1 | SIMPLE      | xf_profile_post                   | ALL    | NULL                                                                                 | NULL                    | NULL    | NULL                                                       | 48301 | Using where; Using filesort |
|    1 | SIMPLE      | xf_user_User_1                    | eq_ref | PRIMARY                                                                              | PRIMARY                 | 4       | sufficientv_t2.xf_profile_post.user_id                     | 1     |                             |
|    1 | SIMPLE      | xf_reaction_content_Reactions_2   | eq_ref | content_type_id_user_id,content_type_id_reaction_date,reaction_user_id_reaction_date | content_type_id_user_id | 35      | const,sufficientv_t2.xf_profile_post.profile_post_id,const | 1     | Using where                 |
|    1 | SIMPLE      | xf_sv_content_mentions_Mentions_3 | eq_ref | PRIMARY                                                                              | PRIMARY                 | 31      | const,sufficientv_t2.xf_profile_post.profile_post_id       | 1     | Using where                 |
|    1 | SIMPLE      | xf_user_ProfileUser_4             | eq_ref | PRIMARY                                                                              | PRIMARY                 | 4       | sufficientv_t2.xf_profile_post.profile_user_id             | 1     |                             |
|    1 | SIMPLE      | xf_user_privacy_Privacy_5         | eq_ref | PRIMARY                                                                              | PRIMARY                 | 4       | sufficientv_t2.xf_user_ProfileUser_4.user_id               | 1     | Using where                 |
+------+-------------+-----------------------------------+--------+--------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------------------------+-------+-----------------------------+
Note; the query is horrible even without the xf_sv_content_mentions join

The explain is better, just not sure why. But ultimately the problem is;
  1. No index usage on xf_profile_post
  2. Insanely bad index join to xf_user
Can you try running these queries to update table stats?
SQL:
ANALYZE TABLE xf_profile_post;
ANALYZE TABLE xf_user;
ANALYZE TABLE xf_user_privacy;
ANALYZE TABLE xf_reaction_content;
ANALYZE TABLE xf_sv_content_mentions;
 
Last edited:

Xon

Well-known member
The only work-around I know of this issue is;
SQL:
set optimizer_use_condition_selectivity=1;
set global optimizer_use_condition_selectivity=1;

Then add /etc/my.cnf.d/optimize_fix.cnf or /etc/mysql/conf.d/optimize_fix.cnf (depends on distro!)
Code:
[mysqld]
optimizer_use_condition_selectivity=1
 

egerci

Member
Hi Xon,

Can you give the actual query rather than that typed out one?

I can't understand what you mean? I just copied query from SHOW FULL PROCESSLIST query.



I have just run queries that you wrote :
SQL:
ANALYZE TABLE xf_profile_post;
+-----------------------------+---------+----------+----------+
| Table                       | Op      | Msg_type | Msg_text |
+-----------------------------+---------+----------+----------+
| kk_forum_xf.xf_profile_post | analyze | status   | OK       |
+-----------------------------+---------+----------+----------+
1 row in set (0.159 sec)

ANALYZE TABLE xf_user;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| kk_forum_xf.xf_user | analyze | status   | OK       |
+---------------------+---------+----------+----------+
1 row in set (1.285 sec)


ANALYZE TABLE xf_user_privacy;
+-----------------------------+---------+----------+----------+
| Table                       | Op      | Msg_type | Msg_text |
+-----------------------------+---------+----------+----------+
| kk_forum_xf.xf_user_privacy | analyze | status   | OK       |
+-----------------------------+---------+----------+----------+
1 row in set (0.056 sec)

ANALYZE TABLE xf_reaction_content;
+---------------------------------+---------+----------+----------+
| Table                           | Op      | Msg_type | Msg_text |
+---------------------------------+---------+----------+----------+
| kk_forum_xf.xf_reaction_content | analyze | status   | OK       |
+---------------------------------+---------+----------+----------+
1 row in set (1 min 0.110 sec)

ANALYZE TABLE xf_sv_content_mentions;
+------------------------------------+---------+----------+----------+
| Table                              | Op      | Msg_type | Msg_text |
+------------------------------------+---------+----------+----------+
| kk_forum_xf.xf_sv_content_mentions | analyze | status   | OK       |
+------------------------------------+---------+----------+----------+
1 row in set (0.613 sec)
 

egerci

Member
@Xon Hi,

I have add /etc/mysql/conf.d/optimize_fix.cnf file and

Code:
[mysqld]
optimizer_use_condition_selectivity=1
Restart mysql and run the query :

SQL:
SELECT `xf_profile_post`.*, `xf_user_User_1`.*, `xf_reaction_content_Reactions_2`.*, `xf_sv_content_mentions_Mentions_3`.*, `xf_user_ProfileUser_4`.*, `xf_user_privacy_Privacy_5`.*
FROM `xf_profile_post`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_profile_post`.`user_id`)
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_2` ON (`xf_reaction_content_Reactions_2`.`content_type` = 'profile_post' AND `xf_reaction_content_Reactions_2`.`content_id` = `xf_profile_post`.`profile_post_id` AND `xf_reaction_content_Reactions_2`.`reaction_user_id` = '1024023')
LEFT JOIN `xf_sv_content_mentions` AS `xf_sv_content_mentions_Mentions_3` ON (`xf_sv_content_mentions_Mentions_3`.`content_type` = 'profile_post' AND `xf_sv_content_mentions_Mentions_3`.`content_id` = `xf_profile_post`.`profile_post_id`)
LEFT JOIN `xf_user` AS `xf_user_ProfileUser_4` ON (`xf_user_ProfileUser_4`.`user_id` = `xf_profile_post`.`profile_user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_5` ON (`xf_user_privacy_Privacy_5`.`user_id` = `xf_user_ProfileUser_4`.`user_id`)
WHERE (`xf_profile_post`.`message_state` <> 'moderated') AND (`xf_profile_post`.`message_state` <> 'deleted')
ORDER BY `xf_profile_post`.`post_date` DESC       
LIMIT 200

Like a miracle. Result 200 rows in set (0.011 sec)

EXPLAIN Result:
Code:
+------+-------------+-----------------------------------+--------+-----------------------------------------------------------------------------------+-------------------------+---------+---------------------------------------------------------+------+-------------+
| id   | select_type | table                             | type   | possible_keys                                                                     | key                     | key_len | ref                                                     | rows | Extra       |
+------+-------------+-----------------------------------+--------+-----------------------------------------------------------------------------------+-------------------------+---------+---------------------------------------------------------+------+-------------+
|    1 | SIMPLE      | xf_profile_post                   | index  | NULL                                                                              | post_date               | 4       | NULL                                                    | 200  | Using where |
|    1 | SIMPLE      | xf_user_User_1                    | eq_ref | PRIMARY                                                                           | PRIMARY                 | 4       | kk_forum_xf.xf_profile_post.user_id                     | 1    |             |
|    1 | SIMPLE      | xf_reaction_content_Reactions_2   | eq_ref | content_type_id_user_id,content_type_id_reaction_date,content_id,reaction_user_id | content_type_id_user_id | 35      | const,kk_forum_xf.xf_profile_post.profile_post_id,const | 1    | Using where |
|    1 | SIMPLE      | xf_sv_content_mentions_Mentions_3 | eq_ref | PRIMARY                                                                           | PRIMARY                 | 31      | const,kk_forum_xf.xf_profile_post.profile_post_id       | 1    | Using where |
|    1 | SIMPLE      | xf_user_ProfileUser_4             | eq_ref | PRIMARY                                                                           | PRIMARY                 | 4       | kk_forum_xf.xf_profile_post.profile_user_id             | 1    |             |
|    1 | SIMPLE      | xf_user_privacy_Privacy_5         | eq_ref | PRIMARY                                                                           | PRIMARY                 | 4       | kk_forum_xf.xf_user_ProfileUser_4.user_id               | 1    | Using where |
+------+-------------+-----------------------------------+--------+-----------------------------------------------------------------------------------+-------------------------+---------+---------------------------------------------------------+------+-------------+

I will wait a bit more to see.

Thanks very very much. You save my time.
 
Top