Re: truncating pg_multixact/members

Hi,

On 2014-01-03 11:11:13 -0300, Alvaro Herrera wrote:
> > Yeah. Since we expect mxids to be composed at a much lower rate than
> > xids, we can keep pg_multixact small without needing to increase the
> > rate of full table scans.

I don't think that's necessarily true - there have been several
pg_controldata outputs posted lately which had more multis used than
xids. In workloads using explicit row locking or heavily used FKs that's
not that suprising.

> > However, it seems to me that we ought to
> > have GUCs for mxid_freeze_table_age and mxid_freeze_min_age. There's
> > no principled way to derive those values from the corresponding values
> > for XIDs, and I can't see any reason to suppose that we know how to
> > auto-tune brand new values better than we know how to auto-tune their
> > XID equivalents that we've had for years.
> >
> > One million is probably a reasonable default for mxid_freeze_min_age, though.

I think setting mxid_freeze_min_age to something lower is fair game, I'd
even start at 100k or so. What I think is important is that we do *not*
set mxid_freeze_table_age to something very low. People justifiedly hate
anti-wraparound vacuums.

What's your thought about the autovacuum_freeze_max_age equivalent?

I am not sure about introducing new GUCs in the back branches, I don't
have a problem with it, but I am also not sure it's necessary. Fixing
members wraparound into itself seems more important and once we trigger
vacuums via that it doesn't seem to be too important to have low
settings.

> Also, what would be good names? Peter E. complained recently about the
> word MultiXactId being exposed in some error messages; maybe "mxid" is
> too short an abbreviation of that. Perhaps
> multixactid_freeze_min_age = 1 million
> multixactid_freeze_table_age = 3 million
> ?

I personally am fine with mxid - we use xid in other settings after all.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services