Re: The case against multixact GUCs

On Tue, Mar 11, 2014 at 3:14 PM, Josh Berkus <josh@agliodbs.com> wrote:
> In the 9.3.3 updates, we added three new GUCs to control multixact
> freezing.  This was an unprecented move in my memory -- I can't recall
> ever adding a GUC to a minor release which wasn't backwards
> compatibility for a security fix.  This was a mistake.

I disagree.  I think it was the right decision.  I think it was a
mistake not including all of that stuff in the first place, and I
think it's good that we've now corrected that oversight.

> What makes these GUCs worse is that nobody knows how to set them; nobody
> on this list and nobody in the field.  Heck, I doubt 1 in 1000 of our
> users (or 1 in 10 people on this list) know what a multixact *is*.

Yeah, and that's a problem.   See, it turns out that we love periodic
full-table scans to freeze xmin so much that, in 9.3, we committed to
a design that requires us to make periodic full-table scans to freeze
xmax, too.  That may or may not have been a good decision, but at this
point we're stuck with it.  People are going to have to come to
understand the requirements there just as they do for freezing xmin.
Denying the user the ability to adjust the thresholds is not going to
accelerate the process of figuring out how they should be set.

> Further, there's no clear justification why these cannot be set to be
> the same as our other freeze ages (which our users also don't
> understand), or a constant calculated portion of them, or just a
> constant.

On most systems, mxid consumption will be much slower than xid
consumption because most users won't use tuple locks all that heavily.If we made all the defaults the same, then a
full-tablescan for xid
 
freezing would likely conclude that the many or all of the mxids
weren't old enough to be frozen yet.  To the greatest extent possible,
we want full-table vacuums for either XID freezing or MXID freezing to
advance both relfrozenxid and relminmxid so that we don't go through
and freeze for one reason and then have to come back and freeze for
the other reasons shortly thereafter.  Nobody knows exactly how to set
the settings to make that happen just yet, so we need settings at
least until people can determine what values work well in practice -
and probably permanently, because unfortunately I think the answer is
likely workload-dependent.

> Since nobody anticipated someone adding a GUC in a minor
> release, there was no discussion of this topic that I can find; the new
> GUCs were added as a "side effect" of fixing the multixact vacuum issue.
>  Certainly I would have raised a red flag if the discussion of the new
> GUCs hadn't been buried deep inside really long emails.

Alvaro did explicitly ask if anyone wanted to oppose back-patching.  I
don't think you can really blame him if you didn't see/read that
email.

> Adding new GUCs which nobody has any idea how to set, or can even
> explain to new users, is not a service to our users.  These should be
> removed.

The need for these GUCs is an outgrowth of the fkey locking stuff.
Unless we rip that out again or rewrite it completely, the need for
them doesn't seem likely to go away - so we're going to need to learn
to live with it, not pretend like it isn't a problem.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company