Aggressive freezing in lazy-vacuum

"Jim C. Nasby" <jim(at)nasby(dot)net> wrote:

> > * Aggressive freezing
> > we will use OldestXmin as the threshold to freeze tuples in
> > dirty pages or pages that have some dead tuples. Or, many UNFROZEN
> > pages still remain after vacuum and they will cost us in the next
> > vacuum preventing XID wraparound.
>
> Another good idea. If it's not too invasive I'd love to see that as a
> stand-alone patch so that we know it can get in.

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:

- The page is already dirty.
- There are another tuple to be frozen in the same page.
- There are another dead tuples in the same page.
Freezing is delayed until the heap vacuum phase.

Anyway we create new dirty buffers and/or write WAL then, so additional
freezing is almost free. Keeping the number of unfrozen tuples low,
we can reduce the cost of next XID wraparound vacuum and piggyback
multiple freezing operations in the same page.

The following test shows differences of the number of unfrozen tuples
with or without the patch. Formerly, recently inserted tuples are not
frozen immediately (1). Even if there are some dead tuples in the same
page, unfrozen live tuples are not frozen (2). With patch, the number
after first vacuum was already low (3), because the pages including recently
inserted tuples were dirty and not written yet, so aggressive freeze was
performed for it. Moreover, if there are dead tuples in a page, other live
tuples in the same page are also frozen (4).

# CREATE CAST (xid AS integer) WITHOUT FUNCTION AS IMPLICIT;

[without patch]
$ ./pgbench -i -s1 (including vacuum)
# SELECT count(*) FROM accounts WHERE xmin > 2; => 100000 (1)
# UPDATE accounts SET aid = aid WHERE aid % 20 = 0; => UPDATE 5000
# SELECT count(*) FROM accounts WHERE xmin > 2; => 100000
# VACUUM accounts;
# SELECT count(*) FROM accounts WHERE xmin > 2; => 100000 (2)

[with patch]
$ ./pgbench -i -s1 (including vacuum)
# SELECT count(*) FROM accounts WHERE xmin > 2; => 2135 (3)
# UPDATE accounts SET aid = aid WHERE aid % 20 = 0; => UPDATE 5000
# SELECT count(*) FROM accounts WHERE xmin > 2; => 7028
# VACUUM accounts;
# SELECT count(*) FROM accounts WHERE xmin > 2; => 0 (4)

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center