From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Visibility map, partial vacuums
Date: Wed, 03 Dec 2008 20:33:19 +0200
Message-ID: <4936D0EF.6000200@enterprisedb.com> (raw)
In-Reply-To: <87bpvtqh2b.fsf@oxford.xeocode.com>
References: <4905AE17.7090305@enterprisedb.com>
<491D376B.9000608@enterprisedb.com>
<491D7F52.6070908@enterprisedb.com>
<4925664C.3090605@enterprisedb.com>
<26361.1227467112@sss.pgh.pa.us>
<492A6032.6080000@enterprisedb.com>
<18086.1227537479@sss.pgh.pa.us>
<492D4460.1000809@enterprisedb.com>
<5856.1227705135@sss.pgh.pa.us>
<492EF88F.9050709@enterprisedb.com>
<4936884B.6050205@enterprisedb.com>
<87tz9lqs2a.fsf@oxford.xeocode.com>
<4936B88E.5090108@enterprisedb.com>
<87bpvtqh2b.fsf@oxford.xeocode.com>
Gregory Stark wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>> Gregory Stark wrote:
>>> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>>>> Hmm. It just occurred to me that I think this circumvented the anti-wraparound
>>>> vacuuming: a normal vacuum doesn't advance relfrozenxid anymore. We'll need to
>>>> disable the skipping when autovacuum is triggered to prevent wraparound. VACUUM
>>>> FREEZE does that already, but it's unnecessarily aggressive in freezing.
>> FWIW, it seems the omission is actually the other way 'round. Autovacuum always
>> forces a full-scanning vacuum, making the visibility map useless for
>> autovacuum. This obviously needs to be fixed.
>
> How does it do that? Is there some option in the VacStmt to control this? Do
> we just need a syntax to set that option?
The way it works now is that if VacuumStmt->freeze_min_age is not -1
(which means "use the default"), the visibility map is not used and the
whole table is scanned. Autovacuum always sets freeze_min_age, so it's
never using the visibility map. Attached is a patch I'm considering to
fix that.
> How easy is it to tell what percentage of the table needs to be vacuumed? If
> it's > 50% perhaps it would make sense to scan the whole table? (Hm. Not
> really if it's a contiguous 50% though...)
Hmm. You could scan the visibility map to see how much you could skip by
using it. You could account for contiguity.
> Another idea: Perhaps each page of the visibility map should have a frozenxid
> (or multiple frozenxids?). Then if an individual page of the visibility map is
> old we could force scanning all the heap pages covered by that map page and
> update it. I'm not sure we can do that safely though without locking issues --
> or is it ok because it's vacuum doing the updating?
We discussed that a while ago:
http://archives.postgresql.org/message-id/492A6032.6080000@enterprisedb.com
Tom was concerned about making the visibility map not just a hint but
critical data. Rightly so. This is certainly 8.5 stuff; perhaps it would
be more palatable after we get the index-only-scans working using the
visibility map, since the map would be critical data anyway.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.comAttachments:visibilitymap-autovac-1.patch (text/x-diff, 3.4K) downloaddiff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.cindex fd2429a..3e3cb9d 100644--- a/src/backend/commands/vacuumlazy.c+++ b/src/backend/commands/vacuumlazy.c@@ -171,10 +171,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
vacrelstats->hasindex = (nindexes > 0);
/* Should we use the visibility map or scan all pages? */
- if (vacstmt->freeze_min_age != -1)- scan_all = true;- else- scan_all = false;+ scan_all = vacstmt->scan_all;
/* Do the vacuuming */
lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.cindex eb7ab4d..2781f6e 100644--- a/src/backend/nodes/copyfuncs.c+++ b/src/backend/nodes/copyfuncs.c@@ -2771,6 +2771,7 @@ _copyVacuumStmt(VacuumStmt *from)
COPY_SCALAR_FIELD(analyze);
COPY_SCALAR_FIELD(verbose);
COPY_SCALAR_FIELD(freeze_min_age);
+ COPY_SCALAR_FIELD(scan_all));
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(va_cols);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.cindex d4c57bb..86a032f 100644--- a/src/backend/nodes/equalfuncs.c+++ b/src/backend/nodes/equalfuncs.c@@ -1436,6 +1436,7 @@ _equalVacuumStmt(VacuumStmt *a, VacuumStmt *b)
COMPARE_SCALAR_FIELD(analyze);
COMPARE_SCALAR_FIELD(verbose);
COMPARE_SCALAR_FIELD(freeze_min_age);
+ COMPARE_SCALAR_FIELD(scan_all);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(va_cols);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex 85f4616..1aab75c 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -5837,6 +5837,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->analyze = false;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
+ n->scan_all = $3;
n->verbose = $4;
n->relation = NULL;
n->va_cols = NIL;
@@ -5849,6 +5850,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->analyze = false;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
+ n->scan_all = $3;
n->verbose = $4;
n->relation = $5;
n->va_cols = NIL;
@@ -5860,6 +5862,7 @@ VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->vacuum = true;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
+ n->scan_all = $3;
n->verbose |= $4;
$$ = (Node *)n;
}
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.cindex 8d8947f..2c68779 100644--- a/src/backend/postmaster/autovacuum.c+++ b/src/backend/postmaster/autovacuum.c@@ -2649,6 +2649,7 @@ autovacuum_do_vac_analyze(autovac_table *tab,
vacstmt.full = false;
vacstmt.analyze = tab->at_doanalyze;
vacstmt.freeze_min_age = tab->at_freeze_min_age;
+ vacstmt.scan_all = tab->at_wraparound;
vacstmt.verbose = false;
vacstmt.relation = NULL; /* not used since we pass a relid */
vacstmt.va_cols = NIL;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.hindex bb71ac1..df19f7e 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -1966,6 +1966,7 @@ typedef struct VacuumStmt
bool full; /* do FULL (non-concurrent) vacuum */
bool analyze; /* do ANALYZE step */
bool verbose; /* print progress info */
+ bool scan_all; /* force scan of all pages */
int freeze_min_age; /* min freeze age, or -1 to use default */
RangeVar *relation; /* single table to process, or NULL */
List *va_cols; /* list of column names, or NIL for all */
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-hackers@postgresql.org
Cc: heikki.linnakangas@enterprisedb.com, stark@enterprisedb.com, tgl@sss.pgh.pa.us
Subject: Re: Visibility map, partial vacuums
In-Reply-To: <4936D0EF.6000200@enterprisedb.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