Re: truncating pg_multixact/members

Alvaro Herrera escribió:

> 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.

Some more comments on the patch:

* I haven't introduced settings to tweak this per table for
autovacuum.  I don't think those are needed.  It's not hard to do,
however; if people opine against this, I will implement that.

* The multixact_freeze_table_age value has been set to 5 million.
I feel this is a big enough number that shouldn't cause too much
vacuuming churn, while at the same time not leaving excessive storage
occupied by pg_multixact/members, which amplifies the space used by the
average number of member in each multi.

(A bit of math: each Xid uses 2 bits.  Therefore for the default 200
million transactions of vacuum_freeze_table_age we use 50 million bytes,
or about 27 MB of space, plus some room for per-page LSNs.  For each
Multi we use 4 bytes in offset plus 5 bytes per member; if we consider 2
members per multi in average, that totals 70 million bytes for the
default multixact_freeze_table_age, so 66 MB of space.)

* I have named the parameters by simply replacing "vacuum" with
"multixact".  I could instead have added the "multixact" word in the
middle:
vacuum_multixact_freeze_min_age
but this doesn't seem an improvement.

* In the word "Multixact" in the docs I left the X as lowercase.  I used
uppercase first but that looked pretty odd.  In the middle of a
sentence, the M is also lowercase.


I reworded the paragraph in maintenance.sgml a bit.  If there are
suggestions, please shout.
  <para>    Similar to transaction IDs, Multixact IDs are implemented as a 32-bit    counter and corresponding storage
whichrequires 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,
thatinformation is stored separately    and only a reference to it is in the <structfield>xmax</> field in the tuple
header.  </para>     
 
   <para>    As with transaction IDs,    <command>VACUUM</> is in charge of removing old values.  Each
<command>VACUUM</>run sets <structname>pg_class</>.<structfield>relminmxid</>     indicating the oldest possible value
stillstored in that table; every time    this value is older than <xref linkend="guc-multixact-freeze-table-age">, a
full-tablescan is forced.    During any table scan (either partial or full-table), any multixact older    than <xref
linkend="guc-multixact-freeze-min-age">is replaced by something    else, which can be the zero value, a single
transactionID,    or a newer multixact.  Eventually, as all tables in all databases are    scanned and their oldest
multixactvalues are advanced, on-disk storage for    older multixacts can be removed.   </para>
 
  </sect3>

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services