Message-ID: <4BDC60A9.60108@2ndquadrant.com>
Date: Sat, 01 May 2010 13:11:05 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: Josh Berkus <josh@agliodbs.com>, Rick <richard.branton@ca.com>,
	pgsql-performance@postgresql.org
Subject: Re: autovacuum strategy / parameters
References: <c2d2b6cf-895f-4a4b-aa8b-3adaac33c794@z3g2000yqz.googlegroups.com>	
	<y2g603c8f071004221155h3874a3c0ybbe2c790e3c1bf80@mail.gmail.com>	
	<65d9d15d-41d2-4bfa-8cc3-6148b4fb9022@z3g2000yqz.googlegroups.com>	
	<h2o603c8f071004271755v3e56a408hbd23b5c355058aa@mail.gmail.com>	
	<4BDB5ECE.4010607@agliodbs.com>
	<AANLkTik9xSarBYBapF1CVrPoRuqimyGAFAgOaGgvHpLR@mail.gmail.com>
In-Reply-To: <AANLkTik9xSarBYBapF1CVrPoRuqimyGAFAgOaGgvHpLR@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Robert Haas wrote:
> I don't have a stake in the ground on what the right settings are, but
> I think it's fair to say that if you vacuum OR analyze much less
> frequently than what we recommend my default, it might break.
>   

I think the default settings are essentially minimum recommended 
frequencies.  They aren't too terrible for the giant data warehouse case 
Josh was suggesting they came from--waiting until there's 20% worth of 
dead stuff before kicking off an intensive vacuum is OK when vacuum is 
expensive and you're mostly running big queries anyway.  And for smaller 
tables, the threshold helps it kick in a little earlier.  It's unlikely 
anyone wants to *increase* those, so that autovacuum runs even less; out 
of the box it's not tuned to run very often at all.

If anything, I'd expect people to want to increase how often it runs, 
for tables where much less than 20% dead is a problem.  The most common 
situation I've seen where that's the case is when you have a hotspot of 
heavily updated rows in a large table, and this may match some of the 
situations that Robert was alluding to seeing.  Let's say you have a big 
table where 0.5% of the users each update their respective records 
heavily, averaging 30 times each.  That's only going to result in 15% 
dead rows, so no autovacuum.  But latency for those users will suffer 
greatly, because they might have to do lots of seeking around to get 
their little slice of the data.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us