Message-ID: <5053A66A.9040809@agliodbs.com>
Date: Fri, 14 Sep 2012 14:49:30 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:15.0) Gecko/20120824 Thunderbird/15.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Setting autovacuum_vacuum_scale_factor to 0 a good
 idea ?
References: 
 <CAGa5y0Nv==ObEnWr5BbR6mRWPTP21uLwduM2UYs3oxMdy+60sA@mail.gmail.com>
In-Reply-To: 
 <CAGa5y0Nv==ObEnWr5BbR6mRWPTP21uLwduM2UYs3oxMdy+60sA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit


> I am pondering about this... My thinking is that since *_scale_factor need
> to be set manually for largish tables (>1M), why not
> set autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor, and
> increase the value of autovacuum_vacuum_threshold to, say, 10000, and
> autovacuum_analyze_threshold
> to 2500 ? What do you think ?

I really doubt you want to be vacuuming a large table every 10,000 rows.
 Or analyzing every 2500 rows, for that matter.  These things aren't
free, or we'd just do them constantly.

Manipulating the analyze thresholds for a large table make sense; on
tables of over 10m rows, I often lower autovacuum_analyze_scale_factor
to 0.02 or 0.01, to get them analyzed a bit more often.  But vacuuming
them more often makes no sense.

> Also, with systems handling 8k-10k tps and dedicated to a single database,
> would there be any cons to decreasing autovacuum_naptime to say 15s, so
> that the system perf is less spiky ?

You might also want to consider more autovacuum workers.  Although if
you've set the thresholds as above, that's the reason autovacuum is
always busy and not keeping up ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com