Re: Re: Hot Standby query cancellation and Streaming Replication integration

Josh Berkus wrote:
> Well, we could throw this on the user if we could get them some
> information on how to calculate that number.  For example, some way for
> them to calculate the number of XIDs per minute via a query, and then
> set vacuum_defer_cleanup_age appropriately on the master.  Sure, it's
> clunky, but we've already warned people that 9.0 will be clunky and hard
> to administer.  And it's no worse than setting FSM_pages used to be.
>
> However, first we need to test that setting vacuum_defer_cleanup_age
> actually benefits query cancel issues.
>   

Proving that setting works as expected is already on my test case grid, 
seems fine in my limited testing so far.  I've started looking into ways 
to monitor XID churn in a way for setting it better.  I'll take care of 
providing all that in my next test case update.  My intent here is to 
take the ideas outlined in my "Hot Standby Tradeoffs" blog post and turn 
that into a new documentation section making it more clear where the 
problem steps are, regardless of what else happens here.  And I need 
some concrete example of XID burn rate measurement to finish that job.

The main problem with setting vacuum_defer_cleanup_age high isn't 
showing it works, it's a pretty simple bit of code.  It's when you 
recognize that it penalizes all cleanup all the time, whether or not the 
standby is actually executing a long-running query or not, that you note 
the second level of pain in increasing it.  Returning to the idea of 
"how is this different from a site already in production?", it may very 
well be the case that a site that sets vacuum_defer_cleanup_age high 
enough to support off-peak batch reporting cannot tolerate how that will 
impact vacuums during their peak time of day.  The XID export 
implementation sidesteps that issue by only making the vacuum delay 
increase when queries that require it are running, turning this back 
into a standard "what's the best time of day to run my big reports?" 
issue that people understand how to cope with already.

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