Auto Vacuum während Updates

Allgemeines zu PostgreSQL.
chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Auto Vacuum während Updates

Beitrag von chkaufmann » Mo 15. Okt 2018, 16:02

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

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Auto Vacuum während Updates

Beitrag von castorp » Mo 15. Okt 2018, 16:45

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.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Re: Auto Vacuum während Updates

Beitrag von chkaufmann » Mo 15. Okt 2018, 17:05

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

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Auto Vacuum während Updates

Beitrag von castorp » Mo 15. Okt 2018, 17:54

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.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Re: Auto Vacuum während Updates

Beitrag von chkaufmann » Di 16. Okt 2018, 08:00

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:

Code: Alles auswählen

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:

Code: Alles auswählen

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

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Auto Vacuum während Updates

Beitrag von castorp » Di 16. Okt 2018, 08:45

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:

Code: Alles auswählen

update rankingitem  r
   set place = t.place, 
       swimresultid = t.swimresultid
from temp_import t 
where r.rankingclubid = t.rankingclubid
  and r.sortcode = t.sortcode;
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Re: Auto Vacuum während Updates

Beitrag von chkaufmann » Di 16. Okt 2018, 09:01

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

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Auto Vacuum während Updates

Beitrag von castorp » Di 16. Okt 2018, 09:19

Batch-Updates wären auf jeden Fall ein Verbesserung und bedeutet vermutlich weniger Umstellung als eine Temporäre Tabelle
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

chkaufmann
Neuer Benutzer
Beiträge: 21
Registriert: Di 8. Mai 2018, 08:38

Re: Auto Vacuum während Updates

Beitrag von chkaufmann » Di 6. Nov 2018, 23:33

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:

Code: Alles auswählen

SELECT * FROM RANKINGITEM WHERE RANKINGCLUBID = $1 ORDER BY SORTCODE
mit folgendem Plan:

Code: Alles auswählen

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:

Code: Alles auswählen

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

akretschmer
Erfahrener Benutzer
Beiträge: 7513
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Auto Vacuum während Updates

Beitrag von akretschmer » Mi 7. Nov 2018, 09:26

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Antworten