From 801c2dc72cb3c68a7c430bb244675b7a68fd541a Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 13 Feb 2014 19:30:30 -0300 Subject: Separate multixact freezing parameters from xid's Previously we were piggybacking on transaction ID parameters to freeze multixacts; but since there isn't necessarily any relationship between rates of Xid and multixact consumption, this turns out not to be a good idea. Therefore, we now have multixact-specific freezing parameters: vacuum_multixact_freeze_min_age: when to remove multis as we come across them in vacuum (default to 5 million, i.e. early in comparison to Xid's default of 50 million) vacuum_multixact_freeze_table_age: when to force whole-table scans instead of scanning only the pages marked as not all visible in visibility map (default to 150 million, same as for Xids). Whichever of both which reaches the 150 million mark earlier will cause a whole-table scan. autovacuum_multixact_freeze_max_age: when for cause emergency, uninterruptible whole-table scans (default to 400 million, double as that for Xids). This means there shouldn't be more frequent emergency vacuuming than previously, unless multixacts are being used very rapidly. Backpatch to 9.3 where multixacts were made to persist enough to require freezing. To avoid an ABI break in 9.3, VacuumStmt has a couple of fields in an unnatural place, and StdRdOptions is split in two so that the newly added fields can go at the end. Patch by me, reviewed by Robert Haas, with additional input from Andres Freund and Tom Lane. --- doc/src/sgml/config.sgml | 70 +++++++++++++++++++++++++++++++++++++- doc/src/sgml/maintenance.sgml | 56 +++++++++++++++++++++++++++++- doc/src/sgml/ref/create_table.sgml | 39 ++++++++++++++++++++- 3 files changed, 162 insertions(+), 3 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 000a46fabb..e12778b263 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -4892,6 +4892,33 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + autovacuum_multixact_freeze_max_age (integer) + + autovacuum_multixact_freeze_max_age configuration parameter + + + + Specifies the maximum age (in multixacts) that a table's + pg_class.relminmxid field can + attain before a VACUUM operation is forced to + prevent multixact ID wraparound within the table. + Note that the system will launch autovacuum processes to + prevent wraparound even when autovacuum is otherwise disabled. + + + + Vacuuming multixacts also allows removal of old files from the + pg_multixact/members and pg_multixact/offsets + subdirectories, which is why the default is a relatively low + 400 million multixacts. + This parameter can only be set at server start, but the setting + can be reduced for individual tables by changing storage parameters. + For more information see . + + + + autovacuum_vacuum_cost_delay (integer) @@ -5300,7 +5327,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; pg_class.relfrozenxid field has reached 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 + two billions, VACUUM will silently limit the effective value to 95% of , so that a periodical manual VACUUM has a chance to run before an anti-wraparound autovacuum is launched for the table. For more @@ -5331,6 +5358,47 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + vacuum_multixact_freeze_table_age (integer) + + vacuum_multixact_freeze_table_age configuration parameter + + + + VACUUM performs a whole-table scan if the table's + pg_class.relminmxid field has reached + the age specified by this setting. The default is 150 million multixacts. + Although users can set this value anywhere from zero to two billions, + VACUUM will silently limit the effective value to 95% of + , so that a + periodical manual VACUUM has a chance to run before an + anti-wraparound is launched for the table. + For more information see . + + + + + + vacuum_multixact_freeze_min_age (integer) + + vacuum_multixact_freeze_min_age configuration parameter + + + + Specifies the cutoff age (in multixacts) that 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 5 million multixacts. + 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 that there is not an unreasonably short time between forced + autovacuums. + For more information see . + + + + bytea_output (enum) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 339891e8a0..8ff309b78f 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -108,7 +108,8 @@ To protect against loss of very old data due to - transaction ID wraparound. + transaction ID wraparound or + multixact ID wraparound. @@ -379,6 +380,11 @@ wraparound + + wraparound + of transaction IDs + + PostgreSQL's MVCC transaction semantics depend on being able to compare transaction ID (XID) @@ -597,6 +603,54 @@ HINT: Stop the postmaster and vacuum that database in single-user mode. page for details about using single-user mode. + + Multixacts and Wraparound + + + MultiXactId + + + + wraparound + of multixact IDs + + + + 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 as a + multixact separately in the pg_multixact subdirectory, + and only its ID is in the xmax field + in the tuple header. + Similar to transaction IDs, multixact IDs are implemented as a + 32-bit counter and corresponding storage, all of which requires + careful aging management, storage cleanup, and wraparound handling. + + + + During a VACUUM table scan, either partial or of the whole + table, any multixact ID older than + + is replaced by a different value, which can be the zero value, a single + transaction ID, or a newer multixact ID. For each table, + pg_class.relminmxid stores the oldest + possible value still stored in any tuple of that table. Every time this + value is older than + , a whole-table + scan is forced. Whole-table VACUUM scans, regardless of + what causes them, enable advancing the value for that table. + Eventually, as all tables in all databases are scanned and their + oldest multixact values are advanced, on-disk storage for older + multixacts can be removed. + + + + As a safety device, a whole-table vacuum scan will occur for any table + whose multixact-age is greater than + . + This will occur even if autovacuum is nominally disabled. + + diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e0b8a4ecaf..7a01c63d5f 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -985,7 +985,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Custom parameter. Note that autovacuum will ignore attempts to set a per-table - autovacuum_freeze_min_age larger than the half system-wide + autovacuum_freeze_min_age larger than half the system-wide setting. @@ -1014,6 +1014,43 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI + + autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer) + + + Custom parameter. + Note that autovacuum will ignore attempts to set a per-table + autovacuum_multixact_freeze_min_age larger than half the + system-wide + setting. + + + + + + autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer) + + + Custom parameter. Note + that autovacuum will ignore attempts to set a per-table + autovacuum_multixact_freeze_max_age larger than the + system-wide setting (it can only be set smaller). Note that while you + can set autovacuum_multixact_freeze_max_age very small, + or even zero, this is usually unwise since it will force frequent + vacuuming. + + + + + + autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age (integer) + + + Custom parameter. + + + + -- cgit v1.2.1