# Slow post deletion after v6+ migrations **URL:** \ **Category:** Troubleshooting\ **Created:** [November 28, 2021, 1:03pm UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623 "2021-11-28T13:03:35Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![dmittr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/dmittr/32/2157_2.png) [@dmittr](https://forum.mattermost.com/u/dmittr)\ **Post date:** [November 28, 2021, 1:03pm UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/1 "2021-11-28T13:03:35Z")
**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 can`t post new messages during someone`s 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? ---
**Author:** ![agnivade](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agnivade/32/3846_2.png) [@agnivade](https://forum.mattermost.com/u/agnivade)\ **Post date:** [November 29, 2021, 3:24am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/2 "2021-11-29T03:24:51Z")
Hey @dmittr Would you be able to post the output of: ```auto EXPLAIN UPDATE Posts SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, ‘$.deleteBy’, ‘buqskqrwmjnhfuqskqrwmjn4ca’) Where Id = ‘c3gazo74m3rkjps71qbtso6twc’ OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’ ``` Thanks. cc @streamer45 ---
**Author:** ![dmittr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/dmittr/32/2157_2.png) [@dmittr](https://forum.mattermost.com/u/dmittr)\ **Post date:** [November 29, 2021, 3:34am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/3 "2021-11-29T03:34:17Z")
```auto 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) ``` ---
**Author:** ![agnivade](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agnivade/32/3846_2.png) [@agnivade](https://forum.mattermost.com/u/agnivade)\ **Post date:** [November 29, 2021, 8:44am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/4 "2021-11-29T08:44:35Z")
Ah, the classic index\_merge appears again. We will take a look into it. Thanks @dmittr ---
**Author:** ![agnivade](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agnivade/32/3846_2.png) [@agnivade](https://forum.mattermost.com/u/agnivade)\ **Post date:** [December 16, 2021, 4:26am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/5 "2021-12-16T04:26:13Z")
@dmittr - Would you be able to run this command and let us know how long it takes: ```auto UPDATE Posts FORCE INDEX (primary) SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, ‘$.deleteBy’, ‘buqskqrwmjnhfuqskqrwmjn4ca’) Where Id = ‘c3gazo74m3rkjps71qbtso6twc’ OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’ ``` ---
**Author:** ![agnivade](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agnivade/32/3846_2.png) [@agnivade](https://forum.mattermost.com/u/agnivade)\ **Post date:** [December 17, 2021, 3:08pm UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/6 "2021-12-17T15:08:08Z")
@dmittr - Ignore, my last comment, I have another query for you to try: ```auto UPDATE Posts SET DeleteAt = 1637998911684, UpdateAt = 1637998911684, Props = JSON_SET(Props, ‘$.deleteBy’, ‘buqskqrwmjnhfuqskqrwmjn4ca’) Where Id = ‘c3gazo74m3rkjps71qbtso6twc’ OR RootId = ‘c3gazo74m3rkjps71qbtso6twc’ ORDER BY Id ``` ---
**Author:** ![dmittr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/dmittr/32/2157_2.png) [@dmittr](https://forum.mattermost.com/u/dmittr)\ **Post date:** [December 23, 2021, 9:34am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/7 "2021-12-23T09:34:54Z")
it works. “ORDER BY Id” makes query as fast as before update. ```auto 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 ``` ---
**Author:** ![agnivade](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agnivade/32/3846_2.png) [@agnivade](https://forum.mattermost.com/u/agnivade)\ **Post date:** [December 24, 2021, 6:07am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/8 "2021-12-24T06:07:38Z")
Thanks for the confirmation @dmittr ! This is fixed now in master and will be available in the next release. ---
**Author:** ![dmittr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/dmittr/32/2157_2.png) [@dmittr](https://forum.mattermost.com/u/dmittr)\ **Post date:** [December 24, 2021, 7:00am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/9 "2021-12-24T07:00:35Z")
Thank`s a lot! We look forward to updates ---
**Author:** ![dmittr](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/dmittr/32/2157_2.png) [@dmittr](https://forum.mattermost.com/u/dmittr)\ **Post date:** [January 17, 2022, 2:09pm UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/10 "2022-01-17T14:09:05Z")
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. ---
**Author:** ![agnivade](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agnivade/32/3846_2.png) [@agnivade](https://forum.mattermost.com/u/agnivade)\ **Post date:** [January 18, 2022, 3:42am UTC](https://forum.mattermost.com/t/slow-post-deletion-after-v6-migrations/12623/11 "2022-01-18T03:42:51Z")
Hi @dmittr - This will be in the 6.4 release. The 6.3 branch was frozen when the original fix was merged.