Langsame Seitenladezeiten bei Benutzerprofilen

[Ghan]

Wir haben erfolgreich einen Import von Benutzerbeiträgen von unserer aktuellen Software nach Discourse durchgeführt. Dabei ist uns aufgefallen, dass einige Benutzerseiten sehr langsam laden (mehrere Sekunden), was auf langsame Abfragen zurückzuführen zu sein scheint. Wie können wir dies troubleshooting und den Engpass identifizieren?

Hier sind weitere Informationen zu unserer Einrichtung:

Server-Spezifikationen: 8 Kerne, 32 GB RAM, NVMe-SSD-Speicher mit ZFS

Das Forum hat etwa 25 Millionen Beiträge. Die meisten davon sind private Unterhaltungen, davon sind etwa 10,5 Millionen öffentlich.
Wir haben eine Reihe von Megathemen (wir sind eine Schreibplattform, daher entwickeln sich einzelne Geschichten langsam über die Zeit und sammeln viele Beiträge an), mit tausenden von Beiträgen. Dies könnte ein Faktor sein, aber ich weiß nicht, wie ich das bestätigen kann.

Hier ist eine Abfrage, die Probleme verursacht – ich habe sie im integrierten Lastanalysator mit über 5000 ms gesehen:

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

Beim Beobachten des Servers während des Ladens einer Benutzerseite scheint die CPU-Auslastung der PostgreSQL-Prozesse während der Ausführung der Abfrage stark anzusteigen. Ich habe keine Konfigurationsänderungen an der Installation vorgenommen, außer MySQL-Unterstützung für den Importprozess hinzuzufügen. Jegliche Ratschläge zu den nächsten Schritten wären sehr willkommen!

[Ghan]

Ich füge dies zur Referenz hinzu. Ich habe der Datenbank 4 Indizes hinzugefügt, was die Leistung erheblich verbessert hat, obwohl ich denke, dass sie noch besser sein könnte. Hier ist, was ich hinzugefügt habe:

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[]));

[codinghorror (Jeff Atwood)]

Hmm, fehlen hier einige Indizes, @tgxworld?

[klopsknoedel (Sascha Hofmann)]

Hallo @Ghan,

danke vielmals für diese Indizes. Wir haben das gleiche Problem schon seit einiger Zeit und konnten es bisher nicht lösen.
Nachdem wir diese Indizes angewendet haben, hat sich das Laden von Benutzerprofilen drastisch verbessert. Es gab einige Profile im „schlechtesten Fall“, die bis zu 30 Sekunden zum Laden brauchten. Jetzt werden sie in etwa 5 Sekunden angezeigt.

Nochmals vielen Dank, und ich hoffe, es gibt noch mehr Potenzial, dies zu beschleunigen.

Mit freundlichen Grüßen
Sascha

P.S. Selbst die /about-Seite benötigt bis zu ~20 Sekunden zum Laden. Vielleicht lässt sich auch diese beschleunigen.

[codinghorror (Jeff Atwood)]

Nochmal gefragt. Fehlen uns diese Indizes @tgxworld @sam?

[Falco]

Lass mich die Pläne für diese Abfragen in Meta prüfen.

Abfragen sind in Meta recht schnell, aber ich kann verstehen, wie sie auf unterdimensionierten Datenbanken schlecht abschneiden können. Wir nutzen ActiveRecord und der Code ist recht sauber, aber das generierte SQL ist ziemlich unübersichtlich.

Durch manuelles Schreiben des SQL können wir die Abfrage so gestalten, dass sie vorhandene Indizes nutzt und sie stark optimieren:

Vorher

Nachher

In der optimierten Abfrage holen wir nur Beiträge des Benutzers ab und begrenzen damit die Schleife der Beiträge, bevor der Join zu spät stattfindet.

Ich bin daher der Meinung, dass wir eine bessere Leistung erzielen können, ohne zusätzlichen Index-Overhead hinzuzufügen.

[Ghan]

Kann die überarbeitete Version im ActiveRecord-Code erfolgen, oder müsste hier manuell eine Abfragezeichenkette übergeben werden?

[Falco]

Ich hoffe das, denn der aktuelle Ruby-Code ist wunderschön. Falls nicht, haben wir ein sehr einfaches Muster für SQL im App, das bereits weit verbreitet ist.

[tgxworld (Alan Tan)]

@codinghorror

Nochmal gefragt: Fehlen uns diese Indizes @tgxworld @sam?

Es scheint tatsächlich, als fehlten uns bei vielen dieser Abfragen auf der Benutzerzusammenfassungsseite Indizes. Wir müssen diese Abfragen einzeln optimieren.

@Falco, ich sehe, du hast dir diese Aufgabe zugewiesen. Möchtest du sie noch übernehmen? Der knifflige Teil dabei ist, dass diese Abfragen größtenteils ungetestet sind, sodass wir vorsichtig sein müssen, nichts kaputtzumachen.

[Falco]

@tgxworld

Der knifflige Teil dabei ist, dass diese Abfragen größtenteils noch nicht getestet sind, also müssen wir vorsichtig sein, nichts zu beschädigen.

Ich habe hier einen Entwurf-Commit hinzugefügt, um die Ausgabe des Serializers zu testen, damit wir nicht blind agieren:

DEV: Add spec for summary links

Danach habe ich versucht, das, was ich hier getan habe, erneut anzuwenden, stellte sich aber heraus, dass ich eine zusätzliche Klausel hinzugefügt hatte, die die Abfrage zwar schneller, aber falsch machte.

Die Tatsache, dass wir all dies (sogar die am meisten gelikten/gelikt von) durch den Guardian leiten, bedeutet, dass die Informationen immer sehr korrekt und auf den Betrachter zugeschnitten sind, macht es jedoch unmöglich, sie gegen mehrere Benutzer zu cachen.

Ich schätze, wir können zumindest einen längeren Cache für diesen Endpunkt hinzufügen? Meiner Meinung nach wäre ein 1-Stunden-Cache angemessen, da sich die Informationen in der Zusammenfassung nicht oft ändern.

[codinghorror (Jeff Atwood)]

@Falco

Meiner Meinung nach wäre ein 1-Stunden-Cache angemessen, da sich die Informationen in der Zusammenfassung nicht häufig ändern.

Klingt gut… dann machen wir das so!

[Falco]

Ich habe den Cache in meinen PR-Branch gepusht:

Optimization of summary SQL (#10175)

Beim weiteren Debuggen ist mir aufgefallen, dass wir dort auch einige N+1-Abfragen haben. Thumbnails und Solved lösen bei den Themenlisten N+1-Abfragen aus. Habt ihr dazu Ideen, @david @tgxworld?

[Falco]

Die Cache-Arbeit wurde zusammengeführt.

Vorher	Nachher

Noch immer an N+1 gearbeitet.

[tgxworld (Alan Tan)]

@Falco

Miniaturansichten und „Gelöst“ lösen ein N+1-Problem in den Themenlisten aus. Habt ihr dazu Ideen @david @tgxworld?

Welchen Serializer verwendet die Benutzerzusammenfassungsseite für Themenlisten? Für die Volltextsuche habe ich kürzlich das N+1-Problem behoben, indem ich das Attribut thumbnails aus dem Such-Serializer entfernt habe, da es überhaupt nicht verwendet wird.

[Falco]

Behoben in PERF: Preload topic thumbnails for all topic lists by davidtaylorhq · Pull Request #11238 · discourse/discourse · GitHub und PERF: Simplify topic serialization for user summary page by davidtaylorhq · Pull Request #11236 · discourse/discourse · GitHub