ユーザープロフィールの読み込みが遅い

貢献 特徴
1

現在のソフトウェアから Discourse へユーザー投稿のインポートを完了しました。その際、いくつかのユーザーページが読み込みに非常に時間がかかる（数秒）ことに気づきました。これは遅いクエリが原因のようです。この問題をトラブルシューティングし、ボトルネックを特定するにはどうすればよいでしょうか？

以下に、私たちの環境に関する追加情報を記載します。

サーバー仕様：8 コア、32 GB RAM、ZFS を搭載した NVMe SSD ストレージ

掲示板には約 2500 万件の投稿があります。その大部分は非公開の会話であり、公開されているのは約 1050 万件です。
多数の巨大トピック（メガトピック）があります（私たちは執筆サイトなので、個々のストーリーは時間とともにゆっくりと発展し、多くの投稿が蓄積される傾向があるため）。
これが要因である可能性はありますが、それを確認する方法がわかりません。

以下は問題の兆候が見られるクエリの一例です。内蔵のロードアナライザーでは 5000ms を超える実行時間が観測されています。

SELECT "topic_links"."id" AS t0_r0, "topic_links"."topic_id" AS t0_r1, "topic_links"."post_id" AS t0_r2, "topic_links"."user_id" AS t0_r3, "topic_links"."url" AS t0_r4, "topic_links"."domain" AS t0_r5, "topic_links"."internal" AS t0_r6, "topic_links"."link_topic_id" AS t0_r7, "topic_links"."created_at" AS t0_r8, "topic_links"."updated_at" AS t0_r9, "topic_links"."reflection" AS t0_r10, "topic_links"."clicks" AS t0_r11, "topic_links"."link_post_id" AS t0_r12, "topic_links"."title" AS t0_r13, "topic_links"."crawled_at" AS t0_r14, "topic_links"."quote" AS t0_r15, "topic_links"."extension" AS t0_r16, "topics"."id" AS t1_r0, "topics"."title" AS t1_r1, "topics"."last_posted_at" AS t1_r2, "topics"."created_at" AS t1_r3, "topics"."updated_at" AS t1_r4, "topics"."views" AS t1_r5, "topics"."posts_count" AS t1_r6, "topics"."user_id" AS t1_r7, "topics"."last_post_user_id" AS t1_r8, "topics"."reply_count" AS t1_r9, "topics"."featured_user1_id" AS t1_r10, "topics"."featured_user2_id" AS t1_r11, "topics"."featured_user3_id" AS t1_r12, "topics"."deleted_at" AS t1_r13, "topics"."highest_post_number" AS t1_r14, "topics"."like_count" AS t1_r15, "topics"."incoming_link_count" AS t1_r16, "topics"."category_id" AS t1_r17, "topics"."visible" AS t1_r18, "topics"."moderator_posts_count" AS t1_r19, "topics"."closed" AS t1_r20, "topics"."archived" AS t1_r21, "topics"."bumped_at" AS t1_r22, "topics"."has_summary" AS t1_r23, "topics"."archetype" AS t1_r24, "topics"."featured_user4_id" AS t1_r25, "topics"."notify_moderators_count" AS t1_r26, "topics"."spam_count" AS t1_r27, "topics"."pinned_at" AS t1_r28, "topics"."score" AS t1_r29, "topics"."percent_rank" AS t1_r30, "topics"."subtype" AS t1_r31, "topics"."slug" AS t1_r32, "topics"."deleted_by_id" AS t1_r33, "topics"."participant_count" AS t1_r34, "topics"."word_count" AS t1_r35, "topics"."excerpt" AS t1_r36, "topics"."pinned_globally" AS t1_r37, "topics"."pinned_until" AS t1_r38, "topics"."fancy_title" AS t1_r39, "topics"."highest_staff_post_number" AS t1_r40, "topics"."featured_link" AS t1_r41, "topics"."reviewable_score" AS t1_r42, "topics"."image_upload_id" AS t1_r43, "posts"."id" AS t2_r0, "posts"."user_id" AS t2_r1, "posts"."topic_id" AS t2_r2, "posts"."post_number" AS t2_r3, "posts"."raw" AS t2_r4, "posts"."cooked" AS t2_r5, "posts"."created_at" AS t2_r6, "posts"."updated_at" AS t2_r7, "posts"."reply_to_post_number" AS t2_r8, "posts"."reply_count" AS t2_r9, "posts"."quote_count" AS t2_r10, "posts"."deleted_at" AS t2_r11, "posts"."off_topic_count" AS t2_r12, "posts"."like_count" AS t2_r13, "posts"."incoming_link_count" AS t2_r14, "posts"."bookmark_count" AS t2_r15, "posts"."score" AS t2_r16, "posts"."reads" AS t2_r17, "posts"."post_type" AS t2_r18, "posts"."sort_order" AS t2_r19, "posts"."last_editor_id" AS t2_r20, "posts"."hidden" AS t2_r21, "posts"."hidden_reason_id" AS t2_r22, "posts"."notify_moderators_count" AS t2_r23, "posts"."spam_count" AS t2_r24, "posts"."illegal_count" AS t2_r25, "posts"."inappropriate_count" AS t2_r26, "posts"."last_version_at" AS t2_r27, "posts"."user_deleted" AS t2_r28, "posts"."reply_to_user_id" AS t2_r29, "posts"."percent_rank" AS t2_r30, "posts"."notify_user_count" AS t2_r31, "posts"."like_score" AS t2_r32, "posts"."deleted_by_id" AS t2_r33, "posts"."edit_reason" AS t2_r34, "posts"."word_count" AS t2_r35, "posts"."version" AS t2_r36, "posts"."cook_method" AS t2_r37, "posts"."wiki" AS t2_r38, "posts"."baked_at" AS t2_r39, "posts"."baked_version" AS t2_r40, "posts"."hidden_at" AS t2_r41, "posts"."self_edits" AS t2_r42, "posts"."reply_quoted" AS t2_r43, "posts"."via_email" AS t2_r44, "posts"."raw_email" AS t2_r45, "posts"."public_version" AS t2_r46, "posts"."action_code" AS t2_r47, "posts"."locked_by_id" AS t2_r48, "posts"."image_upload_id" AS t2_r49 FROM "topic_links" INNER JOIN "topics" ON "topics"."id" = "topic_links"."topic_id" AND ("topics"."deleted_at" IS NULL) INNER JOIN "posts" ON "posts"."id" = "topic_links"."post_id" AND ("posts"."deleted_at" IS NULL) WHERE (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (3,4))) AND "topic_links"."user_id" = 7237 AND "topic_links"."internal" = FALSE AND "topic_links"."reflection" = FALSE AND "topic_links"."quote" = FALSE ORDER BY clicks DESC, topic_links.created_at DESC LIMIT 6

ユーザーページを読み込んでいる間サーバーを監視すると、クエリの実行中に PostgreSQL プロセスの CPU 使用率が急上昇しているようです。インポートプロセスのために MySQL サポートを追加した以外、インストール設定の変更は行っていません。次のステップに関するアドバイスがあれば幸いです！

「いいね！」 7
Slow Profile Loads with 100GB+ database
2

参考までにここに記載します。データベースに 4 つのインデックスを追加したところ、パフォーマンスが大幅に向上しましたが、さらに改善の余地があると思います。追加した内容は以下の通りです。

CREATE INDEX index_topic_links_on_clicks_and_created ON public.topic_links USING btree (clicks, created_at);
CREATE INDEX index_posts_on_like_count_and_created ON public.posts USING btree (like_count, created_at);
CREATE INDEX index_topic_links_on_clicks_and_created_desc ON public.topic_links USING btree (clicks DESC, created_at DESC);
CREATE INDEX index_posts_on_like_count_and_created_desc ON public.posts USING btree (like_count DESC, created_at DESC, user_id) WHERE deleted_at IS NULL AND post_number > 1 AND (post_type = ANY ('{1,2,3,4}'::integer[]));
「いいね！」 6
Slow Profile Loads with 100GB+ database
3

あれ、何かインデックスが抜けていませんか、@tgxworld？

「いいね！」 2
Slow Profile Loads with 100GB+ database
4

@Ghan さん、こんにちは。

これらのインデックスをありがとうございます。私たちは以前から 同じ問題 に直面しており、解決策が見つからずにいました。
これらのインデックスを適用した後、ユーザープロフィールの読み込みが劇的に改善しました。以前は最大 30 秒もかかっていた「最悪ケース」のプロフィールも、現在では約 5 秒で表示されるようになりました。

改めて感謝申し上げます。さらに速度向上の余地があることを願っています。

よろしくお願いいたします、
Sascha

追伸：/about ページの読み込みにも最大で約 20 秒かかっています。これも速度を上げられるかもしれません。

「いいね！」 1
5

再度伺います。これらのインデックスを見落としてはいませんか @tgxworld @sam？

「いいね！」 2
Slow Profile Loads with 100GB+ database
6

Meta のそれらのクエリに関する計画を確認させてください。

Meta ではクエリは非常に高速ですが、リソースが不足したデータベースではパフォーマンスが低下する可能性があるとわかります。私たちは ActiveRecord を使用しており、コード自体は非常にクリーンですが、生成される SQL はかなり複雑です。

SQL を手動で記述することで、既存のインデックスを活用し、クエリを大幅に最適化できます。

最適化前

image
image1662×840 184 KB

最適化後

image
image1557×779 169 KB

最適化されたクエリでは、ユーザーの投稿のみを取得し、結合によるループが後から発生するのを防いでいます。

したがって、インデックスのオーバーヘッドを追加することなく、パフォーマンスを向上できると考えています。

「いいね！」 7
8

書き換え版は ActiveRecord コードで実装できますか、それともここで手動でクエリ文字列を渡す必要がありますか？

9

そうであってほしいですね。現在の Ruby コードは素晴らしいからです。もしそうでない場合でも、アプリ内で SQL を実行するための非常にシンプルなパターンが既に広く使われています。

「いいね！」 1
10

ユーザーサマリーページで実行されているこれらのクエリには、インデックスが不足しているように見えます。これらのクエリを一つずつ最適化する必要があります。

@Falco あなたがこのタスクを引き受けていらっしゃるとのことですが、引き続き担当されますか？ここで難しいのは、これらのクエリがほとんどテストされていないため、何かを壊さないよう慎重に進めなければならない点です。

「いいね！」 3
11

盲目に進めないよう、シリアライザーの出力をテストするためのドラフトコミットを追加しました。

その後、こちらで行った作業を再適用しようとしましたが、高速化にはなるものの正しくない追加条件を追加していたことがわかりました。

（最もいいねされた投稿や、いいねしたユーザーのリストを含む）すべての情報を guardian を経由して渡しているため、情報は常に非常に正確で閲覧者に最適化されていますが、その分、複数のユーザーに対するキャッシュの効果が薄れてしまいます。

少なくとも、このエンドポイントにキャッシュ時間を長く設定することは可能でしょうか？私の意見では、サマリーに含まれる情報は頻繁に変更されないため、1 時間のキャッシュで十分だと考えます。

「いいね！」 4
12

いいですね。そのようにしましょう！

「いいね！」 1
Slow Profile Loads with 100GB+ database
13

キャッシュを私の PR ブランチにプッシュしました:

さらにデバッグを進める中で、そこにもいくつかの N+1 問題があることに気づきました。サムネイルと解決済みステータスがトピックリストに対して N+1 を引き起こしています。これについて何かアイデアはありますか？@david @tgxworld

「いいね！」 2
14

キャッシュ関連の作業をマージしました。

以前 以後
image
image708×375 63.9 KB
image
image690×419 69.6 KB

N+1 問題の修正を継続中です。

「いいね！」 7
15

ユーザーサマリーページは、トピックリストにどのシリアライザーを使用していますか？完全なページ検索については、検索シリアライザーから thumbnails 属性を削除することで、最近 N+1 問題を修正しました。これは全く使用されていないためです。

「いいね！」 3
16

PERF: Preload topic thumbnails for all topic lists by davidtaylorhq · Pull Request #11238 · discourse/discourse · GitHub および PERF: Simplify topic serialization for user summary page by davidtaylorhq · Pull Request #11236 · discourse/discourse · GitHub で修正されました。

「いいね！」 4