Thread: Visibility map and freezing
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
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. >
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
Zdenek Kotala <Zdenek.Kotala@Sun.COM> 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!
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)
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
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; </listitem> </varlistentry> + <varlistentry id="guc-vacuum-freeze-max-age" xreflabel="vacuum_freeze_max_age"> + <term><varname>vacuum_freeze_max_age</varname> (<type>integer</type>)</term> + <indexterm> + <primary><varname>vacuum_freeze_max_age</> configuration parameter</primary> + </indexterm> + <listitem> + <para> + <command>VACUUM</> performs a whole-table scan if the table's + <structname>pg_class</>.<structfield>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, <command>VACUUM</> will silently limit the effective value + to the value of <xref linkend="guc-autovacuum-freeze-max-age"> minus + 1 million transactions, so that regular manual <command>VACUUM</> has a + chance to run before autovacuum is launched to prevent XID wraparound. + For more information see <xref linkend="vacuum-for-wraparound">. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age"> <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term> <indexterm> @@ -3935,7 +3955,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; Specifies the cutoff age (in transactions) that <command>VACUUM</> should use to decide whether to replace transaction IDs with <literal>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, <command>VACUUM</> will silently limit the effective value to half the value of <xref linkend="guc-autovacuum-freeze-max-age">, 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 @@ </para> <para> - <command>VACUUM</>'s behavior is controlled by the configuration parameter - <xref linkend="guc-vacuum-freeze-min-age">: any XID older than - <varname>vacuum_freeze_min_age</> transactions is replaced by - <literal>FrozenXID</>. Larger values of <varname>vacuum_freeze_min_age</> + <command>VACUUM</>'s behavior is controlled by the two configuration + parameters: <xref linkend="guc-vacuum-freeze-min-age"> and + <xref linkend="guc-vacuum-freeze-max-age">. + <varname>vacuum_freeze_max_age</> controls when <command>VACUUM</> + performs a full sweep of the table to replace old XID values with + <literal>FrozenXID</>. <varname>vacuum_freeze_min_age</> + controls how old an XID value has to be before it's replaced with + <literal>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 @@ <para> The maximum time that a table can go unvacuumed is two billion transactions minus the <varname>vacuum_freeze_min_age</> that was used - when it was last vacuumed. If it were to go unvacuumed for longer than + when <command>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 <xref @@ -403,7 +408,8 @@ </para> <para> - The sole disadvantage of increasing <varname>autovacuum_freeze_max_age</> + The sole disadvantage of increasing <varname>vacuum_freeze_max_age</> + and <varname>autovacuum_freeze_max_age</> is that the <filename>pg_clog</> subdirectory of the database cluster will take more space, because it must store the commit status for all transactions back to the <varname>autovacuum_freeze_max_age</> horizon. @@ -459,9 +465,20 @@ SELECT datname, age(datfrozenxid) FROM pg_database; <command>VACUUM</>, <literal>age(relfrozenxid)</> should be a little more than the <varname>vacuum_freeze_min_age</> setting that was used (more by the number of transactions started since the <command>VACUUM</> - started). If <literal>age(relfrozenxid)</> exceeds - <varname>autovacuum_freeze_max_age</>, an autovacuum will soon be forced - for the table. + started). + </para> + + <para> + <command>VACUUM</> normally only scans pages that have been modified + since last vacuum, but <structfield>relfrozenxid</> can only be advanced + when the whole table is scanned. The whole table is scanned when + <structfield>relfrozenxid</> is more than + <varname>vacuum_freeze_max_age</> transactions old, if + <command>VACUUM FREEZE</> command is used, or if all pages happen to + require vacuuming to remove dead row versions. + If no whole-table-scanning <command>VACUUM</> is issued on the table until + <varname>autovacuum_freeze_max_age</> is reached, an autovacuum will soon + be forced for the table. </para> <para> 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 */
On Tue, Dec 23, 2008 at 4:24 AM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> 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
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
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
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
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
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
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 ---- <entry></entry> <entry>Custom <varname>autovacuum_freeze_max_age</> parameter</entry> </row> + + <row> + <entry><structfield>freeze_table_age</structfield></entry> + <entry><type>integer</type></entry> + <entry></entry> + <entry>Custom <varname>vacuum_freeze_table_age</> parameter</entry> + </row> </tbody> </tgroup> </table> *** 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 ---- </listitem> </varlistentry> + <varlistentry id="guc-vacuum-freeze-max-age" xreflabel="vacuum_freeze_max_age"> + <term><varname>vacuum_freeze_max_age</varname> (<type>integer</type>)</term> + <indexterm> + <primary><varname>vacuum_freeze_max_age</> configuration parameter</primary> + </indexterm> + <listitem> + <para> + <command>VACUUM</> performs a whole-table scan if the table's + <structname>pg_class</>.<structfield>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, <command>VACUUM</> will silently limit the effective value + to the value of <xref linkend="guc-autovacuum-freeze-max-age"> minus + 1 million transactions, so that regular manual <command>VACUUM</> has a + chance to run before autovacuum is launched to prevent XID wraparound. + For more information see <xref linkend="vacuum-for-wraparound">. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age"> <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term> <indexterm> *************** *** 3960,3966 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; Specifies the cutoff age (in transactions) that <command>VACUUM</> should use to decide whether to replace transaction IDs with <literal>FrozenXID</> while scanning a table. ! The default is 100 million transactions. Although users can set this value anywhere from zero to one billion, <command>VACUUM</> will silently limit the effective value to half the value of <xref linkend="guc-autovacuum-freeze-max-age">, so --- 3980,3986 ---- Specifies the cutoff age (in transactions) that <command>VACUUM</> should use to decide whether to replace transaction IDs with <literal>FrozenXID</> while scanning a table. ! The default is 50 million transactions. Although users can set this value anywhere from zero to one billion, <command>VACUUM</> will silently limit the effective value to half the value of <xref linkend="guc-autovacuum-freeze-max-age">, so *************** *** 3971,3976 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; --- 3991,4017 ---- </listitem> </varlistentry> + <varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age"> + <term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)</term> + <indexterm> + <primary><varname>vacuum_freeze_table_age</> configuration parameter</primary> + </indexterm> + <listitem> + <para> + Specifies the cutoff age (in transactions) that <command>VACUUM</> + should use to decide whether to replace scan the whole table, + to freeze old tuples and advance <structname>pg_class</>.<structfield>relfrozenxid</>. + The default is 150 million transactions. Although + users can set this value anywhere from zero to two billion, + <command>VACUUM</> will silently limit the effective value to 95% + of the value of <xref linkend="guc-autovacuum-freeze-max-age">, so + that a regularly run manual <command>VACUUM</> has a chance to do + a whole table scan before an anti-wraparound autovacuum is launched. + For more information see <xref linkend="vacuum-for-wraparound">. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-xmlbinary" xreflabel="xmlbinary"> <term><varname>xmlbinary</varname> (<type>enum</type>)</term> <indexterm> *** doc/src/sgml/maintenance.sgml --- doc/src/sgml/maintenance.sgml *************** *** 367,376 **** </para> <para> ! <command>VACUUM</>'s behavior is controlled by the configuration parameter ! <xref linkend="guc-vacuum-freeze-min-age">: any XID older than ! <varname>vacuum_freeze_min_age</> transactions is replaced by ! <literal>FrozenXID</>. Larger values of <varname>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 ---- </para> <para> ! <command>VACUUM</>'s behavior is controlled by the two configuration ! parameters: <xref linkend="guc-vacuum-freeze-min-age"> and ! <xref linkend="guc-vacuum-freeze-table-age">. ! <varname>vacuum_freeze_table_age</> controls when <command>VACUUM</> ! performs a full sweep of the table, in order to replace old XID values ! with <literal>FrozenXID</>. <varname>vacuum_freeze_min_age</> ! controls how old an XID value has to be before it's replaced with ! <literal>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 **** <para> The maximum time that a table can go unvacuumed is two billion transactions minus the <varname>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 <xref --- 383,390 ---- <para> The maximum time that a table can go unvacuumed is two billion transactions minus the <varname>vacuum_freeze_min_age</> that was used ! when <command>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 <xref *************** *** 403,409 **** </para> <para> ! The sole disadvantage of increasing <varname>autovacuum_freeze_max_age</> is that the <filename>pg_clog</> subdirectory of the database cluster will take more space, because it must store the commit status for all transactions back to the <varname>autovacuum_freeze_max_age</> horizon. --- 408,415 ---- </para> <para> ! The sole disadvantage of increasing <varname>vacuum_freeze_table_age</> ! and <varname>autovacuum_freeze_max_age</> is that the <filename>pg_clog</> subdirectory of the database cluster will take more space, because it must store the commit status for all transactions back to the <varname>autovacuum_freeze_max_age</> horizon. *************** *** 411,418 **** <varname>autovacuum_freeze_max_age</> has its maximum allowed value of a little less than two billion, <filename>pg_clog</> can be expected to grow to about half a gigabyte. If this is trivial compared to your ! total database size, setting <varname>autovacuum_freeze_max_age</> to ! its maximum allowed value is recommended. Otherwise, set it depending on what you are willing to allow for <filename>pg_clog</> storage. (The default, 200 million transactions, translates to about 50MB of <filename>pg_clog</> storage.) --- 417,425 ---- <varname>autovacuum_freeze_max_age</> has its maximum allowed value of a little less than two billion, <filename>pg_clog</> can be expected to grow to about half a gigabyte. If this is trivial compared to your ! total database size, setting <varname>autovacuum_freeze_max_age</> and ! <varname>vacuum_freeze_table_age</varname> to their maximum allowed values ! is recommended. Otherwise, set them depending on what you are willing to allow for <filename>pg_clog</> storage. (The default, 200 million transactions, translates to about 50MB of <filename>pg_clog</> storage.) *************** *** 455,467 **** SELECT datname, age(datfrozenxid) FROM pg_database; </programlisting> The <literal>age</> column measures the number of transactions from the ! cutoff XID to the current transaction's XID. Immediately after a ! <command>VACUUM</>, <literal>age(relfrozenxid)</> should be a little ! more than the <varname>vacuum_freeze_min_age</> setting that was used ! (more by the number of transactions started since the <command>VACUUM</> ! started). If <literal>age(relfrozenxid)</> exceeds ! <varname>autovacuum_freeze_max_age</>, an autovacuum will soon be forced ! for the table. </para> <para> --- 462,485 ---- </programlisting> The <literal>age</> column measures the number of transactions from the ! cutoff XID to the current transaction's XID. When <command>VACUUM</> ! scans the whole table, after it's finished <literal>age(relfrozenxid)</> ! should be a little more than the <varname>vacuum_freeze_min_age</> setting ! that was used (more by the number of transactions started since the ! <command>VACUUM</> started). ! </para> ! ! <para> ! <command>VACUUM</> normally only scans pages that have been modified ! since last vacuum, but <structfield>relfrozenxid</> can only be advanced ! when the whole table is scanned. The whole table is scanned when ! <structfield>relfrozenxid</> is more than ! <varname>vacuum_freeze_table_age</> transactions old, if ! <command>VACUUM FREEZE</> command is used, or if all pages happen to ! require vacuuming to remove dead row versions. If no whole-table-scanning ! <command>VACUUM</> is issued on the table until ! <varname>autovacuum_freeze_max_age</> is reached, an autovacuum will soon ! be forced for the table. </para> <para> *** 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;
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.
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> 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!
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
Gregory Stark <stark@enterprisedb.com> 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 increaseone 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
ITAGAKI Takahiro wrote: > Gregory Stark <stark@enterprisedb.com> 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 <varname>vacuum_freeze_table_age</> > and <varname>autovacuum_freeze_max_age</> > is that the <filename>pg_clog</> subdirectory of the database cluster > will take more space, because it must store the commit status for all > transactions back to the <varname>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
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
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();
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
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/
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:
<para>
+ <command>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. <varname>vacuum_freeze_table_age</> controls when
+ <command>VACUUM</> does that: a whole table sweep is forced if
+ <structfield>relfrozenxid</> is more than
+ <varname>vacuum_freeze_table_age</> transactions old. Setting it to 0
+ makes <command>VACUUM</> to ignore the visibility map and always
scan all
+ pages. The effective maximum is 0.95 *
<varname>autovacuum_freeze_max_age</>;
+ a setting higher than that will be capped to that maximum. A value
+ higher than <varname>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
+ <command>VACUUM</> before that happens. As a rule of thumb,
+ <command>vacuum_freeze_table_age</> should be set to a value somewhat
+ below <varname>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.
+ </para>
-- Heikki Linnakangas EnterpriseDB http://www.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