Re: pg_multixact/members growing
On 2018-May-23, Tom Lane wrote: > Tiffany Thang 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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: pg_multixact/members growing
Tiffany Thang 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
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 wrote: > On Wed, May 23, 2018 at 7:49 AM, Tom Lane wrote: > > Tiffany Thang 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 >
Re: pg_multixact/members growing
On Wed, May 23, 2018 at 7:49 AM, Tom Lane wrote: > Tiffany Thang 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
Re: pg_multixact/members growing
Tiffany Thang 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
