Sidekiq에서 두 개의 작업이 완료되는 데 시간이 매우 오래 걸리는 것 같습니다. 전체 프로세스는 동일해 보이지만, 두 작업 모두 RUNNING 상태로 나열되어 있습니다. 이 작업들은 이미 6시간째 실행 중이며, 첫 번째 쿼리에 대해 EXPLAIN ANALYZE를 실행하려고 할 때조차 Postgres가 CLI에서 응답을 멈추고 있습니다.
이 쿼리들이 실행되는 데 이렇게 오래 걸리는 원인에 대해 혹시 아이디어가 있으신가요?
그 쿼리들을 중단해도 무방합니다. 시스템이 나중에 자동으로 재시도할 것입니다.
5개의 좋아요
사실 저는 그 전에 이미 그렇게 했습니다. 재인덱싱이 완료되도록 잡을 종료했기 때문에, 이는 그 후 재시작된 것입니다.
오늘 아침에 잡들이 밤새 어딘가에서 완료된 것으로 보이며, 다시 실행 중입니다. 현재 상태는 다음과 같습니다:
이 서브쿼리는 우리 인스턴스에서 약 13,000개의 행을 반환합니다:
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
이후 이 결과는 84,000개의 행을 가진 user_badges 테이블에 LEFT JOIN됩니다. 마지막 조건인 WHERE ub.badge_id = 15 AND q.user_id IS NULL 중 무언가가 이 쿼리를 폭주시키는 것처럼 보입니다. WHERE 절을 제거하면 합리적인 시간(약 20초) 내에 실행되지만, WHERE ub.badge_id = 15만 포함해도 합리적인 시간 내에 EXPLAIN을 실행할 수조차 없습니다. EXPLAIN은 몇 분 동안 결과를 반환하지 않고 계속 멈춰 있습니다. 전체 쿼리의 실제 실행은 몇 시간째 진행 중입니다. 이 쿼리를 최적화할 수 있는 방법이 있을까요?
2개의 좋아요
어젯밤부터 Meta의 관련 토픽을 읽어본 결과, 특히 데이터베이스의 규모가 큰 경우 어떤 작업도 8시간 이상 실행되면 안 되는 것 같습니다.
하지만 더 이상 무엇을 할 수 있을지 확실하지 않습니다.
EXPLAIN이 아무것도 표시하지 못하는 이유는 멈추기 때문인데, 이런 상황이 정말 이상합니다.
1개의 좋아요
이 파일을 보고 있습니다: discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
현재 막혀 있는 쿼리를 실행하는 다음과 같은 코드가 있습니다. 첫 번째 join을 LEFT JOIN에서 INNER JOIN으로 변경하면 쿼리가 즉시 실행됩니다. 이것이 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
1개의 좋아요
이 배지 쿼리를 더 빠르게 만드는 것이 가능한가요?
또 다른 문제가 있는 쿼리는 다음으로 생각됩니다. 주간 정리 작업(weekly cleanup job)에서 실행되는 것 같습니다:
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
이 쿼리의 실행 계획(explain)을 보면 posts 테이블의 모든 2,600만 행을 정렬하려고 하는 것으로 보입니다. 이 쿼리에 어떤 방법이 사용될지 정확히 판단하기 어렵지만, 활성 대기(active wait)가 "DataFileRead"라는 사실에 기반하여 디스크에서 무언가를 읽고 있는 것으로 추정됩니다…
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)
여기서 역류(상류)를 헤엄치는 것 같은 강한 느낌을 받습니다. 사용하고 계신 데이터베이스에는 Discourse를 실행할 자원이 단순히 부족해 보입니다.
- 하드웨어(CPU / 하드 드라이브 제조사 및 모델)의 정확한 사양은 무엇입니까?
- VM의 정확한 사양은 무엇입니까?
이 쿼리들은 실제로 비용이 많이 듭니다. 하지만 우리는 상당한 규모의 포럼(예: About - Straight Dope Message Board, 2,200만 개의 게시물)을 호스팅하고 있으며, 해당 인스턴스에서 이러한 모든 쿼리를 문제없이 실행할 수 있습니다.
1개의 좋아요
다음 사양을 갖춘 전용 서버입니다:
AMD Ryzen 7 3800X
64 GB ECC RAM @ 2666 Mhz
2 x 1.2 TB Intel P3600 NVMe SSD (ZFS RAID 1)
Discourse를 실행하는 VM에는 8개의 CPU 코어와 32 GB의 RAM이 할당되어 있습니다.
첫 번째 쿼리의 문제를 발견했거나, 적어도 쿼리 플래너가 올바른 결정을 내리도록 지시하는 방법을 찾은 것 같습니다. 16시간 이상 완료되지 않던 쿼리는 다음과 같습니다(이것은 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
올바른 위치에 ORDER BY 한 줄만 추가하면, 이제 이 쿼리는 몇 초 만에 완료됩니다:
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
플래너가 올바른 위치에 정렬을 수행할 정도로 충분히 지능적이어야 한다고 생각하지만, 그렇지 않은 것 같습니다… 그래도 지금은 수정이 꽤 쉬운 것 같습니다.
percent_rank에 대한 다른 쿼리는 아직 제대로 파헤치지 않았습니다.
2개의 좋아요
통계가 좋지 않을 때 계획이 어긋나기도 합니다… 일부 예외적인 경우 완전한 vacuum이 도움이 될 수 있으며, 임포트 후에는 최소한의 vacuum을 강력히 권장합니다. 두 가지를 모두 수행하신 것 같습니다.
Docker를 호스트에 직접 실행하는 대신 VM에서 실행하는 이유가 있나요?
1개의 좋아요
혹시 Straight Dope에 할당된 리소스가 어떻게 되는지, 그리고 우리처럼 2,700만 개의 게시물 중 몇 시간씩 걸리는 작업이 있는지 여쭤봐도 될까요?
네, VACUUM ANALYZE를 몇 번 실행했습니다. 통계는 정확해야 하는데, 여러 번의 재구축, Postgres 튜닝 조정 및 vacuum에도 불구하고 선택이 여전히 부실한 것 같습니다.
이 호스트 머신에는 다른 VM들이 실행 중이지만, 현재 여유 자원이 있어 Discourse를 테스트하기 위한 시스템을 여기에 구축했습니다.
대형 인스턴스에서 /sidekiq/scheduler를 확인한 결과 다음과 같습니다.
그리고
FULL VACUUM을 수행하셨나요?
저희 DB 서버는 하드웨어 성능이 여러분의 것과 대략 비슷합니다(다만 더 큰 RAID 어레이를 사용하므로 IO 성능은 더 빠릅니다). 그러나 저희는 가상화 환경을 전혀 사용하지 않습니다. 이것이 가장 큰 차이점입니다.
2개의 좋아요
아직 수행하지 않았습니다. 시도해 보고 동작이 달라지는지 확인해 보겠습니다.
VM에서 실행할 때 일부 성능 저하가 있을 것으로 생각하지만, 하드웨어를 과도하게 점유하는 작업은 없습니다. 다른 소프트웨어에서 모든 데이터를 가져오기 위해 임포트를 실행했을 때, 여러 임포트 프로세스를 동시에 실행하여 8개 코어의 60~70% 사용률을 달성할 수 있었습니다.
현재 이러한 작업들이 대기하며 스피닝(spinning) 상태일 때, 보통 로드 어버리지(load average)가 2~3을 넘지 않아 가용한 CPU를 모두 사용하지도 않습니다.
2개의 좋아요
전체 비어코밍(full vacuum)은 대규모 마이그레이션 이후에 도움이 되는 것을 본 적이 있는데, 이것이 어떤 영향을 미칠지 궁금합니다.
3개의 좋아요
현재 실행 중입니다.
2개의 좋아요
주 1회 실행되는 작업의 일부로 보이는 percent_rank 쿼리에 대해, 대규모 인스턴스에서 EXPLAIN 결과는 어떻게 나오나요? 제 인스턴스에서는 해당 쿼리의 비용이 800만 이상으로 보고되고 있는데, 조금 걱정되는 수준입니다.
app.yml의 postgres 튜닝 값에 대해 추천 사항이 있으신가요? 현재 저는 다음과 같은 설정을 사용하고 있습니다:
shared_buffers: 16GB
work_mem: 512MB
VACUUM FULL은 완료되었지만, 쿼리 성능에 차이를 만드는 것 같지 않습니다. ORDER BY 절을 추가하지 않는 한 배지 쿼리는 여전히 몇 시간씩 실행될 것 같고, percent_rank 쿼리는 현재 2시간째 실행 중이지만 아직 완료되지 않았습니다. 아마도 First Quote 배지의 SQL 정의를 변경해야 할 것 같고, 그 다음에는 percent_rank 쿼리를 수정하기 위해 어떤 조치를 취할 수 있는지 살펴봐야 할 것 같습니다.
이 EXPLAIN 결과를 바탕으로 percent_rank 쿼리에 대한 제안이 있으신가요?
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)
1개의 좋아요
limit 값을 조정해 보는 것이 좋습니다. limit을 1000으로 설정하면 충분히 빠르게 동작할 수 있습니다.
제한값을 변경해도 쿼리 플랜에 큰 변화(비용 등)가 없는 것 같습니다. 문제는 쿼리가 해당 작업을 수행하기 전에 posts 테이블 전체(우리의 경우 약 2,650만 행)를 정렬해야 한다는 점입니다. 여기에 인덱스를 활용할 기회가 있을 수 있습니다. 현재 posts 테이블의 어떤 인덱스에도 score 컬럼이 포함되어 있지 않은 것으로 보입니다.
랭킹은 주제별로 이루어지며, 전체 집합에 대한 랭킹이 아닙니다.
주제 ID 기준으로 게이트를 설정할 수 있습니다 … WHERE topic_id < 1000 … 2000 … 10000 등. 아마도 초기 업데이트가 완료되면 실행 속도가 빨라질 것입니다.
1개의 좋아요