Tenho dois trabalhos do Sidekiq que parecem estar demorando muito para serem concluídos. Parece ser o mesmo processo geral, mas há dois trabalhos listados com o status RUNNING. Esses trabalhos estão em execução há 6 horas agora, e o PostgreSQL está travado na CLI quando tento até mesmo executar um EXPLAIN ANALYZE contra a primeira dessas consultas.
Alguma ideia do que poderia estar causando essas consultas a demorarem tanto para serem executadas?
Sinta-se à vontade para cancelar essas consultas, pois o sistema tentará novamente mais tarde.
Eu já havia feito isso antes, na verdade. Eu matei o job para permitir que um reindex terminasse, então isso foi ele reiniciando após isso.
Parece que hoje de manhã os jobs terminaram em algum momento durante a noite e estão rodando novamente. Status atual:
Essa subconsulta retorna cerca de 13.000 linhas na nossa instância:
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
Em seguida, ela está sendo LEFT JOINed à tabela user_badges, que possui 84.000 linhas. Parece que algo na última condição WHERE ub.badge_id = 15 AND q.user_id IS NULL está causando um problema nessa consulta. Se eu remover a cláusula WHERE, ela executa em um tempo razoável (cerca de 20 segundos), mas se eu incluir apenas WHERE ub.badge_id = 15, nem consigo executar um EXPLAIN nessa consulta em um tempo razoável. O EXPLAIN está travado há vários minutos sem resultados. A execução real da consulta completa está rodando há horas. Há algo que possamos fazer para otimizar essa consulta?
Ao ler os tópicos desde ontem à noite aqui no Meta, parece que nenhuma tarefa deveria estar rodando por mais de 8 horas, especialmente para um banco de dados grande.
Mas não tenho certeza do que mais podemos fazer para melhorar isso.
É inacreditável para mim que não consigamos obter o EXPLAIN para mostrar nada, pois ele fica travado.
Olhando este arquivo: discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
Há o seguinte código que executa essa consulta que está atualmente travada. Se eu alterar a primeira junção de LEFT JOIN para INNER JOIN, a consulta é executada instantaneamente. Existe algum motivo para que isso precise ser um left join?
sql = <<~SQL
DELETE FROM user_badges
WHERE id IN (
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
#{badge.query}
) q ON q.user_id = ub.user_id
#{post_clause}
WHERE ub.badge_id = :id AND q.user_id IS NULL
)
SQL
É possível tornar essas consultas de distintivos mais rápidas?
Outra consulta que parece ruim é esta, acredito que seja do job de limpeza semanal:
UPDATE posts
SET percent_rank = X.percent_rank
FROM (
SELECT posts.id, Y.percent_rank
FROM posts
JOIN (
SELECT id, percent_rank()
OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank
FROM posts
) Y ON Y.id = posts.id
JOIN topics ON posts.topic_id = topics.id
WHERE (posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank)
LIMIT 20000
) AS X
WHERE posts.id = X.id
O EXPLAIN dessa consulta mostra que ela está tentando ordenar todas as 26 milhões de linhas na tabela posts. Não consigo identificar qual método será usado para essa consulta, mas com base no fato de que a espera ativa é “DataFileRead”, acho que ela está acessando o disco para algo…
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Update on posts (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Nested Loop (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Subquery Scan on x (cost=8312704.18..8464468.35 rows=20000 width=48)
-> Limit (cost=8312704.18..8464268.35 rows=20000 width=12)
-> Hash Join (cost=8312704.18..209445240.14 rows=26540908 width=12)
Hash Cond: (posts_1.topic_id = topics.id)
-> Nested Loop (cost=8277347.60..209340213.36 rows=26540908 width=16)
-> WindowAgg (cost=8277347.16..8809352.84 rows=26600284 width=24)
-> Sort (cost=8277347.16..8343847.87 rows=26600284 width=16)
Sort Key: posts_2.topic_id, posts_2.score DESC
-> Seq Scan on posts posts_2 (cost=0.00..4542277.84 rows=26600284 width=16)
-> Index Scan using posts_pkey on posts posts_1 (cost=0.44..7.52 rows=1 width=16)
Index Cond: (id = posts_2.id)
Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) <> percent_rank))
-> Hash (cost=23871.05..23871.05 rows=918842 width=4)
-> Index Only Scan using topics_pkey on topics (cost=0.42..23871.05 rows=918842 width=4)
-> Index Scan using posts_pkey on posts (cost=0.44..8.14 rows=1 width=781)
Index Cond: (id = x.id)
JIT:
Functions: 24
Options: Inlining true, Optimization true, Expressions true, Deforming true
(21 rows)
Tenho a forte sensação de que você está nadando contra a corrente aqui e que o banco de dados que você está usando simplesmente não tem recursos suficientes para executar o Discourse.
Essas consultas são de fato custosas, mas hospedamos vários fóruns grandes (por exemplo: About - Straight Dope Message Board 22 milhões de posts) e conseguimos executar todas essas consultas sem problemas nessa instância.
É um servidor dedicado com as seguintes especificações:
AMD Ryzen 7 3800X
64 GB de RAM ECC @ 2666 MHz
2 x 1,2 TB Intel P3600 NVMe SSD (ZFS RAID 1)
A VM que executa o Discourse recebeu 8 núcleos de CPU e 32 GB de RAM.
Acredito ter descoberto o problema com a primeira consulta, ou pelo menos uma maneira de instruir o planejador de consultas a tomar a decisão correta. Aqui está a consulta que não concluía em mais de 16 horas (isso é para a medalha First Quote):
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
) q ON q.user_id = ub.user_id
AND (q.post_id = ub.post_id OR NOT TRUE)
WHERE ub.badge_id = 15 AND q.user_id IS NULL
Se eu adicionar uma única linha ORDER BY no local correto, essa consulta agora é concluída em alguns segundos:
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
ORDER BY ids.user_id
) q ON q.user_id = ub.user_id
AND (q.post_id = ub.post_id OR NOT TRUE)
WHERE ub.badge_id = 15 AND q.user_id IS NULL
Acho que deveria ser inteligente o suficiente para fazer essa ordenação no local correto, mas parece que não é… Ainda assim, a correção parece bastante simples neste momento.
Ainda não comecei a investigar a outra consulta sobre percent_rank.
Às vezes, o planejamento falha quando as estatísticas estão ruins… em alguns casos excepcionais, um vacuum completo pode ajudar; um vacuum mínimo é totalmente recomendado após as importações. Acho que você fez ambos.
Há algum motivo para você estar executando em uma VM em vez de usar o Docker diretamente no host?
Quais são os recursos alocados para o Straight Dope, se posso perguntar, e ele tem algum trabalho que leve várias horas, como fazemos aqui com 27 milhões?
Sim, executei VACUUM ANALYZE algumas vezes. As estatísticas devem estar corretas, mas isso parece estar escolhendo mal em várias reconstruções, ajustes de tuning do Postgres e vacuums.
Estamos executando outras VMs nesta máquina host, mas temos recursos sobrando no momento, então foi aqui que criei um sistema para testar o Discourse.
Da nossa instância grande, olhando para: /sidekiq/scheduler
E
Você fez um VACUUM completo?
Nosso servidor de banco de dados tem desempenho de hardware semelhante ao seu (embora tenhamos IO mais rápido, já que possuímos um array RAID maior). No entanto, não executamos virtualização de forma alguma. Essa é uma grande diferença.
Não fiz. Posso tentar e ver se o comportamento muda.
Tenho certeza de que há alguma perda de desempenho ao rodar em uma VM, mas nada está sobrecarregando o hardware pesadamente. Quando executei a importação para trazer todos os nossos dados de nosso outro software, consegui obter 60-70% de utilização de todos os 8 núcleos, rodando múltiplos processos de importação simultaneamente.
Agora, quando essas tarefas ficam paradas e girando, geralmente não vejo a média de carga acima de 2-3, então elas nem sequer estão usando toda a CPU disponível.
O vacuum completo é algo que vi ajudar após migrações massivas; curioso para ver se tem impacto.
Ele está em execução no momento.
Sobre a consulta percent_rank que parece fazer parte do trabalho semanal, como é o EXPLAIN na sua instância grande? Minha instância está reportando um custo de consulta superior a 8 milhões nessa consulta, o que parece um pouco assustador.
Você tem alguma recomendação para os valores de ajuste do PostgreSQL no app.yml? Atualmente estou usando:
shared_buffers: 16GB
work_mem: 512MB
O VACUUM FULL foi concluído, mas não parece ter feito diferença no desempenho da consulta. A consulta do badge ainda parece que vai rodar por horas e horas, a menos que eu adicione a cláusula ORDER BY, e a consulta percent_rank está rodando há duas horas sem concluir. Provavelmente precisaremos alterar a definição SQL do badge “First Quote” e, em seguida, analisar o que pode ser feito para corrigir a consulta percent_rank.
Você tem alguma sugestão para a consulta percent_rank com base neste EXPLAIN?
UPDATE posts
SET percent_rank = X.percent_rank
FROM (
SELECT posts.id, Y.percent_rank
FROM posts
JOIN (
SELECT id, percent_rank()
OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank
FROM posts
) Y ON Y.id = posts.id
JOIN topics ON posts.topic_id = topics.id
WHERE (posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank)
LIMIT 20000
) AS X
WHERE posts.id = X.id
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Update on posts (cost=6511439.82..6944253.09 rows=20000 width=828)
-> Nested Loop (cost=6511439.82..6944253.09 rows=20000 width=828)
-> Subquery Scan on x (cost=6511439.38..6784765.09 rows=20000 width=48)
-> Limit (cost=6511439.38..6784565.09 rows=20000 width=12)
-> Nested Loop (cost=6511439.38..374544016.70 rows=26949684 width=12)
-> Nested Loop (cost=6511438.96..192122439.64 rows=26949684 width=16)
-> WindowAgg (cost=6511438.52..7050906.24 rows=26973386 width=24)
-> Sort (cost=6511438.52..6578871.98 rows=26973386 width=16)
Sort Key: posts_2.topic_id, posts_2.score DESC
-> Seq Scan on posts posts_2 (cost=0.00..2721272.86 rows=26973386 width=16)
-> Index Scan using posts_pkey on posts posts_1 (cost=0.44..6.84 rows=1 width=16)
Index Cond: (id = posts_2.id)
Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) <> percent_rank))
-> Index Only Scan using topics_pkey on topics (cost=0.42..6.77 rows=1 width=4)
Index Cond: (id = posts_1.topic_id)
-> Index Scan using posts_pkey on posts (cost=0.44..7.97 rows=1 width=784)
Index Cond: (id = x.id)
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming true
(20 rows)
Você pode tentar ajustar o limite; talvez com o limite em 1000 isso seja rápido o suficiente para você.
Alterar o limite não parece alterar muito o plano de consulta (custos ou outros). O problema parece ser que a consulta precisa ordenar toda a tabela posts (que, no nosso caso, tem cerca de 26,5 milhões de linhas) antes de executar a operação. Pode haver uma oportunidade para criar um índice aqui. Não vejo a coluna score incluída em nenhum dos índices da tabela posts no momento.
A classificação é por tópico, não classifica todo o conjunto.
Você poderia talvez filtrar por IDs de tópico … WHERE topic_id < 1000 … 2000 … 10000 e assim por diante… Provavelmente, assim que a atualização inicial for concluída, isso será executado mais rapidamente.