Thread: Issues with Quorum Commit

Issues with Quorum Commit

From
Josh Berkus
Date:
All,

There's been a lot of discussion on synch rep lately which involves
quorum commit.  I need to raise some major design issues with quorum
commit which I don't think that people have really considered, and may
be sufficient to prevent it from being included in 9.1.

A. Permanent Synchronization Failure
---------------------------------
Quorum commit, like other forms of more-than-one-standby synch rep,
offers the possibility that one or more standbys could end up
irretrievably desyncronized with the master.

1. Quorum is 3 servers (out of 5) with mode "apply"
2. Standbys 2 and 4 receive and apply transaction # 20001.
3. Due to a network issue, no other standby applies #20001.
4. Accordingly, the master rolls back #20001 and cancels, either due to
timeout or DBA cancel.
5. #2 and #5 are now hopelessly out of synch with the master.


B. Eventual Inconsistency
-------------------------
If we have a quorum commit, it's possible for any individual standby to
be indefinitely ahead of any standby which is not needed by the quorum.This means that:

-- There is no clear criteria for when a standby which is not needed for
quorum should be considered no longer a synch standby, and
-- Applications cannot make assumptions that synch rep promises some
specific window of synchronicity, eliminating a lot of the value of
quorum commit.


C. Performance
--------------
Doing quorum commit requires significant extra accounting on the
master's part: it must keep track of how many standbys committed for
each pending transaction (and remember there may be many at the same
time).

Doing so could involve significant response-time overhead added to the
simple case where there is only one standby, as well as memory usage,
and likely a lot of troubleshooting of the mechanism from us.


D. Adding/Replacing Quorum Members
----------------------------------
For Quorum commit to be really valuable, we need to be able to add new
quorum members and remove dead ones *without stopping the master*.  Per
discussion about the startup issues with only one master, we have not
worked out how to do this for synch rep standbys.  It's reasonable to
assume that this will be more complex for a quorum group than with a
single synch standby.

Consider the case, for example, where due to a network outage we have
dropped below quorum.  What is the strategy for getting the system
running again by adding standbys?


All of the problems above are resolvable.  Some of the CAP databases
have probably resolved them, as well as some older telecom databases.
However, all of them will require significant work, and even more
significant debugging, from the project.

I would like to see Quorum Commit, in part because I think it would help
push PostgreSQL further into cloud frameworks.  However, I'm worried
that if we make quorum commit a requirement of synch rep, we will not
have synch rep in 9.1.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 05.10.2010 22:11, Josh Berkus wrote:
> There's been a lot of discussion on synch rep lately which involves
> quorum commit.  I need to raise some major design issues with quorum
> commit which I don't think that people have really considered, and may
> be sufficient to prevent it from being included in 9.1.

Thanks for bringing these up.

> A. Permanent Synchronization Failure
> ---------------------------------
> Quorum commit, like other forms of more-than-one-standby synch rep,
> offers the possibility that one or more standbys could end up
> irretrievably desyncronized with the master.
>
> 1. Quorum is 3 servers (out of 5) with mode "apply"
> 2. Standbys 2 and 4 receive and apply transaction # 20001.
> 3. Due to a network issue, no other standby applies #20001.
> 4. Accordingly, the master rolls back #20001 and cancels, either due to
> timeout or DBA cancel.

The master can not roll back or cancel the transaction. That's 
completely infeasible, the WAL record has been written to local disk 
already. The best it can do is halt and wait for enough standbys to 
appear to fulfill the quorum. The client will hang waiting for the 
COMMIT to finish, and the transaction will appear as in-progress to 
other transactions.

There's subtle point here that I don't think has been discussed yet: If 
the master is forcibly restarted at that point, with pg_ctl restart -m 
immediate, strictly speaking the master should start up in the same 
state, with the unlucky transaction still appearing as in-progress, 
until the standby acknowledges.

> 5. #2 and #5 are now hopelessly out of synch with the master.

> B. Eventual Inconsistency
> -------------------------
> If we have a quorum commit, it's possible for any individual standby to
> be indefinitely ahead of any standby which is not needed by the quorum.
>   This means that:
>
> -- There is no clear criteria for when a standby which is not needed for
> quorum should be considered no longer a synch standby, and
> -- Applications cannot make assumptions that synch rep promises some
> specific window of synchronicity, eliminating a lot of the value of
> quorum commit.

Yep.

> C. Performance
> --------------
> Doing quorum commit requires significant extra accounting on the
> master's part: it must keep track of how many standbys committed for
> each pending transaction (and remember there may be many at the same
> time).
>
> Doing so could involve significant response-time overhead added to the
> simple case where there is only one standby, as well as memory usage,
> and likely a lot of troubleshooting of the mechanism from us.

My gut feeling is that overhead will pale to insignificance compared to 
the network and other overheads of actually getting the WAL to the 
standby and processing the acknowledgments.

> D. Adding/Replacing Quorum Members
> ----------------------------------
> For Quorum commit to be really valuable, we need to be able to add new
> quorum members and remove dead ones *without stopping the master*.  Per
> discussion about the startup issues with only one master, we have not
> worked out how to do this for synch rep standbys.  It's reasonable to
> assume that this will be more complex for a quorum group than with a
> single synch standby.
>
> Consider the case, for example, where due to a network outage we have
> dropped below quorum.  What is the strategy for getting the system
> running again by adding standbys?

You start a new one from the latest base backup and let it catch up? 
Possibly modifying the config file in the master to let it know about 
the new standby, if we go down that path. This part doesn't seem 
particularly hard to me.

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


Re: Issues with Quorum Commit

From
Josh Berkus
Date:
Heikki,

> The master can not roll back or cancel the transaction. That's
> completely infeasible, the WAL record has been written to local disk
> already. The best it can do is halt and wait for enough standbys to
> appear to fulfill the quorum. The client will hang waiting for the
> COMMIT to finish, and the transaction will appear as in-progress to
> other transactions.

Ohhh.  Good point.  So there's no real point in a timeout setting for
quorum commit; it's always "wait forever".

So, this is a critical issue with "wait forever" even with one server.

> There's subtle point here that I don't think has been discussed yet: If
> the master is forcibly restarted at that point, with pg_ctl restart -m
> immediate, strictly speaking the master should start up in the same
> state, with the unlucky transaction still appearing as in-progress,
> until the standby acknowledges.

Yeah.  That makes the ability to issue a command which says "drop all
synch rep and commit whatever's pending" to be critical.

However, this makes for, in some ways, a worse situation: if you fail to
achieve quorum on any commit, then you need to rebuild your entire
quorum pool from scratch.

> You start a new one from the latest base backup and let it catch up?
> Possibly modifying the config file in the master to let it know about
> the new standby, if we go down that path. This part doesn't seem
> particularly hard to me.

Yeah?  How do you modify the config file and get the master to consider
the new server to be part of the quorum pool *without restarting the
master*?

Again, I'm just saying that merely doing single-server synch rep, *and*
making HS/SR easier to admin in general, is going to be a big task for
9.1.  Quorum Commit needs to be considered a separate feature, and one
which is dispensible for 9.1.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Jeff Davis
Date:
On Tue, 2010-10-05 at 12:11 -0700, Josh Berkus wrote:
> B. Eventual Inconsistency
> -------------------------
> If we have a quorum commit, it's possible for any individual standby to
> be indefinitely ahead of any standby which is not needed by the quorum.
>  This means that:
> 
> -- There is no clear criteria for when a standby which is not needed for
> quorum should be considered no longer a synch standby, and
> -- Applications cannot make assumptions that synch rep promises some
> specific window of synchronicity, eliminating a lot of the value of
> quorum commit.

Point B seems particularly dangerous.

When you lose one of the systems and the lagging server becomes required
for quorum, then all of a sudden you could be facing a huge delay to
commit the next transaction (because it needs to catch up on a lot of
WAL replay). This can happen even without a network problem at all, and
seems very likely to result in the lagging system being considered
"down" due to a timeout. Not good, because the reason it is required for
quorum is because another standby just went down.

In other words, a lagging standby combined with a timeout mechanism is
essentially useless, because it will never catch up in time to be a part
of the quorum.

Regards,Jeff Davis



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 22:32 +0300, Heikki Linnakangas wrote:
> On 05.10.2010 22:11, Josh Berkus wrote:
> > There's been a lot of discussion on synch rep lately which involves
> > quorum commit.  I need to raise some major design issues with quorum
> > commit which I don't think that people have really considered, and may
> > be sufficient to prevent it from being included in 9.1.
> 
> Thanks for bringing these up.

Yes, I'm very happy to discuss these.

The points appear to be directed at "quorum commit", which is a name
I've used. But most of the points apply more to Fujii's patch than my
own. I can only presume that Josh wants to prevent us from adopting a
design that allows sync against multiple standbys.

> > A. Permanent Synchronization Failure
> > ---------------------------------
> > Quorum commit, like other forms of more-than-one-standby synch rep,
> > offers the possibility that one or more standbys could end up
> > irretrievably desyncronized with the master.
> >
> > 1. Quorum is 3 servers (out of 5) with mode "apply"
> > 2. Standbys 2 and 4 receive and apply transaction # 20001.
> > 3. Due to a network issue, no other standby applies #20001.
> > 4. Accordingly, the master rolls back #20001 and cancels, either due to
> > timeout or DBA cancel.
> 
> The master can not roll back or cancel the transaction. That's 
> completely infeasible, the WAL record has been written to local disk 
> already. The best it can do is halt and wait for enough standbys to 
> appear to fulfill the quorum. The client will hang waiting for the 
> COMMIT to finish, and the transaction will appear as in-progress to 
> other transactions.

Yes, that point has long been understood. Neither patch does this, and
in fact the issue is a completely general one.

> There's subtle point here that I don't think has been discussed yet: If 
> the master is forcibly restarted at that point, with pg_ctl restart -m 
> immediate, strictly speaking the master should start up in the same 
> state, with the unlucky transaction still appearing as in-progress, 
> until the standby acknowledges.

That is a very important point, but again, nothing to do with quorum
commit. For strict correctness, we should do that. Are you suggesting we
should do that here? 

> > 5. #2 and #5 are now hopelessly out of synch with the master.
> 
> > B. Eventual Inconsistency
> > -------------------------
> > If we have a quorum commit, it's possible for any individual standby to
> > be indefinitely ahead of any standby which is not needed by the quorum.
> >   This means that:
> >
> > -- There is no clear criteria for when a standby which is not needed for
> > quorum should be considered no longer a synch standby, and
> > -- Applications cannot make assumptions that synch rep promises some
> > specific window of synchronicity, eliminating a lot of the value of
> > quorum commit.
> 
> Yep.

Could the person that wrote that actually explain what a "specific
window of synchronicity" is? I'm not sure whether to agree, or disagree.

> > C. Performance
> > --------------
> > Doing quorum commit requires significant extra accounting on the
> > master's part: it must keep track of how many standbys committed for
> > each pending transaction (and remember there may be many at the same
> > time).
> >
> > Doing so could involve significant response-time overhead added to the
> > simple case where there is only one standby, as well as memory usage,
> > and likely a lot of troubleshooting of the mechanism from us.
> 
> My gut feeling is that overhead will pale to insignificance compared to 
> the network and other overheads of actually getting the WAL to the 
> standby and processing the acknowledgments.

You're ignoring Josh's points. Those exact points have been made by me
in support of the design of my patch and against Fujii's. The mechanism
to do this will be more complex and more likely to break. And it will be
slower and that is a concern for me.

> > D. Adding/Replacing Quorum Members
> > ----------------------------------
> > For Quorum commit to be really valuable, we need to be able to add new
> > quorum members and remove dead ones *without stopping the master*.  Per
> > discussion about the startup issues with only one master, we have not
> > worked out how to do this for synch rep standbys.  It's reasonable to
> > assume that this will be more complex for a quorum group than with a
> > single synch standby.
> >
> > Consider the case, for example, where due to a network outage we have
> > dropped below quorum.  What is the strategy for getting the system
> > running again by adding standbys?
> 
> You start a new one from the latest base backup and let it catch up? 
> Possibly modifying the config file in the master to let it know about 
> the new standby, if we go down that path. This part doesn't seem 
> particularly hard to me.

Agreed, not sure of the issue there.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 13:45 -0700, Jeff Davis wrote:
> On Tue, 2010-10-05 at 12:11 -0700, Josh Berkus wrote:
> > B. Eventual Inconsistency
> > -------------------------
> > If we have a quorum commit, it's possible for any individual standby to
> > be indefinitely ahead of any standby which is not needed by the quorum.
> >  This means that:
> > 
> > -- There is no clear criteria for when a standby which is not needed for
> > quorum should be considered no longer a synch standby, and
> > -- Applications cannot make assumptions that synch rep promises some
> > specific window of synchronicity, eliminating a lot of the value of
> > quorum commit.
> 
> Point B seems particularly dangerous.
> 
> When you lose one of the systems and the lagging server becomes required
> for quorum, then all of a sudden you could be facing a huge delay to
> commit the next transaction (because it needs to catch up on a lot of
> WAL replay). This can happen even without a network problem at all, and
> seems very likely to result in the lagging system being considered
> "down" due to a timeout. Not good, because the reason it is required for
> quorum is because another standby just went down.
> 
> In other words, a lagging standby combined with a timeout mechanism is
> essentially useless, because it will never catch up in time to be a part
> of the quorum.

Thanks for explaining what was meant.

This issue is a serious problem with the apply to *all* servers that
Heikki has been describing as being a useful use case. We register a
standby, it goes down and we decide to wait for it. Then when it does
come back up it takes ages to catch up.

This is really the nail in the coffin for the "All" servers use case,
and a significant blow to the requirement for standby registration.

If we use N+1 redundancy as I have explained, then this situation does
not occur until you have less than N standbys available. But then it's
no surprise that RAID-5 won't work with 4 drives either.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Tue, Oct 5, 2010 at 5:10 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> The points appear to be directed at "quorum commit", which is a name
> I've used. But most of the points apply more to Fujii's patch than my
> own. I can only presume that Josh wants to prevent us from adopting a
> design that allows sync against multiple standbys.

This looks to me like a cheap shot that doesn't advance the
discussion.  You are the first to complain when people don't take your
ideas as seriously as you feel they should.

>> > A. Permanent Synchronization Failure
>> > ---------------------------------
>> > Quorum commit, like other forms of more-than-one-standby synch rep,
>> > offers the possibility that one or more standbys could end up
>> > irretrievably desyncronized with the master.
>> >
>> > 1. Quorum is 3 servers (out of 5) with mode "apply"
>> > 2. Standbys 2 and 4 receive and apply transaction # 20001.
>> > 3. Due to a network issue, no other standby applies #20001.
>> > 4. Accordingly, the master rolls back #20001 and cancels, either due to
>> > timeout or DBA cancel.
>>
>> The master can not roll back or cancel the transaction. That's
>> completely infeasible, the WAL record has been written to local disk
>> already. The best it can do is halt and wait for enough standbys to
>> appear to fulfill the quorum. The client will hang waiting for the
>> COMMIT to finish, and the transaction will appear as in-progress to
>> other transactions.
>
> Yes, that point has long been understood. Neither patch does this, and
> in fact the issue is a completely general one.

Yep.

>> There's subtle point here that I don't think has been discussed yet: If
>> the master is forcibly restarted at that point, with pg_ctl restart -m
>> immediate, strictly speaking the master should start up in the same
>> state, with the unlucky transaction still appearing as in-progress,
>> until the standby acknowledges.
>
> That is a very important point, but again, nothing to do with quorum
> commit. For strict correctness, we should do that. Are you suggesting we
> should do that here?

I agree that this has nothing to do with quorum commit.  It does have
to do with synchronous replication, but I'm skeptical that we want to
get into it for this release, if ever.

>> > 5. #2 and #5 are now hopelessly out of synch with the master.
>>
>> > B. Eventual Inconsistency
>> > -------------------------
>> > If we have a quorum commit, it's possible for any individual standby to
>> > be indefinitely ahead of any standby which is not needed by the quorum.
>> >   This means that:
>> >
>> > -- There is no clear criteria for when a standby which is not needed for
>> > quorum should be considered no longer a synch standby, and
>> > -- Applications cannot make assumptions that synch rep promises some
>> > specific window of synchronicity, eliminating a lot of the value of
>> > quorum commit.
>>
>> Yep.
>
> Could the person that wrote that actually explain what a "specific
> window of synchronicity" is? I'm not sure whether to agree, or disagree.

Me either.

>> > C. Performance
>> > --------------
>> > Doing quorum commit requires significant extra accounting on the
>> > master's part: it must keep track of how many standbys committed for
>> > each pending transaction (and remember there may be many at the same
>> > time).
>> >
>> > Doing so could involve significant response-time overhead added to the
>> > simple case where there is only one standby, as well as memory usage,
>> > and likely a lot of troubleshooting of the mechanism from us.
>>
>> My gut feeling is that overhead will pale to insignificance compared to
>> the network and other overheads of actually getting the WAL to the
>> standby and processing the acknowledgments.
>
> You're ignoring Josh's points. Those exact points have been made by me
> in support of the design of my patch and against Fujii's. The mechanism
> to do this will be more complex and more likely to break. And it will be
> slower and that is a concern for me.

I don't think Heikki ignored Josh's points, and I do think Heikki's
analysis is correct.

>> > D. Adding/Replacing Quorum Members
>> > ----------------------------------
>> > For Quorum commit to be really valuable, we need to be able to add new
>> > quorum members and remove dead ones *without stopping the master*.  Per
>> > discussion about the startup issues with only one master, we have not
>> > worked out how to do this for synch rep standbys.  It's reasonable to
>> > assume that this will be more complex for a quorum group than with a
>> > single synch standby.
>> >
>> > Consider the case, for example, where due to a network outage we have
>> > dropped below quorum.  What is the strategy for getting the system
>> > running again by adding standbys?
>>
>> You start a new one from the latest base backup and let it catch up?
>> Possibly modifying the config file in the master to let it know about
>> the new standby, if we go down that path. This part doesn't seem
>> particularly hard to me.
>
> Agreed, not sure of the issue there.

Also agreed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 13:43 -0700, Josh Berkus wrote:
> Again, I'm just saying that merely doing single-server synch rep,
> *and*
> making HS/SR easier to admin in general, is going to be a big task for
> 9.1.  Quorum Commit needs to be considered a separate feature, and one
> which is dispensible for 9.1. 

Agreed.

So no need at all for standby.conf. Phew!

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 17:21 -0400, Robert Haas wrote:
> On Tue, Oct 5, 2010 at 5:10 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > The points appear to be directed at "quorum commit", which is a name
> > I've used. But most of the points apply more to Fujii's patch than my
> > own. I can only presume that Josh wants to prevent us from adopting a
> > design that allows sync against multiple standbys.
> 
> This looks to me like a cheap shot that doesn't advance the
> discussion.  You are the first to complain when people don't take your
> ideas as seriously as you feel they should.

Whatever are you talking about? This is a technical discussion.

I'm checking what Josh actually means by Quorum Commit, since
regrettably the points fall very badly against Fujii's patch. Josh has
echoed some points of mine and Jeff's point about dangerous behaviour
blows a hole a mile wide in the justification for standby.conf etc..

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Josh Berkus
Date:
Simon,  Robert,

> The points appear to be directed at "quorum commit", which is a name
> I've used. But most of the points apply more to Fujii's patch than my
> own. 

Per previous discussion, I'm trying to get at what reasonable behavior
is, rather than targeting one patch or the other.

> I can only presume that Josh wants to prevent us from adopting a
> design that allows sync against multiple standbys.

Quorum commit == "X servers need to ack for commit", where X > 1.
Usually done as "X out of Y servers must ack", but it's not a given that
the master needs to know how many servers there are, just how many ack'ed.

And I'm not against it; I'm just pointing out that it gives us some
issues which we don't have with a single standby, and thus quorum commit
ought to be treated as a separate feature in 9.1 development.

>> The master can not roll back or cancel the transaction. That's 
>> completely infeasible, the WAL record has been written to local disk 
>> already. The best it can do is halt and wait for enough standbys to 
>> appear to fulfill the quorum. The client will hang waiting for the 
>> COMMIT to finish, and the transaction will appear as in-progress to 
>> other transactions.
> 
> Yes, that point has long been understood. Neither patch does this, and
> in fact the issue is a completely general one.

So, in that case, if it's been 10 minutes, and we're still not getting
ack from standbys, what's the exit strategy for the hapless DBA?
Practically speaking?  Without restarting the master?

Last I checked, our goal with synch standby was to increase availablity,
not decrease it.  This is, however, not an issue with quorum commit, but
an issue with sync rep in general.

> Could the person that wrote that actually explain what a "specific
> window of synchronicity" is? I'm not sure whether to agree, or disagree.

A specific amount of time within which all nodes will be consistent
regarding that specific transaction.

>> You start a new one from the latest base backup and let it catch up? 
>> Possibly modifying the config file in the master to let it know about 
>> the new standby, if we go down that path. This part doesn't seem 
>> particularly hard to me.
> 
> Agreed, not sure of the issue there.

See previous post.  The critical phrase is *without restarting the
master*.  AFAICT, no patch has addressed the need to change the master's
synch configuration without restarting it.  It's possible that I'm not
following something, in which case I'd love to have it pointed out.


--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 15:14 -0700, Josh Berkus wrote:

> > I can only presume that Josh wants to prevent us from adopting a
> > design that allows sync against multiple standbys.
> 
> Quorum commit == "X servers need to ack for commit", where X > 1.
> Usually done as "X out of Y servers must ack", but it's not a given that
> the master needs to know how many servers there are, just how many ack'ed.
> 
> And I'm not against it; I'm just pointing out that it gives us some
> issues which we don't have with a single standby, and thus quorum commit
> ought to be treated as a separate feature in 9.1 development.

OK, so I did understand you correctly.

Heikki had argued that a use case existed where Y out of Y (i.e. all)
nodes must acknowledge before we commit. That was the use case that
required us to have standby registration. It was optional in all other
cases.

We should note that Oracle only allows X=1, i.e. first acknowledgement
releases waiter. My patch provides X=1 only and takes advantage of the
simpler in-memory data structures as a result.

> >> The master can not roll back or cancel the transaction. That's 
> >> completely infeasible, the WAL record has been written to local disk 
> >> already. The best it can do is halt and wait for enough standbys to 
> >> appear to fulfill the quorum. The client will hang waiting for the 
> >> COMMIT to finish, and the transaction will appear as in-progress to 
> >> other transactions.
> > 
> > Yes, that point has long been understood. Neither patch does this, and
> > in fact the issue is a completely general one.
> 
> So, in that case, if it's been 10 minutes, and we're still not getting
> ack from standbys, what's the exit strategy for the hapless DBA?
> Practically speaking?  Without restarting the master?
> 
> Last I checked, our goal with synch standby was to increase availablity,
> not decrease it.  This is, however, not an issue with quorum commit, but
> an issue with sync rep in general.

Completely agree. When we had that discussion some months/weeks back, we
spoke about having a timeout. My patch has implemented a timeout,
followed by a COMMIT. That allows increased availability, as you say.

You would also be able to specifically release all/some transactions
from wait state with a simple function pg_cancel_sync_wait() (or similar
name).

> > Could the person that wrote that actually explain what a "specific
> > window of synchronicity" is? I'm not sure whether to agree, or disagree.
> 
> A specific amount of time within which all nodes will be consistent
> regarding that specific transaction.

Certainly no patch offers that. I'm not sure such a possibility exists.
Asking for higher X does make that situation worse.

> >> You start a new one from the latest base backup and let it catch up? 
> >> Possibly modifying the config file in the master to let it know about 
> >> the new standby, if we go down that path. This part doesn't seem 
> >> particularly hard to me.
> > 
> > Agreed, not sure of the issue there.
> 
> See previous post.  The critical phrase is *without restarting the
> master*.  AFAICT, no patch has addressed the need to change the master's
> synch configuration without restarting it.  It's possible that I'm not
> following something, in which case I'd love to have it pointed out.

My patch does not require a restart of the master to add/remove sync rep
nodes. They just come and go as needed. 

I don't think Fujii's patch would have a great problem with that either,
but I can't speak for that with precision.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Josh Berkus
Date:
> Heikki had argued that a use case existed where Y out of Y (i.e. all)
> nodes must acknowledge before we commit. That was the use case that
> required us to have standby registration. It was optional in all other
> cases.

Yeah, Y of Y is just a special case of X of Y.  And, IMHO, rather
pointless if we can't guarantee consistency between the standbys, which
we can't.

> We should note that Oracle only allows X=1, i.e. first acknowledgement
> releases waiter. My patch provides X=1 only and takes advantage of the
> simpler in-memory data structures as a result.

I agree that we ought to start with X=1 for 9.1 and leave more
complicated architectures until we have that committed and tested.

> You would also be able to specifically release all/some transactions
> from wait state with a simple function pg_cancel_sync_wait() (or similar
> name).

That would be fine for the use cases I'll be implementing.

> My patch does not require a restart of the master to add/remove sync rep
> nodes. They just come and go as needed. 
> 
> I don't think Fujii's patch would have a great problem with that either,
> but I can't speak for that with precision.

Ok.  That really was not made clear in prior arguments.

FYI, for the production uses of synch rep I'd specifically be
implementing, what the users would want is:

1) One master, one synch standby, 1-2 asynch standbys
2) Synch rep tries to synch for # seconds.
3) If it fails, it switches the synch standby to asynch and screams
bloody murder somewhere nagios can pick it up.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Jeff Davis
Date:
On Tue, 2010-10-05 at 22:19 +0100, Simon Riggs wrote:
> > In other words, a lagging standby combined with a timeout mechanism is
> > essentially useless, because it will never catch up in time to be a part
> > of the quorum.
> 
> Thanks for explaining what was meant.
> 
> This issue is a serious problem with the apply to *all* servers that
> Heikki has been describing as being a useful use case. We register a
> standby, it goes down and we decide to wait for it. Then when it does
> come back up it takes ages to catch up.
> 
> This is really the nail in the coffin for the "All" servers use case,
> and a significant blow to the requirement for standby registration.

I'm not sure I entirely understand. I was concerned about the case of a
standby server being allowed to lag behind the rest by a large number of
WAL records. That can't happen in the "wait for all servers to apply"
case, because the system would become unavailable rather than allow a
significant difference in the amount of WAL applied.

I'm not saying that an unavailable system is good, but I don't see how
my particular complaint applies to the "wait for all servers to apply"
case.

The case I was worried about is:* 1 master and 2 standby* The rule is "wait for at least one standby to apply the WAL"

In your notation, I believe that's M -> { S1, S2 }

In that case, if one S1 is just a little faster than S2, then S2 might
build up a significant queue of unapplied WAL. Then, when S1 goes down,
there's no way for the slower one to acknowledge a new transaction
without playing through all of the unapplied WAL.

Intuitively, the administrator would think that he was getting both HA
and redundancy, but in reality the availability is no better than if
there were only two servers (M -> S1), except that it might be faster to
replay the WAL then to set up a new standby (but that's not guaranteed).

I think you would call that a misconfiguration, and I would agree. I was
just trying to point out a pitfall that I didn't see until I read Josh's
email.

> If we use N+1 redundancy as I have explained, then this situation does
> not occur until you have less than N standbys available. But then it's
> no surprise that RAID-5 won't work with 4 drives either.

Now I'm more confused. I assume that was a typo (because a RAID-5 does
work with 4 drives), but I think it obscured your point.

Regards,Jeff Davis



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Tue, 2010-10-05 at 18:52 -0700, Jeff Davis wrote:

> I'm not saying that an unavailable system is good, but I don't see how
> my particular complaint applies to the "wait for all servers to apply"
> case.

> The case I was worried about is:
>  * 1 master and 2 standby
>  * The rule is "wait for at least one standby to apply the WAL"
> 
> In your notation, I believe that's M -> { S1, S2 }
> 
> In that case, if one S1 is just a little faster than S2, then S2 might
> build up a significant queue of unapplied WAL. Then, when S1 goes down,
> there's no way for the slower one to acknowledge a new transaction
> without playing through all of the unapplied WAL.

That situation would require two things
* First, you have set up async replication and you're not monitoring it
properly. Shame on you.
* Second, you would have to request "apply" mode sync rep. If you had
requested "recv" or "fsync" mode, then the standby does *not* have to
have applied the WAL before acknowledgement.

Since the first problem is a generic problem with async replication, and
can already happen in 8.2+, its not exactly an argument against a new
feature.

> Intuitively, the administrator would think that he was getting both HA
> and redundancy, but in reality the availability is no better than if
> there were only two servers (M -> S1), except that it might be faster to
> replay the WAL then to set up a new standby (but that's not guaranteed).

Not guaranteed, but very likely that the standby would not be that far
behind. If it gets too far behind it will likely blow out the disk space
on the standby and fail.

> I think you would call that a misconfiguration, and I would agree. 

Yes, regrettably there are various ways to misconfigure this. The above
is really a degeneration of the 2 standby case into the 1 standby case:
if you ask for 2 standbys and one of them is ineffective, then the
system acts like you have only one.

> I was
> just trying to point out a pitfall that I didn't see until I read Josh's
> email.

You mention that it cannot occur if we choose to lock up the master and
cause transactions to wait. That may be true in many cases. It does
still occur when we have transactions that generate a large amount of
WAL, loads, ALTER TABLEs etc.. In those cases, S2 could well fall far
behind S1 during those long transactions and if S1 goes down at that
point there would be a backlog to apply.  But again, this only applies
to "apply" mode sync rep.

So it can occur in both cases, though it now looks to me that its less
important an issue in either case. So I think this doesn't rate the term
dangerous to describe it any longer.

Thanks for your careful thought and analysis on this.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 01:14, Josh Berkus wrote:
> Last I checked, our goal with synch standby was to increase availablity,
> not decrease it.

No. Synchronous replication does not help with availability. It allows 
you to achieve zero data loss, ie. if the master dies, you are 
guaranteed that any transaction that was acknowledged as committed, is 
still committed.

The other use case is keeping a hot standby server (or servers) 
up-to-date, so that you can run queries against it and you are 
guaranteed to get the same results you would if you ran the query in the 
master.

Those are the two reasonable use cases I've seen. Anything else that has 
been discussed is some sort of a combination of those two, or something 
that doesn't make much sense when you scratch the surface and start 
looking at the failure modes.

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


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 01:14, Josh Berkus wrote:
>>> You start a new one from the latest base backup and let it catch up?
>>> Possibly modifying the config file in the master to let it know about
>>> the new standby, if we go down that path. This part doesn't seem
>>> particularly hard to me.
>>
>> Agreed, not sure of the issue there.
>
> See previous post.  The critical phrase is *without restarting the
> master*.  AFAICT, no patch has addressed the need to change the master's
> synch configuration without restarting it.  It's possible that I'm not
> following something, in which case I'd love to have it pointed out.

Fair enough. I agree it's important that the configuration can be 
changed on the fly. It's orthogonal to the other things discussed, so 
let's just assume for now that we'll have that. If not in the first 
version, it can be added afterwards. "pg_ctl reload" is probably how it 
will be done.

There is some interesting behavioral questions there on what happens 
when the configuration is changed. Like if you first define that 3 out 
of 5 servers must acknowledge, and you have an in-progress commit that 
has received 2 acks already. If you then change the config to "2 out of 
4" servers must acknowledge, is the in-progress commit now satisfied? From the admin point of view, the server that was
removedfrom the 
 
system might've been one that had acknowledged already, and logically in 
the new configuration the transaction has only received 1 acknowledgment 
from those servers that are still part of the system. Explicitly naming 
the standbys in the config file would solve that particular corner case, 
but it would no doubt introduce other similar ones.

But it's an orthogonal issue, we'll figure it out when we get there.

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


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/06/2010 04:31 AM, Simon Riggs wrote:
> That situation would require two things
> * First, you have set up async replication and you're not monitoring it
> properly. Shame on you.

The way I read it, Jeff is complaining about the timeout you propose
that effectively turns sync into async replication in case of a failure.

With a master that waits forever, the standby that's newly required for
quorum certainly still needs its time to catch up. But it wouldn't live
in danger of being "optimized away" for availability in case it cannot
catch up within the given timeout. It's a tradeoff between availability
and durability.

> So it can occur in both cases, though it now looks to me that its less
> important an issue in either case. So I think this doesn't rate the term
> dangerous to describe it any longer.

The proposed timeout certainly still sounds dangerous to me. I'd rather
recommend setting it to an incredibly huge value to minimize its dangers
and get sync replication when that is what has been asked for. Use async
replication for increased availability.

Or do you envision any use case that requires a quorum of X standbies
for normal operation but is just fine with only none to (X-1) standbies
in case of failures? IMO that's when sync replication is most needed and
when it absolutely should hold to its promises - even if it means to
stop the system.

There's no point in continuing operation if you cannot guarantee the
minimum requirements for durability. If you happen to want such a thing,
you should better rethink your minimum requirement (as performance for
normal operations might benefit from a lower minimum as well).

Regards

Markus Wanner



Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 6, 2010 at 10:52 AM, Jeff Davis <pgsql@j-davis.com> wrote:
> I'm not sure I entirely understand. I was concerned about the case of a
> standby server being allowed to lag behind the rest by a large number of
> WAL records. That can't happen in the "wait for all servers to apply"
> case, because the system would become unavailable rather than allow a
> significant difference in the amount of WAL applied.
>
> I'm not saying that an unavailable system is good, but I don't see how
> my particular complaint applies to the "wait for all servers to apply"
> case.
>
> The case I was worried about is:
>  * 1 master and 2 standby
>  * The rule is "wait for at least one standby to apply the WAL"
>
> In your notation, I believe that's M -> { S1, S2 }
>
> In that case, if one S1 is just a little faster than S2, then S2 might
> build up a significant queue of unapplied WAL. Then, when S1 goes down,
> there's no way for the slower one to acknowledge a new transaction
> without playing through all of the unapplied WAL.
>
> Intuitively, the administrator would think that he was getting both HA
> and redundancy, but in reality the availability is no better than if
> there were only two servers (M -> S1), except that it might be faster to
> replay the WAL then to set up a new standby (but that's not guaranteed).

Agreed. This is similar to my previous complaint.
http://archives.postgresql.org/pgsql-hackers/2010-09/msg00946.php

This problem would happen even if we fix the quorum to 1 as Josh propose.
To avoid this, the master must wait for ACK from all the connected
synchronous standbys.

I think that this is likely to happen especially when we choose 'apply'
replication level. Because that level can easily lag a synchronous
standby because of the conflict between recovery and read-only query.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/06/2010 08:31 AM, Heikki Linnakangas wrote:
> On 06.10.2010 01:14, Josh Berkus wrote:
>> Last I checked, our goal with synch standby was to increase availablity,
>> not decrease it.
> 
> No. Synchronous replication does not help with availability. It allows
> you to achieve zero data loss, ie. if the master dies, you are
> guaranteed that any transaction that was acknowledged as committed, is
> still committed.

Strictly speaking, it even reduces availability. Which is why nobody
actually wants *only* synchronous replication. Instead they use quorum
commit or semi-synchronous (shudder) replication, which only requires
*some* nodes to be in sync, but effectively replicates asynchronously to
the others.

From that point of view, the requirement of having one synch and two
async standbies is pretty much the same as having three synch standbies
with a quorum commit of 1. (Except for additional availability of the
later variant, because in case of a failure of the one sync standby, any
of the others can take over without admin intervention).

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 6, 2010 at 3:31 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> No. Synchronous replication does not help with availability. It allows you
> to achieve zero data loss, ie. if the master dies, you are guaranteed that
> any transaction that was acknowledged as committed, is still committed.

Hmm.. but we can increase availability without any data loss by using
synchronous
replication. Many people have already been using synchronous
replication softwares
such as DRBD for that purpose.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 11:09, Fujii Masao wrote:
> On Wed, Oct 6, 2010 at 3:31 PM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com>  wrote:
>> No. Synchronous replication does not help with availability. It allows you
>> to achieve zero data loss, ie. if the master dies, you are guaranteed that
>> any transaction that was acknowledged as committed, is still committed.
>
> Hmm.. but we can increase availability without any data loss by using
> synchronous
> replication. Many people have already been using synchronous
> replication softwares
> such as DRBD for that purpose.

Sure, but it's not the synchronous aspect that increases availability. 
It's the replication aspect, and we already have that. Making the 
replication synchronous allows zero data loss in case the master 
suddenly dies, but it comes at the cost of availability.

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


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/06/2010 10:17 AM, Heikki Linnakangas wrote:
> On 06.10.2010 11:09, Fujii Masao wrote:
>> Hmm.. but we can increase availability without any data loss by using
>> synchronous
>> replication. Many people have already been using synchronous
>> replication softwares
>> such as DRBD for that purpose.
> 
> Sure, but it's not the synchronous aspect that increases availability.
> It's the replication aspect, and we already have that.

..the *asynchronous* replication aspect, yes.

The drdb.conf man page [1] describes parameters of DRDB. It's worth
noting that even in "Protocol C" (synchronous mode), they sport a
timeout of only 6 seconds (by default).

After that, the primary node proceeds without any kind of guarantee
(which can be thought of as switching to async replication). Just as
Simon proposes for Postgres as well.

Maybe that really is enough for now. Everybody that needs stricter
durability guarantees needs to wait for Postgres-R ;-)

Regards

Markus Wanner


[1]: drdb.conf man page:
http://www.drbd.org/users-guide/re-drbdconf.html



Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 6, 2010 at 5:17 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 06.10.2010 11:09, Fujii Masao wrote:
>>
>> On Wed, Oct 6, 2010 at 3:31 PM, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com>  wrote:
>>>
>>> No. Synchronous replication does not help with availability. It allows
>>> you
>>> to achieve zero data loss, ie. if the master dies, you are guaranteed
>>> that
>>> any transaction that was acknowledged as committed, is still committed.
>>
>> Hmm.. but we can increase availability without any data loss by using
>> synchronous
>> replication. Many people have already been using synchronous
>> replication softwares
>> such as DRBD for that purpose.
>
> Sure, but it's not the synchronous aspect that increases availability. It's
> the replication aspect, and we already have that. Making the replication
> synchronous allows zero data loss in case the master suddenly dies, but it
> comes at the cost of availability.

Yep. But I mean that the synchronous aspect is helpful to increase the
availability of the system which requires no data loss. In asynchronous
replication, when the master goes down, we have to salvage the missing
WAL for the standby from the failed master to avoid data loss. This would
take very long and decrease the availability of the system which doesn't
accept any data loss. Since the synchronous doesn't require such a salvage,
it can increase the availability of such a system.

If we want only no data loss, we have only to implement the wait-forever
option. But if we make consideration for the above-mentioned availability,
the return-immediately option also would be required.

In some (many, I think) cases, I think that we need to consider availability
and no data loss together, and consider the balance of them.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 11:39, Markus Wanner wrote:
> On 10/06/2010 10:17 AM, Heikki Linnakangas wrote:
>> On 06.10.2010 11:09, Fujii Masao wrote:
>>> Hmm.. but we can increase availability without any data loss by using
>>> synchronous
>>> replication. Many people have already been using synchronous
>>> replication softwares
>>> such as DRBD for that purpose.
>>
>> Sure, but it's not the synchronous aspect that increases availability.
>> It's the replication aspect, and we already have that.
>
> ..the *asynchronous* replication aspect, yes.
>
> The drdb.conf man page [1] describes parameters of DRDB. It's worth
> noting that even in "Protocol C" (synchronous mode), they sport a
> timeout of only 6 seconds (by default).

Wow, that is really short. Are you sure? I have no first hand experience 
with DRBD, and reading that man page, I get the impression that the 
timeout us just for deciding that the TCP connection is dead. There is 
also the ko-count parameter, which defaults to zero. I would guess that 
ko-count=0 is "wait forever", while ko-count=1 is what you described, 
but I'm not sure.

It's not hard to imagine the master failing in a way that first causes 
the connection to standby to drop, and the disk failing 6 seconds later. 
A fire that destroys the network cable first and then spreads to the 
disk array for example.

> [1]: drdb.conf man page:
> http://www.drbd.org/users-guide/re-drbdconf.html

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


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 11:49, Fujii Masao wrote:
> On Wed, Oct 6, 2010 at 5:17 PM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com>  wrote:
>> Sure, but it's not the synchronous aspect that increases availability. It's
>> the replication aspect, and we already have that. Making the replication
>> synchronous allows zero data loss in case the master suddenly dies, but it
>> comes at the cost of availability.
>
> Yep. But I mean that the synchronous aspect is helpful to increase the
> availability of the system which requires no data loss. In asynchronous
> replication, when the master goes down, we have to salvage the missing
> WAL for the standby from the failed master to avoid data loss. This would
> take very long and decrease the availability of the system which doesn't
> accept any data loss. Since the synchronous doesn't require such a salvage,
> it can increase the availability of such a system.

In general, salvaging the WAL that was not sent to the standby yet is 
outright impossible. You can't achieve zero data loss with asynchronous 
replication at all.

> If we want only no data loss, we have only to implement the wait-forever
> option. But if we make consideration for the above-mentioned availability,
> the return-immediately option also would be required.
>
> In some (many, I think) cases, I think that we need to consider availability
> and no data loss together, and consider the balance of them.

If you need both, you need three servers as Simon pointed out earlier. 
There is no way around that.

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


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/06/2010 10:53 AM, Heikki Linnakangas wrote:
> Wow, that is really short. Are you sure? I have no first hand experience
> with DRBD,

Neither do I.

> and reading that man page, I get the impression that the
> timeout us just for deciding that the TCP connection is dead. There is
> also the ko-count parameter, which defaults to zero. I would guess that
> ko-count=0 is "wait forever", while ko-count=1 is what you described,
> but I'm not sure.

Yeah, sounds more likely. Then I'm surprised that I didn't find any
warning that the Protocol C definitely reduces availability (with the
ko-count=0 default, that is). Instead, they only state that it's the
most used replication mode, which really makes me wonder. [1]

Sorry for adding confusion by not researching properly.

Regards

Markus Wanner


[1] DRDB Repliaction Modes
http://www.drbd.org/users-guide-emb/s-replication-protocols.html


Re: Issues with Quorum Commit

From
Magnus Hagander
Date:
On Wed, Oct 6, 2010 at 10:17, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 06.10.2010 11:09, Fujii Masao wrote:
>>
>> On Wed, Oct 6, 2010 at 3:31 PM, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com>  wrote:
>>>
>>> No. Synchronous replication does not help with availability. It allows
>>> you
>>> to achieve zero data loss, ie. if the master dies, you are guaranteed
>>> that
>>> any transaction that was acknowledged as committed, is still committed.
>>
>> Hmm.. but we can increase availability without any data loss by using
>> synchronous
>> replication. Many people have already been using synchronous
>> replication softwares
>> such as DRBD for that purpose.
>
> Sure, but it's not the synchronous aspect that increases availability. It's
> the replication aspect, and we already have that. Making the replication
> synchronous allows zero data loss in case the master suddenly dies, but it
> comes at the cost of availability.

That's only for a narrow definition of availability. For a lot of
people, having access to your data isn't considered availability
unless you can trust the data...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 13:41, Magnus Hagander wrote:
> That's only for a narrow definition of availability. For a lot of
> people, having access to your data isn't considered availability
> unless you can trust the data...

Ok, fair enough. For that, synchronous replication in the "wait forever" 
mode is the only alternative. That on its own doesn't give you any boost 
in availability, on the contrary, but coupled with suitable clustering 
tools to handle failover and deciding when the standby is dead, you can 
achieve that.

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


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Markus Wanner <markus@bluegap.ch> writes:
> On 10/06/2010 04:31 AM, Simon Riggs wrote:
>> That situation would require two things
>> * First, you have set up async replication and you're not monitoring it
>> properly. Shame on you.
>
> The way I read it, Jeff is complaining about the timeout you propose
> that effectively turns sync into async replication in case of a failure.
>
> With a master that waits forever, the standby that's newly required for
> quorum certainly still needs its time to catch up. But it wouldn't live
> in danger of being "optimized away" for availability in case it cannot
> catch up within the given timeout. It's a tradeoff between availability
> and durability.

What is necessary here is a clear view on the possible states that a
standby can be in at any time, and we must stop trying to apply to
some non-ready standby the behavior we want when it's already in-sync.

From my experience operating londiste, those states would be:
1. base-backup  — self explaining2. catch-up     — getting the WAL to catch up after base backup3. wanna-sync   — don't
yethave all the WAL to get in sync4. do-sync      — all WALs are there, coming soon5. ok (async | recv | fsync | reply
—feedback loop engaged) 

So you only consider that a standby is a candidate for sync rep when
it's reached the ok state, and that's when it's able to fill the
feedback loop we've been talking about. Standby state != ok, no waiting
no nothing, it's *not* a standby as far as the master is concerned.

The other states allow to manage accepting a new standby into an
existing setup, and to manage error failures. When we stop receiving the
feedback loop events, the master knows the slave ain't in the "ok" state
any more and can demote it to "wanna-sync", because it has to keep WALs
until the slave comes again.

If the standby is not back online and wal_keep_segments makes it so that
we can't keep its wal anymore, the state gets back to "base-backup".

Not going into every details here (for example, we might need some
protocol arbitrage for the standby to be able to explain the master that
it's ok even if the master thinks it's not), but my point is that
without a clear list of standby states, we're going to hinder the master
in situations where it makes no sense to do so.

> Or do you envision any use case that requires a quorum of X standbies
> for normal operation but is just fine with only none to (X-1) standbies
> in case of failures? IMO that's when sync replication is most needed and
> when it absolutely should hold to its promises - even if it means to
> stop the system.
>
> There's no point in continuing operation if you cannot guarantee the
> minimum requirements for durability. If you happen to want such a thing,
> you should better rethink your minimum requirement (as performance for
> normal operations might benefit from a lower minimum as well).

This part of the discussion made me think of yet another refinement on
the Quorum Commit idea, even if I'm beginning to think that can be
material for later.

Basic Quorum Commit is having each transaction on the master wait for a
total number of votes to accept the transaction synced. Each standby has
a weight, meaning 1 or more votes. The problem is the flexibility isn't
there, some cases are impossible to setup. Also people want to be able
to specify their favorite standby and that's quickly uneasy.

Idea : segment the votes into "colors" or any categories you like. Have
each standby be a member of a category list, and require per-category
quorums to be reached. This is the same as attributing roles to standbys
and saying that they're all equivalent as soon as part of the given
role, with the added flexibility that you can sometime want more than
one standby of a given role to take part of the quorum.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 15:22, Dimitri Fontaine wrote:
> What is necessary here is a clear view on the possible states that a
> standby can be in at any time, and we must stop trying to apply to
> some non-ready standby the behavior we want when it's already in-sync.
>
>  From my experience operating londiste, those states would be:
>
>   1. base-backup  — self explaining
>   2. catch-up     — getting the WAL to catch up after base backup
>   3. wanna-sync   — don't yet have all the WAL to get in sync
>   4. do-sync      — all WALs are there, coming soon
>   5. ok (async | recv | fsync | reply — feedback loop engaged)
>
> So you only consider that a standby is a candidate for sync rep when
> it's reached the ok state, and that's when it's able to fill the
> feedback loop we've been talking about. Standby state != ok, no waiting
> no nothing, it's *not* a standby as far as the master is concerned.

You're not going to get zero data loss that way. Can you elaborate what 
the use case for that mode is?

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


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Wed, 2010-10-06 at 15:26 +0300, Heikki Linnakangas wrote:

> You're not going to get zero data loss that way.

Ending the wait state does not cause data loss. It puts you at *risk* of
data loss, which is a different thing entirely.

If you want to avoid data loss you use N+k redundancy and get on with
life, rather than sitting around waiting.

Putting in a feature for people that choose k=0 seems wasteful to me,
since they knowingly put themselves at risk in the first place.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>>   1. base-backup  — self explaining
>>   2. catch-up     — getting the WAL to catch up after base backup
>>   3. wanna-sync   — don't yet have all the WAL to get in sync
>>   4. do-sync      — all WALs are there, coming soon
>>   5. ok (async | recv | fsync | reply — feedback loop engaged)
>>
>> So you only consider that a standby is a candidate for sync rep when
>> it's reached the ok state, and that's when it's able to fill the
>> feedback loop we've been talking about. Standby state != ok, no waiting
>> no nothing, it's *not* a standby as far as the master is concerned.
>
> You're not going to get zero data loss that way. Can you elaborate what the
> use case for that mode is?

You can't pretend to sync with zero data loss until the standby is ready
for it, or you need to take the site down while you add your standby.

I can see some user willing to take the site down while doing the base
backup dance then waiting for initial sync, then only accepting traffic
and being secure against data loss, but I'd much rather that be an
option and you could watch for your standby's state in a system view.

Meanwhile, I can't understand any reason for the master to pretend it
can safely manage any sync-rep transaction while there's no standby
around. Either you wait for the quorum and don't have it, or you have to
track standby states with precision and maybe actively reject writes.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 17:20, Simon Riggs wrote:
> On Wed, 2010-10-06 at 15:26 +0300, Heikki Linnakangas wrote:
>
>> You're not going to get zero data loss that way.
>
> Ending the wait state does not cause data loss. It puts you at *risk* of
> data loss, which is a different thing entirely.

Looking at it that way, asynchronous replication just puts you at risk 
of data loss too, it doesn't necessarily mean you get data loss.

The key is whether you are guaranteed to have zero data loss or not. If 
you don't wait forever, you're not guaranteed zero data loss. It's just 
best effort, like asynchronous replication. The situation you want to 
avoid is that the master dies, and you don't know if you have suffered 
data loss or not.

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


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 18:02, Dimitri Fontaine wrote:
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  writes:
>>>    1. base-backup  — self explaining
>>>    2. catch-up     — getting the WAL to catch up after base backup
>>>    3. wanna-sync   — don't yet have all the WAL to get in sync
>>>    4. do-sync      — all WALs are there, coming soon
>>>    5. ok (async | recv | fsync | reply — feedback loop engaged)
>>>
>>> So you only consider that a standby is a candidate for sync rep when
>>> it's reached the ok state, and that's when it's able to fill the
>>> feedback loop we've been talking about. Standby state != ok, no waiting
>>> no nothing, it's *not* a standby as far as the master is concerned.
>>
>> You're not going to get zero data loss that way. Can you elaborate what the
>> use case for that mode is?
>
> You can't pretend to sync with zero data loss until the standby is ready
> for it, or you need to take the site down while you add your standby.
>
> I can see some user willing to take the site down while doing the base
> backup dance then waiting for initial sync, then only accepting traffic
> and being secure against data loss, but I'd much rather that be an
> option and you could watch for your standby's state in a system view.
>
> Meanwhile, I can't understand any reason for the master to pretend it
> can safely manage any sync-rep transaction while there's no standby
> around. Either you wait for the quorum and don't have it, or you have to
> track standby states with precision and maybe actively reject writes.

I'm sorry, but I still don't understand the use case you're envisioning. 
How many standbys are there? What are you trying to achieve with 
synchronous replication over what asynchronous offers?

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


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/06/2010 04:20 PM, Simon Riggs wrote:
> Ending the wait state does not cause data loss. It puts you at *risk* of
> data loss, which is a different thing entirely.

These kind of risk scenarios is what sync replication is all about. A
minimum guarantee that doesn't hold in face of the first few failures
(see Jeff's argument) isn't worth a dime.

Keep in mind that upon failure, the other nodes presumably get more
load. As has been seen with RAID, that easily leads to subsequent
failures. Sync rep needs to be able to protect against that *as well*.

> If you want to avoid data loss you use N+k redundancy and get on with
> life, rather than sitting around waiting.

With that notion, I'd argue that quorum_commit needs to be set to
exactly k, because any higher value would only cost performance without
any useful benefit.

But if I want at least k ACKs and if I think it's worth the performance
penalty that brings during normal operation, I want that guarantee to
hold true *especially* in case of an emergency.

If availability is more important, you need to increase N and make sure
enough of these (asynchronously) replicated nodes stay up. Increase k
(thus quorum commit) for a stronger durability guarantee.

> Putting in a feature for people that choose k=0 seems wasteful to me,
> since they knowingly put themselves at risk in the first place.

Given the above logic, k=0 equals to completely async replication. Not
sure what's wrong about that.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> I'm sorry, but I still don't understand the use case you're envisioning. How
> many standbys are there? What are you trying to achieve with synchronous
> replication over what asynchronous offers?

Sorry if I've been unclear, I read loads of message then tried to pick
up the right one to answer, and obviously missed to spell out some
context.

My concern starts with only 1 standby, and is in fact 2 questions:
- Why o why you wouldn't be able to fix your sync setup in the master  as soon as there's a standby doing a base
backup?
- when do you start considering the standby as a candidate to your sync  rep requirements?

Lots of the discussion we're having are taking as an implicit that the
answer is "as soon as you know about its existence, that must be at the
pg_start_backup() point". I claim that's incorrect, and you can't ask
the master to wait forever until the standby is in sync. All the more
because there's a window with wal_keep_segments here too, so the sync
might never happen.

To solve that problem, I propose managing current state of the
standby. 
 That means auto registration of any standby, and feedback loop at more stages, and some protocol arbitrage for the
standbyto be able to say "I'm this far actually" so that the master can know how to consider it, rather than just
demoteit while live.
 

One you have a clear list of possible states for a standby, and can
decide on what errors are meaning in terms of transitions in the state
machine, you're able to decide when wait forever is an option and when
you should ignore it or refuse any side-effect transaction commit.

And you can offer an option to guarantee the wait-forever behavior only
when it makes sense, rather than trying to catch your own tail as soon
as a standby is added in the mix, with the proposals I've read on how
you can't even restart the master at this point.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Josh Berkus
Date:
All,

Let me clarify and consolidate this discussion.  Again, it's my goal
that this thread specifically identify only the problems and desired
behaviors for synch rep with more than one sync standby.  There are
several issues with even one sync standby which still remain unresolved,
but I believe that we should discuss those on a separate thread, for
clarity.

I also strongly believe that we should get single-standby functionality
committed and tested *first*, before working further on multi-standby.

So, to summarize earlier discussion on this thread:

There are 2 reasons to have more than one sync standby:

1) To increase durability above the level of a single synch standby,
even at the cost of availability.

2) To increase availability without decreasing durability below the
level offered by a single sync standby.

The "pure" setup for each of these options, where N is the number of
standbys and k is the number of acks required from standbys is:

1) k = N, N > 1, apply
2) k = 1, N > 1, recv

(Timeouts are a specific compromise of durability for availability on
*one* server, and as such will not be discussed here.  BTW, I was the
one who suggested a timeout, rather than Simon, so if you don't like the
idea, harass me about it.)

Any other configuration (3) than the two above is a specific compromise
between durability and availability, for example:

3a) k = 2, N = 3, fsync
3b) k = 3, N = 10, recv

... should give you better durability than case 2) and better
availability than case 1).

While it's nice to dismiss case (1) as an edge-case, consider the
likelyhood of someone running PostgreSQL with fsync=off on cloud
hosting.  In that case, having k = N = 5 does not seem like an
unreasonable arrangement if you want to ensure durability via
replication.  It's what the CAP databases do.

After eliminating some of my issues as non-issues, here's what we're
left with for problems on the above:

(1), (3) Accounting/Registration.  Implementing any of these cases would
seem to require some form of accounting and/or registration on the
master in terms of, at a minimum, the number of acks for each data send.More likely we will need, as proposed on other
threads,a register of
 
standbys and the sync state of each.  Not only will this
accounting/registration be hard code to write, it will have at least
*some* performance overhead.   Whether that overhead is minority or
substantial can only be determined through testing.  Further, there's
the issue of whether, and how, we transmit this register to the standbys
so that they can be promoted.

(2), (3) Degradation: (Jeff) these two cases make sense only if we give
DBAs the tools they need to monitor which standbys are falling behind,
and to drop and replace those standbys.  Otherwise we risk giving DBAs
false confidence that they have better-than-1-standby reliability when
actually they don't.  Current tools are not really adequate for this.

(1), (3) Dynamic Re-configuration: we need the ability to add and remove
standbys at runtime.  We also need to have a verdict on how to handle
the case where a transaction is pending, per Heikki.

(2), (3) Promotion: all multi-standby high-availability cases only make
sense if we provide tools to promote the most current standby to be the
new master.  Otherwise the whole cluster still goes down whenever we
have to replace the master.  We also should provide some mechanism for
promoting an async standby to sync; this has already been discussed.
(1) Consistency: this is another DBA-false-confidence issue.  DBAs who
implement (1) are liable to do so thinking that they are not only
guaranteeing the consistency of every standby with the master, but the
consistency of every standby with every other standby -- a kind of dummy
multi-master.  They are not, so it will take multiple reminders and
workarounds in the docs to explain this.  And we'll get complaints anyway.

(1), (2), (3) Initialization: (Dimitri) we need a process whereby a
standby can go from cloned to synched to being a sync rep standby, and
possibly from degraded to synced again and back.


--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
Hello Dimitri,

On 10/06/2010 05:41 PM, Dimitri Fontaine wrote:
>  - when do you start considering the standby as a candidate to your sync
>    rep requirements?

That question doesn't make much sense to me. There's no point in time I
ever mind if a standby is a "candidate" or not. Either I want to
synchronously replicate to X standbies, or not.

> Lots of the discussion we're having are taking as an implicit that the
> answer is "as soon as you know about its existence, that must be at the
> pg_start_backup() point".

This is an admin decision. Whether or not your standbies are up and
running or not, existing or just about to be bought, that doesn't have
any impact on your durability requirements. If you want your banking
accounts data to be saved in at least two different locations, I think
that's your requirement.

You'd be quite unhappy if your bank lost your last month's salary, but
stated: "hey, at least we didn't have any downtime".

> And you can offer an option to guarantee the wait-forever behavior only
> when it makes sense, rather than trying to catch your own tail as soon
> as a standby is added in the mix

Of course, it doesn't make sense to wait-forever on *every* standby that
ever gets added. Quorum commit is required, yes (and that's what this
thread is about, IIRC). But with quorum commit, adding a standby only
improves availability, but certainly doesn't block the master in any
way. (Quite the opposite: it can allow the master to continue, if it has
been blocked before because the quorum hasn't been reached).

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Markus Wanner <markus@bluegap.ch> writes:
> There's no point in time I
> ever mind if a standby is a "candidate" or not. Either I want to
> synchronously replicate to X standbies, or not.

Ok so I think we're agreeing here: what I said amounts to propose that
the code does work this way when the quorum is such setup, and/or is
able to reject any non-read-only transaction (those that needs a real
XID) until your standby is fully in sync.

I'm just saying that this should be an option, not the only choice.

And that by having a clear view of the system's state, it's possible to
have a clear error response policy set out.

> This is an admin decision. Whether or not your standbies are up and
> running or not, existing or just about to be bought, that doesn't have
> any impact on your durability requirements.

Depends, lots of things out there work quite well in best effort mode,
even if some projects needs more careful thinking. That's again the idea
of waiting forever or just continuing, there's a middle-ground which is
starting the system before reaching the durability requirements or
downgrading it to read only, or even off, until you get them.

You can read my proposal as a way to allow our users to choose between
those two incompatible behaviours.

> Of course, it doesn't make sense to wait-forever on *every* standby that
> ever gets added. Quorum commit is required, yes (and that's what this
> thread is about, IIRC). But with quorum commit, adding a standby only
> improves availability, but certainly doesn't block the master in any
> way. (Quite the opposite: it can allow the master to continue, if it has
> been blocked before because the quorum hasn't been reached).

If you ask for a quorum larger than what the current standbys are able
to deliver, and you're set to wait forever until the quorum is reached,
you just blocked the master.

Good news is that the quorum is a per-transaction setting, so opening a
superuser connection to act on the currently waiting transaction is
still possible (pass/fail, but fail is what at this point? shutdown to
wait some more offline?).

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 06.10.2010 20:57, Josh Berkus wrote:
> While it's nice to dismiss case (1) as an edge-case, consider the
> likelyhood of someone running PostgreSQL with fsync=off on cloud
> hosting.  In that case, having k = N = 5 does not seem like an
> unreasonable arrangement if you want to ensure durability via
> replication.  It's what the CAP databases do.

Seems reasonable, but what is a CAP database?

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


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/06/2010 09:04 PM, Dimitri Fontaine wrote:
> Ok so I think we're agreeing here: what I said amounts to propose that
> the code does work this way when the quorum is such setup, and/or is
> able to reject any non-read-only transaction (those that needs a real
> XID) until your standby is fully in sync.
> 
> I'm just saying that this should be an option, not the only choice.

I'm sorry, I just don't see the use case for a mode that drops
guarantees when they are most needed. People who don't need those
guarantees should definitely go for async replication instead.

What does a synchronous replication mode that falls back to async upon
failure give you, except for a severe degradation in performance during
normal operation? Why not use async right away in such a case?

> Depends, lots of things out there work quite well in best effort mode,
> even if some projects needs more careful thinking. That's again the idea
> of waiting forever or just continuing, there's a middle-ground which is
> starting the system before reaching the durability requirements or
> downgrading it to read only, or even off, until you get them.

In such cases the admin should be free to reconfigure the quorum. And
yes, a read-only mode might be feasible. Please just don't fool the
admin with a "best effort" things that guarantees nothing (but trouble).

> If you ask for a quorum larger than what the current standbys are able
> to deliver, and you're set to wait forever until the quorum is reached,
> you just blocked the master.

Correct. That's the intended behavior.

> Good news is that the quorum is a per-transaction setting

I definitely like the per-transaction thing.

> so opening a
> superuser connection to act on the currently waiting transaction is
> still possible (pass/fail, but fail is what at this point? shutdown to
> wait some more offline?).

Not sure I'm following here. The admin will be busy re-establishing
(connections to) standbies, killing transactions on the master doesn't
help anything - whether or not the master waits forever.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Josh Berkus
Date:
> Seems reasonable, but what is a CAP database?

Database based around the CAP theorem[1].  Cassandra, Dynamo,
Hypertable, etc.

For us, the equation is: CAD, as in Consistency, Availability,
Durability.  Pick any two, at best.  But it's a very similar bag of
issues as the ones CAP addresses.

[1]http://www.julianbrowne.com/article/viewer/brewers-cap-theorem

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Wed, 2010-10-06 at 18:04 +0300, Heikki Linnakangas wrote:

> The key is whether you are guaranteed to have zero data loss or not.

We agree that is an important question.

You seem willing to trade anything for that guarantee. I seek a more
pragmatic approach that balances availability and risk.

Those views are different, but not inconsistent. Oracle manages to offer
multiple options and so can we.

If you desire that, go for it. But don't try to stop others having a
simple, pragmatic approach. The code to implement your desired option is
more complex and really should come later. I don't in any way wish to
block that option in this release, or any other, but please don't try to
persuade people it's the only sensible option 'cos it damn well isn't.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Wed, 2010-10-06 at 10:57 -0700, Josh Berkus wrote:

> I also strongly believe that we should get single-standby
> functionality committed and tested *first*, before working further on
> multi-standby.

Yes, lets get k = 1 first.

With k = 1 the number of standbys is not limited, so we can still have
very robust and highly available architectures. So we mean
"first-acknowledgement-releases-waiters".

> (1) Consistency: this is another DBA-false-confidence issue.  DBAs who
> implement (1) are liable to do so thinking that they are not only
> guaranteeing the consistency of every standby with the master, but the
> consistency of every standby with every other standby -- a kind of
> dummy multi-master.  They are not, so it will take multiple reminders
> and workarounds in the docs to explain this.  And we'll get complaints
> anyway.

This puts the matter very clearly. Setting k = N is not as good an idea
as it sounds when first described.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Wed, 2010-10-06 at 10:57 -0700, Josh Berkus wrote:
> (2), (3) Degradation: (Jeff) these two cases make sense only if we
> give
> DBAs the tools they need to monitor which standbys are falling behind,
> and to drop and replace those standbys.  Otherwise we risk giving DBAs
> false confidence that they have better-than-1-standby reliability when
> actually they don't.  Current tools are not really adequate for this.

Current tools work just fine for identifying if a server is falling
behind. This improved in 9.0 to give fine-grained information. Nothing
more is needed here within the server.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/06/2010 10:01 PM, Simon Riggs wrote:
> The code to implement your desired option is
> more complex and really should come later.

I'm sorry, but I think of that exactly the opposite way. The timeout for
automatic continuation after waiting for a standby is the addition. The
wait state of the master is there anyway, whether or not it's bound by a
timeout. The timeout option should thus come later.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Markus Wanner <markus@bluegap.ch> writes:
>> I'm just saying that this should be an option, not the only choice.
>
> I'm sorry, I just don't see the use case for a mode that drops
> guarantees when they are most needed. People who don't need those
> guarantees should definitely go for async replication instead.

We're still talking about freezing the master and all the applications
when the first standby still has to do a base backup and catch-up to
where the master currently is, right?

> What does a synchronous replication mode that falls back to async upon
> failure give you, except for a severe degradation in performance during
> normal operation? Why not use async right away in such a case?

It's all about the standard case you're building, sync rep, and how to
manage errors. In most cases I want flexibility. Alert says standby is
down, you lost your durability requirements, so now I'm building a new
standby. Does it mean my applications are all off and the master
refusing to work? I sure hope I can choose about that, if possible per
application.

Next step, the old standby has been able to boot again, thanks to the
sysadmins who repaired it, so it's online again, and my replacement
machine is doing a base-backup. Are all the applications still
unavailable? I sure hope I have a word in this decision.

>> so opening a
>> superuser connection to act on the currently waiting transaction is
>> still possible (pass/fail, but fail is what at this point? shutdown to
>> wait some more offline?).
>
> Not sure I'm following here. The admin will be busy re-establishing
> (connections to) standbies, killing transactions on the master doesn't
> help anything - whether or not the master waits forever.

The idea here would be to be able to manually ACK a transaction that's
waiting forever, because you know it won't have an answer and you'd
prefer the application to just continue. But I see that's not a valid
use case for you.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 07.10.2010 12:52, Dimitri Fontaine wrote:
> Markus Wanner<markus@bluegap.ch>  writes:
>>> I'm just saying that this should be an option, not the only choice.
>>
>> I'm sorry, I just don't see the use case for a mode that drops
>> guarantees when they are most needed. People who don't need those
>> guarantees should definitely go for async replication instead.
>
> We're still talking about freezing the master and all the applications
> when the first standby still has to do a base backup and catch-up to
> where the master currently is, right?

Either that, or you configure your system for asynchronous replication 
first, and flip the switch to synchronous only after the standby has 
caught up. Setting up the first standby happens only once when you 
initially set up the system, or if you're recovering from a catastrophic 
loss of the standby.

>> What does a synchronous replication mode that falls back to async upon
>> failure give you, except for a severe degradation in performance during
>> normal operation? Why not use async right away in such a case?
>
> It's all about the standard case you're building, sync rep, and how to
> manage errors. In most cases I want flexibility. Alert says standby is
> down, you lost your durability requirements, so now I'm building a new
> standby. Does it mean my applications are all off and the master
> refusing to work?

Yes. That's why you want to have at least two standbys if you care about 
availability. Or if durability isn't that important to you after all, 
use asynchronous replication.

Of course, if in the heat of the moment the admin is willing to forge 
ahead without the standby, he can temporarily change the configuration 
in the master. If you want the standby to be rebuilt automatically, you 
can even incorporate that configuration change in the scripts too. The 
important point is that you or your scripts are in control, and you know 
at all times whether you can trust the standby or not. If the master 
makes such decisions automatically, you don't know if the standby is 
trustworthy (ie. guaranteed up-to-date) or not.

>>> so opening a
>>> superuser connection to act on the currently waiting transaction is
>>> still possible (pass/fail, but fail is what at this point? shutdown to
>>> wait some more offline?).
>>
>> Not sure I'm following here. The admin will be busy re-establishing
>> (connections to) standbies, killing transactions on the master doesn't
>> help anything - whether or not the master waits forever.
>
> The idea here would be to be able to manually ACK a transaction that's
> waiting forever, because you know it won't have an answer and you'd
> prefer the application to just continue. But I see that's not a valid
> use case for you.

I don't see anything wrong with having tools for admins to deal with the 
unexpected. I'm not sure overriding individual transactions is very 
useful though, more likely you'll want to take the whole server offline, 
or you want to change the config to allow all transactions to continue 
without the synchronous standby.

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


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Either that, or you configure your system for asynchronous replication
> first, and flip the switch to synchronous only after the standby has caught
> up. Setting up the first standby happens only once when you initially set up
> the system, or if you're recovering from a catastrophic loss of the
> standby.

Or if the standby is lagging and the master wal_keep_segments is not
sized big enough. Is that a catastrophic loss of the standby too?

>> It's all about the standard case you're building, sync rep, and how to
>> manage errors. In most cases I want flexibility. Alert says standby is
>> down, you lost your durability requirements, so now I'm building a new
>> standby. Does it mean my applications are all off and the master
>> refusing to work?
>
> Yes. That's why you want to have at least two standbys if you care about
> availability. Or if durability isn't that important to you after all, use
> asynchronous replication.

Agreed, that's a nice simple use case.

Another one is to say that I want sync rep when the standby is
available, but I don't have the budget for more. So I prefer a good
alerting system and low-budget-no-guarantee when the standby is down,
that's my risk evaluation.

> Of course, if in the heat of the moment the admin is willing to forge ahead
> without the standby, he can temporarily change the configuration in the
> master. If you want the standby to be rebuilt automatically, you can even
> incorporate that configuration change in the scripts too. The important
> point is that you or your scripts are in control, and you know at all times
> whether you can trust the standby or not. If the master makes such decisions
> automatically, you don't know if the standby is trustworthy (ie. guaranteed
> up-to-date) or not.

My proposal is that the master has the information to make the decision,
and the behavior is something you setup. Default to security, so wait
forever and block the applications, but could be set to ignore standby
that have not at least reached this state.

I don't see that you can make everybody happy without a knob here, and I
don't see how we can deliver one without a clear state diagram of the
standby possible current states and transitions.

The other alternative is to just don't care and accept the timeout as
being an option with the quorum, so that you just don't wait for the
quorum if so you want. It's much more dynamic and dangerous, but with a
good alerting system it'll be very popular I guess.

> I don't see anything wrong with having tools for admins to deal with the
> unexpected. I'm not sure overriding individual transactions is very useful
> though, more likely you'll want to take the whole server offline, or you
> want to change the config to allow all transactions to continue without the
> synchronous standby.

The question then is, should the new configuration alter running
transactions? My implicit was that I don't think so, and then I need
another facility, such as
 SELECT pg_cancel_quorum_wait(procpid)   FROM pg_stat_activity  WHERE waiting_quorum;

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Thu, 2010-10-07 at 11:46 +0200, Markus Wanner wrote:
> On 10/06/2010 10:01 PM, Simon Riggs wrote:
> > The code to implement your desired option is
> > more complex and really should come later.
> 
> I'm sorry, but I think of that exactly the opposite way. 

I see why you say that. Dimitri's suggestion is an enhancement on the
basic feature, just as Heikki's is. My reply was directed at Heikki, but
should also apply to Dimitri's idea also.

> The timeout for
> automatic continuation after waiting for a standby is the addition. The
> wait state of the master is there anyway, whether or not it's bound by a
> timeout. The timeout option should thus come later.

Adding timeout is very little code. We can take that out of the patch if
that's an objection.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/07/2010 01:08 PM, Simon Riggs wrote:
> Adding timeout is very little code. We can take that out of the patch if
> that's an objection.

Okay. If you take it out, we are at the wait-forever option, right?

If not, I definitely don't understand how you envision things to happen.
I've been asking [1] about that distinction before, but didn't get a
direct answer.

Regards

Markus Wanner


[1]: Re: Configuring synchronous replication, Markus Wanner:
http://archives.postgresql.org/message-id/4C9C5887.4040901@bluegap.ch


Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Thu, Oct 7, 2010 at 3:30 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Yes, lets get k = 1 first.
>
> With k = 1 the number of standbys is not limited, so we can still have
> very robust and highly available architectures. So we mean
> "first-acknowledgement-releases-waiters".

+1.  I like the design Greg Smith proposed yesterday (though there are
details to be worked out).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
Salut Dimitri,

On 10/07/2010 12:32 PM, Dimitri Fontaine wrote:
> Another one is to say that I want sync rep when the standby is
> available, but I don't have the budget for more. So I prefer a good
> alerting system and low-budget-no-guarantee when the standby is down,
> that's my risk evaluation.

I think that's a pretty special case, because the "good alerting system"
is at least as expensive as another server that just persistently stores
and ACKs incoming WAL.

Why does one ever want the guarantee that sync replication gives to only
hold true up to one failure, if a better guarantee doesn't cost anything
extra? (Note that a "good alerting system" is impossible to achieve with
only two servers. You need a third device anyway).

Or put another way: a "good alerting system" is one that understands
Postgres to some extent. It protects you from data loss in *every* case.
If you attach at least two database servers to it, you get availability
as long as any one of the two is up and running. No matter what happened
before, even a full cluster power outage is guaranteed to recover from
automatically without any data loss.

[ Okay, the standby mode that only stores and ACKs WAL without having a
full database behind still needs to be written. However, pg_streamrecv
certainly goes that direction already, see [1]. ]

Sync replication between really just two servers is asking for trouble
and certainly not worth the savings in hardware cost. Better invest in a
good UPS and redundant power supplies for a single server.

> The question then is, should the new configuration alter running
> transactions?

It should definitely affect all currently running and waiting
transactions. For anything beyond three servers, where quorum_commit
could be bigger than one, it absolutely makes sense to be able to just
lower the requirements temporarily, instead of having to cancel the
guarantee completely.

Regards

Markus Wanner


[1]: Using streaming replication as log archiving, Magnus Hagander
http://archives.postgresql.org/message-id/AANLkTi=_BzsYT8a1KjtpWZxNWyYgqNVp1NbJWRnsD_Nv@mail.gmail.com


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Markus Wanner <markus@bluegap.ch> writes:
> Why does one ever want the guarantee that sync replication gives to only
> hold true up to one failure, if a better guarantee doesn't cost anything
> extra? (Note that a "good alerting system" is impossible to achieve with
> only two servers. You need a third device anyway).

I think you're all into durability, and that's good. The extra cost is
service downtime if that's not what you're after: there's also
availability and load balancing read queries on a system with no lag (no
stale data servicing) when all is working right.

I still think your use case is a solid one, but that we need to be ready
to answer to some other ones, that you call relaxed and wrong because of
data loss risks. My proposal is to make the risk window obvious and the
behavior when you enter it configurable.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Aidan Van Dyk
Date:
On Thu, Oct 7, 2010 at 6:32 AM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:

> Or if the standby is lagging and the master wal_keep_segments is not
> sized big enough. Is that a catastrophic loss of the standby too?

Sure, but that lagged standy is already asynchrounous, not
synchrounous.  If it was synchronous, it would have slowed the master
down enough it would not be lagged.

I'm really confused with all this k < N scenarious I see bandied
about, because, all it really amounts to is "I only want *one*
syncronous replication, and a bunch of synchrounous replications".
And a bit of chance thrown in the mix to hope the "syncronous" one is
pretty stable, the asynchronous ones aren't *too* far behind (define
too and far at your leisure).

And then I see a lot of posturing about how to "recover" when the
"asynchronous standbys" aren't "synchronous enough" at some point...

>
> Agreed, that's a nice simple use case.
>
> Another one is to say that I want sync rep when the standby is
> available, but I don't have the budget for more. So I prefer a good
> alerting system and low-budget-no-guarantee when the standby is down,
> that's my risk evaluation.

That screems wrong in my books:

"OK, I want durability, so I always want to have 2 copies of the data,
but if we loose one, copy, I want to keep on trucking, because I don't
*really* want durability".

If you want most-of-the time mostly 2 copy durabiltiy, then really
good asynchronous replication is a really good solutions.

Yes, I believe you need to have a way for an admin (or
process/control/config) to be able to "demote" a synchronous
replication scenario into async (or "standalone", which is just an
extension of really async).  But it's no longer syncronous replication
at that point.  And if the choice is made to "keep trucking" while a
new standby is being brought online and available and caught up,
that's fine too.  But during that perioud, until the slave is caught
up and synchrounously replicating, it's *not* synchronous replication.

So I'm not arguing that there shouldn't be a way to turn of
synchronous replication once it's on.  Hopefully without having to
take down the cluster (pg instance  type cluster) But I am pleading
that there is a way to setup PG such that synchronous replication *is*
synchronously replicating, or things stop and backup until such a time
as it is.

a.

--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Aidan Van Dyk <aidan@highrise.ca> writes:
> Sure, but that lagged standy is already asynchrounous, not
> synchrounous.  If it was synchronous, it would have slowed the master
> down enough it would not be lagged.

Agreed, except in the case of a joining standby. But you're saying it
better than I do:

> Yes, I believe you need to have a way for an admin (or
> process/control/config) to be able to "demote" a synchronous
> replication scenario into async (or "standalone", which is just an
> extension of really async).  But it's no longer syncronous replication
> at that point.  And if the choice is made to "keep trucking" while a
> new standby is being brought online and available and caught up,
> that's fine too.  But during that perioud, until the slave is caught
> up and synchrounously replicating, it's *not* synchronous replication.

That's exactly my point. I think we need to handle the case and make it
obvious that this window is a data-loss window where there's no sync rep
ongoing, then offer users a choice of behaviour.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Aidan Van Dyk
Date:
On Thu, Oct 7, 2010 at 10:08 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Aidan Van Dyk <aidan@highrise.ca> writes:
>> Sure, but that lagged standy is already asynchrounous, not
>> synchrounous.  If it was synchronous, it would have slowed the master
>> down enough it would not be lagged.
>
> Agreed, except in the case of a joining standby.

*shrug*  The joining standby is still asynchronous at this point.
It's not synchronous replication.  It's just another ^k of the N
slaves serving stale data ;-)

>                                                   But you're saying it
> better than I do:
>
>> Yes, I believe you need to have a way for an admin (or
>> process/control/config) to be able to "demote" a synchronous
>> replication scenario into async (or "standalone", which is just an
>> extension of really async).  But it's no longer syncronous replication
>> at that point.  And if the choice is made to "keep trucking" while a
>> new standby is being brought online and available and caught up,
>> that's fine too.  But during that perioud, until the slave is caught
>> up and synchrounously replicating, it's *not* synchronous replication.
>
> That's exactly my point. I think we need to handle the case and make it
> obvious that this window is a data-loss window where there's no sync rep
> ongoing, then offer users a choice of behaviour.

Again, I'm stating there is *no* choice in synchronous replication.
It's *got* to block, otherwise it's not synchronous replication.  The
"choice" is if you want synchronous replication or not at that point.

And turning it off might be a good (best) choice for for most people.
I just want to make sure that:
1) There's now way to *sensibly* think it's still "synchronously replicating"
2) There is a way to enforce that the commits happening *are*
synchronously replicating.

a.

--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Aidan Van Dyk <aidan@highrise.ca> writes:
> *shrug*  The joining standby is still asynchronous at this point.
> It's not synchronous replication.  It's just another ^k of the N
> slaves serving stale data ;-)

Agreed *here*, but if you read the threads again, you'll see that's not
at all what's been talked about before my proposal.

In particular, the questions about how to unlock a master's setup while
its synced standby is doing a base backup should not be allowed to
exists, and you seem to agree with my point.

>> That's exactly my point. I think we need to handle the case and make it
>> obvious that this window is a data-loss window where there's no sync rep
>> ongoing, then offer users a choice of behaviour.
>
> Again, I'm stating there is *no* choice in synchronous replication.
> It's *got* to block, otherwise it's not synchronous replication.  The
> "choice" is if you want synchronous replication or not at that point.

Exactly, even if I didn't dare spell it this way.

What I want to propose is for the user to be able to configure things so
that he loses the sync aspect of the replication if it so happens that
the setup is not able to provide for it.

It may sound strange, but it's needed when all you want is a no stale
data reporting stanbdy, e.g. And it so happens that it's already in
Simon's code, AFAIUI (yet to read it, see).

> And turning it off might be a good (best) choice for for most people.
> I just want to make sure that:
> 1) There's now way to *sensibly* think it's still "synchronously replicating"
> 2) There is a way to enforce that the commits happening *are*
> synchronously replicating.

We're on the same track. I don't know how to offer your options without
a clear listing of standby states and transitions, which must include
the synchronicity and whether you just lost it or whatnot.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Greg Smith
Date:
Markus Wanner wrote:
> I think that's a pretty special case, because the "good alerting system"
> is at least as expensive as another server that just persistently stores
> and ACKs incoming WAL.
>   

The cost of hardware capable of running a database server is a large 
multiple of what you can build an alerting machine for.  I have two 
systems that are approaching the trash heap just at my house, relative 
to the main work I do, but that are fully capable of running an alerting 
system.  Building a production quality database server requires a more 
significant investment:  high quality disks, ECC RAM, battery-backed 
RAID controller, etc.  Relative to what the hardware in a database 
server costs, what you need to build an alerting system is almost free.  
Oh:  and most businesses that are complicated enough to need a serious 
database server already have them, so they actually cost nothing beyond 
the software setup time to point them toward the databases, too.

> Why does one ever want the guarantee that sync replication gives to only
> hold true up to one failure, if a better guarantee doesn't cost anything
> extra? (Note that a "good alerting system" is impossible to achieve with
> only two servers. You need a third device anyway).
>   

I do not disagree with your theory or reasoning.  But as a practical 
matter, I'm afraid the true cost of the better guarantee you're 
suggesting here is additional code complexity that will likely cause 
this feature to miss 9.1 altogether.  As far as I'm concerned, this 
whole diversion into the topic of quorum commit is only consuming 
resources away from targeting something achievable in the time frame of 
a single release.

> Sync replication between really just two servers is asking for trouble
> and certainly not worth the savings in hardware cost. Better invest in a
> good UPS and redundant power supplies for a single server.
>   

I wish I could give you the long list of data recovery projects I've 
worked on over the last few years, so you could really appreciate how 
much what you're saying here is exactly the opposite of the reality 
here.  You cannot make a single server reliable enough to survive all of 
the things that Murphy's Law will inflict upon it, at any price.  For 
most of the businesses I work with who want sync rep, data is not 
considered safe until the second copy is on storage miles away from the 
original, because they know this too.

Personal anecdote I can share:  I used to have an important project 
related to stock trading where I kept my backup system about 50 miles 
away from me.  I was aiming for constant availability, while still being 
able to drive to the other server if needed for disaster recovery.  
Guess what?  Even those two turned out not to be nearly independent 
enough; see http://en.wikipedia.org/wiki/Northeast_Blackout_of_2003 for 
details of how I lost both of those at the same time for days.  Silly 
me, I'd only spread them across two adjacent states with different power 
providers!  Not nearly good enough to avoid a correlated failure.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us




Re: Issues with Quorum Commit

From
Josh Berkus
Date:
On 10/7/10 6:41 AM, Aidan Van Dyk wrote:
> I'm really confused with all this k < N scenarious I see bandied
> about, because, all it really amounts to is "I only want *one*
> syncronous replication, and a bunch of synchrounous replications".
> And a bit of chance thrown in the mix to hope the "syncronous" one is
> pretty stable, the asynchronous ones aren't *too* far behind (define
> too and far at your leisure).

Effectively, yes.  The the difference between k of N synch rep and 1
synch standby + several async standbys is that in k of N, you have a
pool and aren't dependent on having a specific standby be very reliable,
just that any one of them is.

So if you have k = 3 and N = 10, then you can have 10 standbys and only
3 of them need to ack any specific commit for the master to proceed. As
long as (a) you retain at least one of the 3 which ack'd, and (b) you
have some way of determining which standby is the most "caught up", data
loss is fairly unlikely; you'd need to lose 4 of the 10, and the wrong
4, to lose data.

The advantage of this for availability over just having k = N = 3 comes
when one of the standbys is responding slowly (due to traffic) or goes
offline unexpectedly due to a hardware failure.  In the k = N = 3 case,
the system halts.  In the k = 3, N = 10 case, you can lose up to 7
standbys without the system going down.

It's notable that the massively scalable transactional databases
(Dynamo, Cassandra, various telecom databases, etc.) all operate this way.

However, I do consider this "advanced" functionality and not worth
pursuing until we have the k = 1 case implemented and well-tested.  For
comparison, Cassandra, Hypertable and Riak have been working on their k
< N functionality for a couple years now and none of them has it stable
*and* fast.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Aidan Van Dyk
Date:
On Thu, Oct 7, 2010 at 1:22 PM, Josh Berkus <josh@agliodbs.com> wrote:

> So if you have k = 3 and N = 10, then you can have 10 standbys and only
> 3 of them need to ack any specific commit for the master to proceed. As
> long as (a) you retain at least one of the 3 which ack'd, and (b) you
> have some way of determining which standby is the most "caught up", data
> loss is fairly unlikely; you'd need to lose 4 of the 10, and the wrong
> 4, to lose data.
>
> The advantage of this for availability over just having k = N = 3 comes
> when one of the standbys is responding slowly (due to traffic) or goes
> offline unexpectedly due to a hardware failure.  In the k = N = 3 case,
> the system halts.  In the k = 3, N = 10 case, you can lose up to 7
> standbys without the system going down.

Sure, but here is where I might not be following.

If you want "synchronous replication" because you want "query
availabilty" while making sure you're not getting "stale" queries from
all your slaves, than using your k < N (k = 3 and N - 10) situation is
screwing your self.

To get "non-stale" responses, you can only query those k=3 servers.
But you've shot your self in the foot because you don't know which
3/10 those will be.  The other 7 *are* stale (by definition).  They
talk about picking the "caught up" slave when the master fails, but
you actually need to do that for *every query*.

If you say they are "pretty close so by the time you get the query to
them they will be caught up", well then, all you really want is good
async replication, you don't really *need* the synchronous part.

The only case I see a "race to quorum" type of k < N being useful is
if you're just trying to duplicate data everywhere, but not actually
querying any of the replicas.  I can see that "all queries go to the
master, but the chances are pretty high the multiple machines are
going to fail so I want >> multiple replicas" being useful, but I
*don't* think that's what most people are wanting in their "I want 3
of 10 servers to ack the commit".

The difference between good async and sync is only the *guarentee*.
If you don't need the guarantee, you don't need the synchronous part.

a.


--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.


Re: Issues with Quorum Commit

From
Josh Berkus
Date:
> If you want "synchronous replication" because you want "query
> availabilty" while making sure you're not getting "stale" queries from
> all your slaves, than using your k < N (k = 3 and N - 10) situation is
> screwing your self.

Correct. If that is your reason for synch standby, then you should be
using k = N configuration.

However, some people are willing to sacrifice consistency for durability
and availability.  We should give them that option (eventually), since
among that triad you can never have more than two.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/07/2010 06:41 PM, Greg Smith wrote:
> The cost of hardware capable of running a database server is a large
> multiple of what you can build an alerting machine for.

You realize you don't need lots of disks nor RAM for a box that only
ACKs? A box with two SAS disks and a BBU isn't that expensive anymore.

> I do not disagree with your theory or reasoning.  But as a practical
> matter, I'm afraid the true cost of the better guarantee you're
> suggesting here is additional code complexity that will likely cause
> this feature to miss 9.1 altogether.  As far as I'm concerned, this
> whole diversion into the topic of quorum commit is only consuming
> resources away from targeting something achievable in the time frame of
> a single release.

So far I've been under the impression that Simon already has the code
for quorum_commit k = 1.

What I'm opposing to is the timeout "feature", which I consider to be
additional code, unneeded complexity and foot-gun.

> You cannot make a single server reliable enough to survive all of
> the things that Murphy's Law will inflict upon it, at any price.

That's exactly what I'm saying applies to two servers as well. And why a
timeout is a bad thing here, because the chance the second nodes fails
as well is there (and is higher than you think, according to Murphy).

> For
> most of the businesses I work with who want sync rep, data is not
> considered safe until the second copy is on storage miles away from the
> original, because they know this too.

Now, that are the people who really need sync rep, yes. What do you
think how happy those businesses were to find out that Postgres is
cheating on them in case of a network outage, for example? Do they
really value (write!) availability more than data safety?

> Silly
> me, I'd only spread them across two adjacent states with different power
> providers!  Not nearly good enough to avoid a correlated failure.

Thanks for sharing this. I hope you didn't loose data.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Josh Berkus
Date:
> But as a practical matter, I'm afraid the true cost of the better
> guarantee you're suggesting here is additional code complexity that will
> likely cause this feature to miss 9.1 altogether.  As far as I'm
> concerned, this whole diversion into the topic of quorum commit is only
> consuming resources away from targeting something achievable in the time
> frame of a single release.

Yes. My purpose in starting this thread was to show that k > 1 "quorum
commit" is considerably more complex than the people who have been
bringing it up in other threads seem to think it is.  It is not
achievable for 9.1, and maybe not even for 9.2.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
"Kevin Grittner"
Date:
Aidan Van Dyk <aidan@highrise.ca> wrote:
> To get "non-stale" responses, you can only query those k=3
> servers.  But you've shot your self in the foot because you don't
> know which 3/10 those will be.  The other 7 *are* stale (by
> definition). They talk about picking the "caught up" slave when
> the master fails, but you actually need to do that for *every
> query*.
With web applications, at least, you often don't care that the data
read is absolutely up-to-date, as long as the point in time doesn't
jump around from one request to the next.  When we have used load
balancing between multiple database servers (which has actually
become unnecessary for us lately because PostgreSQL has gotten so
darned fast!), we have established affinity between a session and
one of the database servers, so that if they became slightly out of
sync, data would not pop in and out of existence arbitrarily.  I
think a reasonable person could combine this technique with a "3 of
10" synchronous replication quorum to get both safe persistence of
data and reasonable performance.
I can also envision use cases where this would not be desirable.
-Kevin


Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Thu, Oct 7, 2010 at 2:10 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Aidan Van Dyk <aidan@highrise.ca> wrote:
>
>> To get "non-stale" responses, you can only query those k=3
>> servers.  But you've shot your self in the foot because you don't
>> know which 3/10 those will be.  The other 7 *are* stale (by
>> definition). They talk about picking the "caught up" slave when
>> the master fails, but you actually need to do that for *every
>> query*.
>
> With web applications, at least, you often don't care that the data
> read is absolutely up-to-date, as long as the point in time doesn't
> jump around from one request to the next.  When we have used load
> balancing between multiple database servers (which has actually
> become unnecessary for us lately because PostgreSQL has gotten so
> darned fast!), we have established affinity between a session and
> one of the database servers, so that if they became slightly out of
> sync, data would not pop in and out of existence arbitrarily.  I
> think a reasonable person could combine this technique with a "3 of
> 10" synchronous replication quorum to get both safe persistence of
> data and reasonable performance.
>
> I can also envision use cases where this would not be desirable.

Well, keep in mind all updates have to be done on the single master.
That works pretty well for fine-grained replication, but I don't think
it's very good for full-cluster replication.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: Issues with Quorum Commit

From
"Kevin Grittner"
Date:
Robert Haas <robertmhaas@gmail.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>> With web applications, at least, you often don't care that the
>> data read is absolutely up-to-date, as long as the point in time
>> doesn't jump around from one request to the next.  When we have
>> used load balancing between multiple database servers (which has
>> actually become unnecessary for us lately because PostgreSQL has
>> gotten so darned fast!), we have established affinity between a
>> session and one of the database servers, so that if they became
>> slightly out of sync, data would not pop in and out of existence
>> arbitrarily.  I think a reasonable person could combine this
>> technique with a "3 of 10" synchronous replication quorum to get
>> both safe persistence of data and reasonable performance.
>> 
>> I can also envision use cases where this would not be desirable.
> 
> Well, keep in mind all updates have to be done on the single
> master.  That works pretty well for fine-grained replication, but
> I don't think it's very good for full-cluster replication.
I'm completely failing to understand your point here.  Could you
restate another way?
-Kevin


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/07/2010 03:19 PM, Dimitri Fontaine wrote:
> I think you're all into durability, and that's good. The extra cost is
> service downtime

It's just *reduced* availability. That doesn't necessarily mean
downtime, if you combine cleverly with async replication.

> if that's not what you're after: there's also
> availability and load balancing read queries on a system with no lag (no
> stale data servicing) when all is working right.

All I'm saying is that those use cases are much better served with async
replication. Maybe together with something that warns and takes action
in case the standby's lag gets too big.

Or what kind of customers do you think really need a no-lag solution for
read-only queries? In the LAN case, the lag of async rep is negligible
and in the WAN case the latencies of sync rep are prohibitive.

> My proposal is to make the risk window obvious and the
> behavior when you enter it configurable.

I don't buy that. The risk calculation gets a lot simpler and obvious
with strict guarantees.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/07/2010 07:44 PM, Aidan Van Dyk wrote:
> The only case I see a "race to quorum" type of k < N being useful is
> if you're just trying to duplicate data everywhere, but not actually
> querying any of the replicas.  I can see that "all queries go to the
> master, but the chances are pretty high the multiple machines are
> going to fail so I want >> multiple replicas" being useful, but I
> *don't* think that's what most people are wanting in their "I want 3
> of 10 servers to ack the commit".

What else do you think they want it for, if not for protection against
data loss?

(Note that the queries don't need to go to the master exclusively if you
can live with some lag - and I think the vast majority of people can.
The zero data loss guarantee holds true in any case, though).

> The difference between good async and sync is only the *guarentee*.
> If you don't need the guarantee, you don't need the synchronous part.

Here we are exactly on the same page again.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Thu, Oct 7, 2010 at 2:31 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Robert Haas <robertmhaas@gmail.com> wrote:
>> Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>
>>> With web applications, at least, you often don't care that the
>>> data read is absolutely up-to-date, as long as the point in time
>>> doesn't jump around from one request to the next.  When we have
>>> used load balancing between multiple database servers (which has
>>> actually become unnecessary for us lately because PostgreSQL has
>>> gotten so darned fast!), we have established affinity between a
>>> session and one of the database servers, so that if they became
>>> slightly out of sync, data would not pop in and out of existence
>>> arbitrarily.  I think a reasonable person could combine this
>>> technique with a "3 of 10" synchronous replication quorum to get
>>> both safe persistence of data and reasonable performance.
>>>
>>> I can also envision use cases where this would not be desirable.
>>
>> Well, keep in mind all updates have to be done on the single
>> master.  That works pretty well for fine-grained replication, but
>> I don't think it's very good for full-cluster replication.
>
> I'm completely failing to understand your point here.  Could you
> restate another way?

Establishing an affinity between a session and one of the database
servers will only help if the traffic is strictly read-only.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Markus Wanner <markus@bluegap.ch> writes:
> I don't buy that. The risk calculation gets a lot simpler and obvious
> with strict guarantees.

Ok, I'm lost in the use cases and analysis.


I still don't understand why you want to consider the system already
synchronous when it's not, whatever is the guarantee you're asking for.

All I'm saying is that we should be able to know and show what the
current system is up to, and we should be able to offer sane reactions
in case of errors.

You're calling a sane reaction blocking the master entirely when the
standby ain't ready yet (it's still at the base backup state), and I can
live with that. As an option.

I say that either we go the lax quorum route, or we have to care for
details and summary the failure cases with precision, and the possible
responses with care. I don't see that possible without a clear state of
each element in the system, their transitions, and a way to derive the
global state of the distributed system out of that.

It might be that the simpler way to go here is what Greg Smith has been
proposing for a long time already, and again quite recently on this
thread: have all the information you need in a system table and offer to
run a user defined function to determine the state of the system.


I think we managed to show what Josh Berkus wanted to know now. That's a
quagmire here. Now, the problem I have is not Quorum Commit but the very
definition of synchronous replication and the system we're trying to
build. Not sure there's two of us wanting the same thing here.


Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
"Kevin Grittner"
Date:
Robert Haas <robertmhaas@gmail.com> wrote:
> Establishing an affinity between a session and one of the database
> servers will only help if the traffic is strictly read-only.
Thanks; I now see your point.
In our environment, that's pretty common.  Our most heavily used web
app (the one for which we have, at times, needed load balancing)
connects to the database with a read-only login.  Many of our web
apps do their writing by posting to queues which are handled at the
appropriate source database later.  (I had the opportunity to use
one of these "for real" last night, to fill in a juror questionnaire
after receiving a summons from the jury clerk in the county where I
live.)
Like I said, there are sane cases for this usage, but it won't fit
everybody.  I have no idea on percentages.
-Kevin


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Thu, 2010-10-07 at 13:44 -0400, Aidan Van Dyk wrote:

> To get "non-stale" responses, you can only query those k=3 servers.
> But you've shot your self in the foot because you don't know which
> 3/10 those will be.  The other 7 *are* stale (by definition).  They
> talk about picking the "caught up" slave when the master fails, but
> you actually need to do that for *every query*.

There is a big confusion around that point and I need to point out that
statement isn't accurate. It's taken me a long while to understand this.

Asking for k > 1 does *not* mean those servers are time synchronised.
All it means is that the master will stop waiting after 3
acknowledgements. There is no connection between the master receiving
acknowledgements and the standby applying changes received from master;
the standbys are all independent of one another.

In a bad case, those 3 acknowledgements might happen say 5 seconds apart
on the worst and best of the 3 servers. So the first standby to receive
the data could have applied the changes ~4.8 seconds prior to the 3rd
standby. There is still a chance of reading stale data on one standby,
but reading fresh data on another server. In most cases the time window
is small, but still exists.

The other 7 are stale with respect to the first 3. But then so are the
last 9 compared with the first one. The value of k has nothing
whatsoever to do with the time difference between the master and the
last standby to receive/apply the changes. The gap between first and
last standby (i.e. N, not k) is the time window during which a query
might/might not see a particular committed result.

So standbys are eventually consistent whether or not the master relies
on them to provide an acknowledgement. The only place where you can
guarantee non-stale data is on the master.

High values of k reduce the possibility of data loss, whereas expected
cluster availability is reduced as N - k gets smaller.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Thu, 2010-10-07 at 19:50 +0200, Markus Wanner wrote:

> So far I've been under the impression that Simon already has the code
> for quorum_commit k = 1.

I do, but its not a parameter. The k = 1 behaviour is hardcoded and
considerably simplifies the design. Moving to k > 1 is additional work,
slows things down and seems likely to be fragile.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Josh Berkus
Date:
All,

> Establishing an affinity between a session and one of the database
> servers will only help if the traffic is strictly read-only.

I think this thread has drifted very far away from anything we're going
to do for 9.1.  And seems to have little to do with synchronous replication.

Synch rep ensures durability.  It is not, by itself, a method of
ensuring consistency, nor does it pretend to be one.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Greg Smith
Date:
Markus Wanner wrote:
> So far I've been under the impression that Simon already has the code
> for quorum_commit k = 1.
>
> What I'm opposing to is the timeout "feature", which I consider to be
> additional code, unneeded complexity and foot-gun.
>   

Additional code?  Yes.  Foot-gun?  Yes.  Timeout should be disabled by 
default so that you get wait forever unless you ask for something 
different?  Probably.  Unneeded?  This is where we don't agree anymore.  
The example that Josh Berkus just sent to the list is a typical example 
of what I expect people to do here.  They'll use Sync Rep to maximize 
the odds a system failure doesn't cause any transaction loss.  They'll 
use good quality hardware on the master so it's unlikely to fail.  But 
when the database finds the standby unreachable, and it's left with the 
choice between either degrading into async rep or coming to a complete 
halt, you must give people the option of choosing to degrade instead 
after a timeout.  Let them set off the red flashing lights, sound the 
alarms, and pray the master doesn't go down until you can fix the 
problem.  But the choice to allow uptime concerns to win over the normal 
sync rep preferences, that's a completely valid business decision people 
will absolutely want to make in a way opposite of your personal 
preference here.

I don't see this as needing any implementation any more complicated than 
the usual way such timeouts are handled.  Note how long you've been 
trying to reach the standby.  Default to -1 for forever.  And if you hit 
the timeout, mark the standby as degraded and force them to do a proper 
resync when they disconnect.  Once that's done, then they can re-enter 
sync rep mode again, via the same process a new node would have done so.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book



Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 6, 2010 at 6:11 PM, Markus Wanner <markus@bluegap.ch> wrote:
> Yeah, sounds more likely. Then I'm surprised that I didn't find any
> warning that the Protocol C definitely reduces availability (with the
> ko-count=0 default, that is).

Really? I don't think that ko-count=0 means "wait-forever". IIRC,
when I tried DRBD, I can write data in master's DRBD disk, without
connected standby. So I think that by default the master waits for
timeout and works alone when the standby goes down.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 6, 2010 at 6:00 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> In general, salvaging the WAL that was not sent to the standby yet is
> outright impossible. You can't achieve zero data loss with asynchronous
> replication at all.

No. That depends on the type of failure. Unless the disk in the master has
been corrupted, we might be able to salvage WAL.

>> If we want only no data loss, we have only to implement the wait-forever
>> option. But if we make consideration for the above-mentioned availability,
>> the return-immediately option also would be required.
>>
>> In some (many, I think) cases, I think that we need to consider
>> availability
>> and no data loss together, and consider the balance of them.
>
> If you need both, you need three servers as Simon pointed out earlier. There
> is no way around that.

No. That depends on how far you'd like to ensure no data loss.

Poeple who use shared disk failover solution with one master and one standby
don't such a high durability. They can avoid data loss by using something
like RAID to a certain extent. So it's not problem for them to run the master
alone after failover happens or standby goes down. But something like RAID
cannot increase availability. Synchronous replication is solution for that
purpose.

Of course, if we are worried about running the master alone, we can increase
the number of standbys. Furthermore, if we'd like to avoid data loss from the
disaster which can destroy all the servers at the same time, we might need to
increase the standbys further and locate some of them in the remote site.

Please imagine that "return-immediately (i.e., timeout = small)" is useful
for some use cases.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Thu, Oct 7, 2010 at 10:24 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Wed, Oct 6, 2010 at 6:00 PM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com> wrote:
>> In general, salvaging the WAL that was not sent to the standby yet is
>> outright impossible. You can't achieve zero data loss with asynchronous
>> replication at all.
>
> No. That depends on the type of failure. Unless the disk in the master has
> been corrupted, we might be able to salvage WAL.

So I guess another way to say this is that zero data loss is
unachievable, period.  Greg Smith made a flip comment about having
been so silly as to only put his redundant servers in adjacent states
on different power grids, and yet still having an outage due to the
Northeast blackouts.  So what would he have had to do to completely
rule out a correlated failure?

Answer: It can't be done.  If a massive asteroid comes zooming into
the inner solar system tomorrow and hits the earth, obliterating all
life, you're toast.  Or likewise if nuclear war ensues.  You could put
your redundant server on the moon or, better yet, on a moon of one of
the outer planets, but the hosting costs are pretty high and the ping
times suck.

So the point is that the question is not whether or not a correlated
failure can happen, but whether you can imagine a scenario where a
correlated failure has occurred yet you still wish you had your data.
Different people will, obviously, draw that line in different places.
Let's start by doing something simple that covers SOME of the cases
people want, get it committed, and then move on from there.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 6, 2010 at 9:22 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
> From my experience operating londiste, those states would be:
>
>  1. base-backup  — self explaining
>  2. catch-up     — getting the WAL to catch up after base backup
>  3. wanna-sync   — don't yet have all the WAL to get in sync
>  4. do-sync      — all WALs are there, coming soon
>  5. ok (async | recv | fsync | reply — feedback loop engaged)

I agree to mange these standby states in a different standpoint.

To avoid data loss, we must not promote the standby which is
catching up with the master in half way to the new master at the
failover. If clusterware can get the current standby state via SQL,
it can check whether the failover causes data loss or not
and give up failover before creating the trigger file.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Thu, Oct 7, 2010 at 5:01 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> You seem willing to trade anything for that guarantee. I seek a more
> pragmatic approach that balances availability and risk.
>
> Those views are different, but not inconsistent. Oracle manages to offer
> multiple options and so can we.

+1

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Thu, Oct 7, 2010 at 3:01 AM, Markus Wanner <markus@bluegap.ch> wrote:
> Of course, it doesn't make sense to wait-forever on *every* standby that
> ever gets added. Quorum commit is required, yes (and that's what this
> thread is about, IIRC). But with quorum commit, adding a standby only
> improves availability, but certainly doesn't block the master in any
> way.

But, even with quorum commit, if you choose wait-forever option,
failover would decrease availability. Right after the failover,
no standby has connected to new master, so if quorum >= 1, all
the transactions must wait for a while.

Basically we need to take a base backup from new master to start
the standbys and make them connect to new master. This might take
a long time. Since transaction commits cannot advance for that time,
availability would goes down.

Or you think that wait-forever option is applied only when the
standby goes down?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
"Joshua D. Drake"
Date:
On Thu, 2010-10-07 at 19:44 -0400, Greg Smith wrote:

> I don't see this as needing any implementation any more complicated than 
> the usual way such timeouts are handled.  Note how long you've been 
> trying to reach the standby.  Default to -1 for forever.  And if you hit 
> the timeout, mark the standby as degraded and force them to do a proper 
> resync when they disconnect.  Once that's done, then they can re-enter 
> sync rep mode again, via the same process a new node would have done so.

What I don't understand is why this isn't obvious to everyone. Greg this
is very well put and the -hackers need to start thinking like people
that actually use the database.

JD
-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt



Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Fri, Oct 8, 2010 at 8:44 AM, Greg Smith <greg@2ndquadrant.com> wrote:
> Additional code?  Yes.  Foot-gun?  Yes.  Timeout should be disabled by
> default so that you get wait forever unless you ask for something different?
>  Probably.  Unneeded?  This is where we don't agree anymore.  The example
> that Josh Berkus just sent to the list is a typical example of what I expect
> people to do here.  They'll use Sync Rep to maximize the odds a system
> failure doesn't cause any transaction loss.  They'll use good quality
> hardware on the master so it's unlikely to fail.  But when the database
> finds the standby unreachable, and it's left with the choice between either
> degrading into async rep or coming to a complete halt, you must give people
> the option of choosing to degrade instead after a timeout.  Let them set off
> the red flashing lights, sound the alarms, and pray the master doesn't go
> down until you can fix the problem.  But the choice to allow uptime concerns
> to win over the normal sync rep preferences, that's a completely valid
> business decision people will absolutely want to make in a way opposite of
> your personal preference here.

Definitely agreed.

> I don't see this as needing any implementation any more complicated than the
> usual way such timeouts are handled.  Note how long you've been trying to
> reach the standby.  Default to -1 for forever.  And if you hit the timeout,
> mark the standby as degraded and force them to do a proper resync when they
> disconnect.  Once that's done, then they can re-enter sync rep mode again,
> via the same process a new node would have done so.

Fair enough.

One question is when this timeout is applied. Obviously it should be applied
when the standby goes down. But timeout should be applied when we initially
start the master, and when no standby has not connected to new master yet after
failover?

I guess that people who want wait-forever would want to use "timeout = -1"
for all those cases. Otherwise they cannot ensure their no data loss.

OTOH, people who don't want wait-forever would not want to wait for timeout
in the latter two cases. So ISTM that something like enable_wait_forever or
reaction_after_timeout parameter is required separately from the timeout.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Greg Smith <greg@2ndquadrant.com> writes:
[…]
> I don't see this as needing any implementation any more complicated than the
> usual way such timeouts are handled.  Note how long you've been trying to
> reach the standby.  Default to -1 for forever.  And if you hit the timeout,
> mark the standby as degraded and force them to do a proper resync when they
> disconnect.  Once that's done, then they can re-enter sync rep mode again,
> via the same process a new node would have done so.

Thank you for this post, which is so much better than anything I could
achieve.

Just wanted to add that it should be possible in lots of cases to have a
standby rejoin the party without getting as far back as taking a new
base backup. Depends on wal_keep_segments and standby's degraded state,
among other parameters (archives, etc).

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 12:30 AM, Simon Riggs wrote:
> I do, but its not a parameter. The k = 1 behaviour is hardcoded and
> considerably simplifies the design. Moving to k > 1 is additional work,
> slows things down and seems likely to be fragile.

Perfect! So I'm all in favor of committing that, but leaving away the
timeout thing, which I think is just adding unneeded complexity and
fragility.

Regards

Markus Wanner




Re: Issues with Quorum Commit

From
Markus Wanner
Date:
Simon,

On 10/08/2010 12:25 AM, Simon Riggs wrote:
> Asking for k > 1 does *not* mean those servers are time synchronised.

Yes, it's technically impossible to create a fully synchronized cluster
(on the basis of shared-nothing nodes we are aiming for, that is). There
always is some kind of "lag" on either side.

Maybe the use case for a no-lag cluster doesn't exist, because it's
technically not feasible.

> In a bad case, those 3 acknowledgements might happen say 5 seconds apart
> on the worst and best of the 3 servers. So the first standby to receive
> the data could have applied the changes ~4.8 seconds prior to the 3rd
> standby. There is still a chance of reading stale data on one standby,
> but reading fresh data on another server. In most cases the time window
> is small, but still exists.

Well, the transaction isn't committed on the master, so one could argue
it shouldn't matter. The guarantee just needs to be one way: as soon as
confirmed committed to the client, all k standbies need to have it
committed, too. (At least for the "apply" replication level).

> So standbys are eventually consistent whether or not the master relies
> on them to provide an acknowledgement. The only place where you can
> guarantee non-stale data is on the master.

That's formulated a bit too strong. With "apply" replication level, you
should be able to rely on the guarantee that a committed transaction is
visible on at least k standbies. Maybe in advance of the commit on the
master, but I wouldn't call that "stale" data.

Given the current proposals, the master is the one that's "lagging" the
most, compared to the k standbies.

> High values of k reduce the possibility of data loss, whereas expected
> cluster availability is reduced as N - k gets smaller.

Exactly. One addendum: a timeout increases availability at the cost of
increased danger of data loss and higher complexity. Don't use it, just
increase (N - k) instead.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 07.10.2010 21:38, Markus Wanner wrote:
> On 10/07/2010 03:19 PM, Dimitri Fontaine wrote:
>> I think you're all into durability, and that's good. The extra cost is
>> service downtime
>
> It's just *reduced* availability. That doesn't necessarily mean
> downtime, if you combine cleverly with async replication.
>
>> if that's not what you're after: there's also
>> availability and load balancing read queries on a system with no lag (no
>> stale data servicing) when all is working right.
>
> All I'm saying is that those use cases are much better served with async
> replication. Maybe together with something that warns and takes action
> in case the standby's lag gets too big.
>
> Or what kind of customers do you think really need a no-lag solution for
> read-only queries? In the LAN case, the lag of async rep is negligible
> and in the WAN case the latencies of sync rep are prohibitive.

There is a very good use case for that particular set up, actually. If 
your hot standby is guaranteed to be up-to-date with any transaction 
that has been committed in the master, you can use the standby 
interchangeably with the master for read-only queries. Very useful for 
load balancing. Imagine a web application that's mostly read-only, but a 
user can modify his own personal details like name and address, for 
example. Imagine that the user changes his street address and clicks 
'save', causing an UPDATE, and the next query fetches that information 
again to display to the user. If you use load balancing, the query can 
be routed to the hot standby server, and if it lags even 1-2 seconds 
behind it's quite possible that it will still return the old address. 
The user will go "WTF, I just changed that!".

That's the "load balancing" use case, which is quite different from the 
"zero data loss on server failure" use case that most people here seem 
to be interested in.

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


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 04:01 AM, Fujii Masao wrote:
> Really? I don't think that ko-count=0 means "wait-forever".

Telling from the documentation, I'd also say it doesn't wait forever by
default. However, please note that there are different parameters for
the initial wait for connection during boot up (wfc-timeout and
degr-wfc-timeout). So you might to test what happens on a node failure,
not just absence of a standby.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 08.10.2010 06:41, Fujii Masao wrote:
> On Thu, Oct 7, 2010 at 3:01 AM, Markus Wanner<markus@bluegap.ch>  wrote:
>> Of course, it doesn't make sense to wait-forever on *every* standby that
>> ever gets added. Quorum commit is required, yes (and that's what this
>> thread is about, IIRC). But with quorum commit, adding a standby only
>> improves availability, but certainly doesn't block the master in any
>> way.
>
> But, even with quorum commit, if you choose wait-forever option,
> failover would decrease availability. Right after the failover,
> no standby has connected to new master, so if quorum>= 1, all
> the transactions must wait for a while.

Sure, the new master can't proceed with commits until enough standbys 
have connected to it.

> Basically we need to take a base backup from new master to start
> the standbys and make them connect to new master.

Do we really need that? I don't think that's acceptable, we'll need to 
fix that if that's the case.

I think you're right, streaming replication doesn't work across timeline 
changes. We left that out of 9.0, to keep things simple, but it seems 
that we really should fix that for 9.1.

You can cross timelines with the archive, though. But IIRC there was 
some issue with that too, you needed to restart the standbys because the 
standby scans what timelines exist at the beginning of recovery, and 
won't notice new timelines that appear after that?

We need to address that, apart from any of the other things discussed 
wrt. synchronous replication. It will benefit asynchronous replication 
too. IMHO *that* is the next thing we should do, the next patch we commit.

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


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Fri, 2010-10-08 at 09:52 +0200, Markus Wanner wrote:

> One addendum: a timeout increases availability at the cost of
> increased danger of data loss and higher complexity. Don't use it,
> just increase (N - k) instead. 

Completely agree.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 05:41 AM, Fujii Masao wrote:
> But, even with quorum commit, if you choose wait-forever option,
> failover would decrease availability. Right after the failover,
> no standby has connected to new master, so if quorum >= 1, all
> the transactions must wait for a while.

That's a point, yes. But again, this is just write-availability, you can
happily read from all active standbies. And connection time is certainly
negligible compared to any kind of timeout (which certainly needs to be
way bigger than a couple of network round-trips).

> Basically we need to take a base backup from new master to start
> the standbys and make them connect to new master. This might take
> a long time. Since transaction commits cannot advance for that time,
> availability would goes down.

Just don't increase your quorum_commit to unreasonable values which your
hardware cannot possible satisfy. It doesn't make sense to set a
quorum_commit of 1 or even bigger, if you don't already have a standby
attached.

Start with 0 (i.e. replication off), then add standbies, then increase
quorum_commit to your new requirements.

> Or you think that wait-forever option is applied only when the
> standby goes down?

That wouldn't work in case of a full-cluster crash, where the
wait-forever option is required again. Otherwise you risk a split-brain
situation.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 08.10.2010 01:25, Simon Riggs wrote:
> On Thu, 2010-10-07 at 13:44 -0400, Aidan Van Dyk wrote:
>
>> To get "non-stale" responses, you can only query those k=3 servers.
>> But you've shot your self in the foot because you don't know which
>> 3/10 those will be.  The other 7 *are* stale (by definition).  They
>> talk about picking the "caught up" slave when the master fails, but
>> you actually need to do that for *every query*.
>
> There is a big confusion around that point and I need to point out that
> statement isn't accurate. It's taken me a long while to understand this.
>
> Asking for k>  1 does *not* mean those servers are time synchronised.
> All it means is that the master will stop waiting after 3
> acknowledgements. There is no connection between the master receiving
> acknowledgements and the standby applying changes received from master;
> the standbys are all independent of one another.
>
> In a bad case, those 3 acknowledgements might happen say 5 seconds apart
> on the worst and best of the 3 servers. So the first standby to receive
> the data could have applied the changes ~4.8 seconds prior to the 3rd
> standby. There is still a chance of reading stale data on one standby,
> but reading fresh data on another server. In most cases the time window
> is small, but still exists.
>
> The other 7 are stale with respect to the first 3. But then so are the
> last 9 compared with the first one. The value of k has nothing
> whatsoever to do with the time difference between the master and the
> last standby to receive/apply the changes. The gap between first and
> last standby (i.e. N, not k) is the time window during which a query
> might/might not see a particular committed result.
>
> So standbys are eventually consistent whether or not the master relies
> on them to provide an acknowledgement. The only place where you can
> guarantee non-stale data is on the master.

Yes, that's a good point. Synchronous replication for load-balancing 
purposes guarantees that when *you* perform a commit, after it finishes 
it will be visible in all standbys. But if you run the same query across 
different standbys, you're not guaranteed get same results. If you just 
pick a random server for every query, you might even see time moving 
backwards. Affinity is definitely a good idea for the load-balancing 
scenario, but even then the anomaly is possible if you get re-routed to 
a different server because the one you were bound to dies.

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


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Fri, 2010-10-08 at 10:56 +0300, Heikki Linnakangas wrote:
> >
> > Or what kind of customers do you think really need a no-lag solution for
> > read-only queries? In the LAN case, the lag of async rep is negligible
> > and in the WAN case the latencies of sync rep are prohibitive.
> 
> There is a very good use case for that particular set up, actually. If 
> your hot standby is guaranteed to be up-to-date with any transaction 
> that has been committed in the master, you can use the standby 
> interchangeably with the master for read-only queries. 

This is an important point. It is desirable, but there is no such thing.
We must not take any project decisions based upon that false premise.

Hot Standby is never guaranteed to be up-to-date with master. There is
no such thing as certainty that you have the same data as the master.

All sync rep gives you is a better durability guarantee that the changes
are safe. It doesn't guarantee those changes are transferred to all
nodes prior to making the data changes on any one standby.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 08.10.2010 11:25, Simon Riggs wrote:
> On Fri, 2010-10-08 at 10:56 +0300, Heikki Linnakangas wrote:
>>>
>>> Or what kind of customers do you think really need a no-lag solution for
>>> read-only queries? In the LAN case, the lag of async rep is negligible
>>> and in the WAN case the latencies of sync rep are prohibitive.
>>
>> There is a very good use case for that particular set up, actually. If
>> your hot standby is guaranteed to be up-to-date with any transaction
>> that has been committed in the master, you can use the standby
>> interchangeably with the master for read-only queries.
>
> This is an important point. It is desirable, but there is no such thing.
> We must not take any project decisions based upon that false premise.
>
> Hot Standby is never guaranteed to be up-to-date with master. There is
> no such thing as certainty that you have the same data as the master.

Synchronous replication in the 'replay' mode is supposed to guarantee 
exactly that, no?

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


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 10:27 AM, Heikki Linnakangas wrote:
> Synchronous replication in the 'replay' mode is supposed to guarantee
> exactly that, no?

The master may lag behind, so it's not strictly speaking the same data.

Regards

Markus Wanner



Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 09:56 AM, Heikki Linnakangas wrote:
> Imagine a web application that's mostly read-only, but a
> user can modify his own personal details like name and address, for
> example. Imagine that the user changes his street address and clicks
> 'save', causing an UPDATE, and the next query fetches that information
> again to display to the user.

I don't think that use case justifies sync replication and the
additional network overhead that brings. Latency is low in that case,
okay, but so is the lag for async replication.

Why not tell the load balancer to read from the master for n seconds
after the last write. After that, it should be save to query standbies,
again.

If the load on the master is the problem, and you want to reduce that by
moving the read-only transactions to the slave, sync replication pretty
certainly won't help you, either, because it actually *increases*
concurrency (by increased commit latency).

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Fri, 2010-10-08 at 11:27 +0300, Heikki Linnakangas wrote:
> On 08.10.2010 11:25, Simon Riggs wrote:
> > On Fri, 2010-10-08 at 10:56 +0300, Heikki Linnakangas wrote:
> >>>
> >>> Or what kind of customers do you think really need a no-lag solution for
> >>> read-only queries? In the LAN case, the lag of async rep is negligible
> >>> and in the WAN case the latencies of sync rep are prohibitive.
> >>
> >> There is a very good use case for that particular set up, actually. If
> >> your hot standby is guaranteed to be up-to-date with any transaction
> >> that has been committed in the master, you can use the standby
> >> interchangeably with the master for read-only queries.
> >
> > This is an important point. It is desirable, but there is no such thing.
> > We must not take any project decisions based upon that false premise.
> >
> > Hot Standby is never guaranteed to be up-to-date with master. There is
> > no such thing as certainty that you have the same data as the master.
> 
> Synchronous replication in the 'replay' mode is supposed to guarantee 
> exactly that, no?

>From the perspective of the person making the change on the master: yes.
If they make the change, wait for commit, then check the value on a
standby, yes it will be there (or a later version).

>From the perspective of an observer, randomly selecting a standby for
load balancing purposes: No, they are not guaranteed to see the "latest"
answer, nor even can they find out whether what they are seeing is the
latest answer.

What sync rep does guarantee is that if the person making the change is
told it succeeded (commit) then that change is safe on at least k other
servers. Sync rep is about guarantees of safety, not observability.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 01:44 AM, Greg Smith wrote:
> They'll use Sync Rep to maximize
> the odds a system failure doesn't cause any transaction loss.  They'll
> use good quality hardware on the master so it's unlikely to fail.

.."unlikely to fail"?

Ehm.. is that you speaking, Greg? ;-)

> But
> when the database finds the standby unreachable, and it's left with the
> choice between either degrading into async rep or coming to a complete
> halt, you must give people the option of choosing to degrade instead
> after a timeout.  Let them set off the red flashing lights, sound the
> alarms, and pray the master doesn't go down until you can fix the
> problem.

Okay, okay, fair enough - if there had been red flashing lights. And
alarms. And bells and whistles. But that's what I'm afraid the timeout
is removing.

> I don't see this as needing any implementation any more complicated than
> the usual way such timeouts are handled.  Note how long you've been
> trying to reach the standby.  Default to -1 for forever.  And if you hit
> the timeout, mark the standby as degraded

..and how do you make sure you are not marking your second standby as
degraded just because it's currently lagging? Effectively degrading the
utterly needed one, because your first standby has just bitten the dust?

And how do you prevent the split brain situation in case the master dies
shortly after these events, but fails to come up again immediately?

Your list of data recovery projects will get larger and the projects
more complicated. Because there's a lot more to it than just the
implementation of a timeout.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 11:00 AM, Simon Riggs wrote:
> From the perspective of an observer, randomly selecting a standby for
> load balancing purposes: No, they are not guaranteed to see the "latest"
> answer, nor even can they find out whether what they are seeing is the
> latest answer.

I completely agree. The application (or at least the load balancer)
needs to be aware of that fact.

Regards

Markus


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Markus Wanner <markus@bluegap.ch> writes:
> ..and how do you make sure you are not marking your second standby as
> degraded just because it's currently lagging? 

Well, in sync rep, a standby that's not able to stay under the timeout
is degraded. Full stop. The presence of the timeout (or its value not
being -1) means that the admin has chosen this definition.

> Effectively degrading the
> utterly needed one, because your first standby has just bitten the
> dust?

Well, now you have a worst case scenario: first standby is dead and the
remaining one was not able to keep up. You have lost all your master's
failover replacements.

> And how do you prevent the split brain situation in case the master dies
> shortly after these events, but fails to come up again immediately?

Same old story. Either you're able to try and fix the master so that you
don't lose any data and don't even have to check for that, or you take a
risk and start from a non synced standby. It's all availability against
durability again.

What I really want us to be able to provide is the clear facts so that
whoever has to take the decision is able to. Meaning, here, that it
should be easy to see that neither the standby are in sync at this
point.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 11:41 AM, Dimitri Fontaine wrote:
> Same old story. Either you're able to try and fix the master so that you
> don't lose any data and don't even have to check for that, or you take a
> risk and start from a non synced standby. It's all availability against
> durability again.

..and a whole lot of manual work, that's prone to error for something
that could easily be automated, at certainly less than 2000 EUR initial,
additional cost (if any at all, in case you already have three servers).
Sorry, I still fail to understand that use case.

It reminds me of the customer that wanted to save the cost of the BBU
and ran with fsync=off. Until his server got down due to a power outage.

But yeah, we provide that option as well, yes. Point taken.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Markus Wanner <markus@bluegap.ch> writes:
> ..and a whole lot of manual work, that's prone to error for something
> that could easily be automated

So, the master just crashed, first standby is dead and second ain't in
sync. What's the easy and automated way out? Sorry, I need a hand here.

-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Fri, Oct 8, 2010 at 5:07 PM, Markus Wanner <markus@bluegap.ch> wrote:
> On 10/08/2010 04:01 AM, Fujii Masao wrote:
>> Really? I don't think that ko-count=0 means "wait-forever".
>
> Telling from the documentation, I'd also say it doesn't wait forever by
> default. However, please note that there are different parameters for
> the initial wait for connection during boot up (wfc-timeout and
> degr-wfc-timeout). So you might to test what happens on a node failure,
> not just absence of a standby.

Unfortunately I've already taken down my DRBD environment. As far as
I heard from my colleague who is familiar with DRBD, standby node
failure doesn't prevent the master from writing data to the DRBD disk
by default. If there is DRBD environment available around me, I'll try
the test.

And, I'd like to know whether the master waits forever because of the
standby failure in other solutions such as Oracle DataGuard, MySQL
semi-synchronous replication.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Tom Lane
Date:
Greg Smith <greg@2ndquadrant.com> writes:
> I don't see this as needing any implementation any more complicated than 
> the usual way such timeouts are handled.  Note how long you've been 
> trying to reach the standby.  Default to -1 for forever.  And if you hit 
> the timeout, mark the standby as degraded and force them to do a proper 
> resync when they disconnect.  Once that's done, then they can re-enter 
> sync rep mode again, via the same process a new node would have done so.

Well, actually, that's *considerably* more complicated than just a
timeout.  How are you going to "mark the standby as degraded"?  The
standby can't keep that information, because it's not even connected
when the master makes the decision.  ISTM that this requires

1. a unique identifier for each standby (not just role names that
multiple standbys might share);

2. state on the master associated with each possible standby -- not just
the ones currently connected.

Both of those are perhaps possible, but the sense I have of the
discussion is that people want to avoid them.

Actually, #2 seems rather difficult even if you want it.  Presumably
you'd like to keep that state in reliable storage, so it survives master
crashes.  But how you gonna commit a change to that state, if you just
lost every standby (suppose master's ethernet cable got unplugged)?
Looks to me like it has to be reliable non-replicated storage.  Leaving
aside the question of how reliable it can really be if not replicated,
it's still the case that we have noplace to put such information given
the WAL-is-across-the-whole-cluster design.
        regards, tom lane


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Fri, Oct 8, 2010 at 5:10 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> Do we really need that?

Yes. But if there is no unsent WAL when the master goes down,
we can start new standby without new backup by copying the
timeline history file from new master to new standby and
setting recovery_target_timeline to 'latest'. In this case,
new standby advances the recovery to the latest timeline ID
which new master uses before connecting to the master.

This seems to have been successful in my test environment.
Though I'm missing something.

> I don't think that's acceptable, we'll need to fix
> that if that's the case.

Agreed.

> You can cross timelines with the archive, though. But IIRC there was some
> issue with that too, you needed to restart the standbys because the standby
> scans what timelines exist at the beginning of recovery, and won't notice
> new timelines that appear after that?

Yes.

> We need to address that, apart from any of the other things discussed wrt.
> synchronous replication. It will benefit asynchronous replication too. IMHO
> *that* is the next thing we should do, the next patch we commit.

You mean to commit that capability before synchronous replication? If so,
I disagree with you. I think that it's not easy to address that problem.
So I'm worried about that implementing that capability first means the miss
of sync rep in 9.1.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 04:11 PM, Tom Lane wrote:
> Actually, #2 seems rather difficult even if you want it.  Presumably
> you'd like to keep that state in reliable storage, so it survives master
> crashes.  But how you gonna commit a change to that state, if you just
> lost every standby (suppose master's ethernet cable got unplugged)?

IIUC you seem to assume that the master node keeps its master role. But
users who value availability a lot certainly want automatic fail-over,
so any node can potentially be the new master.

After recovery from a full-cluster outage, the first question is which
node was the most recent master (or which former standby is up to date
and could take over).

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Dimitri Fontaine
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Well, actually, that's *considerably* more complicated than just a
> timeout.  How are you going to "mark the standby as degraded"?  The
> standby can't keep that information, because it's not even connected
> when the master makes the decision.  ISTM that this requires
>
> 1. a unique identifier for each standby (not just role names that
> multiple standbys might share);
>
> 2. state on the master associated with each possible standby -- not just
> the ones currently connected.
>
> Both of those are perhaps possible, but the sense I have of the
> discussion is that people want to avoid them.

What we'd like to avoid is for the users to have to cope with such
needs. Now, if that's internal to the code and automatic, that's not the
same thing at all.

What I'd have in mind is a "Database standby system identifier" that
would be part of the initial hand shake in the replication protocol. And
a system function to be able to "unregister" the standby.

> Actually, #2 seems rather difficult even if you want it.  Presumably
> you'd like to keep that state in reliable storage, so it survives master
> crashes.  But how you gonna commit a change to that state, if you just
> lost every standby (suppose master's ethernet cable got unplugged)?

I don't see that as a huge problem myself, because I'm already well sold
to the per-transaction replication-synchronous behaviour. So any change
done there by the master would be hard-coded as async. What I'm missing?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 12:05 PM, Dimitri Fontaine wrote:
> Markus Wanner <markus@bluegap.ch> writes:
>> ..and a whole lot of manual work, that's prone to error for something
>> that could easily be automated
> 
> So, the master just crashed, first standby is dead and second ain't in
> sync. What's the easy and automated way out? Sorry, I need a hand here.

Thinking this through, I'm realizing that this can potentially work
automatically with three nodes in both cases. Each node needs to keep
track of whether or not it is (or became) the master - and when (lamport
timestamp, maybe, not necessarily wall clock). A new master might
continue to commit new transactions after a fail-over, without the old
master being able to record that fact (because it's down).

This means there's a different requirement after a full-cluster crash
(i.e. master failure and no up-to-date standby is available). With the
timeout, you absolutely need the former master to come back up again for
zero data loss, no matter what your quorum_commit setting was. To be
able to automatically tell who was the most recent master, you need to
query the state of all other nodes, because they could be a more recent
master. If that's not possible (or not feasible, because the replacement
part isn't currently available), you are at risk of data loss.

With the given three node scenario, the zero data loss guarantee only
holds true as long as either at least one node (that is in sync) is
running or if you can recover the former master after a full cluster crash.

When waiting forever, you only need one of the k nodes to come back up
again. You also need to query other nodes to find out which the k of N
nodes are, but being able to recovery (N - k + 1) nodes is sufficient to
figure that out. So any (k-1) nodes may fail, even permanently, at any
point in time, and you are still not at risk of losing data. (Nor at
risk of losing availability, BTW). I'm still of the opinion that that's
the way easier and clearer guarantee.

Also note that with higher values for N, this gets more and more
important, because the chance to be able to recovery all N nodes after a
full crash shrinks with increasing N (while the time required to do so
increases). But maybe the current sync rep feature doesn't need to
target setups with that many nodes.

I certainly agree that either way is complicated to implement. With
Postgtres-R, I'm clearly going the way that's able to satisfy large
numbers of nodes.

Thanks for an interesting discussion. And for respectful disagreement.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Tom Lane
Date:
Markus Wanner <markus@bluegap.ch> writes:
> On 10/08/2010 04:11 PM, Tom Lane wrote:
>> Actually, #2 seems rather difficult even if you want it.  Presumably
>> you'd like to keep that state in reliable storage, so it survives master
>> crashes.  But how you gonna commit a change to that state, if you just
>> lost every standby (suppose master's ethernet cable got unplugged)?

> IIUC you seem to assume that the master node keeps its master role. But
> users who value availability a lot certainly want automatic fail-over,

Huh?  Surely loss of the slaves shouldn't force a failover.  Maybe the
slaves really are all dead.
        regards, tom lane


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 04:38 PM, Tom Lane wrote:
> Markus Wanner <markus@bluegap.ch> writes:
>> IIUC you seem to assume that the master node keeps its master role. But
>> users who value availability a lot certainly want automatic fail-over,
> 
> Huh?  Surely loss of the slaves shouldn't force a failover.  Maybe the
> slaves really are all dead.

I think we are talking across each other. I'm speaking about the need to
be able to fail-over to a standby in case the master fails.

In case of a full-cluster crash after such a fail-over, you need to take
care you don't enter split brain. Some kind of STONITH, lamport clock,
or what not. Figuring out which node has been the most recent (and thus
most up to date) master is far from trivial.

(See also my mail in answer to Dimitri a few minutes ago).

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Fri, 2010-10-08 at 10:11 -0400, Tom Lane wrote:

> 1. a unique identifier for each standby (not just role names that
> multiple standbys might share);

That is difficult because each standby is identical. If a standby goes
down, people can regenerate a new standby by taking a copy from another
standby. What number do we give this new standby?...

> 2. state on the master associated with each possible standby -- not just
> the ones currently connected.
> 
> Both of those are perhaps possible, but the sense I have of the
> discussion is that people want to avoid them.

Yes, I really want to avoid such issues and likely complexities we get
into trying to solve them. In reality they should not be common because
it only happens if the sysadmin has not configured sufficient number of
redundant standbys.

My proposed design is that the timeout does not cause the standby to be
"marked as degraded". It is up to the user to decide whether they wait,
or whether they progress without sync rep. Or sysadmin can release the
waiters via a function call.

If the cluster does become degraded the sysadmin just generates a new
standby and plugs in back into the cluster and away we go. Simple, no
state to be recorded and no state to get screwed up either. I don't
think we should be spending too much time trying to help people that say
they want additional durability guarantees but do not match that with
sufficient hardware resources to make it happen smoothly.

If we do try to tackle those problems who will be able to validate our
code actually works?

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Fri, Oct 8, 2010 at 5:16 PM, Markus Wanner <markus@bluegap.ch> wrote:
> On 10/08/2010 05:41 AM, Fujii Masao wrote:
>> But, even with quorum commit, if you choose wait-forever option,
>> failover would decrease availability. Right after the failover,
>> no standby has connected to new master, so if quorum >= 1, all
>> the transactions must wait for a while.
>
> That's a point, yes. But again, this is just write-availability, you can
> happily read from all active standbies.

I believe many systems require write-availability.

>> Basically we need to take a base backup from new master to start
>> the standbys and make them connect to new master. This might take
>> a long time. Since transaction commits cannot advance for that time,
>> availability would goes down.
>
> Just don't increase your quorum_commit to unreasonable values which your
> hardware cannot possible satisfy. It doesn't make sense to set a
> quorum_commit of 1 or even bigger, if you don't already have a standby
> attached.
>
> Start with 0 (i.e. replication off), then add standbies, then increase
> quorum_commit to your new requirements.

No. This only makes the procedure of failover more complex.

>> Or you think that wait-forever option is applied only when the
>> standby goes down?
>
> That wouldn't work in case of a full-cluster crash, where the
> wait-forever option is required again. Otherwise you risk a split-brain
> situation.

What is a full-cluster crash? Why does it cause a split-brain?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Fri, Oct 8, 2010 at 6:00 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >From the perspective of an observer, randomly selecting a standby for
> load balancing purposes: No, they are not guaranteed to see the "latest"
> answer, nor even can they find out whether what they are seeing is the
> latest answer.

To guarantee that each standby returns the same result, we would need to
use the cluster-wide snapshot to run queries. IIRC, Postgres-XC provides
that feature. Though I'm not sure if it can be applied in HS/SR.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Fri, 2010-10-08 at 23:55 +0900, Fujii Masao wrote:
> On Fri, Oct 8, 2010 at 6:00 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > >From the perspective of an observer, randomly selecting a standby for
> > load balancing purposes: No, they are not guaranteed to see the "latest"
> > answer, nor even can they find out whether what they are seeing is the
> > latest answer.
> 
> To guarantee that each standby returns the same result, we would need to
> use the cluster-wide snapshot to run queries. IIRC, Postgres-XC provides
> that feature. Though I'm not sure if it can be applied in HS/SR.

That is my understanding.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 04:47 PM, Simon Riggs wrote:
> Yes, I really want to avoid such issues and likely complexities we get
> into trying to solve them. In reality they should not be common because
> it only happens if the sysadmin has not configured sufficient number of
> redundant standbys.

Well, full cluster outages are infrequent, but sadly cannot be avoided
entirely. (Murphy's laughing). IMO we should be prepared to deal with
those. Or am I understanding you wrongly here?

> I don't
> think we should be spending too much time trying to help people that say
> they want additional durability guarantees but do not match that with
> sufficient hardware resources to make it happen smoothly.

I fully agree to that statement.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/08/2010 04:48 PM, Fujii Masao wrote:
> I believe many systems require write-availability.

Sure. Make sure you have enough standbies to fail over to.

(I think there are even more situations where read-availability is much
more important, though).

>> Start with 0 (i.e. replication off), then add standbies, then increase
>> quorum_commit to your new requirements.
> 
> No. This only makes the procedure of failover more complex.

Huh? This doesn't affect fail-over at all. Quite the opposite, the
guarantees and requirements remain the same even after a fail-over.

> What is a full-cluster crash?

The event that all of your cluster nodes are down (most probably due to
power failure, but fires or other catastrophic events can be other
causes). Chances for that to happen can certainly be reduced by
distributing to distant locations, but that equally certainly increases
latency, which isn't always an option.

> Why does it cause a split-brain?

First master node A fails, a standby B takes over, but then fails as
well. Let node C take over. Then the power aggregates catches fire, the
infamous full-cluster crash (where "lights out management" gets a
completely new meaning ;-) ).

Split brain would be the situation that arises if all three nodes (A, B
and C) start up again and think they have been the former master, so
they can now continue to apply new transactions. Their data diverges,
leading to what could be seen as a split-brain from the outside.

Obviously, you must disallow A and B to take the role of the master
after recovery. Ideally, C would continue as the master. However, if the
fire destroyed node C, let's hope you had another (sync!) standby that
can act as the new master. Otherwise you've lost data.

Hope that explains it. Wikipedia certainly provides a better (and less
Postgres colored) explanation.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Josh Berkus
Date:
> And, I'd like to know whether the master waits forever because of the
> standby failure in other solutions such as Oracle DataGuard, MySQL
> semi-synchronous replication.

MySQL used to be fond of simiply failing sliently.  Not sure what 5.4 
does, or Oracle.  In any case MySQL's replication has always really been 
async (except Cluster, which is a very different database), so it's not 
really a comparison.

Here's the comparables:
Oracle DataGuard
DRBD
SQL Server
DB2

If anyone knows what the above do by default, please speak up!

--                                   -- Josh Berkus                                     PostgreSQL Experts Inc.
                           http://www.pgexperts.com
 


Re: Issues with Quorum Commit

From
Rob Wultsch
Date:
*

On 10/8/10, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Fri, Oct 8, 2010 at 5:10 PM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com> wrote:
>> Do we really need that?
>
> Yes. But if there is no unsent WAL when the master goes down,
> we can start new standby without new backup by copying the
> timeline history file from new master to new standby and
> setting recovery_target_timeline to 'latest'. In this case,
> new standby advances the recovery to the latest timeline ID
> which new master uses before connecting to the master.
>
> This seems to have been successful in my test environment.
> Though I'm missing something.
>
>> I don't think that's acceptable, we'll need to fix
>> that if that's the case.
>
> Agreed.
>
>> You can cross timelines with the archive, though. But IIRC there was some
>> issue with that too, you needed to restart the standbys because the
>> standby
>> scans what timelines exist at the beginning of recovery, and won't notice
>> new timelines that appear after that?
>
> Yes.
>
>> We need to address that, apart from any of the other things discussed wrt.
>> synchronous replication. It will benefit asynchronous replication too.
>> IMHO
>> *that* is the next thing we should do, the next patch we commit.
>
> You mean to commit that capability before synchronous replication? If so,
> I disagree with you. I think that it's not easy to address that problem.
> So I'm worried about that implementing that capability first means the miss
> of sync rep in 9.1.
>
> Regards,
>
> --
> Fujii Masao
> NIPPON TELEGRAPH AND TELEPHONE CORPORATION
> NTT Open Source Software Center
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


-- 
Rob Wultsch
wultsch@gmail.com


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 08.10.2010 17:26, Fujii Masao wrote:
> On Fri, Oct 8, 2010 at 5:10 PM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com>  wrote:
>> Do we really need that?
>
> Yes. But if there is no unsent WAL when the master goes down,
> we can start new standby without new backup by copying the
> timeline history file from new master to new standby and
> setting recovery_target_timeline to 'latest'.

.. and restart the standby.

> In this case,
> new standby advances the recovery to the latest timeline ID
> which new master uses before connecting to the master.
>
> This seems to have been successful in my test environment.
> Though I'm missing something.

Yeah, that should work, but it's awfully complicated.

>> I don't think that's acceptable, we'll need to fix
>> that if that's the case.
>
> Agreed.
>
>> You can cross timelines with the archive, though. But IIRC there was some
>> issue with that too, you needed to restart the standbys because the standby
>> scans what timelines exist at the beginning of recovery, and won't notice
>> new timelines that appear after that?
>
> Yes.
>
>> We need to address that, apart from any of the other things discussed wrt.
>> synchronous replication. It will benefit asynchronous replication too. IMHO
>> *that* is the next thing we should do, the next patch we commit.
>
> You mean to commit that capability before synchronous replication? If so,
> I disagree with you. I think that it's not easy to address that problem.
> So I'm worried about that implementing that capability first means the miss
> of sync rep in 9.1.

It's a pretty severe shortcoming at the moment. For starters, it means 
that you need a shared archive, even if you set wal_keep_segments to a 
high number. Secondly, it's a lot of scripting to get it working, I 
don't like the thought of testing failovers in synchronous replication 
if I have to do all that. Frankly, this seems more important to me than 
synchronous replication.

It shouldn't be too hard to fix. Walsender needs to be able to read WAL 
from preceding timelines, like recovery does, and walreceiver needs to 
write the incoming WAL to the right file.

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


Re: Issues with Quorum Commit

From
Greg Smith
Date:
Markus Wanner wrote:
> ..and how do you make sure you are not marking your second standby as
> degraded just because it's currently lagging? Effectively degrading the
> utterly needed one, because your first standby has just bitten the dust?
>   

People are going to monitor the standby lag.  If it gets excessive 
relative to where it's approaching the known timeout, the flashing 
yellow lights should go off at this point, before it gets this bad.  And 
if you've set a reasonable business oriented timeout on how long you can 
stand for the master to be held up waiting for a lagging standby, the 
right thing to do may very well be to cut it off.  At some point people 
will want to stop waiting for a standby if it's taking so long to commit 
that it's interfering with the ability of the master to operate 
normally.  Such a master is already degraded, if your performance 
metrics for availability includes processing transactions in a timely 
manner.

> And how do you prevent the split brain situation in case the master dies
> shortly after these events, but fails to come up again immediately?
>   

How is that a new problem?  It's already possible to end up with a 
standby pair that has suffered through some bizarre failure chain such 
that it's not necessarily obvious which of the two systems has the most 
recent set of data on it.  And that's not this project's problem to 
solve.  Useful answers to the split brain problem involve fencing 
implementations that normally drop to the hardware level, and clustering 
solutions including those features are already available that PostgreSQL 
can integrate into.  Assuming you have to solve this in order to deliver 
a useful database replication component is excessively ambitious. 

You seem to be under the assumption that a more complicated replication 
implementation here will make reaching a bad state impossible.  I think 
that's optimistic, both in theory and in regards to how successful code 
gets built.  Here's the thing:  the difficultly of testing to prove your 
code actually works is also proportional to that complexity.  This 
project can chose to commit and potentially ship a simple solution that 
has known limitations, and expect that people will fill in the gap with 
existing add-on software to handle the clustering parts it doesn't:  
fencing, virtual IP address assignment, etc.  All while getting useful 
testing feedback on the simple bottom layer, whose main purpose in life 
is to transport WAL data synchronously.  Or, we can argue in favor of 
adding additional complexity on top first instead, so we end up with 
layers and layers of untested code.  That path leads to situations where 
you're lucky to ship at all, and when you do the result is difficult to 
support.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us




Re: Issues with Quorum Commit

From
Greg Smith
Date:
Tom Lane wrote:
> How are you going to "mark the standby as degraded"?  The
> standby can't keep that information, because it's not even connected
> when the master makes the decision.
From a high level, I'm assuming only that the master has a list in 
memory of the standby system(s) it believes are up to date, and that it 
is supposed to commit to synchronously.  When I say mark as degraded, I 
mean that the master merely closes whatever communications channel it 
had open with that system and removes the standby from that list.

If that standby now reconnects again, I don't see how resolving what 
happens at that point is any different from when a standby is first 
started after both systems were turned off.  If the standby is current 
with the data available on the master when it has an initial 
conversation, great; it's now available for synchronous commit too 
then.  If it's not, it goes into a catchup mode first instead.  When the 
master sees you're back to current again, if you're on the list of sync 
servers too you go back onto the list of active sync systems.

There's shouldn't be any state information to save here.  If the master 
and standby can't figure out if they are in or out of sync with one 
another based on the conversation they have when they first connect to 
one another, that suggests to me there needs to be improvements made in 
the communications protocol they use to exchange messages. 

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us




Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Fri, 2010-10-08 at 17:06 +0200, Markus Wanner wrote:
> Well, full cluster outages are infrequent, but sadly cannot be avoided
> entirely. (Murphy's laughing). IMO we should be prepared to deal with
> those. 

I've described how I propose to deal with those. I'm not waving away
these issues, just proposing that we consciously choose simplicity and
therefore robustness.

Let me say it again for clarity. (This is written for the general case,
though my patch uses only k=1 i.e. one acknowledgement):

If we want robustness, we have multiple standbys. So if you lose one,
you continue as normal without interruption. That is the first and most
important line of defence - not software.

When we start to wait, if there aren't sufficient active standbys to
acknowledge a commit, then the commit won't wait. This behaviour helps
us avoid situations where we are hours or days away from having a
working standby to acknowledge the commit. We've had a long debate about
servers that "ought to be there" but aren't; I suggest we treat standbys
that aren't there as having a strong possibility they won't come back,
and hence not worth waiting for. Heikki disagrees; I have no problem
with adding server registration so that we can add additional waits, but
I doubt that the majority of users prefer waiting over availability. It
can be an option

Once we are waiting, if insufficient standbys acknowledge the commit we
will wait until the timeout expires, after which we commit and continue
working. If you don't like timeouts, set the timeout to 0 to wait
forever. This behaviour is designed to emphasise availability. (I
acknowledge that some people are so worried by data loss that they would
choose to stop changes altogether, and accept unavailability; I regard
that as a minority use case, but one which I would not argue against
including as an options at some point in the future.)

To cover Dimitri's observation that when a streaming standby first
connects it might take some time before it can sensibly acknowledge, we
don't activate the standby until it has caught up. Once caught up, it
will advertise it's capability to offer a sync rep service. Standbys
that don't wish to be failover targets can set
synchronous_replication_service = off.

The paths between servers aren't defined explicitly, so the parameters
all still work even after failover.
-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Simon Riggs
Date:
On Fri, 2010-10-08 at 16:34 -0400, Greg Smith wrote:
> Tom Lane wrote:
> > How are you going to "mark the standby as degraded"?  The
> > standby can't keep that information, because it's not even connected
> > when the master makes the decision.
> 
>  From a high level, I'm assuming only that the master has a list in 
> memory of the standby system(s) it believes are up to date, and that it 
> is supposed to commit to synchronously.  When I say mark as degraded, I 
> mean that the master merely closes whatever communications channel it 
> had open with that system and removes the standby from that list.

My current coding works with two sets of parameters: 

The "master marks standby as degraded" is handled by the tcp keepalives.
When it notices no response, it kicks out the standby. We already had
this, so I never mentioned it before as being part of the solution.

The second part is the synchronous_replication_timeout which is a user
settable parameter defining how long the app is prepared to wait, which
could be more or less time than the keepalives.

> If that standby now reconnects again, I don't see how resolving what 
> happens at that point is any different from when a standby is first 
> started after both systems were turned off.  If the standby is current 
> with the data available on the master when it has an initial 
> conversation, great; it's now available for synchronous commit too 
> then.  If it's not, it goes into a catchup mode first instead.  When the 
> master sees you're back to current again, if you're on the list of sync 
> servers too you go back onto the list of active sync systems.
> 
> There's shouldn't be any state information to save here.  If the master 
> and standby can't figure out if they are in or out of sync with one 
> another based on the conversation they have when they first connect to 
> one another, that suggests to me there needs to be improvements made in 
> the communications protocol they use to exchange messages. 

Agreed.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Development, 24x7 Support, Training and Services



Re: Issues with Quorum Commit

From
Markus Wanner
Date:
Greg,

to me it looks like we have very similar goals, but start from different
preconditions. I absolutely agree with you given the preconditions you
named.

On 10/08/2010 10:04 PM, Greg Smith wrote:
> How is that a new problem?  It's already possible to end up with a
> standby pair that has suffered through some bizarre failure chain such
> that it's not necessarily obvious which of the two systems has the most
> recent set of data on it.  And that's not this project's problem to
> solve.

Thanks for pointing that out. I think that might not have been clear to
me. This limitation of scope certainly make sense for the Postgres
project in general.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Sat, Oct 9, 2010 at 12:12 AM, Markus Wanner <markus@bluegap.ch> wrote:
> On 10/08/2010 04:48 PM, Fujii Masao wrote:
>> I believe many systems require write-availability.
>
> Sure. Make sure you have enough standbies to fail over to.

Unfortunately even enough standbys don't increase write-availability
unless you choose wait-forever. Because, after promoting one of
standbys to new master, you must keep all the transactions waiting
until at least one standby has connected to and caught up with new
master. Currently this wait time is not short.

> (I think there are even more situations where read-availability is much
> more important, though).

Even so, we should not ignore the write-availability aspect.

>>> Start with 0 (i.e. replication off), then add standbies, then increase
>>> quorum_commit to your new requirements.
>>
>> No. This only makes the procedure of failover more complex.
>
> Huh? This doesn't affect fail-over at all. Quite the opposite, the
> guarantees and requirements remain the same even after a fail-over.

Hmm.. that increases the number of procedures which the users must
perform at the failover. At least, the users seem to have to wait
until the standby has caught up with new master, increase quorum_commit
and then reload the configuration file.

>> What is a full-cluster crash?
>
> The event that all of your cluster nodes are down (most probably due to
> power failure, but fires or other catastrophic events can be other
> causes). Chances for that to happen can certainly be reduced by
> distributing to distant locations, but that equally certainly increases
> latency, which isn't always an option.

Yep.

>> Why does it cause a split-brain?
>
> First master node A fails, a standby B takes over, but then fails as
> well. Let node C take over. Then the power aggregates catches fire, the
> infamous full-cluster crash (where "lights out management" gets a
> completely new meaning ;-) ).
>
> Split brain would be the situation that arises if all three nodes (A, B
> and C) start up again and think they have been the former master, so
> they can now continue to apply new transactions. Their data diverges,
> leading to what could be seen as a split-brain from the outside.
>
> Obviously, you must disallow A and B to take the role of the master
> after recovery.

Yep. Something like STONITH would be required.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Sat, Oct 9, 2010 at 1:41 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> And, I'd like to know whether the master waits forever because of the
>> standby failure in other solutions such as Oracle DataGuard, MySQL
>> semi-synchronous replication.
>
> MySQL used to be fond of simiply failing sliently.  Not sure what 5.4 does,
> or Oracle.  In any case MySQL's replication has always really been async
> (except Cluster, which is a very different database), so it's not really a
> comparison.

IIRC, MySQL *semi-synchronous* replication is not async, so it can be
comparison. Of course, though MySQL default replication is async.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Sat, Oct 9, 2010 at 4:31 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
>> Yes. But if there is no unsent WAL when the master goes down,
>> we can start new standby without new backup by copying the
>> timeline history file from new master to new standby and
>> setting recovery_target_timeline to 'latest'.
>
> .. and restart the standby.

Yes.

> It's a pretty severe shortcoming at the moment. For starters, it means that
> you need a shared archive, even if you set wal_keep_segments to a high
> number. Secondly, it's a lot of scripting to get it working, I don't like
> the thought of testing failovers in synchronous replication if I have to do
> all that. Frankly, this seems more important to me than synchronous
> replication.

There seems to be difference in outlook between us. I prefer sync rep.
But I'm OK to address that first if it's not hard.

> It shouldn't be too hard to fix. Walsender needs to be able to read WAL from
> preceding timelines, like recovery does, and walreceiver needs to write the
> incoming WAL to the right file.

And walsender seems to need to transfer the current timeline history to
the standby. Otherwise, the standby cannot recover the WAL file with new
timeline. And the standby might need to create the timeline history file
in order to recover the WAL file with new timeline even after it's restarted.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Heikki Linnakangas
Date:
On 13.10.2010 08:21, Fujii Masao wrote:
> On Sat, Oct 9, 2010 at 4:31 AM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com>  wrote:
>> It shouldn't be too hard to fix. Walsender needs to be able to read WAL from
>> preceding timelines, like recovery does, and walreceiver needs to write the
>> incoming WAL to the right file.
>
> And walsender seems to need to transfer the current timeline history to
> the standby. Otherwise, the standby cannot recover the WAL file with new
> timeline. And the standby might need to create the timeline history file
> in order to recover the WAL file with new timeline even after it's restarted.

Yes, true, you need that too.

It might be good to divide this work into two phases, teaching archive 
recovery to notice new timelines appearing in the archive first, and 
doing the walsender/walreceiver changes after that.

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


Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Wed, Oct 13, 2010 at 2:43 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 13.10.2010 08:21, Fujii Masao wrote:
>>
>> On Sat, Oct 9, 2010 at 4:31 AM, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com>  wrote:
>>>
>>> It shouldn't be too hard to fix. Walsender needs to be able to read WAL
>>> from
>>> preceding timelines, like recovery does, and walreceiver needs to write
>>> the
>>> incoming WAL to the right file.
>>
>> And walsender seems to need to transfer the current timeline history to
>> the standby. Otherwise, the standby cannot recover the WAL file with new
>> timeline. And the standby might need to create the timeline history file
>> in order to recover the WAL file with new timeline even after it's
>> restarted.
>
> Yes, true, you need that too.
>
> It might be good to divide this work into two phases, teaching archive
> recovery to notice new timelines appearing in the archive first, and doing
> the walsender/walreceiver changes after that.

There's another problem here we should think about, too.  Suppose you
have a master and two standbys.  The master dies.  You promote one of
the standbys, which turns out to be behind the other.  You then
repoint the other standby at the one you promoted.  Congratulations,
your database is now very possible corrupt, and you may very well get
no warning of that fact.  It seems to me that we would be well-advised
to install some kind of bullet-proof safeguard against this kind of
problem, so that you will KNOW that the standby needs to be re-synced.I mention this because I have a vague feeling
thattimelines are 
supposed to prevent you from getting different WAL histories confused
with each other, but they don't actually cover all the cases that can
happen.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Issues with Quorum Commit

From
Markus Wanner
Date:
On 10/13/2010 06:43 AM, Fujii Masao wrote:
> Unfortunately even enough standbys don't increase write-availability
> unless you choose wait-forever. Because, after promoting one of
> standbys to new master, you must keep all the transactions waiting
> until at least one standby has connected to and caught up with new
> master. Currently this wait time is not short.

Why is that? Don't the standbies just have to switch from one walsender
to another? If there's any significant delay in switching, this either
hurts availability or robustness, yes.

> Hmm.. that increases the number of procedures which the users must
> perform at the failover.

I only consider fully automated failover. However, you seem to be
worried about the initial setup of sync rep.

> At least, the users seem to have to wait
> until the standby has caught up with new master, increase quorum_commit
> and then reload the configuration file.

For switching from a single node to a sync replication setup with one or
more standbies, that seems reasonable. There are way more components you
need to setup or adjust in such a case (network, load balancer, alerting
system and maybe even the application itself).

There's really no other option, if you want the kind of robustness
guarantee that sync rep with wait forever provides. OTOH, if you just
replicate to whatever standby is there and don't care much if it isn't,
the admin doesn't need to worry much about quorum_commit - it doesn't
have much of an effect anyway.

Regards

Markus Wanner


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 13, 2010 at 3:43 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 13.10.2010 08:21, Fujii Masao wrote:
>>
>> On Sat, Oct 9, 2010 at 4:31 AM, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com>  wrote:
>>>
>>> It shouldn't be too hard to fix. Walsender needs to be able to read WAL
>>> from
>>> preceding timelines, like recovery does, and walreceiver needs to write
>>> the
>>> incoming WAL to the right file.
>>
>> And walsender seems to need to transfer the current timeline history to
>> the standby. Otherwise, the standby cannot recover the WAL file with new
>> timeline. And the standby might need to create the timeline history file
>> in order to recover the WAL file with new timeline even after it's
>> restarted.
>
> Yes, true, you need that too.
>
> It might be good to divide this work into two phases, teaching archive
> recovery to notice new timelines appearing in the archive first, and doing
> the walsender/walreceiver changes after that.

OK. In detail,

1. After failover, when the standby connects to new master, walsender transfers  the current timeline history in the
handshakeprocessing. 

2. If the timeline history in the master is inconsistent with that in
the standby,  walreceiver terminates the replication connection.

3. Walreceiver creates the timeline history file.

4. Walreceiver signals the change of timeline history to startup process and  makes it read the timeline history file.
Afterthis, startup process tries  to recover the WAL files with even new timeline ID. 

5. After the handshake, walsender sends the WAL from preceding timelines,  like recovery does, and walreceiver writes
theincoming WAL to the right  file. 

Am I missing something?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Wed, Oct 13, 2010 at 3:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> There's another problem here we should think about, too.  Suppose you
> have a master and two standbys.  The master dies.  You promote one of
> the standbys, which turns out to be behind the other.  You then
> repoint the other standby at the one you promoted.  Congratulations,
> your database is now very possible corrupt, and you may very well get
> no warning of that fact.  It seems to me that we would be well-advised
> to install some kind of bullet-proof safeguard against this kind of
> problem, so that you will KNOW that the standby needs to be re-synced.

Yep. This is why I said it's not easy to implement that.

To start the standby without taking a base backup from new master after
failover, the user basically has to promote the standby which is ahead
of the other standbys (e.g., by comparing pg_last_xlog_replay_location
on each standby).

As the safeguard, we seem to need to compare the location at the switch
of the timeline on the master with the last replay location on the standby.
If the latter location is ahead AND the timeline ID of the standby is not
the same as that of the master, we should emit warning and terminate the
replication connection.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Greg Stark
Date:
On Tue, Oct 12, 2010 at 11:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> There's another problem here we should think about, too.  Suppose you
> have a master and two standbys.  The master dies.  You promote one of
> the standbys, which turns out to be behind the other.  You then
> repoint the other standby at the one you promoted.  Congratulations,
> your database is now very possible corrupt, and you may very well get
> no warning of that fact.  It seems to me that we would be well-advised
> to install some kind of bullet-proof safeguard against this kind of
> problem, so that you will KNOW that the standby needs to be re-synced.
>  I mention this because I have a vague feeling that timelines are
> supposed to prevent you from getting different WAL histories confused
> with each other, but they don't actually cover all the cases that can
> happen.
>

Why don't the usual protections kick in here? The new record read from
the location the xlog reader is expecting to find it has to have a
valid CRC and a correct back pointer to the previous record. If the
new wal sender is behind the old one then the new record it's sent
won't match up at all.

--
greg


Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Wed, Oct 13, 2010 at 5:22 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Wed, Oct 13, 2010 at 3:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> There's another problem here we should think about, too.  Suppose you
>> have a master and two standbys.  The master dies.  You promote one of
>> the standbys, which turns out to be behind the other.  You then
>> repoint the other standby at the one you promoted.  Congratulations,
>> your database is now very possible corrupt, and you may very well get
>> no warning of that fact.  It seems to me that we would be well-advised
>> to install some kind of bullet-proof safeguard against this kind of
>> problem, so that you will KNOW that the standby needs to be re-synced.
>
> Yep. This is why I said it's not easy to implement that.
>
> To start the standby without taking a base backup from new master after
> failover, the user basically has to promote the standby which is ahead
> of the other standbys (e.g., by comparing pg_last_xlog_replay_location
> on each standby).
>
> As the safeguard, we seem to need to compare the location at the switch
> of the timeline on the master with the last replay location on the standby.
> If the latter location is ahead AND the timeline ID of the standby is not
> the same as that of the master, we should emit warning and terminate the
> replication connection.

That doesn't seem very bullet-proof.  You can accidentally corrupt a
standby even when only one time-line is involved. AFAIK, stopping a
standby, removing recovery.conf, and starting it up again does not
change time lines.  You can even shut down the standby, bring it up as
a master, generate a little WAL, shut it back down, and bring it back
up as a standby pointing to the same master.  It would be nice to
embed in each checkpoint record an identifier that changes randomly on
each transition to normal running, so that if you do something like
this we can notice and complain loudly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Issues with Quorum Commit

From
Fujii Masao
Date:
On Thu, Oct 14, 2010 at 11:18 AM, Greg Stark <gsstark@mit.edu> wrote:
> Why don't the usual protections kick in here? The new record read from
> the location the xlog reader is expecting to find it has to have a
> valid CRC and a correct back pointer to the previous record.

Yep. In most cases, those protections seem to be able to make the standby
notice the inconsistency of WAL and then give up continuing replication.
But not in all the cases. We can regard those protections as bullet-proof
safeguard?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


Re: Issues with Quorum Commit

From
Robert Haas
Date:
On Wed, Oct 13, 2010 at 10:18 PM, Greg Stark <gsstark@mit.edu> wrote:
> On Tue, Oct 12, 2010 at 11:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> There's another problem here we should think about, too.  Suppose you
>> have a master and two standbys.  The master dies.  You promote one of
>> the standbys, which turns out to be behind the other.  You then
>> repoint the other standby at the one you promoted.  Congratulations,
>> your database is now very possible corrupt, and you may very well get
>> no warning of that fact.  It seems to me that we would be well-advised
>> to install some kind of bullet-proof safeguard against this kind of
>> problem, so that you will KNOW that the standby needs to be re-synced.
>>  I mention this because I have a vague feeling that timelines are
>> supposed to prevent you from getting different WAL histories confused
>> with each other, but they don't actually cover all the cases that can
>> happen.
>>
>
> Why don't the usual protections kick in here? The new record read from
> the location the xlog reader is expecting to find it has to have a
> valid CRC and a correct back pointer to the previous record. If the
> new wal sender is behind the old one then the new record it's sent
> won't match up at all.

There's some kind of logic that rewinds to the beginning of the WAL
segment and tries to replay from there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: Issues with Quorum Commit

From
Bruce Momjian
Date:
Tom Lane wrote:
> Greg Smith <greg@2ndquadrant.com> writes:
> > I don't see this as needing any implementation any more complicated than 
> > the usual way such timeouts are handled.  Note how long you've been 
> > trying to reach the standby.  Default to -1 for forever.  And if you hit 
> > the timeout, mark the standby as degraded and force them to do a proper 
> > resync when they disconnect.  Once that's done, then they can re-enter 
> > sync rep mode again, via the same process a new node would have done so.
> 
> Well, actually, that's *considerably* more complicated than just a
> timeout.  How are you going to "mark the standby as degraded"?  The
> standby can't keep that information, because it's not even connected
> when the master makes the decision.  ISTM that this requires
> 
> 1. a unique identifier for each standby (not just role names that
> multiple standbys might share);
> 
> 2. state on the master associated with each possible standby -- not just
> the ones currently connected.
> 
> Both of those are perhaps possible, but the sense I have of the
> discussion is that people want to avoid them.
> 
> Actually, #2 seems rather difficult even if you want it.  Presumably
> you'd like to keep that state in reliable storage, so it survives master
> crashes.  But how you gonna commit a change to that state, if you just
> lost every standby (suppose master's ethernet cable got unplugged)?
> Looks to me like it has to be reliable non-replicated storage.  Leaving
> aside the question of how reliable it can really be if not replicated,
> it's still the case that we have noplace to put such information given
> the WAL-is-across-the-whole-cluster design.

I assumed we would have a parameter called "sync_rep_failure" that would
take a command and the command would be called when communication to the
slave was lost.  If you restart, it tries again and might call the
function again.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +