Ich habe zwei Jobs von Sidekiq, die anscheinend sehr lange brauchen, um abgeschlossen zu werden. Es scheint derselbe Gesamtprozess zu sein, aber es gibt zwei Jobs, die den Status RUNNING anzeigen. Diese Jobs laufen jetzt seit 6 Stunden, und PostgreSQL hängt in der CLI, wenn ich versuche, sogar eine EXPLAIN ANALYZE für die erste dieser Abfragen auszuführen.
Haben Sie eine Idee, was bewirken könnte, dass diese Abfragen so lange zur Ausführung brauchen?
Fühlen Sie sich frei, diese Abfragen zu beenden, und lassen Sie das System es später erneut versuchen.
Das habe ich eigentlich schon früher gemacht. Ich habe den Job abgebrochen, damit ein Reindex abgeschlossen werden konnte, und dies ist das Neustarten danach.
Es sieht so aus, als ob die Jobs heute Morgen irgendwann in der Nacht fertig wurden und nun wieder laufen. Aktueller Status:
Diese Unterabfrage gibt auf unserer Instanz etwa 13.000 Zeilen zurück:
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
Anschließend wird ein LEFT JOIN mit der Tabelle user_badges durchgeführt, die 84.000 Zeilen enthält. Es scheint, als würde etwas in der letzten Bedingung WHERE ub.badge_id = 15 AND q.user_id IS NULL diese Abfrage in die Knie zwingen. Wenn ich die WHERE-Klausel weglasse, wird sie in einer angemessenen Zeit ausgeführt (etwa 20 Sekunden). Sobald ich jedoch sogar nur WHERE ub.badge_id = 15 hinzufüge, kann ich nicht einmal mehr eine EXPLAIN-Abfrage in angemessener Zeit ausführen. Die EXPLAIN-Abfrage hängt seit mehreren Minuten fest und liefert keine Ergebnisse. Die tatsächliche Ausführung der vollständigen Abfrage läuft seit Stunden. Gibt es etwas, das wir tun können, um diese Abfrage zu optimieren?
Da ich seit gestern Abend hier auf Meta Themen lese, scheint es, als sollte keine Aufgabe länger als 8 Stunden laufen, insbesondere bei einer überdimensionierten Datenbank.
Aber ich bin mir nicht sicher, was wir noch tun können, um das zu verbessern.
Es ist für mich unfassbar, dass wir EXPLAIN nicht ausführen können, da es einfach hängt.
Ich schaue mir diese Datei an: discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
Es gibt den folgenden Code, der diese Abfrage ausführt, die derzeit hängen bleibt. Wenn ich den ersten Join von LEFT JOIN in INNER JOIN ändere, wird die Abfrage sofort ausgeführt. Gibt es einen Grund, warum dies ein Left Join sein muss?
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
Ist es möglich, diese Badge-Abfragen zu beschleunigen?
Eine weitere Anfrage, die schlecht aussieht, ist diese hier, meiner Meinung nach aus dem wöchentlichen Bereinigungsauftrag:
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
Die Erklärung dazu zeigt, dass versucht wird, alle 26 Millionen Zeilen in der Tabelle posts zu sortieren. Ich kann nicht sagen, welche Methode diese Abfrage verwenden wird, aber basierend auf der Tatsache, dass der aktive Wartezeitpunkt “DataFileRead” ist, vermute ich, dass sie für etwas auf die Festplatte zugreift…
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)
Ich habe das starke Gefühl, dass du hier gegen den Strom schwimmst und die von dir verwendete Datenbank einfach nicht über ausreichende Ressourcen verfügt, um Discourse zu betreiben.
Diese Abfragen sind zwar tatsächlich aufwendig, aber wir hosten zahlreiche große Foren (z. B. About - Straight Dope Message Board 22 Millionen Beiträge) und können alle diese Abfragen auf dieser Instanz problemlos ausführen.
Es handelt sich um einen dedizierten Server mit folgenden Spezifikationen:
AMD Ryzen 7 3800X
64 GB ECC-RAM @ 2666 MHz
2 x 1,2 TB Intel P3600 NVMe SSD (ZFS RAID 1)
Der VM, auf der Discourse läuft, wurden 8 CPU-Kerne und 32 GB RAM zugewiesen.
Ich glaube, ich habe das Problem mit der ersten Abfrage gefunden, oder zumindest eine Möglichkeit, den Abfrageplaner anzuweisen, die richtige Entscheidung zu treffen. Hier ist die Abfrage, die nicht innerhalb von 16+ Stunden abgeschlossen wurde (dies ist für das „First Quote“-Abzeichen):
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
Wenn ich an der richtigen Stelle eine einzelne ORDER BY-Zeile hinzufüge, wird diese Abfrage nun in wenigen Sekunden abgeschlossen:
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
Ich hätte gedacht, dass es intelligent genug ist, diese Sortierung an der richtigen Stelle vorzunehmen, aber es scheint nicht so zu sein… Trotzdem scheint die Lösung in diesem Fall ziemlich einfach zu sein.
Ich habe mich noch nicht wirklich mit der anderen Abfrage bezüglich percent_rank beschäftigt.
Manchmal ist die Planung fehlerhaft, wenn die Statistiken schlecht sind … in einigen Ausnahmefällen kann ein vollständiges VACUUM helfen, ein minimales VACUUM wird nach Importen dringend empfohlen. Ich denke, du hast beides gemacht.
Gibt es einen Grund, warum du in einer VM statt Docker direkt auf dem Host ausführst?
Welche Ressourcen stehen dem Straight Dope zur Verfügung, wenn ich fragen darf? Und gibt es dort Stellen, die mehrere Stunden in Anspruch nehmen, wie es bei uns mit 27 Millionen der Fall ist?
Ja, ich habe VACUUM ANALYZE ein paar Mal ausgeführt. Die Statistiken sollten korrekt sein, aber es scheint, dass dies über mehrere Neuaufbauten, Postgres-Tuning-Anpassungen und VACUUMs hinweg schlecht gewählt wird.
Wir führen auf diesem Host-Computer andere VMs aus, aber wir haben derzeit überschüssige Ressourcen, daher habe ich hier ein System zum Testen von Discourse aufgebaut.
Von unserer großen Instanz aus betrachtet: /sidekiq/scheduler
Und
Hast du ein FULL VACUUM durchgeführt?
Unser Datenbankserver hat eine in etwa vergleichbare Hardwareleistung wie deiner (obwohl wir aufgrund eines größeren RAID-Arrays eine schnellere E/A haben). Allerdings betreiben wir keine Virtualisierung. Das ist ein großer Unterschied.
Das habe ich nicht. Ich kann es gerne einmal ausprobieren und sehen, ob sich das Verhalten ändert.
Ich bin mir sicher, dass durch den Betrieb in einer VM ein gewisser Leistungsverlust entsteht, aber nichts belastet die Hardware stark. Als ich den Import durchführte, um alle unsere Daten von unserer anderen Software zu übernehmen, konnte ich bei gleichzeitiger Ausführung mehrerer Importprozesse eine Auslastung von 60–70 % aller 8 Kerne erreichen.
Wenn diese Jobs derzeit im Leerlauf sind und nur noch drehen, sehe ich die Last durchschnittlich meist nicht über 2–3, sodass sie nicht einmal die gesamte verfügbare CPU-Auslastung beanspruchen.
Der vollständige VACUUM-Befehl hat mir nach massiven Migrationen schon geholfen. Ich bin gespannt, ob er hier eine Auswirkung hat.
Es läuft derzeit.
Bei der percent_rank-Abfrage, die anscheinend Teil des wöchentlichen Jobs ist: Wie sieht die EXPLAIN-Ausgabe für Ihre große Instanz aus? Meine Instanz meldet eine Abfragekosten von über 8 Millionen für diese Abfrage, was etwas beunruhigend wirkt.
Haben Sie Empfehlungen für die PostgreSQL-Tuning-Werte in app.yml? Momentan verwende ich:
shared_buffers: 16GB
work_mem: 512MB
Der VACUUM FULL wurde abgeschlossen, scheint aber keine Auswirkung auf die Abfrageleistung gehabt zu haben. Die Badge-Abfrage scheint immer noch stundenlang laufen zu müssen, es sei denn, ich füge die ```ORDER BY``*-Klausel hinzu, und die percent_rank-Abfrage läuft nun seit zwei Stunden ohne Abschluss. Wir werden wahrscheinlich die SQL-Definition für das „First Quote“-Badge ändern müssen, und anschließend muss ich prüfen, was zur Behebung der percent_rank-Abfrage getan werden kann.
Haben Sie auf Basis dieser EXPLAIN-Ausgabe Vorschläge für die percent_rank-Abfrage?
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)
Du könntest versuchen, das Limit anzupassen. Vielleicht ist es bei einem Limit von 1000 schnell genug für dich.
Die Änderung des Limits scheint den Abfrageplan (Kosten oder sonstiges) kaum zu beeinflussen. Das Problem scheint darin zu bestehen, dass die Abfrage die gesamte posts-Tabelle sortieren muss (in unserem Fall etwa 26,5 Millionen Zeilen), bevor sie die Operation ausführen kann. Hier könnte sich die Möglichkeit bieten, einen Index zu erstellen. Der Spalte score wird derzeit in keinem der Indizes auf der posts-Tabelle berücksichtigt.
Die Rangliste erfolgt pro Thema, nicht für den gesamten Datensatz.
Sie könnten die Abfrage nach Themen-IDs filtern … WHERE topic_id < 1000 … 2000 … 10000 und so weiter … Wahrscheinlich wird dies nach dem ersten Update schneller ausgeführt.