查询导致 400% CPU 负载

错误
1

我们最近遇到了 CPU 过载的问题。
我们设法将其定位到一条查询，该查询会持续运行数小时（见下文）。
正在运行的任务是 postmaster - 一旦所有任务都被终止，CPU 就会降低，直到每五分钟重建一次。

SELECT “users”.“id”, ARRAY_AGG(ARRAY [uccm.id, c_msg.id]) AS memberships_with_unread_messages
FROM “users”
         INNER JOIN “user_options” ON “user_options”.“user_id” = “users”.“id”
         INNER JOIN user_chat_channel_memberships uccm ON uccm.user_id = users.id
         INNER JOIN chat_channels cc ON cc.id = uccm.chat_channel_id
         INNER JOIN chat_messages c_msg ON c_msg.chat_channel_id = uccm.chat_channel_id
         LEFT OUTER JOIN chat_mentions c_mentions ON c_mentions.chat_message_id = c_msg.id
WHERE “user_options”.“chat_enabled” = TRUE
  AND “user_options”.“chat_email_frequency” = 1
  AND (users.last_seen_at < ‘2023 - 11 - 15 09:54:26.931723’)
  AND (c_msg.deleted_at IS NULL AND c_msg.user_id <> users.id)
  AND (c_msg.created_at > ‘2023 - 11 - 08 10:09:26.931848’)
  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) AND
       (uccm.user_id = c_mentions.user_id AND uccm.following IS true AND
        cc.chatable_type = ‘Category’

有人能帮忙或提出解决方案吗？

更多信息：

  • 运行 3.2.0.beta4-dev，但这种情况已经持续了至少几个月
  • 这是我们安装的插件列表：

Screenshot 2023-11-15 at 10.43.22
Screenshot 2023-11-15 at 10.43.22837×697 92 KB

谢谢！

2 个赞
2

@andrei 最近在优化聊天查询，更新到最新版本后是否还会发生这种情况？

4 个赞
4

我处理过另一个类似的查询，那个修复是针对该查询的，所以可能在这里没有帮助。不过，更新到最新版本还是值得的，可能还有其他优化。

2 个赞
5

我们确实是最新的。

我认为这是查询中的聊天问题。我们能否从设置中调整一些内容来解决它？

1 个赞
6

我们的一位客户在聊天中启用了 @all 提及功能，涉及两个拥有 16k 用户（共 32k 用户）的频道。那真是一段“有趣”的经历！
在提及了两次 @all 之后，他们很快就将其关闭了。12 小时后，他们（强大的）系统仍然处于高 CPU 负载状态。

问题通过清除 mentions 表中 320k 条违规记录得到解决。

delete from chat_mentions
where chat_message_id in (
  (select chat_message_id
   from chat_mentions
   group by chat_message_id having count(*) > 100)
);

但我们仍然不明白为什么这会引起如此大的问题。
这个查询 仍然在运行。

由于这些行已经消失，explain 命令没有给出好的结果，但很明显它在进行繁重的处理。所有索引都已到位。

1 个赞
7

昨天好像有人提到查询很耗费资源：

2 个赞
8

谢谢 - 我应该学会更有效地搜索这个论坛。

1 个赞
9

这并没有解决我们的问题。

我们也看到聊天消息消失了几分钟才重新出现。

总的来说，我认为聊天还有很多需要改进的地方，尽管我非常喜欢它发展的方向。我最担心的是聊天如何会造成一种无法停止的过载。我们关闭了提及的功能，但那也没有奏效。

有没有办法阻止我提到的查询再次出现？@andrei @JammyDodger

提前感谢

10

嘿 @sok777，感谢你的跟进——我们有几位工程师正在调查此事，一旦有进展我们会在此处向你汇报。感谢你的耐心等待！

2 个赞
11

一个效果很好的技巧是：管理员 - 自定义 - 监视词语，然后将这两个提及都添加到“审查”列表中。

12

所以“all”和“here”前面加上“@”？

谢谢！

1 个赞
13

好的。谢谢！

1 个赞
14

快速更新 @lindsey @RGJ

我们有一条消息是发给 @all 的，我们已将其删除。但此操作并未解决问题。该查询每 5 分钟就会返回一次，耗时很长，以至于导致许多其他查询排队。
一旦我们禁用聊天，CPU 负载就降至 15%。太疯狂了。

15

您需要进入数据库并删除提及记录

psql : delete from chat_mentions where chat_message_id = X
其中 X 是您删除的消息的 ID。

我的消息中描述了一个更通用的查询 此处，请根据需要调整数字。

1 个赞
16

是的，我们已经尝试过这个方法。

供参考，这是我们的统计数据：

users: 239251
chat_channels: 2864
chat_drafts: 205
chat_mentions: 155527 ->7500~
chat_messages: 390453
chat_threads: 25131
chat_message_reactions: 5993
user_chat_channel_memberships: 158480

1 个赞
17

该查询之所以耗时，唯一的原因是 chat_mentions 表中的条目过多。您是否尝试过我发布的通用查询？也许不是 @all，而是提到了另一个大群组。

有问题的查询会扫描过去 7 天内所有聊天中的提及。

您还应该终止该查询的所有现有实例。

18

是的，我们已经解决了它们，但它们还在不断出现。
我们检查了上面提到的所有 100 多个，但都没有发现问题。不确定还有什么其他原因会导致这种情况发生。
在我看来，查询存在根本性问题，不过我也可能弄错。

1 个赞
19

即使提及次数很少，此查询也很可能很慢。我将着手修复它。

2 个赞
20

很高兴能继续从我们的用例中提供支持！

2 个赞
22

@sok777 我需要你在这方面提供一些帮助。我想查看你的网站上 Postgres 使用的查询计划。你能否在你的数据库上运行此命令并分享结果：

EXPLAIN VERBOSE SELECT "users"."id", ARRAY_AGG(ARRAY [uccm.id, c_msg.id]) AS memberships_with_unread_messages
FROM "users"
         INNER JOIN "user_options" ON "user_options"."user_id" = "users"."id"
         INNER JOIN "group_users" ON "group_users"."user_id" = "users"."id"
         INNER JOIN "groups" ON "groups"."id" = "group_users"."group_id"
         INNER JOIN user_chat_channel_memberships uccm ON uccm.user_id = users.id
         INNER JOIN chat_channels cc ON cc.id = uccm.chat_channel_id
         INNER JOIN chat_messages c_msg ON c_msg.chat_channel_id = uccm.chat_channel_id
         LEFT OUTER JOIN chat_mentions c_mentions ON c_mentions.chat_message_id = c_msg.id
WHERE "user_options"."chat_enabled" = TRUE
  AND "user_options"."chat_email_frequency" = 1
  AND (users.last_seen_at < '2023-11-27 08:00:00.0000000')
  AND (c_msg.deleted_at IS NULL AND c_msg.user_id <> users.id)
  AND (c_msg.created_at > '2023-11-20 08:00:00.0000000')
  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) AND
       (
               (uccm.user_id = c_mentions.user_id AND uccm.following IS true AND cc.chatable_type = 'Category') OR
               (cc.chatable_type = 'DirectMessage')
           )
    )
GROUP BY users.id, uccm.user_id
ORDER BY "users"."id" ASC
LIMIT 1000;
1 个赞