Tom Lane <[EMAIL PROTECTED]> wrote: > > This is a stand-alone patch for aggressive freezing. I'll propose > > to use OldestXmin instead of FreezeLimit as the freeze threshold > > in the circumstances below: > > I think it's a really bad idea to freeze that aggressively under any > circumstances except being told to (ie, VACUUM FREEZE). When you > freeze, you lose history information that might be needed later --- for > forensic purposes if nothing else.
I don't think we can supply such a historical database functionality here, because we can guarantee it just only for INSERTed tuples even if we pay attention. We've already enabled autovacuum as default, so that we cannot predict when the next vacuum starts and recently UPDATEd and DELETEd tuples are removed at random times. Furthermore, HOT will also accelerate removing expired tuples. Instead, we'd better to use WAL or something like audit logs for keeping history information. > You need to show a fairly amazing > performance gain to justify that, and I don't think you can. Thank you for your advice. I found that aggressive freezing for already dirty pages made things worse, but for pages that contain other tuples being frozen or dead tuples was useful. I did an acceleration test for XID wraparound vacuum. I initialized the database with $ ./pgbench -i -s100 # VACUUM FREEZE accounts; # SET vacuum_freeze_min_age = 6; and repeated the following queries. CHECKPOINT; UPDATE accounts SET aid=aid WHERE random() < 0.005; SELECT count(*) FROM accounts WHERE xmin > 2; VACUUM accounts; After the freeze threshold got at vacuum_freeze_min_age (run >= 3), the VACUUM became faster with aggressive freezing. I think it came from piggybacking multiple freezing operations -- the number of unfrozen tuples were kept lower values. * Durations of VACUUM [sec] run| HEAD | freeze ---+--------+-------- 1 | 5.8 | 8.2 2 | 5.2 | 9.0 3 | 118.2 | 102.0 4 | 122.4 | 99.8 5 | 121.0 | 79.8 6 | 122.1 | 77.9 7 | 123.8 | 115.5 ---+--------+-------- avg| 121.5 | 95.0 3-7| * Numbers of unfrozen tuples run| HEAD | freeze ---+--------+-------- 1 | 50081 | 50434 2 | 99836 | 100072 3 | 100047 | 86484 4 | 100061 | 86524 5 | 99766 | 87046 6 | 99854 | 86824 7 | 99502 | 86595 ---+--------+-------- avg| 99846 | 86695 3-7| Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org