Date: Fri, 6 Sep 2013 20:29:08 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Andres Freund <andres@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>,
 Robert Haas <robertmhaas@gmail.com>,
 Peter Geoghegan <peter@2ndquadrant.com>,
 Tom Lane <tgl@sss.pgh.pa.us>, Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] encouraging index-only scans
Message-ID: <20130907002908.GA12753@momjian.us>
References: <20130906001437.GA29264@momjian.us>
 <CA+Tgmobh_-n-GvKX++DVctToZ9SZkdhvszP=CBjh9XZOvWsTEg@mail.gmail.com>
 <20130906020043.GH27195@momjian.us>
 <20130906052236.GF6067@eldon.alvh.no-ip.org>
 <20130906130854.GB600952@alap2.anarazel.de>
 <20130906163056.GA13158@momjian.us>
 <20130906163647.GA626072@alap2.anarazel.de>
 <20130906170159.GB13158@momjian.us>
 <20130906191330.GA13193@momjian.us>
 <20130906222623.GC626072@alap2.anarazel.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20130906222623.GC626072@alap2.anarazel.de>
User-Agent: Mutt/1.5.20 (2009-06-14)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Sat, Sep  7, 2013 at 12:26:23AM +0200, Andres Freund wrote:
> > So, what should trigger an auto-vacuum vacuum for these workloads? 
> > Rather than activity, which is what normally drives autovacuum, it is
> > lack of activity that should drive it, combined with a high VM cleared
> > bit percentage.
> > 
> > It seems we can use these statistics values:
> > 
> > 	 n_tup_ins           | bigint                   
> > 	 n_tup_upd           | bigint                   
> > 	 n_tup_del           | bigint                   
> > 	 n_tup_hot_upd       | bigint                   
> > 	 n_live_tup          | bigint                   
> > 	 n_dead_tup          | bigint                   
> > 	 n_mod_since_analyze | bigint                   
> > 	 last_vacuum         | timestamp with time zone 
> > 	 last_autovacuum     | timestamp with time zone 
> > 
> > Particilarly last_vacuum and last_autovacuum can tell us the last time
> > of vacuum.  If the n_tup_upd/n_tup_del counts are low, and the  VM set
> > bit count is low, it might need vacuuming, though inserts into existing
> > pages would complicate that.
> 
> I wonder if we shouldn't trigger most vacuums (not analyze!) via unset
> fsm bits. Perhaps combined with keeping track of RecentGlobalXmin to

Fsm bits?  FSM tracks the free space on each page.  How does that help?

> make sure we're not repeatedly checking for work that cannot yet be
> done.

The idea of using RecentGlobalXmin to see how much _work_ has happened
since the last vacuum is interesting, but it doesn't handle read-only
transactions;  I am not sure how they can be tracked.  You make a good
point that 5 minutes passing is meaningless --- you really want to know
how many transactions have completed.  Unfortunately, our virtual
transactions make that hard to compute.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers