Re: Synchronization levels in SR

On Thu, 2010-05-27 at 16:13 +0900, Fujii Masao wrote:
> On Wed, May 26, 2010 at 10:37 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > If the remote server responded first, then that proves it is a better
> > candidate for failover than the one you think of as near. If the two
> > standbys vary over time then you have network problems that will
> > directly affect the performance on the master; synch_rep = N would
> > respond better to any such problems.
>
> No. The remote standby might respond first temporarily though it's almost
> behind the near one. The read-only queries or incrementally updated
> backup operation might cause a bursty disk write, and delay the ACK from
> the standby. The lock contention between read-only queries and recovery
> would delay the ACK. So the standby which responds first is not always
> the best candidate for failover.

Seems strange. If you have 2 standbys and you say you would like node1
to be the preferred candidate, then you load it so heavily that a remote
server with by-definition much larger network delay responds first, then
I say your preference was wrong. The above situation is caused by the
DBA and the DBA can solve it also - if the preference is to keep a
"preferred" server then that server would need to be lightly loaded so
it could respond sensibly.

This is the same thing as having an optimizer pick the best path and
then the user saying "no dumb-ass, use the index I tell you" even though
it is slower. If you really don't want to know the fastest way, then I
personally will agree you can have that, as is my view (now) on the
optimizer issue also - sometimes the admin does know best.

> Also the administrator generally doesn't
> put the remote standby under the control of a clusterware like heartbeat.
> In this case, the remote standby will never be the candidate for failover.
> But quorum commit cannot cover this simple case.

If you, Jan and Yeb wish to completely exclude standbys from being part
of any quorum, then I guess we need to have per-standby settings to
allow that to be defined. I'm in favour of giving people options. That
needn't be a mandatory per-standby setting, just a non-default option,
so that we can reduce the complexity of configuration for common cases.
If we're looking for simplest-implementation-first that isn't it.

Currently, Oracle provides these settings, which correspond to
Maximum Performance => quorum = 0
Maximum Availability => quorum = 1, timeout_action = commit
Maximum Protection => quorum = 1, timeout_action = shutdown

So Oracle already supports the quorum case...

Oracle doesn't provide
i) any capability to have quorum > 1
ii) any capability to include an async node as a sync node, if the
quorum cannot be reached with servers marked "sync", or in the situation
where because of mis-use/mis-configuration the sync servers are actually
slower.
iii) ability to wait for apply
iv) ability to specify wait mode at transaction level

all of those are desirable in some cases and easily possible by
specifying things in the way I've suggested.

--
Simon Riggs www.2ndQuadrant.com