Slow post deletion after v6+ migrations

Troubleshooting
1

Summary
Slow post deletion after upgrading to v.6

Steps to reproduce
Centos 8.2, 4 cores, 16Gb of Ram, ssd storage
Mattermost 6.1
Server version: 8.0.21
18+ million records in Posts (27Gb)
Baisc message deletion procedure from client or web

Expected behavior
As it was before v6 upgrade.

Observed behavior
Users cant post new messages during someones deletion.
Mysql process list show following qeury during more than 120 sec:
UPDATE Posts SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, ‘$.deleteBy’, ‘buqskqrwmjnhfuqskqrwmjn4ca’) Where Id = ‘c3gazo74m3rkjps71qbtso6twc’ OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’

PS: Looks like dropping index idx_posts_root_id during v6 migration was a bad idea, because there is a huge time difference between query with RootId and without it. I have tried to execute same UPDATE without “OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’” and got result instantly. As temporary solution i have to restrict DELETE method on nginx for /api/v4/posts/ URL.
Is it safe to recreate idx_posts_root_id by myself?
Is there some plans not to search by RootId on UPDATE next server versions?

2

Hey @dmittr

Would you be able to post the output of:

EXPLAIN UPDATE Posts SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, ‘$.deleteBy’, ‘buqskqrwmjnhfuqskqrwmjn4ca’) Where Id = ‘c3gazo74m3rkjps71qbtso6twc’ OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’

Thanks.

cc @streamer45

3 
mysql> explain UPDATE Posts SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, '$.deleteBy', 'buqskqrwmjnhfuqskqrwmjn4ca') Where Id = 'c3gazo74m3rkjps71qbtso6twc' OR RootId = 'c3gazo74m3rkjps71qbtso6twc';
+----+-------------+-------+------------+-------------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                       | key                                 | key_len | ref  | rows | filtered | Extra                                                                               |
+----+-------------+-------+------------+-------------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
|  1 | UPDATE      | Posts | NULL       | index_merge | PRIMARY,idx_posts_root_id_delete_at | idx_posts_root_id_delete_at,PRIMARY | 107,106 | NULL |    2 |   100.00 | Using sort_union(idx_posts_root_id_delete_at,PRIMARY); Using where; Using temporary |
+----+-------------+-------+------------+-------------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
4

Ah, the classic index_merge appears again.

We will take a look into it. Thanks @dmittr

5

@dmittr - Would you be able to run this command and let us know how long it takes:

UPDATE Posts FORCE INDEX (primary) SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, ‘$.deleteBy’, ‘buqskqrwmjnhfuqskqrwmjn4ca’) Where Id = ‘c3gazo74m3rkjps71qbtso6twc’ OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’
6

@dmittr - Ignore, my last comment, I have another query for you to try:

UPDATE Posts SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, ‘$.deleteBy’, ‘buqskqrwmjnhfuqskqrwmjn4ca’) Where Id = ‘c3gazo74m3rkjps71qbtso6twc’ OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’ ORDER BY Id
7

it works. “ORDER BY Id” makes query as fast as before update.

time mysql mattermost -e 'UPDATE Posts SET DeleteAt = 1640251149684, UpdateAt = 1640251149684, Props = JSON_SET(Props, "$.deleteBy", "buqskqrwmjnhfuqskqrwmjn4ca") Where Id = "pzdhmmdwnfg85nimnseepjuy1y" OR RootId = "pzdhmmdwnfg85nimnseepjuy1y"  ORDER BY Id'

real	0m0.016s
user	0m0.007s
sys	0m0.004s
8

Thanks for the confirmation @dmittr ! This is fixed now in master and will be available in the next release.

1 Like
9

Thank`s a lot!
We look forward to updates

10

looks like there is no effect with v6.3.0, same behavior as brefore. Terribly slow deletion and like-as-usual fast deleting messages.

‘show full processlist’ displays “Searching rows for update…” for 1-2 min. Users messages not inserting into Posts during this period.

11

Hi @dmittr - This will be in the 6.4 release. The 6.3 branch was frozen when the original fix was merged.