Auto Vacuum während Updates

Auf einer Tabelle mit ca. 150 Millionen Datensätzen (4 32bit-Integer Werte) muss ich jeweils am Montag auf 2-3 Millionen ein Update machen.

Mit meinen aktuellen Einstellungen startet auto vacuum immer wieder. Ich habe mich nun gefragt, ob das so ok ist oder ob es schneller wäre, wenn ich auto vacuum für die Tabelle ausschalte, bis die Updates gemacht sind und dann erst wieder auto vacuum zulasse.

Indices (2 Stück) kann ich nicht ausschalten, da es während Update Lesezugriffe von einer Website gibt.

Meine Einstellungen:
Postgres SQL 10.4, 64bit, Windows 2012 Server

autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 1000
autovacuum_vacuum_scale_factor=0.001 (nur für diese Tabelle)

Alle übrigen Werte sind auf “default”.

Christian

Was ist wenn Du autovacuum_naptime für diese Tabelle größer machst? Standardwert dafür ist 1 Minute.

Mal angenommen, Dein UPDATE dauert 5 Minuten, dann könntest Du die naptime auf z.B. 6 Minuten stellen. Wenn der auto-vacuum Daemon während dem UPDATE startet, dann versucht er es das nächste Mal erst dann, wenn das UPDATE mit hoher Wahrscheinlichkeit beendet wurde.

Nun, mein Update Task dauert länger. Je nach Input vom Wochenende sind es mehrere Stunden.

Am wichtigsten ist es, dass die Lesezugriffe von der Website schnell bleiben. Das Update darf auch länger dauern.

Christian

Wenn Du ausschließlich Montags UPDATEs auf die Tabelle machst, dann kannst Du natürlich auto vacuum für die Tabelle ganz ausschalten und das VACUUM ANALYZE nach dem UPDATE manuell machen. Oder Du setzt die naptime auf mehrere Stunden.

Wobei ganz ausschalten meistens keine so gute Idee ist.

Die eigentliche Frage ist: warum dauert der UPDATE so lang? mehrere Stunden für “nur” 3 Millionen Datensätze klingt jetzt erstmal - ohne Näheres zu wissen - viel zu lang.

Ich habe mal ein Query auf pg_stat_statements gemacht und erhalte folgende Werte für mein Update Query:
calls: 114’021’663
rows: 114’021’445
totaltime: 164’543’016

Das gäbe total 24 Minuten für 1’000’000 Updates.

Das ist die Tabelle:

CREATE TABLE rankingitem (
  place Integer,
  rankingclubid Integer,
  sortcode Integer,
  swimresultid Integer
);
CREATE INDEX ix_rankingitem_ranking
  ON rankingitem(rankingclubid, sortcode);
CREATE INDEX ix_rankingitem_swimresult
  ON rankingitem(swimresultid, rankingclubid);

und das ist mein Update Query:

UPDATE RANKINGITEM SET PLACE = $1, SWIMRESULTID = $2 WHERE RANKINGCLUBID = $3 AND SORTCODE = $4

Wo ich da noch optimieren kann, ist mir grad nicht klar.

Christian

Hmm, kann es sein, das Du da Millionen von Single-Row updates machst?

Deine Beschreibung klingt danach, als würdest Du irgendwoher z.B. eine Textdatei mit den Resultaten bekommen.

Wenn das der Fall ist, wäre es wesentlich effizienter, diese Daten in eine Temp Tabelle zu importieren (z.B. mit COPY, das dauert nur ein paar Sekunden bei nur 4 Millionen Datensätzen) und dann - unter der Annahme, dass (rankingclubid, sortcode) eindeutig ist - z.B. sowas zu machen:

update rankingitem  r
   set place = t.place, 
       swimresultid = t.swimresultid
from temp_import t 
where r.rankingclubid = t.rankingclubid
  and r.sortcode = t.sortcode;

Ja, im Moment sind es “Single-Row updates” aus einem Delphi Programm. Die Library, die ich verwende bietet noch Batch Updates und ich werde das wohl mal ausprobieren.
Und sonst ist Dein Vorschlag eine gute Idee: Ich stelle die Updates in einer Textdatei zusammen, importiere das in eine temporäre Tabelle und mache das Update auf der grossen Tabelle mit dem von Dir vorgeschlagenen Command.

Danke für alle Hinweise.

Christian

Batch-Updates wären auf jeden Fall ein Verbesserung und bedeutet vermutlich weniger Umstellung als eine Temporäre Tabelle

Ich habe meine Update Funktion nun verbessert und schreibe alles zuerst in eine temporäre Tabelle. Zudem lese ich vorher die bestehenden Datensätze, so dass ich nur dort wo nötig ein Update / Insert mache. Dazu verwende ich folgendes query:

SELECT * FROM RANKINGITEM WHERE RANKINGCLUBID = $1 ORDER BY SORTCODE

mit folgendem Plan:

QUERY PLAN
Index Scan using ix_rankingitem_ranking on rankingitem  (cost=0.57..6345.51 rows=2896 width=16) (actual time=0.102..0.191 rows=100 loops=1)
  Index Cond: (rankingclubid = 8556634)"
    Buffers: shared hit=17 read=33 dirtied=6
Planning time: 0.087 ms
Execution time: 0.205 ms

Häufig ist dieser Aufruf extrem langsam (mehrere Sekunden für max. ein paar tausend Datensätze). Scheinbar sind da noch Aufräumarbeiten von vorherigen Updates nötig. Der Eintrag in pg_stat_statements sieht so aus:

calls               = 3'509'518    
totaltime           =  66'233s     
mintime             =       0 
maxtime             = 185'279ms
meantime            =      18ms
stddevtime          =     592ms
rows                = 153'289'311
shared_blks_hit     =  47'968'847  
shared_blks_read    =  36'992'187  
shared_blks_dirtied =   4'481'981  
shared_blks_written =     316'322

Mir fallen hier die hohen Werte für shared_blks_dirtied und shared_blks_written auf. Ich habe in den Dokumentationen gesucht aber dazu keine verwendenbaren Infos gefunden.

Was muss ich anders machen, damit die Updates und auch das Select schneller wird?

Christian

ich vermute mal, Du hast da zumindest zeitweise massiv table-bloat. Dirtied/written bei Select bedeuted, das die visibility-hints aktualisiert werden. Das passiert idealerweise aber beim vacuum.

Nun, auto vacuum läuft während des Updates quasi dauernd. Hier ein Auszug aus dem Log File:

2018-11-06 00:05:46.607 CET [744] LOG:  automatic vacuum of table "swimrankings.public.rankingitem": index scans: 1
	pages: 0 removed, 976332 remain, 3 skipped due to pins, 191153 skipped frozen
	tuples: 32889 removed, 172486024 remain, 41392 are dead but not yet removable, oldest xmin: 127959159
	buffer usage: 194404 hits, 1148843 misses, 96910 dirtied
	avg read rate: 9.645 MB/s, avg write rate: 0.814 MB/s
	system usage: CPU: user: 24.67 s, system: 3.98 s, elapsed: 930.56 s
2018-11-06 00:12:51.687 CET [6032] LOG:  automatic vacuum of table "swimrankings.public.rankingclub": index scans: 1
	pages: 0 removed, 92054 remain, 0 skipped due to pins, 4466 skipped frozen
	tuples: 4489 removed, 10167374 remain, 0 are dead but not yet removable, oldest xmin: 127992479
	buffer usage: 32120 hits, 103768 misses, 10443 dirtied
	avg read rate: 5.214 MB/s, avg write rate: 0.525 MB/s
	system usage: CPU: user: 2.60 s, system: 0.50 s, elapsed: 155.49 s
2018-11-06 00:34:09.556 CET [6032] LOG:  automatic vacuum of table "swimrankings.public.rankingitem": index scans: 1
	pages: 0 removed, 976332 remain, 1 skipped due to pins, 137991 skipped frozen
	tuples: 262503 removed, 173017774 remain, 46146 are dead but not yet removable, oldest xmin: 127996197
	buffer usage: 378173 hits, 1439679 misses, 294860 dirtied
	avg read rate: 8.862 MB/s, avg write rate: 1.815 MB/s
	system usage: CPU: user: 41.71 s, system: 5.84 s, elapsed: 1269.17 s
2018-11-06 01:01:51.000 CET [3784] LOG:  automatic vacuum of table "swimrankings.public.rankingitem": index scans: 1
	pages: 0 removed, 976332 remain, 0 skipped due to pins, 156323 skipped frozen
	tuples: 227262 removed, 172521519 remain, 32854 are dead but not yet removable, oldest xmin: 128013464
	buffer usage: 350130 hits, 1360561 misses, 264551 dirtied
	avg read rate: 8.210 MB/s, avg write rate: 1.596 MB/s
	system usage: CPU: user: 50.26 s, system: 6.34 s, elapsed: 1294.74 s

Ich habe mich darum auch schon gefragt, ob es besser wäre, das gezielter auszuführen.

  • Select’s um Daten zu laden
  • Update’s / Insert’s machen
  • Vaccum explizit aufrufen
  • und wieder von vorne beginnen

Und im Gegensatz auto vaccum wieder weniger “aggressiv” für diese Tabelle. Im Moment ist autovacuum_vacuum_scale_factor = 0.0001.

Ich habe auch fillfactor = 80 gesetzt, aber das scheint die Sache nicht wirklich zu verbessern.

Christian

ja, setze den scale_factor höher, damit das nicht ständig durch die Tabelle rennen muß. 20% bloat in einer table sind normalerweise kein Problem. Nach größeren Änderungen kannst / solltest Du aber ein explizietes vacuum analyse laufen lassen.

Bringt es etwas wenn ich den im Select verwendeten Index unique mache und als primary key definiere?

Gibt es andere Möglichkeiten um zu verhindern, dass während dem Select diese Aufräumarbeiten, die sonst bei VACUUM kommen, durchgeführt werden müssen?

Christian

ob ein solcher index in diesem Fall was bringt - probieren. Generell sollte jede Tabelle aber einen PK haben. Das macht Tools wie PGAdmin es leichter und auch wenn Du mal z.B. mit BDR einen Multi-Master-Cluster aufsetzen willst wird ein PK dir sehr helfen.

Das, was Vacuum macht, ist nützlich und richtig. Man kann aber, wie schon gesagt, mit 20% bloat ganz gut leben - meistens. Fillfactor 80% ist übrigens auch gut, damit förderst Du HOT-Updates.