Re: Random performance hit, unknown cause.

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Brian Fehrle" <brianf(at)consistentstate(dot)com>
Cc: "Claudio Freire" <klaussfreire(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Random performance hit, unknown cause.
Date: 2012-04-12 22:00:13
Message-ID: 4F870A1D0200002500046EA9@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Brian Fehrle <brianf(at)consistentstate(dot)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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Benoit Delbosc 2012-04-12 22:09:23 Slow fulltext query plan
Previous Message Brian Fehrle 2012-04-12 21:31:21 Re: Random performance hit, unknown cause.