Re: We probably need autovacuum_max_wraparound_workers

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Josh Berkus (josh(at)agliodbs(dot)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