Re: On-the-fly index tuple deletion vs. hot_standby - Mailing list pgsql-hackers

On Fri, Apr 22, 2011 at 11:10:34AM -0400, Noah Misch wrote:
> On Tue, Mar 15, 2011 at 10:22:59PM -0400, Noah Misch wrote:
> > On Mon, Mar 14, 2011 at 01:56:22PM +0200, Heikki Linnakangas wrote:
> > > On 12.03.2011 12:40, Noah Misch wrote:
> > >> The installation that inspired my original report recently upgraded from 9.0.1
> > >> to 9.0.3, and your fix did significantly decrease its conflict frequency.  The
> > >> last several conflicts I have captured involve XLOG_BTREE_REUSE_PAGE records.
> > >> (FWIW, the index has generally been pg_attribute_relid_attnam_index.)  I've
> > >> attached a test script demonstrating the behavior.  _bt_page_recyclable approves
> > >> any page deleted no more recently than RecentXmin, because we need only ensure
> > >> that every ongoing scan has witnessed the page as dead.  For the hot standby
> > >> case, we need to account for possibly-ongoing standby transactions.  Using
> > >> RecentGlobalXmin covers that, albeit with some pessimism: we really only need
> > >> LEAST(RecentXmin, PGPROC->xmin of walsender_1, .., PGPROC->xmin of walsender_N)
> > >> - vacuum_defer_cleanup_age.  Not sure the accounting to achieve that would pay
> > >> off, though.  Thoughts?
> > >
> > > Hmm, instead of bloating the master, I wonder if we could detect more  
> > > accurately if there are any on-going scans, in the standby. For example,  
> > > you must hold a lock on the index to scan it, so only transactions  
> > > holding the lock need to be checked for conflict.
> > 
> > That would be nice.  Do you have an outline of an implementation in mind?
> 
> In an attempt to resuscitate this thread, here's my own shot at that.  Apologies
> in advance if it's just an already-burning straw man.
[full proposal at http://archives.postgresql.org/message-id/20110422151034.GA8150@tornado.gateway.2wire.net]

Anyone care to comment?  On this system, which has vacuum_defer_cleanup_age set
to 3 peak hours worth of xid consumption, the problem caps recovery conflict
hold off at 10-20 minutes.  It will have the same effect on standby feedback in
9.1.  I think we should start by using RecentGlobalXmin instead of RecentXmin as
the reuse horizon when wal_level = hot_standby, and backpatch that.  Then,
independently consider for master a bloat-avoidance improvement like I outlined
most recently; I'm not sure whether that's worth it.  In any event, I'm hoping
to get some consensus on the way forward.

Thanks,
nm