CPU負荷を400%引き起こすクエリ

バグ
1

CPU負荷が過負荷になっています。
数時間実行され続ける1つのクエリに起因していることが判明しました（下記参照）。
実行されているタスクはpostmasterですが、すべてを終了するとCPU使用率が低下し、5分ごとに再構築されます。

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 メンションを有効にしたクライアントがおり、16,000 人のユーザーがいる 2 つのチャンネルがありました。それは楽しかったです！
19 回の全員へのメンションの後、彼らはすぐにそれを無効にしました。12 時間後、彼らの（強力な）システムは依然として高い CPU 負荷がかかっていました。

問題は、メンションテーブルから 320,000 件の該当レコードを削除することで解決されました。

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

うまくいくトリックの1つは、管理者 - カスタマイズ - 監視対象の単語に移動し、両方のメンションを「検閲」リストに追加することです。

12

「すべて」と「ここ」にプレフィックス「@」を付けるということですか？

ありがとうございます！

「いいね！」 1
13

素晴らしい。ありがとうございます！

「いいね！」 1
14

簡単なアップデートです @lindsey @RGJ

@all宛のメッセージが1件あり、削除しましたが、問題は解決していません。クエリは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