Brian Fehrle <brianf@consistentstate.com> wrote:
> In this system I have a table that is extremely active. On a
> 'normal' day, the autovacuum process takes about 7 hours to
> complete on this table, and once it's complete, the system
> performs an autoanalyze on the table, finding that we have
> millions of new dead rows. Once this happens, it kicks off the
> autovacuum again, so we basically always have a vacuum running on
> this table at any given time.
>
> If I were to tweak the autovacuum_vacuum_cost_delay parameter,
> what would that be doing?
That controls how long an autovacuum worker naps after it has done
enough work to hit the autovacuum_cost_limit. As tuning knobs go,
this one is pretty coarse.
> Would it be limiting what the current autovacuum is allowed to do?
No, just how fast it does it.
> Or does it simply space out the time between autovacuum runs?
Not that either; it's part of pacing the work of a run.
> In my case, with 7 hour long autovacuums (sometimes 14 hours), a
> few milliseconds between each vacuum wouldn't mean anything to me.
Generally, I find that the best way to tune it is to pick 10ms to
20ms for autovacuum_cost_delay, and adjust adjust
autovacuum_cost_limit to tune from there. A small change in the
former can cause a huge change in pacing; the latter is better for
fine-tuning.
> It may cause the system to perform better at that time, but would
> prolong the length of the autovacuum right?
Right.
-Kevin