Re: multixacts woes

On Mon, May 11, 2015 at 12:56 AM, Noah Misch <noah@leadboat.com> wrote:
> On Sun, May 10, 2015 at 09:17:58PM -0400, Robert Haas wrote:
>> On Sun, May 10, 2015 at 1:40 PM, Noah Misch <noah@leadboat.com> wrote:
>> > I don't know whether this deserves prompt remediation, but if it does, I would
>> > look no further than the hard-coded 25% figure.  We permit users to operate
>> > close to XID wraparound design limits.  GUC maximums force an anti-wraparound
>> > vacuum at no later than 93.1% of design capacity.  XID assignment warns at
>> > 99.5%, then stops at 99.95%.  PostgreSQL mandates a larger cushion for
>> > pg_multixact/offsets, with anti-wraparound VACUUM by 46.6% and a stop at
>> > 50.0%.  Commit 53bb309d2d5a9432d2602c93ed18e58bd2924e15 introduced the
>> > bulkiest mandatory cushion yet, an anti-wraparound vacuum when
>> > pg_multixact/members is just 25% full.
>>
>> That's certainly one possible approach.  I had discounted it because
>> you can't really get more than a small multiple out of it, but getting
>> 2-3x more room might indeed be enough to help some people quite a bit.
>> Just raising the threshold from 25% to say 40% would buy back a
>> healthy amount.
>
> Right.  It's fair to assume that the new VACUUM burden would be discountable
> at a 90+% threshold, because the installations that could possibly find it
> expensive are precisely those experiencing corruption today.  These reports
> took eighteen months to appear, whereas some corruption originating in commit
> 0ac5ad5 saw reports within three months.  Therefore, sites burning
> pg_multixact/members proportionally faster than both pg_multixact/offsets and
> XIDs must be unusual.  Bottom line: if we do need to reduce VACUUM burden
> caused by the commits you cited upthread, we almost certainly don't need more
> than a 4x improvement.

I looked into the approach of adding a GUC called
autovacuum_multixact_freeze_max_members to set the threshold.  I
thought to declare it this way:
       {
+               {"autovacuum_multixact_freeze_max_members",
PGC_POSTMASTER, AUTOVACUUM,
+                       gettext_noop("# of multixact members at which
autovacuum is forced to prevent multixact member wraparound."),
+                       NULL
+               },
+               &autovacuum_multixact_freeze_max_members,
+               2000000000, 10000000, 4000000000,
+               NULL, NULL, NULL
+       },

Regrettably, I think that's not going to work, because 4000000000
overflows int.  We will evidently need to denote this GUC in some
other units, unless we want to introduce config_int64.

Given your concerns, and the need to get a fix for this out the door
quickly, what I'm inclined to do for the present is go bump the
threshold from 25% of MaxMultiXact to 50% of MaxMultiXact without
changing anything else.  Your analysis shows that this is more in line
with the existing policy for multixact IDs than what I did, and it
will reduce the threat of frequent wraparound scans.  Now, it will
also increase the chances of somebody hitting the wall before
autovacuum can bail them out.  But maybe not that much.  If we need
75% of the multixact member space to complete one cycle of
anti-wraparound vacuums, we're actually very close to the point where
the system just cannot work.  If that's one big table, we're done.

Also, if somebody does have a workload where the auto-clamping doesn't
provide them with enough headroom, they can still improve things by
reducing autovacuum_multixact_freeze_max_age to a value less than the
value to which we're auto-clamping it.  If they need an effective
value of less than 10 million they are out of luck, but if that is the
case then there is a good chance that they are hosed anyway - an
anti-wraparound vacuum every 10 million multixacts sounds awfully
painful.

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