Some SQL Execute query's make High usage disk I/O and CPU

For feature requests, please see: http://www.mattermost.org/feature-requests/.

For troubleshooting questions, please post in the following format:

Summary
Some query’s make High CPU Load and Disk I/O

SELECT COUNT(DISTINCT(Posts.RootId)) FROM Posts LEFT JOIN ThreadMemberships ON Posts.RootId = ThreadMemberships.PostId LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (((Channels.TeamId = ? OR Channels.TeamId = ?) AND (ThreadMemberships.UserId = ? AND ThreadMemberships.Following = ?)) AND COALESCE(Posts.DeleteAt, 0) = ?) AND Posts.CreateAt > ThreadMemberships.LastViewed

Steps to reproduce
Mattermost Enterprise Edition 6.2.1
Ubuntu 20.04.4 LTS
MariaDB 10.6.5 Source distribution

Expected behavior
Unknown

Observed behavior
Recently, while analyzing the cause of the slow response of the application, found specific query did not end and continued to accumulate.

+----+----------+----------------------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User     | Host                 | db         | Command | Time | State        | Info                                                                                                                                                                                                                                                                                                                                                                                           | Progress |
+----+----------+----------------------+------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 20 | mmuser   | localhost:41896      | mattermost | Execute |  894 | Sending data | SELECT COUNT(DISTINCT(Posts.RootId)) FROM Posts LEFT JOIN ThreadMemberships ON Posts.RootId = ThreadMemberships.PostId LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (((Channels.TeamId = ? OR Channels.TeamId = ?) AND (ThreadMemberships.UserId = ? AND ThreadMemberships.Following = ?)) AND COALESCE(Posts.DeleteAt, 0) = ?) AND Posts.CreateAt > ThreadMemberships.LastViewed |    0.000 |
| 21 | mmuser   | localhost:41898      | mattermost | Execute |  834 | Sending data | SELECT COUNT(DISTINCT(Posts.RootId)) FROM Posts LEFT JOIN ThreadMemberships ON Posts.RootId = ThreadMemberships.PostId LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (((Channels.TeamId = ? OR Channels.TeamId = ?) AND (ThreadMemberships.UserId = ? AND ThreadMemberships.Following = ?)) AND COALESCE(Posts.DeleteAt, 0) = ?) AND Posts.CreateAt > ThreadMemberships.LastViewed |    0.000 |
| 24 | mmuser   | localhost:41904      | mattermost | Execute |  654 | Sending data | SELECT COUNT(DISTINCT(Posts.RootId)) FROM Posts LEFT JOIN ThreadMemberships ON Posts.RootId = ThreadMemberships.PostId LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (((Channels.TeamId = ? OR Channels.TeamId = ?) AND (ThreadMemberships.UserId = ? AND ThreadMemberships.Following = ?)) AND COALESCE(Posts.DeleteAt, 0) = ?) AND Posts.CreateAt > ThreadMemberships.LastViewed |    0.000 |
| 23 | mmuser   | localhost:41902      | mattermost | Sleep   |    2 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                           |    0.000 |
| 27 | mmuser   | localhost:42220      | mattermost | Execute |  534 | Sending data | SELECT COUNT(DISTINCT(Posts.RootId)) FROM Posts LEFT JOIN ThreadMemberships ON Posts.RootId = ThreadMemberships.PostId LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (((Channels.TeamId = ? OR Channels.TeamId = ?) AND (ThreadMemberships.UserId = ? AND ThreadMemberships.Following = ?)) AND COALESCE(Posts.DeleteAt, 0) = ?) AND Posts.CreateAt > ThreadMemberships.LastViewed |    0.000 |
| 31 | mmuser   | localhost:42238      | mattermost | Execute | 1089 | Sending data | SELECT COUNT(DISTINCT(Posts.RootId)) FROM Posts LEFT JOIN ThreadMemberships ON Posts.RootId = ThreadMemberships.PostId LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (((Channels.TeamId = ? OR Channels.TeamId = ?) AND (ThreadMemberships.UserId = ? AND ThreadMemberships.Following = ?)) AND COALESCE(Posts.DeleteAt, 0) = ?) AND Posts.CreateAt > ThreadMemberships.LastViewed |    0.000 |
| 34 | mmuser   | localhost:42258      | mattermost | Execute |  714 | Sending data | SELECT COUNT(DISTINCT(Posts.RootId)) FROM Posts LEFT JOIN ThreadMemberships ON Posts.RootId = ThreadMemberships.PostId LEFT JOIN Channels ON Posts.ChannelId = Channels.Id WHERE (((Channels.TeamId = ? OR Channels.TeamId = ?) AND (ThreadMemberships.UserId = ? AND ThreadMemberships.Following = ?)) AND COALESCE(Posts.DeleteAt, 0) = ?) AND Posts.CreateAt > ThreadMemberships.LastViewed |    0.000 |

I couldn’t find out what that query was for.

and also i checked mattermost log and found something.

{"timestamp":"2022-06-30 11:48:20.689 +09:00","level":"error","msg":"Unable to get user threads","caller":"web/context.go:105","path":"/api/v4/users/o8xyn1dq73yb8keayiotnpjq9y/teams/gj54z9s3gjrndnuaw1gnaaytdh/threads","request_id":"tr8c7o86j3dkbgtds7imehb8jo","ip_addr":"Censored","user_id":"o8xyn1dq73yb8keayiotnpjq9y","method":"GET","err_where":"GetThreadsForUser","http_code":500,"err_details":"failed to get count unread on threads for user id=o8xyn1dq73yb8keayiotnpjq9y: context deadline exceeded"}

This type of error record is also observed.
This issues occurred suddenly two days ago and is still in progress.
What can i do for solve this?

@Linu - Please upgrade to 7.0.0 which has improved this query.

As a side note: we don’t support MariaDB. So you will likely run into issues using this database. I’d suggest moving to MySQL or Postgres.

1 Like

// cc @agarciamontoro

Thank you for information.
But, what is that query used for? just want to know what is doing on that.

It counts the total number of unread threads for a user.

1 Like