Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group