# 查询导致 400% CPU 负载
**URL:** https://meta.discourse.org/t/query-causing-400-cpu-load/285542
**Category:** Bug
**Tags:** chat
**Created:** [2023年十一月15日 10:46 UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542 "2023-11-15T10:46:49Z")
**Posts on this page:** 6
**Page:** 2
### Author:  [@sok777](https://meta.discourse.org/u/sok777)
#### Post date: [2023年十一月27日 17:08 UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/23 "2023-11-27T17:08:24Z")
我们最近删除了所有的提及，从那以后 CPU 负载下降到 15-20%，所以效果相当好，但因此上面的查询将不会返回结果。（或者由于我们删除了它，只会返回少量结果）。
---
### Author:  [@andrei](https://meta.discourse.org/u/andrei)
#### Post date: [2023年十一月27日 17:34 UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/24 "2023-11-27T17:34:52Z")
> [@sok777](#):
>
> 但因此上面的查询将不会返回结果
上面的查询在任何情况下都应该返回结果，它以 `EXPLAIN VERBOSE` 开头，因此它会返回一个执行计划。当您有时间时，能否请您复制并粘贴我消息中的确切查询，运行它并分享结果？
---
### Author:  [@sok777](https://meta.discourse.org/u/sok777)
#### Post date: [2023年十一月30日 16:16 UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/25 "2023-11-30T16:16:21Z")
```plaintext
Limit (cost=123.37..22204.48 rows=1000 width=40)
Output: users.id, (array_agg(ARRAY[uccm.id, c_msg.id])), uccm.user_id
-> GroupAggregate (cost=123.37..339399.59 rows=15365 width=40)
Output: users.id, array_agg(ARRAY[uccm.id, c_msg.id]), uccm.user_id
Group Key: users.id, uccm.user_id
-> Nested Loop (cost=123.37..336077.65 rows=417317 width=24)
Output: users.id, uccm.user_id, uccm.id, c_msg.id
Inner Unique: true
-> Merge Join (cost=123.23..271629.01 rows=417317 width=28)
Output: users.id, group_users.group_id, uccm.id, uccm.user_id, c_msg.id
Merge Cond: (user_options.user_id = group_users.user_id)
-> Nested Loop Left Join (cost=121.72..221430.16 rows=132835 width=28)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, c_msg.id
Filter: (((uccm.user_id = c_mentions.user_id) AND (uccm.following IS TRUE) AND ((cc.chatable_type)::text = 'Category'::text)) OR ((cc.chatable_type)::text = 'DirectMessage'::text))
-> Nested Loop (cost=121.32..149930.76 rows=132967 width=42)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.following, cc.chatable_type, c_msg.id
Join Filter: ((c_msg.user_id <> users.id) AND (cc.id = c_msg.chat_channel_id))
-> Nested Loop (cost=120.91..57868.92 rows=21066 width=54)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following, cc.id, cc.chatable_type
Inner Unique: true
-> Nested Loop (cost=120.63..51231.73 rows=21066 width=33)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following
Inner Unique: true
-> Merge Join (cost=120.21..36835.88 rows=21096 width=29)
Output: users.id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following
Inner Unique: true
Merge Cond: (uccm.user_id = users.id)
-> Index Scan using user_chat_channel_unique_memberships on public.user_chat_channel_memberships uccm (cost=0.41..12177.25 rows=21618 width=25)
Output: uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.following, uccm.muted, uccm.desktop_notification_level, uccm.mobile_notification_level, uccm.created_at, uccm.updated_at, uccm.last_unread_mention_when_emailed_id, uccm.join_mode, uccm.last_viewed_at
-> Index Scan using users_pkey on public.users (cost=0.42..23823.53 rows=237544 width=4)
Output: users.id
Filter: (users.last_seen_at < '2023-11-27 08:00:00'::timestamp without time zone)
-> Index Scan using index_user_options_on_user_id on public.user_options (cost=0.42..0.68 rows=1 width=4)
Output: user_options.user_id
Index Cond: (user_options.user_id = users.id)
Filter: (user_options.chat_enabled AND (user_options.chat_email_frequency = 1))
-> Index Scan using chat_channels_pkey on public.chat_channels cc (cost=0.28..0.32 rows=1 width=21)
Output: cc.id, cc.chatable_id, cc.deleted_at, cc.deleted_by_id, cc.featured_in_category_id, cc.delete_after_seconds, cc.chatable_type, cc.created_at, cc.updated_at, cc.name, cc.description, cc.status, cc.user_count, cc.last_message_sent_at, cc.auto_join_users, cc.user_count_stale, cc.type, cc.slug, cc.allow_channel_wide_mentions, cc.messages_count, cc.threading_enabled, cc.last_message_id
Index Cond: (cc.id = uccm.chat_channel_id)
-> Index Scan using index_chat_messages_on_chat_channel_id_and_created_at on public.chat_messages c_msg (cost=0.41..4.19 rows=12 width=16)
Output: c_msg.id, c_msg.chat_channel_id, c_msg.user_id
Index Cond: ((c_msg.chat_channel_id = uccm.chat_channel_id) AND (c_msg.created_at > '2023-11-20 08:00:00'::timestamp without time zone))
Filter: ((c_msg.deleted_at IS NULL) AND ((uccm.last_read_message_id IS NULL) OR (c_msg.id > uccm.last_read_message_id)) AND ((uccm.last_unread_mention_when_emailed_id IS NULL) OR (c_msg.id > uccm.last_unread_mention_when_emailed_id)))
-> Index Only Scan using chat_mentions_index on public.chat_mentions c_mentions (cost=0.40..0.52 rows=1 width=8)
Output: c_mentions.chat_message_id, c_mentions.user_id, c_mentions.notification_id
Index Cond: (c_mentions.chat_message_id = c_msg.id)
-> Index Only Scan using index_group_users_on_user_id_and_group_id on public.group_users (cost=0.42..44019.59 rows=760157 width=8)
Output: group_users.user_id, group_users.group_id
-> Index Only Scan using groups_pkey on public.groups (cost=0.14..0.16 rows=1 width=4)
Output: groups.id
Index Cond: (groups.id = group_users.group_id)
```
(51 行)
---
### Author:  [@andrei](https://meta.discourse.org/u/andrei)
#### Post date: [2023年十二月1日 20:00 UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/26 "2023-12-01T20:00:57Z")
@sok777 感谢您分享查询计划！非常有帮助。
您的实例包含大量用户 _以及_ 大量 `user_chat_channel_memberships` 记录（正如您在此处 [here](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/16) 分享的那样）。
在您分享测试计划之前，我曾尝试在用户量大的站点上重现问题，但查询运行良好。现在，我看到那些站点没有大量的 `user_chat_channel_memberships`，这很可能是您的站点特有的问题，并且是导致您遇到如此严重问题的根源。
据我所知，您现在没有问题，因为您在某个时候删除了所有提及，但我预计即使提及量很少，此查询将来也会再次出现问题。因此，无论如何我都会对其进行优化。
我目前正在着手修复导致数据库创建大量 `chat_mention` 记录的根本问题。在此期间，请务必在“watched words”中保留 @all 和 @here。我会在处理完这个问题后在此发布更新，然后我会确保优化此查询。
---
### Author:  [@andrei](https://meta.discourse.org/u/andrei)
#### Post date: [2024年三月28日 19:32 UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/27 "2024-03-28T19:32:56Z")
> [@andrei](#):
>
> 我目前正在着手解决导致数据库中创建大量 `chat_mention` 记录的根本问题。在解决此问题的过程中，请务必将 @所有人 和 @此处 保持在监视词列表中。我会在处理完之后在此处发布更新，然后我会确保优化此查询。
根本问题已通过以下方式解决：
>
>
> At the moment, when someone is mentioning a group, or using \`here\` or \`all\` ment…ion, we create a \`chat\_mention\` record \_per user\_. What we want instead is to have special kinds of mentions, so we can create only one \`chat\_mention\` record in such cases. This PR implements that.
>
> Note, that such mentions will still have N related notifications, one notification per a user. We don't expect we'll have performance problems on the notifications side, but if at some point we do, we should be able to solve them on the side of notifications (notifications are handled in jobs, also some little delays with the notifications are acceptable, so we can make sure notifications are properly queued, and that processing of every notification is fast enough to make delays small enough).
>
> The preparation work for this PR was done in fbd24fa6, where we make it possible for one mention to have several related notifications.
>
> A pretty tricky part of this PR is schema and data migration, I've explained related details inline on the migration files.
今后不应再出现此类性能问题，也无需将大规模提及保留在监视词中。
关于这一点：
> [@andrei](#):
>
> 据我所知，您现在没有问题，因为您在某个时候删除了所有提及，但我预计此查询在某个时候会再次出现问题，即使提及数量很少。
似乎没有人抱怨此查询，所以我没有对其进行优化。如有问题，请随时报告。
---
### Author:  [@j.jaffeux](https://meta.discourse.org/u/j.jaffeux)
#### Post date: [2024年三月29日 08:44 UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/29 "2024-03-29T08:44:33Z")
[上一頁](https://meta.discourse.org/t/query-causing-400-cpu-load/285542.md?page=1)