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?