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