Aggressive freezing in lazy-vacuum

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Aggressive freezing in lazy-vacuum
Date: 2007-03-05 10:14:34
Message-ID: 20070305182401.C4BC.ITAGAKI.TAKAHIRO@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches pgsql-performance

"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

Attachment Content-Type Size
aggressive_freeze.patch application/octet-stream 10.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2007-03-05 10:32:02 Stream bitmaps
Previous Message Gregory Stark 2007-03-05 10:10:47 Re: Bug: Buffer cache is not scan resistant

Browse pgsql-patches by date

  From Date Subject
Next Message Neil Conway 2007-03-05 14:14:04 Re: Trivial comment fix
Previous Message Heikki Linnakangas 2007-03-05 09:51:14 Trivial comment fix

Browse pgsql-performance by date

  From Date Subject
Next Message Arjen van der Meijden 2007-03-05 10:52:08 Re: [kris@obsecurity.org: Progress on scaling of FreeBSD on 8 CPU systems]
Previous Message ITAGAKI Takahiro 2007-03-05 08:22:16 Re: Automatic adjustment of bgwriter_lru_maxpages