Message-ID: <4DF0FA36.2010802@2ndquadrant.com>
Date: Thu, 09 Jun 2011 12:52:06 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: "Reuven M. Lerner" <reuven@lerner.co.il>
CC: pgsql-performance@postgresql.org
Subject: Re: Triggering autovacuum
References: <4DF0E5A0.7080101@lerner.co.il>
In-Reply-To: <4DF0E5A0.7080101@lerner.co.il>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Reuven M. Lerner wrote:
> Could this be because we're only doing INSERTs and SELECTs?  In such a 
> case, then we would never reach the threshold of modified tuples that 
> autovacuum looks for, and thus it would never run.  That would, by my 
> reasoning, mean that we'll never tag dead tuples (which isn't a big 
> deal if we're never deleting or updating rows), but also that we'll 
> never run ANALYZE as part of autovacuum.  Which would mean that we'd 
> be running with out-of-date statistics.

The computation for whether the auto-analyze portion of autovacuum runs 
takes into account INSERT traffic, so the stats don't go too far out of 
data on this style of workload.  The one for the vacuum work only 
considers dead rows.  So your case should be seeing regular entries for 
the last auto-analyze, but possibly not for last auto-vacuum.

Eventually autovacuum will kick in anyway for transaction id wraparound, 
and that might be traumatic when it does happen.  You might want to 
schedule periodic manual vacuum on these tables to at least have that 
happen at a good time.  Wraparound autovacuum has this bad habit of 
finally kicking in only during periods of peak busy on the server.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books