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

Bruce Momjian wrote: <blockquote cite="mid:201003020454.o224s4601113@momjian.us" type="cite"><pre wrap="">Joachim
Wielandwrote: </pre><blockquote type="cite"><pre wrap="">1) With the current implementation they will see better
performanceon
 
the master and more aggressive vacuum (!), since they have less
long-running queries now on the master and autovacuum can kick in and
clean up with less delay than before. On the other hand their queries
on the standby might fail and they will start thinking that this HS+SR
feature is not as convincing as they thought it was...   </pre></blockquote><pre wrap="">
I assumed they would set max_standby_delay = -1 and be happy. </pre></blockquote><br /> The admin in this situation
mightbe happy until the first time the primary fails and a failover is forced, at which point there is an unbounded
amountof recovery data to apply that was stuck waiting behind whatever long-running queries were active.  I don't know
ifyou've ever watched what happens to a pre-8.2 cold standby when you start it up with hundreds or thousands of backed
upWAL files to process before the server can start, but it's not a fast process.  I watched a production 8.1 standby
get>4000 files behind once due to an archive_command bug, and it's not something I'd like to ever chew my nails off
toagain.  If your goal was HA and you're trying to bring up the standby, the server is down the whole time that's going
on.<br/><br /> This is why no admin who prioritizes HA would consider 'max_standby_delay = -1' a reasonable setting,
andthose are the sort of users Joachim's example was discussing.  Only takes one rogue query that runs for a long time
tomake the standby so far behind it's useless for HA purposes.  And you also have to ask yourself "if recovery is
haltedwhile waiting for this query to run, how stale is the data on the standby getting?".  That's true for any large
settingfor this parameter, but using -1 for the unlimited setting also gives the maximum possible potential for such
staleness.<br/><br /> 'max_standby_delay = -1' is really only a reasonable idea if you are absolutely certain all
queriesare going to be short, which we can't dismiss as an unfounded use case so it has value.  I would expect you have
toalso combine it with a matching reasonable statement_timeout to enforce that expectation to make that situation
safer.<br/><br /> In any of the "offload batch queries to the failover standby" situations, it's unlikely an unlimited
valuefor this setting will be practical.  Perhaps you set max_standby_delay to some number of hours, to match your
expectedworst-case query run time and reduce the chance of cancellation.  Not putting a limit on it at all is a
situationno DBA with healthy paranoia is going to be happy with the potential downside of in a HA environment, given
thatboth unbounded staleness and recovery time are then both possible.  The potential of a failed long-running query is
muchless risky than either of those.<br /><br /><pre class="moz-signature" cols="72">-- 
 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
<a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>   <a
class="moz-txt-link-abbreviated"href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
 
</pre>