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

Bruce Momjian wrote:
> Joachim Wieland wrote:
>
>> 1) With the current implementation they will see better performance on
>> 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...
>>
>
> I assumed they would set max_standby_delay = -1 and be happy.
>

The admin in this situation might be happy until the first time the
primary fails and a failover is forced, at which point there is an
unbounded amount of recovery data to apply that was stuck waiting behind
whatever long-running queries were active. I don't know if you've ever
watched what happens to a pre-8.2 cold standby when you start it up with
hundreds or thousands of backed up WAL 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 to again. 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.

This is why no admin who prioritizes HA would consider
'max_standby_delay = -1' a reasonable setting, and those are the sort of
users Joachim's example was discussing. Only takes one rogue query that
runs for a long time to make the standby so far behind it's useless for
HA purposes. And you also have to ask yourself "if recovery is halted
while waiting for this query to run, how stale is the data on the
standby getting?". That's true for any large setting for this
parameter, but using -1 for the unlimited setting also gives the maximum
possible potential for such staleness.

'max_standby_delay = -1' is really only a reasonable idea if you are
absolutely certain all queries are going to be short, which we can't
dismiss as an unfounded use case so it has value. I would expect you
have to also combine it with a matching reasonable statement_timeout to
enforce that expectation to make that situation safer.

In any of the "offload batch queries to the failover standby"
situations, it's unlikely an unlimited value for this setting will be
practical. Perhaps you set max_standby_delay to some number of hours,
to match your expected worst-case query run time and reduce the chance
of cancellation. Not putting a limit on it at all is a situation no DBA
with healthy paranoia is going to be happy with the potential downside
of in a HA environment, given that both unbounded staleness and recovery
time are then both possible. The potential of a failed long-running
query is much less risky than either of those.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us