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(at)2ndQuadrant(dot)com www.2ndQuadrant.us