Re: multixacts woes - Mailing list pgsql-hackers
From | Robert Haas |
---|---|
Subject | Re: multixacts woes |
Date | |
Msg-id | CA+TgmoZSE0n8bo8yhr-m0=m6LTsyuuZ5WKLXtvRz_LZkdQC-6A@mail.gmail.com Whole thread Raw |
In response to | Re: multixacts woes (Noah Misch <noah@leadboat.com>) |
Responses |
Re: multixacts woes
|
List | pgsql-hackers |
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
pgsql-hackers by date: