Robert Haas escribió:
> On Fri, Jan 3, 2014 at 9:11 AM, Alvaro Herrera <[email protected]>
> wrote:
> Yeah, this stuff is definitely underdocumented relative to vacuum right now.
I have added a paragraph or two. It's a (probably insufficient) start.
I would like to add a sample query to monitor usage, but I just realize
we don't have a function such as age(xid) to expose this info usefully.
We can't introduce one in 9.3 now, but probably we should do so in HEAD.
> Also, while multixactid_freeze_min_age should be low, perhaps a
> million as you suggest, multixactid_freeze_table_age should NOT be
> lowered to 3 million or anything like it. If you do that, people who
> are actually doing lots of row locking will start getting many more
> full-table scans. We want to avoid that at all cost. I'd probably
> make the default the same as for vacuum_freeze_table_age, so that
> mxids only cause extra full-table scans if they're being used more
> quickly than xids.
I agree that the freeze_table limit should not be low, but 150 million
seems too high. Not really sure what's a good value here.
Here's a first cut at this. Note I have omitted a setting equivalent to
autovacuum_freeze_max_age, but I think we should have one too.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 5148,5153 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 5148,5168 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-multixact-freeze-table-age" xreflabel="multixact_freeze_table_age">
+ <term><varname>multixact_freeze_table_age</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>multixact_freeze_table_age</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ <command>VACUUM</> performs a whole-table scan if the table's
+ <structname>pg_class</>.<structfield>relminmxid</> field has reached
+ the age specified by this setting. The default is 5 million multixacts.
+ For more information see <xref linkend="multixact-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>
***************
*** 5169,5174 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 5184,5205 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-multixact-freeze-min-age" xreflabel="multixact_freeze_min_age">
+ <term><varname>multixact_freeze_min_age</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>multixact_freeze_min_age</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the cutoff age (in multixacts) that <command>VACUUM</>
+ should use to decide whether to replace multixact IDs with a newer
+ transaction ID or multixact ID while scanning a table. The default
+ is 1 million multixacts.
+ For more information see <xref linkend="multixact-wraparound">.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-bytea-output" xreflabel="bytea_output">
<term><varname>bytea_output</varname> (<type>enum</type>)</term>
<indexterm>
*** a/doc/src/sgml/maintenance.sgml
--- b/doc/src/sgml/maintenance.sgml
***************
*** 599,604 **** HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
--- 599,632 ----
page for details about using a single-user backend.
</para>
+ <sect3 id="multixact-wraparound">
+ <title>Multixacts and Wraparound</title>
+
+ <indexterm>
+ <primary>Multixact ID</primary>
+ <secondary>wraparound</secondary>
+ </indexterm>
+ <!-- how about another index entry primary="wraparound",
+ secondary="multixact", and the same for xids? -->
+
+ <para>
+ Similar to transaction IDs, Multixact IDs are implemented as a 32-bit
+ counter and corresponding storage which requires careful aging management,
+ storage cleanup, and wraparound handling. Multixacts are used to implement
+ row locking by multiple transactions: since there is limited space in the
+ tuple header to store lock information, that information is stored separately
+ and only a reference to it is in the tuple header. As with transaction IDs,
+ <command>VACUUM</> is in charge of removing old values. Each
+ <command>VACUUM</> run sets a mark in each table that indicates what's the
+ oldest possible value still stored in it; every time this value is older than
+ <xref linkend="guc-multixact-freeze-table-age">, a full-table scan is forced.
+ Any Multixact older than <xref linkend="guc-multixact-freeze-min-age"> is
+ replaced by something else, which can be the zero value, a lone transaction ID,
+ or a newer Multixact. Eventually, as all tables in all databases have been
+ scanned and their oldest Multixact values are advanced, on-disk storage for
+ Multixact can be removed.
+ </para>
+ </sect3>
</sect2>
<sect2 id="autovacuum">
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
***************
*** 55,60 ****
--- 55,62 ----
*/
int vacuum_freeze_min_age;
int vacuum_freeze_table_age;
+ int multixact_freeze_min_age;
+ int multixact_freeze_table_age;
/* A few variables that don't seem worth passing around as parameters */
***************
*** 406,411 **** vacuum_set_xid_limits(int freeze_min_age,
--- 408,414 ----
MultiXactId *mxactFullScanLimit)
{
int freezemin;
+ int mxid_freezemin;
TransactionId limit;
TransactionId safeLimit;
MultiXactId mxactLimit;
***************
*** 462,472 **** vacuum_set_xid_limits(int freeze_min_age,
*freezeLimit = limit;
/*
! * simplistic MultiXactId removal limit: use the same policy as for
! * freezing Xids (except we use the oldest known mxact instead of the
! * current next value).
*/
! mxactLimit = GetOldestMultiXactId() - freezemin;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
*multiXactCutoff = mxactLimit;
--- 465,475 ----
*freezeLimit = limit;
/*
! * Determine the minimum multixact freeze age to use: as specified by
! * caller, or multixact_freeze_min_age.
*/
! mxid_freezemin = Min(freeze_min_age, multixact_freeze_min_age);
! mxactLimit = GetOldestMultiXactId() - mxid_freezemin;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
*multiXactCutoff = mxactLimit;
***************
*** 503,516 **** vacuum_set_xid_limits(int freeze_min_age,
/*
* Compute MultiXactId limit to cause a full-table vacuum, being
* careful not to generate an invalid multi. We just copy the logic
! * (and limits) from plain XIDs here.
*/
mxactLimit = ReadNextMultiXactId() - freezetable;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
*mxactFullScanLimit = mxactLimit;
}
}
/*
--- 506,524 ----
/*
* Compute MultiXactId limit to cause a full-table vacuum, being
* careful not to generate an invalid multi. We just copy the logic
! * from plain XIDs here.
*/
+ freezetable = multixact_freeze_table_age;
mxactLimit = ReadNextMultiXactId() - freezetable;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
*mxactFullScanLimit = mxactLimit;
}
+ else
+ {
+ Assert(mxactFullScanLimit == NULL);
+ }
}
/*
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 1907,1912 **** static struct config_int ConfigureNamesInt[] =
--- 1907,1932 ----
},
{
+ {"multixact_freeze_min_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Minimum age at which VACUUM should freeze a MultiXactId in a table row."),
+ NULL
+ },
+ &multixact_freeze_min_age,
+ 1000000, 0, 200000000,
+ NULL, NULL, NULL
+ },
+
+ {
+ {"multixact_freeze_table_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Multixact age at which VACUUM should scan whole table to freeze tuples."),
+ NULL
+ },
+ &multixact_freeze_table_age,
+ 5000000, 0, 200000000,
+ NULL, NULL, NULL
+ },
+
+ {
{"vacuum_defer_cleanup_age", PGC_SIGHUP, REPLICATION_MASTER,
gettext_noop("Number of transactions by which VACUUM and HOT cleanup should be deferred, if any."),
NULL
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 492,497 ****
--- 492,499 ----
#lock_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
+ #multixact_freeze_min_age = 1000000
+ #multixact_freeze_table_age = 5000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
*** a/src/include/commands/vacuum.h
--- b/src/include/commands/vacuum.h
***************
*** 136,141 **** extern PGDLLIMPORT int default_statistics_target; /* PGDLLIMPORT for
--- 136,143 ----
* PostGIS */
extern int vacuum_freeze_min_age;
extern int vacuum_freeze_table_age;
+ extern int multixact_freeze_min_age;
+ extern int multixact_freeze_table_age;
/* in commands/vacuum.c */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
