Re: Synchronization levels in SR

On 26/05/10 18:31, Robert Haas wrote:
> And frankly, I don't think it's possible for quorum commit to reduce
> the number of parameters.  Even if we have that feature available, not
> everyone will want to use it.  And the people who don't will
> presumably need whatever parameters they would have needed if quorum
> commit hadn't been available in the first place.

Agreed, quorum commit is not a panacea.

For example, suppose that you have two servers, master and a standby, 
and you want transactions to be synchronously committed to both, so that 
in the event of a meteor striking the master, you don't lose any 
transactions that have been replied to the client as committed.

Now you want to set up a temporary replica of the master at a 
development server, for testing purposes. If you set quorum to 2, your 
development server becomes critical infrastructure, which is not what 
you want. If you set quorum to 1, it also becomes critical 
infrastructure, because it's possible that a transaction has been 
replicated to the test server but not the real production standby, and a 
meteor strikes.

Per-standby settings would let you express that, but not OTOH the quorum 
behavior where you require N out of M to acknowledge the commit before 
returning to client.

There's really no limit to how complex a setup can be. For example, 
imagine that you have two data centers, with two servers in each. You 
want to replicate the master to all four servers, but for commit to 
return to the client, it's enough that the transaction has been 
replicated to one server in each data center. How do you express that in 
the config file? And it would be nice to have per-transaction control 
too, like with synchronous_commit...

So this is a tradeoff between
* flexibility, how complex a setup you can express?
* code complexity, how complicated is it to implement?
* user-friendliness, how easy is it to configure?

One way out of this is to implement something very simple in PostgreSQL, 
and build external WAL proxying tools in pgfoundry that allow you to 
cascade and disseminate the WAL in as complex scenarios as you want.

>> Your reply has again avoided the subject of how we would handle failure
>> modes with per-standby settings. That is important.
>
> I don't think anyone is avoiding that, we just haven't discussed it.
> The thing is, I don't think quorum commit actually does anything to
> address that problem.  If I have a master and a standby configured for
> sync rep and the standby goes down, we have to decide what impact that
> has on the master.  If I have a master and two standbys configured for
> sync rep with quorum commit such that I only need an ack from one of
> them, and they both go down, we still have to decide what impact that
> has on the master.  I agree we need to talk about, but I don't agree
> that putting in quorum commit will remove the need to design that
> case.

Right, failure modes need to be discussed, but how quorum commit or 
whatnot is configured is irrelevant to that.

No-one has come up with a scheme on how to abort a transaction if you 
don't get a reply from a synchronous standby (or all standbys or a 
quorum of standbys). Until someone does, a commit on the master will 
have to always succeed. The "synchronous" aspect will provide a 
guarantee that if a standby is connected, any transaction in the master 
will become visible (or fsync'd or just streamed to, depending on the 
level) on the standby too before it's acknowledged as committed to the 
client, nothing more, nothing less.

One way to do that would be to refrain from flushing the commit record 
to disk on the master until the standby has acknowledged it. The 
downside is that the master is in a very severe state at that point: 
until you flush the WAL, you can buffer only a small amount WAL traffic 
until you run out of wal_buffers, stalling all write activity in the 
master, with backends waiting. You can't even shut down the server 
cleanly. But if you value your transaction integrity much higher than 
availability, maybe that's what you want.

PS. I whole-heartedly agree with Simon's concern upthread that if we 
allow a standby to specify in its config file that it wants to be a 
synchronous standby, that's a bit dangerous because connecting such a 
standby to the master will suddenly make all commits on the master a lot 
slower. Adding a synchronous standby should require some action in the 
master, since it affects the behavior on master.

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