pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAggressive freezing in lazy-vacuum
11+ messages / 5 participants
[nested] [flat]
* Aggressive freezing in lazy-vacuum
@ 2007-03-05 10:14 ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: ITAGAKI Takahiro @ 2007-03-05 10:14 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; pgsql-patches@postgresql.org
"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
Attachments:
aggressive_freeze.patch (application/octet-stream, 10.7K) download
diff -cpr HEAD/src/backend/commands/vacuumlazy.c aggressive_freeze/src/backend/commands/vacuumlazy.c
*** HEAD/src/backend/commands/vacuumlazy.c Mon Feb 26 09:46:04 2007
--- aggressive_freeze/src/backend/commands/vacuumlazy.c Mon Mar 5 19:11:09 2007
*************** lazy_scan_heap(Relation onerel, LVRelSta
*** 220,226 ****
{
BlockNumber nblocks,
blkno;
- HeapTupleData tuple;
char *relname;
BlockNumber empty_pages,
vacuumed_pages;
--- 220,225 ----
*************** lazy_scan_heap(Relation onerel, LVRelSta
*** 260,268 ****
maxoff;
bool tupgone,
hastup;
! int prev_dead_count;
! OffsetNumber frozen[MaxOffsetNumber];
! int nfrozen;
vacuum_delay_point();
--- 259,267 ----
maxoff;
bool tupgone,
hastup;
! int ndead,
! nlive;
! OffsetNumber live_tuples[MaxHeapTuplesPerPage];
vacuum_delay_point();
*************** lazy_scan_heap(Relation onerel, LVRelSta
*** 342,356 ****
continue;
}
- nfrozen = 0;
hastup = false;
! prev_dead_count = vacrelstats->num_dead_tuples;
maxoff = PageGetMaxOffsetNumber(page);
for (offnum = FirstOffsetNumber;
offnum <= maxoff;
offnum = OffsetNumberNext(offnum))
{
! ItemId itemid;
itemid = PageGetItemId(page, offnum);
--- 341,355 ----
continue;
}
hastup = false;
! ndead = nlive = 0;
maxoff = PageGetMaxOffsetNumber(page);
for (offnum = FirstOffsetNumber;
offnum <= maxoff;
offnum = OffsetNumberNext(offnum))
{
! ItemId itemid;
! HeapTupleData tuple;
itemid = PageGetItemId(page, offnum);
*************** lazy_scan_heap(Relation onerel, LVRelSta
*** 401,406 ****
--- 400,406 ----
{
lazy_record_dead_tuple(vacrelstats, &(tuple.t_self));
tups_vacuumed += 1;
+ ndead += 1;
}
else
{
*************** lazy_scan_heap(Relation onerel, LVRelSta
*** 408,458 ****
hastup = true;
/*
! * Each non-removable tuple must be checked to see if it
! * needs freezing. If we already froze anything, then
! * we've already switched the buffer lock to exclusive.
*/
! if (heap_freeze_tuple(tuple.t_data, FreezeLimit,
! (nfrozen > 0) ? InvalidBuffer : buf))
! frozen[nfrozen++] = offnum;
}
} /* scan along page */
! /*
! * If we froze any tuples, mark the buffer dirty, and write a WAL
! * record recording the changes. We must log the changes to be
! * crash-safe against future truncation of CLOG.
! */
! if (nfrozen > 0)
{
! MarkBufferDirty(buf);
! /* no XLOG for temp tables, though */
! if (!onerel->rd_istemp)
{
! XLogRecPtr recptr;
! recptr = log_heap_freeze(onerel, buf, FreezeLimit,
! frozen, nfrozen);
! PageSetLSN(page, recptr);
! PageSetTLI(page, ThisTimeLineID);
}
}
!
! /*
! * If there are no indexes then we can vacuum the page right now
! * instead of doing a second scan.
! */
! if (nindexes == 0 &&
! vacrelstats->num_dead_tuples > 0)
{
! /* Trade in buffer share lock for super-exclusive lock */
! LockBuffer(buf, BUFFER_LOCK_UNLOCK);
! LockBufferForCleanup(buf);
! /* Remove tuples from heap */
! lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats);
! /* Forget the now-vacuumed tuples, and press on */
! vacrelstats->num_dead_tuples = 0;
! vacuumed_pages++;
}
/*
--- 408,497 ----
hastup = true;
/*
! * We don't freeze tuples here. If there are some dead tuples,
! * we delay freezing until lazy_vacuum_heap in order to avoid
! * making dirty buffers only for freezing. If no dead tuples,
! * we freeze them just below.
*/
! live_tuples[nlive++] = offnum;
}
} /* scan along page */
! if (ndead > 0)
{
! /*
! * If there are no indexes then we can vacuum the page right now
! * instead of doing a second scan.
! */
! if (nindexes == 0)
{
! Assert(vacrelstats->num_dead_tuples == ndead);
! /* Trade in buffer share lock for super-exclusive lock */
! LockBuffer(buf, BUFFER_LOCK_UNLOCK);
! LockBufferForCleanup(buf);
! /* Remove tuples from heap */
! lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats);
! /* Forget the now-vacuumed tuples, and press on */
! vacrelstats->num_dead_tuples = ndead = 0;
! vacuumed_pages++;
}
}
! else if (nlive > 0)
{
! int nfrozen;
! OffsetNumber frozen[MaxHeapTuplesPerPage];
! TransactionId limit;
!
! /* If the page is already dirty, we freeze tuples aggressively. */
! limit = (BufferIsDirty(buf) ? OldestXmin : FreezeLimit);
!
! nfrozen = 0;
! for (i = 0; i < nlive; i++)
! {
! ItemId itemid;
! HeapTupleHeader tuple;
!
! itemid = PageGetItemId(page, live_tuples[i]);
! tuple = (HeapTupleHeader) PageGetItem(page, itemid);
!
! /*
! * Each non-removable tuple must be checked to see if it
! * needs freezing. If we already froze anything, then
! * we've already switched the buffer lock to exclusive.
! */
! if (heap_freeze_tuple(tuple, limit,
! nfrozen > 0 ? InvalidBuffer : buf))
! {
! /*
! * If there are any tuples to be frozen in this page.
! * We will freeze leftover tuples aggressively. It
! * requires no additional costs.
! */
! limit = OldestXmin;
! frozen[nfrozen++] = live_tuples[i];
! }
! }
!
! /*
! * If we froze any tuples, mark the buffer dirty, and write a WAL
! * record recording the changes. We must log the changes to be
! * crash-safe against future truncation of CLOG.
! */
! if (nfrozen > 0)
! {
! MarkBufferDirty(buf);
! /* no XLOG for temp tables, though */
! if (!onerel->rd_istemp)
! {
! XLogRecPtr recptr;
!
! recptr = log_heap_freeze(onerel, buf, FreezeLimit,
! frozen, nfrozen);
! PageSetLSN(page, recptr);
! PageSetTLI(page, ThisTimeLineID);
! }
! }
}
/*
*************** lazy_scan_heap(Relation onerel, LVRelSta
*** 462,468 ****
* page, so remember its free space as-is. (This path will always be
* taken if there are no indexes.)
*/
! if (vacrelstats->num_dead_tuples == prev_dead_count)
{
lazy_record_free_space(vacrelstats, blkno,
PageGetFreeSpace(page));
--- 501,507 ----
* page, so remember its free space as-is. (This path will always be
* taken if there are no indexes.)
*/
! if (ndead == 0)
{
lazy_record_free_space(vacrelstats, blkno,
PageGetFreeSpace(page));
*************** lazy_vacuum_heap(Relation onerel, LVRelS
*** 571,577 ****
}
/*
! * lazy_vacuum_page() -- free dead tuples on a page
* and repair its fragmentation.
*
* Caller must hold pin and lock on the buffer.
--- 610,616 ----
}
/*
! * lazy_vacuum_page() -- free dead tuples and freeze live tuples on a page
* and repair its fragmentation.
*
* Caller must hold pin and lock on the buffer.
*************** lazy_vacuum_page(Relation onerel, BlockN
*** 587,607 ****
OffsetNumber unused[MaxOffsetNumber];
int uncnt;
Page page = BufferGetPage(buffer);
! ItemId itemid;
START_CRIT_SECTION();
! for (; tupindex < vacrelstats->num_dead_tuples; tupindex++)
{
! BlockNumber tblk;
! OffsetNumber toff;
! tblk = ItemPointerGetBlockNumber(&vacrelstats->dead_tuples[tupindex]);
! if (tblk != blkno)
! break; /* past end of tuples for this block */
! toff = ItemPointerGetOffsetNumber(&vacrelstats->dead_tuples[tupindex]);
! itemid = PageGetItemId(page, toff);
! itemid->lp_flags &= ~LP_USED;
}
uncnt = PageRepairFragmentation(page, unused);
--- 626,683 ----
OffsetNumber unused[MaxOffsetNumber];
int uncnt;
Page page = BufferGetPage(buffer);
! OffsetNumber offdead;
! OffsetNumber offnum,
! maxoff;
! OffsetNumber frozen[MaxHeapTuplesPerPage];
! int nfrozen;
!
! Assert(tupindex < vacrelstats->num_dead_tuples);
! Assert(blkno == ItemPointerGetBlockNumber(&vacrelstats->dead_tuples[tupindex]));
!
! offdead = ItemPointerGetOffsetNumber(&vacrelstats->dead_tuples[tupindex]);
! maxoff = PageGetMaxOffsetNumber(page);
! nfrozen = 0;
START_CRIT_SECTION();
! for (offnum = FirstOffsetNumber;
! offnum <= maxoff;
! offnum = OffsetNumberNext(offnum))
{
! ItemId itemid = PageGetItemId(page, offnum);
! if (offnum == offdead)
! {
! itemid->lp_flags &= ~LP_USED;
!
! tupindex++;
! if (tupindex < vacrelstats->num_dead_tuples &&
! blkno == ItemPointerGetBlockNumber(
! &vacrelstats->dead_tuples[tupindex]))
! {
! offdead = ItemPointerGetOffsetNumber(
! &vacrelstats->dead_tuples[tupindex]);
! }
! else
! {
! /* past end of dead tuples for this block */
! offdead = InvalidOffsetNumber;
! }
! }
! else if (ItemIdIsUsed(itemid))
! {
! HeapTupleHeader tuple;
!
! tuple = (HeapTupleHeader) PageGetItem(page, itemid);
!
! /*
! * Do an aggressive freeze. We use OldestXmin as the freeze
! * threshold instead FreezeLimit here.
! */
! if (heap_freeze_tuple(tuple, OldestXmin, InvalidBuffer))
! frozen[nfrozen++] = offnum;
! }
}
uncnt = PageRepairFragmentation(page, unused);
*************** lazy_vacuum_page(Relation onerel, BlockN
*** 613,618 ****
--- 689,696 ----
{
XLogRecPtr recptr;
+ if (nfrozen > 0)
+ log_heap_freeze(onerel, buffer, OldestXmin, frozen, nfrozen);
recptr = log_heap_clean(onerel, buffer, unused, uncnt);
PageSetLSN(page, recptr);
PageSetTLI(page, ThisTimeLineID);
diff -cpr HEAD/src/backend/storage/buffer/bufmgr.c aggressive_freeze/src/backend/storage/buffer/bufmgr.c
*** HEAD/src/backend/storage/buffer/bufmgr.c Mon Feb 5 10:35:59 2007
--- aggressive_freeze/src/backend/storage/buffer/bufmgr.c Mon Mar 5 19:11:09 2007
*************** buffer_write_error_callback(void *arg)
*** 2149,2151 ****
--- 2149,2163 ----
bufHdr->tag.rnode.dbNode,
bufHdr->tag.rnode.relNode);
}
+
+ /*
+ * BufferIsDirty -- retrieve dirty status of the buffer
+ */
+ bool
+ BufferIsDirty(Buffer buffer)
+ {
+ volatile BufferDesc *bufHdr;
+
+ bufHdr = &BufferDescriptors[buffer - 1];
+ return (bufHdr->flags & BM_DIRTY) != 0;
+ }
diff -cpr HEAD/src/include/storage/bufmgr.h aggressive_freeze/src/include/storage/bufmgr.h
*** HEAD/src/include/storage/bufmgr.h Thu Jan 11 14:20:57 2007
--- aggressive_freeze/src/include/storage/bufmgr.h Mon Mar 5 19:11:09 2007
*************** extern Size BufferShmemSize(void);
*** 141,146 ****
--- 141,147 ----
extern RelFileNode BufferGetFileNode(Buffer buffer);
extern void SetBufferCommitInfoNeedsSave(Buffer buffer);
+ extern bool BufferIsDirty(Buffer buffer);
extern void UnlockBuffers(void);
extern void LockBuffer(Buffer buffer, int mode);
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
@ 2007-03-05 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-05 19:26 ` Re: Aggressive freezing in lazy-vacuum Florian G. Pflug <fgp@phlo.org>
2007-03-06 10:03 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2007-03-05 16:02 UTC (permalink / raw)
To: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; +Cc: pgsql-hackers@postgresql.org; pgsql-patches@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-05 19:26 ` Florian G. Pflug <fgp@phlo.org>
2007-03-05 19:30 ` Re: [PATCHES] Aggressive freezing in lazy-vacuum Heikki Linnakangas <heikki@enterprisedb.com>
1 sibling, 1 reply; 11+ messages in thread
From: Florian G. Pflug @ 2007-03-05 19:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; pgsql-hackers@postgresql.org; pgsql-patches@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCHES] Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-05 19:26 ` Re: Aggressive freezing in lazy-vacuum Florian G. Pflug <fgp@phlo.org>
@ 2007-03-05 19:30 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-03-05 19:41 ` Re: [PATCHES] Aggressive freezing in lazy-vacuum Florian G. Pflug <fgp@phlo.org>
0 siblings, 1 reply; 11+ messages in thread
From: Heikki Linnakangas @ 2007-03-05 19:30 UTC (permalink / raw)
To: Florian G. Pflug <fgp@phlo.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; pgsql-hackers@postgresql.org; pgsql-patches@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PATCHES] Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-05 19:26 ` Re: Aggressive freezing in lazy-vacuum Florian G. Pflug <fgp@phlo.org>
2007-03-05 19:30 ` Re: [PATCHES] Aggressive freezing in lazy-vacuum Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-03-05 19:41 ` Florian G. Pflug <fgp@phlo.org>
0 siblings, 0 replies; 11+ messages in thread
From: Florian G. Pflug @ 2007-03-05 19:41 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; pgsql-hackers@postgresql.org; pgsql-patches@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-06 10:03 ` ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-06 11:12 ` Re: Aggressive freezing in lazy-vacuum Gregory Stark <stark@enterprisedb.com>
2007-03-06 15:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 11+ messages in thread
From: ITAGAKI Takahiro @ 2007-03-06 10:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
Tom Lane <tgl@sss.pgh.pa.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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-06 10:03 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
@ 2007-03-06 11:12 ` Gregory Stark <stark@enterprisedb.com>
2007-03-07 01:56 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
1 sibling, 1 reply; 11+ messages in thread
From: Gregory Stark @ 2007-03-06 11:12 UTC (permalink / raw)
To: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
"ITAGAKI Takahiro" <itagaki.takahiro@oss.ntt.co.jp> writes:
> 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.
Well comparing the data to WAL is precisely the kind of debugging that I think
Tom is concerned with.
The hoped for gain here is that vacuum finds fewer pages with tuples that
exceed vacuum_freeze_min_age? That seems useful though vacuum is still going
to have to read every page and I suspect most of the writes pertain to dead
tuples, not freezing tuples.
This strikes me as something that will be more useful once we have the DSM
especially if it ends up including a frozen map. Once we have the DSM vacuum
will no longer be visiting every page, so it will be much easier for pages to
get quite old and only be caught by a vacuum freeze. The less i/o that vacuum
freeze has to do the better. If we get a freeze map then agressive freezing
would help keep pages out of that map so they never need to be vacuumed just
to freeze the tuples in them.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-06 10:03 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-06 11:12 ` Re: Aggressive freezing in lazy-vacuum Gregory Stark <stark@enterprisedb.com>
@ 2007-03-07 01:56 ` ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
0 siblings, 0 replies; 11+ messages in thread
From: ITAGAKI Takahiro @ 2007-03-07 01:56 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; pgsql-hackers@postgresql.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Gregory Stark <stark@enterprisedb.com> wrote:
> The hoped for gain here is that vacuum finds fewer pages with tuples that
> exceed vacuum_freeze_min_age? That seems useful though vacuum is still going
> to have to read every page and I suspect most of the writes pertain to dead
> tuples, not freezing tuples.
Yes. VACUUM makes dirty pages only for freezing exceeded tuples in
particular cases and I think we can reduce the writes by keeping the
number of unfrozen tuples low.
There are three additional costs in FREEZE.
1. CPU cost for changing the xids of target tuples.
2. Writes cost for WAL entries of FREEZE (log_heap_freeze).
3. Writes cost for newly created dirty pages.
I did additional freezing in the following two cases. We'll have created
dirty buffers and WAL entries for required operations then, so that I think
the additional costs of 2 and 3 are ignorable, though 1 still affects us.
| - 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.
> This strikes me as something that will be more useful once we have the DSM
> especially if it ends up including a frozen map. Once we have the DSM vacuum
> will no longer be visiting every page, so it will be much easier for pages to
> get quite old and only be caught by a vacuum freeze. The less i/o that vacuum
> freeze has to do the better. If we get a freeze map then agressive freezing
> would help keep pages out of that map so they never need to be vacuumed just
> to freeze the tuples in them.
Yeah, I was planning to 2 bits/page DSM exactly for the purpose. One of the
bits means to-be-vacuumed and another means to-be-frozen. It helps us avoid
full scanning of the pages for XID wraparound vacuums, but DSM should be more
reliable and not lost any information. I made an attempt to accomplish it
in DSM, but I understand the need to demonstrate it works as designed to you.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-06 10:03 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
@ 2007-03-06 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-07 02:18 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2007-03-06 15:02 UTC (permalink / raw)
To: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; +Cc: pgsql-hackers@postgresql.org
ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
I said nothing about expired tuples. The point of not freezing is to
preserve information about the insertion time of live tuples. And your
test case is unconvincing, because no sane DBA would run with such a
small value of vacuum_freeze_min_age.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-06 10:03 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-06 15:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-03-07 02:18 ` ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-07 04:34 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: ITAGAKI Takahiro @ 2007-03-07 02:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I said nothing about expired tuples. The point of not freezing is to
> preserve information about the insertion time of live tuples.
I don't know what good it will do -- for debugging?
Why don't you use CURRENT_TIMESTAMP?
> And your
> test case is unconvincing, because no sane DBA would run with such a
> small value of vacuum_freeze_min_age.
I intended to use the value for an accelerated test.
The penalties of freeze are divided for the long term in normal use,
but we surely suffer from them by bits.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Aggressive freezing in lazy-vacuum
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-06 10:03 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-06 15:02 ` Re: Aggressive freezing in lazy-vacuum Tom Lane <tgl@sss.pgh.pa.us>
2007-03-07 02:18 ` Re: Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
@ 2007-03-07 04:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2007-03-07 04:34 UTC (permalink / raw)
To: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>; +Cc: pgsql-hackers@postgresql.org
ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I said nothing about expired tuples. The point of not freezing is to
>> preserve information about the insertion time of live tuples.
> I don't know what good it will do -- for debugging?
Exactly. As an example, I've been chasing offline a report from Merlin
Moncure about duplicate entries in a unique index; I still don't know
what exactly is going on there, but the availability of knowledge about
which transactions inserted which entries has been really helpful. If
we had a system designed to freeze tuples as soon as possible, that info
would have been gone forever pretty soon after the problem happened.
I don't say that this behavior can never be acceptable, but you need
much more than a marginal performance improvement to convince me that
it's worth the loss of forensic information.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2007-03-07 04:34 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-05 10:14 Aggressive freezing in lazy-vacuum ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-05 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-05 19:26 ` Florian G. Pflug <fgp@phlo.org>
2007-03-05 19:30 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-03-05 19:41 ` Florian G. Pflug <fgp@phlo.org>
2007-03-06 10:03 ` ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-06 11:12 ` Gregory Stark <stark@enterprisedb.com>
2007-03-07 01:56 ` ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-06 15:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-07 02:18 ` ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
2007-03-07 04:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox