Re: Re: Hot Standby query cancellation and Streaming Replication integration - Mailing list pgsql-hackers
From | Greg Smith |
---|---|
Subject | Re: Re: Hot Standby query cancellation and Streaming Replication integration |
Date | |
Msg-id | 4B8CA710.8020601@2ndquadrant.com Whole thread Raw |
In response to | Re: Re: Hot Standby query cancellation and Streaming Replication integration (Bruce Momjian <bruce@momjian.us>) |
Responses |
Re: Re: Hot Standby query cancellation and
Streaming Replication integration
|
List | pgsql-hackers |
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>
pgsql-hackers by date: