Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6D75D65034D for ; Wed, 3 Dec 2008 14:33:29 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 60372-07 for ; Wed, 3 Dec 2008 14:33:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.179]) by mail.postgresql.org (Postfix) with ESMTP id D4EF165035E for ; Wed, 3 Dec 2008 14:33:24 -0400 (AST) Received: by ik-out-1112.google.com with SMTP id c21so3309653ika.3 for ; Wed, 03 Dec 2008 10:33:23 -0800 (PST) Received: by 10.210.61.19 with SMTP id j19mr15707199eba.41.1228329203068; Wed, 03 Dec 2008 10:33:23 -0800 (PST) Received: from ?80.223.223.193? (dsl-hkibrasgw2-fedfdf00-193.dhcp.inet.fi [80.223.223.193]) by mx.google.com with ESMTPS id i4sm37164361nfh.1.2008.12.03.10.33.20 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 03 Dec 2008 10:33:21 -0800 (PST) Message-ID: <4936D0EF.6000200@enterprisedb.com> Date: Wed, 03 Dec 2008 20:33:19 +0200 Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.17 (X11/20081018) MIME-Version: 1.0 To: Gregory Stark CC: PostgreSQL-development , Tom Lane Subject: Re: Visibility map, partial vacuums 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> In-Reply-To: <87bpvtqh2b.fsf@oxford.xeocode.com> Content-Type: multipart/mixed; boundary="------------030307030503080103060408" From: Heikki Linnakangas X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/167 X-Sequence-Number: 128796 This is a multi-part message in MIME format. --------------030307030503080103060408 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Gregory Stark wrote: > Heikki Linnakangas writes: >> Gregory Stark wrote: >>> Heikki Linnakangas 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.com --------------030307030503080103060408 Content-Type: text/x-diff; name="visibilitymap-autovac-1.patch" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="visibilitymap-autovac-1.patch" diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index 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.c index 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.c index 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.y index 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.c index 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.h index 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 */ --------------030307030503080103060408--