Re: Aggressive freezing in lazy-vacuum

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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