Chargements de page lents sur les profils utilisateurs

[Ghan]

Nous avons terminé l’importation des publications des utilisateurs depuis notre logiciel actuel vers Discourse, et nous avons constaté que certaines pages d’utilisateurs mettent beaucoup de temps à se charger (plusieurs secondes), ce qui semble être dû à des requêtes lentes. Comment pouvons-nous diagnostiquer ce problème et identifier le goulot d’étranglement ?

Voici quelques informations supplémentaires sur notre configuration :

Spécifications du serveur : 8 cœurs, 32 Go de RAM, stockage NVMe SSD avec ZFS

Le forum compte environ 25 millions de publications. La plupart sont des conversations privées, dont environ 10,5 millions sont publiques.
Nous avons également plusieurs mégathèmes (nous sommes un site d’écriture, donc les histoires individuelles ont tendance à évoluer lentement dans le temps, accumulant de nombreuses publications), avec des milliers de publications chacune, ce qui pourrait y contribuer, mais je ne sais pas comment le confirmer.

Voici une requête qui semble poser problème – je l’ai observée à plus de 5000 ms dans l’analyseur de charge intégré :

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

Lorsque je surveille le serveur pendant le chargement d’une page utilisateur, il semble que l’utilisation du CPU des processus PostgreSQL augmente brusquement pendant l’exécution de la requête. Je n’ai apporté aucune modification à la configuration de l’installation, à l’exception de l’ajout du support MySQL pour le processus d’importation. Toute suggestion pour les prochaines étapes serait la bienvenue !

[Ghan]

Je le mets ici pour référence. J’ai ajouté 4 index à la base de données, ce qui a considérablement amélioré les performances, même si je pense qu’on pourrait encore faire mieux. Voici ce que j’ai ajouté :

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, est-ce qu’il manque des index ici @tgxworld ?

[klopsknoedel (Sascha Hofmann)]

Bonjour @Ghan,

merci beaucoup pour ces index. Nous rencontrons le même problème depuis un certain temps et n’avons pas pu le résoudre.
Après l’application de ces index, le chargement des profils utilisateurs s’est considérablement amélioré. Certains profils « pires cas » mettaient jusqu’à 30 secondes à se charger. Maintenant, ils s’affichent en environ 5 secondes.

Merci encore et j’espère qu’il reste encore des possibilités pour accélérer cela.

Cordialement,
Sascha

P.S. Même la page /about prend jusqu’à environ 20 secondes à charger. Cela pourrait peut-être être accéléré également.

[codinghorror (Jeff Atwood)]

Je pose la question à nouveau. Ces index nous manquent-ils @tgxworld @sam ?

[Falco]

Laissez-moi vérifier les plans d’exécution pour ces requêtes dans Meta.

Les requêtes sont très rapides sur Meta, mais je comprends qu’elles puissent mal performer sur des bases de données sous-dimensionnées. Nous utilisons ActiveRecord et le code est assez propre, mais le SQL généré est assez désordonné.

En écrivant le SQL manuellement, nous pouvons faire en sorte que la requête utilise les index existants et l’optimiser considérablement :

Avant

Après

Dans la requête optimisée, nous récupérons uniquement les publications de l’utilisateur, ce qui limite la boucle sur les publications pour éviter que le jointure ne se produise trop tard.

Je pense donc que nous pouvons obtenir de meilleures performances sans ajouter de surcharge liée aux index.

[Ghan]

La version réécrite peut-elle être réalisée dans le code ActiveRecord ou devriez-vous passer une chaîne de requête manuelle ici ?

[Falco]

J’espère bien, car le code Ruby actuel est magnifique. Mais sinon, nous avons un modèle très simple pour exécuter du SQL dans l’application, qui est déjà largement utilisé.

[tgxworld (Alan Tan)]

@codinghorror

Je demande à nouveau. Ces index nous manquent-ils @tgxworld @sam ?

Il semble effectivement que nous manquions d’index pour beaucoup de ces requêtes s’exécutant sur la page de résumé utilisateur. Nous devrons optimiser ces requêtes une par une.

@Falco, je vois que vous vous êtes assigné cette tâche, souhaitez-vous toujours la prendre en charge ? La partie délicate ici est que ces requêtes sont pour la plupart non testées, nous devons donc faire attention à ne rien casser.

[Falco]

@tgxworld

Le point délicat ici est que ces requêtes sont pour la plupart peu testées, nous devons donc faire attention à ne rien casser.

J’ai ajouté un commit brouillon pour tester la sortie du sérialiseur ici, afin que nous ne naviguions pas à l’aveugle :

DEV: Add spec for summary links

Ensuite, j’ai essayé de réappliquer ce que j’avais fait ici, mais il s’avère que j’ajoutais une clause supplémentaire qui rendait le résultat rapide mais incorrect.

Le fait que nous passions tout cela (même les « les plus aimés » / « aimés par ») par le gardien signifie que les informations sont toujours très précises et adaptées au spectateur, mais cela rend la mise en cache inefficace contre les multiples utilisateurs.

Je pense que nous pouvons au moins ajouter une durée de cache plus longue à ce point de terminaison ? À mon avis, un cache d’une heure serait adéquat, car les informations dans le résumé ne changent pas souvent.

[codinghorror (Jeff Atwood)]

@Falco

À mon avis, un cache d’une heure serait suffisant, car les informations du résumé ne changent pas souvent.

Ça me va… faisons-le !

[Falco]

J’ai poussé le cache sur ma branche de PR :

Optimization of summary SQL (#10175)

En déboguant davantage, j’ai remarqué qu’il y a aussi des problèmes de N+1. Les miniatures et la résolution déclenchent un N+1 pour les listes de sujets. Des idées à ce sujet @david @tgxworld ?

[Falco]

Fusion du travail sur le cache.

Avant	Après

Travail toujours en cours sur le problème N+1.

[tgxworld (Alan Tan)]

@Falco

Les vignettes et la mention « Résolu » déclenchent un problème N+1 dans les listes de sujets. Avez-vous des idées à ce sujet @david @tgxworld ?

Quel sérialiseur utilise la page de résumé utilisateur pour les listes de sujets ? Pour la recherche sur la page complète, j’ai récemment résolu le problème N+1 en supprimant les attributs thumbnails du sérialiseur de recherche, car ils ne sont pas utilisés du tout.

[Falco]

Corrigé dans PERF: Preload topic thumbnails for all topic lists by davidtaylorhq · Pull Request #11238 · discourse/discourse · GitHub et PERF: Simplify topic serialization for user summary page by davidtaylorhq · Pull Request #11236 · discourse/discourse · GitHub