Re: multixacts woes

On 05/11/2015 09:54 AM, Robert Haas wrote:
> OK, I have made this change. Barring further trouble reports, this
> completes the multixact work I plan to do for the next release. Here
> is what is outstanding:
>
> 1. We might want to introduce a GUC to control the point at which
> member offset utilization begins clamping
> autovacuum_multixact_freeze_max_age. It doesn't seem wise to do
> anything about this before pushing a minor release out. It's not
> entirely trivial, and it may be helpful to learn more about how the
> changes already made work out in practice before proceeding. Also, we
> might not back-patch this anyway.

-1 on back-patching a new GUC. People don't know what to do with the
existing multixact GUCs, and without an age(multixact) function
built-in, any adjustments a user tries to make are likely to do more
harm than good.

In terms of adding a new GUC in 9.5: can't we take a stab at auto-tuning
this instead of adding a new GUC? We already have a bunch of freezing
GUCs which fewer than 1% of our user base has any idea how to set.

> 2. The recent changes adjust things - for good reason - so that the
> safe threshold for multixact member creation is advanced only at
> checkpoint time. This means it's theoretically possible to have a
> situation where autovacuum has done all it can, but because no
> checkpoint has happened yet, the user can't create any more
> multixacts. Thanks to some good work by Thomas, autovacuum will
> realize this and avoid spinning uselessly over every table in the
> system, which is good, but you're still stuck with errors until the
> next checkpoint. Essentially, we're hoping that autovacuum will clean
> things up far enough in advance of hitting the threshold where we have
> to throw an error that a checkpoint will intervene before the error
> starts happening. It's possible we could improve this further, but I
> think it would be unwise to mess with it right now. It may be that
> there is no real-world problem here.

Given that our longest possible checkpoint timeout is an hour, is it
even hypotethically possible that we would hit a limit in that time?
How many mxact members are we talking about?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com