Re: Random performance hit, unknown cause.

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