Ho due job di Sidekiq che sembrano richiedere molto tempo per completarsi. Sembra lo stesso processo generale, ma ci sono due job elencati nello stato RUNNING. Questi job sono in esecuzione da 6 ore e postgres si blocca sulla CLI quando provo a eseguire persino un EXPLAIN ANALYZE sulla prima di queste query.
Avete idee su cosa potrebbe causare un’esecuzione così lunga di queste query?
Sentiti libero di eliminare quelle query e lascia che il sistema riprovi più tardi.
In realtà l’avevo già fatto prima. Ho terminato il processo per permettere il completamento della reindicizzazione, quindi questo è il riavvio successivo.
Sembra che questa mattina i processi si siano conclusi durante la notte e siano di nuovo in esecuzione. Stato attuale:
Questa subquery restituisce circa 13.000 righe sulla nostra istanza:
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
Viene poi eseguita una LEFT JOIN con la tabella user_badges, che contiene 84.000 righe. Sembra che qualcosa nell’ultima condizione WHERE ub.badge_id = 15 AND q.user_id IS NULL stia facendo esplodere questa query. Se rimuovo la clausola WHERE, la query viene eseguita in un tempo ragionevole (circa 20 secondi), ma se includo anche solo WHERE ub.badge_id = 15, non riesco nemmeno a far eseguire un EXPLAIN su questa query in un tempo ragionevole. L’EXPLAIN è in sospeso da diversi minuti senza restituire risultati. L’esecuzione effettiva della query completa è in corso da ore. Possiamo fare qualcosa per ottimizzare questa query?
Dalla lettura dei topic qui su Meta dall’ultima notte, sembra che nessun job dovrebbe durare oltre le 8 ore, specialmente per un database di grandi dimensioni.
Ma non sono sicuro di cosa possiamo fare ulteriormente per migliorarlo.
È assurdo per me che non riusciamo a ottenere nulla da EXPLAIN perché si blocca comunque.
Guardando questo file: discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
C’è il seguente codice che esegue questa query, attualmente bloccata. Se cambio la prima join da LEFT JOIN a INNER JOIN, la query viene eseguita istantaneamente. C’è una ragione per cui questa deve essere una 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
È possibile rendere più veloci queste query per i badge?
Un’altra query che sembra problematica è questa, credo proveniente dal lavoro di pulizia settimanale:
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
L’EXPLAIN per questa query mostra che sta cercando di ordinare tutte le 26 milioni di righe nella tabella posts. Non riesco a capire quale metodo utilizzerà per questa query, ma basandomi sul fatto che l’attesa attiva è “DataFileRead”, penso che stia accedendo al disco per qualcosa…
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)
Ho la forte sensazione che tu stia remando controcorrente qui e che il database che stai utilizzando non abbia semplicemente risorse sufficienti per eseguire Discourse.
Queste query sono effettivamente costose, ma ospitiamo molti forum di grandi dimensioni (ad esempio: About - Straight Dope Message Board 22 milioni di post) e siamo in grado di eseguire tutte queste query senza problemi su quell’istanza.
È un server dedicato con le seguenti specifiche:
AMD Ryzen 7 3800X
64 GB di RAM ECC @ 2666 MHz
2 x 1,2 TB Intel P3600 NVMe SSD (ZFS RAID 1)
All’VM che esegue Discourse sono stati assegnati 8 core CPU e 32 GB di RAM.
Credo di aver individuato il problema con la prima query, o almeno un modo per istruire il pianificatore delle query a prendere la decisione corretta. Ecco la query che non completava l’esecuzione in oltre 16 ore (questa è per il badge Primo Citato):
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 aggiungo una singola riga ORDER BY nella posizione corretta, questa query ora si completa in pochi secondi:
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
Sembra che dovrebbe essere abbastanza intelligente da eseguire questo ordinamento nel posto giusto, ma a quanto pare non lo è… Comunque, la soluzione sembra piuttosto semplice in questo momento.
Non ho ancora iniziato a esaminare approfonditamente l’altra query relativa a percent_rank.
A volte la pianificazione è compromessa quando le statistiche sono scadenti… in alcuni casi eccezionali, un vacuum completo può aiutare; dopo le importazioni è assolutamente consigliato eseguire un vacuum minimo. Penso che tu abbia fatto entrambe le cose.
C’è un motivo per cui stai eseguendo in una VM invece che con Docker direttamente sull’host?
Quali risorse sono allocate a Straight Dope, se posso chiedere, e ha qualche posizione che richiede diverse ore di lavoro come facciamo noi con 27 milioni?
Sì, ho eseguito VACUUM ANALYZE alcune volte. Le statistiche dovrebbero essere corrette, ma sembra che il sistema stia scegliendo in modo inadeguato attraverso più ricostruzioni, regolazioni di tuning di Postgres e vacuum.
Stiamo eseguendo altre VM su questa macchina host, ma al momento abbiamo risorse disponibili, quindi è qui che ho costruito un sistema per testare Discourse.
Dalla nostra istanza di grandi dimensioni, guardando a: /sidekiq/scheduler
E
Hai eseguito un FULL VACUUM?
Il nostro server di database ha prestazioni hardware simili alle tue (anche se abbiamo un I/O più veloce dato che abbiamo un array RAID più grande). Tuttavia, non eseguiamo affatto la virtualizzazione. Questa è una grande differenza.
Non l’ho fatto. Posso provare e vedere se il comportamento cambia.
Sono sicuro che ci sia una certa perdita di prestazioni dovuta all’esecuzione in una VM, ma nulla sta sovraccaricando pesantemente l’hardware. Quando ho eseguito l’importazione per trasferire tutti i nostri dati dal nostro altro software, sono riuscito a ottenere un utilizzo del 60-70% di tutti gli 8 core eseguendo contemporaneamente più processi di importazione.
Ora, quando questi processi sono in attesa e in ciclo continuo, di solito non vedo la media del carico superare 2-3, quindi non stanno nemmeno utilizzando tutta la CPU disponibile.
Il vacuum completo è qualcosa che ho visto aiutare dopo migrazioni massive; sono curioso di vedere se ha un impatto.
Attualmente è in esecuzione.
Per la query percent_rank che sembra far parte del lavoro settimanale, com’è l’EXPLAIN per la tua istanza di grandi dimensioni? La mia istanza sta riportando un costo della query superiore a 8 milioni su quella, il che sembra un po’ preoccupante.
Hai qualche raccomandazione per i valori di tuning di postgres in app.yml? Attualmente sto usando:
shared_buffers: 16GB
work_mem: 512MB
Il comando VACUUM FULL è stato completato, ma non sembra aver fatto alcuna differenza nelle prestazioni della query. La query per il badge sembra ancora che richiederà ore e ore per essere eseguita, a meno che non aggiunga la clausola ORDER BY, e la query percent_rank è in esecuzione da due ore senza completare. Probabilmente dovremo modificare la definizione SQL per il badge Primo Citato e poi dovrò vedere cosa si può fare per risolvere il problema della query percent_rank.
Hai qualche suggerimento per la query percent_rank basato su questo 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 righe)
Potresti provare a modificare il limite; forse con un limite di 1000 sarà abbastanza veloce per te.
Modificare il limite non sembra alterare molto il piano di query (né i costi né altro). Il problema sembra essere che la query deve ordinare l’intera tabella posts (che nel nostro caso contiene circa 26,5 milioni di righe) prima di poter eseguire l’operazione. Potrebbe esserci un’opportunità per creare un indice qui. Al momento non vedo la colonna score inclusa in nessuno degli indici sulla tabella posts.
La classifica è per argomento, non classifica l’intero set.
Potresti magari applicare un filtro sugli ID degli argomenti… WHERE topic_id < 1000 … 2000 … 10000 e così via… Probabilmente, una volta completato l’aggiornamento iniziale, questa operazione sarà più veloce.