Re: Why do we let autovacuum give up?

On Fri, Jan 24, 2014 at 12:33 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 01/24/2014 11:32 AM, Tom Lane wrote:
>> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>>> Claudio Freire escribió:
>>>> If you ask me, I'd like autovac to know when not to run (or rather
>>>> wait a bit, not forever), perhaps by checking load factors or some
>>>> other tell-tale of an already-saturated I/O system.
>>
>>> We had a proposed design to tell autovac when not to run (or rather,
>>> when to switch settings very high so that in practice it'd never run).
>>> At some point somebody said "but we can just change autovacuum=off in
>>> postgresql.conf via crontab when the high load period starts, and turn
>>> it back on afterwards" --- and that was the end of it.
>>
>> The hard part of this is that shutting down autovacuum during heavy
>> load may be exactly the wrong thing to do.
>
> Yep. In fact, it may be appropriate to limit or stop autovacuum's work
> on some big tables, while pushing its activity even higher for small,
> high churn tables.
>
> If you stop autovacuum on a message-queue system when load gets high,
> you'll get a giant messy bloat explosion.

A message queue has a steady state and needs way more than autovacuum.
A table used as a message queue would need a wholly dedicated
autovacuum worker to be constantly vacuuming. It's certainly an
extreme example.

But normal tables are much bigger than their active set, so vacuuming,
which walks all those cold gigabytes, tends to wreak havoc with I/O
performance. Doing it in peak hours, which is autovacuum's preferred
time, is terrible. Delaying autovacuum for a while doesn't sound like
such a disastruous thing.

In essence, I'm talking about two thresholds. A "vacuum in the
background" threshold, and a "omfg this table is a mess vacuum now now
now" threshold. The background part is quite not straightforward
though. As in, what is background?