Sync Rep v17

On 19 February 2011 00:06, Simon Riggs <simon@2ndquadrant.com> wrote:

Well, good news all round.

v17 implements what I believe to be the final set of features for sync
rep. This one I'm actually fairly happy with. It can be enjoyed best at
DEBUG3.

The patch is very lite touch on a few areas of code, plus a chunk of
specific code, all on master-side. Pretty straight really. I'm sure
problems will be found, its not long since I completed this; thanks to
Daniel Farina for your help with patch assembly.

Which is just as well, because the other news is that I'm off on holiday
for a few days, which is most inconvenient. I won't be committing this
for at least a week and absent from the list. OTOH, I think its ready
for a final review and commit, so I'm OK if you commit or OK if you
leave it for me.

That's not the end of it. I can see a few things we could/should do in
this release, but this includes all the must-do things. Docs could do
with a little love also. So I expect work for me when I return.

Config Summary
==============

Most parameters are set on the primary. Set

 primary: synchronous_standby_names = 'node1, node2, node3'

which means that whichever of those standbys connect first will
become the main synchronous standby. Servers arriving later will
be potential standbys (standby standbys doesn't sound right...).
synchronous_standby_names can change at reload.

Currently, the standby_name is the application_name parameter
set in the primary_conninfo.

When we set this for a client, or in postgresql.conf

 primary: synchronous_replication = on

then we will wait at commit until the synchronous standby has
reached the WAL location of our commit point.

If the current synchronous standby dies then one of the other standbys
will take over. (I think it would be a great idea to make the
list a priority order, but I haven't had time to code that).

If none of the standbys are available, then we don't wait at all
if allow_standalone_primary is set.
allow_standalone_primary can change at reload.

Whatever happens, if you set sync_replication_timeout_client
then backends will give up waiting if some WALsender doesn't
wake them quickly enough.

You can generally leave these parameters at their default settings

 primary: sync_replication_timeout_client = 120s
 primary: allow_standalone_primary = on
 standby: wal_receiver_status_interval = 10s

I see the updated patch I provided last time to fix various errata and
spacing issues got lost in the last round of conversations. Maybe
it's safer to provide a patch for the patch.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Sat, 2011-02-19 at 00:32 +0000, Thom Brown wrote:

I see the updated patch I provided last time to fix various errata and
spacing issues got lost in the last round of conversations. Maybe
it's safer to provide a patch for the patch.

I'm sorry about that Thom, your changes were and are welcome. The docs
were assembled rather quickly about 2 hours ago and we'll definitely
need your care and attention to bring them to a good level of quality.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

Excerpts from Thom Brown's message of vie feb 18 21:32:17 -0300 2011:

I see the updated patch I provided last time to fix various errata and
spacing issues got lost in the last round of conversations. Maybe
it's safer to provide a patch for the patch.

interdiff?

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Feb 18, 2011 at 7:06 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

The patch is very lite touch on a few areas of code, plus a chunk of
specific code, all on master-side. Pretty straight really. I'm sure
problems will be found, its not long since I completed this; thanks to
Daniel Farina for your help with patch assembly.

This looks like it's in far better shape than the previous version.
Thanks to you and Dan for working on it.

As you have this coded, enabling synchronous_replication forces all
transactions to commit synchronously. This means that, when
synchronous_replication=on, you get synchronous_commit=on behavior
even if you have synchronous_commit=off. I'd prefer to see us go the
other way, and say that you can only get synchronous replication when
you're also getting synchronous commit.

Even if not, we should at least arrange the test so that we don't
force synchronous commit for transactions that haven't written XLOG.
That is, instead of:

((wrote_xlog && XactSyncCommit) || forceSyncCommit || nrels > 0 ||
SyncRepRequested())

...we should instead say:

((write_xlog && (XactSyncCommit || SyncRepRequested()) ||
forceSyncCommit || nrels > 0)

...but as I say I'd be inclined to instead keep the existing test, and
just skip SyncRepWaitForLSN() if we aren't committing synchronously.

I'm unsure of the value of sync_replication_timeout_client (which
incidentally is spelled replication_timeout_client in one place, and
elsewhere asynchornus -> asynchronous). I think in practice if you
start hitting that timeout, your server will slow to such a crawl that
you might as well be down. On the other hand, I see no particular
harm in leaving the option in there either, though I definitely think
the default should be changed to -1.

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

Simon Riggs wrote:

Most parameters are set on the primary. Set

primary: synchronous_standby_names = 'node1, node2, node3'

which means that whichever of those standbys connect first will
become the main synchronous standby. Servers arriving later will
be potential standbys (standby standbys doesn't sound right...).
synchronous_standby_names can change at reload.

Currently, the standby_name is the application_name parameter
set in the primary_conninfo.

When we set this for a client, or in postgresql.conf

primary: synchronous_replication = on

then we will wait at commit until the synchronous standby has
reached the WAL location of our commit point.

If the current synchronous standby dies then one of the other standbys
will take over. (I think it would be a great idea to make the
list a priority order, but I haven't had time to code that).

If none of the standbys are available, then we don't wait at all
if allow_standalone_primary is set.
allow_standalone_primary can change at reload.

Are we going to allow a command to be run when these things happen, like
archive_command does, or is that something for 9.2?

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

+ It's impossible for everything to be true. +

On Fri, 2011-02-18 at 20:45 -0500, Robert Haas wrote:

On Fri, Feb 18, 2011 at 7:06 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

The patch is very lite touch on a few areas of code, plus a chunk of
specific code, all on master-side. Pretty straight really. I'm sure
problems will be found, its not long since I completed this; thanks to
Daniel Farina for your help with patch assembly.

This looks like it's in far better shape than the previous version.
Thanks to you and Dan for working on it.

As you have this coded, enabling synchronous_replication forces all
transactions to commit synchronously. This means that, when
synchronous_replication=on, you get synchronous_commit=on behavior
even if you have synchronous_commit=off. I'd prefer to see us go the
other way, and say that you can only get synchronous replication when
you're also getting synchronous commit.

First, we should be clear to explain that you are referring to the fact
that the request
synchronous_commit = off
synchronous_replication = on
makes no sense in the way the replication system is currently designed,
even though it is a wish-list item to make it work in 9.2+

Since that combination makes no sense we need to decide how will we
react when it is requested. I think fail-safe is the best way. We should
make the default the safe way and allow performance options in
non-default paths.

Hence the above combination of options is taken in the patch to be the
same as
synchronous_commit = on
synchronous_replication = on

If you want performance, you can still get it with
synchronous_commit = off
synchronous_replication = off
which does have meaning

So the way the patch is coded makes most sense for a safety feature. I
think it does need to be documented also.

Must fly now...

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2011-02-18 at 21:39 -0500, Bruce Momjian wrote:

Simon Riggs wrote:

Most parameters are set on the primary. Set

primary: synchronous_standby_names = 'node1, node2, node3'

which means that whichever of those standbys connect first will
become the main synchronous standby. Servers arriving later will
be potential standbys (standby standbys doesn't sound right...).
synchronous_standby_names can change at reload.

Currently, the standby_name is the application_name parameter
set in the primary_conninfo.

When we set this for a client, or in postgresql.conf

primary: synchronous_replication = on

then we will wait at commit until the synchronous standby has
reached the WAL location of our commit point.

If the current synchronous standby dies then one of the other standbys
will take over. (I think it would be a great idea to make the
list a priority order, but I haven't had time to code that).

If none of the standbys are available, then we don't wait at all
if allow_standalone_primary is set.
allow_standalone_primary can change at reload.

Are we going to allow a command to be run when these things happen, like
archive_command does, or is that something for 9.2?

Not really sure which events you're speaking of, sorry. As I write, I
don't see a place or a reason to run a command, but that might change
with a longer explanation of what you're thinking. I wouldn't rule out
adding something like that in this release, but I'm not around for the
next week to add it.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

On Fri, 2011-02-18 at 20:45 -0500, Robert Haas wrote:

On the other hand, I see no particular
harm in leaving the option in there either, though I definitely think
the default should be changed to -1.

The default setting should be to *not* freeze up if you lose the
standby. That behaviour unexpectedly leads to an effective server down
situation, rather than 2 minutes of slow running. This is supposed to be
High Availability software so it will be bad for us if we allow that.

We've discussed this many times already. The people that wish to wait
forever have their wait-forever option, but lets not force it on
everyone.

--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services

Simon Riggs wrote:

On Fri, 2011-02-18 at 21:39 -0500, Bruce Momjian wrote:

Simon Riggs wrote:

Most parameters are set on the primary. Set

primary: synchronous_standby_names = 'node1, node2, node3'

which means that whichever of those standbys connect first will
become the main synchronous standby. Servers arriving later will
be potential standbys (standby standbys doesn't sound right...).
synchronous_standby_names can change at reload.

Currently, the standby_name is the application_name parameter
set in the primary_conninfo.

When we set this for a client, or in postgresql.conf

primary: synchronous_replication = on

then we will wait at commit until the synchronous standby has
reached the WAL location of our commit point.

If the current synchronous standby dies then one of the other standbys
will take over. (I think it would be a great idea to make the
list a priority order, but I haven't had time to code that).

If none of the standbys are available, then we don't wait at all
if allow_standalone_primary is set.
allow_standalone_primary can change at reload.

Are we going to allow a command to be run when these things happen, like
archive_command does, or is that something for 9.2?

Not really sure which events you're speaking of, sorry. As I write, I
don't see a place or a reason to run a command, but that might change
with a longer explanation of what you're thinking. I wouldn't rule out
adding something like that in this release, but I'm not around for the
next week to add it.

Sorry. I was thinking of allowing a command to alert an administrator
when we switch standby machines, or if we can't do synchronous standby
any longer. I assume we put a message in the logs, and the admin could
have a script that monitors that, but I thought a pager-like command
would be helpful, though I don't think we do that in any other case, so
maybe it is overkill. These are all optional ideas.

Also, I like that you are using application name here.

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

+ It's impossible for everything to be true. +

On 2/18/11 4:06 PM, Simon Riggs wrote:

v17 implements what I believe to be the final set of features for sync
rep. This one I'm actually fairly happy with. It can be enjoyed best at
DEBUG3.

Yes, but what wine do I serve with it? ;-)

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

On Sat, Feb 19, 2011 at 15:23, Bruce Momjian <bruce@momjian.us> wrote:

Sorry.  I was thinking of allowing a command to alert an administrator
when we switch standby machines, or if we can't do synchronous standby
any longer.  I assume we put a message in the logs, and the admin could
have a script that monitors that, but I thought a pager-like command
would be helpful

For asynchronous notifications we already have LISTEN/NOTIFY. Perhaps
that could be used?

Regards,
Marti

Marti Raudsepp wrote:

On Sat, Feb 19, 2011 at 15:23, Bruce Momjian <bruce@momjian.us> wrote:

Sorry. ?I was thinking of allowing a command to alert an administrator
when we switch standby machines, or if we can't do synchronous standby
any longer. ?I assume we put a message in the logs, and the admin could
have a script that monitors that, but I thought a pager-like command
would be helpful

For asynchronous notifications we already have LISTEN/NOTIFY. Perhaps
that could be used?

Well, those are going to require work to notify someone externally, like
send an email alert.

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

+ It's impossible for everything to be true. +

On Sat, Feb 19, 2011 at 6:05 PM, Bruce Momjian <bruce@momjian.us> wrote:

Marti Raudsepp wrote:

On Sat, Feb 19, 2011 at 15:23, Bruce Momjian <bruce@momjian.us> wrote:

Sorry. ?I was thinking of allowing a command to alert an administrator
when we switch standby machines, or if we can't do synchronous standby
any longer. ?I assume we put a message in the logs, and the admin could
have a script that monitors that, but I thought a pager-like command
would be helpful

For asynchronous notifications we already have LISTEN/NOTIFY. Perhaps
that could be used?

Well, those are going to require work to notify someone externally, like
send an email alert.

although, that seems the work for a monitor tool.

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

On Sat, Feb 19, 2011 at 3:28 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

First, we should be clear to explain that you are referring to the fact
that the request
 synchronous_commit = off
 synchronous_replication = on
makes no sense in the way the replication system is currently designed,
even though it is a wish-list item to make it work in 9.2+

What exactly do you mean by "make it work"? We can either (1) wait
for the local commit and the remote commit (synchronous_commit=on,
synchronous_replication=on), (2) wait for the local commit only
(synchronous_commit=on, synchronous_replication=off), or (3) wait for
neither (synchronous_commit=off, synchronous_replication=off).
There's no fourth possible behavior, AFAICS.

The question is whether synchronous_commit=off,
synchronous_replication=on should behave like (1) or (3); AFAICS
there's no fourth possible behavior. You have it as #1; I'm arguing
it should be #3. I realize it's an arguable point; I'm just arguing
for what makes most sense to me.

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

On Sat, Feb 19, 2011 at 3:35 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Fri, 2011-02-18 at 20:45 -0500, Robert Haas wrote:

On the other hand, I see no particular
harm in leaving the option in there either, though I definitely think
the default should be changed to -1.

The default setting should be to *not* freeze up if you lose the
standby. That behaviour unexpectedly leads to an effective server down
situation, rather than 2 minutes of slow running.

My understanding is that the parameter will wait on every commit, not
just once. There's no mechanism to do anything else. But I did some
testing this evening and actually it appears to not work at all. I
hit walreceiver with a SIGSTOP and the commit never completes, even
after the two minute timeout. Also, when I restarted walreceiver
after a long time, I got a server crash.

DEBUG: write 0/3027BC8 flush 0/3014690 apply 0/3014690
DEBUG: released 0 procs up to 0/3014690
DEBUG: write 0/3027BC8 flush 0/3027BC8 apply 0/3014690
DEBUG: released 2 procs up to 0/3027BC8
WARNING: could not locate ourselves on wait queue
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: DEBUG:
shmem_exit(-1): 0 callbacks to make
DEBUG: proc_exit(-1): 0 callbacks to make
FATAL: could not receive data from WAL stream: server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Failed.
!> LOG: record with zero length at 0/3027BC8
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG: received replication command: IDENTIFY_SYSTEM
DEBUG: received replication command: START_REPLICATION 0/3000000
LOG: streaming replication successfully connected to primary
DEBUG: standby "standby" is a potential synchronous standby
DEBUG: write 0/0 flush 0/0 apply 0/3027BC8
DEBUG: released 0 procs up to 0/0
DEBUG: standby "standby" has now caught up with primary
DEBUG: write 0/3027C18 flush 0/0 apply 0/3027BC8
DEBUG: standby "standby" is now the synchronous replication standby
DEBUG: released 0 procs up to 0/0
DEBUG: write 0/3027C18 flush 0/3027C18 apply 0/3027BC8
DEBUG: released 0 procs up to 0/3027C18
DEBUG: write 0/3027C18 flush 0/3027C18 apply 0/3027C18
DEBUG: released 0 procs up to 0/3027C18

(lots more copies of those last two messages)

I believe the problem is that the definition of IsOnSyncRepQueue is
bogus, so that the loop in SyncRepWaitOnQueue always takes the first
branch.

It was a little confusing to me setting this up that setting only
synchronous_replication did nothing; I had to also set
synchronous_standby_names. We might need a cross-check there. I
believe the docs for synchronous_replication also need some updating;
this part appears to be out of date:

+        between primary and standby. The commit wait will last until the
+        first reply from any standby. Multiple standby servers allow
+        increased availability and possibly increase performance as well.

The words "on the primary" in the next sentence may not be necessary
any more either, as I believe this parameter now has no effect
anywhere else.

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

On Sat, Feb 19, 2011 at 10:52 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, Feb 19, 2011 at 3:28 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

First, we should be clear to explain that you are referring to the fact
that the request
 synchronous_commit = off
 synchronous_replication = on
makes no sense in the way the replication system is currently designed,
even though it is a wish-list item to make it work in 9.2+

What exactly do you mean by "make it work"?  We can either (1) wait
for the local commit and the remote commit (synchronous_commit=on,
synchronous_replication=on), (2) wait for the local commit only
(synchronous_commit=on, synchronous_replication=off), or (3) wait for
neither (synchronous_commit=off, synchronous_replication=off).
There's no fourth possible behavior, AFAICS.

The question is whether synchronous_commit=off,
synchronous_replication=on should behave like (1) or (3); AFAICS
there's no fourth possible behavior.  You have it as #1; I'm arguing
it should be #3.  I realize it's an arguable point; I'm just arguing
for what makes most sense to me.

IMHO, we should stick to the safest option.

considering that synchronous_replication to on means that we *want*
durability, and that synchronous_commit to off means we don't *care*
about durability. Then the real question here is: in the presence of
synchronous_replication to on (which means we want durability), are we
allowed to assume we can loss data?

one way to manage that is simply disallow that combination with an
error, maybe that is a bit strict but we haven't to make assumptions;
the other option is to keep safe which means keep durability so if you
want to risk some data then you should disable synchronous_replication
as well as synchronous_commit... maybe sending a message to the log
when you detect the conflicting situation.

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

On Feb20, 2011, at 08:12 , Jaime Casanova wrote:

considering that synchronous_replication to on means that we *want*
durability, and that synchronous_commit to off means we don't *care*
about durability. Then the real question here is: in the presence of
synchronous_replication to on (which means we want durability), are we
allowed to assume we can loss data?

From the angle, shouldn't we turn synchronous_replication=on into a third
possible state of synchronous_commit?

We'd then have

synchronous_commit=off #Same as now
synchronous_commit=local #Same as synchronous_commit=on,
#synchronous_replication=off
synchronous_commit=standby #Same as synchronous_commit=on,
#synchronous_replication=on

one way to manage that is simply disallow that combination with an
error, maybe that is a bit strict but we haven't to make assumptions;
the other option is to keep safe which means keep durability so if you
want to risk some data then you should disable synchronous_replication
as well as synchronous_commit... maybe sending a message to the log
when you detect the conflicting situation.

The question is where we'd raise the error, though. Doing it on GUC
assignment makes the behaviour depend on assignment order. That's a
bad idea I believe, since it possibly breaks ALTER ROLE/DATEBASE SET ....

best regards,
Florian Pflug

On Sun, Feb 20, 2011 at 7:20 AM, Florian Pflug <fgp@phlo.org> wrote:

On Feb20, 2011, at 08:12 , Jaime Casanova wrote:

considering that synchronous_replication to on means that we *want*
durability, and that synchronous_commit to off means we don't *care*
about durability. Then the real question here is: in the presence of
synchronous_replication to on (which means we want durability), are we
allowed to assume we can loss data?

From the angle, shouldn't we turn synchronous_replication=on into a third
possible state of synchronous_commit?

We'd then have

synchronous_commit=off #Same as now
synchronous_commit=local #Same as synchronous_commit=on,
                        #synchronous_replication=off
synchronous_commit=standby #Same as synchronous_commit=on,
                          #synchronous_replication=on

that would be a little confuse and difficult to document. at least i
know that as far as we say something like this "to activate
synchronous replication set synchronous commit to standby" users
somehow will have the impression that locally the commit is
asynchronous (ok, a have had bad experiences with Ecuadorian users ;)

one way to manage that is simply disallow that combination with an
error, maybe that is a bit strict but we haven't to make assumptions;
the other option is to keep safe which means keep durability so if you
want to risk some data then you should disable synchronous_replication
as well as synchronous_commit... maybe sending a message to the log
when you detect the conflicting situation.

The question is where we'd raise the error, though. Doing it on GUC
assignment makes the behaviour depend on assignment order. That's a
bad idea I believe, since it possibly breaks ALTER ROLE/DATEBASE SET ....

well, yeah... maybe is just to much worthless work... but we can check
before commit and send a NOTICE message

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

On Feb21, 2011, at 00:09 , Jaime Casanova wrote:

On Sun, Feb 20, 2011 at 7:20 AM, Florian Pflug <fgp@phlo.org> wrote:

On Feb20, 2011, at 08:12 , Jaime Casanova wrote:

considering that synchronous_replication to on means that we *want*
durability, and that synchronous_commit to off means we don't *care*
about durability. Then the real question here is: in the presence of
synchronous_replication to on (which means we want durability), are we
allowed to assume we can loss data?

From the angle, shouldn't we turn synchronous_replication=on into a third
possible state of synchronous_commit?

We'd then have

synchronous_commit=off #Same as now
synchronous_commit=local #Same as synchronous_commit=on,
#synchronous_replication=off
synchronous_commit=standby #Same as synchronous_commit=on,
#synchronous_replication=on

that would be a little confuse and difficult to document. at least i
know that as far as we say something like this "to activate
synchronous replication set synchronous commit to standby" users
somehow will have the impression that locally the commit is
asynchronous (ok, a have had bad experiences with Ecuadorian users ;)

I figured we'd say something like

'To guarantee durability of transactions except in the fail-over case,
set synchronous_commit to "local". To additionally guarantee durability
even in the case of a fail-over to a standby node, set synchronous_commit
to "standby". This causes a COMMIT to only report success once the commit
record has be acknowledged by the current synchronous standby node, and
will therefore induce a higher latency of COMMIT requests.'

Other possible names for the "standby" options that come to mind are
"replication", "replica" or "replicate". Or maybe "cluster", but that
seems confusing since we sometimes call the collection of databases managed
by one postgres instance a cluster.

best regards,
Florian Pflug