Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Robert Haas wrote:
>> So here's a new patch, based on your latest version, which looks
>> reasonably committable to me.
>
> I think this code should also reduce the multixact_freeze_min_age value
> at the same time as multixact_freeze_table_age. If the table age is
> reduced but freeze_min_age remains high, old multixacts might still
> remain in the table. The default value for freeze min age is 5 million,
> but users may change it. Perhaps freeze min age should be set to
> Min(modified freeze table age, freeze min age) so that old multixacts
> are effectively frozen whenever a full table scan requested.
I would rather see min age reduced proportionally to table age, or
at least ensure that min age is some percentage below table age.
>> 1. Should we be installing one or more GUCs to control this behavior?
>> I've gone back to hard-coding things so that at 25% we start
>> triggering autovacuum and by 75% we zero out the freeze ages, because
>> the logic you proposed in your last version looks insanely complicated
>> to me. (I do realize that I suggested the approach, but that was
>> before I realized the full complexity of the problem.) I now think
>> that if we want to make this tunable, we need to create and expose
>> GUCs for it. I'm hoping we can get by without that, but I'm not sure.
>
> I think things are complicated enough; I vote for no additional GUCs at
> this point.
+1
For one thing, we should try to have something we can back-patch,
and new GUCs in a minor release seems like something to avoid, if
possible. For another thing, we've tended not to put in GUCs if
there is no reasonable way for a user to determine a good value,
and that seems to be the case here.
>> 2. Doesn't the code that sets MultiXactState->multiVacLimit also need
>> to use what I'm now calling MultiXactMemberFreezeThreshold() - or some
>> similar logic? Otherwise, a user with autovacuum=off won't get
>> emergency autovacuums for member exhaustion, even though they will get
>> them for offset exhaustion.
>
> Yeah, it looks like it does.
+1
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company