Carregamento lento de páginas em perfis de usuário

Contribuir Funcionalidade
1

Concluímos a importação de posts de usuários do nosso software atual para o Discourse, e uma coisa que notamos é que algumas páginas de usuários estão muito lentas para carregar (vários segundos), e isso parece ser devido a consultas lentas. Como podemos solucionar esse problema e identificar o gargalo?

Aqui estão mais informações sobre nossa configuração:

Especificações do Servidor: 8 núcleos, 32 GB de RAM, armazenamento NVMe SSD com ZFS

O fórum tem cerca de 25 milhões de posts. A maioria deles são conversas privadas, com cerca de 10,5 milhões sendo públicos.
Temos vários megatópicos (somos um site de escrita, então histórias individuais tendem a evoluir lentamente ao longo do tempo, acumulando muitos posts), com milhares de posts cada, então isso pode estar contribuindo, mas não sei como confirmar isso.

Aqui está uma consulta que parece estar causando problemas — já a vi com mais de 5000ms no analisador de carga integrado:

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

Ao observar o servidor enquanto uma página de usuário é carregada, parece que o uso de CPU nos processos do PostgreSQL apenas dispara enquanto a consulta é executada. Não fiz nenhuma alteração de configuração na instalação, exceto para adicionar suporte ao MySQL para o processo de importação. Qualquer conselho sobre os próximos passos seria muito apreciado!

7 curtidas
Slow Profile Loads with 100GB+ database
2

Colocando isso aqui para referência. Adicionei 4 índices ao banco de dados, o que melhorou muito o desempenho, embora eu ache que ainda possa ser melhor. Isso é o que adicionei:

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 curtidas
Slow Profile Loads with 100GB+ database
3

Hmm, estamos perdendo alguns índices aqui, @tgxworld?

2 curtidas
Slow Profile Loads with 100GB+ database
4

Olá @Ghan,

muito obrigado por esses índices. Temos o mesmo problema há algum tempo e não conseguimos resolvê-lo.
Após aplicar esses índices, o carregamento dos perfis de usuários melhorou drasticamente. Havia alguns perfis de “pior caso” que levavam até 30 segundos para carregar. Agora eles aparecem em ~5 segundos.

Então, obrigado novamente e espero que haja mais potencial para acelerar isso.

Atenciosamente,
Sascha

P.S. Até a página /about leva até ~20 segundos para carregar. Talvez isso também possa ser acelerado.

1 curtida
5

Perguntando novamente. Estamos deixando passar esses índices @tgxworld @sam?

2 curtidas
Slow Profile Loads with 100GB+ database
6

Deixe-me verificar os planos para essas consultas no Meta.

As consultas são bastante rápidas no Meta, mas consigo ver como elas podem ter desempenho ruim em bancos de dados com recursos limitados. Estamos usando o ActiveRecord e o código está bastante limpo, mas o SQL gerado é bastante confuso.

Ao escrever o SQL manualmente, podemos fazer com que a consulta utilize índices existentes e otimizá-la bastante:

Antes

image
image1662×840 184 KB

Depois

image
image1557×779 169 KB

Na consulta otimizada, recuperamos apenas as postagens do usuário, limitando o loop de postagens para que a junção ocorra mais tarde.

Portanto, acredito que podemos obter um melhor desempenho sem adicionar sobrecarga de índices.

7 curtidas
8

A versão reescrita pode ser feita no código do ActiveRecord ou você precisaria passar uma string de consulta manual aqui?

9

Espero que sim, porque o código Ruby atual é lindo. Mas, se não for, temos um padrão muito simples para fazer SQL no aplicativo que já é amplamente utilizado.

1 curtida
10

Parece que estamos perdendo índices em muitas dessas consultas executadas na página de resumo do usuário. Precisaremos otimizar essas consultas uma por uma.

@Falco, vejo que você se atribuiu a isso. Você ainda quer assumir? A parte complicada aqui é que essas consultas são, em sua maioria, não testadas, então precisamos ter cuidado para não quebrar nada.

3 curtidas
11

Adicionei um commit de rascunho para testar a saída do serializador aqui, para que não estejamos voando às cegas:

Depois disso, tentei reaplicar o que fiz aqui, mas descobri que estava adicionando uma cláusula extra que tornava a consulta rápida, mas incorreta.

O fato de passarmos tudo isso (incluindo os mais curtidos/curtidos por) pelo guardião significa que as informações estão sempre muito corretas e adaptadas ao visualizador, mas isso torna o cache inútil contra múltiplos usuários.

Acho que podemos pelo menos adicionar um cache mais longo a esse endpoint? Na minha opinião, um cache de 1 hora seria adequado, já que as informações no resumo não mudam com frequência.

4 curtidas
12

Soa bem… vamos fazer isso!

1 curtida
Slow Profile Loads with 100GB+ database
13

Empurrei o cache para minha branch do PR:

Enquanto depurava mais, percebi que também temos alguns N+1 aí. Miniaturas e Resolvido estão disparando um N+1 para as listas de tópicos. Alguma ideia sobre isso, @david @tgxworld?

2 curtidas
14

Juntei o trabalho de cache.

Antes Depois
image
image708×375 63.9 KB
image
image690×419 69.6 KB

Ainda trabalhando no N+1.

7 curtidas
15

Qual serializador a página de resumo do usuário usa para as listas de tópicos? Para a pesquisa de página completa, corrigi recentemente o problema N+1 removendo os atributos thumbnails do serializador de pesquisa, pois eles não são utilizados de forma alguma.

3 curtidas
16

Corrigido em 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

4 curtidas