Visibility map and freezing
Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
The way VACUUM works with the visibility map is that if any pages are skipped, relfrozenxid can't be updated. That means that plain VACUUM won't advance relfrozenxid, and doesn't protect from XID wraparound. We discussed this in the context of autovacuum before, and we have that covered now. Autovacuum will launch a full-scanning vacuum that advances relfrozenxid, when autovacuum_freeze_max_age is reached, and partial vacuums otherwise. Autovacuum will launch anti-wraparound vacuum even if it's otherwise disabled. Which is good, but it'll be an unpleasant surprise for someone who performs a simple manual database-wide "VACUUM", for example, every night from a cron job. You could run VACUUM FREEZE, say monthly, to force a full-scanning vacuum, but that's unnecessarily aggressive, and you need to know about the issue to set that up in the first place. I think we need a threshold similar to autovacuum_freeze_max_age for manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and relfrozenxid is older than vacuum_freeze_max_age, the visibility map is ignored and all pages are scanned. This ensures that you don't run into forced anti-wraparound autovacuums if you do your VACUUMs manually. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
Zdenek Kotala <Zdenek.Kotala@Sun.COM>
Дата:
What's about add second bit which mark frozen page (all tuples have freeze XID)? It should avoid full scan, but extend size of map. Zdenek Heikki Linnakangas napsal(a): > The way VACUUM works with the visibility map is that if any pages are > skipped, relfrozenxid can't be updated. That means that plain VACUUM > won't advance relfrozenxid, and doesn't protect from XID wraparound. > > We discussed this in the context of autovacuum before, and we have that > covered now. Autovacuum will launch a full-scanning vacuum that advances > relfrozenxid, when autovacuum_freeze_max_age is reached, and partial > vacuums otherwise. > > Autovacuum will launch anti-wraparound vacuum even if it's otherwise > disabled. Which is good, but it'll be an unpleasant surprise for someone > who performs a simple manual database-wide "VACUUM", for example, every > night from a cron job. You could run VACUUM FREEZE, say monthly, to > force a full-scanning vacuum, but that's unnecessarily aggressive, and > you need to know about the issue to set that up in the first place. > > I think we need a threshold similar to autovacuum_freeze_max_age for > manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and > relfrozenxid is older than vacuum_freeze_max_age, the visibility map is > ignored and all pages are scanned. > > This ensures that you don't run into forced anti-wraparound autovacuums > if you do your VACUUMs manually. >
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Zdenek Kotala wrote: > What's about add second bit which mark frozen page (all tuples have > freeze XID)? It should avoid full scan, but extend size of map. First of all, we'd still have to make the decision of when to scan pages that need freezing, and when to only scan pages that have dead tuples. Secondly, if it's just one bit, we'd have to freeze all tuples on the page to set the bit, which is a lot more aggressive than we do now. Thirdly, those frozen bits would be critical, not just hints like the visibility map is right now. Corrupt frozen bits would lead to data loss. That means we'd have to solve the problem of how to make sure the bits are always cleared when tuples are updated on a page. That's not completely crash-proof at the moment, see comments in visibilitymap.c. Even if we did solve that, I'd rather live with a hints-only visibility map for one release, before we take the plunge and make it a critical part. Just in case... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
Gregory Stark <greg.stark@enterprisedb.com>
Дата:
Zdenek Kotala writes: > What's about add second bit which mark frozen page (all tuples have freeze > XID)? It should avoid full scan, but extend size of map. That would only really work if you have a very static table where entire pages get frozen and stay frozen long before the freeze_max_age is reached. Even that wouldn't really work because the partial vacuums would never see those pages. One option is to keep a frozenxid per page which would allow us to visit only pages that need freezing. A more complex scheme would be to have a bit which indicates that all non-frozen xids are > relfrozenxid+100M. When we find all the bits set we can clear them all and bump relfrozenxid by 100M. This would allow regular partial vacuums to gradually move the frozenxid forward. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
Re: Visibility map and freezing
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Heikki Linnakangas wrote: > I think we need a threshold similar to autovacuum_freeze_max_age for > manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and > relfrozenxid is older than vacuum_freeze_max_age, the visibility map is > ignored and all pages are scanned. Would one parameter to control both suffice? (i.e., rename autovacuum_freeze_max_age to vacuum_freeze_max_age)
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Peter Eisentraut wrote: > Heikki Linnakangas wrote: >> I think we need a threshold similar to autovacuum_freeze_max_age for >> manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and >> relfrozenxid is older than vacuum_freeze_max_age, the visibility map >> is ignored and all pages are scanned. > > Would one parameter to control both suffice? (i.e., rename > autovacuum_freeze_max_age to vacuum_freeze_max_age) Imagine that you run a nightly VACUUM from cron, and have autovacuum disabled. If autovacuum_freeze_max_age is the same as vacuum_freeze_max_age, as soon as that age is reached, an anti-wraparound autovacuum launched. What we'd want to happen is for the next nightly VACUUM to do the work instead. So they need to be separate settings, with some space between them by default. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Heikki Linnakangas wrote:
> Peter Eisentraut wrote:
>> Heikki Linnakangas wrote:
>>> I think we need a threshold similar to autovacuum_freeze_max_age for
>>> manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and
>>> relfrozenxid is older than vacuum_freeze_max_age, the visibility map
>>> is ignored and all pages are scanned.
>>
>> Would one parameter to control both suffice? (i.e., rename
>> autovacuum_freeze_max_age to vacuum_freeze_max_age)
>
> Imagine that you run a nightly VACUUM from cron, and have autovacuum
> disabled. If autovacuum_freeze_max_age is the same as
> vacuum_freeze_max_age, as soon as that age is reached, an
> anti-wraparound autovacuum launched. What we'd want to happen is for the
> next nightly VACUUM to do the work instead. So they need to be separate
> settings, with some space between them by default.
Attached is a proposed patch to handle freezing. In a nutshell:
Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the
whole table and advance relfrozenxid, if relfrozenxid is older than
vacuum_freeze_max_age.
If vacuum_freeze_max_age is greater than autovacuum_freeze_max_age - 1
million transactions, it's effectively capped at that value. It doesn't
make sense to have vacuum_freeze_max_age > autovacuum_freeze_max_age,
because the manual VACUUM wouldn't have a chance to do the full sweep
before the anti-wraparound autovacuum is launched. The "minus one
million transactions" is to give some headroom.
I'm lowering vacuum_freeze_min_age from 100 million transactions to 50
million, so that the whole-table vacuum doesn't need to run as often.
Note that since VACUUM normally only scans pages that need vacuuming
according to the visibility map, tuples on skipped pages are not frozen
any earlier even though vacuum_freeze_min_age is lower.
To recap, here's the new defaults:
autovacuum_freeze_max_age 200000000
vacuum_freeze_max_age 150000000
vacuum_freeze_min_age 50000000
This means that with defaults, autovacuum will launch a whole-table
vacuum every 150 million transactions (autovacuum_freeze_max_age -
vacuum_freeze_min_age). If you run a nightly manual VACUUM, you'll get a
whole-table vacuum every 100 million transactions.
vacuum_freeze_max_age also affects autovacuums. If an autovacuum is
launched on table to remove dead tuples, and vacuum_freeze_max_age has
been reached (but not yet autovacuum_freeze_max_age), the autovacuum
will scan the whole table. I'm not sure if this is desirable, to avoid
having to launch separate anti-wraparound autovacuums even when there's
not many dead tuples, or just confusing.
If you set vacuum_freeze_max_age to 0, the visibility map is not used to
skip pages, so you'll get the pre-8.4 old behavior.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7493ca9..9848ce0 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3925,6 +3925,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
+
+ vacuum_freeze_max_age (integer)
+
+ vacuum_freeze_max_age</> configuration parameter
+
+
+
+ VACUUM</> performs a whole-table scan if the table's
+ pg_class</>.relfrozenxid</> field reaches the
+ age specified by this setting. The default is 150 million
+ transactions. Although users can set this value anywhere from zero to
+ one billion, VACUUM</> will silently limit the effective value
+ to the value of minus
+ 1 million transactions, so that regular manual VACUUM</> has a
+ chance to run before autovacuum is launched to prevent XID wraparound.
+ For more information see .
+
+
+
+
vacuum_freeze_min_age (integer)
@@ -3935,7 +3955,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
Specifies the cutoff age (in transactions) that VACUUM</>
should use to decide whether to replace transaction IDs with
FrozenXID</> while scanning a table.
- The default is 100 million transactions. Although
+ The default is 50 million transactions. Although
users can set this value anywhere from zero to one billion,
VACUUM</> will silently limit the effective value to half
the value of , so
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 03ce2e9..c41d464 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -367,10 +367,14 @@
- VACUUM</>'s behavior is controlled by the configuration parameter
- : any XID older than
- vacuum_freeze_min_age</> transactions is replaced by
- FrozenXID</>. Larger values of vacuum_freeze_min_age</>
+ VACUUM</>'s behavior is controlled by the two configuration
+ parameters: and
+ .
+ vacuum_freeze_max_age</> controls when VACUUM</>
+ performs a full sweep of the table to replace old XID values with
+ FrozenXID</>. vacuum_freeze_min_age</>
+ controls how old an XID value has to be before it's replaced with
+ FrozenXID</>. Larger values of these settings
preserve transactional information longer, while smaller values increase
the number of transactions that can elapse before the table must be
vacuumed again.
@@ -379,7 +383,8 @@
The maximum time that a table can go unvacuumed is two billion
transactions minus the vacuum_freeze_min_age</> that was used
- when it was last vacuumed. If it were to go unvacuumed for longer than
+ when VACUUM</> last scanned the whole table. If it were to go
+ unvacuumed for longer than
that, data loss could result. To ensure that this does not happen,
autovacuum is invoked on any table that might contain XIDs older than the
age specified by the configuration parameter
- The sole disadvantage of increasing autovacuum_freeze_max_age</>
+ The sole disadvantage of increasing vacuum_freeze_max_age</>
+ and autovacuum_freeze_max_age</>
is that the pg_clog</> subdirectory of the database cluster
will take more space, because it must store the commit status for all
transactions back to the autovacuum_freeze_max_age</> horizon.
@@ -459,9 +465,20 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
VACUUM</>, age(relfrozenxid)</> should be a little
more than the vacuum_freeze_min_age</> setting that was used
(more by the number of transactions started since the VACUUM</>
- started). If age(relfrozenxid)</> exceeds
- autovacuum_freeze_max_age</>, an autovacuum will soon be forced
- for the table.
+ started).
+
+
+
+ VACUUM</> normally only scans pages that have been modified
+ since last vacuum, but relfrozenxid</> can only be advanced
+ when the whole table is scanned. The whole table is scanned when
+ relfrozenxid</> is more than
+ vacuum_freeze_max_age</> transactions old, if
+ VACUUM FREEZE</> command is used, or if all pages happen to
+ require vacuuming to remove dead row versions.
+ If no whole-table-scanning VACUUM</> is issued on the table until
+ autovacuum_freeze_max_age</> is reached, an autovacuum will soon
+ be forced for the table.
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index c8e4226..39e0cca 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -62,6 +62,7 @@
* GUC parameters
*/
int vacuum_freeze_min_age;
+int vacuum_freeze_max_age;
/*
* VacPage structures keep track of each page on which we find useful
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index acb02a4..a82fea5 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -144,6 +144,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
BlockNumber possibly_freeable;
PGRUsage ru0;
TimestampTz starttime = 0;
+ bool scan_all;
pg_rusage_init(&ru0);
@@ -156,6 +157,42 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
else
elevel = DEBUG2;
+ /*
+ * Determine if we should scan the whole relation, either because we were
+ * told to, or because relfrozenxid has reached vacuum_freeze_max_age.
+ */
+ if (vacstmt->scan_all)
+ scan_all = true;
+ else
+ {
+ TransactionId limit;
+ TransactionId effective_max_age;
+
+ /*
+ * Limit vacuum_freeze_max_age to autovacuum_freeze_max_age - 1
+ * million transactions, so that regular VACUUM gets a chance to
+ * freeze tuples before anti-wraparound autovacuum is launched.
+ */
+ if (vacuum_freeze_max_age > autovacuum_freeze_max_age - 1000000)
+ effective_max_age = autovacuum_freeze_max_age - 1000000;
+ else
+ effective_max_age = vacuum_freeze_max_age;
+
+ /*
+ * Compute the cutoff XID, being careful not to generate a
+ * "permanent" XID
+ */
+ limit = ReadNewTransactionId() - effective_max_age;
+ if (!TransactionIdIsNormal(limit))
+ limit = FirstNormalTransactionId;
+
+ if (TransactionIdPrecedes(onerel->rd_rel->relfrozenxid, limit))
+ scan_all = true;
+ else
+ scan_all = false;
+ }
+
+
vac_strategy = bstrategy;
vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared,
@@ -171,7 +208,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
vacrelstats->hasindex = (nindexes > 0);
/* Do the vacuuming */
- lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, vacstmt->scan_all);
+ lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all);
/* Done with indexes */
vac_close_indexes(nindexes, Irel, NoLock);
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index b912df0..cf27055 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1533,7 +1533,16 @@ static struct config_int ConfigureNamesInt[] =
NULL
},
&vacuum_freeze_min_age,
- 100000000, 0, 1000000000, NULL, NULL
+ 50000000, 0, 1000000000, NULL, NULL
+ },
+
+ {
+ {"vacuum_freeze_max_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Age at which VACUUM should scan whole table to freeze tuples."),
+ NULL
+ },
+ &vacuum_freeze_max_age,
+ 150000000, 0, 2000000000, NULL, NULL
},
{
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index bb51632..e81e7c8 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -122,6 +122,7 @@ typedef struct VacAttrStats
extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for
* PostGIS */
extern int vacuum_freeze_min_age;
+extern int vacuum_freeze_max_age;
/* in commands/vacuum.c */
Re: Visibility map and freezing
От:
"Fujii Masao" <masao.fujii@gmail.com>
Дата:
On Tue, Dec 23, 2008 at 4:24 AM, Heikki Linnakangas wrote: > Heikki Linnakangas wrote: >> >> Peter Eisentraut wrote: >>> >>> Heikki Linnakangas wrote: >>>> >>>> I think we need a threshold similar to autovacuum_freeze_max_age for >>>> manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and >>>> relfrozenxid is older than vacuum_freeze_max_age, the visibility map is >>>> ignored and all pages are scanned. >>> >>> Would one parameter to control both suffice? (i.e., rename >>> autovacuum_freeze_max_age to vacuum_freeze_max_age) >> >> Imagine that you run a nightly VACUUM from cron, and have autovacuum >> disabled. If autovacuum_freeze_max_age is the same as vacuum_freeze_max_age, >> as soon as that age is reached, an anti-wraparound autovacuum launched. What >> we'd want to happen is for the next nightly VACUUM to do the work instead. >> So they need to be separate settings, with some space between them by >> default. > > Attached is a proposed patch to handle freezing. In a nutshell: > > Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the > whole table and advance relfrozenxid, if relfrozenxid is older than > vacuum_freeze_max_age. > > If vacuum_freeze_max_age is greater than autovacuum_freeze_max_age - 1 > million transactions, it's effectively capped at that value. It doesn't make > sense to have vacuum_freeze_max_age > autovacuum_freeze_max_age, because the > manual VACUUM wouldn't have a chance to do the full sweep before the > anti-wraparound autovacuum is launched. The "minus one million transactions" > is to give some headroom. > > I'm lowering vacuum_freeze_min_age from 100 million transactions to 50 > million, so that the whole-table vacuum doesn't need to run as often. Note > that since VACUUM normally only scans pages that need vacuuming according to > the visibility map, tuples on skipped pages are not frozen any earlier even > though vacuum_freeze_min_age is lower. > > To recap, here's the new defaults: > autovacuum_freeze_max_age 200000000 > vacuum_freeze_max_age 150000000 > vacuum_freeze_min_age 50000000 > > This means that with defaults, autovacuum will launch a whole-table vacuum > every 150 million transactions (autovacuum_freeze_max_age - > vacuum_freeze_min_age). If you run a nightly manual VACUUM, you'll get a > whole-table vacuum every 100 million transactions. > > vacuum_freeze_max_age also affects autovacuums. If an autovacuum is launched > on table to remove dead tuples, and vacuum_freeze_max_age has been reached > (but not yet autovacuum_freeze_max_age), the autovacuum will scan the whole > table. I'm not sure if this is desirable, to avoid having to launch separate > anti-wraparound autovacuums even when there's not many dead tuples, or just > confusing. > > If you set vacuum_freeze_max_age to 0, the visibility map is not used to > skip pages, so you'll get the pre-8.4 old behavior. It seems to be strange that "max" can be less than "min". Is it worth dividing a parameter into two(min/max)? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Re: Visibility map and freezing
От:
Jeff Davis <pgsql@j-davis.com>
Дата:
On Mon, 2008-12-22 at 21:24 +0200, Heikki Linnakangas wrote: > Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the > whole table and advance relfrozenxid, if relfrozenxid is older than > vacuum_freeze_max_age. > It's confusing to have two GUCs named vacuum_freeze_min_age and vacuum_freeze_max_age with incompatible definitions. The former is the minimum transaction age of a tuple found during the scan of a table, while the latter is the maximum transaction age of the relfrozenxid of the table. > If you set vacuum_freeze_max_age to 0, the visibility map is not used to > skip pages, so you'll get the pre-8.4 old behavior. Seems like a strange way to turn off visibility maps, and the meaning doesn't seem to fit with either vacuum_freeze_min_age or autovacuum_freeze_max_age. The proposal itself makes sense, but I think we need to decide on some better names. Right now the meanings of autovacuum_freeze_max_age and vacuum_freeze_min_age are incompatible, so we're not in a good position, but there has to be something less confusing. For one thing, there isn't even a common definition of "min" or "max" between them. They both trigger an event (freezing or vacuuming) when something (tuple xmin or relfrozenxid) exceeds some number. Why is one called a min and the other a max? Regards,Jeff Davis
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Jeff Davis wrote: > On Mon, 2008-12-22 at 21:24 +0200, Heikki Linnakangas wrote: >> Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the >> whole table and advance relfrozenxid, if relfrozenxid is older than >> vacuum_freeze_max_age. >> > > It's confusing to have two GUCs named vacuum_freeze_min_age and > vacuum_freeze_max_age with incompatible definitions. The former is the > minimum transaction age of a tuple found during the scan of a table, > while the latter is the maximum transaction age of the relfrozenxid of > the table. > >> If you set vacuum_freeze_max_age to 0, the visibility map is not used to >> skip pages, so you'll get the pre-8.4 old behavior. > > Seems like a strange way to turn off visibility maps, and the meaning > doesn't seem to fit with either vacuum_freeze_min_age or > autovacuum_freeze_max_age. > > The proposal itself makes sense, but I think we need to decide on some > better names. Right now the meanings of autovacuum_freeze_max_age and > vacuum_freeze_min_age are incompatible, so we're not in a good position, > but there has to be something less confusing. > > For one thing, there isn't even a common definition of "min" or "max" > between them. They both trigger an event (freezing or vacuuming) when > something (tuple xmin or relfrozenxid) exceeds some number. Why is one > called a min and the other a max? Yeah, you're right. Fuji's point that it's confusing that you can have a min greater than max is also valid. How about autovacuum_freeze_max_age -> autovacuum_freeze_scan_age vacuum_freeze_max_age -> vacuum_freeze_scan_age vacuum_freeze_min_age -> vacuum_freeze_tuple_age *_scan_age settings control when the table is fully scanned to freeze tuples and advance relfrozenxid, and vacuum_freeze_tuple_age controls how old a tuple needs to be to be frozen. One objection is that you can read "freeze_scan" to mean that a scan is frozen, like a tuple is frozen. Any better ideas? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
Jeff Davis <pgsql@j-davis.com>
Дата:
On Wed, 2009-01-07 at 09:34 +0200, Heikki Linnakangas wrote: > autovacuum_freeze_max_age -> autovacuum_freeze_scan_age > vacuum_freeze_max_age -> vacuum_freeze_scan_age > vacuum_freeze_min_age -> vacuum_freeze_tuple_age > > *_scan_age settings control when the table is fully scanned to freeze > tuples and advance relfrozenxid, and vacuum_freeze_tuple_age controls > how old a tuple needs to be to be frozen. One objection is that you can > read "freeze_scan" to mean that a scan is frozen, like a tuple is > frozen. Any better ideas? I see what you mean about the possible misinterpretation, but I think it's a big improvement, and I don't have a better suggestion. Thanks,Jeff Davis
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Jeff Davis wrote: > On Wed, 2009-01-07 at 09:34 +0200, Heikki Linnakangas wrote: >> autovacuum_freeze_max_age -> autovacuum_freeze_scan_age >> vacuum_freeze_max_age -> vacuum_freeze_scan_age >> vacuum_freeze_min_age -> vacuum_freeze_tuple_age >> >> *_scan_age settings control when the table is fully scanned to freeze >> tuples and advance relfrozenxid, and vacuum_freeze_tuple_age controls >> how old a tuple needs to be to be frozen. One objection is that you can >> read "freeze_scan" to mean that a scan is frozen, like a tuple is >> frozen. Any better ideas? > > I see what you mean about the possible misinterpretation, but I think > it's a big improvement, and I don't have a better suggestion. Thinking about this some more, I'm not too happy with those names either. vacuum_freeze_scan_age and autovacuum_freeze_scan_age don't mean quite the same thing, like vacuum_cost_delay and autovacuum_vacuum_cost_delay do, for example. I'm now leaning towards: autovacuum_freeze_max_age vacuum_freeze_table_age vacuum_freeze_min_age where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged, and vacuum_freeze_table_age is the new setting that controls when VACUUM or autovacuum should perform a full scan of the table to advance relfrozenxid. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
Jeff Davis <pgsql@j-davis.com>
Дата:
On Fri, 2009-01-09 at 13:49 +0200, Heikki Linnakangas wrote: > Thinking about this some more, I'm not too happy with those names > either. vacuum_freeze_scan_age and autovacuum_freeze_scan_age don't mean > quite the same thing, like vacuum_cost_delay and > autovacuum_vacuum_cost_delay do, for example. If the distinction you're making is that autovacuum_freeze_max_age affects the launching of a vacuum rather than the behavior of a vacuum, maybe we could incorporate the word "launch" like: autovacuum_launch_freeze_threshold > I'm now leaning towards: > > autovacuum_freeze_max_age > vacuum_freeze_table_age > vacuum_freeze_min_age > > where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged, > and vacuum_freeze_table_age is the new setting that controls when VACUUM > or autovacuum should perform a full scan of the table to advance > relfrozenxid. I'm still bothered by the fact that "max" and "min" really mean the same thing here. I don't think we can perfectly capture the meaning of these GUCs in the name. I think our goal should be to avoid confusion between them. Regards,Jeff Davis
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Jeff Davis wrote:
> On Fri, 2009-01-09 at 13:49 +0200, Heikki Linnakangas wrote:
> If the distinction you're making is that autovacuum_freeze_max_age
> affects the launching of a vacuum rather than the behavior of a vacuum,
> maybe we could incorporate the word "launch" like:
>
> autovacuum_launch_freeze_threshold
Hmm, I think I'd like it in the form autovacuum_freeze_launch_threshold
more.
>> I'm now leaning towards:
>>
>> autovacuum_freeze_max_age
>> vacuum_freeze_table_age
>> vacuum_freeze_min_age
>>
>> where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged,
>> and vacuum_freeze_table_age is the new setting that controls when VACUUM
>> or autovacuum should perform a full scan of the table to advance
>> relfrozenxid.
>
> I'm still bothered by the fact that "max" and "min" really mean the same
> thing here.
Yeah. Those are existing names, though, and I don't recall any
complaints from users.
> I don't think we can perfectly capture the meaning of these GUCs in the
> name. I think our goal should be to avoid confusion between them.
Agreed.
Well, for better or worse here's a patch leaving the existing setting
names alone, and the new GUC is called "vacuum_freeze_table_age". I'm
not opposed to changing the names of the existing GUCs. If we do change
them, the columns in pg_autovacuum need to be changed too.
There's documentation changes included to descríbe the new GUC, and the
new behavior of VACUUM with visibility map in general. Is it readable,
and is it enough?
Alvaro, are you getting rid of pg_autovacuum in favor of the reloptions
for 8.4? This patch adds a new column to pg_autovacuum, reflecting the
new vacuum_freeze_table_age GUC just like freeze_min_age column reflects
vacuum_freeze_min_age. If I commit this tomorrow, will I cause you a lot
of trouble with the reloptions patch?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
*** doc/src/sgml/catalogs.sgml
--- doc/src/sgml/catalogs.sgml
***************
*** 1361,1366 ****
--- 1361,1373 ----
Custom autovacuum_freeze_max_age</> parameter
+
+
+ freeze_table_age
+ integer
+
+ Custom vacuum_freeze_table_age</> parameter
+
*** doc/src/sgml/config.sgml
--- doc/src/sgml/config.sgml
***************
*** 3950,3955 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 3950,3975 ----
+
+ vacuum_freeze_max_age (integer)
+
+ vacuum_freeze_max_age</> configuration parameter
+
+
+
+ VACUUM</> performs a whole-table scan if the table's
+ pg_class</>.relfrozenxid</> field reaches the
+ age specified by this setting. The default is 150 million
+ transactions. Although users can set this value anywhere from zero to
+ one billion, VACUUM</> will silently limit the effective value
+ to the value of minus
+ 1 million transactions, so that regular manual VACUUM</> has a
+ chance to run before autovacuum is launched to prevent XID wraparound.
+ For more information see .
+
+
+
+
vacuum_freeze_min_age (integer)
***************
*** 3960,3966 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
Specifies the cutoff age (in transactions) that VACUUM</>
should use to decide whether to replace transaction IDs with
FrozenXID</> while scanning a table.
! The default is 100 million transactions. Although
users can set this value anywhere from zero to one billion,
VACUUM</> will silently limit the effective value to half
the value of , so
--- 3980,3986 ----
Specifies the cutoff age (in transactions) that VACUUM</>
should use to decide whether to replace transaction IDs with
FrozenXID</> while scanning a table.
! The default is 50 million transactions. Although
users can set this value anywhere from zero to one billion,
VACUUM</> will silently limit the effective value to half
the value of , so
***************
*** 3971,3976 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 3991,4017 ----
+
+ vacuum_freeze_table_age (integer)
+
+ vacuum_freeze_table_age</> configuration parameter
+
+
+
+ Specifies the cutoff age (in transactions) that VACUUM</>
+ should use to decide whether to replace scan the whole table,
+ to freeze old tuples and advance pg_class</>.relfrozenxid</>.
+ The default is 150 million transactions. Although
+ users can set this value anywhere from zero to two billion,
+ VACUUM</> will silently limit the effective value to 95%
+ of the value of , so
+ that a regularly run manual VACUUM</> has a chance to do
+ a whole table scan before an anti-wraparound autovacuum is launched.
+ For more information see .
+
+
+
+
xmlbinary (enum)
*** doc/src/sgml/maintenance.sgml
--- doc/src/sgml/maintenance.sgml
***************
*** 367,376 ****
! VACUUM</>'s behavior is controlled by the configuration parameter
! : any XID older than
! vacuum_freeze_min_age</> transactions is replaced by
! FrozenXID</>. Larger values of vacuum_freeze_min_age</>
preserve transactional information longer, while smaller values increase
the number of transactions that can elapse before the table must be
vacuumed again.
--- 367,380 ----
! VACUUM</>'s behavior is controlled by the two configuration
! parameters: and
! .
! vacuum_freeze_table_age</> controls when VACUUM</>
! performs a full sweep of the table, in order to replace old XID values
! with FrozenXID</>. vacuum_freeze_min_age</>
! controls how old an XID value has to be before it's replaced with
! FrozenXID</>. Larger values of these settings
preserve transactional information longer, while smaller values increase
the number of transactions that can elapse before the table must be
vacuumed again.
***************
*** 379,385 ****
The maximum time that a table can go unvacuumed is two billion
transactions minus the vacuum_freeze_min_age</> that was used
! when it was last vacuumed. If it were to go unvacuumed for longer than
that, data loss could result. To ensure that this does not happen,
autovacuum is invoked on any table that might contain XIDs older than the
age specified by the configuration parameter
The maximum time that a table can go unvacuumed is two billion
transactions minus the vacuum_freeze_min_age</> that was used
! when VACUUM</> last scanned the whole table. If it were to go
! unvacuumed for longer than
that, data loss could result. To ensure that this does not happen,
autovacuum is invoked on any table that might contain XIDs older than the
age specified by the configuration parameter
! The sole disadvantage of increasing autovacuum_freeze_max_age</>
is that the pg_clog</> subdirectory of the database cluster
will take more space, because it must store the commit status for all
transactions back to the autovacuum_freeze_max_age</> horizon.
--- 408,415 ----
! The sole disadvantage of increasing vacuum_freeze_table_age</>
! and autovacuum_freeze_max_age</>
is that the pg_clog</> subdirectory of the database cluster
will take more space, because it must store the commit status for all
transactions back to the autovacuum_freeze_max_age</> horizon.
***************
*** 411,418 ****
autovacuum_freeze_max_age</> has its maximum allowed value of
a little less than two billion, pg_clog</> can be expected to
grow to about half a gigabyte. If this is trivial compared to your
! total database size, setting autovacuum_freeze_max_age</> to
! its maximum allowed value is recommended. Otherwise, set it depending
on what you are willing to allow for pg_clog</> storage.
(The default, 200 million transactions, translates to about 50MB of
pg_clog</> storage.)
--- 417,425 ----
autovacuum_freeze_max_age</> has its maximum allowed value of
a little less than two billion, pg_clog</> can be expected to
grow to about half a gigabyte. If this is trivial compared to your
! total database size, setting autovacuum_freeze_max_age</> and
! vacuum_freeze_table_age to their maximum allowed values
! is recommended. Otherwise, set them depending
on what you are willing to allow for pg_clog</> storage.
(The default, 200 million transactions, translates to about 50MB of
pg_clog</> storage.)
***************
*** 455,467 **** SELECT datname, age(datfrozenxid) FROM pg_database;
The age</> column measures the number of transactions from the
! cutoff XID to the current transaction's XID. Immediately after a
! VACUUM</>, age(relfrozenxid)</> should be a little
! more than the vacuum_freeze_min_age</> setting that was used
! (more by the number of transactions started since the VACUUM</>
! started). If age(relfrozenxid)</> exceeds
! autovacuum_freeze_max_age</>, an autovacuum will soon be forced
! for the table.
--- 462,485 ----
The age</> column measures the number of transactions from the
! cutoff XID to the current transaction's XID. When VACUUM</>
! scans the whole table, after it's finished age(relfrozenxid)</>
! should be a little more than the vacuum_freeze_min_age</> setting
! that was used (more by the number of transactions started since the
! VACUUM</> started).
!
!
!
! VACUUM</> normally only scans pages that have been modified
! since last vacuum, but relfrozenxid</> can only be advanced
! when the whole table is scanned. The whole table is scanned when
! relfrozenxid</> is more than
! vacuum_freeze_table_age</> transactions old, if
! VACUUM FREEZE</> command is used, or if all pages happen to
! require vacuuming to remove dead row versions. If no whole-table-scanning
! VACUUM</> is issued on the table until
! autovacuum_freeze_max_age</> is reached, an autovacuum will soon
! be forced for the table.
*** src/backend/commands/cluster.c
--- src/backend/commands/cluster.c
***************
*** 789,796 **** copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex)
* freeze_min_age to avoid having CLUSTER freeze tuples earlier than a
* plain VACUUM would.
*/
! vacuum_set_xid_limits(-1, OldHeap->rd_rel->relisshared,
! &OldestXmin, &FreezeXid);
/*
* FreezeXid will become the table's new relfrozenxid, and that mustn't
--- 789,796 ----
* freeze_min_age to avoid having CLUSTER freeze tuples earlier than a
* plain VACUUM would.
*/
! vacuum_set_xid_limits(-1, -1, OldHeap->rd_rel->relisshared,
! &OldestXmin, &FreezeXid, NULL);
/*
* FreezeXid will become the table's new relfrozenxid, and that mustn't
*** src/backend/commands/vacuum.c
--- src/backend/commands/vacuum.c
***************
*** 62,67 ****
--- 62,68 ----
* GUC parameters
*/
int vacuum_freeze_min_age;
+ int vacuum_freeze_table_age;
/*
* VacPage structures keep track of each page on which we find useful
***************
*** 590,598 **** get_rel_oids(Oid relid, const RangeVar *vacrel, const char *stmttype)
* vacuum_set_xid_limits() -- compute oldest-Xmin and freeze cutoff points
*/
void
! vacuum_set_xid_limits(int freeze_min_age, bool sharedRel,
TransactionId *oldestXmin,
! TransactionId *freezeLimit)
{
int freezemin;
TransactionId limit;
--- 591,602 ----
* vacuum_set_xid_limits() -- compute oldest-Xmin and freeze cutoff points
*/
void
! vacuum_set_xid_limits(int freeze_min_age,
! int freeze_table_age,
! bool sharedRel,
TransactionId *oldestXmin,
! TransactionId *freezeLimit,
! TransactionId *freezeTableLimit)
{
int freezemin;
TransactionId limit;
***************
*** 648,653 **** vacuum_set_xid_limits(int freeze_min_age, bool sharedRel,
--- 652,685 ----
}
*freezeLimit = limit;
+
+ if (freezeTableLimit != NULL)
+ {
+ int freezetable;
+
+ /*
+ * Determine the table freeze age to use: as specified by the caller,
+ * or vacuum_freeze_table_age, but in any case not more than
+ * autovacuum_freeze_max_age * 0.95, so that if you have e.g nightly
+ * VACUUM schedule, the nightly VACUUM gets a chance to freeze tuples
+ * before anti-wraparound autovacuum is launched.
+ */
+ freezetable = freeze_min_age;
+ if (freezetable < 0)
+ freezetable = vacuum_freeze_table_age;
+ freezetable = Min(freezetable, autovacuum_freeze_max_age * 0.95);
+ Assert(freezetable >= 0);
+
+ /*
+ * Compute the cutoff XID, being careful not to generate a
+ * "permanent" XID.
+ */
+ limit = ReadNewTransactionId() - freezetable;
+ if (!TransactionIdIsNormal(limit))
+ limit = FirstNormalTransactionId;
+
+ *freezeTableLimit = limit;
+ }
}
***************
*** 1219,1226 **** full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt)
i;
VRelStats *vacrelstats;
! vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared,
! &OldestXmin, &FreezeLimit);
/*
* Flush any previous async-commit transactions. This does not guarantee
--- 1251,1259 ----
i;
VRelStats *vacrelstats;
! vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age,
! onerel->rd_rel->relisshared,
! &OldestXmin, &FreezeLimit, NULL);
/*
* Flush any previous async-commit transactions. This does not guarantee
*** src/backend/commands/vacuumlazy.c
--- src/backend/commands/vacuumlazy.c
***************
*** 144,149 **** lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
--- 144,151 ----
BlockNumber possibly_freeable;
PGRUsage ru0;
TimestampTz starttime = 0;
+ bool scan_all;
+ TransactionId freezeTableLimit;
pg_rusage_init(&ru0);
***************
*** 158,165 **** lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
vac_strategy = bstrategy;
! vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared,
! &OldestXmin, &FreezeLimit);
vacrelstats = (LVRelStats *) palloc0(sizeof(LVRelStats));
--- 160,170 ----
vac_strategy = bstrategy;
! vacuum_set_xid_limits(vacstmt->freeze_min_age, vacstmt->freeze_table_age,
! onerel->rd_rel->relisshared,
! &OldestXmin, &FreezeLimit, &freezeTableLimit);
! scan_all = TransactionIdPrecedesOrEquals(onerel->rd_rel->relfrozenxid,
! freezeTableLimit);
vacrelstats = (LVRelStats *) palloc0(sizeof(LVRelStats));
***************
*** 171,177 **** lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
vacrelstats->hasindex = (nindexes > 0);
/* Do the vacuuming */
! lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, vacstmt->scan_all);
/* Done with indexes */
vac_close_indexes(nindexes, Irel, NoLock);
--- 176,182 ----
vacrelstats->hasindex = (nindexes > 0);
/* Do the vacuuming */
! lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all);
/* Done with indexes */
vac_close_indexes(nindexes, Irel, NoLock);
*** src/backend/nodes/copyfuncs.c
--- src/backend/nodes/copyfuncs.c
***************
*** 2865,2871 **** _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);
--- 2865,2871 ----
COPY_SCALAR_FIELD(analyze);
COPY_SCALAR_FIELD(verbose);
COPY_SCALAR_FIELD(freeze_min_age);
! COPY_SCALAR_FIELD(freeze_table_age);
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(va_cols);
*** src/backend/nodes/equalfuncs.c
--- src/backend/nodes/equalfuncs.c
***************
*** 1454,1460 **** _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);
--- 1454,1460 ----
COMPARE_SCALAR_FIELD(analyze);
COMPARE_SCALAR_FIELD(verbose);
COMPARE_SCALAR_FIELD(freeze_min_age);
! COMPARE_SCALAR_FIELD(freeze_table_age);
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(va_cols);
*** src/backend/parser/gram.y
--- src/backend/parser/gram.y
***************
*** 6263,6269 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->analyze = false;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
! n->scan_all = $2 || $3;
n->verbose = $4;
n->relation = NULL;
n->va_cols = NIL;
--- 6263,6269 ----
n->analyze = false;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
! n->freeze_table_age = $3 ? 0 : -1;
n->verbose = $4;
n->relation = NULL;
n->va_cols = NIL;
***************
*** 6276,6282 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->analyze = false;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
! n->scan_all = $2 || $3;
n->verbose = $4;
n->relation = $5;
n->va_cols = NIL;
--- 6276,6282 ----
n->analyze = false;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
! n->freeze_table_age = $3 ? 0 : -1;
n->verbose = $4;
n->relation = $5;
n->va_cols = NIL;
***************
*** 6288,6294 **** VacuumStmt: VACUUM opt_full opt_freeze opt_verbose
n->vacuum = true;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
! n->scan_all = $2 || $3;
n->verbose |= $4;
$$ = (Node *)n;
}
--- 6288,6294 ----
n->vacuum = true;
n->full = $2;
n->freeze_min_age = $3 ? 0 : -1;
! n->freeze_table_age = $3 ? 0 : -1;
n->verbose |= $4;
$$ = (Node *)n;
}
***************
*** 6302,6307 **** AnalyzeStmt:
--- 6302,6308 ----
n->analyze = true;
n->full = false;
n->freeze_min_age = -1;
+ n->freeze_table_age = -1;
n->verbose = $2;
n->relation = NULL;
n->va_cols = NIL;
***************
*** 6314,6319 **** AnalyzeStmt:
--- 6315,6321 ----
n->analyze = true;
n->full = false;
n->freeze_min_age = -1;
+ n->freeze_table_age = -1;
n->verbose = $2;
n->relation = $3;
n->va_cols = $4;
*** src/backend/postmaster/autovacuum.c
--- src/backend/postmaster/autovacuum.c
***************
*** 136,143 **** static volatile sig_atomic_t got_SIGTERM = false;
/* Comparison point for determining whether freeze_max_age is exceeded */
static TransactionId recentXid;
! /* Default freeze_min_age to use for autovacuum (varies by database) */
static int default_freeze_min_age;
/* Memory context for long-lived data */
static MemoryContext AutovacMemCxt;
--- 136,144 ----
/* Comparison point for determining whether freeze_max_age is exceeded */
static TransactionId recentXid;
! /* Default freeze ages to use for autovacuum (varies by database) */
static int default_freeze_min_age;
+ static int default_freeze_table_age;
/* Memory context for long-lived data */
static MemoryContext AutovacMemCxt;
***************
*** 174,179 **** typedef struct autovac_table
--- 175,181 ----
bool at_dovacuum;
bool at_doanalyze;
int at_freeze_min_age;
+ int at_freeze_table_age;
int at_vacuum_cost_delay;
int at_vacuum_cost_limit;
bool at_wraparound;
***************
*** 1857,1863 **** do_autovacuum(void)
pgstat_vacuum_stat();
/*
! * Find the pg_database entry and select the default freeze_min_age. We
* use zero in template and nonconnectable databases, else the system-wide
* default.
*/
--- 1859,1865 ----
pgstat_vacuum_stat();
/*
! * Find the pg_database entry and select the default freeze ages. We
* use zero in template and nonconnectable databases, else the system-wide
* default.
*/
***************
*** 1869,1877 **** do_autovacuum(void)
--- 1871,1885 ----
dbForm = (Form_pg_database) GETSTRUCT(tuple);
if (dbForm->datistemplate || !dbForm->datallowconn)
+ {
default_freeze_min_age = 0;
+ default_freeze_table_age = 0;
+ }
else
+ {
default_freeze_min_age = vacuum_freeze_min_age;
+ default_freeze_table_age = vacuum_freeze_table_age;
+ }
ReleaseSysCache(tuple);
***************
*** 2418,2423 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map)
--- 2426,2432 ----
if (doanalyze || dovacuum)
{
int freeze_min_age;
+ int freeze_table_age;
int vac_cost_limit;
int vac_cost_delay;
***************
*** 2443,2448 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map)
--- 2452,2460 ----
freeze_min_age = (avForm->freeze_min_age >= 0) ?
avForm->freeze_min_age : default_freeze_min_age;
+
+ freeze_table_age = (avForm->freeze_table_age >= 0) ?
+ avForm->freeze_table_age : default_freeze_table_age;
}
else
{
***************
*** 2453,2458 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map)
--- 2465,2472 ----
autovacuum_vac_cost_delay : VacuumCostDelay;
freeze_min_age = default_freeze_min_age;
+
+ freeze_table_age = default_freeze_table_age;
}
tab = palloc(sizeof(autovac_table));
***************
*** 2460,2465 **** table_recheck_autovac(Oid relid, HTAB *table_toast_map)
--- 2474,2480 ----
tab->at_dovacuum = dovacuum;
tab->at_doanalyze = doanalyze;
tab->at_freeze_min_age = freeze_min_age;
+ tab->at_freeze_table_age = freeze_table_age;
tab->at_vacuum_cost_limit = vac_cost_limit;
tab->at_vacuum_cost_delay = vac_cost_delay;
tab->at_wraparound = wraparound;
***************
*** 2649,2655 **** 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;
--- 2664,2670 ----
vacstmt.full = false;
vacstmt.analyze = tab->at_doanalyze;
vacstmt.freeze_min_age = tab->at_freeze_min_age;
! vacstmt.freeze_table_age = tab->at_freeze_table_age;
vacstmt.verbose = false;
vacstmt.relation = NULL; /* not used since we pass a relid */
vacstmt.va_cols = NIL;
*** src/backend/utils/misc/guc.c
--- src/backend/utils/misc/guc.c
***************
*** 1544,1550 **** static struct config_int ConfigureNamesInt[] =
NULL
},
&vacuum_freeze_min_age,
! 100000000, 0, 1000000000, NULL, NULL
},
{
--- 1544,1559 ----
NULL
},
&vacuum_freeze_min_age,
! 50000000, 0, 1000000000, NULL, NULL
! },
!
! {
! {"vacuum_freeze_table_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
! gettext_noop("Age at which VACUUM should scan whole table to freeze tuples."),
! NULL
! },
! &vacuum_freeze_table_age,
! 150000000, 0, 2000000000, NULL, NULL
},
{
*** src/backend/utils/misc/postgresql.conf.sample
--- src/backend/utils/misc/postgresql.conf.sample
***************
*** 414,419 ****
--- 414,420 ----
#session_replication_role = 'origin'
#statement_timeout = 0 # 0 is disabled
#vacuum_freeze_min_age = 100000000
+ #vacuum_freeze_table_age = 150000000
#xmlbinary = 'base64'
#xmloption = 'content'
*** src/include/catalog/catversion.h
--- src/include/catalog/catversion.h
***************
*** 53,58 ****
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200901051
#endif
--- 53,58 ----
*/
/* yyyymmddN */
! #define CATALOG_VERSION_NO 200901141 /* XXX: update this before committing */
#endif
*** src/include/catalog/pg_autovacuum.h
--- src/include/catalog/pg_autovacuum.h
***************
*** 34,39 **** CATALOG(pg_autovacuum,1248) BKI_WITHOUT_OIDS
--- 34,40 ----
int4 vac_cost_limit; /* vacuum cost limit */
int4 freeze_min_age; /* vacuum min freeze age */
int4 freeze_max_age; /* max age before forcing vacuum */
+ int4 freeze_table_age; /* age at which vacuum scans whole table */
} FormData_pg_autovacuum;
/* ----------------
***************
*** 58,63 **** typedef FormData_pg_autovacuum *Form_pg_autovacuum;
--- 59,65 ----
#define Anum_pg_autovacuum_vac_cost_limit 8
#define Anum_pg_autovacuum_freeze_min_age 9
#define Anum_pg_autovacuum_freeze_max_age 10
+ #define Anum_pg_autovacuum_freeze_table_age 11
/* There are no preloaded tuples in pg_autovacuum.h */
*** src/include/commands/vacuum.h
--- src/include/commands/vacuum.h
***************
*** 122,127 **** typedef struct VacAttrStats
--- 122,128 ----
extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for
* PostGIS */
extern int vacuum_freeze_min_age;
+ extern int vacuum_freeze_table_age;
/* in commands/vacuum.c */
***************
*** 135,143 **** extern void vac_update_relstats(Relation relation,
double num_tuples,
bool hasindex,
TransactionId frozenxid);
! extern void vacuum_set_xid_limits(int freeze_min_age, bool sharedRel,
TransactionId *oldestXmin,
! TransactionId *freezeLimit);
extern void vac_update_datfrozenxid(void);
extern bool vac_is_partial_index(Relation indrel);
extern void vacuum_delay_point(void);
--- 136,146 ----
double num_tuples,
bool hasindex,
TransactionId frozenxid);
! extern void vacuum_set_xid_limits(int freeze_min_age, int freeze_table_age,
! bool sharedRel,
TransactionId *oldestXmin,
! TransactionId *freezeLimit,
! TransactionId *freezeTableLimit);
extern void vac_update_datfrozenxid(void);
extern bool vac_is_partial_index(Relation indrel);
extern void vacuum_delay_point(void);
*** src/include/nodes/parsenodes.h
--- src/include/nodes/parsenodes.h
***************
*** 2155,2162 **** 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 */
} VacuumStmt;
--- 2155,2162 ----
bool full; /* do FULL (non-concurrent) vacuum */
bool analyze; /* do ANALYZE step */
bool verbose; /* print progress info */
int freeze_min_age; /* min freeze age, or -1 to use default */
+ int freeze_table_age; /* age at which to scan whole table */
RangeVar *relation; /* single table to process, or NULL */
List *va_cols; /* list of column names, or NIL for all */
} VacuumStmt;
Re: Visibility map and freezing
От:
Alvaro Herrera <alvherre@commandprompt.com>
Дата:
Heikki Linnakangas escribió: > Alvaro, are you getting rid of pg_autovacuum in favor of the reloptions > for 8.4? That's the intention, yes. > This patch adds a new column to pg_autovacuum, reflecting the new > vacuum_freeze_table_age GUC just like freeze_min_age column reflects > vacuum_freeze_min_age. If I commit this tomorrow, will I cause you a > lot of trouble with the reloptions patch? No -- go ahead, I'll merge it. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Re: Visibility map and freezing
От:
Gregory Stark <stark@enterprisedb.com>
Дата:
Heikki Linnakangas writes: > Jeff Davis wrote: >> On Fri, 2009-01-09 at 13:49 +0200, Heikki Linnakangas wrote: >>> >>> I'm now leaning towards: >>> >>> autovacuum_freeze_max_age >>> vacuum_freeze_table_age >>> vacuum_freeze_min_age >>> >>> where autovacuum_freeze_max_age and vacuum_freeze_min_age are unchanged, and >>> vacuum_freeze_table_age is the new setting that controls when VACUUM or >>> autovacuum should perform a full scan of the table to advance relfrozenxid. >> >> I'm still bothered by the fact that "max" and "min" really mean the same >> thing here. > > Yeah. Those are existing names, though, and I don't recall any complaints from > users. > >> I don't think we can perfectly capture the meaning of these GUCs in the >> name. I think our goal should be to avoid confusion between them. > > Agreed. I was thinking it would be clearer if the options which control *when* autovacuum fires off a worker consistently had some action word in them like "trigger" or "start" or "launch". -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Alvaro Herrera wrote: > Heikki Linnakangas escribió: >> This patch adds a new column to pg_autovacuum, reflecting the new >> vacuum_freeze_table_age GUC just like freeze_min_age column reflects >> vacuum_freeze_min_age. If I commit this tomorrow, will I cause you a >> lot of trouble with the reloptions patch? > > No -- go ahead, I'll merge it. Ok, committed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Дата:
Gregory Stark wrote: > >> I don't think we can perfectly capture the meaning of these GUCs in the > >> name. I think our goal should be to avoid confusion between them. > > I was thinking it would be clearer if the options which control *when* > autovacuum fires off a worker consistently had some action word in them like > "trigger" or "start" or "launch". I think we need more explanations about those variables, not only "how to work" but also "how to tune" them. I feel they are un-tunable parameters. Our documentation says: | Larger values of these settings | preserve transactional information longer, while smaller values increase | the number of transactions that can elapse before the table must be | vacuumed again. i.e, we are explaining the variables only as "Larger is better", but is it really true? I think we should have answers about the following questions: - What relation are there between autovacuum_freeze_max_age, vacuum_freeze_min_age and vacuum_freeze_table_age? If we increase one of them, should we also increase the others? - Is it ok to increase the variables to maximum values? Are there any trade-off? - Are there some conditions where whole-table-scanning vacuum is more effective than vacuums using visibility map? If so, we should switch to full-scan *automatically*, without relying on user configurations. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
ITAGAKI Takahiro wrote: > Gregory Stark wrote: > >>>> I don't think we can perfectly capture the meaning of these GUCs in the >>>> name. I think our goal should be to avoid confusion between them. >> I was thinking it would be clearer if the options which control *when* >> autovacuum fires off a worker consistently had some action word in them like >> "trigger" or "start" or "launch". > > I think we need more explanations about those variables, > not only "how to work" but also "how to tune" them. > I feel they are un-tunable parameters. > > Our documentation says: > | Larger values of these settings > | preserve transactional information longer, while smaller values increase > | the number of transactions that can elapse before the table must be > | vacuumed again. > i.e, we are explaining the variables only as "Larger is better", > but is it really true? Yes, that is explicitly explained in the docs: > The sole disadvantage of increasing vacuum_freeze_table_age</> > and autovacuum_freeze_max_age</> > is that the pg_clog</> subdirectory of the database cluster > will take more space, because it must store the commit status for all > transactions back to the autovacuum_freeze_max_age</> horizon.> ... > - What relation are there between autovacuum_freeze_max_age, > vacuum_freeze_min_age and vacuum_freeze_table_age? If we increase > one of them, should we also increase the others? Yeah, that's a fair question. I'll try to work a doc patch to explain that better. > - Is it ok to increase the variables to maximum values? > Are there any trade-off? Disk space. > - Are there some conditions where whole-table-scanning vacuum is more > effective than vacuums using visibility map? If so, we should switch > to full-scan *automatically*, without relying on user configurations. Hmm, the only downside I can see is that skipping a page here and there could defeat the OS read-ahead. Perhaps we should call posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic to only skip pages when there's at least N consecutive pages that can be skipped. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
Simon Riggs <simon@2ndQuadrant.com>
Дата:
On Tue, 2009-01-20 at 11:03 +0200, Heikki Linnakangas wrote: > > - Are there some conditions where whole-table-scanning vacuum is > more > > effective than vacuums using visibility map? If so, we should > switch > > to full-scan *automatically*, without relying on user > configurations. > > Hmm, the only downside I can see is that skipping a page here and > there could defeat the OS read-ahead. Perhaps we should call > posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic > to only skip pages when there's at least N consecutive pages that can > be skipped. I would rather we didn't skip any pages at all unless the gains are significant. Skipping the odd page makes no difference from a performance perspective but may have a robustness impact. "Significant gains" should take into account the size of both heap and indexes, and recognise that we still scan whole indexes in either case. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Simon Riggs wrote:
> On Tue, 2009-01-20 at 11:03 +0200, Heikki Linnakangas wrote:
>>> - Are there some conditions where whole-table-scanning vacuum is
>> more
>>> effective than vacuums using visibility map? If so, we should
>> switch
>>> to full-scan *automatically*, without relying on user
>> configurations.
>>
>> Hmm, the only downside I can see is that skipping a page here and
>> there could defeat the OS read-ahead. Perhaps we should call
>> posix_fadvise(SEQUENTIAL) to compensate. Or, we could modify the logic
>> to only skip pages when there's at least N consecutive pages that can
>> be skipped.
>
> I would rather we didn't skip any pages at all unless the gains are
> significant. Skipping the odd page makes no difference from a
> performance perspective but may have a robustness impact.
>
> "Significant gains" should take into account the size of both heap and
> indexes, and recognise that we still scan whole indexes in either case.
That sounds pretty complex, approaching what the planner does. I'd
rather keep it simple.
Attached is a simple patch to only start skipping pages after 20
consecutive pages marked as visible in the visibility map. This doesn't
do any "look-ahead", so it will always scan the first 20 pages of a
table before it starts to skip pages, and whenever there's even one page
that needs vacuuming, the next 19 pages will also be vacuumed.
We could adjust that figure 20 according to table size. Or by
seq_page_cost/random_page_cost. But I'm leaning towards a simple
hard-coded value for now.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
*** src/backend/commands/vacuumlazy.c
--- src/backend/commands/vacuumlazy.c
***************
*** 271,276 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
--- 271,277 ----
int i;
PGRUsage ru0;
Buffer vmbuffer = InvalidBuffer;
+ BlockNumber all_visible_streak;
pg_rusage_init(&ru0);
***************
*** 292,297 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
--- 293,299 ----
lazy_space_alloc(vacrelstats, nblocks);
+ all_visible_streak = 0;
for (blkno = 0; blkno < nblocks; blkno++)
{
Buffer buf;
***************
*** 309,315 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
/*
* Skip pages that don't require vacuuming according to the
! * visibility map.
*/
if (!scan_all)
{
--- 311,324 ----
/*
* Skip pages that don't require vacuuming according to the
! * visibility map. But only if we've seen a streak of at least
! * 20 pages marked as clean. Since we're reading sequentially,
! * the OS should be doing readahead for us and there's no gain
! * in skipping a page now and then. You need a longer run of
! * consecutive skipped pages before it's worthwhile. Also,
! * skipping even a single page means that we can't update
! * relfrozenxid or reltuples, so we only want to do it if
! * there's a good chance to skip a goodly number of pages.
*/
if (!scan_all)
{
***************
*** 317,325 **** lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats,
visibilitymap_test(onerel, blkno, &vmbuffer);
if (all_visible_according_to_vm)
{
! vacrelstats->scanned_all = false;
! continue;
}
}
vacuum_delay_point();
--- 326,340 ----
visibilitymap_test(onerel, blkno, &vmbuffer);
if (all_visible_according_to_vm)
{
! all_visible_streak++;
! if (all_visible_streak >= 20)
! {
! vacrelstats->scanned_all = false;
! continue;
! }
}
+ else
+ all_visible_streak = 0;
}
vacuum_delay_point();
Re: Visibility map and freezing
От:
Simon Riggs <simon@2ndQuadrant.com>
Дата:
On Tue, 2009-01-20 at 21:51 +0200, Heikki Linnakangas wrote: > Attached is a simple patch to only start skipping pages after 20 > consecutive pages marked as visible in the visibility map. This doesn't > do any "look-ahead", so it will always scan the first 20 pages of a > table before it starts to skip pages, and whenever there's even one page > that needs vacuuming, the next 19 pages will also be vacuumed. > > We could adjust that figure 20 according to table size. Or by > seq_page_cost/random_page_cost. But I'm leaning towards a simple > hard-coded value for now. Yes, sounds good. Can we stick to multiples of 2 as the OS readahead does IIRC? So either 16 or 32. I'd go 32. -- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support
Re: Visibility map and freezing
От:
Euler Taveira de Oliveira <euler@timbira.com>
Дата:
Simon Riggs escreveu: > On Tue, 2009-01-20 at 21:51 +0200, Heikki Linnakangas wrote: > >> Attached is a simple patch to only start skipping pages after 20 >> consecutive pages marked as visible in the visibility map. This doesn't >> do any "look-ahead", so it will always scan the first 20 pages of a >> table before it starts to skip pages, and whenever there's even one page >> that needs vacuuming, the next 19 pages will also be vacuumed. >> >> We could adjust that figure 20 according to table size. Or by >> seq_page_cost/random_page_cost. But I'm leaning towards a simple >> hard-coded value for now. > > Yes, sounds good. Can we stick to multiples of 2 as the OS readahead > does IIRC? So either 16 or 32. I'd go 32. > Agreed. And do it a constant (ALL_VISIBLE_VM_THRESHOLD?). -- Euler Taveira de Oliveira http://www.timbira.com/
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Heikki Linnakangas wrote:
> ITAGAKI Takahiro wrote:
>> - What relation are there between autovacuum_freeze_max_age,
>> vacuum_freeze_min_age and vacuum_freeze_table_age? If we increase
>> one of them, should we also increase the others?
>
> Yeah, that's a fair question. I'll try to work a doc patch to explain
> that better.
Ok, how does this sound:
+ VACUUM</> normally skips pages that don't have any dead row
+ versions, but those pages might still have tuples with old XID values.
+ To replace them too, a scan of the whole table is needed every once
+ in a while. vacuum_freeze_table_age</> controls when
+ VACUUM</> does that: a whole table sweep is forced if
+ relfrozenxid</> is more than
+ vacuum_freeze_table_age</> transactions old. Setting it to 0
+ makes VACUUM</> to ignore the visibility map and always
scan all
+ pages. The effective maximum is 0.95 *
autovacuum_freeze_max_age</>;
+ a setting higher than that will be capped to that maximum. A value
+ higher than autovacuum_freeze_max_age</> wouldn't make sense
+ because an anti-wraparound autovacuum would be triggered at that point
+ anyway, and the 0.95 multiplier leaves some breathing room to run a
manual
+ VACUUM</> before that happens. As a rule of thumb,
+ vacuum_freeze_table_age</> should be set to a value somewhat
+ below autovacuum_freeze_max_age</>. Setting it too close could
+ lead to anti-wraparound autovacuums, even though the table was recently
+ manually vacuumed, whereas lower values lead to more frequent
whole-table
+ scans.
+
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: Visibility map and freezing
От:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Дата:
Euler Taveira de Oliveira wrote: > Simon Riggs escreveu: >> On Tue, 2009-01-20 at 21:51 +0200, Heikki Linnakangas wrote: >> >>> Attached is a simple patch to only start skipping pages after 20 >>> consecutive pages marked as visible in the visibility map. This doesn't >>> do any "look-ahead", so it will always scan the first 20 pages of a >>> table before it starts to skip pages, and whenever there's even one page >>> that needs vacuuming, the next 19 pages will also be vacuumed. >>> >>> We could adjust that figure 20 according to table size. Or by >>> seq_page_cost/random_page_cost. But I'm leaning towards a simple >>> hard-coded value for now. >> Yes, sounds good. Can we stick to multiples of 2 as the OS readahead >> does IIRC? So either 16 or 32. I'd go 32. >> > Agreed. And do it a constant (ALL_VISIBLE_VM_THRESHOLD?). Okay-dokay. I committed this with the constant as a #define, at value 32. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com