Thread: We probably need autovacuum_max_wraparound_workers
Folks, Yeah, I can't believe I'm calling for *yet another* configuration variable either. Suggested workaround fixes very welcome. The basic issue is that autovacuum_max_workers is set by most users based on autovac's fairly lightweight action most of the time: analyze, vacuuming pages not on the visibility list, etc. However, when XID wraparound kicks in, then autovac starts reading entire tables from disk ... and those tables may be very large. This becomes a downtime issue if you've set autovacuum_max_workers to, say, 5 and several large tables hit the wraparound threshold at the same time (as they tend to do if you're using the default settings). Then you have 5 autovacuum processes concurrently doing heavy IO and getting in each others' way. I've seen this at two sites now, and my conclusion is that a single autovacuum_max_workers isn't sufficient if to cover the case of wraparound vacuum. Nor can we just single-thread the wraparound vacuum (i.e. just one worker) since that would hurt users who have thousands of small tables. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> Yeah, I can't believe I'm calling for *yet another* configuration
> variable either. Suggested workaround fixes very welcome.
> The basic issue is that autovacuum_max_workers is set by most users
> based on autovac's fairly lightweight action most of the time: analyze,
> vacuuming pages not on the visibility list, etc. However, when XID
> wraparound kicks in, then autovac starts reading entire tables from disk
> ... and those tables may be very large.
It doesn't seem to me that this has much of anything to do with
wraparound; that just happens to be one possible trigger condition
for a lot of vacuuming activity to be happening. (Others are bulk
data loads or bulk updates, for instance.) Nor am I convinced that
changing the max_workers setting is an appropriate fix anyway.
I think what you've really got here is inappropriate autovacuum cost
delay settings, and/or the logic in autovacuum.c to try to divvy up the
available I/O capacity by tweaking workers' delay settings isn't working
very well. It's hard to propose improvements without a lot more detail
than you've provided, though.
regards, tom lane
On Jun 27, 2012, at 22:00, Josh Berkus <josh@agliodbs.com> wrote: > Folks, > > Yeah, I can't believe I'm calling for *yet another* configuration > variable either. Suggested workaround fixes very welcome. > > The basic issue is that autovacuum_max_workers is set by most users > based on autovac's fairly lightweight action most of the time: analyze, > vacuuming pages not on the visibility list, etc. However, when XID > wraparound kicks in, then autovac starts reading entire tables from disk > ... and those tables may be very large. > > This becomes a downtime issue if you've set autovacuum_max_workers to, > say, 5 and several large tables hit the wraparound threshold at the same > time (as they tend to do if you're using the default settings). Then > you have 5 autovacuum processes concurrently doing heavy IO and getting > in each others' way. > > I've seen this at two sites now, and my conclusion is that a single > autovacuum_max_workers isn't sufficient if to cover the case of > wraparound vacuum. Nor can we just single-thread the wraparound vacuum > (i.e. just one worker) since that would hurt users who have thousands of > small tables. > > Would there be enough benefit to setting up separate small/medium?/large thresholds with user-changeable default table sizeboundaries so that you can configure 6 workers where 3 handle the small tables, 2 handle the medium tables, and 1 handlesthe large tables. Or alternatively a small worker consumes 1, medium 2, and large 3 'units' from whatever size poolhas been defined. So you could have 6 small tables or two large tables in-progress simultaneously. David J.
> I think what you've really got here is inappropriate autovacuum cost > delay settings, and/or the logic in autovacuum.c to try to divvy up the > available I/O capacity by tweaking workers' delay settings isn't working > very well. It's hard to propose improvements without a lot more detail > than you've provided, though. Wait, we *have* that logic? If so, that's the problem ... it's not working very well. What detail do you want? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh, all,
* Josh Berkus (josh@agliodbs.com) wrote:
> Yeah, I can't believe I'm calling for *yet another* configuration
> variable either. Suggested workaround fixes very welcome.
As I suggested on IRC, my thought would be to have a goal-based system
for autovacuum which is similar to our goal-based commit system. We
don't need autovacuum sucking up all the I/O in the box, nor should we
ask the users to manage that. Instead, let's decide when the autovacuum
on a given table needs to finish and then plan to keep on working at a
rate that'll allow us to get done well in advance of that deadline.
Just my 2c.
Thanks,
Stephen
Josh Berkus <josh@agliodbs.com> writes:
>> I think what you've really got here is inappropriate autovacuum cost
>> delay settings, and/or the logic in autovacuum.c to try to divvy up the
>> available I/O capacity by tweaking workers' delay settings isn't working
>> very well. It's hard to propose improvements without a lot more detail
>> than you've provided, though.
> Wait, we *have* that logic? If so, that's the problem ... it's not
> working very well.
> What detail do you want?
What's it doing? What do you think it should do instead?
regards, tom lane
Stephen Frost <sfrost@snowman.net> writes:
> * Josh Berkus (josh@agliodbs.com) wrote:
>> Yeah, I can't believe I'm calling for *yet another* configuration
>> variable either. Suggested workaround fixes very welcome.
> As I suggested on IRC, my thought would be to have a goal-based system
> for autovacuum which is similar to our goal-based commit system. We
> don't need autovacuum sucking up all the I/O in the box, nor should we
> ask the users to manage that. Instead, let's decide when the autovacuum
> on a given table needs to finish and then plan to keep on working at a
> rate that'll allow us to get done well in advance of that deadline.
If we allow individual vacuum operations to stretch out just because
they don't need to be completed right away, we will need more concurrent
vacuum workers (so that we can respond to vacuum requirements for other
tables). So I submit that this would only move the problem around:
the number of active workers would increase to the point where things
are just as painful, plus or minus a bit.
The intent of the autovacuum cost delay features is to ensure that
autovacuum doesn't suck an untenable fraction of the machine's I/O
capacity, even when it's running flat out. So I think Josh's complaint
indicates that we have a problem with cost-delay tuning; hard to tell
what exactly without more info. It might only be that the defaults
are bad for these particular users, or it could be more involved.
regards, tom lane
On Wed, Jun 27, 2012 at 11:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Stephen Frost <sfrost@snowman.net> writes: >> * Josh Berkus (josh@agliodbs.com) wrote: >>> Yeah, I can't believe I'm calling for *yet another* configuration >>> variable either. Suggested workaround fixes very welcome. > >> As I suggested on IRC, my thought would be to have a goal-based system >> for autovacuum which is similar to our goal-based commit system. We >> don't need autovacuum sucking up all the I/O in the box, nor should we >> ask the users to manage that. Instead, let's decide when the autovacuum >> on a given table needs to finish and then plan to keep on working at a >> rate that'll allow us to get done well in advance of that deadline. > > If we allow individual vacuum operations to stretch out just because > they don't need to be completed right away, we will need more concurrent > vacuum workers (so that we can respond to vacuum requirements for other > tables). So I submit that this would only move the problem around: > the number of active workers would increase to the point where things > are just as painful, plus or minus a bit. > > The intent of the autovacuum cost delay features is to ensure that > autovacuum doesn't suck an untenable fraction of the machine's I/O > capacity, even when it's running flat out. So I think Josh's complaint > indicates that we have a problem with cost-delay tuning; hard to tell > what exactly without more info. It might only be that the defaults > are bad for these particular users, or it could be more involved. I've certainly come across many reports of the cost delay settings being difficult to tune, both on pgsql-hackers/performance and in various private EnterpriseDB correspondence. I think Stephen's got it exactly right: the system needs to figure out the rate at which vacuum needs to happen, not rely on the user to provide that information. For checkpoints, we estimated the percentage of the checkpoint that ought to be completed and the percentage that actually is completed; if the latter is less than the former, we speed things up until we're back on track. For autovacuum, the trick is to speed things up when the rate at which tables are coming due for autovacuum exceeds the rate at which we are vacuuming them; or, when we anticipate that a whole bunch of wraparound vacuums are going to come due simultaneously, to start doing them sooner so that they are more spread out. For example, suppose that 26 tables each of which is 4GB in size are going to simultaneously come due for an anti-wraparound vacuum in 26 hours. For the sake of simplicity suppose that each will take 1 hour to vacuum. What we currently do is wait for 26 hours and then start vacuuming them all at top speed, thrashing the I/O system. What we ought to do is start vacuuming them much sooner and do them consecutively. Of course, the trick is to design a mechanism that does something intelligent if we think we're on track and then all of a sudden the rate of XID consumption changes dramatically, and now we've got vacuum faster or with more workers. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> For example, suppose that 26 tables each of which is 4GB in size are
> going to simultaneously come due for an anti-wraparound vacuum in 26
> hours. For the sake of simplicity suppose that each will take 1 hour
> to vacuum. What we currently do is wait for 26 hours and then start
> vacuuming them all at top speed, thrashing the I/O system.
This is a nice description of a problem that has nothing to do with
reality. In the first place, we don't vacuum them all at once; we can
only vacuum max_workers of them at a time. In the second place, the
cost-delay features ought to be keeping autovacuum from thrashing the
I/O, entirely independently of what the reason was for starting the
vacuums. Clearly, since people are complaining, there's something that
needs work there. But not the work you're proposing.
regards, tom lane
On Thu, Jun 28, 2012 at 12:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> For example, suppose that 26 tables each of which is 4GB in size are >> going to simultaneously come due for an anti-wraparound vacuum in 26 >> hours. For the sake of simplicity suppose that each will take 1 hour >> to vacuum. What we currently do is wait for 26 hours and then start >> vacuuming them all at top speed, thrashing the I/O system. > > This is a nice description of a problem that has nothing to do with > reality. In the first place, we don't vacuum them all at once; we can > only vacuum max_workers of them at a time. In the second place, the > cost-delay features ought to be keeping autovacuum from thrashing the > I/O, entirely independently of what the reason was for starting the > vacuums. I don't think it works that way. The point is that the workload imposed by autovac is intermittent and spikey. If you configure the cost limit too low, or the delay too high, or the number of autovac workers is too low, then autovac can't keep up, which causes all of your tables to bloat and is a total disaster. You have to make sure that isn't going to happen, so you naturally configure the settings aggressively enough that you're sure autovac will be able to stay ahead of your bloat problem. But then autovac is more resource-intensive ALL the time, not just when there's a real need for it. This is like giving a kid a $20 bill to buy lunch and having them walk around until they find a restaurant sufficiently expensive that lunch there costs $20. The point of handing over $20 was that you were willing to spend that much *if needed*, not that the money was burning a hole in your pocket. To make that more concrete, suppose that a table has an update rate such that it hits the autovac threshold every 10 minutes. If you set the autovac settings such that an autovacuum of that table takes 9 minutes to complete, you are hosed: there will eventually be some 10-minute period where the update rate is ten times the typical amount, and the table will gradually become horribly bloated. But if you set the autovac settings such that an autovacuum of the table can finish in 1 minute, so that you can cope with a spike, then whenever there isn't a spike you are processing the table ten times faster than necessary, and now one minute out of every ten carries a heavier I/O load than the other 9, leading to uneven response times. It's just ridiculous to assert that it doesn't matter if all the anti-wraparound vacuums start simultaneously. It does matter. For one thing, once every single autovacuum worker is pinned down doing an anti-wraparound vacuum of some table, then a table that needs an ordinary vacuum may have to wait quite some time before a worker is available. Depending on the order in which workers iterate through the tables, you could end up finishing all of the anti-wraparound vacuums before doing any of the regular vacuums. If the wraparound vacuums had been properly spread out, then there would at all times have been workers available for regular vacuums as needed. For another thing, you can't possibly think that three or five workers running simultaneously, each reading a different table, is just as efficient as having one worker grind through them consecutively. Parallelism is not free, ever, and particularly not here, where it has the potential to yank the disk head around between five different files, seeking like crazy, instead of a nice sequential I/O pattern on each file in turn. Josh wouldn't keep complaining about this if it didn't suck. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> It's just ridiculous to assert that it doesn't matter if all the
> anti-wraparound vacuums start simultaneously. It does matter. For
> one thing, once every single autovacuum worker is pinned down doing an
> anti-wraparound vacuum of some table, then a table that needs an
> ordinary vacuum may have to wait quite some time before a worker is
> available.
Well, that's a fair point, but I don't think it has anything to do with
Josh's complaint --- which AFAICT is about imposed load, not about
failure to vacuum things that need vacuumed. Any scheme you care to
design will sometimes be running max_workers workers at once, and if
that's too much load there will be trouble. I grant that there can be
value in a more complex strategy for when to schedule vacuuming
activities, but I don't think that it has a lot to do with solving the
present complaint.
> Parallelism is not free, ever, and particularly not here, where it has
> the potential to yank the disk head around between five different
> files, seeking like crazy, instead of a nice sequential I/O pattern on
> each file in turn.
Interesting point. Maybe what's going on here is that
autovac_balance_cost() is wrong to suppose that N workers can each have
1/N of the I/O bandwidth that we'd consider okay for a single worker to
eat. Maybe extra seek costs mean we have to derate that curve by some
large factor. 1/(N^2), perhaps? I bet the nature of the disk subsystem
affects this a lot, though.
regards, tom lane
On Wed, Jun 27, 2012 at 7:00 PM, Josh Berkus <josh@agliodbs.com> wrote: > I've seen this at two sites now, and my conclusion is that a single > autovacuum_max_workers isn't sufficient if to cover the case of > wraparound vacuum. Nor can we just single-thread the wraparound vacuum > (i.e. just one worker) since that would hurt users who have thousands of > small tables. I have also witnessed very unfortunate un-smooth performance behavior around wraparound time. It seems like a bit of adaptive response in terms of allowed autovacuum throughput to number of pages requiring wraparound vacuuming would be one load off my mind. Getting slower and slower gradually with some way to know that autovacuum has decided it should work harder and harder is better than the brick wall that can sneak up currently. Count me as appreciative for improvements in this area. -- fdr
On Thu, Jun 28, 2012 at 2:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> It's just ridiculous to assert that it doesn't matter if all the >> anti-wraparound vacuums start simultaneously. It does matter. For >> one thing, once every single autovacuum worker is pinned down doing an >> anti-wraparound vacuum of some table, then a table that needs an >> ordinary vacuum may have to wait quite some time before a worker is >> available. > > Well, that's a fair point, but I don't think it has anything to do with > Josh's complaint --- which AFAICT is about imposed load, not about > failure to vacuum things that need vacuumed. Any scheme you care to > design will sometimes be running max_workers workers at once, and if > that's too much load there will be trouble. I grant that there can be > value in a more complex strategy for when to schedule vacuuming > activities, but I don't think that it has a lot to do with solving the > present complaint. I think it's got everything to do with it. Josh could fix his problem by increasing the cost limit and/or reducing the cost delay, but if he did that then his database would get bloated... >> Parallelism is not free, ever, and particularly not here, where it has >> the potential to yank the disk head around between five different >> files, seeking like crazy, instead of a nice sequential I/O pattern on >> each file in turn. > > Interesting point. Maybe what's going on here is that > autovac_balance_cost() is wrong to suppose that N workers can each have > 1/N of the I/O bandwidth that we'd consider okay for a single worker to > eat. Maybe extra seek costs mean we have to derate that curve by some > large factor. 1/(N^2), perhaps? I bet the nature of the disk subsystem > affects this a lot, though. ...and this would have the same effect. Let's not assume that the problem is that Josh doesn't know how to make autovacuum less aggressive, because I'm pretty sure that ain't the issue. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> >> Parallelism is not free, ever, and particularly not here, where it has > >> the potential to yank the disk head around between five different > >> files, seeking like crazy, instead of a nice sequential I/O pattern on > >> each file in turn. > > > > Interesting point. Maybe what's going on here is that > > autovac_balance_cost() is wrong to suppose that N workers can each have > > 1/N of the I/O bandwidth that we'd consider okay for a single worker to > > eat. Maybe extra seek costs mean we have to derate that curve by some > > large factor. 1/(N^2), perhaps? I bet the nature of the disk subsystem > > affects this a lot, though. > > ...and this would have the same effect. Let's not assume that the > problem is that Josh doesn't know how to make autovacuum less > aggressive, because I'm pretty sure that ain't the issue. we may need reserved workers to work on system tables, at least. Just as a protection in case all workers all locked hours walking 'log' tables. In the meantime, the pg_type table can bloat a lot for ex. It might be that limiting the number of workers in 'antiwraparound-mode' to (max_workers - round(max_workers/3)) is enough. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jun 28, 2012 at 2:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, that's a fair point, but I don't think it has anything to do with
>> Josh's complaint --- which AFAICT is about imposed load, not about
>> failure to vacuum things that need vacuumed.
> I think it's got everything to do with it. Josh could fix his problem
> by increasing the cost limit and/or reducing the cost delay, but if he
> did that then his database would get bloated...
Josh hasn't actually explained what his problem is, nor what if any
adjustments he made to try to ameliorate it. In the absence of data
I refuse to rule out misconfiguration. But, again, to the extent that
he's given us any info at all, it seemed to be a complaint about
oversaturated I/O at max load, *not* about inability to complete
vacuuming tasks as needed. You are inventing problem details to fit
your solution.
regards, tom lane
On Thu, Jun 28, 2012 at 9:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > You are inventing problem details to fit > your solution. Well, what I'm actually doing is assuming that Josh's customers have the same problem that our customers do. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert, Tom, Stephen, So, first, a description of the specific problem I've encountered at two sites. I'm working on another email suggesting workarounds and solutions, but that's going to take a bit longer. Observation ----------- This problem occured on two database systems which shared the following characteristics: 1) They were running with default autovacuum & vacuum settings, except that one database had 5 workers instead of 3. 2) They have large partitioned tables, in which the partitions are time-based and do not receive UPDATES after a certain date. Each partition was larger than RAM. 3) The databases are old enough, and busy enough, to have been through XID wraparound at least a couple of times. Users reported that the database system became unresponsive, which was surprising since both of these DBs had been placed on hardware which was engineered for at least 100% growth over the current database size. On investigation, we discovered the following things: a) Each database had autovacuum_max_workers (one DB 5, one DB 3) doing anti-wraparound vacuum on several partitions simultaneously. b) The I/O created by the anti-wraparound vacuum was tying up the system. c) terminating any individual autovacuum process didn't help, as it simply caused autovac to start on a different partition. So, first question was: why was autovacuum wanting to anti-wrapround vacuum dozens of tables at the same time? A quick check showed that all of these partitions had nearly identical XID ages (as in less than 100,000 transactions apart), which all had exceeded autovacuum_max_freeze_age. How did this happen? I'm still not sure. One thought is: this is an artifact of the *previous* wraparound vacuums on each database. On cold partitions with old dead rows which have been through wraparound vacuum several times, this tends to result in the cold partitions converging towards having the same relfrozenxid over time; I'm still working on the math to prove this. Alternately, it's possible that a schema change to the partitioned tables gave them all the same effective relfrozenxid at some point in the past; both databases are still in development. So there are two parts to this problem, each of which needs a different solution: 1. Databases can inadvertently get to the state where many tables need wraparound vacuuming at exactly the same time, especially if they have many "cold" data partition tables. 2. When we do hit wraparound thresholds for multiple tables, autovacuum has no hesitation about doing autovacuum_max_workers worth of wraparound vacuum simultaneously, even when that exceeds the I/O capactity of the system. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Excerpts from Josh Berkus's message of jue jun 28 15:03:15 -0400 2012: > 2) They have large partitioned tables, in which the partitions are > time-based and do not receive UPDATES after a certain date. Each > partition was larger than RAM. I think the solution to this problem has nothing to do with vacuum or autovacuum settings, and lots to do with cataloguing enough info about each of these tables to note that, past a certain point, they don't need any vacuuming at all. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, Jun 28, 2012 at 3:03 PM, Josh Berkus <josh@agliodbs.com> wrote: > 1. Databases can inadvertently get to the state where many tables need > wraparound vacuuming at exactly the same time, especially if they have > many "cold" data partition tables. This suggests that this should be handled rather earlier, and with some attempt to not do them all simultaneously. In effect, if there are 25 tables that will need wraparound vacuums in the next million transactions, it is presumably beneficial to start hitting on them right away, ideally one at a time, so as to draw their future needs further apart. The loose thought is that any time autovac isn't very busy, it should consider (perhaps based on probability?) picking a table that is in a cluster of tables that currently have wraparound needs at about the same time, and, in effect, spread that cluster out. I suppose there are two considerations, that conflict somewhat: a) If there are tables that Absolutely Require wraparound vacuuming, Right Soon Now, there's nothing to help this. They MUST be vacuumed, otherwise the system will get very unhappy. b) It's undesirable to *worsen* things by 'clustering' future wraparound vacuums together, which gets induced any time autovac is continually vacuuming a series of tables. If 25 tables get vacuumed right around now, then that may cluster their next wraparound vacuum to 2^31 transactions from 'right around now.' But there's no helping a). I suppose this suggests having an autovac thread that is 'devoted' to spreading out future wraparound vacuums. - If a *lot* of tables were just vacuumed recently, then it shouldn't do anything, as Right Now is a cluster of 'badness.' - It should group tables by slicing their next wraparounds (grouping by rounding wraparound txid to the nearest, say, 10M or 20M), and consider vacuuming a table Right Now that would take that table out of the worst such "slice" Thus, supposing the grouping is like: | TxId - nearest 10 million | Tables Wrapping In Range | |---------------------------+--------------------------| | 0 | 250 | | 1 | 80 | | 2 | 72 | | 3 | 30 | | 4 | 21 | | 5 | 35 | | 6 | 9 | | 7 | 15 | | 8 | 8 | | 9 | 7 | | 10 | 22 | | 11 | 35 | | 12 | 14 | | 13 | 135 | | 14 | 120 | | 15 | 89 | | 16 | 35 | | 17 | 45 | | 18 | 60 | | 19 | 25 | | 20 | 15 | | 21 | 150 | Suppose current txid is 7500000, and the reason for there to be 250 tables in the current range is that there are a bunch of tables that get *continually* vacuumed. No need to worry about that range, and I'll presume that these are all in the past. In this example, it's crucial to, pretty soon, vacuum the 150 tables in partition #21, as they're getting near wraparound. Nothing to be improved on there. Though it would be kind of nice to start on the 150 as early as possible, so that we *might* avoid having them dominate autovac, as in Josh Berkus' example. But once those are done, the next "crucial" set, in partition #20, are a much smaller set of tables. It would be nice, at that point, to add in a few tables from partitions #13 and #14, to smooth out the burden. The ideal "steady state" would look like the following: | TxId - nearest 10 million | Tables Wrapping In Range | |---------------------------+--------------------------| | 0 | 250 | | 1 | 51 | | 2 | 51 | | 3 | 51 | | 4 | 51 | | 5 | 51 | | 6 | 51 | | 7 | 51 | | 8 | 51 | | 9 | 51 | | 10 | 51 | | 11 | 51 | | 12 | 51 | | 13 | 51 | | 14 | 51 | | 15 | 51 | | 16 | 51 | | 17 | 51 | | 18 | 51 | | 19 | 51 | | 20 | 51 | | 21 | 51 | We might not get something totally smooth, but getting rid of the *really* chunky ranges would be good. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
Josh Berkus <josh@agliodbs.com> writes:
> So there are two parts to this problem, each of which needs a different
> solution:
> 1. Databases can inadvertently get to the state where many tables need
> wraparound vacuuming at exactly the same time, especially if they have
> many "cold" data partition tables.
I'm not especially sold on your theory that there's some behavior that
forces such convergence, but it's certainly plausible that there was,
say, a schema alteration applied to all of those partitions at about the
same time. In any case, as Robert has been saying, it seems like it
would be smart to try to get autovacuum to spread out the
anti-wraparound work a bit better when it's faced with a lot of tables
with similar relfrozenxid values.
> 2. When we do hit wraparound thresholds for multiple tables, autovacuum
> has no hesitation about doing autovacuum_max_workers worth of wraparound
> vacuum simultaneously, even when that exceeds the I/O capactity of the
> system.
I continue to maintain that this problem is unrelated to wraparound as
such, and that thinking it is is a great way to design a bad solution.
There are any number of reasons why autovacuum might need to run
max_workers at once. What we need to look at is making sure that they
don't run the system into the ground when that happens.
Since your users weren't complaining about performance with one or two
autovac workers running (were they?), we can assume that the cost-delay
settings were such as to not create a problem in that scenario. So it
seems to me that it's down to autovac_balance_cost(). Either there's
a plain-vanilla bug in there, or seek costs are breaking the assumption
that it's okay to give N workers each 1/Nth of the single-worker I/O
capacity.
As far as bugs are concerned, I wonder if the premise of the calculation
* The idea here is that we ration out I/O equally. The amount of I/O * that a worker can consume is determined
bycost_limit/cost_delay, so we * try to equalize those ratios rather than the raw limit settings.
might be wrong in itself? The ratio idea seems plausible but ...
regards, tom lane
> I'm not especially sold on your theory that there's some behavior that > forces such convergence, but it's certainly plausible that there was, > say, a schema alteration applied to all of those partitions at about the > same time. In any case, as Robert has been saying, it seems like it > would be smart to try to get autovacuum to spread out the > anti-wraparound work a bit better when it's faced with a lot of tables > with similar relfrozenxid values. Well, I think we can go even further than that. I think one of the fundamental problems is that our "opportunistic" vacuum XID approach is essentially broken for any table which doesn't receive continuous update/deletes (I think Chris Browne makes largely the same point). They way opportunism currently works is via vacuum_freeze_table_age, which says "if you were going to vacuum this table *anyway*, and it's relfrozenxid is # old, then full-scan it". That works fine for tables getting constant UPDATEs to avoid hitting the wraparound deadline, but tables which have stopped getting activity, or are insert-only, never get it. What we should have instead is some opportunism in autovacuum which says: "If I have otherwise idle workers, and the system isn't too busy*, find the table with the oldest relfrozenxid which is over autovacuum_max_freeze_age/2 and vacuum-full-scan it." The key difficulty there is "if the system isn't too busy". That's a hard thing to determine, and subject to frequent change. An opportunistic solution would still be useful without that requirement, but not as helpful. I don't find Stephen's proposal of goal-based solutions to be practical.A goal-based approach makes the assumption that databaseactivity is predictable, and IME most databases are anything but. A second obstacle to "opportunistic wraparound vacuum" is that wraparound vacuum is not interruptable. If you have to kill it off and do something else for a couple hours, it can't pick up where it left off; it needs to scan the whole table from the beginning again. > I continue to maintain that this problem is unrelated to wraparound as > such, and that thinking it is is a great way to design a bad solution. > There are any number of reasons why autovacuum might need to run > max_workers at once. What we need to look at is making sure that they > don't run the system into the ground when that happens. 100% agree. > Since your users weren't complaining about performance with one or two > autovac workers running (were they?), No, it's when we hit 3 that it fell over. Thresholds vary with memory and table size, of course. BTW, the primary reason I think (based on a glance at system stats) this drove the system to its knees was that the simultaneous wraparound vacuum of 3 old-cold tables evicted all of the "current" data out of the FS cache, forcing user queries which would normally hit the FS cache onto disk. I/O throughput was NOT at 100% capacity. During busy periods, a single wraparound vacuum wasn't enough to clear the FS cache because it's competing on equal terms with user access to data. But three avworkers "ganged up" on the user queries and kicked the tar out of them. Unfortunately, for the 5-worker system, I didn't find out about the issue until after it was over, and I know it was related to wraparound only because we were logging autovacuum. So I don't know if it had the same case. There are also problems with our defaults and measurements for the various vacuum_freeze settings, but changing those won't really fix the underlying problem, so it's not worth fiddling with them. The other solution, as mentioned last year, is to come up with a way in which old-cold data doesn't need to be vacuumed *at all*. This would be the ideal solution, but it's not clear how to implement it, since any wraparound-counting solution would bloat the CLOG intolerably. > we can assume that the cost-delay > settings were such as to not create a problem in that scenario. So it > seems to me that it's down to autovac_balance_cost(). Either there's > a plain-vanilla bug in there, or seek costs are breaking the assumption > that it's okay to give N workers each 1/Nth of the single-worker I/O > capacity. Yeah, I think our I/O balancing approach was too simplistic to deal with situations like this one. Factors I think break it are: * modifying cost-limit/cost-delay doesn't translate exactly into 1:1 modifying I/O (in fact, it seems higly unlikely that it does) * seek costs, as you mention * FS cache issues and competition with user queries (per above) > As far as bugs are concerned, I wonder if the premise of the calculation > > * The idea here is that we ration out I/O equally. The amount of I/O > * that a worker can consume is determined by cost_limit/cost_delay, so we > * try to equalize those ratios rather than the raw limit settings. > > might be wrong in itself? The ratio idea seems plausible but ... Well, I think it's "plausible but wrong under at least some common circumstances". In addition to seeking, it ignores FS cache effects (not that I have any idea how to account for these mathematically). It also makes the assumption that 3 autovacuum workers running at 1/3 speed each is better than having one worker running at full speed, which is debatable. And it makes the assumption that the main thing autovac needs to share I/O with is itself ... instead of with user queries. I'm not saying I have a formula which is better, or that we should junk that logic and go back to not allocating at all. But we should see if we can figure out something better. Lemme think about it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
* Josh Berkus (josh@agliodbs.com) wrote:
> I don't find Stephen's proposal of goal-based solutions to be practical.
> A goal-based approach makes the assumption that database activity is
> predictable, and IME most databases are anything but.
We're talking about over the entire transaction space, and we can be
pretty liberal, in my view, with our estimates. If we get it right, we
might risk doing more autovac's for wraparound than strictly necessary,
but they should happen over a sufficient time that it doesn't cause
performance issues.
One definite problem with this, of course, is that the wraparound
autovac can't be stopped and restarted, and anything that increases the
amount of wall-clock time required to complete the autovac will
necessairly increase the risk that we'll lose a bunch of work due to a
database restart.
Thanks,
Stephen
Josh Berkus <josh@agliodbs.com> writes:
> Well, I think it's "plausible but wrong under at least some common
> circumstances". In addition to seeking, it ignores FS cache effects
> (not that I have any idea how to account for these mathematically). It
> also makes the assumption that 3 autovacuum workers running at 1/3 speed
> each is better than having one worker running at full speed, which is
> debatable.
Well, no, not really, because the original implementation with only one
worker was pretty untenable. But maybe we need some concept like only
one worker working on *big* tables? Or at least, less than max_workers
of them.
regards, tom lane
Le vendredi 29 juin 2012 04:26:42, Tom Lane a écrit : > Josh Berkus <josh@agliodbs.com> writes: > > Well, I think it's "plausible but wrong under at least some common > > circumstances". In addition to seeking, it ignores FS cache effects > > (not that I have any idea how to account for these mathematically). It > > also makes the assumption that 3 autovacuum workers running at 1/3 speed > > each is better than having one worker running at full speed, which is > > debatable. > > Well, no, not really, because the original implementation with only one > worker was pretty untenable. But maybe we need some concept like only > one worker working on *big* tables? Or at least, less than max_workers > of them. I think it is easier to manage to keep some workers available to work on other task instead of having all of them doing the same longest job. pgfincore allows since years to snapshot and restore the OS cache to work around such issues. Autovacuum should snapshot the xMB ahead and restore the previous state cache when done. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
On Thu, Jun 28, 2012 at 6:57 PM, Josh Berkus <josh@agliodbs.com> wrote: > > A second obstacle to "opportunistic wraparound vacuum" is that > wraparound vacuum is not interruptable. If you have to kill it off and > do something else for a couple hours, it can't pick up where it left > off; it needs to scan the whole table from the beginning again. Would recording a different relfrozenxid for each 1GB chunk of the relation solve that? >> Since your users weren't complaining about performance with one or two >> autovac workers running (were they?), > > No, it's when we hit 3 that it fell over. Thresholds vary with memory > and table size, of course. Does that mean it worked fine with 2 workers simultaneously in large tables, or did that situation not occur and so it is not known whether it would have worked fine or not? > BTW, the primary reason I think (based on a glance at system stats) this > drove the system to its knees was that the simultaneous wraparound > vacuum of 3 old-cold tables evicted all of the "current" data out of the > FS cache, forcing user queries which would normally hit the FS cache > onto disk. I/O throughput was NOT at 100% capacity. Do you know if it was the input or the output that caused that to happen? I would think the kernel has logic similar to BAS to prevent reading a huge amount of data sequentially from evicting all the other data. But that logic might be defeated if all that data is dirtied right after being read. If the partitions had not been touched since the last freeze, then it should generate no dirty blocks (right?), but if they were touched since then you could basically be writing out the entire table. Cheers, Jeff