Lente caricamento delle pagine dei profili utente

[Ghan]

Abbiamo completato l’importazione dei post degli utenti dal nostro software attuale a Discourse e abbiamo notato che alcune pagine utente sono molto lente da caricare (diversi secondi), probabilmente a causa di query lente. Come possiamo diagnosticare il problema e identificare il collo di bottiglia?

Ecco alcune informazioni aggiuntive sulla nostra configurazione:

Specifiche del server: 8 core, 32 GB di RAM, archiviazione NVMe SSD con ZFS

La community conta circa 25 milioni di post. La maggior parte di questi sono conversazioni private, con circa 10,5 milioni di post pubblici.
Abbiamo diversi megatopic (siamo un sito per scrittori, quindi le storie individuali tendono a evolversi lentamente nel tempo, accumulando molti post), con migliaia di post ciascuno, il che potrebbe contribuire al problema, ma non so come confermarlo.

Ecco una query che sembra causare problemi: l’ho vista eseguire per oltre 5000 ms nell’analizzatore di carico integrato:

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

Osservando il server durante il caricamento di una pagina utente, sembra che i processi PostgreSQL vadano in picchi di utilizzo della CPU mentre la query viene eseguita. Non ho apportato modifiche alla configurazione dell’installazione, se non per aggiungere il supporto MySQL durante il processo di importazione. Apprezzerei qualsiasi consiglio sui prossimi passi da compiere!

[Ghan]

Lo inserisco qui per riferimento. Ho aggiunto 4 indici al database, il che ha notevolmente migliorato le prestazioni, anche se penso che si possa ancora fare meglio. Ecco cosa ho aggiunto:

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, mancano qui alcuni indici, @tgxworld?

[klopsknoedel (Sascha Hofmann)]

Ciao @Ghan,

grazie mille per questi indici. Abbiamo lo stesso problema da un po’ di tempo e non siamo riusciti a risolverlo.
Dopo aver applicato questi indici, il caricamento dei profili utente è migliorato drasticamente. C’erano alcuni profili “peggiori” che richiedevano fino a 30 secondi per essere caricati. Ora vengono visualizzati in circa 5 secondi.

Quindi, grazie ancora e spero ci sia ancora margine per accelerare ulteriormente.

Cordiali saluti
Sascha

P.S. Anche la pagina /about richiede fino a circa 20 secondi per essere caricata. Forse anche questa potrebbe essere velocizzata.

[codinghorror (Jeff Atwood)]

Chiedendo di nuovo. Stiamo tralasciando questi indici @tgxworld @sam?

[Falco]

Controlliamo i piani per quelle query in Meta.

Le query sono piuttosto veloci su Meta, ma capisco come possano performare male su database sottodimensionati. Stiamo utilizzando ActiveRecord e il codice è piuttosto pulito, ma il SQL generato è piuttosto disordinato.

Scrivendo il SQL manualmente possiamo far sì che la query utilizzi gli indici esistenti e ottimizzarla notevolmente:

Prima

Dopo

Nella query ottimizzata recuperiamo solo i post dell’utente, limitando il ciclo dei post per il join che avviene troppo tardi.

Quindi credo che possiamo ottenere prestazioni migliori senza aggiungere sovraccarico di indici.

[Ghan]

La versione riscritta può essere realizzata nel codice ActiveRecord o è necessario passare qui una stringa di query manuale?

[Falco]

Spero di sì, perché il codice Ruby attuale è splendido. Ma se non fosse così, abbiamo un pattern molto semplice per eseguire query SQL nell’app che è già ampiamente utilizzato.

[tgxworld (Alan Tan)]

@codinghorror

Chiedo di nuovo. Manca questi indici @tgxworld @sam?

Sembra proprio che manchino indici su molte di queste query eseguite nella pagina di riepilogo dell’utente. Dovremo ottimizzare queste query una per una.

@Falco vedo che ti sei assegnato questo compito, vuoi ancora occupartene? La parte complicata qui è che queste query sono per lo più non testate, quindi dobbiamo fare attenzione a non rompere nulla.

[Falco]

@tgxworld

La parte delicata qui è che queste query sono per lo più non testate, quindi dobbiamo fare attenzione a non rompere nulla.

Ho aggiunto una commit di bozza per testare l’output del serializzatore qui, così non lavoriamo alla cieca:

DEV: Add spec for summary links

Dopo di ciò, ho provato a riapplicare quanto fatto qui, ma risulta che stavo aggiungendo una clausola extra che lo rendeva veloce ma errato.

Il fatto che passiamo tutto questo (anche i più apprezzati/apprezzati da) attraverso il guardian significa che le informazioni sono sempre molto corrette e adattate al visualizzatore, ma rende la memorizzazione nella cache inutile contro più utenti.

Immagino che possiamo almeno aggiungere una cache più lunga a questo endpoint? A mio parere, una cache di 1 ora sarebbe adeguata, poiché le informazioni nel riepilogo non cambiano spesso.

[codinghorror (Jeff Atwood)]

@Falco

Secondo me, una cache di 1 ora sarebbe adeguata, poiché le informazioni nel riepilogo non cambiano spesso.

Sembra una buona idea… procediamo!

[Falco]

Ho spinto la cache nel mio branch della PR:

Optimization of summary SQL (#10175)

Mentre continuavo a fare debug, ho notato che c’è anche un problema N+1. Le miniature e lo stato “Risolto” stanno causando un N+1 per le liste dei topic. Avete qualche idea al riguardo @david @tgxworld?

[Falco]

Unito il lavoro sulla cache.

Prima	Dopo

Sto ancora lavorando sul problema N+1.

[tgxworld (Alan Tan)]

@Falco

Le miniature e “Risolto” stanno causando un problema N+1 nelle liste dei topic. Avete idee su come risolverlo @david @tgxworld?

Quale serializzatore utilizza la pagina di riepilogo utente per le liste dei topic? Per la ricerca completa della pagina, ho recentemente risolto il problema N+1 rimuovendo gli attributi thumbnails dal serializzatore di ricerca, dato che non vengono utilizzati in alcun modo.

[Falco]

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