From: Markus Schaber <schabi@logix-tt.com>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Default autovacuum settings too conservative
Date: Wed, 08 Feb 2006 12:05:10 +0100
Message-ID: <43E9D066.40101@logix-tt.com> (raw)
In-Reply-To: <m34q3a32w5.fsf@mobile.int.cbbrowne.com>
References: <20060201211633.GM95850@pervasive.com>
<43E12A03.6070906@zeut.net>
<20060207050545.GZ1240@pervasive.com>
<m38xsn4rvm.fsf@mobile.int.cbbrowne.com>
<20060207072621.GI13592@pervasive.com>
<43E89506.40903@logix-tt.com>
<20060208010339.GT38134@pervasive.com>
<43E94E42.3080605@pws.com.au>
<m34q3a32w5.fsf@mobile.int.cbbrowne.com>
Hi, Christopher,
Christopher Browne wrote:
> Right. And part of the trouble is that you lose certainty that you
> have covered off transaction wraparound.
Yes. Vacuum (full) serve at least four purposes:
- TID wraparound prevention
- obsolete row removal
- table compaction
- giving space back to the OS by truncating files
While the first one needs full table sweeps, the others don't. And from
my personal experience, at least the obsolete row removal is needed much
more frequently than TID wraparound prevention.
>>When tables are tracked individually for wraparound, the longest
>>transaction required for vacuuming will be one to vacuum one
>>table. With delete-map and other functions, the time for that
>>transaction may be reduced. Partial vacuum of large tables is an
>>option, but again requires some real smarts in the autovac code to
>>track wraparound issues.
>
> Unfortunately, "delete-map" *doesn't* help you with the wraparound
> problem. The point of the "delete map" or "vacuum space map" is to
> allow the VACUUM to only touch the pages known to need vacuuming.
>
> At some point, you still need to walk through the whole table (touched
> parts and untouched) in order to make sure that the old tuples are
> frozen.
Preventing transaction ID wraparound needs a guaranteed full table sweep
during a vacuum run, but not necessarily in a single transaction. It
should be possible to divide this full table sweep into smaller chunks,
each of them in its own transaction.
It will certainly be necessary to block e. G. simultaneous VACUUMs,
CLUSTERs or other maintainance commands for the whole VACUUM run, but
normal SELECT, INSERT and UPDATE statement should be able to interleave
with the VACUUM transaction.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: schabi@logix-tt.com, cbbrowne@acm.org
Subject: Re: Default autovacuum settings too conservative
In-Reply-To: <43E9D066.40101@logix-tt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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