Thread: multixacts woes
My colleague Thomas Munro and I have been working with Alvaro, and also with Kevin and Amit, to fix bug #12990, a multixact-related data corruption bug. I somehow did not realize until very recently that we actually use two SLRUs to keep track of multixacts: one for the multixacts themselves (pg_multixacts/offsets) and one for the members (pg_multixacts/members). Confusingly, members are sometimes called offsets, and offsets are sometimes called IDs, or multixacts. If either of these SLRUs wraps around, we get data loss. This comment in multixact.c explains it well: /* * Since multixacts wrap differently from transaction IDs, this logic is * not entirely correct: insome scenarios we could go for longer than 2 * billion multixacts without seeing any data loss, and in some others we * could get in trouble before that if the new pg_multixact/members data * stomps on the previouscycle's data. For lack of a better mechanism we * use the same logic as for transaction IDs, that is, start taking action * halfway around the oldest potentially-existing multixact. */ multiWrapLimit = oldest_datminmxid+ (MaxMultiXactId >> 1); if (multiWrapLimit < FirstMultiXactId) multiWrapLimit += FirstMultiXactId; Apparently, we have been hanging our hat since the release of 9.3.0 on the theory that the average multixact won't ever have more than two members, and therefore the members SLRU won't overwrite itself and corrupt data. This is not good enough: we need to prevent multixact IDs from wrapping around, and we separately need to prevent multixact members from wrapping around, and the current code was conflating those things in a way that simply didn't work. Recent commits by Alvaro and by me have mostly fixed this, but there are a few loose ends: 1. I believe that there is still a narrow race condition that cause the multixact code to go crazy and delete all of its data when operating very near the threshold for member space exhaustion. See http://www.postgresql.org/message-id/CA+TgmoZiHwybETx8NZzPtoSjprg2Kcr-NaWGajkzcLcbVJ1pKQ@mail.gmail.com for the scenario and proposed fix. 2. We have some logic that causes autovacuum to run in spite of autovacuum=off when wraparound threatens. My commit 53bb309d2d5a9432d2602c93ed18e58bd2924e15 provided most of the anti-wraparound protections for multixact members that exist for multixact IDs and for regular XIDs, but this remains an outstanding issue. I believe I know how to fix this, and will work up an appropriate patch based on some of Thomas's earlier work. 3. It seems to me that there is a danger that some users could see extremely frequent anti-mxid-member-wraparound vacuums as a result of this work. Granted, that beats data corruption or errors, but it could still be pretty bad. The default value of autovacuum_multixact_freeze_max_age is 400000000. Anti-mxid-member-wraparound vacuums kick in when you exceed 25% of the addressable space, or 1073741824 total members. So, if your typical multixact has more than 1073741824/400000000 = ~2.68 members, you're going to see more autovacuum activity as a result of this change. We're effectively capping autovacuum_multixact_freeze_max_age at 1073741824/(average size of your multixacts). If your multixacts are just a couple of members (like 3 or 4) this is probably not such a big deal. If your multixacts typically run to 50 or so members, your effective freeze age is going to drop from 400m to ~21.4m. At that point, I think it's possible that relminmxid advancement might start to force full-table scans more often than would be required for relfrozenxid advancement. If so, that may be a problem for some users. What can we do about this? Alvaro proposed back-porting his fix for bug #8470, which avoids locking a row if a parent subtransaction already has the same lock. Alvaro tells me (via chat) that on some workloads this can dramatically reduce multixact size, which is certainly appealing. But the fix looks fairly invasive - it changes the return value of HeapTupleSatisfiesUpdate in certain cases, for example - and I'm not sure it's been thoroughly code-reviewed by anyone, so I'm a little nervous about the idea of back-porting it at this point. I am inclined to think it would be better to release the fixes we have - after handling items 1 and 2 - and then come back to this issue. Another thing to consider here is that if the high rate of multixact consumption is organic rather than induced by lots of subtransactions of the same parent locking the same tuple, this fix won't help. Another thought that occurs to me is that if we had a freeze map, it would radically decrease the severity of this problem, because freezing would become vastly cheaper. I wonder if we ought to try to get that into 9.5, even if it means holding up 9.5. Quite aside from multixacts, repeated wraparound autovacuuming of static data is a progressively more serious problem as data set sizes and transaction volumes increase. The possibility that multixact freezing may in some scenarios exacerbate that problem is just icing on the cake. The fundamental problem is that a 32-bit address space just isn't that big on modern hardware, and the problem is worse for multixact members than it is for multixact IDs, because a given multixact only uses consumes one multixact ID, but as many slots in the multixact member space as it has members. Thoughts, advice, etc. are most welcome. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi, On 2015-05-08 14:15:44 -0400, Robert Haas wrote: > Apparently, we have been hanging our hat since the release of 9.3.0 on > the theory that the average multixact won't ever have more than two > members, and therefore the members SLRU won't overwrite itself and > corrupt data. It's essentially a much older problem - it has essentially existed since multixacts were introduced (8.1?). The consequences of it were much lower before 9.3 though. > 3. It seems to me that there is a danger that some users could see > extremely frequent anti-mxid-member-wraparound vacuums as a result of > this work. Granted, that beats data corruption or errors, but it > could still be pretty bad. It's certainly possible to have workloads triggering that, but I think it's relatively uncommon. I in most cases I've checked the multixact consumption rate is much lower than the xid consumption. There are some exceptions, but often that's pretty bad code. > At that > point, I think it's possible that relminmxid advancement might start > to force full-table scans more often than would be required for > relfrozenxid advancement. If so, that may be a problem for some > users. I think it's the best we can do right now. > What can we do about this? Alvaro proposed back-porting his fix for > bug #8470, which avoids locking a row if a parent subtransaction > already has the same lock. Alvaro tells me (via chat) that on some > workloads this can dramatically reduce multixact size, which is > certainly appealing. But the fix looks fairly invasive - it changes > the return value of HeapTupleSatisfiesUpdate in certain cases, for > example - and I'm not sure it's been thoroughly code-reviewed by > anyone, so I'm a little nervous about the idea of back-porting it at > this point. I am inclined to think it would be better to release the > fixes we have - after handling items 1 and 2 - and then come back to > this issue. Another thing to consider here is that if the high rate > of multixact consumption is organic rather than induced by lots of > subtransactions of the same parent locking the same tuple, this fix > won't help. I'm not inclined to backport it at this stage. Maybe if we get some field reports about too many anti-wraparound vacuums due to this, *and* the code has been tested in 9.5. > Another thought that occurs to me is that if we had a freeze map, it > would radically decrease the severity of this problem, because > freezing would become vastly cheaper. I wonder if we ought to try to > get that into 9.5, even if it means holding up 9.5 I think that's not realistic. Doing this right isn't easy. And doing it wrong can lead to quite bad results, i.e. data corruption. Doing it under the pressure of delaying a release further and further seems like recipe for disaster. > Quite aside from multixacts, repeated wraparound autovacuuming of > static data is a progressively more serious problem as data set sizes > and transaction volumes increase. Yes. Agreed. > The possibility that multixact freezing may in some > scenarios exacerbate that problem is just icing on the cake. The > fundamental problem is that a 32-bit address space just isn't that big > on modern hardware, and the problem is worse for multixact members > than it is for multixact IDs, because a given multixact only uses > consumes one multixact ID, but as many slots in the multixact member > space as it has members. FWIW, I intend to either work on this myself, or help whoever seriously tackles this, in the next cycle. Greetings, Andres Freund
On Fri, May 8, 2015 at 2:27 PM, Andres Freund <andres@anarazel.de> wrote: > On 2015-05-08 14:15:44 -0400, Robert Haas wrote: >> Apparently, we have been hanging our hat since the release of 9.3.0 on >> the theory that the average multixact won't ever have more than two >> members, and therefore the members SLRU won't overwrite itself and >> corrupt data. > > It's essentially a much older problem - it has essentially existed since > multixacts were introduced (8.1?). The consequences of it were much > lower before 9.3 though. OK, I wasn't aware of that. What exactly were the consequences before 9.3? > I'm not inclined to backport it at this stage. Maybe if we get some > field reports about too many anti-wraparound vacuums due to this, *and* > the code has been tested in 9.5. That was about what I was thinking, too. >> Another thought that occurs to me is that if we had a freeze map, it >> would radically decrease the severity of this problem, because >> freezing would become vastly cheaper. I wonder if we ought to try to >> get that into 9.5, even if it means holding up 9.5 > > I think that's not realistic. Doing this right isn't easy. And doing it > wrong can lead to quite bad results, i.e. data corruption. Doing it > under the pressure of delaying a release further and further seems like > recipe for disaster. Those are certainly good things to worry about. > FWIW, I intend to either work on this myself, or help whoever seriously > tackles this, in the next cycle. That would be great. I'll investigate what resources EnterpriseDB can commit to this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 2015-05-08 14:32:14 -0400, Robert Haas wrote: > On Fri, May 8, 2015 at 2:27 PM, Andres Freund <andres@anarazel.de> wrote: > > On 2015-05-08 14:15:44 -0400, Robert Haas wrote: > >> Apparently, we have been hanging our hat since the release of 9.3.0 on > >> the theory that the average multixact won't ever have more than two > >> members, and therefore the members SLRU won't overwrite itself and > >> corrupt data. > > > > It's essentially a much older problem - it has essentially existed since > > multixacts were introduced (8.1?). The consequences of it were much > > lower before 9.3 though. > > OK, I wasn't aware of that. What exactly were the consequences before 9.3? I think just problems when locking a row. That's obviously much less bad than problems when reading a row. > > FWIW, I intend to either work on this myself, or help whoever seriously > > tackles this, in the next cycle. > > That would be great. With "this" I mean freeze avoidance. While I obviously, having proposed it as well at some point, think that freeze maps are a possible solution, I'm not yet sure that it's the best solution. > I'll investigate what resources EnterpriseDB can commit to this. Cool. Greetings, Andres Freund
On 05/08/2015 11:27 AM, Andres Freund wrote: > Hi, > > On 2015-05-08 14:15:44 -0400, Robert Haas wrote: >> 3. It seems to me that there is a danger that some users could see >> extremely frequent anti-mxid-member-wraparound vacuums as a result of >> this work. Granted, that beats data corruption or errors, but it >> could still be pretty bad. > > It's certainly possible to have workloads triggering that, but I think > it's relatively uncommon. I in most cases I've checked the multixact > consumption rate is much lower than the xid consumption. There are some > exceptions, but often that's pretty bad code. I have a couple workloads in my pool which do consume mxids faster than xids, due to (I think) execeptional numbers of FK conflicts. It's definitely unusual, though, and I'm sure they'd rather have corruption protection and endure some more vacuums. If we do this, though, it might be worthwhile to backport the multixact age function, so that affected users can check and schedule mxact wraparound vacuums themselves, something you currently can't do on 9.3. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 2015-05-08 12:57:17 -0700, Josh Berkus wrote: > I have a couple workloads in my pool which do consume mxids faster than > xids, due to (I think) execeptional numbers of FK conflicts. It's > definitely unusual, though, and I'm sure they'd rather have corruption > protection and endure some more vacuums. If we do this, though, it > might be worthwhile to backport the multixact age function, so that > affected users can check and schedule mxact wraparound vacuums > themselves, something you currently can't do on 9.3. That's not particularly realistic due to the requirement of an initdb to change the catalog. Greetings, Andres Freund
Josh Berkus wrote: > I have a couple workloads in my pool which do consume mxids faster than > xids, due to (I think) execeptional numbers of FK conflicts. It's > definitely unusual, though, and I'm sure they'd rather have corruption > protection and endure some more vacuums. If we do this, though, it > might be worthwhile to backport the multixact age function, so that > affected users can check and schedule mxact wraparound vacuums > themselves, something you currently can't do on 9.3. Backporting that is difficult in core, but you can do it with an extension without too much trouble. Also, the multixact age function does not give you the "oldest member" which is what you need to properly monitor the whole of this; you can add that to an extension too. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Robert Haas wrote: > My colleague Thomas Munro and I have been working with Alvaro, and > also with Kevin and Amit, to fix bug #12990, a multixact-related data > corruption bug. Thanks for this great summary of the situation. > 1. I believe that there is still a narrow race condition that cause > the multixact code to go crazy and delete all of its data when > operating very near the threshold for member space exhaustion. See > http://www.postgresql.org/message-id/CA+TgmoZiHwybETx8NZzPtoSjprg2Kcr-NaWGajkzcLcbVJ1pKQ@mail.gmail.com > for the scenario and proposed fix. I agree that there is a problem here. > 2. We have some logic that causes autovacuum to run in spite of > autovacuum=off when wraparound threatens. My commit > 53bb309d2d5a9432d2602c93ed18e58bd2924e15 provided most of the > anti-wraparound protections for multixact members that exist for > multixact IDs and for regular XIDs, but this remains an outstanding > issue. I believe I know how to fix this, and will work up an > appropriate patch based on some of Thomas's earlier work. I believe autovacuum=off is fortunately uncommon, but certainly getting this issue fixed is a good idea. > 3. It seems to me that there is a danger that some users could see > extremely frequent anti-mxid-member-wraparound vacuums as a result of > this work. I agree with the idea that the long term solution to this issue is to make the freeze process cheaper. I don't have any good ideas on how to make this less severe in the interim. You say the fix for #8470 is not tested thoroughly enough to back-patch it just yet, and I can behind that; so let's wait until 9.5 has been tested a bit more. Another avenue not mentioned and possibly worth exploring is making some more use of the multixact cache, and reuse multixacts that were previously issued and have the same effects as the one you're interested in: for instance, if you want a multixact with locking members (10,20,30) and you have one for (5,10,20,30) but transaction 5 has finished, then essentially both have the same semantics (because locks don't have any effect the transaction has finished) so we can use it instead of creating a new one. I have no idea how to implement this; obviously, having to run TransactionIdIsCurrentTransactionId for each member on each multixact in the cache each time you want to create a new multixact is not very reasonable. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Fri, May 8, 2015 at 5:39 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: >> 1. I believe that there is still a narrow race condition that cause >> the multixact code to go crazy and delete all of its data when >> operating very near the threshold for member space exhaustion. See >> http://www.postgresql.org/message-id/CA+TgmoZiHwybETx8NZzPtoSjprg2Kcr-NaWGajkzcLcbVJ1pKQ@mail.gmail.com >> for the scenario and proposed fix. > > I agree that there is a problem here. OK, I'm glad we now agree on that, since it seemed like you were initially unconvinced. >> 2. We have some logic that causes autovacuum to run in spite of >> autovacuum=off when wraparound threatens. My commit >> 53bb309d2d5a9432d2602c93ed18e58bd2924e15 provided most of the >> anti-wraparound protections for multixact members that exist for >> multixact IDs and for regular XIDs, but this remains an outstanding >> issue. I believe I know how to fix this, and will work up an >> appropriate patch based on some of Thomas's earlier work. > > I believe autovacuum=off is fortunately uncommon, but certainly getting > this issue fixed is a good idea. Right. >> 3. It seems to me that there is a danger that some users could see >> extremely frequent anti-mxid-member-wraparound vacuums as a result of >> this work. > > I agree with the idea that the long term solution to this issue is to > make the freeze process cheaper. I don't have any good ideas on how to > make this less severe in the interim. You say the fix for #8470 is not > tested thoroughly enough to back-patch it just yet, and I can behind > that; so let's wait until 9.5 has been tested a bit more. Sounds good. > Another avenue not mentioned and possibly worth exploring is making some > more use of the multixact cache, and reuse multixacts that were > previously issued and have the same effects as the one you're interested > in: for instance, if you want a multixact with locking members > (10,20,30) and you have one for (5,10,20,30) but transaction 5 has > finished, then essentially both have the same semantics (because locks > don't have any effect the transaction has finished) so we can use it > instead of creating a new one. I have no idea how to implement this; > obviously, having to run TransactionIdIsCurrentTransactionId for each > member on each multixact in the cache each time you want to create a new > multixact is not very reasonable. This sounds to me like it's probably too clever. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 05/08/2015 09:57 PM, Josh Berkus wrote: > [snip] >> It's certainly possible to have workloads triggering that, but I think >> it's relatively uncommon. I in most cases I've checked the multixact >> consumption rate is much lower than the xid consumption. There are some >> exceptions, but often that's pretty bad code. > I have a couple workloads in my pool which do consume mxids faster than > xids, due to (I think) execeptional numbers of FK conflicts. It's > definitely unusual, though, and I'm sure they'd rather have corruption > protection and endure some more vacuums. Seen corruption happen recently with OpenBravo on PostgreSQL 9.3.6 (Debian; binaries upgraded from 9.3.2) in a cluster pg_upgraded from 9.2.4 (albeit with quite insufficient autovacuum / poorly configured Postgres) I fear that this might be more widespread than we thought, depending on the exact workload/activity pattern. If it would help, I can try to get hold of a copy of the cluster in question (if the customer keeps any copy at all) > If we do this, though, it > might be worthwhile to backport the multixact age function, so that > affected users can check and schedule mxact wraparound vacuums > themselves, something you currently can't do on 9.3. Thanks, J.L.
On Fri, May 08, 2015 at 02:15:44PM -0400, Robert Haas wrote: > My colleague Thomas Munro and I have been working with Alvaro, and > also with Kevin and Amit, to fix bug #12990, a multixact-related data > corruption bug. Thanks Alvaro, Amit, Kevin, Robert and Thomas for mobilizing to get this fixed. > 1. I believe that there is still a narrow race condition that cause > the multixact code to go crazy and delete all of its data when > operating very near the threshold for member space exhaustion. See > http://www.postgresql.org/message-id/CA+TgmoZiHwybETx8NZzPtoSjprg2Kcr-NaWGajkzcLcbVJ1pKQ@mail.gmail.com > for the scenario and proposed fix. For anyone else following along, Thomas's subsequent test verified this threat beyond reasonable doubt: http://www.postgresql.org/message-id/CAEepm=3C32VPJLOo45y0c3-3KWXNV2xM4jaPTSVjCRD2VG0Qgg@mail.gmail.com > 2. We have some logic that causes autovacuum to run in spite of > autovacuum=off when wraparound threatens. My commit > 53bb309d2d5a9432d2602c93ed18e58bd2924e15 provided most of the > anti-wraparound protections for multixact members that exist for > multixact IDs and for regular XIDs, but this remains an outstanding > issue. I believe I know how to fix this, and will work up an > appropriate patch based on some of Thomas's earlier work. That would be good to have, and its implementation should be self-contained. > 3. It seems to me that there is a danger that some users could see > extremely frequent anti-mxid-member-wraparound vacuums as a result of > this work. Granted, that beats data corruption or errors, but it > could still be pretty bad. The default value of > autovacuum_multixact_freeze_max_age is 400000000. > Anti-mxid-member-wraparound vacuums kick in when you exceed 25% of the > addressable space, or 1073741824 total members. So, if your typical > multixact has more than 1073741824/400000000 = ~2.68 members, you're > going to see more autovacuum activity as a result of this change. > We're effectively capping autovacuum_multixact_freeze_max_age at > 1073741824/(average size of your multixacts). If your multixacts are > just a couple of members (like 3 or 4) this is probably not such a big > deal. If your multixacts typically run to 50 or so members, your > effective freeze age is going to drop from 400m to ~21.4m. At that > point, I think it's possible that relminmxid advancement might start > to force full-table scans more often than would be required for > relfrozenxid advancement. If so, that may be a problem for some > users. I don't know whether this deserves prompt remediation, but if it does, I would look no further than the hard-coded 25% figure. We permit users to operate close to XID wraparound design limits. GUC maximums force an anti-wraparound vacuum at no later than 93.1% of design capacity. XID assignment warns at 99.5%, then stops at 99.95%. PostgreSQL mandates a larger cushion for pg_multixact/offsets, with anti-wraparound VACUUM by 46.6% and a stop at 50.0%. Commit 53bb309d2d5a9432d2602c93ed18e58bd2924e15 introduced the bulkiest mandatory cushion yet, an anti-wraparound vacuum when pg_multixact/members is just 25% full. The pgsql-bugs thread driving that patch did reject making it GUC-controlled, essentially on the expectation that 25% should be adequate for everyone: http://www.postgresql.org/message-id/CA+Tgmoap6-o_5ESu5X2mBRVht_F+KNoY+oO12OvV_WekSA=ezQ@mail.gmail.com http://www.postgresql.org/message-id/20150506143418.GT2523@alvh.no-ip.org http://www.postgresql.org/message-id/1570859840.1241196.1430928954257.JavaMail.yahoo@mail.yahoo.com > What can we do about this? Alvaro proposed back-porting his fix for > bug #8470, which avoids locking a row if a parent subtransaction > already has the same lock. Like Andres and yourself, I would not back-patch it. > Another thought that occurs to me is that if we had a freeze map, it > would radically decrease the severity of this problem, because > freezing would become vastly cheaper. I wonder if we ought to try to > get that into 9.5, even if it means holding up 9.5. Declaring that a release will wait for a particular feature has consistently ended badly for PostgreSQL, and this feature is just in the planning stages. If folks are ready to hit the ground running on the project, I suggest they do so; a non-WIP submission to the first 9.6 CF would be a big accomplishment. The time to contemplate slipping it into 9.5 comes after the patch is done. If these aggressive ideas earn more than passing consideration, the 25% threshold should become user-controllable after all.
On 05/10/2015 10:30 AM, Robert Haas wrote: >>> 2. We have some logic that causes autovacuum to run in spite of >>> autovacuum=off when wraparound threatens. My commit >>> 53bb309d2d5a9432d2602c93ed18e58bd2924e15 provided most of the >>> anti-wraparound protections for multixact members that exist for >>> multixact IDs and for regular XIDs, but this remains an outstanding >>> issue. I believe I know how to fix this, and will work up an >>> appropriate patch based on some of Thomas's earlier work. >> I believe autovacuum=off is fortunately uncommon, but certainly getting >> this issue fixed is a good idea. > Right. > > I suspect it's quite a bit more common than many people imagine. cheers andrew
On 5/8/15 1:15 PM, Robert Haas wrote: > I somehow did not realize until very recently that we > actually use two SLRUs to keep track of multixacts: one for the > multixacts themselves (pg_multixacts/offsets) and one for the members > (pg_multixacts/members). Confusingly, members are sometimes called > offsets, and offsets are sometimes called IDs, or multixacts. FWIW, since I had to re-read this bit... * We use two SLRU areas, one for storing the offsets at which the data * startsfor each MultiXactId in the other one. This trick allows us to * store variable length arrays of TransactionIds. Another way this could be 'fixed' would be to bump MultiXactOffset (but NOT MultiXactId) to uint64. That would increase the number of total members we could keep by a factor of 2^32. At that point wraparound wouldn't even be possible, because you can't have more than 2^31 members in an MXID (and there can only be 2^31 MXIDs). It may not be a trivial change through, because SLRUs are currently capped at 2^32 pages. This probably isn't a good long-term solution, but it would eliminate the risk of really frequent freeze vacuums. It sounds like Josh at least knows some people that could cause big problems for. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
On Sun, May 10, 2015 at 1:40 PM, Noah Misch <noah@leadboat.com> wrote: > I don't know whether this deserves prompt remediation, but if it does, I would > look no further than the hard-coded 25% figure. We permit users to operate > close to XID wraparound design limits. GUC maximums force an anti-wraparound > vacuum at no later than 93.1% of design capacity. XID assignment warns at > 99.5%, then stops at 99.95%. PostgreSQL mandates a larger cushion for > pg_multixact/offsets, with anti-wraparound VACUUM by 46.6% and a stop at > 50.0%. Commit 53bb309d2d5a9432d2602c93ed18e58bd2924e15 introduced the > bulkiest mandatory cushion yet, an anti-wraparound vacuum when > pg_multixact/members is just 25% full. That's certainly one possible approach. I had discounted it because you can't really get more than a small multiple out of it, but getting 2-3x more room might indeed be enough to help some people quite a bit. Just raising the threshold from 25% to say 40% would buy back a healthy amount. Or, as you suggest, we could just add a GUC. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sun, May 10, 2015 at 09:17:58PM -0400, Robert Haas wrote: > On Sun, May 10, 2015 at 1:40 PM, Noah Misch <noah@leadboat.com> wrote: > > I don't know whether this deserves prompt remediation, but if it does, I would > > look no further than the hard-coded 25% figure. We permit users to operate > > close to XID wraparound design limits. GUC maximums force an anti-wraparound > > vacuum at no later than 93.1% of design capacity. XID assignment warns at > > 99.5%, then stops at 99.95%. PostgreSQL mandates a larger cushion for > > pg_multixact/offsets, with anti-wraparound VACUUM by 46.6% and a stop at > > 50.0%. Commit 53bb309d2d5a9432d2602c93ed18e58bd2924e15 introduced the > > bulkiest mandatory cushion yet, an anti-wraparound vacuum when > > pg_multixact/members is just 25% full. > > That's certainly one possible approach. I had discounted it because > you can't really get more than a small multiple out of it, but getting > 2-3x more room might indeed be enough to help some people quite a bit. > Just raising the threshold from 25% to say 40% would buy back a > healthy amount. Right. It's fair to assume that the new VACUUM burden would be discountable at a 90+% threshold, because the installations that could possibly find it expensive are precisely those experiencing corruption today. These reports took eighteen months to appear, whereas some corruption originating in commit 0ac5ad5 saw reports within three months. Therefore, sites burning pg_multixact/members proportionally faster than both pg_multixact/offsets and XIDs must be unusual. Bottom line: if we do need to reduce VACUUM burden caused by the commits you cited upthread, we almost certainly don't need more than a 4x improvement.
On Mon, May 11, 2015 at 12:56 AM, Noah Misch <noah@leadboat.com> wrote: > On Sun, May 10, 2015 at 09:17:58PM -0400, Robert Haas wrote: >> On Sun, May 10, 2015 at 1:40 PM, Noah Misch <noah@leadboat.com> wrote: >> > I don't know whether this deserves prompt remediation, but if it does, I would >> > look no further than the hard-coded 25% figure. We permit users to operate >> > close to XID wraparound design limits. GUC maximums force an anti-wraparound >> > vacuum at no later than 93.1% of design capacity. XID assignment warns at >> > 99.5%, then stops at 99.95%. PostgreSQL mandates a larger cushion for >> > pg_multixact/offsets, with anti-wraparound VACUUM by 46.6% and a stop at >> > 50.0%. Commit 53bb309d2d5a9432d2602c93ed18e58bd2924e15 introduced the >> > bulkiest mandatory cushion yet, an anti-wraparound vacuum when >> > pg_multixact/members is just 25% full. >> >> That's certainly one possible approach. I had discounted it because >> you can't really get more than a small multiple out of it, but getting >> 2-3x more room might indeed be enough to help some people quite a bit. >> Just raising the threshold from 25% to say 40% would buy back a >> healthy amount. > > Right. It's fair to assume that the new VACUUM burden would be discountable > at a 90+% threshold, because the installations that could possibly find it > expensive are precisely those experiencing corruption today. These reports > took eighteen months to appear, whereas some corruption originating in commit > 0ac5ad5 saw reports within three months. Therefore, sites burning > pg_multixact/members proportionally faster than both pg_multixact/offsets and > XIDs must be unusual. Bottom line: if we do need to reduce VACUUM burden > caused by the commits you cited upthread, we almost certainly don't need more > than a 4x improvement. I looked into the approach of adding a GUC called autovacuum_multixact_freeze_max_members to set the threshold. I thought to declare it this way: { + {"autovacuum_multixact_freeze_max_members", PGC_POSTMASTER, AUTOVACUUM, + gettext_noop("# of multixact members at which autovacuum is forced to prevent multixact member wraparound."), + NULL + }, + &autovacuum_multixact_freeze_max_members, + 2000000000, 10000000, 4000000000, + NULL, NULL, NULL + }, Regrettably, I think that's not going to work, because 4000000000 overflows int. We will evidently need to denote this GUC in some other units, unless we want to introduce config_int64. Given your concerns, and the need to get a fix for this out the door quickly, what I'm inclined to do for the present is go bump the threshold from 25% of MaxMultiXact to 50% of MaxMultiXact without changing anything else. Your analysis shows that this is more in line with the existing policy for multixact IDs than what I did, and it will reduce the threat of frequent wraparound scans. Now, it will also increase the chances of somebody hitting the wall before autovacuum can bail them out. But maybe not that much. If we need 75% of the multixact member space to complete one cycle of anti-wraparound vacuums, we're actually very close to the point where the system just cannot work. If that's one big table, we're done. Also, if somebody does have a workload where the auto-clamping doesn't provide them with enough headroom, they can still improve things by reducing autovacuum_multixact_freeze_max_age to a value less than the value to which we're auto-clamping it. If they need an effective value of less than 10 million they are out of luck, but if that is the case then there is a good chance that they are hosed anyway - an anti-wraparound vacuum every 10 million multixacts sounds awfully painful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, May 11, 2015 at 08:29:05AM -0400, Robert Haas wrote: > Given your concerns, and the need to get a fix for this out the door > quickly, what I'm inclined to do for the present is go bump the > threshold from 25% of MaxMultiXact to 50% of MaxMultiXact without > changing anything else. +1 > Your analysis shows that this is more in line > with the existing policy for multixact IDs than what I did, and it > will reduce the threat of frequent wraparound scans. Now, it will > also increase the chances of somebody hitting the wall before > autovacuum can bail them out. But maybe not that much. If we need > 75% of the multixact member space to complete one cycle of > anti-wraparound vacuums, we're actually very close to the point where > the system just cannot work. If that's one big table, we're done. Agreed.
On Mon, May 11, 2015 at 10:11 AM, Noah Misch <noah@leadboat.com> wrote: > On Mon, May 11, 2015 at 08:29:05AM -0400, Robert Haas wrote: >> Given your concerns, and the need to get a fix for this out the door >> quickly, what I'm inclined to do for the present is go bump the >> threshold from 25% of MaxMultiXact to 50% of MaxMultiXact without >> changing anything else. > > +1 > >> Your analysis shows that this is more in line >> with the existing policy for multixact IDs than what I did, and it >> will reduce the threat of frequent wraparound scans. Now, it will >> also increase the chances of somebody hitting the wall before >> autovacuum can bail them out. But maybe not that much. If we need >> 75% of the multixact member space to complete one cycle of >> anti-wraparound vacuums, we're actually very close to the point where >> the system just cannot work. If that's one big table, we're done. > > Agreed. 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. 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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
Josh Berkus wrote: > 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. If you have development resources to pour onto 9.5, I think it would be better spent changing multixact usage tracking so that oldestOffset is included in pg_control; also make pg_multixact truncation be WAL-logged. With those changes, the need for a lot of pretty complicated code would go away. The fact that truncation is done by both vacuum and checkpoint causes a lot of the mess we were in (and from which Robert and Thomas took us --- thanks guys!). Such a change is the first step towards auto-tuning, I think. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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. Many thanks for all the effort here -- much appreciated. > 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. See my response to Josh. I think much of the current rube-goldbergian design is due to the fact that pg_control cannot be changed in back branches. Going forward, I think a better plan is to include more info in pg_control, WAL-log more operations, remove checkpoint from the loop and have everything happen at vacuum time. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 05/11/2015 10:24 AM, Josh Berkus wrote: > 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. That is a documentation problem not a user problem. Although I agree that yet another GUC for an obscure "feature" that should be internally intelligent is likely the wrong direction. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.