Re: pg_multixact/members growing

Thanks Tom and Thomas.

Where do I find pg_controldata? I could not locate it on the file system.

pg_clog/ or pg_subtrans/ or pg_multixact/offsets/ are getting larger too
but by only a few hundreds MBs.

This is not a replicated system.

How do I tell if a system is aggressively running "wraparound prevention"
autovacuums?

Sorry, I failed to follow the calculation. How did you get
“~435 million more members can be created.”?

What happens when no more members can be created? Does the database halt or
shut down?

Thanks.

On Tue, May 22, 2018 at 7:20 PM Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
wrote:

> On Wed, May 23, 2018 at 7:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Tiffany Thang <tiffanythang(at)gmail(dot)com> writes:
> >> Our pg_multixact/members directory has been growing to more than 18GB
> over
> >> the last couple of months. According to the documentation, the files in
> >> there are used to support row locking by multiple transactions and when
> all
> >> tables in all databases are eventually scanned by VACUUM, the older
> >> multixacts are removed. In our case, the files are not removed.
> >
> > Hmm. What does pg_controldata tell you about NextMultiXactId,
> > NextMultiOffset, oldestMultiXid, oldestMulti's DB?
> > Are pg_clog/ or pg_subtrans/ or pg_multixact/offsets/ getting large?
> > Is there anything at all in pg_twophase/? Is this system a replication

> > master, and if so are any of its slaves lagging behind?
>
> Some thoughts:
>
> There are MULTIXACT_MEMBERS_PER_PAGE = 1636 members for every 8KB
> page. The reported directory size implies 18GB / 8KB * 1636 =
> 3,859,808,256 members. Above MULTIXACT_MEMBER_SAFE_THRESHOLD =
> 2,147,483,647 we should be triggering emergency autovacuums to try to
> reclaim space. Only ~435 million more members can be created.
>
> Is this system now aggressively running "wraparound prevention"
> autovacuums?
>
> There are MULTIXACT_OFFSETS_PER_PAGE = 2048 multixacts for every 8KB
> page, so the default autovacuum_multixact_freeze_max_age should
> soft-cap the size of pg_multixact/offsets at around 1.5GB ~=
> 400,000,000 / 2048 * 8KB.
>
> Unfortunately autovacuum_multixact_freeze_max_age doesn't impose any
> limit on the number of members. The totals can be quite explosive
> with high numbers of backends, because when n backends share lock a
> row we make O(n) multixacts and O(n^2) members. First we make a
> multixact with 2 members, then a new one with 3 members, etc... so
> that's n - 1 multixacts and (n * (n + 1)) / 2 - 1 members.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>