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.
