XF 2.2 Sql query time is long for xf_post

[Azaly]

Hi! I got sql performance issue after updating XenForo 2.2.3 (not sure is it matter, but in same time I created custom thread field for the first time and deleting 10 popular tags). Now my slow query log is full of queries like this:

SELECT post_id, post_date, user_id, username, reaction_score, reactions FROM xf_post WHERE thread_id = '14369' ORDER BY post_date LIMIT 1;                                                     
+---------+------------+---------+----------+----------------+----------------+
| post_id | post_date  | user_id | username | reaction_score | reactions      |
+---------+------------+---------+----------+----------------+----------------+
| 4193961 | 1606840932 |   111   | Li       |             25 | {"1":24,"4":1} |
+---------+------------+---------+----------+----------------+----------------+
1 row in set (22.006 sec)

Explain:

explain SELECT post_id, post_date, user_id, username, reaction_score, reactions FROM xf_post WHERE thread_id = '14369' ORDER BY post_date LIMIT 1;
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys                                               | key       | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | xf_post | index | thread_id_post_date,thread_id_position,thread_id_score_date | post_date | 4       | NULL | 196  | Using where |
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+------+------+-------------+
1 row in set (0.001 sec)

ANALYZE TABLE:

ANALYZE TABLE xf_post;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| xf_post            | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.042 sec)

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

This happens when moderators merge 2 post in large threads (10–20K messages). There are no issue with posting or deleting posts in these threads and everything is ok with merging posts inside small threads.

I’ve struggling with this almost a week and do not find a clue. My server admin couldn't find any problem in config too.
(After updating XenForo 2.2.3 I got also long queries like here and set optimizer_use_condition_selectivity=1 as @Xon suggested there, but problem with merging still there.)

I use nginx 1.9.3, php 7.2.23, mariadb 10.5.6 + memcached

Thanks

 

[Xon]

This will dump the table schema, maybe you are missing some indexes?

show create table xf_post;

Can you try these?

explain
SELECT post_id, post_date, user_id, username, reaction_score, reactions
FROM xf_post use index (thread_id_post_date)
WHERE thread_id = '14369'
ORDER BY post_date
LIMIT 1;

explain
SELECT post_id, post_date, user_id, username, reaction_score, reactions
FROM xf_post
WHERE thread_id = 14369
ORDER BY post_date
LIMIT 1;

Try int vs a string, and adding an index hint.

 

[Azaly]

Hi Xon!

This will dump the table schema, maybe you are missing some indexes?

show create table xf_post;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table              |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xf_post | CREATE TABLE `xf_post` (
  `post_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thread_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `username` varchar(50) NOT NULL,
  `post_date` int(10) unsigned NOT NULL,
  `message` mediumtext NOT NULL,
  `ip_id` int(10) unsigned NOT NULL DEFAULT 0,
  `message_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
  `attach_count` smallint(5) unsigned NOT NULL DEFAULT 0,
  `position` int(10) unsigned NOT NULL,
  `type_data` mediumblob NOT NULL,
  `reaction_score` int(11) NOT NULL DEFAULT 0,
  `reactions` blob DEFAULT NULL,
  `reaction_users` blob NOT NULL,
  `warning_id` int(10) unsigned NOT NULL DEFAULT 0,
  `warning_message` varchar(255) NOT NULL DEFAULT '',
  `last_edit_date` int(10) unsigned NOT NULL DEFAULT 0,
  `last_edit_user_id` int(10) unsigned NOT NULL DEFAULT 0,
  `edit_count` int(10) unsigned NOT NULL DEFAULT 0,
  `original_poster_waindigo` text DEFAULT NULL,
  `embed_metadata` blob DEFAULT NULL,
  `vote_score` int(11) NOT NULL,
  `vote_count` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`post_id`),
  KEY `thread_id_post_date` (`thread_id`,`post_date`),
  KEY `thread_id_position` (`thread_id`,`position`),
  KEY `user_id` (`user_id`),
  KEY `post_date` (`post_date`),
  KEY `thread_id_score_date` (`thread_id`,`vote_score`,`post_date`)
) ENGINE=InnoDB AUTO_INCREMENT=4269202 DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Queries with index:

explain
    -> SELECT post_id, post_date, user_id, username, reaction_score, reactions
    -> FROM xf_post use index (thread_id_post_date)
    -> WHERE thread_id = '14369'
    -> ORDER BY post_date
    -> LIMIT 1;
+------+-------------+---------+------+---------------------+---------------------+---------+-------+-------+-------------+
| id   | select_type | table   | type | possible_keys       | key                 | key_len | ref   | rows  | Extra       |
+------+-------------+---------+------+---------------------+---------------------+---------+-------+-------+-------------+
|    1 | SIMPLE      | xf_post | ref  | thread_id_post_date | thread_id_post_date | 4       | const | 20044 | Using where |
+------+-------------+---------+------+---------------------+---------------------+---------+-------+-------+-------------+
1 row in set (0.001 sec)


explain
    -> SELECT post_id, post_date, user_id, username, reaction_score, reactions
    -> FROM xf_post
    -> WHERE thread_id = 14369
    -> ORDER BY post_date
    -> LIMIT 1;
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+------+------+-------------+
| id   | select_type | table   | type  | possible_keys                                               | key       | key_len | ref  | rows | Extra       |
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | xf_post | index | thread_id_post_date,thread_id_position,thread_id_score_date | post_date | 4       | NULL | 194  | Using where |
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+------+------+-------------+
1 row in set (0.001 sec)

 

[Xon]

Can you run those queries using analyze instead of explain? That runs them and returns the actual time/rows touched.

I suspect XF will likely need to add the use index hint for this query to get the optimizer under control.

 

[Azaly]

Can you run those queries using analyze instead of explain?

With index query is pretty quick!

analyze
    -> SELECT post_id, post_date, user_id, username, reaction_score, reactions
    -> FROM xf_post use index (thread_id_post_date)
    -> WHERE thread_id = '14369'
    -> ORDER BY post_date
    -> LIMIT 1;
+------+-------------+---------+------+---------------------+---------------------+---------+-------+-------+--------+----------+------------+-------------+
| id   | select_type | table   | type | possible_keys       | key                 | key_len | ref   | rows  | r_rows | filtered | r_filtered | Extra       |
+------+-------------+---------+------+---------------------+---------------------+---------+-------+-------+--------+----------+------------+-------------+
|    1 | SIMPLE      | xf_post | ref  | thread_id_post_date | thread_id_post_date | 4       | const | 20172 | 1.00   |   100.00 |     100.00 | Using where |
+------+-------------+---------+------+---------------------+---------------------+---------+-------+-------+--------+----------+------------+-------------+
1 row in set (0.001 sec)


analyze
    -> SELECT post_id, post_date, user_id, username, reaction_score, reactions
    -> FROM xf_post
    -> WHERE thread_id = 14369
    -> ORDER BY post_date
    -> LIMIT 1;

+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+-------+---------+------------+----------+------------+-------------+
| id   | select_type | table   | type  | possible_keys                                               | key       | key_len | ref   | rows    | r_rows     | filtered | r_filtered | Extra       |
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+-------+---------+------------+----------+------------+-------------+
|    1 | SIMPLE      | xf_post | index | thread_id_post_date,thread_id_position,thread_id_score_date | post_date | 4       | const | 3880759 | 4018744.00 |     0.52 |       0.00 | Using where |
+------+-------------+---------+-------+-------------------------------------------------------------+-----------+---------+-------+---------+------------+----------+------------+-------------+
1 row in set (21.908 sec)

I suspect XF will likely need to add the use index hint for this query to get the optimizer under control.

So I need to post bug report for XF team? Right?
upd: https://xenforo.com/community/threads/add-use-index-to-merge-posts-query.191247/