Re: Cancelling of autovacuums considered harmful

On Wed, Feb 26, 2014 at 8:15 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> As some of you folks know I work on some pretty beefy machines. Some
> of these machines don't see rest for months or days on end. And
> autovacuum keeps cancelling. I set it more aggressive, it still never
> finishes.
>
> We've now had to schedule manual vacuums because autovac never
> finishes on large heavily updated tables.
>
> Is there some way to turn off the cancelling of autovacuums?
>

I don't think so, not without recompiling or hacking shared memory.

Perhaps you could set vacuum_freeze_table_age to a ridiculously low value,
such that most vacuums become anti-wraparound ones.

You could have a frequent cron job look in pg_stat_activity for autovacs,
and then automatically fire up a regular vacuum to replace them. But if
they already got cancelled by someone else, you wouldn't notice that.

Are you sure you would want the currently cancelling processes to instead
block for hours or days? If I had regularly scheduled jobs that cancelled
autovac, I'd probably make that job also do a vacuum, that way I could
control it.

Cheers,

Jeff