Re: max_standby_delay considered harmful

Robert Haas wrote:
> On Thu, May 6, 2010 at 2:47 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> Now that I've realized what the real problem is with max_standby_delay
>>> (namely, that inactivity on the master can use up the delay), I think
>>> we should do what Tom originally suggested here. It's not as good as
>>> a really working max_standby_delay, but we're not going to have that
>>> for 9.0, and it's clearly better than a boolean.
>> I guess I'm not clear on how what Tom proposed is fundamentally
>> different from max_standby_delay = -1. If there's enough concurrent
>> queries, recovery would never catch up.
>
> If your workload is that the standby server is getting pounded with
> queries like crazy, then it's probably not that different: it will
> fall progressively further behind. But I suspect many people will set
> up standby servers where most of the activity happens on the primary,
> but they run some reporting queries on the standby. If you expect
> your reporting queries to finish in <10s, you could set the max delay
> to say 60s. In the event that something gets wedged, recovery will
> eventually kill it and move on rather than just getting stuck forever.
> If the volume of queries is known not to be too high, it's reasonable
> to expect that a few good whacks will be enough to get things back on
> track.

Yeah, I could live with that.

A problem with using the name "max_standby_delay" for Tom's suggestion
is that it sounds like a hard limit, which it isn't. But if we name it
something like:

# -1 = no timeout
# 0 = kill conflicting queries immediately
# > 0 wait for N seconds, then kill query
standby_conflict_timeout = -1

it's more clear that the setting is a timeout for each *conflict*, and
it's less surprising that the standby can fall indefinitely behind in
the worst case. If we name the setting along those lines, I could live
with that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com