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 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 <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 still stored in that table; every time
this value is older than <xref linkend="guc-multixact-freeze-table-age">, a
full-table scan 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 transaction ID,
or a newer multixact. 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.
</para>

</sect3>

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