Thread: Aggressive freezing in lazy-vacuum

"Jim C. Nasby" <jim@nasby.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

ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> 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.  You need to show a fairly amazing
performance gain to justify that, and I don't think you can.

            regards, tom lane

Tom Lane wrote:
> ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
>> 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.  You need to show a fairly amazing
> performance gain to justify that, and I don't think you can.

There could be a GUC vacuum_freeze_limit, and the actual FreezeLimit
would be calculated as
GetOldestXmin() - vacuum_freeze_limit

The default for vacuum_freeze_limit would be MaxTransactionId/2, just
as it is now.

greetings, Florian Pflug

Florian G. Pflug wrote:
> There could be a GUC vacuum_freeze_limit, and the actual FreezeLimit
> would be calculated as
> GetOldestXmin() - vacuum_freeze_limit

We already have that. It's called vacuum_freeze_min_age, and the default
is 100 million transactions.

IIRC we added it late in the 8.2 release cycle when we changed the clog
truncation point to depend on freeze limit.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas wrote:
> Florian G. Pflug wrote:
>> There could be a GUC vacuum_freeze_limit, and the actual FreezeLimit
>> would be calculated as
>> GetOldestXmin() - vacuum_freeze_limit
>
> We already have that. It's called vacuum_freeze_min_age, and the default
> is 100 million transactions.
>
> IIRC we added it late in the 8.2 release cycle when we changed the clog
> truncation point to depend on freeze limit.

Ok, that explains why I didn't find it when I checked the source - I
checked the 8.1 sources by accident ;-)

Anyway, thanks for pointing that out ;-)

greetings, Florian Pflug