Thread: Synchronization levels in SR

Synchronization levels in SR

From
Fujii Masao
Date:
Hi,

I'm now designing the "synchronous" replication feature based on
SR for 9.1, while discussing that at another thread.
http://archives.postgresql.org/pgsql-hackers/2010-04/msg01516.php

At the first design phase, I'd like to clarify which synch levels
should be supported 9.1 and how it should be specified by users.

The log-shipping replication has some synch levels as follows.
  The transaction commit on the master  #1 doesn't wait for replication (already suppored in 9.0)  #2 waits for WAL to
bereceived by the standby  #3 waits for WAL to be received and flushed by the standby  #4 waits for WAL to be received,
flushedand replayed by     the standby  ..etc?
 

Which should we include in 9.1? I'd like to add #2 and #3.
They are enough for high-availability use case (i.e., to
prevent failover from losing any transactions committed).
AFAIR, MySQL semi-synchronous replication supports #2 level.

#4 is useful for some cases, but might often make the
transaction commit on the master get stuck since read-only
query can easily block recovery by the lock conflict. So
#4 seems not to be worth working on until that HS problem
has been addressed. Thought?

Second, we need to discuss about how to specify the synch
level. There are three approaches:

* Per standby Since the purpose, location and H/W resource often differ from one standby to another, specifying level
perstandby (i.e., we set the level in recovery.conf) is a straightforward approach, I think. For example, we can choose
#3for high-availability standby near the master, and choose #1 (async) for the disaster recovery standby remote.
 

* Per transaction Define the PGC_USERSET option specifying the level and specify it on the master in response to the
purposeof transaction. In this approach, for example, we can choose #4 for the transaction which should be visible on
thestandby as soon as a "success" of the commit has been returned to a client. We can also choose #1 for time-critical
butnot mission-critical transaction.
 

* Mix Allow users to specify the level per standby and transaction at the same time, and then calculate the real level
fromthem by using some algorithm.
 

Which should we adopt for 9.1? I'd like to implement the
"per-standby" approach at first since it's simple and seems
to cover more use cases. Thought?

Regards,

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


Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 24/05/10 16:20, Fujii Masao wrote:
> The log-shipping replication has some synch levels as follows.
>
>     The transaction commit on the master
>     #1 doesn't wait for replication (already suppored in 9.0)
>     #2 waits for WAL to be received by the standby
>     #3 waits for WAL to be received and flushed by the standby
>     #4 waits for WAL to be received, flushed and replayed by
>        the standby
>     ..etc?
>
> Which should we include in 9.1? I'd like to add #2 and #3.
> They are enough for high-availability use case (i.e., to
> prevent failover from losing any transactions committed).
> AFAIR, MySQL semi-synchronous replication supports #2 level.
>
> #4 is useful for some cases, but might often make the
> transaction commit on the master get stuck since read-only
> query can easily block recovery by the lock conflict. So
> #4 seems not to be worth working on until that HS problem
> has been addressed. Thought?

I see a lot of value in #4; it makes it possible to distribute read-only 
load to the standby using something like pgbouncer, completely 
transparently to the application. In the lesser modes, the application 
can see slightly stale results.

But whatever we can easily implement, really. Pick one that you think is 
the easiest and start with that, but keep the other modes in mind in the 
design and in the user interface so that you don't paint yourself in the 
corner.

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


Re: Synchronization levels in SR

From
Josh Berkus
Date:
> #4 is useful for some cases, but might often make the
> transaction commit on the master get stuck since read-only
> query can easily block recovery by the lock conflict. So
> #4 seems not to be worth working on until that HS problem
> has been addressed. Thought?

I agree that #4 should be done last, but it will be needed, not in the
least by your employer ;-) .  I don't see any obvious way to make #4
compatible with any significant query load on the slave, but in general
I'd think that users of #4 are far more concerned with 0% data loss than
they are with getting the slave to run read queries.

> Second, we need to discuss about how to specify the synch
> level. There are three approaches:
> 
> * Per standby
> 
> * Per transaction

Ach, I'm torn.  I can see strong use cases for both of the above.
Really, I think:

> * Mix
>   Allow users to specify the level per standby and
>   transaction at the same time, and then calculate the real
>   level from them by using some algorithm.

What we should do is specify it per-standby, and then have a USERSET GUC
on the master which specifies which transactions will be synched, and
those will be synched only on the slaves which are set up to support
synch.  That is, if you have:

Master
Slave #1: synch
Slave #2: not synch
Slave #3: not synch

And you have:
Session #1: synch
Session #2: not synch

Session #1 will be synched on Slave #1 before commit.  Nothing will be
synched on Slaves 2 and 3, and session #2 will not wait for synch on any
slave.

I think this model delivers the maximum HA flexibility to users while
still making intuitive logical sense.

> Which should we adopt for 9.1? I'd like to implement the
> "per-standby" approach at first since it's simple and seems
> to cover more use cases. Thought?

If people agree that the above is our roadmap, implementing
"per-standby" first makes sense, and then we can implement "per-session"
GUC later.


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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Tue, May 25, 2010 at 1:18 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> I see a lot of value in #4; it makes it possible to distribute read-only
> load to the standby using something like pgbouncer, completely transparently
> to the application.

Agreed.

> In the lesser modes, the application can see slightly
> stale results.

Yes

BTW, even if we got #4, we would need to be careful about that
we might see the uncommitted results from the standby. That is,
the transaction commit might become visible in the standby before
the master returns its "success" to a client. I think that we
would never get the completely-transaction-consistent results
from the standby until we have implemented the "snapshot cloning"
feature.
http://wiki.postgresql.org/wiki/ClusterFeatures#Export_snapshots_to_other_sessions

> But whatever we can easily implement, really. Pick one that you think is the
> easiest and start with that, but keep the other modes in mind in the design
> and in the user interface so that you don't paint yourself in the corner.

Yep, the design and implementation for #2 and #3 should be
easily extensible for #4. I'll keep in mind that.

Regards,

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Tue, May 25, 2010 at 10:29 AM, Josh Berkus <josh@agliodbs.com> wrote:
> I agree that #4 should be done last, but it will be needed, not in the
> least by your employer ;-) .  I don't see any obvious way to make #4
> compatible with any significant query load on the slave, but in general
> I'd think that users of #4 are far more concerned with 0% data loss than
> they are with getting the slave to run read queries.

Since #2 and #3 are enough for 0% data loss, I think that such users
would be more concerned about what results are visible in the standby.
No?

> What we should do is specify it per-standby, and then have a USERSET GUC
> on the master which specifies which transactions will be synched, and
> those will be synched only on the slaves which are set up to support
> synch.  That is, if you have:
>
> Master
> Slave #1: synch
> Slave #2: not synch
> Slave #3: not synch
>
> And you have:
> Session #1: synch
> Session #2: not synch
>
> Session #1 will be synched on Slave #1 before commit.  Nothing will be
> synched on Slaves 2 and 3, and session #2 will not wait for synch on any
> slave.
>
> I think this model delivers the maximum HA flexibility to users while
> still making intuitive logical sense.

This makes sense.

Since it's relatively easy and simple to implement such a boolean GUC flag
rather than "per-transaction" levels (there are four valid values #1, #2,
#3 and #4), I'll do that.

Regards,

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Mon, May 24, 2010 at 10:20 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> At the first design phase, I'd like to clarify which synch levels
> should be supported 9.1 and how it should be specified by users.

There is another question about synch level:
   When should the master wait for replication?

In my current design, the backend waits for replication only at
the end of the transaction commit. Is this enough? Is there other
waiting point?

For example, smart or fast shutdown on the master should wait
for a shutdown checkpoint record to be replicated to the standby
(btw, in 9.0, shutdown waits for checkpoint record to be *sent*)?
pg_switch_xlog() needs to wait for all of original WAL file to
be replicated?

I'm not sure if the above two "waits-for-replication" have use
cases, so I'm thinking they are not worth implementing, but..

Regards,

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 12:40 +0900, Fujii Masao wrote:
> On Tue, May 25, 2010 at 10:29 AM, Josh Berkus <josh@agliodbs.com> wrote:
> > I agree that #4 should be done last, but it will be needed, not in the
> > least by your employer ;-) .  I don't see any obvious way to make #4
> > compatible with any significant query load on the slave, but in general
> > I'd think that users of #4 are far more concerned with 0% data loss than
> > they are with getting the slave to run read queries.
> 
> Since #2 and #3 are enough for 0% data loss, I think that such users
> would be more concerned about what results are visible in the standby.
> No?

Please add #4 also. You can do that easily at the same time as #2 and
#3, and it will leave me free to fix the perceived conflict problems.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Mon, 2010-05-24 at 22:20 +0900, Fujii Masao wrote:

> Second, we need to discuss about how to specify the synch
> level. There are three approaches:
> 
> * Per standby
>   Since the purpose, location and H/W resource often differ
>   from one standby to another, specifying level per standby
>   (i.e., we set the level in recovery.conf) is a
>   straightforward approach, I think. For example, we can
>   choose #3 for high-availability standby near the master,
>   and choose #1 (async) for the disaster recovery standby
>   remote.
> 
> * Per transaction
>   Define the PGC_USERSET option specifying the level and
>   specify it on the master in response to the purpose of
>   transaction. In this approach, for example, we can choose
>   #4 for the transaction which should be visible on the
>   standby as soon as a "success" of the commit has been
>   returned to a client. We can also choose #1 for
>   time-critical but not mission-critical transaction.
> 
> * Mix
>   Allow users to specify the level per standby and
>   transaction at the same time, and then calculate the real
>   level from them by using some algorithm.
> 
> Which should we adopt for 9.1? I'd like to implement the
> "per-standby" approach at first since it's simple and seems
> to cover more use cases. Thought?

-1

Synchronous replication implies that a commit should wait. This wait is
experienced by the transaction, not by other parts of the system. If we
define robustness at the standby level then robustness depends upon
unseen administrators, as well as the current up/down state of standbys.
This is action-at-a-distance in its worst form. 

Imagine having 2 standbys, 1 synch, 1 async. If the synch server goes
down, performance will improve and robustness will have been lost. What
good would that be?

Imagine a standby connected over a long distance. DBA brings up standby
in synch mode accidentally and the primary server hits massive
performance problems without any way of directly controlling this.

The worst aspect of standby-level controls is that nobody ever knows how
safe a transaction is. There is no definition or test for us to check
exactly how safe any particular transaction is. Also, the lack of safety
occurs at the time when you least want it - when one of your servers is
already down.

So I call "per-standby" settings simple, and broken in multiple ways.

Putting the control in the hands of the transaction owner (i.e. on the
master) is exactly where the control should be. I personally like the
idea of that being a USERSET, though could live with system wide
settings if need be. But the control must be on the *master* not on the
standbys.

The best parameter we can specify is the number of servers that we wish
to wait for confirmation from. That is a definition that easily manages
the complexity of having various servers up/down at any one time. It
also survives misconfiguration more easily, as well as providing a
workaround if replicating across a bursty network where we can't
guarantee response times, even of the typical response time is good.

(We've discussed this many times before over a period of years and not
really sure why we have to re-discuss this repeatedly just because
people disagree. You don't mention the earlier discussions, not sure
why. If we want to follow the community process, then all previous
discussions need to be taken into account, unless things have changed -
which they haven't: same topic, same people, AFAICS.)


-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Mon, 2010-05-24 at 18:29 -0700, Josh Berkus wrote:

> If people agree that the above is our roadmap, implementing
> "per-standby" first makes sense, and then we can implement "per-session"
> GUC later.

IMHO "per-standby" sounds simple, but is dangerously simplistic,
explained on another part of the thread.

We need to think clearly about failure modes and how they will be
handled. Failure modes and edge cases completely govern the design here.
"All running smoothly" isn't a major concern and so it appears that the
user interface can be done various ways.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Tue, May 25, 2010 at 12:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Synchronous replication implies that a commit should wait. This wait is
> experienced by the transaction, not by other parts of the system. If we
> define robustness at the standby level then robustness depends upon
> unseen administrators, as well as the current up/down state of standbys.
> This is action-at-a-distance in its worst form.

Maybe, but I can't help thinking people are going to want some form of
this.  The case where someone wants to do sync rep to the machine in
the next rack over and async rep to a server at a remote site seems
too important to ignore.

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


Re: Synchronization levels in SR

From
"Joshua D. Drake"
Date:
On Tue, 2010-05-25 at 12:40 -0400, Robert Haas wrote:
> On Tue, May 25, 2010 at 12:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > Synchronous replication implies that a commit should wait. This wait is
> > experienced by the transaction, not by other parts of the system. If we
> > define robustness at the standby level then robustness depends upon
> > unseen administrators, as well as the current up/down state of standbys.
> > This is action-at-a-distance in its worst form.
>
> Maybe, but I can't help thinking people are going to want some form of
> this.  The case where someone wants to do sync rep to the machine in
> the next rack over and async rep to a server at a remote site seems
> too important to ignore.

Uhh yeah, that is pretty much the standard use case. The "next rack" is
only 50% of the equation. The next part is the disaster recovery rack
over 100Mb (or even 10Mb) that is half way across the country. It is
common, very common.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Confused about the buffer pool size

From
MMK
Date:
Hello All:

In the code (costsize.c), I see that effective_cache_size is set to DEFAULT_EFFECTIVE_CACHE_SIZE.

This is defined as follows in cost.h

#define DEFAULT_EFFECTIVE_CACHE_SIZE 16384

But when I say 

show shared_buffers in psql I get,

shared_buffers 
----------------
 28MB

In postgresql.conf file, the following lines appear

shared_buffers = 28MB                   # min 128kB
           # (change requires restart)
#temp_buffers = 8MB                     # min 800kB


So I am assuming that the buffer pool size is 28MB = 28 * 128 = 3584 8K pages.

So should effective_cache_size be set to 3584 rather than the 16384?

Thanks,

MMK.












Re: Synchronization levels in SR

From
"Kevin Grittner"
Date:
Robert Haas <robertmhaas@gmail.com> wrote:
> Simon Riggs <simon@2ndquadrant.com> wrote:
>> If we define robustness at the standby level then robustness
>> depends upon unseen administrators, as well as the current
>> up/down state of standbys.  This is action-at-a-distance in its
>> worst form.
> 
> Maybe, but I can't help thinking people are going to want some
> form of this.  The case where someone wants to do sync rep to the
> machine in the next rack over and async rep to a server at a
> remote site seems too important to ignore.
I think there may be a terminology issue here -- I took "configure
by standby" to mean that *at the master* you would specify rules for
each standby.  I think Simon took it to mean that each standby would
define the rules for replication to it.  Maybe this issue can
resolve gracefully with a bit of clarification?
-Kevin


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 12:40 -0400, Robert Haas wrote:
> On Tue, May 25, 2010 at 12:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > Synchronous replication implies that a commit should wait. This wait is
> > experienced by the transaction, not by other parts of the system. If we
> > define robustness at the standby level then robustness depends upon
> > unseen administrators, as well as the current up/down state of standbys.
> > This is action-at-a-distance in its worst form.
> 
> Maybe, but I can't help thinking people are going to want some form of
> this.  
> The case where someone wants to do sync rep to the machine in
> the next rack over and async rep to a server at a remote site seems
> too important to ignore.

The use case of "machine in the next rack over and async rep to a server
at a remote site" *is* important, but you give no explanation as to why
that implies "per-standby" is the solution to it.

If you read the rest of my email, you'll see that I have explained the
problems "per-standby" settings would cause.

Please don't be so quick to claim it is me ignoring anything.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 11:52 -0500, Kevin Grittner wrote:
> Robert Haas <robertmhaas@gmail.com> wrote:
> > Simon Riggs <simon@2ndquadrant.com> wrote:
> >> If we define robustness at the standby level then robustness
> >> depends upon unseen administrators, as well as the current
> >> up/down state of standbys.  This is action-at-a-distance in its
> >> worst form.
> > 
> > Maybe, but I can't help thinking people are going to want some
> > form of this.  The case where someone wants to do sync rep to the
> > machine in the next rack over and async rep to a server at a
> > remote site seems too important to ignore.
>  
> I think there may be a terminology issue here -- I took "configure
> by standby" to mean that *at the master* you would specify rules for
> each standby.  I think Simon took it to mean that each standby would
> define the rules for replication to it.  Maybe this issue can
> resolve gracefully with a bit of clarification?

The use case of "machine in the next rack over and async rep to a server
at a remote site" would require the settings

server.nextrack = synch
server.remotesite = async

which leaves open the question of what happens when "nextrack" is down.

In many cases, to give adequate performance in that situation people add
an additional server, so the config becomes

server.nextrack1 = synch
server.nextrack2 = synch
server.remotesite = async

We then want to specify for performance reasons that we can get a reply
from either nextrack1 or nextrack2, so it all still works safely and
quickly if one of them is down. How can we express that rule concisely?
With some difficulty.

My suggestion is simply to have a single parameter (name unimportant)

number_of_synch_servers_we_wait_for = N

which is much easier to understand because it is phrased in terms of the
guarantee given to the transaction, not in terms of what the admin
thinks is the situation.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Tue, May 25, 2010 at 1:10 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Tue, 2010-05-25 at 11:52 -0500, Kevin Grittner wrote:
>> Robert Haas <robertmhaas@gmail.com> wrote:
>> > Simon Riggs <simon@2ndquadrant.com> wrote:
>> >> If we define robustness at the standby level then robustness
>> >> depends upon unseen administrators, as well as the current
>> >> up/down state of standbys.  This is action-at-a-distance in its
>> >> worst form.
>> >
>> > Maybe, but I can't help thinking people are going to want some
>> > form of this.  The case where someone wants to do sync rep to the
>> > machine in the next rack over and async rep to a server at a
>> > remote site seems too important to ignore.
>>
>> I think there may be a terminology issue here -- I took "configure
>> by standby" to mean that *at the master* you would specify rules for
>> each standby.  I think Simon took it to mean that each standby would
>> define the rules for replication to it.  Maybe this issue can
>> resolve gracefully with a bit of clarification?
>
> The use case of "machine in the next rack over and async rep to a server
> at a remote site" would require the settings
>
> server.nextrack = synch
> server.remotesite = async
>
> which leaves open the question of what happens when "nextrack" is down.
>
> In many cases, to give adequate performance in that situation people add
> an additional server, so the config becomes
>
> server.nextrack1 = synch
> server.nextrack2 = synch
> server.remotesite = async
>
> We then want to specify for performance reasons that we can get a reply
> from either nextrack1 or nextrack2, so it all still works safely and
> quickly if one of them is down. How can we express that rule concisely?
> With some difficulty.

Perhaps the difficulty here is that those still look like per-server
settings to me.  Just maybe with a different set of semantics.

> My suggestion is simply to have a single parameter (name unimportant)
>
> number_of_synch_servers_we_wait_for = N
>
> which is much easier to understand because it is phrased in terms of the
> guarantee given to the transaction, not in terms of what the admin
> thinks is the situation.

So I agree that we need to talk about whether or not we want to do
this.  I'll give my opinion.  I am not sure how useful this really is.Consider a master with two standbys.  The master
commitsa 
transaction and waits for one of the two standbys, then acknowledges
the commit back to the user.  Then the master crashes.  Now what?
It's not immediately obvious which standby we should being online as
the primary, and if we guess wrong we could lose transactions thought
to be committed.  This is probably a solvable problem, with enough
work: we can write a script to check the last LSN received by each of
the two standbys and promote whichever one is further along.

But... what happens if the master and one standby BOTH crash
simultaneously?  There's no way of knowing (until we get at least one
of them back up) whether it's safe to promote the other standby.

I like the idea of a "quorum commit" type feature where we promise the
user that things are committed when "enough" servers have acknowledged
the commit.  But I think most people are not going to want that
configuration unless we also provide some really good management tools
that we don't have today.

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


Re: Confused about the buffer pool size

From
Heikki Linnakangas
Date:
On 25/05/10 19:49, MMK wrote:
> Hello All:
> In the code (costsize.c), I see that effective_cache_size is set to DEFAULT_EFFECTIVE_CACHE_SIZE.
> This is defined as follows in cost.h
> #define DEFAULT_EFFECTIVE_CACHE_SIZE 16384
> But when I say
> show shared_buffers in psql I get,
> shared_buffers ---------------- 28MB
> In postgresql.conf file, the following lines appear
> shared_buffers = 28MB                   # min 128kB           # (change requires restart)#temp_buffers = 8MB
          # min 800kB
 
>
> So I am assuming that the buffer pool size is 28MB = 28 * 128 = 3584 8K pages.
> So should effective_cache_size be set to 3584 rather than the 16384?

No. Please see the manual for what effective_cache_size means:

http://www.postgresql.org/docs/8.4/interactive/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 13:31 -0400, Robert Haas wrote:
> On Tue, May 25, 2010 at 1:10 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Tue, 2010-05-25 at 11:52 -0500, Kevin Grittner wrote:
> >> Robert Haas <robertmhaas@gmail.com> wrote:
> >> > Simon Riggs <simon@2ndquadrant.com> wrote:
> >> >> If we define robustness at the standby level then robustness
> >> >> depends upon unseen administrators, as well as the current
> >> >> up/down state of standbys.  This is action-at-a-distance in its
> >> >> worst form.
> >> >
> >> > Maybe, but I can't help thinking people are going to want some
> >> > form of this.  The case where someone wants to do sync rep to the
> >> > machine in the next rack over and async rep to a server at a
> >> > remote site seems too important to ignore.
> >>
> >> I think there may be a terminology issue here -- I took "configure
> >> by standby" to mean that *at the master* you would specify rules for
> >> each standby.  I think Simon took it to mean that each standby would
> >> define the rules for replication to it.  Maybe this issue can
> >> resolve gracefully with a bit of clarification?
> >
> > The use case of "machine in the next rack over and async rep to a server
> > at a remote site" would require the settings
> >
> > server.nextrack = synch
> > server.remotesite = async
> >
> > which leaves open the question of what happens when "nextrack" is down.
> >
> > In many cases, to give adequate performance in that situation people add
> > an additional server, so the config becomes
> >
> > server.nextrack1 = synch
> > server.nextrack2 = synch
> > server.remotesite = async
> >
> > We then want to specify for performance reasons that we can get a reply
> > from either nextrack1 or nextrack2, so it all still works safely and
> > quickly if one of them is down. How can we express that rule concisely?
> > With some difficulty.
> 
> Perhaps the difficulty here is that those still look like per-server
> settings to me.  Just maybe with a different set of semantics.

(Those are the per-server settings.)

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 19:08 +0200, Alastair Turner wrote:
> On Tue, May 25, 2010 at 6:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> .......
> >
> > The best parameter we can specify is the number of servers that we wish
> > to wait for confirmation from. That is a definition that easily manages
> > the complexity of having various servers up/down at any one time. It
> > also survives misconfiguration more easily, as well as providing a
> > workaround if replicating across a bursty network where we can't
> > guarantee response times, even of the typical response time is good.
> >
> 
> This may be an incredibly naive question, but what happens to the
> transaction on the master if the number of confirmations is not
> received? Is this intended to create a situation where the master
> effectively becomes unavailable for write operations when its
> synchronous slaves are unavailable?

How we handle degraded mode is important, yes. Whatever parameters we
choose the problem will remain the same.

Should we just ignore degraded mode and respond as if nothing bad had
happened? Most people would say not.

If we specify server1 = synch and server2 = async we then also need to
specify what happens if server1 is down. People might often specifyif (server1 == down) server2 = synch.
So now we have 3 configuration settings, one quite complex.

It's much easier to say you want to wait for N servers to respond, but
don't care which they are. One parameter, simple and flexible.

In both cases, we have to figure what to do if we can't get either
server to respond. In replication there is no such thing as "server
down" just a "server didn't reply in time X". So we need to define
timeouts.

So whatever we do, we need additional parameters to specify timeouts
(including wait-forever as an option) and action-on-timeout: commit or
rollback. 

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 13:31 -0400, Robert Haas wrote:
> So I agree that we need to talk about whether or not we want to do
> this.  I'll give my opinion.  I am not sure how useful this really is.
>  Consider a master with two standbys.  The master commits a
> transaction and waits for one of the two standbys, then acknowledges
> the commit back to the user.  Then the master crashes.  Now what?
> It's not immediately obvious which standby we should being online as
> the primary, and if we guess wrong we could lose transactions thought
> to be committed.  This is probably a solvable problem, with enough
> work: we can write a script to check the last LSN received by each of
> the two standbys and promote whichever one is further along.
> 
> But... what happens if the master and one standby BOTH crash
> simultaneously?  There's no way of knowing (until we get at least one
> of them back up) whether it's safe to promote the other standby.

Not much of a problem really, is it? If you have one server left out of
3, then you promote it OR you stay down - your choice.

There is no "safe to promote" knowledge in *any* scenario; you never
know what was on the primary, only what was received by the standby. If
you have N standbys still up, you can pick which using the algorithm you
mention.

Remember that the WAL is sequential, so its not like the commit order of
transactions will differ across servers if we use quorum commit. So not
a problem.

The multiple simultaneous case is fairly common for people that pick the
"synch to server in next rack" because there's a 100 reasons why we'd
take out both at the same time, ask JD.

> I like the idea of a "quorum commit" type feature where we promise the
> user that things are committed when "enough" servers have acknowledged
> the commit.  But I think most people are not going to want that
> configuration unless we also provide some really good management tools
> that we don't have today.

Good name.

Management tools has nothing to do with this; completely orthogonal.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Alastair Turner
Date:
On Tue, May 25, 2010 at 6:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
.......
>
> The best parameter we can specify is the number of servers that we wish
> to wait for confirmation from. That is a definition that easily manages
> the complexity of having various servers up/down at any one time. It
> also survives misconfiguration more easily, as well as providing a
> workaround if replicating across a bursty network where we can't
> guarantee response times, even of the typical response time is good.
>

This may be an incredibly naive question, but what happens to the
transaction on the master if the number of confirmations is not
received? Is this intended to create a situation where the master
effectively becomes unavailable for write operations when its
synchronous slaves are unavailable?

Alastair "Bell" Turner

^F5


Re: Synchronization levels in SR

From
Yeb Havinga
Date:
Simon Riggs wrote:
> How we handle degraded mode is important, yes. Whatever parameters we
> choose the problem will remain the same.
>
> Should we just ignore degraded mode and respond as if nothing bad had
> happened? Most people would say not.
>
> If we specify server1 = synch and server2 = async we then also need to
> specify what happens if server1 is down. People might often specify
>     if (server1 == down) server2 = synch.
>   
I have a hard time imagining including async servers in the quorum. If 
an async servers vote is necessary to reach quorum due to a 'real' sync 
standby server failure, it would mean that the async-intended standby is 
now also in sync with the master transactions. IMHO this is a bad 
situation, since instead of the DBA getting the error: "not enough sync 
standbys to reach quorum", he'll now get "database is slow" complaints, 
only to find out later that too much sync standby servers went south. 
(under the assumption that async servers are mostly on too slow links to 
consider for sync standby).

regards,
Yeb Havinga



Re: Synchronization levels in SR

From
Dimitri Fontaine
Date:
Hi,

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Tue, 2010-05-25 at 19:08 +0200, Alastair Turner wrote:
>> On Tue, May 25, 2010 at 6:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> > The best parameter we can specify is the number of servers that we wish
>> > to wait for confirmation from. 
>> 
>> This may be an incredibly naive question, but what happens to the
>> transaction on the master if the number of confirmations is not
>> received? 
>
> It's much easier to say you want to wait for N servers to respond, but
> don't care which they are. One parameter, simple and flexible.
[...]
> So whatever we do, we need additional parameters to specify timeouts
> (including wait-forever as an option) and action-on-timeout: commit or
> rollback. 

I was preparing an email on the line that we need each slave to declare
its desired minimum level of synchronicity, and have the master filter
that with what the transaction wants.

Scratch that.

Thinking about it some more, I see that Simon's proposal is both more
simple and effective: we already have Hot Standby and admin functions
that tells us the last replayed LSN. The bigger wins. So in case of
failover we know which slave to choose.

The only use case I can see for what I had in mind is to allow the user
to choose which server is trusted to have accurate data or better read
only performances. But if the link is slow, the code will soon enough
notice, mind you.

I'm still not sure about my preference here, but I can see why Simon's
proposal is simpler and addresses all concerns apart from forcing the
servers into a non-optimal setup for a gain that is uneasy to see.

Regards,
-- 
dim


Re: Confused about the buffer pool size

From
MMK
Date:
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hello Heikki:<br /><br
/>Thisis what the documentation says (see below).<br /><br />But it does not tell my anything about what the actual
buffersize is.<br />How do I know what the real buffer size is? I am using 8.4.4 and I am running only one query at a
time.<br/><br />Cheers,<br /><br />MMK.<br /><br />Sets the planner's assumption about the effective size of the disk
cachethat is available to a single query. This is factored into estimates of the cost of using an index; a higher value
makesit more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When
settingthis parameter you should consider both <span class="PRODUCTNAME">PostgreSQL</span>'s shared buffers and the
portionof the kernel's disk cache that will be used for <span class="PRODUCTNAME">PostgreSQL</span> data files. Also,
takeinto account the expected number of concurrent queries on different tables, since they will have to share the
availablespace. This parameter has no effect on the size of shared memory allocated by <span
class="PRODUCTNAME">PostgreSQL</span>,nor does it reserve kernel disk cache; it is used only for estimation purposes.
Thedefault is 128 megabytes (<tt class="LITERAL">128MB</tt>). <br /><br /><br /><br />--- On <b>Tue, 5/25/10, Heikki
Linnakangas<i><heikki.linnakangas@enterprisedb.com></i></b> wrote:<br /><blockquote style="border-left: 2px solid
rgb(16,16, 255); margin-left: 5px; padding-left: 5px;"><br />From: Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com><br/>Subject: Re: [HACKERS] Confused about the buffer pool size<br />To:
"MMK"<bomuvi@yahoo.com><br />Cc: "PostgreSQL-development" <pgsql-hackers@postgresql.org><br />Date:
Tuesday,May 25, 2010, 11:36 AM<br /><br /><div class="plainMail">On 25/05/10 19:49, MMK wrote:<br />> Hello All:<br
/>>In the code (costsize.c), I see that effective_cache_size is set to DEFAULT_EFFECTIVE_CACHE_SIZE.<br />> This
isdefined as follows in cost.h<br />> #define DEFAULT_EFFECTIVE_CACHE_SIZE 16384<br />> But when I say<br />>
showshared_buffers in psql I get,<br />> shared_buffers ---------------- 28MB<br />> In postgresql.conf file, the
followinglines appear<br />> shared_buffers = 28MB                   # min 128kB           # (change requires
restart)#temp_buffers= 8MB                     # min 800kB<br />> <br />> So I am assuming that the buffer pool
sizeis 28MB = 28 * 128 = 3584 8K pages.<br />> So should effective_cache_size be set to 3584 rather than the
16384?<br/><br />No. Please see the manual for what effective_cache_size means:<br /><br /><a
href="http://www.postgresql.org/docs/8.4/interactive/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE"
target="_blank">http://www.postgresql.org/docs/8.4/interactive/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE</a><br
/><br/>--   Heikki Linnakangas<br />  EnterpriseDB   <a href="http://www.enterprisedb.com"
target="_blank">http://www.enterprisedb.com</a><br/><br />-- Sent via pgsql-hackers mailing list (<a
href="/mc/compose?to=pgsql-hackers@postgresql.org"
ymailto="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/>To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></div></blockquote></td></tr></table><br /> 

Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 21:19 +0200, Yeb Havinga wrote:
> Simon Riggs wrote:
> > How we handle degraded mode is important, yes. Whatever parameters we
> > choose the problem will remain the same.
> >
> > Should we just ignore degraded mode and respond as if nothing bad had
> > happened? Most people would say not.
> >
> > If we specify server1 = synch and server2 = async we then also need to
> > specify what happens if server1 is down. People might often specify
> >     if (server1 == down) server2 = synch.
> >   
> I have a hard time imagining including async servers in the quorum. If 
> an async servers vote is necessary to reach quorum due to a 'real' sync 
> standby server failure, it would mean that the async-intended standby is 
> now also in sync with the master transactions. IMHO this is a bad 
> situation, since instead of the DBA getting the error: "not enough sync 
> standbys to reach quorum", he'll now get "database is slow" complaints, 
> only to find out later that too much sync standby servers went south. 
> (under the assumption that async servers are mostly on too slow links to 
> consider for sync standby).

Yeh, there's difficulty either way. 

We don't need to think of servers as being "synch" or "async", more
likely we would rate them in terms of typical synchronisation delay. So
yeh, calling them "fast" and "slow" in terms of synchronisation delay
makes sense.

Some people with low xact rate and high need for protection might want
to switch across to the slow server and keep running. If not, the
max_synch_delay would trip and you would then select
synch_failure_action = rollback. 

The realistic response is to add a second "fast" sync server, to allow
you to stay up even when you lose one of the fast servers. That now
gives you 4 servers and the failure modes start to get real complex.

Specifying rules to achieve what you're after would be much harder. Some
people might want that, but most people won't in the general case and if
they did specify them they'd likely get them wrong.

All of these issues show why I want to specify the synchronisation mode
as a USERSET. That will allow us to specify more easily which parts of
our application are important when the cluster is degraded and which
data is so critical it must reach multiple servers.

-- Simon Riggs           www.2ndQuadrant.com



Re: Confused about the buffer pool size

From
Josh Berkus
Date:
MMK,

> But it does not tell my anything about what the actual buffer size is.
> How do I know what the real buffer size is? I am using 8.4.4 and I am
> running only one query at a time.

Please move this discussion to the pgsql-general or pgsql-performance
lists.  pgsql-hackers is for working on PostgreSQL code, and further
questions on this list will probably not be answered.

Other than that, I have no idea what you mean by "buffer size", nor why
you need to know it.  I'd suggest starting your post on the other
mailing list by explaining what specific problem you're trying to solve.

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


Re: Synchronization levels in SR

From
"Joshua D. Drake"
Date:
On Tue, 2010-05-25 at 12:40 -0400, Robert Haas wrote:
> On Tue, May 25, 2010 at 12:28 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > Synchronous replication implies that a commit should wait. This wait is
> > experienced by the transaction, not by other parts of the system. If we
> > define robustness at the standby level then robustness depends upon
> > unseen administrators, as well as the current up/down state of standbys.
> > This is action-at-a-distance in its worst form.
> 
> Maybe, but I can't help thinking people are going to want some form of
> this.  The case where someone wants to do sync rep to the machine in
> the next rack over and async rep to a server at a remote site seems
> too important to ignore.

Uhh yeah, that is pretty much the standard use case. The "next rack" is
only 50% of the equation. The next part is the disaster recovery rack
over 100Mb (or even 10Mb) that is half way across the country. It is
common, very common.

Joshua D. Drake

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering




Re: Synchronization levels in SR

From
Florian Pflug
Date:
On May 25, 2010, at 22:16 , Simon Riggs wrote:
> All of these issues show why I want to specify the synchronisation mode
> as a USERSET. That will allow us to specify more easily which parts of
> our application are important when the cluster is degraded and which
> data is so critical it must reach multiple servers.


Hm, but since flushing a important COMMIT to the slave(s) will also need to flush all previous (potentially
unimportant)COMMITs to the slave(s), isn't there a substantial chance of priority-inversion type problems there? 

Then again, if asynchronous_commit proved to be effective than so will this probably, so maybe my fear is unjustified.

best regards,
Florian Pflug



Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, May 26, 2010 at 2:10 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> My suggestion is simply to have a single parameter (name unimportant)
>
> number_of_synch_servers_we_wait_for = N
>
> which is much easier to understand because it is phrased in terms of the
> guarantee given to the transaction, not in terms of what the admin
> thinks is the situation.

How can we choose #2, #3 or #4 by using your proposed option?

As the result of the discussion, I'm inclined towards choosing the
"mix" approach. How about specifying #1, #2, #3 or #4 per standby,
and specifying the number of "synchronous" (i.e., means #2, #3 or
#4) standbys the transaction commit waits for at the master as
Simon suggests?

We add new option "replication_mode" (better name?) specifying
when the standby sends the ACK meaning the completion of replication
to the master into recovery.conf. Valid values are "async", "recv",
"fsync" and "redo". Those correspond to #1, #2, #3 and #4 I defined
on the top of the thread.

If "async", the standby never sends any ACK. If "recv", "fsync",
or "redo", the standby sends the ACK when it has received, fsynced
or replayed the WAL from the master, respectively.

On the other hand, we add new GUC "max_synchronous_standbys"
(I prefer it to "number_of_synch_servers_we_wait_for", but does
anyone have better name?) as PGC_USERSET into postgresql.conf.
It specifies the maximum number of standbys which transaction
commit must wait for the ACK from.

If max_synchronous_standbys is 0, no transaction commit waits for
ACK even if some connected standbys set their replication_mode to
"recv", "fsync" or "redo". If it's positive, transaction comit waits
for N ACKs. N is the smaller number between max_synchronous_standbys
and the actual number of connected "synchronous" standbys.

Thought?

Regards,

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


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Tue, May 25, 2010 at 11:36 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Wed, May 26, 2010 at 2:10 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> My suggestion is simply to have a single parameter (name unimportant)
>>
>> number_of_synch_servers_we_wait_for = N
>>
>> which is much easier to understand because it is phrased in terms of the
>> guarantee given to the transaction, not in terms of what the admin
>> thinks is the situation.
>
> How can we choose #2, #3 or #4 by using your proposed option?
>
> As the result of the discussion, I'm inclined towards choosing the
> "mix" approach. How about specifying #1, #2, #3 or #4 per standby,
> and specifying the number of "synchronous" (i.e., means #2, #3 or
> #4) standbys the transaction commit waits for at the master as
> Simon suggests?
>
> We add new option "replication_mode" (better name?) specifying
> when the standby sends the ACK meaning the completion of replication
> to the master into recovery.conf. Valid values are "async", "recv",
> "fsync" and "redo". Those correspond to #1, #2, #3 and #4 I defined
> on the top of the thread.
>
> If "async", the standby never sends any ACK. If "recv", "fsync",
> or "redo", the standby sends the ACK when it has received, fsynced
> or replayed the WAL from the master, respectively.
>
> On the other hand, we add new GUC "max_synchronous_standbys"
> (I prefer it to "number_of_synch_servers_we_wait_for", but does
> anyone have better name?) as PGC_USERSET into postgresql.conf.
> It specifies the maximum number of standbys which transaction
> commit must wait for the ACK from.
>
> If max_synchronous_standbys is 0, no transaction commit waits for
> ACK even if some connected standbys set their replication_mode to
> "recv", "fsync" or "redo". If it's positive, transaction comit waits
> for N ACKs. N is the smaller number between max_synchronous_standbys
> and the actual number of connected "synchronous" standbys.
>
> Thought?

I think we're over-engineering this.  For a first version we should do
something simple.  Then we can add some of these extra knobs in a
follow-on patch.  Quorum commit is definitely an extra knob, IMHO.

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, May 26, 2010 at 1:04 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Tue, 2010-05-25 at 12:40 +0900, Fujii Masao wrote:
>> On Tue, May 25, 2010 at 10:29 AM, Josh Berkus <josh@agliodbs.com> wrote:
>> > I agree that #4 should be done last, but it will be needed, not in the
>> > least by your employer ;-) .  I don't see any obvious way to make #4
>> > compatible with any significant query load on the slave, but in general
>> > I'd think that users of #4 are far more concerned with 0% data loss than
>> > they are with getting the slave to run read queries.
>>
>> Since #2 and #3 are enough for 0% data loss, I think that such users
>> would be more concerned about what results are visible in the standby.
>> No?
>
> Please add #4 also. You can do that easily at the same time as #2 and
> #3, and it will leave me free to fix the perceived conflict problems.

I think that we should implement the feature in small steps rather than
submit one big patch at a time. So I'd like to focus on #2 and #3 at first,
and #4 later (maybe third or fourth CF).

Regards,

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-05-25 at 23:59 -0400, Robert Haas wrote:
> Quorum commit is definitely an extra knob, IMHO.

No, its about three less, as I have explained.

Explain your position, don't just demand others listen.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 13:03 +0900, Fujii Masao wrote:
> On Wed, May 26, 2010 at 1:04 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Tue, 2010-05-25 at 12:40 +0900, Fujii Masao wrote:
> >> On Tue, May 25, 2010 at 10:29 AM, Josh Berkus <josh@agliodbs.com> wrote:
> >> > I agree that #4 should be done last, but it will be needed, not in the
> >> > least by your employer ;-) .  I don't see any obvious way to make #4
> >> > compatible with any significant query load on the slave, but in general
> >> > I'd think that users of #4 are far more concerned with 0% data loss than
> >> > they are with getting the slave to run read queries.
> >>
> >> Since #2 and #3 are enough for 0% data loss, I think that such users
> >> would be more concerned about what results are visible in the standby.
> >> No?
> >
> > Please add #4 also. You can do that easily at the same time as #2 and
> > #3, and it will leave me free to fix the perceived conflict problems.
> 
> I think that we should implement the feature in small steps rather than
> submit one big patch at a time. So I'd like to focus on #2 and #3 at first,
> and #4 later (maybe third or fourth CF).

We both know if you do #2 and #3 then doing #4 also is trivial.

If you leave it out then we'll end up missing something that is required
and have to rework everything.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 12:36 +0900, Fujii Masao wrote:
> On Wed, May 26, 2010 at 2:10 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > My suggestion is simply to have a single parameter (name unimportant)
> >
> > number_of_synch_servers_we_wait_for = N
> >
> > which is much easier to understand because it is phrased in terms of the
> > guarantee given to the transaction, not in terms of what the admin
> > thinks is the situation.
> 
> How can we choose #2, #3 or #4 by using your proposed option?

> If "async", the standby never sends any ACK. If "recv", "fsync",
> or "redo", the standby sends the ACK when it has received, fsynced
> or replayed the WAL from the master, respectively.

Everything I've said about "per-standby" settings applies here, which
was based upon having just 2 settings: sync and async. If you have four
settings instead, things get even more complex. If we were going to
reduce complexity, it would be to reduce the number of options here to
just offering option #2 in the first phase.

AFAICS people would only ever select #2 or #4 anyway. IMHO #3 isn't
likely to be selected on its own because it performs badly for no real
benefit. Having two standbys, I might want to specify #2 to both, or if
one is down then #3 to the remaining standby instead.

Nobody else has yet tried to explain how we would specify what happens
when one of the standbys is down, with per-standby settings. Failure
modes are where the complexity is here. However we proceed, we must have
a discussion about how we specify the failure modes. This is not
something we should add on at the last minute, we should think about
that now and address it openly.

Oracle Data Guard is a great resource for what semantics we might need
to cover, but its also a lesson in complexity from its per-standby
settings. Please look at net_timeout and alternate options in
particular. See how difficult it is to specify failure modes, even
though Data Guard offers probably dozens of parameters and options - its
orientation is per-standby not towards the transaction and the user.

> On the other hand, we add new GUC "max_synchronous_standbys"
> (I prefer it to "number_of_synch_servers_we_wait_for", but does
> anyone have better name?) as PGC_USERSET into postgresql.conf.
> It specifies the maximum number of standbys which transaction
> commit must wait for the ACK from.
> 
> If max_synchronous_standbys is 0, no transaction commit waits for
> ACK even if some connected standbys set their replication_mode to
> "recv", "fsync" or "redo". If it's positive, transaction comit waits
> for N ACKs. N is the smaller number between max_synchronous_standbys
> and the actual number of connected "synchronous" standbys.

To summarise, I think we can get away with just 3 parameters:
synchronous_replication = N     # similar in name to synchronous_commit
synch_rep_timeout = T
synch_rep_timeout_action = commit | abort

Conceptually, this is "I want at least N replica copies made of my
database changes, I will wait for up to T milliseconds to get that
otherwise I will do X". Very easy and clear for an application to
understand what guarantees it is requesting. Also very easy for the
administrator to understand the guarantees requested and how to
provision for them: to deliver robustness they typically need N+1
servers, or for even higher levels of robustness and performance N+2
etc..

Making synchronous_replication into a USERSET would be an industry
first: transaction controlled robustness at every level.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, May 26, 2010 at 5:02 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Everything I've said about "per-standby" settings applies here, which
> was based upon having just 2 settings: sync and async. If you have four
> settings instead, things get even more complex. If we were going to
> reduce complexity, it would be to reduce the number of options here to
> just offering option #2 in the first phase.
>
> AFAICS people would only ever select #2 or #4 anyway. IMHO #3 isn't
> likely to be selected on its own because it performs badly for no real
> benefit. Having two standbys, I might want to specify #2 to both, or if
> one is down then #3 to the remaining standby instead.

I guess that dropping the support of #3 doesn't reduce complexity since
the code of #3 is almost the same as that of #2. Like walreceiver sends
the ACK after receiving the WAL in #2 case, it has only to do the same
thing after the WAL flush.

> Nobody else has yet tried to explain how we would specify what happens
> when one of the standbys is down, with per-standby settings. Failure
> modes are where the complexity is here. However we proceed, we must have
> a discussion about how we specify the failure modes. This is not
> something we should add on at the last minute, we should think about
> that now and address it openly.

>> Imagine having 2 standbys, 1 synch, 1 async. If the synch server goes
>> down, performance will improve and robustness will have been lost. What
>> good would that be?

You are concerned about the above case you described on another post?
In that case, if you want to ensure robustness, you can specify #2, #3
or #4 in both standbys. If one of standbys is in remote site, we can
additionally set max_synchronous_standbys to 1. If you don't want to
failover to the standby in remote site when the master goes down, you
can specify #1 in remote standby, so the standby in the near location
is always guaranteed to be synch with the master.

> Oracle Data Guard is a great resource for what semantics we might need
> to cover, but its also a lesson in complexity from its per-standby
> settings. Please look at net_timeout and alternate options in
> particular. See how difficult it is to specify failure modes, even
> though Data Guard offers probably dozens of parameters and options - its
> orientation is per-standby not towards the transaction and the user.

Yeah, I'll research Oracle Data Guard.

> To summarise, I think we can get away with just 3 parameters:
> synchronous_replication = N     # similar in name to synchronous_commit
> synch_rep_timeout = T
> synch_rep_timeout_action = commit | abort

I agree to add the latter two parameters, which are also listed on
my outline of SynchRep.
http://wiki.postgresql.org/wiki/Streaming_Replication#Synchronization_capability

> Conceptually, this is "I want at least N replica copies made of my
> database changes, I will wait for up to T milliseconds to get that
> otherwise I will do X". Very easy and clear for an application to
> understand what guarantees it is requesting. Also very easy for the
> administrator to understand the guarantees requested and how to
> provision for them: to deliver robustness they typically need N+1
> servers, or for even higher levels of robustness and performance N+2
> etc..

I don't feel that "synchronous_replication" approach is intuitive for
the administrator. Even on this thread, some people seem to prefer
"per-standby" setting.

Without "per-standby" setting, when there are two standbys, one is in
the near rack and another is in remote site, "synchronous_replication=1"
cannot guarantee that the near standby is always synch with the master.
So when the master goes down, unfortunately we might have to failover to
the remote standby. OTOH, "synchronous_replication=2" degrades the
performance on the master very much. "synchronous_replication" approach
doesn't seem to cover the typical use case.

Also, when "synchronous_replication=1" and one of synchronous standbys
goes down, how should the surviving standby catch up with the master?
Such standby might be too far behind the master. The transaction commit
should wait for the ACK from the lagging standby immediately even if
there might be large gap? If yes, "synch_rep_timeout" would screw up
the replication easily.

Regards,

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


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, May 26, 2010 at 2:31 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Tue, 2010-05-25 at 23:59 -0400, Robert Haas wrote:
>> Quorum commit is definitely an extra knob, IMHO.
>
> No, its about three less, as I have explained.
>
> Explain your position, don't just demand others listen.

OK.  In words of one syllable, your way still has all the same knobs,
plus some more.

You sketched out a design which still had a per-standby setting for
each standby, but IN ADDITION had a setting for a setting to control
quorum commit[1].  You also argued that we needed four options for
each transaction rather than three[2], and that we need a userset GUC
to control the behavior on a per-transaction basis[3].  Not one other
person has agreed that we need all of these options in the first
version of the patch.  We don't.  We can start with a sync rep patch
that does ONE thing and does it well, and we can add these other
things later. I don't think I'm going too far out on a limb when I say
that it is easier to get a smaller patch committed than it is to get a
bigger one committed, and it is less likely to have bugs.

[1] http://archives.postgresql.org/pgsql-hackers/2010-05/msg01347.php
[2] http://archives.postgresql.org/pgsql-hackers/2010-05/msg01333.php
[3] http://archives.postgresql.org/pgsql-hackers/2010-05/msg01334.php

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


Re: Synchronization levels in SR

From
Alastair Turner
Date:
A suggestion, based on what I believe would be ideal default settings
for a fully developed SR capability. The thought being that as long as
the default behaviour was stable additional knobs could be added
across version boundaries without causing trouble.

Per slave the master needs to know:- The identity of the slave, even if only to limit who can replicate
(this will have to be specified)- Whether to expect an acknowledgement from the slave (as will this)- How long to wait
forthe acknowledgement (this may be a default)- What the slave is expected to do before acknowledging (I think this
 
should default to remote flush to disk - #3 in the mail which started
this thread - since it prevents data loss without exposing the master
to the possibility of locking delays)

Additionally the process on the master requires:- How many acknowledgments to require before declaring success
(defaulted to the number of servers expected to acknowledge since it
will cause the fewest surprises when failing over to a replica)- What to do if the number of acknowledgments is not
received
(defaulting to abort/rollback since this is really what differentiates
synchronous from asynchronous replication - the certainty that once
data has been committed it can be recovered)

So in order to set up synchronous replication all a DBA would have to
specify is the slave server, that it is expected to send
acknowledgments and possibly a timeout.

If this is in fact a desirable state for the default behaviour or
minimum settings requirement then I would say it is also a desirable
target for the first patch.

Alastair "Bell" Turner
^F5


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:

> I guess that dropping the support of #3 doesn't reduce complexity
> since the code of #3 is almost the same as that of #2. Like
> walreceiver sends the ACK after receiving the WAL in #2 case, it has
> only to do the same thing after the WAL flush.

Hmm, well the code for #3 is similar also to the code for #4. So if you
do #2, its easy to do #2, #3 and #4 together.

The comment is about whether having #3 makes sense from a user interface
perspective. It's easy to add options, but they must have useful
meaning.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:

> > To summarise, I think we can get away with just 3 parameters:
> > synchronous_replication = N     # similar in name to synchronous_commit
> > synch_rep_timeout = T
> > synch_rep_timeout_action = commit | abort
> 
> I agree to add the latter two parameters, which are also listed on
> my outline of SynchRep.
> http://wiki.postgresql.org/wiki/Streaming_Replication#Synchronization_capability
> 
> > Conceptually, this is "I want at least N replica copies made of my
> > database changes, I will wait for up to T milliseconds to get that
> > otherwise I will do X". Very easy and clear for an application to
> > understand what guarantees it is requesting. Also very easy for the
> > administrator to understand the guarantees requested and how to
> > provision for them: to deliver robustness they typically need N+1
> > servers, or for even higher levels of robustness and performance N+2
> > etc..
> 
> I don't feel that "synchronous_replication" approach is intuitive for
> the administrator. Even on this thread, some people seem to prefer
> "per-standby" setting.

Maybe they do, but that is because nobody has yet explained how you
would handle failure modes with per-standby settings. When you do they
will likely change their minds. Put the whole story on the table before
trying to force a decision.

> Without "per-standby" setting, when there are two standbys, one is in
> the near rack and another is in remote site, "synchronous_replication=1"
> cannot guarantee that the near standby is always synch with the master.
> So when the master goes down, unfortunately we might have to failover to
> the remote standby. 

If the remote server responded first, then that proves it is a better
candidate for failover than the one you think of as near. If the two
standbys vary over time then you have network problems that will
directly affect the performance on the master; synch_rep = N would
respond better to any such problems.

> OTOH, "synchronous_replication=2" degrades the
> performance on the master very much. 

Yes, but only because you have only one near standby. It would clearly
to be foolish to make this setting without 2+ near standbys. We would
then have 4 or more servers; how do we specify everything for that
config??

> "synchronous_replication" approach
> doesn't seem to cover the typical use case.

You described the failure modes for the quorum proposal, but avoided
describing the failure modes for the "per-standby" proposal.

Please explain what will happen when the near server is unavailable,
with per-standby settings. Please also explain what will happen if we
choose to have 4 or 5 servers to maintain performance in case of the
near server going down. How will we specify the failure modes?

> Also, when "synchronous_replication=1" and one of synchronous standbys
> goes down, how should the surviving standby catch up with the master?
> Such standby might be too far behind the master. The transaction commit
> should wait for the ACK from the lagging standby immediately even if
> there might be large gap? If yes, "synch_rep_timeout" would screw up
> the replication easily.

That depends upon whether we send the ACK at point #2, #3 or #4. It
would only cause a problem if you waited until #4.

I've explained why I have made the proposals I've done so far: reduced
complexity in failure modes and better user control. To understand that
better, you or somebody needs to explain how we would handle the failure
modes with "per-standby" settings so we can compare.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 07:10 -0400, Robert Haas wrote:

> OK.  In words of one syllable, your way still has all the same knobs,
> plus some more.

I explained how the per-standby settings would take many parameters,
whereas per-transaction settings take far fewer.

> You sketched out a design which still had a per-standby setting for
> each standby, but IN ADDITION had a setting for a setting to control
> quorum commit[1].

No, you misread it. Again. The parameters were not IN ADDITION -
obviously so, otherwise I wouldn't claim there were fewer, would I?

Your reply has again avoided the subject of how we would handle failure
modes with per-standby settings. That is important.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, May 26, 2010 at 9:54 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Wed, 2010-05-26 at 07:10 -0400, Robert Haas wrote:
>
>> OK.  In words of one syllable, your way still has all the same knobs,
>> plus some more.
>
> I explained how the per-standby settings would take many parameters,
> whereas per-transaction settings take far fewer.
>
>> You sketched out a design which still had a per-standby setting for
>> each standby, but IN ADDITION had a setting for a setting to control
>> quorum commit[1].
>
> No, you misread it. Again. The parameters were not IN ADDITION -
> obviously so, otherwise I wouldn't claim there were fewer, would I?

Well, that does seem logical, but I can't figure out how to reconcile
that with what you wrote before, because as far as I can see you're
just saying over and over again that your way will need fewer
parameters without explaining which parameters your way won't need.

And frankly, I don't think it's possible for quorum commit to reduce
the number of parameters.  Even if we have that feature available, not
everyone will want to use it.  And the people who don't will
presumably need whatever parameters they would have needed if quorum
commit hadn't been available in the first place.

> Your reply has again avoided the subject of how we would handle failure
> modes with per-standby settings. That is important.

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

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


Re: Synchronization levels in SR

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

Agreed, quorum commit is not a panacea.

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

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

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

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

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

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

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

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

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

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

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

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


Re: Synchronization levels in SR

From
"Kevin Grittner"
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> One way to do that would be to refrain from flushing the commit
> record to disk on the master until the standby has acknowledged
> it.
I'm not clear on the benefit of doing that, versus flushing the
commit record and then waiting for responses.  Either way some
databases will commit before others -- what is the benefit of having
the master lag?
> Adding a synchronous standby should require some action in the 
> master, since it affects the behavior on master.
+1
-Kevin


Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 26/05/10 20:10, Kevin Grittner wrote:
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  wrote:
>
>> One way to do that would be to refrain from flushing the commit
>> record to disk on the master until the standby has acknowledged
>> it.
>
> I'm not clear on the benefit of doing that, versus flushing the
> commit record and then waiting for responses.  Either way some
> databases will commit before others -- what is the benefit of having
> the master lag?

Hmm, I was going to answer that that way no other transactions can see 
the transaction as committed before it has been safely replicated, but I 
now realize that you could also flush, but refrain from releasing the 
entry from procarray until the standby acknowledges the commit, so the 
transaction would look like in-progress to other transactions in the 
master until that.

Although, if the master crashes at that point, and quickly recovers, you 
could see the last transactions committed on the master before they're 
replicated to the standby.

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 11:31 -0400, Robert Haas wrote:
> > Your reply has again avoided the subject of how we would handle failure
> > modes with per-standby settings. That is important.
> 
> I don't think anyone is avoiding that, we just haven't discussed it.

You haven't discussed it, but even before you do, you know its better.
Not very compelling perspective... 

> The thing is, I don't think quorum commit actually does anything to
> address that problem.  If I have a master and a standby configured for
> sync rep and the standby goes down, we have to decide what impact that
> has on the master.  If I have a master and two standbys configured for
> sync rep with quorum commit such that I only need an ack from one of
> them, and they both go down, we still have to decide what impact that
> has on the master.  

That's already been discussed, and AFAIK Masao and I already agreed on
how that would be handled in the quorum commit case.

What we haven't had explained is how you would handle all the sub cases
or failure modes for the per-standby situation.

The most common case for synch rep IMHO is this:

* 2 near standbys, 1 remote. Want to be able to ACK to first near
standby that responds, or if both are down, ACK to the remote.

I've proposed a way of specifying that with 3 simple parameters, e.g.
synch_rep_acks = 1
synch_rep_timeout = 30
synch_rep_timeout_action = commit

In Oracle this would be all of the following

* all nodes given unique names
DB_UNIQUE_NAME=master
DB_UNIQUE_NAME=near1
DB_UNIQUE_NAME=near2
DB_UNIQUE_NAME=remote

* parameter settings
LOG_ARCHIVE_CONFIG='DG_CONFIG=(master,near1, near2, remote)'

LOG_ARCHIVE_DEST_2='SERVICE=near1 SYNC AFFIRM NET_TIMEOUT=30
DB_UNIQUE_NAME=near1'
LOG_ARCHIVE_DEST_STATE_2='ENABLE'

LOG_ARCHIVE_DEST_3='SERVICE=near2 SYNC AFFIRM NET_TIMEOUT=30
DB_UNIQUE_NAME=near2'
LOG_ARCHIVE_DEST_STATE_3='ENABLE'

LOG_ARCHIVE_DEST_4='SERVICE=remote ASYNC NOAFFIRM DB_UNIQUE_NAME=remote'
LOG_ARCHIVE_DEST_STATE_4='ENABLE'

* modes
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;


The Oracle way doesn't allow you to specify that if near1 and near2 are
down then we should continue to SYNC via remote, nor does it allow you
to specify things from user perspective or at transaction level.

You don't need to do it that way, for sure. But we do need to say what
way you would pick, rather than just arguing against me before you've
even discussed it here or off-list.

> I agree we need to talk about, but I don't agree
> that putting in quorum commit will remove the need to design that
> case.

Yes, you need to design for that case. It's not a magic wand.

All I've said is that covering the common cases is easier and more
flexible by choosing transaction-centric style of parameters, and it
also allows user settable behaviour.

I want to do better than Oracle, if possible, using lessons learned. I
don't want to do the same thing because we're copying them or because
we're going down the same conceptual dead end they went down. We should
try to avoid doing something obvious and aim a little higher.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 12:10 -0500, Kevin Grittner wrote:
> > Adding a synchronous standby should require some action in the 
> > master, since it affects the behavior on master.
>  
> +1

+1
-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
"Kevin Grittner"
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> Although, if the master crashes at that point, and quickly
> recovers, you could see the last transactions committed on the
> master before they're replicated to the standby.
Versus having the transaction committed on one or more slaves but
not on the master?  Unless we have a transaction manager and do
proper distributed transactions, how do you avoid edge conditions
like that?
-Kevin


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 19:55 +0300, Heikki Linnakangas wrote:

> Now you want to set up a temporary replica of the master at a 
> development server, for testing purposes. If you set quorum to 2, your
> development server becomes critical infrastructure, which is not what 
> you want.

That's a good argument for standby relays. Nobody hooks in a disposable
test machine into a critical production config without expecting it to
have some effect.

> If you set quorum to 1, it also becomes critical 
> infrastructure, because it's possible that a transaction has been 
> replicated to the test server but not the real production standby, and
> a meteor strikes.

Why would you not want to use the test server? If its the only thing
left protecting you, and you wish to be protected, then it sounds very
cool to me. In my proposal this test server only gets data ahead of
other things if the "real production standby" responds too slowly.

It scares the **** out of people that a DBA can take down a server and
suddenly the sync protection you thought you had is turned off. That way
of doing things means an application never knows the protection level
any piece of data has had. App designers want to be able to marks things
"handle with care" or "just do it quick, don't care much". It's a real
pain to have to handle all your data the same, and for that to be
selectable only by administrators, who may or may not have everything
configured correctly/available.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, May 26, 2010 at 1:24 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 26/05/10 20:10, Kevin Grittner wrote:
>>
>> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  wrote:
>>
>>> One way to do that would be to refrain from flushing the commit
>>> record to disk on the master until the standby has acknowledged
>>> it.
>>
>> I'm not clear on the benefit of doing that, versus flushing the
>> commit record and then waiting for responses.  Either way some
>> databases will commit before others -- what is the benefit of having
>> the master lag?
>
> Hmm, I was going to answer that that way no other transactions can see the
> transaction as committed before it has been safely replicated, but I now
> realize that you could also flush, but refrain from releasing the entry from
> procarray until the standby acknowledges the commit, so the transaction
> would look like in-progress to other transactions in the master until that.
>
> Although, if the master crashes at that point, and quickly recovers, you
> could see the last transactions committed on the master before they're
> replicated to the standby.

No matter what you do, there's going to be corner cases where one node
thinks the transaction committed and the other node doesn't know.  At
any given time, we're either in a state where a crash and restart on
the master will replay the commit record, or we're not.  And also, but
somewhat independently, we're in a state where a crash on the standby
will replay the commit record, or we're not.  Each of these is
dependent on a disk write, and there's no way to guarantee that both
of those disk writes succeed or both of them fail.

Now, in theory, maybe you could have a system where we don't have a
fixed definition of who the master is.  If either server crashes or if
they lose communication, both crash.  If they both come back up, they
agree on who has the higher LSN on disk and both roll forward to that
point, then designate one server to be the master.  If one comes back
up and can't reach the other, it appeals to the clusterware for help.
The clusterware is then responsible for shooting one node in the head
and telling the other node to carry on as the sole survivor.  When,
eventually, the dead node is resurrected, it *discards* any WAL
written after the point from which the new master restarted.

Short of that, I don't think "abort the transaction" is a recovery
mechanism for when we can't get hold of a standby.  We're going to
have to commit locally first and then we can decide how long to wait
for an ACK that a standby has also committed the same transaction
remotely.  We can wait not at all, forever, or for a while and then
declare the other guy dead.

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


Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 26/05/10 20:33, Kevin Grittner wrote:
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  wrote:
>
>> Although, if the master crashes at that point, and quickly
>> recovers, you could see the last transactions committed on the
>> master before they're replicated to the standby.
>
> Versus having the transaction committed on one or more slaves but
> not on the master?  Unless we have a transaction manager and do
> proper distributed transactions, how do you avoid edge conditions
> like that?

Yeah, I guess you can't. You can guarantee that a commit is always 
safely flushed first in the master, or in the standby, but without 
two-phase commit you can't guarantee atomicity. It's useful to know 
which behavior you get, though, so that you can take it into account in 
your failover procedure.

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


Re: Synchronization levels in SR

From
"Kevin Grittner"
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
>> Unless we have a transaction manager and do proper distributed
>> transactions, how do you avoid edge conditions like that?
> 
> Yeah, I guess you can't. You can guarantee that a commit is
> always safely flushed first in the master, or in the standby, but
> without two-phase commit you can't guarantee atomicity. It's
> useful to know which behavior you get, though, so that you can
> take it into account in your failover procedure.
It strikes me that if you always write the commit for the master
first, there's at least a possibility of developing a heuristic for
getting a slave back in sync should the connection break.  If you
randomly update zero to N slaves and then have a failure, I don't
see much hope.
-Kevin


Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 26/05/10 20:40, Simon Riggs wrote:
> On Wed, 2010-05-26 at 19:55 +0300, Heikki Linnakangas wrote:
>> If you set quorum to 1, it also becomes critical
>> infrastructure, because it's possible that a transaction has been
>> replicated to the test server but not the real production standby, and
>> a meteor strikes.
>
> Why would you not want to use the test server?

Because your failover procedures known nothing about the test server. 
Even if the data is there in theory, it'd be completely impractical to 
fetch it from there.

> If its the only thing
> left protecting you, and you wish to be protected, then it sounds very
> cool to me.  In my proposal this test server only gets data ahead of
> other things if the "real production standby" responds too slowly.

There's many reasons why a test server could respond faster than the 
production standby. Maybe the standby is on a different continent. Maybe 
you have fsync=off on the test server because it's just a test server. 
Either way, you want the master to ignore it for the purpose of 
determining if a commit is safe.

> It scares the **** out of people that a DBA can take down a server and
> suddenly the sync protection you thought you had is turned off.

Yeah, it depends on what you're trying to accomplish. If durability is 
absolutely critical to you, (vs. availability), you don't want the 
commit to ever be acknowledged to the client until it's safely flushed 
to disk in the standby, even if it means refusing any further commits on 
the master, until the standby reconnects and catches up.

OTOH, if you're not that worried about durability, but you're load 
balancing queries to the standby, you want to ensure that when you run a 
query against the standby, a transaction that committed on the master is 
also visible in the standby. In that scenario, if a standby can't be 
reached, it is simply pronounced dead, and the master can just ignore it 
until it reconnects.

> That way
> of doing things means an application never knows the protection level
> any piece of data has had. App designers want to be able to marks things
> "handle with care" or "just do it quick, don't care much".

Yeah, that's useful too.

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


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, May 26, 2010 at 1:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Wed, 2010-05-26 at 11:31 -0400, Robert Haas wrote:
>> > Your reply has again avoided the subject of how we would handle failure
>> > modes with per-standby settings. That is important.
>>
>> I don't think anyone is avoiding that, we just haven't discussed it.
>
> You haven't discussed it, but even before you do, you know its better.
> Not very compelling perspective...

I don't really understand this comment.  I have said, and I believe,
that a system without quorum commit is simpler than one with quorum
commit.  I'd debate the point with you but I find the point so
self-evident that I don't even know where to begin arguing it.  I am
not saying, and I have not said, that we shouldn't have quorum commit.I am saying that it is not something that we need
toadd as part of 
an initial sync rep patch, because we can instead add it in a
follow-on patch.  As far as I can tell, we are not trying to decide
between two competing approaches and therefore do not need to decide
which one is better.

Everything you are proposing sounds useful and valuable.  I am not
sure whether it handles all of the use cases that folks might have.
For example, Heikki mentioned the case upthread of wanting to wait for
a commit ACK from one of two servers in the data center A and one of
two servers in data center B, rather than just two out of four servers
total.  So, we might need to think a little bit about whether we want
to handle those kinds of cases and what sort of infrastructure we
would need to support it.  But certainly I think quorum commit sounds
like a good feature and I hope it will be included in 9.1, or if not
9.1, then some future version.

What I don't agree with is that it needs to be part of the initial
implementation of sync rep.  If there's a case for doing that, I don't
believe it's been made on this thread.  At any rate, the fact that I
don't see it as a sine qua non for sync rep is neither obstructionism
nor an ad hominem attack.  It's simply an opinion, which I believe to
be based on solid technical reasoning, but which I might change my
mind about if someone convinces me that I'm looking at the problem the
wrong way.  That would an involve someone making an argument of the
following form: "If we don't implement quorum commit in the very first
implementation of sync rep, then it will be hard to add later because
X."  So far no one has done that.  You have made the similar argument
"If we do implement quorum commit in the very first version of sync
rep, it will save implementation work elsewhere" - but I don't think
that's true and I have explained why.

>> The thing is, I don't think quorum commit actually does anything to
>> address that problem.  If I have a master and a standby configured for
>> sync rep and the standby goes down, we have to decide what impact that
>> has on the master.  If I have a master and two standbys configured for
>> sync rep with quorum commit such that I only need an ack from one of
>> them, and they both go down, we still have to decide what impact that
>> has on the master.
>
> That's already been discussed, and AFAIK Masao and I already agreed on
> how that would be handled in the quorum commit case.

I can't find that in the thread.  Anyway, again, you're going to
probably want the same options there that you will in the "master with
one standby" case, which I personally think is going to be a LOT more
common than any other configuration.

> [configuration example]
> The Oracle way doesn't allow you to specify that if near1 and near2 are
> down then we should continue to SYNC via remote, nor does it allow you
> to specify things from user perspective or at transaction level.
>
> You don't need to do it that way, for sure. But we do need to say what
> way you would pick, rather than just arguing against me before you've
> even discussed it here or off-list.

Well, again, I am not arguing and have not argued that we shouldn't do
it, just that we shouldn't do it UNTIL we get the basic stuff working.On the substance of the design, the Oracle way
doesn'tlook that bad 
in terms of syntax (I suspect we'll end up with some of the same
knobs), but certainly I agree that it would be nice to do some of the
things they can't which you have detailed here.  I just don't want us
to bite off more than we can chew.  Then we might end up with nothing,
which would suck.

>> I agree we need to talk about, but I don't agree
>> that putting in quorum commit will remove the need to design that
>> case.
>
> Yes, you need to design for that case. It's not a magic wand.
>
> All I've said is that covering the common cases is easier and more
> flexible by choosing transaction-centric style of parameters, and it
> also allows user settable behaviour.

One of the ideas you proposed upthread, in terms of
transaction-centric behavior, is having an individual transaction be
able to ask for a weaker integrity guarantee than whatever the default
is.  I think that is both a very good idea and probably something we
should implement relatively early on - though still maybe not in the
first patch.  I think there are a lot of people who will have SOME
transactions (user transfers money to other user) that absolutely have
to be durable and other transactions (user logs in) that we can risk
losing in the event of a crash.

> I want to do better than Oracle, if possible, using lessons learned. I
> don't want to do the same thing because we're copying them or because
> we're going down the same conceptual dead end they went down. We should
> try to avoid doing something obvious and aim a little higher.

+1.

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 14:30 -0400, Robert Haas wrote:
> On Wed, May 26, 2010 at 1:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Wed, 2010-05-26 at 11:31 -0400, Robert Haas wrote:
> >> > Your reply has again avoided the subject of how we would handle failure
> >> > modes with per-standby settings. That is important.
> >>
> >> I don't think anyone is avoiding that, we just haven't discussed it.
> >
> > You haven't discussed it, but even before you do, you know its better.
> > Not very compelling perspective...
> 
> I don't really understand this comment.  I have said, and I believe,
> that a system without quorum commit is simpler than one with quorum
> commit.  I'd debate the point with you but I find the point so
> self-evident that I don't even know where to begin arguing it.  

> It's simply an opinion, which I believe to
> be based on solid technical reasoning, but which I might change my
> mind about if someone convinces me that I'm looking at the problem the
> wrong way.

You're saying you have solid technical reasons, but they are so
self-evident that you can't even begin to argue them. Why are you so
sure your reasons are solid?? Regrettably, I say this doesn't make any
sense, however much you write.

The decision may already have been made in your eyes, but the community
still has options as to how to proceed, whether or not Masao has already
written this.

Zoltan has already presented a patch that follows my proposal, so there
are alternate valid paths which we can decide between. It's not a matter
of opinion as to which is easier to code cos its already done; you can
run the patch and see. (No comment on other parts of that patch).

The alternative is an approach that hasn't even been presented itself
fully on list, with many unanswered questions. I've thought about this
myself and discussed my reasons on list for the past two years. If you
can read all I've presented to the community and come up with a better
way, great, we'll all be happy. 

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, May 26, 2010 at 3:13 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> I don't really understand this comment.  I have said, and I believe,
>> that a system without quorum commit is simpler than one with quorum
>> commit.  I'd debate the point with you but I find the point so
>> self-evident that I don't even know where to begin arguing it.
>
>> It's simply an opinion, which I believe to
>> be based on solid technical reasoning, but which I might change my
>> mind about if someone convinces me that I'm looking at the problem the
>> wrong way.
>
> You're saying you have solid technical reasons, but they are so
> self-evident that you can't even begin to argue them. Why are you so
> sure your reasons are solid?? Regrettably, I say this doesn't make any
> sense, however much you write.

Yeah, especially when you juxtapose two different parts of my email
that were talking about different things.

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


Re: Synchronization levels in SR

From
"Joshua D. Drake"
Date:
On Wed, 2010-05-26 at 15:37 -0400, Robert Haas wrote:
> On Wed, May 26, 2010 at 3:13 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >> I don't really understand this comment.  I have said, and I believe,
> >> that a system without quorum commit is simpler than one with quorum
> >> commit.  I'd debate the point with you but I find the point so
> >> self-evident that I don't even know where to begin arguing it.
> >
> >> It's simply an opinion, which I believe to
> >> be based on solid technical reasoning, but which I might change my
> >> mind about if someone convinces me that I'm looking at the problem the
> >> wrong way.
> >
> > You're saying you have solid technical reasons, but they are so
> > self-evident that you can't even begin to argue them. Why are you so
> > sure your reasons are solid?? Regrettably, I say this doesn't make any
> > sense, however much you write.
>
> Yeah, especially when you juxtapose two different parts of my email
> that were talking about different things.

/me rings bell and orders the fighters to their respective corners.

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

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Re: Synchronization levels in SR

From
Dimitri Fontaine
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Wed, 2010-05-26 at 19:55 +0300, Heikki Linnakangas wrote:
>> Now you want to set up a temporary replica of the master at a 
>> development server, for testing purposes. If you set quorum to 2, your
>> development server becomes critical infrastructure, which is not what 
>> you want.
>
> That's a good argument for standby relays. 

Well it seems to me we can have the best of both worlds as soon as we
have cascading support. Even in the test server example, this one would
be a slave of the main slave, not counted into the quorum on the master.

Now that's the quorum on the slave that would be deciding on the
availability of the test server. Set it down to 0 and your test server
has no impact on the production environment.

In the example of one master and 4 slaves in 2 different locations,
you'll have a quorum of 2 on the master, which will know about 2 slaves
only. And each of them will have 1 slave, with a quorum to set to 0 or 1
depending on what you want to achieve.

So if you want simplicity to admin, effective data availability and
precise control over the global setup, I say go for:a. transaction level control of the replication levelb. cascading
supportc.quorum with timeoutd. choice of commit or rollback at timeout
 

Then give me a setup example that you can't express fully.

As far as the options to control the whole thing are concerned, I think
that the cascading support does not add any. So that's 3 GUCs.

Regards,
-- 
dim


Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 26/05/10 23:31, Dimitri Fontaine wrote:
>   d. choice of commit or rollback at timeout

Rollback is not an option. There is no going back after the commit 
record has been flushed to disk or sent to a standby.

The choice is to either commit anyway after the timeout, or wait forever.

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


Re: Synchronization levels in SR

From
Jan Wieck
Date:
On 5/26/2010 12:55 PM, Heikki Linnakangas wrote:
> On 26/05/10 18:31, Robert Haas wrote:
>> And frankly, I don't think it's possible for quorum commit to reduce
>> the number of parameters.  Even if we have that feature available, not
>> everyone will want to use it.  And the people who don't will
>> presumably need whatever parameters they would have needed if quorum
>> commit hadn't been available in the first place.
> 
> Agreed, quorum commit is not a panacea.
> 
> For example, suppose that you have two servers, master and a standby, 
> and you want transactions to be synchronously committed to both, so that 
> in the event of a meteor striking the master, you don't lose any 
> transactions that have been replied to the client as committed.
> 
> Now you want to set up a temporary replica of the master at a 
> development server, for testing purposes. If you set quorum to 2, your 
> development server becomes critical infrastructure, which is not what 
> you want. If you set quorum to 1, it also becomes critical 
> infrastructure, because it's possible that a transaction has been 
> replicated to the test server but not the real production standby, and a 
> meteor strikes.
> 
> Per-standby settings would let you express that, but not OTOH the quorum 
> behavior where you require N out of M to acknowledge the commit before 
> returning to client.


You can do this only with per standby options, by giving each standby a 
weight, or a number of votes. Your DEV server would have a weight of 
zero, while your production standby's have higher weights, depending on 
their importance for your overall infrastructure. As long as majority 
means >50% of all votes in the house, you don't have a split brain risk.


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin


Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 26/05/10 23:31, Dimitri Fontaine wrote:
> So if you want simplicity to admin, effective data availability and
> precise control over the global setup, I say go for:
>   a. transaction level control of the replication level
>   b. cascading support
>   c. quorum with timeout
>   d. choice of commit or rollback at timeout
>
> Then give me a setup example that you can't express fully.

One master, one synchronous standby on another continent for HA 
purposes, and one asynchronous reporting server in the same rack as the 
master. You don't want to set up the reporting server as a cascaded 
slave of the standby on the other continent, because that would double 
the bandwidth required, but you also don't want the master to wait for 
the reporting server.

The possibilities are endless... Your proposal above covers a pretty 
good set of scenarios, but it's by no means complete. If we try to solve 
everything the configuration will need to be written in a 
Turing-complete Replication Description Language. We'll have to pick a 
useful, easy-to-understand subset that covers the common scenarios. To 
handle the more exotic scenarios, you can write a proxy that sits in 
front of the master, and implements whatever rules you wish, with the 
rules written in C.

BTW, I think we're going to need a separate config file for listing the 
standbys anyway. There you can write per-server rules and options, but 
explicitly knowing about all the standbys also allows the master to 
recycle WAL as soon as it has been streamed to all the registered 
standbys. Currently we just keep wal_keep_segments files around, just in 
case there's a standby out there that needs them.

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-05-26 at 17:31 -0400, Jan Wieck wrote:

> You can do this only with per standby options, by giving each standby a 
> weight, or a number of votes. Your DEV server would have a weight of 
> zero, while your production standby's have higher weights, depending on 
> their importance for your overall infrastructure. As long as majority 
> means >50% of all votes in the house, you don't have a split brain risk.

Yes, you could do that with per-standby options.

If you give each standby a weight then the parameter has much less
meaning for the user. It doesn't mean number of replicas any more, it
means something else with local and changeable meaning. A fractional
quorum suffers the same way.

What would make some sense would be to have an option for "vote=0|1" so
that a standby would never take part in the transaction sync when
vote=0.

But you still have the problem of specifying rules if insufficient
servers with vote=1 are available. The reaction to that is to supply
more servers with vote=1, though then you need a way to specify how many
servers with vote=1 you care about.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 00:21 +0300, Heikki Linnakangas wrote:
> On 26/05/10 23:31, Dimitri Fontaine wrote:
> >   d. choice of commit or rollback at timeout
> 
> Rollback is not an option. There is no going back after the commit 
> record has been flushed to disk or sent to a standby.

There's definitely no going back after the xid has been removed from
procarray because other transactions will then depend upon the final
state. Currently we PANIC if we abort after we've marked clog, though
that happens after XLogFlush(), which is where we're planning to wait
for synch rep. If we abort after having written a commit record to disk
we can still successfully generate an abort record as well. (Luckily, I
note HS does actually cope with that. Phew!) 

So actually, an abort is a reasonable possibility, though I know it
doesn't sound like it could be at first thought.

> The choice is to either commit anyway after the timeout, or wait forever.

Hmm, wait forever. What happens if we try to shutdown fast while there
is a transaction that is waiting forever? Is that then a commit, even
though it never made it to the standby? How would we know it was safe to
switchover or not? Hmm.

Oracle offers options of COMMIT | SHUTDOWN in this case.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 27/05/10 01:23, Simon Riggs wrote:
> On Thu, 2010-05-27 at 00:21 +0300, Heikki Linnakangas wrote:
>> On 26/05/10 23:31, Dimitri Fontaine wrote:
>>>    d. choice of commit or rollback at timeout
>>
>> Rollback is not an option. There is no going back after the commit
>> record has been flushed to disk or sent to a standby.
>
> There's definitely no going back after the xid has been removed from
> procarray because other transactions will then depend upon the final
> state. Currently we PANIC if we abort after we've marked clog, though
> that happens after XLogFlush(), which is where we're planning to wait
> for synch rep. If we abort after having written a commit record to disk
> we can still successfully generate an abort record as well. (Luckily, I
> note HS does actually cope with that. Phew!)
>
> So actually, an abort is a reasonable possibility, though I know it
> doesn't sound like it could be at first thought.

Hmm, that's an interesting thought. Interesting, as in crazy ;-).

I don't understand how HS could handle that. As soon as it sees the 
commit record, the transaction becomes visible to readers.

>> The choice is to either commit anyway after the timeout, or wait forever.
>
> Hmm, wait forever. What happens if we try to shutdown fast while there
> is a transaction that is waiting forever? Is that then a commit, even
> though it never made it to the standby? How would we know it was safe to
> switchover or not? Hmm.

Refuse to shut down until the standby acknowledges the commit. That's 
the only way to be sure..

In practice, hard synchronous "don't return ever until the commit hits 
the standby" behavior is rarely what admins actually want, because it's 
disastrous from an availability point of view. More likely, admins want 
"wait for ack from standby, unless it's not responding, in which case to 
hell with redundancy and just act like a single server". It makes sense 
if you just want to make sure that the standby doesn't return stale 
results when it's working properly, and you're not worried about 
durability but I'm not sure it's very sound otherwise.

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, May 26, 2010 at 10:20 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:
>
>> I guess that dropping the support of #3 doesn't reduce complexity
>> since the code of #3 is almost the same as that of #2. Like
>> walreceiver sends the ACK after receiving the WAL in #2 case, it has
>> only to do the same thing after the WAL flush.
>
> Hmm, well the code for #3 is similar also to the code for #4. So if you
> do #2, its easy to do #2, #3 and #4 together.

No. #4 requires the way of prompt communication between walreceiver and
startup process, but #2 and #3 not. That is, in #4, walreceiver has to
wake the startup process up as soon as it has flushed WAL. OTOH, the
startup process has to wake walreceiver up as soon as it has replayed
WAL, to request it to send the ACK to the master. In #2 and #3, the
prompt communication from walreceiver to startup process, i.e., changing
the poll loop in the startup process would also be useful for the data
to be visible immediately on the standby. But it's not required.

> The comment is about whether having #3 makes sense from a user interface
> perspective. It's easy to add options, but they must have useful
> meaning.

#3 would be useful for people wanting further robustness. In #2,
when simultaneous power failure on the master and the standby,
and concurrent disk crash on the master happen, transaction whose
"success" indicator has been returned to a client might be lost.
#3 can avoid such a critical situation. This is one of reasons that
DRBD supports "Protocol C", I think.

Regards,

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


Re: Synchronization levels in SR

From
"Joshua D. Drake"
Date:
On Wed, 2010-05-26 at 15:37 -0400, Robert Haas wrote:
> On Wed, May 26, 2010 at 3:13 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >> I don't really understand this comment.  I have said, and I believe,
> >> that a system without quorum commit is simpler than one with quorum
> >> commit.  I'd debate the point with you but I find the point so
> >> self-evident that I don't even know where to begin arguing it.
> >
> >> It's simply an opinion, which I believe to
> >> be based on solid technical reasoning, but which I might change my
> >> mind about if someone convinces me that I'm looking at the problem the
> >> wrong way.
> >
> > You're saying you have solid technical reasons, but they are so
> > self-evident that you can't even begin to argue them. Why are you so
> > sure your reasons are solid?? Regrettably, I say this doesn't make any
> > sense, however much you write.
> 
> Yeah, especially when you juxtapose two different parts of my email
> that were talking about different things.

/me rings bell and orders the fighters to their respective corners.

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

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering




Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 11:28 +0900, Fujii Masao wrote:
> On Wed, May 26, 2010 at 10:20 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:
> >
> >> I guess that dropping the support of #3 doesn't reduce complexity
> >> since the code of #3 is almost the same as that of #2. Like
> >> walreceiver sends the ACK after receiving the WAL in #2 case, it has
> >> only to do the same thing after the WAL flush.
> >
> > Hmm, well the code for #3 is similar also to the code for #4. So if you
> > do #2, its easy to do #2, #3 and #4 together.
> 
> No. #4 requires the way of prompt communication between walreceiver and
> startup process, but #2 and #3 not. That is, in #4, walreceiver has to
> wake the startup process up as soon as it has flushed WAL. OTOH, the
> startup process has to wake walreceiver up as soon as it has replayed
> WAL, to request it to send the ACK to the master. In #2 and #3, the
> prompt communication from walreceiver to startup process, i.e., changing
> the poll loop in the startup process would also be useful for the data
> to be visible immediately on the standby. But it's not required.

You need to pass WAL promptly on primary from backend to WALSender.
Whatever mechanism you use can also be reused symmetrically on standby
to provide #4. So not a problem.

> > The comment is about whether having #3 makes sense from a user interface
> > perspective. It's easy to add options, but they must have useful
> > meaning.
> 
> #3 would be useful for people wanting further robustness. In #2,
> when simultaneous power failure on the master and the standby,
> and concurrent disk crash on the master happen, transaction whose
> "success" indicator has been returned to a client might be lost.
> #3 can avoid such a critical situation. This is one of reasons that
> DRBD supports "Protocol C", I think.

Which few people use it, or if they do its because DRBD didn't
originally support multiple standbys. Not worth emulating IMHO.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 02:18 +0300, Heikki Linnakangas wrote:
> On 27/05/10 01:23, Simon Riggs wrote:
> > On Thu, 2010-05-27 at 00:21 +0300, Heikki Linnakangas wrote:
> >> On 26/05/10 23:31, Dimitri Fontaine wrote:
> >>>    d. choice of commit or rollback at timeout
> >>
> >> Rollback is not an option. There is no going back after the commit
> >> record has been flushed to disk or sent to a standby.
> >
> > There's definitely no going back after the xid has been removed from
> > procarray because other transactions will then depend upon the final
> > state. Currently we PANIC if we abort after we've marked clog, though
> > that happens after XLogFlush(), which is where we're planning to wait
> > for synch rep. If we abort after having written a commit record to disk
> > we can still successfully generate an abort record as well. (Luckily, I
> > note HS does actually cope with that. Phew!)
> >
> > So actually, an abort is a reasonable possibility, though I know it
> > doesn't sound like it could be at first thought.

> Hmm, that's an interesting thought. Interesting, as in crazy ;-).

:-) It's a surprising thought for me also.

> I don't understand how HS could handle that. As soon as it sees the 
> commit record, the transaction becomes visible to readers.

I meant not-barf completely.

> >> The choice is to either commit anyway after the timeout, or wait forever.
> >
> > Hmm, wait forever. What happens if we try to shutdown fast while there
> > is a transaction that is waiting forever? Is that then a commit, even
> > though it never made it to the standby? How would we know it was safe to
> > switchover or not? Hmm.
> 
> Refuse to shut down until the standby acknowledges the commit. That's 
> the only way to be sure..
> 
> In practice, hard synchronous "don't return ever until the commit hits 
> the standby" behavior is rarely what admins actually want, because it's 
> disastrous from an availability point of view. More likely, admins want 
> "wait for ack from standby, unless it's not responding, in which case to 
> hell with redundancy and just act like a single server". It makes sense 
> if you just want to make sure that the standby doesn't return stale 
> results when it's working properly, and you're not worried about 
> durability but I'm not sure it's very sound otherwise.

Which is also crazy. If you're using synch rep its because you care
deeply about durability. Some people wish to treat the COMMIT as a
guarantee, not just a shrug.

I agree that don't-return-ever isn't something anyone will want.

What we need is a "COMMIT with ERROR" message!

Note that Oracle gives the options of COMMIT | SHUTDOWN at this point.
Shutdown is an implicit abort for the writing transaction...

At this point the primary thinks standby is no longer available. If we
have a split brain situation then we should be assuming we will STONITH
and shutdown the primary anyway. If we have more than one standby we can
stay up and probably shouldn't be sending an abort after a commit.

The trouble is *every* option is crazy from some perspective, so we must
consider them all, to see whether they are practical or impractical.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 27/05/10 09:51, Simon Riggs wrote:
> On Thu, 2010-05-27 at 02:18 +0300, Heikki Linnakangas wrote:
>> In practice, hard synchronous "don't return ever until the commit hits
>> the standby" behavior is rarely what admins actually want, because it's
>> disastrous from an availability point of view. More likely, admins want
>> "wait for ack from standby, unless it's not responding, in which case to
>> hell with redundancy and just act like a single server". It makes sense
>> if you just want to make sure that the standby doesn't return stale
>> results when it's working properly, and you're not worried about
>> durability but I'm not sure it's very sound otherwise.
>
> Which is also crazy. If you're using synch rep its because you care
> deeply about durability.

No, not necessarily. As I said above, you might just want a guarantee 
that *if* you query the standby, you get up-to-date results. But if the 
standby is down for any reason, you don't care about it. That's a very 
sensible mode of operation, for example if you're offloading reads to 
the standby with something like pgpool.

In fact I have the feeling that that's the most common use case for 
synchronous replication, not a deep concern of durability.

> I agree that don't-return-ever isn't something anyone will want.
>
> What we need is a "COMMIT with ERROR" message!

Hmm, perhaps we could emit a warning with the commit. I'm not sure what 
an application could do with it, though.

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, May 26, 2010 at 10:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> If the remote server responded first, then that proves it is a better
> candidate for failover than the one you think of as near. If the two
> standbys vary over time then you have network problems that will
> directly affect the performance on the master; synch_rep = N would
> respond better to any such problems.

No. The remote standby might respond first temporarily though it's almost
behind the near one. The read-only queries or incrementally updated
backup operation might cause a bursty disk write, and delay the ACK from
the standby. The lock contention between read-only queries and recovery
would delay the ACK. So the standby which responds first is not always
the best candidate for failover. Also the administrator generally doesn't
put the remote standby under the control of a clusterware like heartbeat.
In this case, the remote standby will never be the candidate for failover.
But quorum commit cannot cover this simple case.

>> OTOH, "synchronous_replication=2" degrades the
>> performance on the master very much.
>
> Yes, but only because you have only one near standby. It would clearly
> to be foolish to make this setting without 2+ near standbys. We would
> then have 4 or more servers; how do we specify everything for that
> config??

If you always want to use the near standby as the candidate for failover
by using quorum commit in the above simple case, you would need to choose
such a foolish setting. Otherwise, unfortunately you might have to failover
to the remote standby not under the control of a clusterware.

>> "synchronous_replication" approach
>> doesn't seem to cover the typical use case.
>
> You described the failure modes for the quorum proposal, but avoided
> describing the failure modes for the "per-standby" proposal.
>
> Please explain what will happen when the near server is unavailable,
> with per-standby settings. Please also explain what will happen if we
> choose to have 4 or 5 servers to maintain performance in case of the
> near server going down. How will we specify the failure modes?

I'll try to explain that.

(1) most standard case: 1 master + 1 "sync" standby (near)   When the master goes down, something like a clusterware
detectsthat   failure, and brings the standby online. Since we can ensure that the   standby has all the committed
transactions,failover doesn't cause   any data loss.
 
   When the standby goes down or network outage happens, walsender   detects that failure via the replication timeout,
keepaliveor error   return from the system calls. Then walsender does something according   to the specified reaction
(GUC)to the failure of the standby, e.g.,   walsender wakes the transaction commit up from the wait-for-ACK, and
exits.Then the master runs standalone.
 

(2) 1 master + 1 "sync" standby (near) + 1 "async" standby (remote)   When the master goes down, something like a
clusterwarebrings the   "sync" standby in the near location online. The administrator would   need to take a fresh base
backupof the new master, load it on the   remote standby, change the primary_conninfo, and restart the remote
standby.
   When one of standbys goes down, walsender does the same thing described   in (1). Until the failed standby has
restarted,the master runs together   with another standby.
 

In (1) and (2), after some failure happens, there would be only one server
which is guaranteed to have all the committed transactions. When it also
goes down, the database service stops. If you want to avoid this fragile
situation, you would need to add one more "sync" standby in the near site.

(3) 1 master + 2 "sync" standbys (near) + 1 "async" standby (remote)   When the master goes down, something like a
clusterwarebrings the   one of "sync" standbys online by using some selection algorithm.   The administrator would need
totake a fresh base backup of the new   master, load it on both remaining standbys, change the primary_conninfo,   and
restartthem.
 
   When one of standbys goes down, walsender does the same thing described   in (1). Until the failed standby has
restarted,the master runs together   with two standbys. At least one standby is guaranteed to be sync with   the
master.

Is this explanation enough?

>> Also, when "synchronous_replication=1" and one of synchronous standbys
>> goes down, how should the surviving standby catch up with the master?
>> Such standby might be too far behind the master. The transaction commit
>> should wait for the ACK from the lagging standby immediately even if
>> there might be large gap? If yes, "synch_rep_timeout" would screw up
>> the replication easily.
>
> That depends upon whether we send the ACK at point #2, #3 or #4. It
> would only cause a problem if you waited until #4.

Yeah, the problem happens. If we implement quorum commit, we need to
design how the surviving standby catches up with the master.

Regards,

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 3:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Thu, 2010-05-27 at 11:28 +0900, Fujii Masao wrote:
>> On Wed, May 26, 2010 at 10:20 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:
>> >
>> >> I guess that dropping the support of #3 doesn't reduce complexity
>> >> since the code of #3 is almost the same as that of #2. Like
>> >> walreceiver sends the ACK after receiving the WAL in #2 case, it has
>> >> only to do the same thing after the WAL flush.
>> >
>> > Hmm, well the code for #3 is similar also to the code for #4. So if you
>> > do #2, its easy to do #2, #3 and #4 together.
>>
>> No. #4 requires the way of prompt communication between walreceiver and
>> startup process, but #2 and #3 not. That is, in #4, walreceiver has to
>> wake the startup process up as soon as it has flushed WAL. OTOH, the
>> startup process has to wake walreceiver up as soon as it has replayed
>> WAL, to request it to send the ACK to the master. In #2 and #3, the
>> prompt communication from walreceiver to startup process, i.e., changing
>> the poll loop in the startup process would also be useful for the data
>> to be visible immediately on the standby. But it's not required.
>
> You need to pass WAL promptly on primary from backend to WALSender.
> Whatever mechanism you use can also be reused symmetrically on standby
> to provide #4. So not a problem.

I cannot be so optimistic since the situation differs from one process
to another.

>> > The comment is about whether having #3 makes sense from a user interface
>> > perspective. It's easy to add options, but they must have useful
>> > meaning.
>>
>> #3 would be useful for people wanting further robustness. In #2,
>> when simultaneous power failure on the master and the standby,
>> and concurrent disk crash on the master happen, transaction whose
>> "success" indicator has been returned to a client might be lost.
>> #3 can avoid such a critical situation. This is one of reasons that
>> DRBD supports "Protocol C", I think.
>
> Which few people use it, or if they do its because DRBD didn't
> originally support multiple standbys. Not worth emulating IMHO.

If so, #3 would be useful for people who don't afford to buy more
than one standby servers, too :)

Regards,

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


Re: Synchronization levels in SR

From
Dimitri Fontaine
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> On 26/05/10 23:31, Dimitri Fontaine wrote:
>> So if you want simplicity to admin, effective data availability and
>> precise control over the global setup, I say go for:
>>   a. transaction level control of the replication level
>>   b. cascading support
>>   c. quorum with timeout
>>   d. choice of commit or rollback at timeout
>>
>> Then give me a setup example that you can't express fully.
>
> One master, one synchronous standby on another continent for HA purposes,
> and one asynchronous reporting server in the same rack as the master. You
> don't want to set up the reporting server as a cascaded slave of the standby
> on the other continent, because that would double the bandwidth required,
> but you also don't want the master to wait for the reporting server.
>
> The possibilities are endless... Your proposal above covers a pretty good
> set of scenarios, but it's by no means complete. If we try to solve
> everything the configuration will need to be written in a Turing-complete
> Replication Description Language. We'll have to pick a useful,
> easy-to-understand subset that covers the common scenarios. To handle the
> more exotic scenarios, you can write a proxy that sits in front of the
> master, and implements whatever rules you wish, with the rules written
> in C.

Agreed on the Turing-completeness side of those things. My current
thinking is that the proxy I want might simply be a PostgreSQL instance
with cascading support. In your example that would give us:
                     Remote Standby, HA  Master -- Proxy -<                      Local Standby, Reporting

So what I think we have here is a pretty good trade-off in terms of what
you can do with some simple setup knobs. What's left there is that with
the quorum idea, you're not sure if the one server that's synced is the
remote or local standby, in this example. Several ideas are floating
around (votes, mixed per-standby and per-transaction settings).

Maybe we could have the standby be able to say it's not interesting into
participating into the quorum, that is, it's an async replica, full
stop.

In your example we'd set the local reporting standby as a non-voting
member of the replication setting, the proxy and the master would have a
quorum of 1, and the remote HA standby would vote.

I don't think the idea of having any number of voting coupons other than
0 or 1 on any server will help us the least.

I do think that your proxy idea is a great one and should be in core. By
the way, the cascading/proxy instance could be set without Hot Standby,
if you don't like to be able to monitor it via a libpq connection and
some queries.

> BTW, I think we're going to need a separate config file for listing the
> standbys anyway. There you can write per-server rules and options, but
> explicitly knowing about all the standbys also allows the master to recycle
> WAL as soon as it has been streamed to all the registered
> standbys. Currently we just keep wal_keep_segments files around, just in
> case there's a standby out there that needs them.

I much prefer that each server in the set publish what it wants. It only
connects to 1 given provider. Then we've been talking about this exact
same retention problem for queueing solutions, with Jan, Marko and Jim.

The idea we came up with is a watermarking solution (which already
exists in Skytools 3, in its coarse-grain version). The first approach
is to have all slave give back to its local master/provider/origin the
last replayed WAL/LSN, once in a while. You derive from that a global
watermark and drop WAL files depending on it.

You now have two problems: no more space and why keeping that many files
on the master anyway, maybe some slave could be set up for retention
instead?

To solve that it's possible for each server to be setup with a
restricted set of servers they're deriving their watermark from. That's
when you need per-server options and an explicit list of all the
standbys whatever their level in the cascading tree. That means explicit
maintenance of the entire replication topology.

I don't think we need to solve that already. I think we need to provide
an option on each member of the replication tree to either PANIC or lose
WALs in case they're running out of space when trying to follow the
watermark. It's crude but already allows to have a standby set to
maintain the common archive and have the master drop the WAL files as
soon as possible (respecting wal_keep_segments).

In our case, if a WAL file is no more available from any active server
we still have the option to fetch it from the archives...

Regards,
-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 10:09 +0300, Heikki Linnakangas wrote:
> No, not necessarily. As I said above, you might just want a guarantee 
> that *if* you query the standby, you get up-to-date results.

Of course. COMMIT was already one of the options, so this comment was
already understood.

What we are discussing is whether additional options exist and/or are
desirable. We should not be forcing everybody to COMMIT whether or not
it is robust.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 16:35 +0900, Fujii Masao wrote:
> On Thu, May 27, 2010 at 3:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Thu, 2010-05-27 at 11:28 +0900, Fujii Masao wrote:
> >> On Wed, May 26, 2010 at 10:20 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >> > On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:
> >> >
> >> >> I guess that dropping the support of #3 doesn't reduce complexity
> >> >> since the code of #3 is almost the same as that of #2. Like
> >> >> walreceiver sends the ACK after receiving the WAL in #2 case, it has
> >> >> only to do the same thing after the WAL flush.
> >> >
> >> > Hmm, well the code for #3 is similar also to the code for #4. So if you
> >> > do #2, its easy to do #2, #3 and #4 together.
> >>
> >> No. #4 requires the way of prompt communication between walreceiver and
> >> startup process, but #2 and #3 not. That is, in #4, walreceiver has to
> >> wake the startup process up as soon as it has flushed WAL. OTOH, the
> >> startup process has to wake walreceiver up as soon as it has replayed
> >> WAL, to request it to send the ACK to the master. In #2 and #3, the
> >> prompt communication from walreceiver to startup process, i.e., changing
> >> the poll loop in the startup process would also be useful for the data
> >> to be visible immediately on the standby. But it's not required.
> >
> > You need to pass WAL promptly on primary from backend to WALSender.
> > Whatever mechanism you use can also be reused symmetrically on standby
> > to provide #4. So not a problem.
> 
> I cannot be so optimistic since the situation differs from one process
> to another.

This spurs some architectural thinking:

I think we need to disconnect the idea of waiting in any of the
components. Anytime we ask WALSender or WALReceiver to wait for
acknowledgement we will be reducing throughput. So we should assume that
they will continue to work as quickly as possible.

The acknowledgement from standby can contain the latest xlog location of
WAL received, WAL written to disk and WAL applied, all by reading values
from shared memory. It's all the same, whether we send back 2 or 3 xlog
locations in the ack message.

Who sends the ack message? Who receives it? Would it be easier to have
this happen in a second pair of processes WALSynchroniser (on primary)
and WAL Acknowledger (on standby). WALAcknowledger would send back a
stream of ack messages with latest xlog positions. WALSynchroniser would
receive these messages and wake up sleeping backends. If we did that
then there'd be almost no change at all to existing code, just
additional code and processes for the sync case. Code would be separate
and there would be no performance concerns either.

Backends can then choose to wait until the xlog location they wish has
been achieved which might be in the next acknowledgement message or in a
subsequent one. That also ensures that the logic for this is completely
on the master and the standby doesn't act differently, apart from
needing to start a WALAcknowledger process if sync rep is requested.

If you do choose to make #3 important, then I'd say you need to work out
how to make WALWriter active as well, so it can perform regular fsyncs,
rather than having WALReceiver wait across that I/O.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 16:13 +0900, Fujii Masao wrote:
> On Wed, May 26, 2010 at 10:37 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

> > Please explain what will happen when the near server is unavailable,
> > with per-standby settings. Please also explain what will happen if we
> > choose to have 4 or 5 servers to maintain performance in case of the
> > near server going down. How will we specify the failure modes?
> 
> I'll try to explain that.

We've been discussing parameters and how we would define what we want to
happen in various scenarios.

You've not explained what parameters you would use, how and where they
would be set, so we aren't yet any closer to understanding what it is
your proposing.

Please explain how your proposal will work.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

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

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

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

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

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

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

So Oracle already supports the quorum case...

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

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

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 6:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Who sends the ack message?

walreceiver

> Who receives it?

walsender

> Would it be easier to have
> this happen in a second pair of processes WALSynchroniser (on primary)
> and WAL Acknowledger (on standby). WALAcknowledger would send back a
> stream of ack messages with latest xlog positions. WALSynchroniser would
> receive these messages and wake up sleeping backends. If we did that
> then there'd be almost no change at all to existing code, just
> additional code and processes for the sync case. Code would be separate
> and there would be no performance concerns either.

No, this seems to be bad idea. We should not establish extra connection
between servers. That would be a source of trouble.

> If you do choose to make #3 important, then I'd say you need to work out
> how to make WALWriter active as well, so it can perform regular fsyncs,
> rather than having WALReceiver wait across that I/O.

Yeah, this might be an option for optimization though I'm not sure how
it has good effect.

Regards,

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 19:21 +0900, Fujii Masao wrote:
> On Thu, May 27, 2010 at 6:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

> > Would it be easier to have
> > this happen in a second pair of processes WALSynchroniser (on primary)
> > and WAL Acknowledger (on standby). WALAcknowledger would send back a
> > stream of ack messages with latest xlog positions. WALSynchroniser would
> > receive these messages and wake up sleeping backends. If we did that
> > then there'd be almost no change at all to existing code, just
> > additional code and processes for the sync case. Code would be separate
> > and there would be no performance concerns either.
> 
> No, this seems to be bad idea. We should not establish extra connection
> between servers. That would be a source of trouble.

What kind of trouble? You think using an extra connection would cause
problems; why?

I've explained it would greatly simplify the code to do it that way and
improve performance. Those sound like good things, not problems.

> > If you do choose to make #3 important, then I'd say you need to work out
> > how to make WALWriter active as well, so it can perform regular fsyncs,
> > rather than having WALReceiver wait across that I/O.
> 
> Yeah, this might be an option for optimization though I'm not sure how
> it has good effect.

As I said, WALreceiver would not need to wait across fsync...

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 7:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Seems strange. If you have 2 standbys and you say you would like node1
> to be the preferred candidate, then you load it so heavily that a remote
> server with by-definition much larger network delay responds first, then
> I say your preference was wrong. The above situation is caused by the
> DBA and the DBA can solve it also - if the preference is to keep a
> "preferred" server then that server would need to be lightly loaded so
> it could respond sensibly.

No. Even if the load is very low in the "preferred" server, there is
*no* guarantee that it responds first. Per-standby setting can give
such a guarantee, i.e., we can specify #2, #3 or #4 in the "preferred"
server and #1 in the other.

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

I think that choice of wrong master causes more serious situation than
that of wrong plan.

>> Also the administrator generally doesn't
>> put the remote standby under the control of a clusterware like heartbeat.
>> In this case, the remote standby will never be the candidate for failover.
>> But quorum commit cannot cover this simple case.
>
> If you, Jan and Yeb wish to completely exclude standbys from being part
> of any quorum, then I guess we need to have per-standby settings to
> allow that to be defined. I'm in favour of giving people options. That
> needn't be a mandatory per-standby setting, just a non-default option,
> so that we can reduce the complexity of configuration for common cases.
> If we're looking for simplest-implementation-first that isn't it.

For now, I agree that we support a quorum commit feature for 9.1 or later.
But I don't think that it's simpler, more intuitive and easier-to-understand
than per-standby setting. So I think that we should include the per-standby
setting in the first patch.

Regards,

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 7:33 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Thu, 2010-05-27 at 19:21 +0900, Fujii Masao wrote:
>> On Thu, May 27, 2010 at 6:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
>> > Would it be easier to have
>> > this happen in a second pair of processes WALSynchroniser (on primary)
>> > and WAL Acknowledger (on standby). WALAcknowledger would send back a
>> > stream of ack messages with latest xlog positions. WALSynchroniser would
>> > receive these messages and wake up sleeping backends. If we did that
>> > then there'd be almost no change at all to existing code, just
>> > additional code and processes for the sync case. Code would be separate
>> > and there would be no performance concerns either.
>>
>> No, this seems to be bad idea. We should not establish extra connection
>> between servers. That would be a source of trouble.
>
> What kind of trouble? You think using an extra connection would cause
> problems; why?

Because the number of connection failure cases doubles. Likewise, the number
of process failure cases would double.

>> > If you do choose to make #3 important, then I'd say you need to work out
>> > how to make WALWriter active as well, so it can perform regular fsyncs,
>> > rather than having WALReceiver wait across that I/O.
>>
>> Yeah, this might be an option for optimization though I'm not sure how
>> it has good effect.
>
> As I said, WALreceiver would not need to wait across fsync...

Right, but walreceiver still needs to wait for WAL flush by walwriter.
If currently WAL flush is the dominant workload for walreceiver,
only leaving it to walwriter might not have so good effect. I'm not sure
whether.

Regards,

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


Re: Synchronization levels in SR

From
Dimitri Fontaine
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> Seems strange. If you have 2 standbys and you say you would like node1
> to be the preferred candidate, then you load it so heavily that a remote
> server with by-definition much larger network delay responds first, then
> I say your preference was wrong.

There's a communication mismatch here I think. The problem with the
dynamic aspect of the system is that the admin wants to plan ahead and
choose in advance the failover server.

Other than that I much prefer the automatic and dynamic quorum idea.

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

+1

> Maximum Performance => quorum = 0
> Maximum Availability => quorum = 1, timeout_action = commit
> Maximum Protection => quorum = 1, timeout_action = shutdown

+1

Being able to say that a given server has not been granted to
participate into the vote allowing to reach the global durability quorum
will allow for choosing the failover candidates.

Now you're able to have this reporting server and know for sure that
your sync replicated transactions are not waiting for it.

To summarize, the current "per-transaction approach" would be :
- transaction level replication synchronous behaviour- proxy/cascading in core- quorum setup for deciding any commit is
safe-any server can be excluded from the sync quorum- timeout can still raises exception or ignore (commit)? 

This last point seems to need some more discussion, or I didn't
understand well the current positions and proposals.

Regards,
--
dim


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Thu, May 27, 2010 at 3:13 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> (1) most standard case: 1 master + 1 "sync" standby (near)
>    When the master goes down, something like a clusterware detects that
>    failure, and brings the standby online. Since we can ensure that the
>    standby has all the committed transactions, failover doesn't cause
>    any data loss.

How do you propose to guarantee that?  ISTM that you have to either
commit locally first, or send the commit to the remote first.  Either
way, the two events won't occur exactly simultaneously.

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 20:13 +0900, Fujii Masao wrote:
> On Thu, May 27, 2010 at 7:33 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > On Thu, 2010-05-27 at 19:21 +0900, Fujii Masao wrote:
> >> On Thu, May 27, 2010 at 6:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >
> >> > Would it be easier to have
> >> > this happen in a second pair of processes WALSynchroniser (on primary)
> >> > and WAL Acknowledger (on standby). WALAcknowledger would send back a
> >> > stream of ack messages with latest xlog positions. WALSynchroniser would
> >> > receive these messages and wake up sleeping backends. If we did that
> >> > then there'd be almost no change at all to existing code, just
> >> > additional code and processes for the sync case. Code would be separate
> >> > and there would be no performance concerns either.
> >>
> >> No, this seems to be bad idea. We should not establish extra connection
> >> between servers. That would be a source of trouble.
> >
> > What kind of trouble? You think using an extra connection would cause
> > problems; why?
> 
> Because the number of connection failure cases doubles. Likewise, the number
> of process failure cases would double.

Not really. The users wait for just the synchroniser to return not for
two things.  Looks to me that other processes are independent of each
other. Very simple.

> >> > If you do choose to make #3 important, then I'd say you need to work out
> >> > how to make WALWriter active as well, so it can perform regular fsyncs,
> >> > rather than having WALReceiver wait across that I/O.
> >>
> >> Yeah, this might be an option for optimization though I'm not sure how
> >> it has good effect.
> >
> > As I said, WALreceiver would not need to wait across fsync...
> 
> Right, but walreceiver still needs to wait for WAL flush by walwriter.

Why does it? I just explained a design where that wasn't required.

> If currently WAL flush is the dominant workload for walreceiver,
> only leaving it to walwriter might not have so good effect. I'm not sure
> whether.

If we're not sure, we could check before agreeing a design.

WAL flush will be costly unless you have huge disk cache.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Thu, 2010-05-27 at 19:50 +0900, Fujii Masao wrote:

> For now, I agree that we support a quorum commit feature for 9.1 or later.
> But I don't think that it's simpler, more intuitive and easier-to-understand
> than per-standby setting. So I think that we should include the per-standby
> setting in the first patch.

There already is a first patch to the community that implements quorum
commit, just not by you.

If you have a better way, describe it in detail and in full now, with
reference to each of the use cases you mentioned, so that people get a
chance to give their opinions on your design. Then we can let the
community decide whether or not that second way is actually better. We
may not need a second patch.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 8:28 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, May 27, 2010 at 3:13 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> (1) most standard case: 1 master + 1 "sync" standby (near)
>>    When the master goes down, something like a clusterware detects that
>>    failure, and brings the standby online. Since we can ensure that the
>>    standby has all the committed transactions, failover doesn't cause
>>    any data loss.
>
> How do you propose to guarantee that?  ISTM that you have to either
> commit locally first, or send the commit to the remote first.  Either
> way, the two events won't occur exactly simultaneously.

Letting the transaction wait until the standby has received / flushed /
replayed the WAL before it returns a "success" indicator to a client
would guarantee that. This ensures that all transactions which a client
knows as committed exist in the memory or disk of the standby. So we
would be able to see those transactions from new master after failover.

Regards,

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 8:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Why does it? I just explained a design where that wasn't required.

Hmm.. my expression might have been ambiguous. Walreceiver still needs
to wait for WAL flush by walwriter *before* sending the ACK to the master,
in #3 case. Because, in #3, the master has to wait until the standby has
flushed the WAL.

Regards,

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 8:30 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> There already is a first patch to the community that implements quorum
> commit, just not by you.

Yeah, AFAIK, that patch includes also per-standby setting.

> If you have a better way, describe it in detail and in full now, with
> reference to each of the use cases you mentioned, so that people get a
> chance to give their opinions on your design. Then we can let the
> community decide whether or not that second way is actually better. We
> may not need a second patch.

See http://archives.postgresql.org/pgsql-hackers/2010-05/msg01407.php

But I think that we should focus on "per-standby" setting at first.

Regards,

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


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Thu, May 27, 2010 at 8:02 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Thu, May 27, 2010 at 8:28 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Thu, May 27, 2010 at 3:13 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>> (1) most standard case: 1 master + 1 "sync" standby (near)
>>>    When the master goes down, something like a clusterware detects that
>>>    failure, and brings the standby online. Since we can ensure that the
>>>    standby has all the committed transactions, failover doesn't cause
>>>    any data loss.
>>
>> How do you propose to guarantee that?  ISTM that you have to either
>> commit locally first, or send the commit to the remote first.  Either
>> way, the two events won't occur exactly simultaneously.
>
> Letting the transaction wait until the standby has received / flushed /
> replayed the WAL before it returns a "success" indicator to a client
> would guarantee that. This ensures that all transactions which a client
> knows as committed exist in the memory or disk of the standby. So we
> would be able to see those transactions from new master after failover.

There could still be additional transactions that the original master
has committed locally but were not acked to the client.  I guess you'd
just work around that by taking a new base backup from the new master.

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Thu, May 27, 2010 at 9:48 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> There could still be additional transactions that the original master
> has committed locally but were not acked to the client.  I guess you'd
> just work around that by taking a new base backup from the new master.

Right.

Unfortunately the transaction aborted for a client might have already
been committed in the standby. In this case, we might need to eliminate
the mismatch of transaction status between a client and new master
after failover.

BTW, the similar situation might happen even when only one server is
running. If the server goes down before returning a "success" to a
client after flushing the commit record, the mismatch would happen
after restart of the server.

Regards,

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


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Thu, May 27, 2010 at 9:09 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Thu, May 27, 2010 at 9:48 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> There could still be additional transactions that the original master
>> has committed locally but were not acked to the client.  I guess you'd
>> just work around that by taking a new base backup from the new master.
>
> Right.
>
> Unfortunately the transaction aborted for a client might have already
> been committed in the standby. In this case, we might need to eliminate
> the mismatch of transaction status between a client and new master
> after failover.
>
> BTW, the similar situation might happen even when only one server is
> running. If the server goes down before returning a "success" to a
> client after flushing the commit record, the mismatch would happen
> after restart of the server.

True.  But that's a slightly different case.  Clients could fail to
receive commit ACKs for a variety of reasons, like losing network
connectivity momentarily.  They had better be prepared for that no
matter whether replication is in use or not.  The new issue that
replication adds is that you've got to make sure that the two (or n)
nodes don't disagree with each other.

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


Re: Synchronization levels in SR

From
Bruce Momjian
Date:
Simon Riggs wrote:
> On Wed, 2010-05-26 at 18:52 +0900, Fujii Masao wrote:
> 
> > I guess that dropping the support of #3 doesn't reduce complexity
> > since the code of #3 is almost the same as that of #2. Like
> > walreceiver sends the ACK after receiving the WAL in #2 case, it has
> > only to do the same thing after the WAL flush.
> 
> Hmm, well the code for #3 is similar also to the code for #4. So if you
> do #2, its easy to do #2, #3 and #4 together.
> 
> The comment is about whether having #3 makes sense from a user interface
> perspective. It's easy to add options, but they must have useful
> meaning.

If the slave is runing read-only queries, #3 is the most reliable option
withouth delaying the slave, so there is a usecase for #3.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com


Re: Synchronization levels in SR

From
Bruce Momjian
Date:
Heikki Linnakangas wrote:
> BTW, I think we're going to need a separate config file for listing the 
> standbys anyway. There you can write per-server rules and options, but 
> explicitly knowing about all the standbys also allows the master to 
> recycle WAL as soon as it has been streamed to all the registered 
> standbys. Currently we just keep wal_keep_segments files around, just in 
> case there's a standby out there that needs them.

Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode'
on the master, and allow the sync/async mode to be set on each slave,
e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then
two slaves with sync mode of #2 or stricter have to complete before the
master can continue.

Naming the slaves on the master seems very confusing because I am
unclear how we would identify named slaves, and the names have to match,
etc.  

Also, what would be cool would be if you could run a query on the master
to view the SR commit mode of each slave.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com


Re: Synchronization levels in SR

From
Robert Haas
Date:
On May 27, 2010, at 4:31 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Heikki Linnakangas wrote:
>> BTW, I think we're going to need a separate config file for listing  
>> the
>> standbys anyway. There you can write per-server rules and options,  
>> but
>> explicitly knowing about all the standbys also allows the master to
>> recycle WAL as soon as it has been streamed to all the registered
>> standbys. Currently we just keep wal_keep_segments files around,  
>> just in
>> case there's a standby out there that needs them.
>
> Ideally we could set 'slave_sync_count' and  
> 'slave_commit_continue_mode'
> on the master, and allow the sync/async mode to be set on each slave,
> e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then
> two slaves with sync mode of #2 or stricter have to complete before  
> the
> master can continue.
>
> Naming the slaves on the master seems very confusing because I am
> unclear how we would identify named slaves, and the names have to  
> match,
> etc.

The names could be configured with a GUC on the slaves, or we could  
base it on the login role.

...Robert


Re: Synchronization levels in SR

From
Greg Smith
Date:
Heikki Linnakangas wrote:
> The possibilities are endless... Your proposal above covers a pretty 
> good set of scenarios, but it's by no means complete. If we try to 
> solve everything the configuration will need to be written in a 
> Turing-complete Replication Description Language. We'll have to pick a 
> useful, easy-to-understand subset that covers the common scenarios. To 
> handle the more exotic scenarios, you can write a proxy that sits in 
> front of the master, and implements whatever rules you wish, with the 
> rules written in C.

I was thinking about this a bit recently.  As I see it, there are three 
fundamental parts of this:

1) We have a transaction that is being committed.  The rest of the 
computations here are all relative to it.

2) There is an (internal?) table that lists the state of each 
replication target relative to that transaction.  It would include the 
node name, perhaps some metadata ('location' seems the one that's most 
likely to help with the remote data center issue), and a state code.  
The codes from http://wiki.postgresql.org/wiki/Streaming_Replication 
work fine for the last part (which is the only dynamic one--everything 
else is static data being joined against):

async=hasn't received yet
recv=been received but just in RAM
fsync=received and synced to disk
apply=applied to the database

These would need to be enums so they can be ordered from lesser to 
greater consistency.

So in a 3 node case, the internal state table might look like this after 
a bit of data had been committed:

node | location | state
----------------------------------
a | local    | fsync 
b | remote | recv
c | remote | async

This means that the local node has a fully persistent copy, but the best 
either remote one has done is received the data, it's not on disk at all 
yet at the remote data center.  Still working its way through.

3) The decision about whether the data has been committed to enough 
places to be considered safe by the master is computed by a function 
that is passed this internal table as something like a SRF, and it 
returns a boolean.  Once that returns true, saying it's satisfied, the 
transaction closes on the master and continues to percolate out from 
there.  If it's false, we wait for another state change to come in and 
return to (2).

I would propose that most behaviors someone has expressed as being their 
desired implementation is possible to implement using this scheme. 

-Semi-sync commit:  proceed as soon somebody else has a copy and hope 
the copies all become consistent:  EXISTS WHERE state>=recv
-Don't proceed until there's a fsync'd commit on at least one of the 
remote nodes:  EXISTS WHERE location='remote' AND state>=fsync
-Look for a quorum of n commits of fsync quality:  CASE WHEN (SELECT 
COUNT(*) WHERE state>=fsync)>n THEN true else FALSE end;

Syntax is obviously rough but I think you can get the drift of what I'm 
suggesting.

While exposing the local state and running this computation isn't free, 
in situations where there truly are remote nodes in here being 
communicated with the network overhead is going to dwarf that.  If there 
were a fast path for the simplest cases and this complicated one for the 
rest, I think you could get the fully programmable behavior some people 
want using simple SQL, rather than having to write a new "Replication 
Description Language" or something so ambitious.  This data about what's 
been replicated to where looks an awful lot like a set of rows you can 
operate on using features already in the database to me.

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



Re: Synchronization levels in SR

From
Heikki Linnakangas
Date:
On 02/06/10 10:22, Greg Smith wrote:
> Heikki Linnakangas wrote:
>> The possibilities are endless... Your proposal above covers a pretty
>> good set of scenarios, but it's by no means complete. If we try to
>> solve everything the configuration will need to be written in a
>> Turing-complete Replication Description Language. We'll have to pick a
>> useful, easy-to-understand subset that covers the common scenarios. To
>> handle the more exotic scenarios, you can write a proxy that sits in
>> front of the master, and implements whatever rules you wish, with the
>> rules written in C.
>
> I was thinking about this a bit recently. As I see it, there are three
> fundamental parts of this:
>
> 1) We have a transaction that is being committed. The rest of the
> computations here are all relative to it.

Agreed.

> So in a 3 node case, the internal state table might look like this after
> a bit of data had been committed:
>
> node | location | state
> ----------------------------------
> a | local | fsync b | remote | recv
> c | remote | async
>
> This means that the local node has a fully persistent copy, but the best
> either remote one has done is received the data, it's not on disk at all
> yet at the remote data center. Still working its way through.
>
> 3) The decision about whether the data has been committed to enough
> places to be considered safe by the master is computed by a function
> that is passed this internal table as something like a SRF, and it
> returns a boolean. Once that returns true, saying it's satisfied, the
> transaction closes on the master and continues to percolate out from
> there. If it's false, we wait for another state change to come in and
> return to (2).

You can't implement "wait for X to ack the commit, but if that doesn't 
happen in Y seconds, time out and return true anyway" with that.

> While exposing the local state and running this computation isn't free,
> in situations where there truly are remote nodes in here being
> communicated with the network overhead is going to dwarf that. If there
> were a fast path for the simplest cases and this complicated one for the
> rest, I think you could get the fully programmable behavior some people
> want using simple SQL, rather than having to write a new "Replication
> Description Language" or something so ambitious. This data about what's
> been replicated to where looks an awful lot like a set of rows you can
> operate on using features already in the database to me.

Yeah, if we want to provide full control over when a commit is 
acknowledged to the client, there's certainly no reason we can't expose 
that using a hook or something.

It's pretty scary to call a user-defined function at that point in 
transaction. Even if we document that you must refrain from doing nasty 
stuff like modifying tables in that function, it's still scary.

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-06-02 at 03:22 -0400, Greg Smith wrote:
> Heikki Linnakangas wrote:
> > The possibilities are endless... Your proposal above covers a pretty 
> > good set of scenarios, but it's by no means complete. If we try to 
> > solve everything the configuration will need to be written in a 
> > Turing-complete Replication Description Language. We'll have to pick a 
> > useful, easy-to-understand subset that covers the common scenarios. To 
> > handle the more exotic scenarios, you can write a proxy that sits in 
> > front of the master, and implements whatever rules you wish, with the 
> > rules written in C.
> 
> I was thinking about this a bit recently.  As I see it, there are three 
> fundamental parts of this:
> 
> 1) We have a transaction that is being committed.  The rest of the 
> computations here are all relative to it.
> 
> 2) There is an (internal?) table that lists the state of each 
> replication target relative to that transaction.  It would include the 
> node name, perhaps some metadata ('location' seems the one that's most 
> likely to help with the remote data center issue), and a state code.  
> The codes from http://wiki.postgresql.org/wiki/Streaming_Replication 
> work fine for the last part (which is the only dynamic one--everything 
> else is static data being joined against):
> 
> async=hasn't received yet
> recv=been received but just in RAM
> fsync=received and synced to disk
> apply=applied to the database
> 
> These would need to be enums so they can be ordered from lesser to 
> greater consistency.
> 
> So in a 3 node case, the internal state table might look like this after 
> a bit of data had been committed:
> 
> node | location | state
> ----------------------------------
> a | local    | fsync 
> b | remote | recv
> c | remote | async
> 
> This means that the local node has a fully persistent copy, but the best 
> either remote one has done is received the data, it's not on disk at all 
> yet at the remote data center.  Still working its way through.
> 
> 3) The decision about whether the data has been committed to enough 
> places to be considered safe by the master is computed by a function 
> that is passed this internal table as something like a SRF, and it 
> returns a boolean.  Once that returns true, saying it's satisfied, the 
> transaction closes on the master and continues to percolate out from 
> there.  If it's false, we wait for another state change to come in and 
> return to (2).
> 
> I would propose that most behaviors someone has expressed as being their 
> desired implementation is possible to implement using this scheme. 
> 
> -Semi-sync commit:  proceed as soon somebody else has a copy and hope 
> the copies all become consistent:  EXISTS WHERE state>=recv
> -Don't proceed until there's a fsync'd commit on at least one of the 
> remote nodes:  EXISTS WHERE location='remote' AND state>=fsync
> -Look for a quorum of n commits of fsync quality:  CASE WHEN (SELECT 
> COUNT(*) WHERE state>=fsync)>n THEN true else FALSE end;
> 
> Syntax is obviously rough but I think you can get the drift of what I'm 
> suggesting.
> 
> While exposing the local state and running this computation isn't free, 
> in situations where there truly are remote nodes in here being 
> communicated with the network overhead is going to dwarf that.  If there 
> were a fast path for the simplest cases and this complicated one for the 
> rest, I think you could get the fully programmable behavior some people 
> want using simple SQL, rather than having to write a new "Replication 
> Description Language" or something so ambitious.  This data about what's 
> been replicated to where looks an awful lot like a set of rows you can 
> operate on using features already in the database to me.

I think we're all agreed on the 4 levels: async, recv, fsync, apply.

I also like the concept of a synchronisation/wakeup rule as an abstract
concept. Certainly makes things easier to discuss.

The inputs to the wakeup rule can be defined in different ways. Holding
per-node state at local level looks too complex to me. I'm not
suggesting that we need both per-node AND per-transaction options
interacting at the same time. (That would be a clear argument against
per-transaction options, if that was a requirement - its not, for me).

There seems to be a simpler way: a service oriented model. The
transaction requests a minimum level of synchronisation, the standbys
together service that request. A simple, clear process:

1. If transaction requests recv, fsync or apply, backend sleeps in the
appropriate queue

2. An agent on behalf of the remote standby provides feedback according
to the levels of service defined for that standby.

3. The agent calls a wakeup-rule to see if the backend can be woken yet

The most basic rule is "first-standby-wakes" meaning that the first
standby to provide feedback that the required synchronisation level has
been met by at least one standby will cause the rule to fire.

The next most basic thing is that some standbys can be marked as not
taking part in the quorum and are not capable of waking people with
certain levels of request.

Rules can record intermediate data, to allow us to wait until multiple
agents have provided feedback.

Another rule might be "wait for all standbys marked apply". Though that
has complex behaviour in failure conditions.

Other more complex rules are possible. That can be defined explicitly
with some DDL/RDL, or we could use a plugin, or just hardcode some
options. That level of complexity is secondary and can be added later.
That is especially easy once we have the concept of a synchronisation
wakeup rule as Greg describes here.

Being able to apply synchronisation level at transaction level is very
important. All other systems only provide a single level of
synchronisation, making application design difficult. It will be a
compelling feature for application designers to be able to make their
reference data updated at APPLY, so always consistent everywhere, while
other important data is FSYNC, and less critical data is ASYNC. It's a
real pain to have to partition applications because of their
synchronisation requirements.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Tom Lane
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> It's pretty scary to call a user-defined function at that point in 
> transaction.

Not so much "pretty scary" as "zero chance of being accepted".
And I do mean zero.
        regards, tom lane


Re: Synchronization levels in SR

From
Greg Smith
Date:
Tom Lane wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>   
>> It's pretty scary to call a user-defined function at that point in 
>> transaction.
>>     
>
> Not so much "pretty scary" as "zero chance of being accepted".
> And I do mean zero.
>   

I swear, you guys are such buzzkills some days.  I was suggesting a 
model for building easy prototypes, and advocating a more formal way to 
explain, in what could be code form, what someone means when they 
suggest a particular quorum model or the like.  Maybe all that will ever 
be exposed into a production server are the best of the hand-written 
implementations, and the scary "try your prototype here" hook only shows 
up in debug builds, or never gets written at all.  I did comment that I 
expected faster built-in implementations to be the primary way these 
would be handled.
From what Heikki said, it sounds like the main thing I was didn't 
remember is to include some timestamp information to allow rules based 
on that information too.

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



Re: Synchronization levels in SR

From
Jan Wieck
Date:
On 5/27/2010 4:31 PM, Bruce Momjian wrote:
> Heikki Linnakangas wrote:
>> BTW, I think we're going to need a separate config file for listing the 
>> standbys anyway. There you can write per-server rules and options, but 
>> explicitly knowing about all the standbys also allows the master to 
>> recycle WAL as soon as it has been streamed to all the registered 
>> standbys. Currently we just keep wal_keep_segments files around, just in 
>> case there's a standby out there that needs them.
> 
> Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode'
> on the master, and allow the sync/async mode to be set on each slave,
> e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then
> two slaves with sync mode of #2 or stricter have to complete before the
> master can continue.
> 
> Naming the slaves on the master seems very confusing because I am
> unclear how we would identify named slaves, and the names have to match,
> etc.  
> 
> Also, what would be cool would be if you could run a query on the master
> to view the SR commit mode of each slave.

What would be the use case for such a query?


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
> On 5/27/2010 4:31 PM, Bruce Momjian wrote:
>>
>> Heikki Linnakangas wrote:
>>>
>>> BTW, I think we're going to need a separate config file for listing the
>>> standbys anyway. There you can write per-server rules and options, but
>>> explicitly knowing about all the standbys also allows the master to recycle
>>> WAL as soon as it has been streamed to all the registered standbys.
>>> Currently we just keep wal_keep_segments files around, just in case there's
>>> a standby out there that needs them.
>>
>> Ideally we could set 'slave_sync_count' and 'slave_commit_continue_mode'
>> on the master, and allow the sync/async mode to be set on each slave,
>> e.g. if slave_sync_count = 2 and slave_commit_continue_mode = #2, then
>> two slaves with sync mode of #2 or stricter have to complete before the
>> master can continue.
>>
>> Naming the slaves on the master seems very confusing because I am
>> unclear how we would identify named slaves, and the names have to match,
>> etc.
>> Also, what would be cool would be if you could run a query on the master
>> to view the SR commit mode of each slave.
>
> What would be the use case for such a query?

Monitoring?

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


Re: Synchronization levels in SR

From
David Fetter
Date:
On Thu, Jun 03, 2010 at 10:57:05PM -0400, Robert Haas wrote:
> On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
> > What would be the use case for such a query?
> 
> Monitoring?

s/\?/!/;

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: Synchronization levels in SR

From
Jan Wieck
Date:
On 6/3/2010 10:57 PM, Robert Haas wrote:
> On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
>> On 5/27/2010 4:31 PM, Bruce Momjian wrote:
>>> Also, what would be cool would be if you could run a query on the master
>>> to view the SR commit mode of each slave.
>>
>> What would be the use case for such a query?
> 
> Monitoring?

So that justifies adding code, that the community needs to maintain and 
document, to the core system. If only I could find some monitoring case 
for transaction commit orders ... sigh!


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
> On 6/3/2010 10:57 PM, Robert Haas wrote:
>>
>> On Thu, Jun 3, 2010 at 8:47 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
>>>
>>> On 5/27/2010 4:31 PM, Bruce Momjian wrote:
>>>>
>>>> Also, what would be cool would be if you could run a query on the master
>>>> to view the SR commit mode of each slave.
>>>
>>> What would be the use case for such a query?
>>
>> Monitoring?
>
> So that justifies adding code, that the community needs to maintain and
> document, to the core system. If only I could find some monitoring case for
> transaction commit orders ... sigh!

Dude, I'm not the one arguing with you... actually I don't think
anyone really is, any more, except about details.

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


Re: Synchronization levels in SR

From
Jan Wieck
Date:
On 6/4/2010 4:22 PM, Robert Haas wrote:
> On Fri, Jun 4, 2010 at 3:35 PM, Jan Wieck <JanWieck@yahoo.com> wrote:
>> So that justifies adding code, that the community needs to maintain and
>> document, to the core system. If only I could find some monitoring case for
>> transaction commit orders ... sigh!
> 
> Dude, I'm not the one arguing with you... actually I don't think
> anyone really is, any more, except about details.

I know. You actually pretty much defend my case. Sorry for lacking smiley's.

This is an old habit I have. A good friend from Germany once suspected 
one of my emails to be a spoof because I actually used a smiley.


Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin


Re: Synchronization levels in SR

From
Boszormenyi Zoltan
Date:
Hi,

Dimitri Fontaine írta:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>   
>> Seems strange. If you have 2 standbys and you say you would like node1
>> to be the preferred candidate, then you load it so heavily that a remote
>> server with by-definition much larger network delay responds first, then
>> I say your preference was wrong.
>>     
>
> There's a communication mismatch here I think. The problem with the
> dynamic aspect of the system is that the admin wants to plan ahead and
> choose in advance the failover server.
>
> Other than that I much prefer the automatic and dynamic quorum idea.
>
>   
>> If you, Jan and Yeb wish to completely exclude standbys from being part
>> of any quorum, then I guess we need to have per-standby settings to
>> allow that to be defined. I'm in favour of giving people options. That
>> needn't be a mandatory per-standby setting, just a non-default option,
>> so that we can reduce the complexity of configuration for common
>> cases.
>>     
>
> +1
>
>   
>> Maximum Performance => quorum = 0
>> Maximum Availability => quorum = 1, timeout_action = commit
>> Maximum Protection => quorum = 1, timeout_action = shutdown
>>     
>
> +1
>
> Being able to say that a given server has not been granted to
> participate into the vote allowing to reach the global durability quorum
> will allow for choosing the failover candidates.
>
> Now you're able to have this reporting server and know for sure that
> your sync replicated transactions are not waiting for it.
>
> To summarize, the current "per-transaction approach" would be :
>
>  - transaction level replication synchronous behaviour
>   

Sorry for answering such an old mail, but what is the purpose of
a transaction level synchronous behaviour if async transactions
can be held back by a sync transaction?

In my patch, when the transactions were waiting for ack from
the standby, they have already released all their locks, the wait
happened at the latest possible point in CommitTransaction().

In Fujii's patch (I am looking at synch_rep_0722.patch, is there
a newer one?) the wait happens in RecordTransactionCommit()
so other transactions still see the sync transaction and most
importantly, the locks held by the sync transaction will make
the async  transactions waiting for the same lock wait too.

>  - proxy/cascading in core
>  - quorum setup for deciding any commit is safe
>  - any server can be excluded from the sync quorum
>  - timeout can still raises exception or ignore (commit)?
>
> This last point seems to need some more discussion, or I didn't
> understand well the current positions and proposals.
>
> Regards,
>   

Best regards,
Zoltán Böszörményi



Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Fri, Sep 3, 2010 at 6:43 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
> In my patch, when the transactions were waiting for ack from
> the standby, they have already released all their locks, the wait
> happened at the latest possible point in CommitTransaction().
>
> In Fujii's patch (I am looking at synch_rep_0722.patch, is there
> a newer one?)

No ;)

We'll have to create the patch based on the result of the recent
discussion held on other thread.

> the wait happens in RecordTransactionCommit()
> so other transactions still see the sync transaction and most
> importantly, the locks held by the sync transaction will make
> the async  transactions waiting for the same lock wait too.

The transaction should be invisible to other transactions until
its replication has been completed. So I put the wait before
CommitTransaction() calls ProcArrayEndTransaction(). Is this unsafe?

Regards,

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


Re: Synchronization levels in SR

From
Boszormenyi Zoltan
Date:
Fujii Masao írta:
> On Fri, Sep 3, 2010 at 6:43 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
>   
>> In my patch, when the transactions were waiting for ack from
>> the standby, they have already released all their locks, the wait
>> happened at the latest possible point in CommitTransaction().
>>
>> In Fujii's patch (I am looking at synch_rep_0722.patch, is there
>> a newer one?)
>>     
>
> No ;)
>
> We'll have to create the patch based on the result of the recent
> discussion held on other thread.
>
>   
>> the wait happens in RecordTransactionCommit()
>> so other transactions still see the sync transaction and most
>> importantly, the locks held by the sync transaction will make
>> the async  transactions waiting for the same lock wait too.
>>     
>
> The transaction should be invisible to other transactions until
> its replication has been completed.

Invisible? How can it be invisible? You are in RecordTransactionCommit(),
even before calling ProcArrayEndTransaction(MyProc, latestXid) and
releasing the locks the transaction holds.

>  So I put the wait before
> CommitTransaction() calls ProcArrayEndTransaction(). Is this unsafe?
>   

I don't know whether it's unsafe. In my patch, I only registered the Xid
at the point where you do WaitXLogSend(), this was the safe point
to setup the waiting for sync ack. Otherwise, when the Xid registration
for the sync ack was done in CommitTransaction() later than
RecordTransactionCommit(), there was a race between the primary and
the standby. The scenario was that the standby received and processed
the COMMIT of certain Xids even before the backend  on the primary
properly registered its Xid, so the backend has set up the waiting for
sync ack after this Xid was acked by the standby. The result was stuck
backends.

My idea to split up the registration for wait and the waiting itself
would allow for transaction-level synchronous setting, i.e. in my
patch the transaction released the locks and did all the post-commit
cleanups *then* it waited for sync ack if needed. In the meantime,
because locks were already released, other transactions could
progress with their job, allowing that e.g. async transactions to
progress and theoretically finish faster than the sync transaction
that was waiting for the ack.

The solution in my patch was not racy, registration of the Xid
was done before XLogInsert() in RecordTransactionCommit().
If the standby acked the Xid to the primary before reaching the
end of CommitTransaction() then this backend didn't even needed
to wait because the Xid was found in its PGPROC structure
and the waiting for sync ack was torn down.

But with the LSNs, as you are waiting for XactLastRecEnd
which is set by XLogInsert(). I don't know if it's safe to
WaitXLogSend() after XLogFlush() in RecordTransactionCommit().
I remember that in previous instances of my patch even if I
put the waiting for sync ack directly after   latestXid = RecordTransactionCommit();
in CommitTransaction(), there were cases when I got stuck
backends after a pgbench run. I had the primary and standbys
on the same machine on different ports, so the ack was almost
instant, which wouldn't be the case with a real network. But the
race condition was still there it just doesn't show up with networks
being slower than memory.  In your patch, the waiting happens
almost at the end of RecordTransactionCommit(), so theoretically
it has the same race condition. Am I missing something?

Best regards,
Zoltán Böszörményi

> Regards,
>
>   



Re: Synchronization levels in SR

From
Dimitri Fontaine
Date:
Boszormenyi Zoltan <zb@cybertec.at> writes:
> Sorry for answering such an old mail, but what is the purpose of
> a transaction level synchronous behaviour if async transactions
> can be held back by a sync transaction?

I don't understand why it would be the case (sync holding back async
transactions) — it's been proposed that walsender could periodically
feed back to the master the current WAL position received, synced and
applied.

So you can register your sync transaction to wait (and block) until
walsender sees a synced WAL position after your own (including it) and
another transaction can wait until walsender sees a received WAL
position after its own, for example. Of course, meanwhile, any async
transaction would just commit without caring about slaves.

Not implementing it nor thinking about how to implement it, it seems
simple enough :)

Regards,
--
dim


Re: Synchronization levels in SR

From
Boszormenyi Zoltan
Date:
Dimitri Fontaine írta:
> Boszormenyi Zoltan <zb@cybertec.at> writes:
>   
>> Sorry for answering such an old mail, but what is the purpose of
>> a transaction level synchronous behaviour if async transactions
>> can be held back by a sync transaction?
>>     
>
> I don't understand why it would be the case (sync holding back async
> transactions) — it's been proposed that walsender could periodically
> feed back to the master the current WAL position received, synced and
> applied. 
>
> So you can register your sync transaction to wait (and block) until
> walsender sees a synced WAL position after your own (including it) and
> another transaction can wait until walsender sees a received WAL
> position after its own, for example. Of course, meanwhile, any async
> transaction would just commit without caring about slaves.
>   

The locks held by a transaction are released after
RecordTransactionCommit(), and waiting for the sync ack
happens in this function. Now what happens when a sync
transaction hold a lock that an async one is waiting for?

> Not implementing it nor thinking about how to implement it, it seems
> simple enough :)
>
> Regards,
>   


-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote:
> Dimitri Fontaine írta:
> > Boszormenyi Zoltan <zb@cybertec.at> writes:
> >   
> >> Sorry for answering such an old mail, but what is the purpose of
> >> a transaction level synchronous behaviour if async transactions
> >> can be held back by a sync transaction?
> >>     
> >
> > I don't understand why it would be the case (sync holding back async
> > transactions) — it's been proposed that walsender could periodically
> > feed back to the master the current WAL position received, synced and
> > applied. 
> >
> > So you can register your sync transaction to wait (and block) until
> > walsender sees a synced WAL position after your own (including it) and
> > another transaction can wait until walsender sees a received WAL
> > position after its own, for example. Of course, meanwhile, any async
> > transaction would just commit without caring about slaves.
> >   
> 
> The locks held by a transaction are released after
> RecordTransactionCommit(), and waiting for the sync ack
> happens in this function. Now what happens when a sync
> transaction hold a lock that an async one is waiting for?

It seems your glass in half-empty. Mine is half-full. My perspective
would be that if there is contention between async and sync transactions
then we will get better throughout than if all transactions were sync.
Though perhaps the main issue in that case would be application lock
contention, not the speed of synchronous replication.

The highest level issue is that the system only has so much physical
resources. If we are unable to focus our resources onto the things that
matter most then we end up wasting resources. Mixing async and sync
transactions on the same server allows a single application to carefully
balance performance and durability. Exactly as we do with
synchronous_commit.

By now, people are beginning to see that synchronous replication is
important but has poor performance. Fine grained control is essential to
using it effectively in areas that matter most.

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



Re: Synchronization levels in SR

From
Tom Lane
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote:
>> The locks held by a transaction are released after
>> RecordTransactionCommit(), and waiting for the sync ack
>> happens in this function. Now what happens when a sync
>> transaction hold a lock that an async one is waiting for?

> It seems your glass in half-empty. Mine is half-full.

Simon, you really are failing to advance the conversation.  You claim
that we can have sync plus async transactions without a performance hit,
but you have failed to explain how, at least in any way that anyone
else understands.  Pontificating about how that will be so much better
than not having it doesn't address the problem that others are having
with seeing how to implement it.
        regards, tom lane


Re: Synchronization levels in SR

From
Boszormenyi Zoltan
Date:
Simon Riggs írta:
> On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote:
>   
>> Dimitri Fontaine írta:
>>     
>>> Boszormenyi Zoltan <zb@cybertec.at> writes:
>>>   
>>>       
>>>> Sorry for answering such an old mail, but what is the purpose of
>>>> a transaction level synchronous behaviour if async transactions
>>>> can be held back by a sync transaction?
>>>>     
>>>>         
>>> I don't understand why it would be the case (sync holding back async
>>> transactions) — it's been proposed that walsender could periodically
>>> feed back to the master the current WAL position received, synced and
>>> applied. 
>>>
>>> So you can register your sync transaction to wait (and block) until
>>> walsender sees a synced WAL position after your own (including it) and
>>> another transaction can wait until walsender sees a received WAL
>>> position after its own, for example. Of course, meanwhile, any async
>>> transaction would just commit without caring about slaves.
>>>   
>>>       
>> The locks held by a transaction are released after
>> RecordTransactionCommit(), and waiting for the sync ack
>> happens in this function. Now what happens when a sync
>> transaction hold a lock that an async one is waiting for?
>>     
>
> It seems your glass in half-empty. Mine is half-full.

This is good, we can meet halfway. :-)

>  My perspective
> would be that if there is contention between async and sync transactions
> then we will get better throughout than if all transactions were sync.
> Though perhaps the main issue in that case would be application lock
> contention, not the speed of synchronous replication.
>   

The difference we are talking about is:

xact1                    xact2
begin                           begin
lock something                           lock same

(in commit)
write wal record
wait for sync ack
release locks/etc   <xact2 can proceed from here

vs.

xact1                    xact2
begin                           begin
lock something                           lock same

(in commit)
write wal record
release locks/etc   <xact2 can proceed from here
wait for sync ack

In the first case, the contention is obviously increased.
With this, we are creating more idle time in the server
instead of letting other transactions do their jobs as soon
as possible. The second method was implemented in my
patch. Are there any drawbacks with this?

> The highest level issue is that the system only has so much physical
> resources. If we are unable to focus our resources onto the things that
> matter most then we end up wasting resources. Mixing async and sync
> transactions on the same server allows a single application to carefully
> balance performance and durability. Exactly as we do with
> synchronous_commit.
>   

I don't think this is the same situation. With synchronous_commit,
you have an auxiliary process that's handed the job of doing
the syncing. But there's nowhere to hand out the waiting for
sync ack from the standby.

> By now, people are beginning to see that synchronous replication is
> important but has poor performance. Fine grained control is essential to
> using it effectively in areas that matter most.
>   


-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Mon, 2010-09-06 at 22:32 +0200, Boszormenyi Zoltan wrote:
> (in commit)
> write wal record
> release locks/etc   <xact2 can proceed from here
> wait for sync ack
> 
> In the first case, the contention is obviously increased.
> With this, we are creating more idle time in the server
> instead of letting other transactions do their jobs as soon
> as possible. The second method was implemented in my
> patch. Are there any drawbacks with this? 

Then I respectfully suggest that you're releasing locks too early.

Your proposal would allow a 2nd user to see the results of the 1st
user's transaction before the 1st user knew about whether it had
committed or not.

I know why you want that, but I don't think its right.

This has very little, if anything, to do with mixing async/sync
connections. You make it sound like all transactions always wait for
other transactions, which they definitely don't, especially in
reasonably well designed applications.

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



Re: Synchronization levels in SR

From
Greg Stark
Date:
On Mon, Sep 6, 2010 at 10:02 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Then I respectfully suggest that you're releasing locks too early.
>
> Your proposal would allow a 2nd user to see the results of the 1st
> user's transaction before the 1st user knew about whether it had
> committed or not.
>
> I know why you want that, but I don't think its right.

Well that's always possible. The 1st user might just not wake up
before the 2nd user gets the response back.

The question is what happens if the server crashes and is failed over
to the slave. The 2nd user with the async transaction might have seen
data commited by the 1st user with his sync transaction but was
subsequently lost. Is the user expecting that making his transaction
synchronously replicated guarantees that *nobody* can see this data
unless the transaction is guaranteed to have been replicated or is he
only expecting it to guarantee that *he* can't see the commit until it
can be trusted to be replicated?

For that matter I'm not entirely clear I understand how the timing
here works at all. If transactions can't be considered to be committed
before they're acknowledged by the replica what happens if the master
crashes after the WAL is written and then comes back without a
failover. Then the transaction would be immediately visible even if it
still hasn't been replicated yet.

I think there's no way with our current infrastructure to guarantee
that other transactions can't see your data before it's been
replicated, So making any promise otherwise for some cases is only
going to be a lie.

To guarantee synchronous replication doesn't show data until it's been
replicated we would have to some kind of 2-phase commit where we send
the commit record to the slave and wait until the slave has received
it and confirmed it has written it (but it doesn't replay it unless
there's a failover) then write the master's commit record and send the
message to the slave that it's safe to replay those records.

-- 
greg


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Mon, 2010-09-06 at 23:07 +0100, Greg Stark wrote:
> On Mon, Sep 6, 2010 at 10:02 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > Then I respectfully suggest that you're releasing locks too early.
> >
> > Your proposal would allow a 2nd user to see the results of the 1st
> > user's transaction before the 1st user knew about whether it had
> > committed or not.
> >
> > I know why you want that, but I don't think its right.
> 
> Well that's always possible. The 1st user might just not wake up
> before the 2nd user gets the response back.
> 
> The question is what happens if the server crashes and is failed over
> to the slave. The 2nd user with the async transaction might have seen
> data commited by the 1st user with his sync transaction but was
> subsequently lost. Is the user expecting that making his transaction
> synchronously replicated guarantees that *nobody* can see this data
> unless the transaction is guaranteed to have been replicated or is he
> only expecting it to guarantee that *he* can't see the commit until it
> can be trusted to be replicated?
> 
> For that matter I'm not entirely clear I understand how the timing
> here works at all. If transactions can't be considered to be committed
> before they're acknowledged by the replica what happens if the master
> crashes after the WAL is written and then comes back without a
> failover. Then the transaction would be immediately visible even if it
> still hasn't been replicated yet.
> 
> I think there's no way with our current infrastructure to guarantee
> that other transactions can't see your data before it's been
> replicated, So making any promise otherwise for some cases is only
> going to be a lie.
> 
> To guarantee synchronous replication doesn't show data until it's been
> replicated we would have to some kind of 2-phase commit where we send
> the commit record to the slave and wait until the slave has received
> it and confirmed it has written it (but it doesn't replay it unless
> there's a failover) then write the master's commit record and send the
> message to the slave that it's safe to replay those records.

Just to add that this part of the discussion has nothing at all to do
with my proposal for master controlled replication. Zoltan is simply
discussing when the wait should occur with sync replication. I have no
proposal to vary that myself, wherever we eventually decide the wait
should occur.

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



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Mon, 2010-09-06 at 16:11 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote:
> >> The locks held by a transaction are released after
> >> RecordTransactionCommit(), and waiting for the sync ack
> >> happens in this function. Now what happens when a sync
> >> transaction hold a lock that an async one is waiting for?
> 
> > It seems your glass in half-empty. Mine is half-full.
> 
> Simon, you really are failing to advance the conversation.  You claim
> that we can have sync plus async transactions without a performance hit,
> but you have failed to explain how, at least in any way that anyone
> else understands.  Pontificating about how that will be so much better
> than not having it doesn't address the problem that others are having
> with seeing how to implement it.

A performance hit from mixing sync and async is unlikely. The overhead
of deciding whether to wait after commit is trivial. At worst, the async
transactions would go at the same speed as the sync transactions,
especially if the application contends with itself, which is by no means
a certainty. If acting independently, the async transactions would
clearly go much faster. So the right question for discussion is "how
much will we gain by mixing async/sync?". Since we had exactly this
situation for synchronous_commit and a similar discussion, I expect a
similar eventual outcome.

The discussion would go better if we had clear performance results
published from existing work and we did not dissuade people from
objective testing. Then you'd probably understand why I think this is so
important to me.

I've explained more than once how my proposal can work and Dimitri at
least appears to have understood with zero off-list conversation. So far
the discussion has been mostly negative and the reasons given haven't
scored high on logic, I'm sorry to say. I will present a code-based
proposal rather than just huge piles of words, to make this a more
concrete discussion.

-- Simon Riggs           www.2ndQuadrant.com



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Mon, Sep 6, 2010 at 5:02 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Then I respectfully suggest that you're releasing locks too early.
>
> Your proposal would allow a 2nd user to see the results of the 1st
> user's transaction before the 1st user knew about whether it had
> committed or not.

Marking the transaction committed in CLOG will have that effect
anyway.  We are not doing strict two-phase locking.

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


Re: Synchronization levels in SR

From
Markus Wanner
Date:
Hi,

On 05/27/2010 01:28 PM, Robert Haas wrote:
> How do you propose to guarantee that?  ISTM that you have to either
> commit locally first, or send the commit to the remote first.  Either
> way, the two events won't occur exactly simultaneously.

I'm not getting the point of this discussion. As long as the database 
confirms the commit to the client only *after* having an ack from the 
standby and *after* committing locally, there's no problem.

In any case, a server failure in between the commit request of the 
client and the commit confirmation for the client results in a client 
that can't tell if its transaction committed or not.

So why do we care what to do first internally? Ideally, these two tasks 
should happen concurrently, IMO.

Regards

Markus Wanner


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Tue, Sep 7, 2010 at 4:01 AM, Markus Wanner <markus@bluegap.ch> wrote:
> In any case, a server failure in between the commit request of the client
> and the commit confirmation for the client results in a client that can't
> tell if its transaction committed or not.
>
> So why do we care what to do first internally? Ideally, these two tasks
> should happen concurrently, IMO.

Right, definitely.  The trouble is that if they happen concurrently,
and there's a crash, you have to be prepared for the possibility that
either one of the two has completed and the other is not.  In
practice, this means that the master and standby need to compare notes
on the ending WAL location and whichever one is further advanced needs
to stream the intervening records to the other.  This would be an
awesome feature, but it's hard, so for a first version, it makes sense
to commit on the master first and then on the standby after the master
is known done.

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


Re: Synchronization levels in SR

From
Markus Wanner
Date:
On 09/07/2010 02:16 PM, Robert Haas wrote:
> Right, definitely.  The trouble is that if they happen concurrently,
> and there's a crash, you have to be prepared for the possibility that
> either one of the two has completed and the other is not.

Understood.

> In
> practice, this means that the master and standby need to compare notes
> on the ending WAL location and whichever one is further advanced needs
> to stream the intervening records to the other.

Not necessarily, no. Remember that the client didn't get a commit 
confirmation. So reverting might also be a correct solution (i.e. not 
violating the durability constraint).

> This would be an
> awesome feature, but it's hard, so for a first version, it makes sense
> to commit on the master first and then on the standby after the master
> is known done.

The obvious downside of that is that latency adds up, instead of just 
being the max of the two operations. And that for normal operation. 
While at best it saves an un-confirmed transaction in the failure case.

It might be harder to implement, yes.

Regards

Markus Wanner


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Tue, Sep 7, 2010 at 9:45 AM, Markus Wanner <markus@bluegap.ch> wrote:
> On 09/07/2010 02:16 PM, Robert Haas wrote:
>>
>> Right, definitely.  The trouble is that if they happen concurrently,
>> and there's a crash, you have to be prepared for the possibility that
>> either one of the two has completed and the other is not.
>
> Understood.
>
>> In
>> practice, this means that the master and standby need to compare notes
>> on the ending WAL location and whichever one is further advanced needs
>> to stream the intervening records to the other.
>
> Not necessarily, no. Remember that the client didn't get a commit
> confirmation. So reverting might also be a correct solution (i.e. not
> violating the durability constraint).

In theory, that's true, but if we do that, then there's an even bigger
problem: the slave might have replayed WAL ahead of the master
location; therefore the slave is now corrupt and a new base backup
must be taken.

>> This would be an
>> awesome feature, but it's hard, so for a first version, it makes sense
>> to commit on the master first and then on the standby after the master
>> is known done.
>
> The obvious downside of that is that latency adds up, instead of just being
> the max of the two operations. And that for normal operation. While at best
> it saves an un-confirmed transaction in the failure case.
>
> It might be harder to implement, yes.

Yeah, I hope we'll get there eventually.

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


Re: Synchronization levels in SR

From
Markus Wanner
Date:
On 09/07/2010 04:15 PM, Robert Haas wrote:
> In theory, that's true, but if we do that, then there's an even bigger
> problem: the slave might have replayed WAL ahead of the master
> location; therefore the slave is now corrupt and a new base backup
> must be taken.

The slave isn't corrupt. It would suffice to "late abort" committed 
transactions the master doesn't know about.

However, I realize that undoing of WAL isn't something that's 
implemented (nor planned). So it's probably easier to forward the master 
in such a case.

> Yeah, I hope we'll get there eventually.

Understood. Thanks.

Markus Wanner



Re: Synchronization levels in SR

From
Ron Mayer
Date:
Markus Wanner wrote:
> On 09/07/2010 02:16 PM, Robert Haas wrote:
>> practice, this means that the master and standby need to compare notes
>> on the ending WAL location and whichever one is further advanced needs
>> to stream the intervening records to the other.
> 
> Not necessarily, no. Remember that the client didn't get a commit
> confirmation. So reverting might also be a correct solution (i.e. not
> violating the durability constraint).

In that situation, wouldn't it be possible that a different client
queried the slave and already saw the result of that transaction
which would later be rolled back?



Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-09-07 at 16:31 +0200, Markus Wanner wrote:
> On 09/07/2010 04:15 PM, Robert Haas wrote:
> > In theory, that's true, but if we do that, then there's an even bigger
> > problem: the slave might have replayed WAL ahead of the master
> > location; therefore the slave is now corrupt and a new base backup
> > must be taken.
> 
> The slave isn't corrupt. It would suffice to "late abort" committed 
> transactions the master doesn't know about.

The slave *might* be ahead of the master. And if it is, the case we're
discussing is where the master just crashed and *might* not even be
coming back at all, at least for a while. The standby does differ from
master, but with the master down I don't regard that as a useful
statement.

If we wait for fsync on master and then transfer to standby the times
are additive. If we do them concurrently the response times will be the
maximum response time of fsync/transfer, as Markus observes.

ISTM that most people would be more interested in reducing response
times by ~50% rather than in being exactly correct in an edge case. So
we should be planning that as a robustness option, not "it cannot be
done", which seems to be echoing around to much for my liking.

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



Re: Synchronization levels in SR

From
Tom Lane
Date:
Markus Wanner <markus@bluegap.ch> writes:
> On 09/07/2010 04:15 PM, Robert Haas wrote:
>> In theory, that's true, but if we do that, then there's an even bigger
>> problem: the slave might have replayed WAL ahead of the master
>> location; therefore the slave is now corrupt and a new base backup
>> must be taken.

> The slave isn't corrupt. It would suffice to "late abort" committed 
> transactions the master doesn't know about.

Oh yes it is.  If the slave replays WAL that didn't happen on the
master, it might for instance have heap tuples in TID slots that are
empty on the master, or index pages laid out differently from the
master.  Trying to apply additional WAL from the master will fail badly.

We can *not* allow the slave to replay WAL ahead of what is known
committed to disk on the master.  The only way to make that safe
is the compare-notes-and-ship-WAL-back approach that Robert mentioned.

If you feel that decoupling WAL application is absolutely essential
to have a credible feature, then you'd better bite the bullet and
start working on the ship-WAL-back code.
        regards, tom lane


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Tue, 2010-09-07 at 11:17 -0400, Tom Lane wrote:
> Markus Wanner <markus@bluegap.ch> writes:
> > On 09/07/2010 04:15 PM, Robert Haas wrote:
> >> In theory, that's true, but if we do that, then there's an even bigger
> >> problem: the slave might have replayed WAL ahead of the master
> >> location; therefore the slave is now corrupt and a new base backup
> >> must be taken.
> 
> > The slave isn't corrupt. It would suffice to "late abort" committed 
> > transactions the master doesn't know about.
> 
> Oh yes it is.  If the slave replays WAL that didn't happen on the
> master, it might for instance have heap tuples in TID slots that are
> empty on the master, or index pages laid out differently from the
> master.  Trying to apply additional WAL from the master will fail badly.
> 
> We can *not* allow the slave to replay WAL ahead of what is known
> committed to disk on the master.  The only way to make that safe
> is the compare-notes-and-ship-WAL-back approach that Robert mentioned.
> 
> If you feel that decoupling WAL application is absolutely essential
> to have a credible feature, then you'd better bite the bullet and
> start working on the ship-WAL-back code.

Why not just failover? 

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



Re: Synchronization levels in SR

From
Markus Wanner
Date:
On 09/07/2010 04:47 PM, Ron Mayer wrote:
> In that situation, wouldn't it be possible that a different client
> queried the slave and already saw the result of that transaction
> which would later be rolled back?

Good point, yes.

Regards

Markus Wanner


Re: Synchronization levels in SR

From
Tom Lane
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Tue, 2010-09-07 at 11:17 -0400, Tom Lane wrote:
>> We can *not* allow the slave to replay WAL ahead of what is known
>> committed to disk on the master.  The only way to make that safe
>> is the compare-notes-and-ship-WAL-back approach that Robert mentioned.
>> 
>> If you feel that decoupling WAL application is absolutely essential
>> to have a credible feature, then you'd better bite the bullet and
>> start working on the ship-WAL-back code.

> Why not just failover? 

Guaranteed failover is another large piece we don't have.
        regards, tom lane


Re: Synchronization levels in SR

From
Markus Wanner
Date:
Hi,

On 09/07/2010 05:17 PM, Tom Lane wrote:
> Oh yes it is.  If the slave replays WAL that didn't happen on the
> master, it might for instance have heap tuples in TID slots that are
> empty on the master, or index pages laid out differently from the
> master.  Trying to apply additional WAL from the master will fail badly.

Sure. Reverting to the master's state would be required to be able to 
safely proceed. Granted, that's far from simple.

Robert's argument about read queries on the standby convinced me, that 
you always need to recover to the node with the newest transactions 
applied (i.e. better advance rather than revert). Making sure the 
standby can't ever be ahead of the master node certainly is the simplest 
way to guarantee that. At its cost for normal operation, though.

How about a master failure which leads to a fail-over, immediately 
followed by a failure of that former standby (and now a master)? The old 
master might then be in the very same situation: having WAL applied that 
the new master doesn't. Do we require former masters to fetch a base 
backup? How does it know the difference, once it gets back up?

> We can *not* allow the slave to replay WAL ahead of what is known
> committed to disk on the master.  The only way to make that safe
> is the compare-notes-and-ship-WAL-back approach that Robert mentioned.

Agreed.

(And it's worth pointing out that this approach has a pretty nasty 
requirement for a full-cluster crash: all nodes that were synchronously 
replicated to need to come back up after such a crash, so as to be able 
to reliably determine which has the newest transaction).

> If you feel that decoupling WAL application is absolutely essential
> to have a credible feature, then you'd better bite the bullet and
> start working on the ship-WAL-back code.

My feeling is that WAL is the wrong format to do replication. But that's 
a another story.

Regards

Markus Wanner


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Tue, Sep 7, 2010 at 11:06 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Tue, 2010-09-07 at 16:31 +0200, Markus Wanner wrote:
>> On 09/07/2010 04:15 PM, Robert Haas wrote:
>> > In theory, that's true, but if we do that, then there's an even bigger
>> > problem: the slave might have replayed WAL ahead of the master
>> > location; therefore the slave is now corrupt and a new base backup
>> > must be taken.
>>
>> The slave isn't corrupt. It would suffice to "late abort" committed
>> transactions the master doesn't know about.
>
> The slave *might* be ahead of the master. And if it is, the case we're
> discussing is where the master just crashed and *might* not even be
> coming back at all, at least for a while. The standby does differ from
> master, but with the master down I don't regard that as a useful
> statement.
>
> If we wait for fsync on master and then transfer to standby the times
> are additive. If we do them concurrently the response times will be the
> maximum response time of fsync/transfer, as Markus observes.
>
> ISTM that most people would be more interested in reducing response
> times by ~50% rather than in being exactly correct in an edge case. So
> we should be planning that as a robustness option, not "it cannot be
> done", which seems to be echoing around to much for my liking.

People who are more concerned about performance than robustness aren't
going to use sync rep in the first place.  They're going to run it in
async, which will improve performance by FAR more than you'll ever be
able to manage by deciding that you don't care about handling some of
the failure cases correctly.

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


Re: Synchronization levels in SR

From
Markus Wanner
Date:
Hi,

On 09/07/2010 06:00 PM, Robert Haas wrote:
> People who are more concerned about performance than robustness aren't
> going to use sync rep in the first place.

I'm advocating sync (or eager, FWIW) replication for years, now. One of 
the hardest preconception I'm always confronted with is: this must 
perform poorly!

Whether or not that's true depends, but my point is: people who need 
that level of robustness certainly care about performance as well. 
Telling them to use async replication instead is not an option. (The 
ability to mix sync and async replication per transaction is one, BTW).

> They're going to run it in
> async, which will improve performance by FAR more than you'll ever be
> able to manage by deciding that you don't care about handling some of
> the failure cases correctly.

Running in async and then trying to achieve the required level of 
robustness in the application layer pretty certainly performs worse than 
a good sync replication implementation. Async only wins if you really 
don't care about the loss of transactions in the case of a failure. In 
every other case, robustness is better taken care of by the database 
system itself, IMO.

That being said, I certainly agree to do things step by step. And the 
ability to write to WAL and wait for ack from a standby concurrently can 
(and probably should) be considered an optimization, yes.

Regards

Markus Wanner


Re: Synchronization levels in SR

From
Markus Wanner
Date:
On 09/07/2010 05:55 PM, Markus Wanner wrote:
> Robert's argument

Sorry, I meant Ron.

Regards

Markus Wanner


Re: Synchronization levels in SR

From
marcin mank
Date:
On Tue, Sep 7, 2010 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> We can *not* allow the slave to replay WAL ahead of what is known
> committed to disk on the master.  The only way to make that safe
> is the compare-notes-and-ship-WAL-back approach that Robert mentioned.
>
> If you feel that decoupling WAL application is absolutely essential
> to have a credible feature, then you'd better bite the bullet and
> start working on the ship-WAL-back code.
>

In the mode where it is not required that the WAL is applied (only
sent to the slave / synced to slave disk) one alternative is to have a
separate pointer to the last WAL record that can be safely applied on
the slave. Then You can send the un-synced WAL to the slave (while
concurrently syncing it on the master). When both the slave an the
master sync complete, one can give the client a commit notification,
increase the pointer, and send it to the slave (it would be a separate
WAL record type I guess).

In case of master failure, the slave can discard the un-applied WAL
after the pointer.

Greetings
marcin


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Tue, Sep 7, 2010 at 4:06 PM, marcin mank <marcin.mank@gmail.com> wrote:
> On Tue, Sep 7, 2010 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We can *not* allow the slave to replay WAL ahead of what is known
>> committed to disk on the master.  The only way to make that safe
>> is the compare-notes-and-ship-WAL-back approach that Robert mentioned.
>>
>> If you feel that decoupling WAL application is absolutely essential
>> to have a credible feature, then you'd better bite the bullet and
>> start working on the ship-WAL-back code.
>>
>
> In the mode where it is not required that the WAL is applied (only
> sent to the slave / synced to slave disk) one alternative is to have a
> separate pointer to the last WAL record that can be safely applied on
> the slave. Then You can send the un-synced WAL to the slave (while
> concurrently syncing it on the master). When both the slave an the
> master sync complete, one can give the client a commit notification,
> increase the pointer, and send it to the slave (it would be a separate
> WAL record type I guess).
>
> In case of master failure, the slave can discard the un-applied WAL
> after the pointer.

But the pointer on the slave has to be fsync'd to make it persistent,
which likely takes roughly the same amount of time as fsync-ing the
WAL itself.

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Tue, Sep 7, 2010 at 6:02 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Mon, 2010-09-06 at 22:32 +0200, Boszormenyi Zoltan wrote:
>> (in commit)
>> write wal record
>> release locks/etc   <xact2 can proceed from here
>> wait for sync ack
>>
>> In the first case, the contention is obviously increased.
>> With this, we are creating more idle time in the server
>> instead of letting other transactions do their jobs as soon
>> as possible. The second method was implemented in my
>> patch. Are there any drawbacks with this?
>
> Then I respectfully suggest that you're releasing locks too early.
>
> Your proposal would allow a 2nd user to see the results of the 1st
> user's transaction before the 1st user knew about whether it had
> committed or not.
>
> I know why you want that, but I don't think its right.

Agreed. That's why I put the wait before ProcArrayEndTransaction()
is called.

Regards,

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


Re: Synchronization levels in SR

From
Boszormenyi Zoltan
Date:
Fujii Masao írta:
> On Tue, Sep 7, 2010 at 6:02 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>   
>> On Mon, 2010-09-06 at 22:32 +0200, Boszormenyi Zoltan wrote:
>>     
>>> (in commit)
>>> write wal record
>>> release locks/etc   <xact2 can proceed from here
>>> wait for sync ack
>>>
>>> In the first case, the contention is obviously increased.
>>> With this, we are creating more idle time in the server
>>> instead of letting other transactions do their jobs as soon
>>> as possible. The second method was implemented in my
>>> patch. Are there any drawbacks with this?
>>>       
>> Then I respectfully suggest that you're releasing locks too early.
>>
>> Your proposal would allow a 2nd user to see the results of the 1st
>> user's transaction before the 1st user knew about whether it had
>> committed or not.
>>
>> I know why you want that, but I don't think its right.
>>     
>
> Agreed. That's why I put the wait before ProcArrayEndTransaction()
> is called.
>   

Then there is no use to implement individual sync/async
replicated transactions, period. An async replicated transaction
that waits for a sync replicated transaction because of locks
will become implicitely sync. It just waits for another transactions'
sync ack.

Best regards,
Zoltán Böszörményi

> Regards,
>
>   


-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/



Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, Sep 8, 2010 at 7:04 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
> Then there is no use to implement individual sync/async
> replicated transactions, period. An async replicated transaction
> that waits for a sync replicated transaction because of locks
> will become implicitely sync. It just waits for another transactions'
> sync ack.

Hmm.. it's the same with async transaction (i.e., synchronous_commit = false)
and sync one (synchronous_commit = true). Async transaction cannot take the
lock held by sync one until the sync has flushed the WAL.

Regards,

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


Re: Synchronization levels in SR

From
Boszormenyi Zoltan
Date:
Fujii Masao írta:
> On Wed, Sep 8, 2010 at 7:04 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
>   
>> Then there is no use to implement individual sync/async
>> replicated transactions, period. An async replicated transaction
>> that waits for a sync replicated transaction because of locks
>> will become implicitely sync. It just waits for another transactions'
>> sync ack.
>>     
>
> Hmm.. it's the same with async transaction (i.e., synchronous_commit = false)
> and sync one (synchronous_commit = true). Async transaction cannot take the
> lock held by sync one until the sync has flushed the WAL.
>   

You are right.

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, Sep 8, 2010 at 6:52 AM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
> Fujii Masao írta:
>> On Wed, Sep 8, 2010 at 7:04 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
>>
>>> Then there is no use to implement individual sync/async
>>> replicated transactions, period. An async replicated transaction
>>> that waits for a sync replicated transaction because of locks
>>> will become implicitely sync. It just waits for another transactions'
>>> sync ack.
>>>
>>
>> Hmm.. it's the same with async transaction (i.e., synchronous_commit = false)
>> and sync one (synchronous_commit = true). Async transaction cannot take the
>> lock held by sync one until the sync has flushed the WAL.
>>
>
> You are right.

I still don't see why it matters whether you wait before or after
releasing locks.  As soon as the transaction is marked committed in
CLOG, other transactions can potentially see its effects.  Holding on
to all the locks might mitigate that somewhat, but it's not going to
eliminate the problem.  And in any event, there is ALWAYS a window of
time during which the client doesn't know the transaction has
committed but other transactions can potentially see its effects.

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, Sep 8, 2010 at 8:43 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> I still don't see why it matters whether you wait before or after
> releasing locks.  As soon as the transaction is marked committed in
> CLOG, other transactions can potentially see its effects.

AFAIR, even if CLOG has been updated, until the transaction is marked
as no longer running in PGPROC, probably other transactions cannot
see its effects. But, if it's not true, I'd make the transaction wait
for replication before CLOG update.

> And in any event, there is ALWAYS a window of
> time during which the client doesn't know the transaction has
> committed but other transactions can potentially see its effects.

Yep. The problem here is that synchronous replication is likely to
make the window very big.

Regards,

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


Re: Synchronization levels in SR

From
Markus Wanner
Date:
On 09/08/2010 12:04 PM, Boszormenyi Zoltan wrote:
> Then there is no use to implement individual sync/async
> replicated transactions, period.

I disagree. Different transactions have different priorities for latency 
vs. failure-resistance.

> An async replicated transaction
> that waits for a sync replicated transaction because of locks
> will become implicitely sync.

Sure. But how often do your transactions wait for another one because of 
locks? What do we have MVCC for?

Regards

Markus Wanner


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-09-08 at 12:04 +0200, Boszormenyi Zoltan wrote:
> >>
> >> I know why you want that, but I don't think its right.
> >>     
> >
> > Agreed. That's why I put the wait before ProcArrayEndTransaction()
> > is called.
> >   
> 
> Then there is no use to implement individual sync/async
> replicated transactions, period. An async replicated transaction
> that waits for a sync replicated transaction because of locks
> will become implicitely sync. It just waits for another transactions'
> sync ack.

You aren't making any sense. You have made a general observation and
deduced something specific about replication from it. Most transactions
are not blocked by locks, especially in well designed applications, so
the argument is not relevant to replication.

If *any* two transactions wait upon each other then t2 will always wait
until t1 has completed. 

If t1 is slow then any tuning you do on t2 will likely be wasted. If you
are concerned about performance you should first remove the dependency
between t1 and t2. The above observation isn't sufficient to conclude
that "tuning of t2 should not happen via the tuning feature Simon has
suggested". It's not sufficient to conclude much, if anything.

As it turns out, in the scenario you outline t2 *would* return faster
because you had marked it as "async". But it would wait behind t1, as
you say. So the performance gain will be clear and measurable. Even so,
it would be best to tune the problem (lock contention) not moan that the
tool you're choosing to use using (tuning replication) is at fault for
being inappropriate to the problem.

Mixing sync and async transactions is useful and it's a simple matter to
come up with real examples where it would benefit, as well as easily
testable workloads using pgbench. For example, customer table updates
(sync) alongside chat messages (async).

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



Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, Sep 8, 2010 at 8:30 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>> And in any event, there is ALWAYS a window of
>> time during which the client doesn't know the transaction has
>> committed but other transactions can potentially see its effects.
>
> Yep. The problem here is that synchronous replication is likely to
> make the window very big.

So what?  If the correctness of your application depends on the
*amount of time* this window lasts, it's already broken.  It seems
like you're arguing that we should artificially increase lock
contention to guard against possible race conditions in user
applications.  That doesn't make any sense to me, so one of us is
confused.

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


Re: Synchronization levels in SR

From
Fujii Masao
Date:
On Wed, Sep 8, 2010 at 10:07 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Sep 8, 2010 at 8:30 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>> And in any event, there is ALWAYS a window of
>>> time during which the client doesn't know the transaction has
>>> committed but other transactions can potentially see its effects.
>>
>> Yep. The problem here is that synchronous replication is likely to
>> make the window very big.
>
> So what?  If the correctness of your application depends on the
> *amount of time* this window lasts, it's already broken.  It seems
> like you're arguing that we should artificially increase lock
> contention to guard against possible race conditions in user
> applications.  That doesn't make any sense to me, so one of us is
> confused.

Yep ;) On second thought, the problem here is that the effects of
the transaction marked as committed but still waiting for replication
can disappear after failover.

Regards,

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


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, Sep 8, 2010 at 9:32 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Wed, Sep 8, 2010 at 10:07 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Sep 8, 2010 at 8:30 AM, Fujii Masao <masao.fujii@gmail.com> wrote:
>>>> And in any event, there is ALWAYS a window of
>>>> time during which the client doesn't know the transaction has
>>>> committed but other transactions can potentially see its effects.
>>>
>>> Yep. The problem here is that synchronous replication is likely to
>>> make the window very big.
>>
>> So what?  If the correctness of your application depends on the
>> *amount of time* this window lasts, it's already broken.  It seems
>> like you're arguing that we should artificially increase lock
>> contention to guard against possible race conditions in user
>> applications.  That doesn't make any sense to me, so one of us is
>> confused.
>
> Yep ;) On second thought, the problem here is that the effects of
> the transaction marked as committed but still waiting for replication
> can disappear after failover.

Ah!  I think that's right.  So the scenario we're trying to guard
against something like this.  A customer makes a withdrawal of money
from an ATM; their bank balance is debited.  The transaction tries to
commit.  After the transaction becomes visible to other backends but
before WAL is reaches the standby, another transaction begins and
reads the customer's balance.  Naturally, they get the new, lower
balance.  Crash, master dead.  Failover.  If another transcation
begins and reads the customer's balance again, it's back to the old
value.  So we have a phantom transaction: it appeared as committed and
then vanished again.

So that means we have to make sure that none of the effects of a
transaction can be seen until WAL is fsync'd on the master AND the
slave has acked.

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


Re: Synchronization levels in SR

From
Simon Riggs
Date:
On Wed, 2010-09-08 at 09:50 -0400, Robert Haas wrote:

> So that means we have to make sure that none of the effects of a
> transaction can be seen until WAL is fsync'd on the master AND the
> slave has acked.

Yes, that's right. And I like your example; one for the docs.

There is a slight complexity there: An application might connect to the
standby and see the changes made by the transaction, even though the
master has not yet been notified, but will be in a moment. I don't see
that as an issue though, but worth mentioning cos its just the
"Byzantine Generals" problem.

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



Re: Synchronization levels in SR

From
David Fetter
Date:
On Wed, Sep 08, 2010 at 03:22:46PM +0100, Simon Riggs wrote:
> On Wed, 2010-09-08 at 09:50 -0400, Robert Haas wrote:
> 
> > So that means we have to make sure that none of the effects of a
> > transaction can be seen until WAL is fsync'd on the master AND the
> > slave has acked.
> 
> Yes, that's right. And I like your example; one for the docs.
> 
> There is a slight complexity there: An application might connect to
> the standby and see the changes made by the transaction, even though
> the master has not yet been notified, but will be in a moment. I
> don't see that as an issue though, but worth mentioning cos its just
> the "Byzantine Generals" problem.

For completeness, a reference to the aforementioned Byzantine
Generals:  http://en.wikipedia.org/wiki/Byzantine_fault_tolerance

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: Synchronization levels in SR

From
Robert Haas
Date:
On Wed, Sep 8, 2010 at 10:22 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Wed, 2010-09-08 at 09:50 -0400, Robert Haas wrote:
>
>> So that means we have to make sure that none of the effects of a
>> transaction can be seen until WAL is fsync'd on the master AND the
>> slave has acked.
>
> Yes, that's right. And I like your example; one for the docs.
>
> There is a slight complexity there: An application might connect to the
> standby and see the changes made by the transaction, even though the
> master has not yet been notified, but will be in a moment. I don't see
> that as an issue though, but worth mentioning cos its just the
> "Byzantine Generals" problem.

I think that's OK too, because there's no way we can guarantee that
the transaction becomes visible exactly simultaneously on both nodes.
What we do need to guarantee is that it is known committed on both
nodes before it becomes visible on either, so that even if there is a
crash or failover it can't uncommit itself.  So the order of events
must be:

- fsync WAL on master
- send WAL to slave
- wait for ack from slave
- allow transaction's effects to become visible on master

If the slave is only guaranteeing *receipt* of the WAL rather than
fsync or replay of the WAL, then there is still a possibility of a
disappearing transaction if the master and standby fail simultaneously
AND a failover then occurs.  So don't pick that mode if a disappearing
transaction will result in someone dying or your $20B company going
bankrupt or ...

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