Fixed Incredibly poor performance for Latest Activity feed for MariaDb 10.4+

[Xon]

Affected version

2.2.8 Patch 1

This is highly dependant on the user, number of followers and the site's activity. ~19.4 seconds vs 0.001 seconds is a serious performance regression.

analyze is like explain but executes the query and reports back actual row counts.

set join_cache_level=2;
analyze
SELECT `xf_news_feed`.*, `xf_user_User_1`.*
FROM `xf_news_feed` FORCE INDEX (`event_date`)
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_news_feed`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`user_id` = `xf_user_User_1`.`user_id`)
LEFT JOIN `xf_user_follow` AS `xf_user_follow_Following_3` ON (`xf_user_follow_Following_3`.`user_id` = `xf_user_User_1`.`user_id` AND `xf_user_follow_Following_3`.`follow_user_id` = '1')
WHERE (
    (`xf_news_feed`.`user_id` = 1) OR
    (`xf_news_feed`.`user_id` = 0) OR
    (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` IN ('everyone', 'members')) OR
    (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` = 'followed' AND `xf_user_follow_Following_3`.`user_id` IS NOT NULL)
)
ORDER BY `xf_news_feed`.`event_date` DESC
limit 30;

+------+-------------+----------------------------+--------+------------------------+----------------+---------+------------------------------+---------+------------+----------+------------+--------------------------------------------------------------+
| id   | select_type | table                      | type   | possible_keys          | key            | key_len | ref                          | rows    | r_rows     | filtered | r_filtered | Extra                                                        |
+------+-------------+----------------------------+--------+------------------------+----------------+---------+------------------------------+---------+------------+----------+------------+--------------------------------------------------------------+
|    1 | SIMPLE      | xf_news_feed               | ALL    | NULL                   | NULL           | NULL    | NULL                         | 2800000 | 3100000.00 |   100.00 |     100.00 | Using temporary; Using filesort                              |
|    1 | SIMPLE      | xf_user_User_1             | eq_ref | PRIMARY                | PRIMARY        | 4       | test2.xf_news_feed.user_id   | 1       | 1.00       |   100.00 |     100.00 |                                                              |
|    1 | SIMPLE      | xf_user_privacy_Privacy_2  | eq_ref | PRIMARY                | PRIMARY        | 4       | test2.xf_user_User_1.user_id | 1       | 1.00       |   100.00 |     100.00 | Using where                                                  |
|    1 | SIMPLE      | xf_user_follow_Following_3 | range  | PRIMARY,follow_user_id | follow_user_id | 4       | NULL                         | 1       | 0.00       |   100.00 |     100.00 | Using where; Using index; Using join buffer (flat, BNL join) |
+------+-------------+----------------------------+--------+------------------------+----------------+---------+------------------------------+---------+------------+----------+------------+--------------------------------------------------------------+
4 rows in set (19.430 sec)

set join_cache_level=0;
analyze
SELECT `xf_news_feed`.*, `xf_user_User_1`.*
FROM `xf_news_feed` FORCE INDEX (`event_date`)
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_news_feed`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`user_id` = `xf_user_User_1`.`user_id`)
LEFT JOIN `xf_user_follow` AS `xf_user_follow_Following_3` ON (`xf_user_follow_Following_3`.`user_id` = `xf_user_User_1`.`user_id` AND `xf_user_follow_Following_3`.`follow_user_id` = '1')
WHERE (
    (`xf_news_feed`.`user_id` = 1) OR
    (`xf_news_feed`.`user_id` = 0) OR
    (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` IN ('everyone', 'members')) OR
    (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` = 'followed' AND `xf_user_follow_Following_3`.`user_id` IS NOT NULL)
)
ORDER BY `xf_news_feed`.`event_date` DESC
limit 30;

+------+-------------+----------------------------+--------+------------------------+----------------+---------+------------------------------+---------+--------+----------+------------+--------------------------+
| id   | select_type | table                      | type   | possible_keys          | key            | key_len | ref                          | rows    | r_rows | filtered | r_filtered | Extra                    |
+------+-------------+----------------------------+--------+------------------------+----------------+---------+------------------------------+---------+--------+----------+------------+--------------------------+
|    1 | SIMPLE      | xf_news_feed               | index  | NULL                   | event_date     | 4       | NULL                         | 2800000 | 35.00  |   100.00 |     100.00 |                          |
|    1 | SIMPLE      | xf_user_User_1             | eq_ref | PRIMARY                | PRIMARY        | 4       | test2.xf_news_feed.user_id   | 1       | 1.00   |   100.00 |     100.00 |                          |
|    1 | SIMPLE      | xf_user_privacy_Privacy_2  | eq_ref | PRIMARY                | PRIMARY        | 4       | test2.xf_user_User_1.user_id | 1       | 1.00   |   100.00 |     100.00 | Using where              |
|    1 | SIMPLE      | xf_user_follow_Following_3 | range  | PRIMARY,follow_user_id | follow_user_id | 4       | NULL                         | 1       | 0.00   |   100.00 |     100.00 | Using where; Using index |
+------+-------------+----------------------------+--------+------------------------+----------------+---------+------------------------------+---------+--------+----------+------------+--------------------------+
4 rows in set (0.001 sec)

The row estimates are completely broken, but this is because of the fan-out to 3 tables deap is likely wonky.

The core reason the 1st query (stock) is so broken is the use of the xf_user_follow table's follow_user_id index is not referencing previous rows.

Adding a 'force index' on the join fixes this.

set join_cache_level=2;
analyze
SELECT `xf_news_feed`.*, `xf_user_User_1`.*
FROM `xf_news_feed` FORCE INDEX (`event_date`)
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_news_feed`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`user_id` = `xf_user_User_1`.`user_id`)
LEFT JOIN `xf_user_follow` AS `xf_user_follow_Following_3` FORCE INDEX (`follow_user_id`) ON (`xf_user_follow_Following_3`.`user_id` = `xf_user_User_1`.`user_id` AND `xf_user_follow_Following_3`.`follow_user_id` = '1')
WHERE (
    (`xf_news_feed`.`user_id` = 1) OR
    (`xf_news_feed`.`user_id` = 0) OR
    (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` IN ('everyone', 'members')) OR
    (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` = 'followed' AND `xf_user_follow_Following_3`.`user_id` IS NOT NULL)
)
ORDER BY `xf_news_feed`.`event_date` DESC
limit 30;

+------+-------------+----------------------------+--------+----------------+----------------+---------+------------------------------------+---------+--------+----------+------------+--------------------------+
| id   | select_type | table                      | type   | possible_keys  | key            | key_len | ref                                | rows    | r_rows | filtered | r_filtered | Extra                    |
+------+-------------+----------------------------+--------+----------------+----------------+---------+------------------------------------+---------+--------+----------+------------+--------------------------+
|    1 | SIMPLE      | xf_news_feed               | index  | NULL           | event_date     | 4       | NULL                               | 2800000 | 37.00  |   100.00 |     100.00 |                          |
|    1 | SIMPLE      | xf_user_User_1             | eq_ref | PRIMARY        | PRIMARY        | 4       | test2.xf_news_feed.user_id         | 1       | 1.00   |   100.00 |     100.00 |                          |
|    1 | SIMPLE      | xf_user_privacy_Privacy_2  | eq_ref | PRIMARY        | PRIMARY        | 4       | test2.xf_user_User_1.user_id       | 1       | 1.00   |   100.00 |     100.00 | Using where              |
|    1 | SIMPLE      | xf_user_follow_Following_3 | eq_ref | follow_user_id | follow_user_id | 8       | const,test2.xf_user_User_1.user_id | 1       | 0.00   |   100.00 |     100.00 | Using where; Using index |
+------+-------------+----------------------------+--------+----------------+----------------+---------+------------------------------------+---------+--------+----------+------------+--------------------------+
4 rows in set (0.001 sec)

However XenForo's finder API doesn't allow forcing index hints on a join

 

[Xon]

IGNORE INDEX (`PRIMARY`) also works on the xf_user_follow join

join_cache_level is a MariaDb only thing, and it really depends on the version which hits this sort of performance issues.

But in general triggering a BNL join when joining from a very large to a small table is going to cause horrible pain. Going from 37 rows to 3.1 million rows does that.

 

[Kier]

I have code to allow index hints in joins, which is probably too new to make it into 2.2.10 but may well be in 2.2.11.

 

[Feanor]

Did this end up being fixed? Ever since I updated MariaDB, this query has been randomly crashing my server by filling /tmp with a 3.5+ GB temporary table file. It doesn't happen all the time, but once in a while, and I finally realized it was caused by this latest activity query. I have now updated XF to 2.2.13, so I'm hoping this will prevent the issue.

 

[Xon]

This bug looks to have been fixed in XF2.2.12 by adding an index hint on the relevant join