Slow query in getParentsPosts

[vpecinka]

Hi,

as I posted comment in #6828 as the query is very slow in large channel. The query:

SELECT 
      q2.*
    FROM
      Posts q2
        INNER JOIN
      (SELECT DISTINCT
        q3.RootId
      FROM
        (SELECT
          RootId
        FROM
          Posts
        WHERE
          ChannelId = :ChannelId1
            AND DeleteAt = 0
        ORDER BY CreateAt DESC
        LIMIT :Limit OFFSET :Offset) q3
      WHERE q3.RootId != '') q1
      ON q1.RootId = q2.Id OR q1.RootId = q2.RootId
    WHERE
      ChannelId = :ChannelId2
        AND DeleteAt = 0
    ORDER BY CreateAt

used in https://github.com/mattermost/mattermost-server/blob/e067272e16b962d04f9c83bb1cbd739ac1b6299c/store/sqlstore/post_store.go#L757

leads to filesort and very slow query in large channel (4mio+ posts). The EXPLAIN command tell:

MySQL [mattermost]> explain SELECT q2.* FROM Posts q2 INNER JOIN (SELECT DISTINCT q3.RootId FROM (SELECT RootId FROM Posts WHERE ChannelId = 'onbayhh4mj81zpsz111ew3qq8r' AND DeleteAt = 0 ORDER BY CreateAt DESC LIMIT 100 OFFSET 0) q3 WHERE q3.RootId != '') q1 ON q1.RootId = q2.Id OR q1.RootId = q2.RootId WHERE ChannelId = 'onbayhh4mj81zpsz111ew3qq8r' AND DeleteAt = 0 ORDER BY CreateAt;
+----+-------------+------------+------------+------+--------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+-------------+---------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys                                                                                                                              | key                                      | key_len | ref         | rows    | filtered | Extra                                              |
+----+-------------+------------+------------+------+--------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+-------------+---------+----------+----------------------------------------------------+
|  1 | PRIMARY     | q2         | NULL       | ref  | PRIMARY,idx_posts_delete_at,idx_posts_channel_id,idx_posts_root_id,idx_posts_channel_id_update_at,idx_posts_channel_id_delete_at_create_at | idx_posts_channel_id_delete_at_create_at | 116     | const,const | 4210138 |   100.00 | Using temporary; Using filesort                    |
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL                                                                                                                                       | NULL                                     | NULL    | NULL        |      89 |    19.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | <derived3> | NULL       | ALL  | NULL                                                                                                                                       | NULL                                     | NULL    | NULL        |     100 |    90.00 | Using where; Using temporary                       |
|  3 | DERIVED     | Posts      | NULL       | ref  | idx_posts_delete_at,idx_posts_channel_id,idx_posts_channel_id_update_at,idx_posts_channel_id_delete_at_create_at                           | idx_posts_channel_id_delete_at_create_at | 116     | const,const | 4210138 |   100.00 | Using where                                        |
+----+-------------+------------+------------+------+--------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+-------------+---------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

MySQL [mattermost]> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `mattermost`.`q2`.`Id` AS `Id`,`mattermost`.`q2`.`CreateAt` AS `CreateAt`,`mattermost`.`q2`.`UpdateAt` AS `UpdateAt`,`mattermost`.`q2`.`EditAt` AS `EditAt`,`mattermost`.`q2`.`DeleteAt` AS `DeleteAt`,`mattermost`.`q2`.`IsPinned` AS `IsPinned`,`mattermost`.`q2`.`UserId` AS `UserId`,`mattermost`.`q2`.`ChannelId` AS `ChannelId`,`mattermost`.`q2`.`RootId` AS `RootId`,`mattermost`.`q2`.`ParentId` AS `ParentId`,`mattermost`.`q2`.`OriginalId` AS `OriginalId`,`mattermost`.`q2`.`Message` AS `Message`,`mattermost`.`q2`.`Type` AS `Type`,`mattermost`.`q2`.`Props` AS `Props`,`mattermost`.`q2`.`Hashtags` AS `Hashtags`,`mattermost`.`q2`.`Filenames` AS `Filenames`,`mattermost`.`q2`.`FileIds` AS `FileIds`,`mattermost`.`q2`.`HasReactions` AS `HasReactions` from `mattermost`.`Posts` `q2` join (/* select#2 */ select distinct `q3`.`RootId` AS `RootId` from (/* select#3 */ select `mattermost`.`Posts`.`RootId` AS `RootId` from `mattermost`.`Posts` where ((`mattermost`.`Posts`.`DeleteAt` = 0) and (`mattermost`.`Posts`.`ChannelId` = 'onbayhh4mj81zpsz111ew3qq8r')) order by `mattermost`.`Posts`.`CreateAt` desc limit 0,100) `q3` where (`q3`.`RootId` <> '')) `q1` where ((`mattermost`.`q2`.`DeleteAt` = 0) and (`mattermost`.`q2`.`ChannelId` = 'onbayhh4mj81zpsz111ew3qq8r') and ((`q1`.`RootId` = `mattermost`.`q2`.`Id`) or (`q1`.`RootId` = `mattermost`.`q2`.`RootId`))) order by `mattermost`.`q2`.`CreateAt` |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Wouldn't be better to introduce "lazy parent posts" loading than this complex query? E.g. to display posts (rootId detects if parent exists) and then with 2nd bulk query get the parents posts for given LIMIT / OFFSET? Leads to more queries but cheaper ones...

[jasonblais]

Thanks for the detailed notes! Have shared with the team as well to see if they have additional insight.

[lieut-data]

@vpecinka, thanks for the analysis! You're right that this is a hotspot in our current post loading strategy, and we're starting to pivot to exactly your suggestion, e.g.: 20f03f7#diff-3839c6aa88b8e4cfee8869d3da6d20ad.

Longer term, the hope is to remove the need to load the root posts + threads in the first place, and truly load them on demand client-side when the RHS is opened. At the moment, we fetch the /entire/ thread for each post in the requested window!

[jdnorthrup]

Hi, is this still an open issue? I am evaluating MM for a large group and it seems like loading channels with lots of messages is still slow. Thanks for any info!

[jasonblais]

@jdnorthrup Which Mattermost server are you running?

[jdnorthrup]

Team Edition 5.20.1 Db schema 5.20.0 mysql Thx!

…

On Apr 8, 2020, at 12:44 PM, Jason Blais ***@***.***> wrote:  @jdnorthrup Which Mattermost server are you running? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[jasonblais]

Thanks! There had been a fix applied but it was on an earlier version. It wasn't directly related to this report, though, so at this point the issue is indeed open.

How large of a group is it, if you don't mind me asking @jdnorthrup?

[grundleborg]

An update on this issue generally - we're gradually getting closer to having it resolved. The initial fix for some aspects of it is already present in 5.22, the next part of the story is here mattermost/mattermost-webapp#5051 and then we will be quite close to completely eradicating the remaining unnecessary root post/thread loading.

[ajellman]

@jdnorthrup If you would like some assistance in configuring the server properly, please send me a direct message on the Mattermost community server (community.mattermost.com) at andy.ellman