Thread: pg_multixact/members growing

Tiffany Thang <tiffanythang@gmail.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?

> Any
> suggestions what I should do to purge the files automatically? Can old
> files since the last reboot be manually removed?

I wouldn't do that.  Much safer to figure out what's blocking automatic
cleanup so you can fix the root cause.

            regards, tom lane

On Wed, May 23, 2018 at 7:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tiffany Thang <tiffanythang@gmail.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

Tiffany Thang <tiffanythang@gmail.com> writes:
> Where do I find pg_controldata? I could not locate it on the file system.

Hmm, should be one of the installed PG executables.

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

This is consistent with the idea that something is holding back the
oldest-transaction horizon.  (However, I'd think you'd also be having
serious problems with table bloat if that were true, so it's a bit
mystifying.)  Did you check for unclosed prepared transactions?

    select * from pg_prepared_xacts;

            regards, tom lane

On 2018-May-23, Tom Lane wrote:

> Tiffany Thang <tiffanythang@gmail.com> writes:
> > Where do I find pg_controldata? I could not locate it on the file system.
> 
> Hmm, should be one of the installed PG executables.
> 
> > pg_clog/ or pg_subtrans/ or pg_multixact/offsets/  are getting larger too
> > but by only a few hundreds MBs.
> 
> This is consistent with the idea that something is holding back the
> oldest-transaction horizon.  (However, I'd think you'd also be having
> serious problems with table bloat if that were true, so it's a bit
> mystifying.)  Did you check for unclosed prepared transactions?

Another option is that you have a standby server with
hot_standby_feedback enabled, and an open transaction there.  I'm not
sure to what extent it is possible for that to cause multixact problems,
but it wouldn't hurt to check.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services