XF 2.2 Profile page Sql query hangs after upgrading XF 2.2.3

[egerci]

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



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 :

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 :

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	xf_profile_post	ALL	NULL	NULL	NULL	NULL	323485	Using where; Using temporary; Using filesort
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	ALL	PRIMARY	NULL	NULL	NULL	718901	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	xf_user_privacy_Privacy_5	eq_ref	PRIMARY	PRIMARY	4	kk_forum_xf.xf_user_ProfileUser_4.user_id	1	Using 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

 

[Xon]

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;

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

+------+-------------+-----------------------------------+--------+--------------------------------------------------------------------------------------+-------------------------+---------+------------------------------------------------------------+-------+-----------------------------+
| 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?

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;

 

[Xon]

The only work-around I know of this issue is;

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!)

[mysqld]
optimizer_use_condition_selectivity=1

 

[egerci]

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 :

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]

@Xon Hi,

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

[mysqld]
optimizer_use_condition_selectivity=1

Restart mysql and run the query :

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:

+------+-------------+-----------------------------------+--------+-----------------------------------------------------------------------------------+-------------------------+---------+---------------------------------------------------------+------+-------------+
| 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.