Sidekiq から2つのジョブが送信され、完了までに非常に時間がかかっているようです。全体のプロセスは同じように見えますが、RUNNING ステータスで表示されているジョブが2つあります。これらのジョブは現在6時間実行されており、最初のクエリに対して EXPLAIN ANALYZE を実行しようとするだけでも、Postgres が CLI でフリーズしています。
これらのクエリの実行にこれほど時間がかかる原因として考えられることは何かご存知でしょうか？
そのクエリは終了させても構いません。システムが後で再試行します。
「いいね！」 5
実際には、それ以前にその処理を行っていました。再インデックスを完了させるためにジョブを強制終了させたため、これはその後に再起動したものです。
今朝、ジョブは夜間に完了し、再び実行されているようです。現在のステータスは以下の通りです：
このサブクエリは、当社のインスタンスで約 13,000 行を返します：
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
その後、84,000 行を持つ user_badges テーブルに LEFT JOIN されています。最後の条件 WHERE ub.badge_id = 15 AND q.user_id IS NULL のどこかがこのクエリを破綻させているようです。WHERE 句を省略すれば、実行は妥当な時間（約 20 秒）で完了しますが、WHERE ub.badge_id = 15 だけでも含めると、このクエリに対する EXPLAIN の実行さえも妥当な時間で行うことができません。EXPLAIN は数分間結果なしでフリーズしたままです。実際のクエリの実行は数時間続いています。このクエリを最適化する方法はありますか？
「いいね！」 2
昨晩からメタでトピックを読んでいると、特に大規模なデータベースの場合、8 時間以上ジョブを実行すべきではないという見解のようです。
しかし、それをさらに改善するために何ができるかは確信が持てません。
ただし、EXPLAIN が何らかの理由でフリーズして何も表示されないというのは、私にとって信じがたいことです。
「いいね！」 1
このファイルをご覧ください：discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
現在、以下のようなコードが実行され、クエリが停止しています。最初の結合を LEFT JOIN から INNER JOIN に変更すると、クエリは即座に実行されます。これに LEFT JOIN である必要がある理由はありますか？
sql = <<~SQL
DELETE FROM user_badges
WHERE id IN (
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
#{badge.query}
) q ON q.user_id = ub.user_id
#{post_clause}
WHERE ub.badge_id = :id AND q.user_id IS NULL
)
SQL
「いいね！」 1
これらのバッジクエリを高速化することは可能でしょうか？
このクエリも問題がありそうです。おそらく週次クリーンアップジョブからのものだと思います：
UPDATE posts
SET percent_rank = X.percent_rank
FROM (
SELECT posts.id, Y.percent_rank
FROM posts
JOIN (
SELECT id, percent_rank() \
OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank \
FROM posts \
) Y ON Y.id = posts.id \
JOIN topics ON posts.topic_id = topics.id \
WHERE (posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank) \
LIMIT 20000 \
) AS X \
WHERE posts.id = X.id
このクエリの実行計画を見ると、posts テーブルの 2600 万行すべてをソートしようとしていることがわかります。このクエリでどの手法が使われるのかは断定できませんが、現在の待機状態が「DataFileRead」であることから、ディスクへのアクセスが発生しているのではないかと推測されます。
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Update on posts (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Nested Loop (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Subquery Scan on x (cost=8312704.18..8464468.35 rows=20000 width=48)
-> Limit (cost=8312704.18..8464268.35 rows=20000 width=12)
-> Hash Join (cost=8312704.18..209445240.14 rows=26540908 width=12)
Hash Cond: (posts_1.topic_id = topics.id)
-> Nested Loop (cost=8277347.60..209340213.36 rows=26540908 width=16)
-> WindowAgg (cost=8277347.16..8809352.84 rows=26600284 width=24)
-> Sort (cost=8277347.16..8343847.87 rows=26600284 width=16)
Sort Key: posts_2.topic_id, posts_2.score DESC
-> Seq Scan on posts posts_2 (cost=0.00..4542277.84 rows=26600284 width=16)
-> Index Scan using posts_pkey on posts posts_1 (cost=0.44..7.52 rows=1 width=16)
Index Cond: (id = posts_2.id)
Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) <> percent_rank))
-> Hash (cost=23871.05..23871.05 rows=918842 width=4)
-> Index Only Scan using topics_pkey on topics (cost=0.42..23871.05 rows=918842 width=4)
-> Index Scan using posts_pkey on posts (cost=0.44..8.14 rows=1 width=781)
Index Cond: (id = x.id)
JIT:
Functions: 24
Options: Inlining true, Optimization true, Expressions true, Deforming true
(21 rows)
Discourse を動かすには、お使いのデータベースのリソースが不足している可能性があり、ここでの対応は逆流に逆らって泳ぐようなものだとの強い感覚があります。
- サーバーの正確なスペック（CPU、ハードディスクのメーカーとモデル）は何ですか？
- VM の正確なスペックは何ですか？
これらのクエリは確かに高負荷ですが、私たちは大規模なフォーラム（例：https://boards.straightdope.com/、約 2200 万件の投稿）を多数ホストしており、そのインスタンス上でこれらのクエリを問題なく実行できています。
「いいね！」 1
専用サーバーには以下の仕様が搭載されています：
AMD Ryzen 7 3800X
64 GB ECC RAM @ 2666 Mhz
2 x 1.2 TB Intel P3600 NVMe SSD (ZFS RAID 1)
Discourse を実行している VM には、8 コアの CPU と 32 GB の RAM が割り当てられています。
最初のクエリの問題、あるいは少なくともクエリオプティマイザに正しい判断を促す方法を発見したと思います。以下は、16 時間以上も完了しなかったクエリです（これは「最初の引用」バッジに関するものです）：
SELECT ub.id \n FROM user_badges ub \n LEFT JOIN ( \n SELECT ids.user_id, q.post_id, p3.created_at granted_at \n FROM \n ( \n SELECT p1.user_id, MIN(q1.id) id \n FROM quoted_posts q1 \n JOIN badge_posts p1 ON p1.id = q1.post_id \n JOIN badge_posts p2 ON p2.id = q1.quoted_post_id \n WHERE (TRUE OR ( p1.id IN (-1) )) \n GROUP BY p1.user_id \n ) ids \n JOIN quoted_posts q ON q.id = ids.id \n JOIN badge_posts p3 ON q.post_id = p3.id \n \n ) q ON q.user_id = ub.user_id \n AND (q.post_id = ub.post_id OR NOT TRUE) \n WHERE ub.badge_id = 15 AND q.user_id IS NULL
適切な場所に単一の ORDER BY 行を追加すると、このクエリは数秒で完了するようになりました：
SELECT ub.id \n FROM user_badges ub \n LEFT JOIN ( \n SELECT ids.user_id, q.post_id, p3.created_at granted_at \n FROM \n ( \n SELECT p1.user_id, MIN(q1.id) id \n FROM quoted_posts q1 \n JOIN badge_posts p1 ON p1.id = q1.post_id \n JOIN badge_posts p2 ON p2.id = q1.quoted_post_id \n WHERE (TRUE OR ( p1.id IN (-1) )) \n GROUP BY p1.user_id \n ) ids \n JOIN quoted_posts q ON q.id = ids.id \n JOIN badge_posts p3 ON q.post_id = p3.id\n ORDER BY ids.user_id \n \n ) q ON q.user_id = ub.user_id \n AND (q.post_id = ub.post_id OR NOT TRUE) \n WHERE ub.badge_id = 15 AND q.user_id IS NULL
システムが適切な場所でこのソートを行うほど賢いはずだと思っていたのですが、そうではないようです……。とはいえ、現時点では修正は比較的簡単のようです。
percent_rank に関する他のクエリの調査はまだ本格的に始めていません。
「いいね！」 2
統計が悪いと計画が外れることがあります。ごく例外的なケースでは完全な真空（full vacuum）が役立つことがあり、インポート後は最小限の真空（minimal vacuum）を強くお勧めします。あなたは両方とも行ったと思います。
なぜホスト上で直接 Docker を使うのではなく、VM 上で実行しているのでしょうか？
「いいね！」 1
もしよろしければ、Straight Dopeに割り当てられているリソースはどのくらいでしょうか。また、私たちが2700万件の投稿で対応しているように、数時間を要するような業務は存在しますか。
はい、VACUUM ANALYZE を数回実行しました。統計情報は正しいはずですが、複数の再構築、Postgres のチューニング調整、VACUUM を行っても、最適な選択ができないようです。
このホストマシンでは他の VM も実行していますが、現時点ではリソースに余裕があるため、Discourse をテストするためのシステムをここに構築しました。
大規模インスタンスから /sidekiq/scheduler を確認しています。
そして
FULL VACUUM を実行しましたか？
当社の DB サーバーは、ハードウェア性能の面で貴社と概ね同様です（ただし、より大きな RAID アレイを備えているため、IO は高速です）。ただし、当社は仮想化を一切使用していません。これが大きな違いです。
「いいね！」 2
いいえ、まだ行っていません。試してみて、挙動に変化があるか確認します。
VM 上で動作することで何らかのパフォーマンス低下があるのは確かですが、ハードウェアを過度に負荷させるような要因はありません。他社ソフトウェアからデータをインポートした際、8 コアすべてで複数のインポートプロセスを並行して実行し、60〜70% の使用率を達成できました。
現在、これらのジョブが待機して処理に時間がかかっている状態でも、通常、負荷平均は 2〜3 を超えることはなく、利用可能な CPU のすべてを消費しているわけではありません。
「いいね！」 2
フルバキュームは、大規模な移行後に効果が見られた事例があり、それがどのような影響を与えるか興味深いです。
「いいね！」 3
現在実行中です。
「いいね！」 2
週次ジョブの一部と思われる percent_rank クエリについて、あなたの大規模インスタンスでの EXPLAIN の出力結果はどうなっていますか？私のインスタンスでは、そのクエリのコストが 800 万以上と報告されており、少し恐ろしく思えます。
app.yml 内の PostgreSQL 調整値について推奨事項はありますか？現在は以下の値を使用しています：
shared_buffers: 16GB
work_mem: 512MB
VACUUM FULL は完了しましたが、クエリパフォーマンスには影響を与えなかったようです。ORDER BY 句を追加しない限り、バッジクエリは何時間も実行され続けるように見えますし、percent_rank クエリも完了せずに 2 時間以上実行され続けています。おそらく「最初の引用」バッジの SQL 定義を変更する必要があり、その後 percent_rank クエリを修正できるか検討する必要があります。
この EXPLAIN 結果に基づいて、percent_rank クエリに対する提案はありますか？
UPDATE posts \n SET percent_rank = X.percent_rank \n FROM ( \n SELECT posts.id, Y.percent_rank \n FROM posts \n JOIN ( \n SELECT id, percent_rank() \n OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank \n FROM posts \n ) Y ON Y.id = posts.id \n JOIN topics ON posts.topic_id = topics.id \n WHERE (posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank) \n LIMIT 20000 \n ) AS X \n WHERE posts.id = X.id
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Update on posts (cost=6511439.82..6944253.09 rows=20000 width=828)
-> Nested Loop (cost=6511439.82..6944253.09 rows=20000 width=828)
-> Subquery Scan on x (cost=6511439.38..6784765.09 rows=20000 width=48)
-> Limit (cost=6511439.38..6784565.09 rows=20000 width=12)
-> Nested Loop (cost=6511439.38..374544016.70 rows=26949684 width=12)
-> Nested Loop (cost=6511438.96..192122439.64 rows=26949684 width=16)
-> WindowAgg (cost=6511438.52..7050906.24 rows=26973386 width=24)
-> Sort (cost=6511438.52..6578871.98 rows=26973386 width=16)
Sort Key: posts_2.topic_id, posts_2.score DESC
-> Seq Scan on posts posts_2 (cost=0.00..2721272.86 rows=26973386 width=16)
-> Index Scan using posts_pkey on posts posts_1 (cost=0.44..6.84 rows=1 width=16)
Index Cond: (id = posts_2.id)
Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) <> percent_rank))
-> Index Only Scan using topics_pkey on topics (cost=0.42..6.77 rows=1 width=4)
Index Cond: (id = posts_1.topic_id)
-> Index Scan using posts_pkey on posts (cost=0.44..7.97 rows=1 width=784)
Index Cond: (id = x.id)
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming true
(20 rows)
「いいね！」 1
制限値を調整してみてください。例えば、制限値を1000に設定すれば、これで十分な速度になるかもしれません。
制限を変更しても、クエリプラン（コストなど）はあまり変わらないようです。問題は、このクエリが操作を実行する前に、posts テーブル全体（当社の場合は約 2,650 万行）をソートしなければならない点にあります。ここでインデックスの活用機会があるかもしれません。現時点では、posts テーブルのどのインデックスにも score カラムが含まれていないようです。
ランキングはトピックごとに行われるもので、セット全体をランキングするものではありません。
トピック ID で条件を絞り込むこともできます。例えば、WHERE topic_id < 1000、2000、10000 のようにです。おそらく、初期更新が完了すれば、これ以降はより高速に実行されるでしょう。
「いいね！」 1