Re: Synchronous Log Shipping Replication

Hi,

In PGCon 2008, I proposed synchronous log shipping replication.
Sorry for late posting, but I'd like to start the discussion
about its implementation from now.
http://www.pgcon.org/2008/schedule/track/Horizontal%20Scaling/76.en.html

First of all, I'm not planning to put the prototype which I demoed
in PGCon into core directly.

- Portability issues (using message queue, multi-threaded ...)
- Have too much dependency on Heartbeat

Yes, since the prototype is useful reference of implementation,
I plan to open it ASAP. But, I'm sorry - it still takes a month
to open it.

Pavan re-designed the sync replication based on the prototype
and I posted that design doc on wiki. Please check it if you
are interested in it.
http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects

This design is too huge. In order to enhance the extensibility
of postgres, I'd like to divide the sync replication into
minimum hooks and some plugins and to develop it, respectively.
Plugins for the sync replication plan to be available at the
time of 8.4 release.

In my design, WAL sending is achieved as follow by WALSender.
WALSender is a new process which I introduce.

1) On COMMIT, backend requests WALSender to send WAL.
2) WALSender reads WAL from walbuffers and send it to slave.
3) WALSender waits for the response from slave and replies
backend.

I propose two hooks for WAL sending.

WAL-writing hook
----------------
This hook is for backend to communicate with WALSender.
WAL-writing hook intercepts write system call in XLogWrite.
That is, backend requests WAL sending whenever write is called.

WAL-writing hook is available also for other uses e.g.
Software RAID (writes WAL into two files for durability).

Hook for WALSender
------------------
This hook is for introducing WALSender. There are the following
three ideas of how to introduce WALSender. A required hook
differs by which idea is adopted.

a) Use WALWriter as WALSender

This idea needs WALWriter hook which intercepts WALWriter
literally. WALWriter stops the local WAL write and focuses on
WAL sending. This idea is very simple, but I don't think of
the use of WALWriter hook other than WAL sending.

b) Use new background process as WALSender

This idea needs background-process hook which enables users
to define new background processes. I think the design of this
hook resembles that of rmgr hook proposed by Simon. I define
the table like RmgrTable. It's for registering some functions
(e.g. main function and exit...) for operating a background
process. Postmaster calls the function from the table suitably,
and manages a start and end of background process. ISTM that
there are many uses in this hook, e.g. performance monitoring
process like statspack.

c) Use one backend as WALSender

In this idea, slave calls the user-defined function which
takes charge of WAL sending via SQL e.g. "SELECT pg_walsender()".
Compared with other ideas, it's easy to implement WALSender
because postmater handles the establishment and authentication
of connection. But, this SQL causes a long transaction which
prevents vacuum. So, this idea needs idle-state hook which
executes plugin before transaction starts. I don't think of
the use of this hook other than WAL sending either.

Which idea should we adopt?

Comments welcome.

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

Hi,

Fujii Masao wrote:
> Pavan re-designed the sync replication based on the prototype
> and I posted that design doc on wiki. Please check it if you
> are interested in it.
> http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects

I've read that wiki page and allow myself to comment from a Postgres-R
developer's perspective ;-)

R1: "without ... any negative performance overhead"? For fully
synchronous replication, that's clearly not possible. I guess that
applies only for async WAL shipping.

NR3: who is supposed to do failure detection and manage automatic
failover? How does integration with such an additional tool work?

I got distracted by the SBY and ACT abbreviations. Why abbreviate
standby or active at all? It's not like we don't already have enough
three letter acronyms, but those stand for rather more complex terms
than single words.

Standby Bootstrap: "stopping the archiving at the ACT" doesn't prevent
overriding WAL files in pg_xlog. It just stops archiving a WAL file
before it gets overridden - which clearly doesn't solve the problem here.

How is communication done? "Serialization of WAL shipping" should better
not mean serialization on the network, i.e. the WAL Sender Process
should be able to await acknowledgment of multiple WAL packets in
parallel, otherwise the interconnect latency might turn into a
bottleneck. How is communication done? What happens if the link between
the active and standby goes down? Or if it's temporarily unavailable for
some time?

The IPC mechanism reminds me a lot of what I did for Postgres-R, which
also has a central "replication manager" process, which receives
changesets from multiple backends. I've implemented an internal
messaging mechanism based on shared memory and signals, using only
Postgres methods. It allows arbitrary processes to send messages to each
other by process id.

Moving the WAL Sender and WAL Receiver processes under the control of
the postmaster certainly sounds like a good thing. After all, those are
fiddling wiht Postgres internals.

> This design is too huge. In order to enhance the extensibility
> of postgres, I'd like to divide the sync replication into
> minimum hooks and some plugins and to develop it, respectively.
> Plugins for the sync replication plan to be available at the
> time of 8.4 release.

Hooks again? I bet you all know by now, that my excitement for hooks has
always been pretty narrow. ;-)

> In my design, WAL sending is achieved as follow by WALSender.
> WALSender is a new process which I introduce.
>
> 1) On COMMIT, backend requests WALSender to send WAL.
> 2) WALSender reads WAL from walbuffers and send it to slave.
> 3) WALSender waits for the response from slave and replies
> backend.
>
> I propose two hooks for WAL sending.
>
> WAL-writing hook
> ----------------
> This hook is for backend to communicate with WALSender.
> WAL-writing hook intercepts write system call in XLogWrite.
> That is, backend requests WAL sending whenever write is called.
>
> WAL-writing hook is available also for other uses e.g.
> Software RAID (writes WAL into two files for durability).
>
> Hook for WALSender
> ------------------
> This hook is for introducing WALSender. There are the following
> three ideas of how to introduce WALSender. A required hook
> differs by which idea is adopted.
>
> a) Use WALWriter as WALSender
>
> This idea needs WALWriter hook which intercepts WALWriter
> literally. WALWriter stops the local WAL write and focuses on
> WAL sending. This idea is very simple, but I don't think of
> the use of WALWriter hook other than WAL sending.
>
> b) Use new background process as WALSender
>
> This idea needs background-process hook which enables users
> to define new background processes. I think the design of this
> hook resembles that of rmgr hook proposed by Simon. I define
> the table like RmgrTable. It's for registering some functions
> (e.g. main function and exit...) for operating a background
> process. Postmaster calls the function from the table suitably,
> and manages a start and end of background process. ISTM that
> there are many uses in this hook, e.g. performance monitoring
> process like statspack.
>
> c) Use one backend as WALSender
>
> In this idea, slave calls the user-defined function which
> takes charge of WAL sending via SQL e.g. "SELECT pg_walsender()".
> Compared with other ideas, it's easy to implement WALSender
> because postmater handles the establishment and authentication
> of connection. But, this SQL causes a long transaction which
> prevents vacuum. So, this idea needs idle-state hook which
> executes plugin before transaction starts. I don't think of
> the use of this hook other than WAL sending either.

The above cited wiki page sounds like you've already decided for b).

I'm unclear on what you want hooks for. If additional processes get
integrated into Postgres, those certainly need to get integrated very
much like we integrated other auxiliary processes. I wouldn't call that
'hooking', but YMMV.

Regards

Markus Wanner

On Fri, 2008-09-05 at 23:21 +0900, Fujii Masao wrote:

> Pavan re-designed the sync replication based on the prototype
> and I posted that design doc on wiki. Please check it if you
> are interested in it.
> http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects

It's good to see the detailed design, many thanks.

I will begin looking at technical details next week.

> This design is too huge. In order to enhance the extensibility
> of postgres, I'd like to divide the sync replication into
> minimum hooks and some plugins and to develop it, respectively.
> Plugins for the sync replication plan to be available at the
> time of 8.4 release.

What is Core's commentary on this plan?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Markus Wanner wrote:
> > Hook for WALSender
> > ------------------
> > This hook is for introducing WALSender. There are the following
> > three ideas of how to introduce WALSender. A required hook
> > differs by which idea is adopted.
> >
> > a) Use WALWriter as WALSender
> >
> > This idea needs WALWriter hook which intercepts WALWriter
> > literally. WALWriter stops the local WAL write and focuses on
> > WAL sending. This idea is very simple, but I don't think of
> > the use of WALWriter hook other than WAL sending.

The problem with this approach is that you are not sending WAL to the
disk _while_ you are, in parallel, sending WAL to the slave; I think
this is useful for performance reasons in synrchonous replication.

> > b) Use new background process as WALSender
> >
> > This idea needs background-process hook which enables users
> > to define new background processes. I think the design of this
> > hook resembles that of rmgr hook proposed by Simon. I define
> > the table like RmgrTable. It's for registering some functions
> > (e.g. main function and exit...) for operating a background
> > process. Postmaster calls the function from the table suitably,
> > and manages a start and end of background process. ISTM that
> > there are many uses in this hook, e.g. performance monitoring
> > process like statspack.

I think starting/stopping a process for each WAL send is too much
overhead.

> > c) Use one backend as WALSender
> >
> > In this idea, slave calls the user-defined function which
> > takes charge of WAL sending via SQL e.g. "SELECT pg_walsender()".
> > Compared with other ideas, it's easy to implement WALSender
> > because postmater handles the establishment and authentication
> > of connection. But, this SQL causes a long transaction which
> > prevents vacuum. So, this idea needs idle-state hook which
> > executes plugin before transaction starts. I don't think of
> > the use of this hook other than WAL sending either.
>
> The above cited wiki page sounds like you've already decided for b).

I assumed that there would be a background process like bgwriter that
would be notified during a commit and send the appropriate WAL files to
the slave.

> I'm unclear on what you want hooks for. If additional processes get
> integrated into Postgres, those certainly need to get integrated very
> much like we integrated other auxiliary processes. I wouldn't call that
> 'hooking', but YMMV.

Yea, I am unclear how this is going to work using simple hooks.

It sounds like Fujii-san is basically saying they can only get the hooks
done for 8.4, not the actual solution. But, as I said above, I am
unclear how a hook solution would even work long-term; I am afraid it
would be thrown away once an integrated solution was developed.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sat, 2008-09-06 at 22:09 -0400, Bruce Momjian wrote:
> Markus Wanner wrote:
> > > Hook for WALSender
> > > ------------------
> > > This hook is for introducing WALSender. There are the following
> > > three ideas of how to introduce WALSender. A required hook
> > > differs by which idea is adopted.
> > >
> > > a) Use WALWriter as WALSender
> > >
> > > This idea needs WALWriter hook which intercepts WALWriter
> > > literally. WALWriter stops the local WAL write and focuses on
> > > WAL sending. This idea is very simple, but I don't think of
> > > the use of WALWriter hook other than WAL sending.
>
> The problem with this approach is that you are not sending WAL to the
> disk _while_ you are, in parallel, sending WAL to the slave; I think
> this is useful for performance reasons in synrchonous replication.

Agreed

> > > b) Use new background process as WALSender
> > >
> > > This idea needs background-process hook which enables users
> > > to define new background processes. I think the design of this
> > > hook resembles that of rmgr hook proposed by Simon. I define
> > > the table like RmgrTable. It's for registering some functions
> > > (e.g. main function and exit...) for operating a background
> > > process. Postmaster calls the function from the table suitably,
> > > and manages a start and end of background process. ISTM that
> > > there are many uses in this hook, e.g. performance monitoring
> > > process like statspack.
>
> I think starting/stopping a process for each WAL send is too much
> overhead.

I would agree with that, but I don't think that was being suggested was
it? See later.

> > > c) Use one backend as WALSender
> > >
> > > In this idea, slave calls the user-defined function which
> > > takes charge of WAL sending via SQL e.g. "SELECT pg_walsender()".
> > > Compared with other ideas, it's easy to implement WALSender
> > > because postmater handles the establishment and authentication
> > > of connection. But, this SQL causes a long transaction which
> > > prevents vacuum. So, this idea needs idle-state hook which
> > > executes plugin before transaction starts. I don't think of
> > > the use of this hook other than WAL sending either.
> >
> > The above cited wiki page sounds like you've already decided for b).
>
> I assumed that there would be a background process like bgwriter that
> would be notified during a commit and send the appropriate WAL files to
> the slave.

ISTM that this last paragraph is actually what was meant by option b).

I think it would work the other way around though, the WALSender would
send continuously and backends may choose to wait for it to reach a
certain LSN, or not. WALWriter really should work this way too.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2008-09-05 at 23:21 +0900, Fujii Masao wrote:

> b) Use new background process as WALSender
>
> This idea needs background-process hook which enables users
> to define new background processes. I think the design of this
> hook resembles that of rmgr hook proposed by Simon. I define
> the table like RmgrTable. It's for registering some functions
> (e.g. main function and exit...) for operating a background
> process. Postmaster calls the function from the table suitably,
> and manages a start and end of background process. ISTM that
> there are many uses in this hook, e.g. performance monitoring
> process like statspack.

Sorry, but the comparison with the rmgr hook is mistaken. The rmgr hook
exists only within the Startup process and I go to some lengths to
ensure it is never called in normal backends. So it has got absolutely
nothing to do with generating WAL messages (existing/new/modified) or
sending them since it doesn't even exist during normal processing.

The intention of the rmgr hook is to allow WAL messages to be
manipulated in new ways in recovery mode. It isn't a sufficient change
to implement replication, and the functionality is orthogonal to
streaming WAL replication.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Sat, 2008-09-06 at 22:09 -0400, Bruce Momjian wrote:

> > I'm unclear on what you want hooks for. If additional processes get
> > integrated into Postgres, those certainly need to get integrated very
> > much like we integrated other auxiliary processes. I wouldn't call that
> > 'hooking', but YMMV.
>
> Yea, I am unclear how this is going to work using simple hooks.
>
> It sounds like Fujii-san is basically saying they can only get the hooks
> done for 8.4, not the actual solution. But, as I said above, I am
> unclear how a hook solution would even work long-term; I am afraid it
> would be thrown away once an integrated solution was developed.

It will be interesting to have various hooks in streaming WAL code to
implement various additional features for enterprise integration.

But that doesn't mean I support hooks in every/all places.

For me, the proposed hook amounts to "we've only got time to implement
2/3 of the required features, so we'd like to circumvent the release
cycle by putting in a hook and providing the code later". For me, hooks
are for adding additional features, not for making up for the lack of
completed code. It's kinda hard to say "we now have WAL streaming"
without the streaming bit. We need either a fully working WAL streaming
feature, or we wait until next release.

We probably need to ask if there is anybody willing to complete the
middle part of this feature so we can get it into 8.4. It would be
sensible to share the code we have now, so we can see what remains to be
implemented. I just committed to delivering Hot Standby for 8.4, so I
can't now get involved to deliver this code.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> > > b) Use new background process as WALSender
> > >
> > > This idea needs background-process hook which enables users
> > > to define new background processes

> I think starting/stopping a process for each WAL send is too much
> overhead.

Yes, of course slow. But I guess it is the only way to share one socket
in all backends. Postgres is not a multi-threaded architecture,
so each backend should use dedicated connections to send WAL buffers.
300 backends require 300 connections for each slave... it's not good at all.

> It sounds like Fujii-san is basically saying they can only get the hooks
> done for 8.4, not the actual solution.

No! He has an actual solution in his prototype ;-)
It is very similar to b) and the overhead was not so bad.
It's not so clean to be a part of postgres, though.

Are there any better idea to share one socket connection between
backends (and bgwriter)? The connections could be established after
fork() from postmaster, and number of them could be two or more.
This is one of the most complicated part of synchronous log shipping.
Switching-processes apporach like b) is just one idea for it.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Hi,

ITAGAKI Takahiro wrote:
> Are there any better idea to share one socket connection between
> backends (and bgwriter)? The connections could be established after
> fork() from postmaster, and number of them could be two or more.
> This is one of the most complicated part of synchronous log shipping.
> Switching-processes apporach like b) is just one idea for it.

I fear I'm repeating myself, but I've had the same problem for
Postgres-R and solved it with an internal message passing infrastructure
which I've simply called imessages. It requires only standard Postgres
shared memory, signals and locking and should thus be pretty portable.

In simple benchmarks, it's not quite as efficient as unix pipes, but
doesn't require as many file descriptors, is independent of the
parent-child relations of processes, maintains message borders and it is
more portable (I hope). It could certainly be improved WRT efficiency
and could theoretically even beat Unix pipes, because it involves less
copying of data and less syscalls.

It has not been reviewed nor commented much. I'd still appreciate that.

Regards

Markus Wanner

Markus Wanner <markus(at)bluegap(dot)ch> wrote:

> ITAGAKI Takahiro wrote:
> > Are there any better idea to share one socket connection between
> > backends (and bgwriter)?
>
> I fear I'm repeating myself, but I've had the same problem for
> Postgres-R and solved it with an internal message passing infrastructure
> which I've simply called imessages. It requires only standard Postgres
> shared memory, signals and locking and should thus be pretty portable.

Imessage serves as a useful reference, but it is one of the detail parts
of the issue. I can break down the issue into three parts:

1. Is process-switching approach the best way to share one socket?
Both Postgres-R and the log-shipping prototype use the approach now.
Can I think there is no objection here?

2. If 1 is reasonable, how should we add a new WAL sender process?
Just add a new process using a core-patch?
Merge into WAL writer?
Consider framework to add any of user-defined auxiliary process?

3. If 1 is reasonable, what should we use for the process-switching
primitive?
Postgres-R uses signals and locking and the log-shipping prototype
uses multi-threads and POSIX message queues now.

Signals and locking is possible choice for 3, but I want to use better
approach if any. Faster is always better.

I guess we could invent a new semaphore-like primitive at the same layer
as LWLocks using spinlock and PGPROC directly...

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Hi,

ITAGAKI Takahiro wrote:
> 1. Is process-switching approach the best way to share one socket?
> Both Postgres-R and the log-shipping prototype use the approach now.
> Can I think there is no objection here?

I don't see any appealing alternative. The postmaster certainly
shouldn't need to worry with any such socket for replication. Threading
falls pretty flat for Postgres. So the socket must be held by one of the
child processes of the Postmaster.

> 2. If 1 is reasonable, how should we add a new WAL sender process?
> Just add a new process using a core-patch?

Seems feasible to me, yes.

> Merge into WAL writer?

Uh.. that would mean you'd loose parallelism between WAL writing to disk
and WAL shipping via network. That does not sound appealing to me.

> Consider framework to add any of user-defined auxiliary process?

What for? What do you miss in the existing framework?

> 3. If 1 is reasonable, what should we use for the process-switching
> primitive?
> Postgres-R uses signals and locking and the log-shipping prototype
> uses multi-threads and POSIX message queues now.

AFAIK message queues are problematic WRT portability. At least Postgres
doesn't currently use them and introducing dependencies on those might
lead to problems, but I'm not sure. Others certainly know more about
issues involved.

A multi-threaded approach is certainly out of bounds, at least within
the Postgres core code.

> Signals and locking is possible choice for 3, but I want to use better
> approach if any. Faster is always better.

I think the approach can reach better throughput than POSIX message
queues or unix pipes, because of the mentioned savings in copying around
between system and application memory. However, that hasn't been proved,
yet.

> I guess we could invent a new semaphore-like primitive at the same layer
> as LWLocks using spinlock and PGPROC directly...

Sure, but in what way would that differ from what I do with imessages?

Regards

Markus Wanner

On Mon, Sep 8, 2008 at 8:44 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
>> Merge into WAL writer?
>
> Uh.. that would mean you'd loose parallelism between WAL writing to disk and
> WAL shipping via network. That does not sound appealing to me.

That depends on the order of WAL writing and WAL shipping.
How about the following order?

1. A backend writes WAL to disk.
2. The backend wakes up WAL sender process and sleeps.
3. WAL sender process does WAL shipping and wakes up the backend.
4. The backend issues sync command.

>> I guess we could invent a new semaphore-like primitive at the same layer
>> as LWLocks using spinlock and PGPROC directly...
>
> Sure, but in what way would that differ from what I do with imessages?

Performance ;)

The timing of the process's receiving a signal is dependent on the scheduler
of kernel. The scheduler does not always handle a signal immediately.

Regards

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

Hi,

Fujii Masao wrote:
> 1. A backend writes WAL to disk.
> 2. The backend wakes up WAL sender process and sleeps.
> 3. WAL sender process does WAL shipping and wakes up the backend.
> 4. The backend issues sync command.

Right, that would work. But still, the WAL writer process would block
during writing WAL blocks.

Are there compelling reasons for using the existing WAL writer process,
as opposed to introducing a new process?

> The timing of the process's receiving a signal is dependent on the scheduler
> of kernel.

Sure, so are pipes or shmem queues.

> The scheduler does not always handle a signal immediately.

What exactly are you proposing to use instead of signals? Semaphores are
pretty inconvenient when trying to wake up arbitrary processes or in
conjunction with listening on sockets via select(), for example.

See src/backend/replication/manager.c from Postgres-R for a working
implementation of such a process using select() and signaling.

Regards

Markus Wanner

On Mon, 2008-09-08 at 19:19 +0900, ITAGAKI Takahiro wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> > > > b) Use new background process as WALSender
> > > >
> > > > This idea needs background-process hook which enables users
> > > > to define new background processes
>
> > I think starting/stopping a process for each WAL send is too much
> > overhead.
>
> Yes, of course slow. But I guess it is the only way to share one socket
> in all backends. Postgres is not a multi-threaded architecture,
> so each backend should use dedicated connections to send WAL buffers.
> 300 backends require 300 connections for each slave... it's not good at all.

So... don't have individual backends do the sending. Have them wait
while somebody else does it for them.

> > It sounds like Fujii-san is basically saying they can only get the hooks
> > done for 8.4, not the actual solution.
>
> No! He has an actual solution in his prototype ;-)

The usual thing if you have a WIP patch you're not sure of is to post
the patch for feedback.

If you guys aren't going to post any code to the project then I'm not
clear why it's being discussed here. Is this a community project or a
private project?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Fujii Masao wrote:
> On Mon, Sep 8, 2008 at 8:44 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> >> Merge into WAL writer?
> >
> > Uh.. that would mean you'd loose parallelism between WAL writing to disk and
> > WAL shipping via network. That does not sound appealing to me.
>
> That depends on the order of WAL writing and WAL shipping.
> How about the following order?
>
> 1. A backend writes WAL to disk.
> 2. The backend wakes up WAL sender process and sleeps.
> 3. WAL sender process does WAL shipping and wakes up the backend.
> 4. The backend issues sync command.

I am confused why this is considered so complicated. Having individual
backends doing the wal transfer to the slave is never going to work
well.

I figured we would have a single WAL streamer that continues advancing
forward in the WAL file, streaming to the standby. Backends would
update a shared memory variable specifying how far they want the wal
streamer to advance and send a signal to the wal streamer if necessary.
Backends would monitor another shared memory variable that specifies how
far the wal streamer has advanced.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Hi,

Bruce Momjian wrote:
> Backends would
> update a shared memory variable specifying how far they want the wal
> streamer to advance and send a signal to the wal streamer if necessary.
> Backends would monitor another shared memory variable that specifies how
> far the wal streamer has advanced.

That sounds like WAL needs to be written to disk, before it can be sent
to the standby. Except maybe with some sort of mmap'ing the WAL.

Regards

Markus Wanner

Markus Wanner wrote:
> Hi,
>
> Bruce Momjian wrote:
> > Backends would
> > update a shared memory variable specifying how far they want the wal
> > streamer to advance and send a signal to the wal streamer if necessary.
> > Backends would monitor another shared memory variable that specifies how
> > far the wal streamer has advanced.
>
> That sounds like WAL needs to be written to disk, before it can be sent
> to the standby. Except maybe with some sort of mmap'ing the WAL.

Well, WAL is either on disk or in the wal_buffers in shared memory ---
in either case, a WAL streamer can get to it.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Hi,

I looked some comment for the synchronous replication and understood
the consensus of the community was that the sync replication should be
added using not hooks and plug-ins but core-patches. If my understanding
is right, I will change my development plan so that the sync replication
may be put into core.

But, I don't think every features should be put into core. Of course, the
high-availability features (like clustering, automatic failover, ...etc) are
out of postgres. The user who wants whole HA solution using the sync
replication must integrate postgres and clustering software like heartbeat.

WAL sending should be put into core. But, I'd like to separate WAL
receiving from core and provide it as a new contrib tool. Because,
there are some users who use the sync replication as only WAL
streaming. They don't want to start postgres on the slave. Of course,
the slave can replay WAL by using pg_standby and WAL receiver tool
which I'd like to provide as a new contrib tool. I think the patch against
recovery code is not necessary.

I arrange the development items below :

1) Patch around XLogWrite.
It enables a backend to wake up the WAL sender process at the
timing of COMMIT.

2) Patch for the communication between a backend and WAL
sender process.
There were some discussions about this topic. Now, I decided to
adopt imessages proposed by Markus.

3) Patch of introducing new background process which I've called
WALSender. It takes charge of sending WAL to the slave.

Now, I assume that WALSender also listens the connection from
the slave, i.e. only one sender process manages multiple slaves.
The relation between WALSender and backend is 1:1. So,
the communication mechanism between them can be simple.
As other idea, I can introduce new listener process and fork new
WALSender for every slave. Which architecture is better? Or,
should postmaster listen also the connection from the slave?

4) New contrib tool which I've called WALReceiver. It takes charge
of receiving WAL from the master and writing it to disk on the slave.

I will submit these patches and tool by Nov Commit Fest at the latest.

Any comment welcome!

best regards

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

"Fujii Masao" <masao(dot)fujii(at)gmail(dot)com> wrote:

> 3) Patch of introducing new background process which I've called
> WALSender. It takes charge of sending WAL to the slave.
>
> Now, I assume that WALSender also listens the connection from
> the slave, i.e. only one sender process manages multiple slaves.

> The relation between WALSender and backend is 1:1. So,
> the communication mechanism between them can be simple.

I assume that he says only one backend communicates with WAL sender
at a time. The communication is done during WALWriteLock is held,
so other backends wait for the communicating backend on WALWriteLock.
WAL sender only needs to send one signal for each time it sends WAL
buffers to slave.

We could be split the LWLock to WALWriterLock and WALSenderLock,
but the essential point is same.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Mon, 2008-09-08 at 17:40 -0400, Bruce Momjian wrote:
> Fujii Masao wrote:
> > On Mon, Sep 8, 2008 at 8:44 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> > >> Merge into WAL writer?
> > >
> > > Uh.. that would mean you'd loose parallelism between WAL writing to disk and
> > > WAL shipping via network. That does not sound appealing to me.
> >
> > That depends on the order of WAL writing and WAL shipping.
> > How about the following order?
> >
> > 1. A backend writes WAL to disk.
> > 2. The backend wakes up WAL sender process and sleeps.
> > 3. WAL sender process does WAL shipping and wakes up the backend.
> > 4. The backend issues sync command.
>
> I am confused why this is considered so complicated. Having individual
> backends doing the wal transfer to the slave is never going to work
> well.

Agreed.

> I figured we would have a single WAL streamer that continues advancing
> forward in the WAL file, streaming to the standby. Backends would
> update a shared memory variable specifying how far they want the wal
> streamer to advance and send a signal to the wal streamer if necessary.
> Backends would monitor another shared memory variable that specifies how
> far the wal streamer has advanced.

Yes. We should have a LogwrtRqst pointer and LogwrtResult pointer for
the send operation. The Write and Send operations can then continue
independently of one another. XLogInsert() cannot advance to a new page
while we are waiting to send or write. Notice that the Send process
might be the bottleneck - that is the price of synchronous replication.

Backends then wait
* not at all for asynch commit
* just for Write for local synch commit
* for both Write and Send for remote synch commit
(various additional options for what happens to confirm Send)

So normal backends neither write nor send. We have two dedicated
processes, one for write, one for send. We need to put an extra test
into WALWriter loop so that it will continue immediately (with no wait)
if there is an outstanding request for synchronous operation.

This gives us the Group Commit feature also, even if we are not using
replication. So we can drop the commit_delay stuff.

XLogBackgroundFlush() processes data page at a time if it can. That may
not be the correct batch size for XLogBackgroundSend(), so we may need a
tunable for the MTU. Under heavy load we need the Write and Send to act
in a way to maximise throughput rather than minimise response time, as
we do now.

If wal_buffers overflows, we continue to hold WALInsertLock while we
wait for WALWriter and WALSender to complete.

We should increase default wal_buffers to 64.

After (or during) XLogInsert backends will sleep in a proc queue,
similar to LWlocks and protected by a spinlock. When preparing to
write/send the WAL process should read the proc at the *tail* of the
queue to see what the next LogwrtRqst should be. Then it performs its
action and wakes procs up starting with the head of the queue. We would
add LSN into PGPROC, so WAL processes can check whether the backend
should be woken. The LSN field can be accessed without spinlocks since
it is only ever set by the backend itself and only read while a backend
is sleeping. So we access spinlock, find tail, drop spinlock then read
LSN of the backend that (was) the tail.

Another thought occurs that we might measure the time a Send takes and
specify a limit on how long we are prepared to wait for confirmation.
Limit=0 => asynchronous. Limit > 0 implies synchronous-up-to-the-limit.
This would give better user behaviour across a highly variable network
connection.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> This gives us the Group Commit feature also, even if we are not using
> replication. So we can drop the commit_delay stuff.

Huh? How does that give us group commit?

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

On Tue, 2008-09-09 at 12:24 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > This gives us the Group Commit feature also, even if we are not using
> > replication. So we can drop the commit_delay stuff.
>
> Huh? How does that give us group commit?

Multiple backends waiting while we perform a write. Commits then happen
as a group (to WAL at least), hence Group Commit.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> Multiple backends waiting while we perform a write. Commits then happen
> as a group (to WAL at least), hence Group Commit.

The problem with our current commit protocol is this:

1. Backend A inserts commit record A
2. Backend A starts to flush commit record A
3. Backend B inserts commit record B
4. Backend B waits until 2. finishes
5. Backend B starts to flush commit record B

Note that we already have the logic to flush all pending commit records
at once. If there's also backend C that insert their commit records
after step 2, B and C will be flushed at once:

1. Backend A inserts commit record A
2. Backend A starts to flush commit record A
3. Backend B inserts commit record B
4. Backend B waits until 2. finishes
5. Backend C inserts commit record C
6. Backend C waits until 2. finishes
5. Flush A finishes. Backend B starts to flush commit records A+B

The idea of group commit is to insert a small delay in backend A between
steps 1 and 2, so that we can flush both commit records in one fsync:

1. Backend A inserts commit record A
2. Backend A waits
3. Backend B inserts commit record B
3. Backend B starts to flush commit record A + B

The tricky part is, how does A know if it should wait, and for how long?
commit_delay sure isn't ideal, but AFAICS the log shipping proposal
doesn't provide any solution to that.

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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:

> The tricky part is, how does A know if it should wait, and for how long?
> commit_delay sure isn't ideal, but AFAICS the log shipping proposal
> doesn't provide any solution to that.

They have no relation each other directly,
but they need similar synchronization modules.

In log shipping, backends need to wait for WAL Sender's job,
and should wake up as fast as possible after the job is done.
It is similar to requirement of the group commit.

Signals and locking, borrewed from Postgres-R, are now studied
for the purpose in the log shipping, but I'm not sure it can be
also used in the group commit.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Tue, Sep 9, 2008 at 5:11 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> Yes. We should have a LogwrtRqst pointer and LogwrtResult pointer for
> the send operation. The Write and Send operations can then continue
> independently of one another. XLogInsert() cannot advance to a new page
> while we are waiting to send or write.

Agreed.

For realizing various synchronous options, the Write and Send operations should
be treated separately. So, I'll introduce XLogCtlSend structure which is shared
state data for WAL sending. XLogCtlInsert might need new field LogsndResult
which indicates a byte position that we have already sended. As you say,
AdvanceXLInsertBuffer() must check both position that we have already written
(fsynced) and sended. I'm doing the detail design of this now :)

> Notice that the Send process
> might be the bottleneck - that is the price of synchronous replication.

Really? In the benchmark result of my prototype, the bottleneck is
still disk I/O.
The communication (between the master and the slave) latency is smaller than
WAL writing (fsyncing) one. Of course, I assume that we use not-poor network
like 1000BASE-T.

What makes the sender process bottleneck?

> Backends then wait
> * not at all for asynch commit
> * just for Write for local synch commit
> * for both Write and Send for remote synch commit
> (various additional options for what happens to confirm Send)

I'd like to introduce new parameter "synchronous_replication" which specifies
whether backends waits for the response from WAL sender process. By
combining synchronous_commit and synchronous_replication, users can
choose various options.

> After (or during) XLogInsert backends will sleep in a proc queue,
> similar to LWlocks and protected by a spinlock. When preparing to
> write/send the WAL process should read the proc at the *tail* of the
> queue to see what the next LogwrtRqst should be. Then it performs its
> action and wakes procs up starting with the head of the queue. We would
> add LSN into PGPROC, so WAL processes can check whether the backend
> should be woken. The LSN field can be accessed without spinlocks since
> it is only ever set by the backend itself and only read while a backend
> is sleeping. So we access spinlock, find tail, drop spinlock then read
> LSN of the backend that (was) the tail.

You mean only XLogInsert treating "commit record" or every XLogInsert?
Anyway, ISTM that the response time get worse :(

> Another thought occurs that we might measure the time a Send takes and
> specify a limit on how long we are prepared to wait for confirmation.
> Limit=0 => asynchronous. Limit > 0 implies synchronous-up-to-the-limit.
> This would give better user behaviour across a highly variable network
> connection.

In the viewpoint of detection of a network failure, this feature is necessary.
When the network goes down, WAL sender can be blocked until it detects
the network failure, i.e. WAL sender keeps waiting for the response which
never comes. A timeout notification is necessary in order to detect a
network failure soon.

regards

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

Hi,

ITAGAKI Takahiro wrote:
> Signals and locking, borrewed from Postgres-R, are now studied
> for the purpose in the log shipping, but I'm not sure it can be
> also used in the group commit.

Yeah. As Heikki points out, there is a completely orthogonal question
WRT group commit: how does transaction A know if or how long it should
wait for other transactions to file their WAL?

If we decide to do all of the WAL writing from a separate WAL writer
process and let the backends communicate with it, then imessages might
help again. But I currently don't think that's feasible.

Apart from possibly having similar IPC requirements, group commit and
log shipping have not much in common and should be considered separate
features.

Regards

Markus Wanner

Fujii Masao wrote:
> What makes the sender process bottleneck?

The keyword here is "might". There's many possibilities, like:
- Slow network.
- Ridiculously fast disk. Like a RAM disk. If you have a synchronous
slave you can fail over to, putting WAL on a RAM disk isn't that crazy.
- slower WAL disk on the slave.
etc.

>> Backends then wait
>> * not at all for asynch commit
>> * just for Write for local synch commit
>> * for both Write and Send for remote synch commit
>> (various additional options for what happens to confirm Send)
>
> I'd like to introduce new parameter "synchronous_replication" which specifies
> whether backends waits for the response from WAL sender process. By
> combining synchronous_commit and synchronous_replication, users can
> choose various options.

There's one thing I haven't figured out in this discussion. Does the
write to the disk happen before or after the write to the slave? Can you
guarantee that if a transaction is committed in the master, it's also
committed in the slave, or vice versa?

>> Another thought occurs that we might measure the time a Send takes and
>> specify a limit on how long we are prepared to wait for confirmation.
>> Limit=0 => asynchronous. Limit > 0 implies synchronous-up-to-the-limit.
>> This would give better user behaviour across a highly variable network
>> connection.
>
> In the viewpoint of detection of a network failure, this feature is necessary.
> When the network goes down, WAL sender can be blocked until it detects
> the network failure, i.e. WAL sender keeps waiting for the response which
> never comes. A timeout notification is necessary in order to detect a
> network failure soon.

Agreed. But what happens if you hit that timeout? Should we enforce that
timeout within the server, or should we leave that to the external
heartbeat system?

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

Hi,

Fujii Masao wrote:
> Really? In the benchmark result of my prototype, the bottleneck is
> still disk I/O.
> The communication (between the master and the slave) latency is smaller than
> WAL writing (fsyncing) one. Of course, I assume that we use not-poor network
> like 1000BASE-T.

Sure. If you do WAL sending to standby and WAL writing to disk in
parallel, only the slower one is relevant (in case you want to wait for
both). If that happens to be the disk, you won't see any performance
degradation compared to standalone operation.

If you want the standby to confirm having written (and flushed) the WAL
to disk as well, that can't possibly be faster than the active node's
local disk (assuming equally fast and busy disk subsystems).

> I'd like to introduce new parameter "synchronous_replication" which specifies
> whether backends waits for the response from WAL sender process. By
> combining synchronous_commit and synchronous_replication, users can
> choose various options.

Various config options have already been proposed. I personally don't
think that helps us much. Instead, I'd prefer to see prototype code or
at least concepts. We can juggle with the GUC variable names or other
config options later on.

> In the viewpoint of detection of a network failure, this feature is necessary.
> When the network goes down, WAL sender can be blocked until it detects
> the network failure, i.e. WAL sender keeps waiting for the response which
> never comes. A timeout notification is necessary in order to detect a
> network failure soon.

That's one of the areas I'm missing from the overall concept. I'm glad
it comes up. You certainly realize, that such a timeout must be set high
enough so as not to trigger "false negatives" every now and then? Or do
you expect some sort of retry loop in case the link to the standby comes
up again? How about multiple standby servers?

Regards

Markus Wanner

On Tue, 2008-09-09 at 20:12 +0900, Fujii Masao wrote:

> What makes the sender process bottleneck?

In my experience, the Atlantic. But I guess the Pacific does it too. :-)

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Tue, 2008-09-09 at 12:54 +0300, Heikki Linnakangas wrote:
> Note that we already have the logic to flush all pending commit
> records at once.

But only when you can grab WALInsertLock when flushing. If you look at
the way I suggested, it does not rely upon that lock being available.

So it is both responsive in low write rate conditions and yet efficient
in high write rate conditions and does not require we specify a wait
time.

IMHO the idea of a wait time is a confusion that comes from using a
simple example (with respect). If we imagine the example slightly
differently you'll see a different answer:

High write rate: A stream of commits come so fast that by the time a
write completes there are always > 1 backends waiting to commit again.
In that case, there is never any need to wait because the arrival
pattern requries us to issues writes as quickly as we can.

Medium write rate: Commits occur relatively frequently, so that the mean
commits/flush is in the range 0.5 - 1. In this case, we can get better
I/O efficiency by introducing waits. But note that a wait is risky, and
at some point we may wait without another commit arriving. In this case,
if the disk can keep up with the write rate, why would we want to
improve I/O efficiency? There's no a priori way of calculating a useful
wait time, so waiting is always a risk. Why would we risk damage to our
response times when the disk can keep up with write rate?

So for me, introducing a wait is something you might want to consider in
medium rate conditions. Anything more or less than that and a wait is
useless. So optimising for the case where the arrival rate is within a
certain fairly tight range seems not worthwhile.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Tue, 2008-09-09 at 20:12 +0900, Fujii Masao wrote:

> I'd like to introduce new parameter "synchronous_replication" which specifies
> whether backends waits for the response from WAL sender process. By
> combining synchronous_commit and synchronous_replication, users can
> choose various options.

We already discussed that on -hackers. See "Transaction Controlled
Robustness". But yes, something like that.

Please note the design mentions fsyncing after applying WAL. I'm sure
you're aware we don't fsync after *applying* WAL now, and I hope we
never do. You might want to fsync data to WAL files on the standby, but
that is a slightly different thing.

> > After (or during) XLogInsert backends will sleep in a proc queue,
> > similar to LWlocks and protected by a spinlock. When preparing to
> > write/send the WAL process should read the proc at the *tail* of the
> > queue to see what the next LogwrtRqst should be. Then it performs its
> > action and wakes procs up starting with the head of the queue. We would
> > add LSN into PGPROC, so WAL processes can check whether the backend
> > should be woken. The LSN field can be accessed without spinlocks since
> > it is only ever set by the backend itself and only read while a backend
> > is sleeping. So we access spinlock, find tail, drop spinlock then read
> > LSN of the backend that (was) the tail.
>
> You mean only XLogInsert treating "commit record" or every XLogInsert?

Just the commit records, when synchronous_commit = on.

> Anyway, ISTM that the response time get worse :(

No, because it would have had to wait in the queue for the WALWriteLock
while prior writes occur.

If the WALWriter sleeps on a semaphore, it too can be nudged into action
at the appropriate time, so no need for a delay between backend
beginning to wait and WALWriter beginning to act. (Well, IPC delay
between two processes, so some, but that is balanced against efficiency
of Send).

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Tue, 2008-09-09 at 13:42 +0200, Markus Wanner wrote:

> How about multiple standby servers?

There are various ways for getting things to work with multiple servers.
I hope we can make this work with just a single standby before we try to
make it work on more. There are various options for synchronous and
asynchronous relay that will burden us if we try to consider all of that
in the remaining 7 weeks we have. So yes please, just not yet.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

"Fujii Masao" <masao(dot)fujii(at)gmail(dot)com> writes:
> On Tue, Sep 9, 2008 at 5:11 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>
>> Yes. We should have a LogwrtRqst pointer and LogwrtResult pointer for
>> the send operation. The Write and Send operations can then continue
>> independently of one another. XLogInsert() cannot advance to a new page
>> while we are waiting to send or write.

> Agreed.

"Agreed"? That last restriction is a deal-breaker.

regards, tom lane

On Tue, 2008-09-09 at 08:24 -0400, Tom Lane wrote:
> "Fujii Masao" <masao(dot)fujii(at)gmail(dot)com> writes:
> > On Tue, Sep 9, 2008 at 5:11 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >>
> >> Yes. We should have a LogwrtRqst pointer and LogwrtResult pointer for
> >> the send operation. The Write and Send operations can then continue
> >> independently of one another. XLogInsert() cannot advance to a new page
> >> while we are waiting to send or write.
>
> > Agreed.
>
> "Agreed"? That last restriction is a deal-breaker.

OK, I should have said *if wal_buffers are full* XLogInsert() cannot
advance to a new page while we are waiting to send or write. So I don't
think its a deal breaker.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Tue, 2008-09-09 at 08:24 -0400, Tom Lane wrote:
>> "Agreed"? That last restriction is a deal-breaker.

> OK, I should have said *if wal_buffers are full* XLogInsert() cannot
> advance to a new page while we are waiting to send or write. So I don't
> think its a deal breaker.

Oh, OK, that's obvious --- there's no place to put more data.

regards, tom lane

Hi,

Le mardi 09 septembre 2008, Heikki Linnakangas a écrit :
> The tricky part is, how does A know if it should wait, and for how long?
> commit_delay sure isn't ideal, but AFAICS the log shipping proposal
> doesn't provide any solution to that.

It might just be I'm not understanding what it's all about, but it seems to me
with WALSender process A will wait, whatever happens, either until the WAL is
sent to slave or written to disk on the slave.

I naively read Simon's proposition to consider GroupCommit done with this new
feature. A is already waiting (for some external event to complete), why
can't we use this for including some other transactions commits into the
local deal?

Regards,
--
dim

Hi,

Dimitri Fontaine wrote:
> It might just be I'm not understanding what it's all about, but it seems to me
> with WALSender process A will wait, whatever happens, either until the WAL is
> sent to slave or written to disk on the slave.

..and it will still has to wait until WAL is written to disk on the
local node, as we do now. These are two different things to wait for.
One is a network socket operation, the other is an fsync(). As these
don't work together too well (blocking), you better run that in two
different processes.

Regards

Markus Wanner

Le mardi 09 septembre 2008, Markus Wanner a écrit :
> ..and it will still has to wait until WAL is written to disk on the
> local node, as we do now. These are two different things to wait for.
> One is a network socket operation, the other is an fsync(). As these
> don't work together too well (blocking), you better run that in two
> different processes.

Exactly the point. The process is now already waiting in all cases, so maybe
we could just force waiting some WALSender signal before sending the fsync()
order, so we now have Group Commit.
I'm not sure this is a good idea at all, it's just the way I understand how
adding WALSender process in the mix could give Group Commit feature for free.

Regards,
--
dim

On Tue, 2008-09-09 at 15:32 +0200, Dimitri Fontaine wrote:
> The process is now already waiting in all cases

If the WALWriter|Sender is available, it can begin the task immediately.
There is no need for it to wait if you want synchronous behaviour.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Hi,

ITAGAKI Takahiro wrote:
> Signals and locking, borrewed from Postgres-R, are now studied
> for the purpose in the log shipping,

Cool. Let me know if you have any questions WRT this imessages stuff.

Regards

Markus Wanner

Hi,

Dimitri Fontaine wrote:
> Exactly the point. The process is now already waiting in all cases, so maybe
> we could just force waiting some WALSender signal before sending the fsync()
> order, so we now have Group Commit.

A single process can only wait on either fsync() or on select(), but not
on both concurrently, because both syscalls are blocking. So mixing
these into a single process is an inherently bad idea due to lack of
parallelism.

I fail to see how log shipping would ease or have any other impact on a
Group Commit feature, which should clearly also work for stand alone
servers, i.e. where there is no WAL sender process.

Regards

Markus Wanner

Le mardi 09 septembre 2008, Simon Riggs a écrit :
> If the WALWriter|Sender is available, it can begin the task immediately.
> There is no need for it to wait if you want synchronous behaviour.

Ok. Now I'm as lost as anyone with respect to how you get Group Commit :)
--
dim

On Tue, 2008-09-09 at 16:05 +0200, Dimitri Fontaine wrote:
> Le mardi 09 septembre 2008, Simon Riggs a écrit :
> > If the WALWriter|Sender is available, it can begin the task immediately.
> > There is no need for it to wait if you want synchronous behaviour.
>
> Ok. Now I'm as lost as anyone with respect to how you get Group Commit :)

OK, sorry. Pls read my reply to Heikki on different subthread of this
topic, he had same question of me.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On Tue, 2008-09-09 at 08:24 -0400, Tom Lane wrote:
>>> "Agreed"? That last restriction is a deal-breaker.
>
>> OK, I should have said *if wal_buffers are full* XLogInsert() cannot
>> advance to a new page while we are waiting to send or write. So I don't
>> think its a deal breaker.
>
> Oh, OK, that's obvious --- there's no place to put more data.

Each WAL sender can keep at most one page locked at a time, right? So,
that should never happen if wal_buffers > 1 + n_wal_senders.

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

On Tue, 2008-09-09 at 17:17 +0300, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> >> On Tue, 2008-09-09 at 08:24 -0400, Tom Lane wrote:
> >>> "Agreed"? That last restriction is a deal-breaker.
> >
> >> OK, I should have said *if wal_buffers are full* XLogInsert() cannot
> >> advance to a new page while we are waiting to send or write. So I don't
> >> think its a deal breaker.
> >
> > Oh, OK, that's obvious --- there's no place to put more data.
>
> Each WAL sender can keep at most one page locked at a time, right? So,
> that should never happen if wal_buffers > 1 + n_wal_senders.

Don't understand. I am referring to the logic at the top of
AdvanceXLInsertBuffer(). We would need to wait for all people reading
the contents of wal_buffers.

Currently, there is no page locking on the WAL buffers, though I have
suggested some for increasing XLogInsert() performance.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> Don't understand. I am referring to the logic at the top of
> AdvanceXLInsertBuffer(). We would need to wait for all people reading
> the contents of wal_buffers.

Oh, I see.

If a slave falls behind, how does it catch up? I guess you're saying
that it can't fall behind, because the master will block before that
happens. Also in asynchronous replication? And what about when the slave
is first set up, and needs to catch up with the master?

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

On Tue, 2008-09-09 at 18:26 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Don't understand. I am referring to the logic at the top of
> > AdvanceXLInsertBuffer(). We would need to wait for all people reading
> > the contents of wal_buffers.
>
> Oh, I see.
>
> If a slave falls behind, how does it catch up?

That is the right question.

> I guess you're saying
> that it can't fall behind, because the master will block before that
> happens. Also in asynchronous replication?

Yes, it can fall behind in async mode. sysadmin must not let it.

> And what about when the slave
> is first set up, and needs to catch up with the master?

We need an initial joining mode while they "match speed". We must allow
for the case where the standby has been recycled, or the network has
been down for a medium-long period of time.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

> > Don't understand. I am referring to the logic at the top of
> > AdvanceXLInsertBuffer(). We would need to wait for all people reading
> > the contents of wal_buffers.
>
> Oh, I see.
>
> If a slave falls behind, how does it catch up? I guess you're saying
> that it can't fall behind, because the master will block before that
> happens. Also in asynchronous replication? And what about
> when the slave
> is first set up, and needs to catch up with the master?

I think the WAL Sender needs the ability to read the WAL files directly.
In cases where it falls behind, or just started, it needs to be able to catch up.
So, it seems we eighter need to copy the WAL buffer into local memory before sending,
or "lock" the WAL buffer until send finished.
Useful network timeouts are in the >= 5-10 sec range (even for GbE lan), so I don't
think locking WAL buffers is feasible. Thus the WAL sender needs to copy (the needed
portion of the current WAL buffer) before send (or use async send that immediately
returns when the buffer is copied into the network stack).

When the WAL sender is ready to continue it eighter still finds the next WAL buffer
(or the rest of the current buffer) or it needs to fall back to Plan B and
read the WAL files again. A sync client could still wait for the replicate, even if
local WAL has already advanced massively. The checkpointer would need the LSN
info from WAL senders to not reuse any still needed WAL files, although in that case
it might be time to declare the replicate broken.

Ideally the WAL sender also knows whether the client waits, so it can decide to send
a part of a buffer. The WAL sender should wake and act whenever a "network packet"
full of WAL buffer is ready, regardless of commits. Whatever size of send seems
appropriate here (might be one WAL page).
The WAL Sender should only need to expect a response, when it sent a commit record,
ideally only if a client is waiting (and once in a while at least for every log switch).

All in all a useful streamer seems like a lot of work.

Andreas

On Tue, Sep 9, 2008 at 8:38 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> There's one thing I haven't figured out in this discussion. Does the write
> to the disk happen before or after the write to the slave? Can you guarantee
> that if a transaction is committed in the master, it's also committed in the
> slave, or vice versa?

We can guarantee that a transaction is committed in both the master and
the slave if we can wait for that one fsyncs WAL to disk and the other holds
it to memory or disk. Even if one fails, the other can continue service.
Even if both fail, the node which wrote WAL can continue service. A transaction
is lost in neither of the cases.

> Agreed. But what happens if you hit that timeout?

The stand-alone master continues service when it it that timeout. On the other
hand, the slave waits for the order by the sysadmin or the clustering software,
then it exits or becomes master.

> Should we enforce that
> timeout within the server, or should we leave that to the external heartbeat
> system?

Within the server. All users do not use such an external system. It's not simple
for the external system to leave the master stand-alone.

regards

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

On Tue, Sep 9, 2008 at 8:42 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
>> In the viewpoint of detection of a network failure, this feature is
>> necessary.
>> When the network goes down, WAL sender can be blocked until it detects
>> the network failure, i.e. WAL sender keeps waiting for the response which
>> never comes. A timeout notification is necessary in order to detect a
>> network failure soon.
>
> That's one of the areas I'm missing from the overall concept. I'm glad it
> comes up. You certainly realize, that such a timeout must be set high enough
> so as not to trigger "false negatives" every now and then?

Yes.
And, as you know, there is trade-off between the false detection of the network
failure and how long WAL sender is blocked.

I'll provide not only that timeout but also keepalive for the network between
the master and the slave. I expect that keepalive eases that trade-off.

regards

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

On Wed, Sep 10, 2008 at 12:26 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> If a slave falls behind, how does it catch up? I guess you're saying that it
> can't fall behind, because the master will block before that happens. Also
> in asynchronous replication? And what about when the slave is first set up,
> and needs to catch up with the master?

The mechanism for the slave to catch up with the master should be
provided on the outside of postgres. I think that postgres should provide
only WAL streaming, i.e. the master always sends *current* WAL data
to the slave.

Of course, the master has to send also the current WAL *file* in the
initial sending just after the slave starts and connects with it.
Because, at the time, current WAL position might be in the middle of
WAL file. Even if the master sends only current WAL data, the slave
which don't have the corresponding WAL file can not handle it.

regards

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

On Wed, 2008-09-10 at 15:15 +0900, Fujii Masao wrote:
> On Wed, Sep 10, 2008 at 12:26 AM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > If a slave falls behind, how does it catch up? I guess you're saying that it
> > can't fall behind, because the master will block before that happens. Also
> > in asynchronous replication? And what about when the slave is first set up,
> > and needs to catch up with the master?
>
> The mechanism for the slave to catch up with the master should be
> provided on the outside of postgres.

So you mean that we still need to do initial setup (copy backup files
and ship and replay WAL segments generated during copy) by external
WAL-shipping tools, like walmgr.py, and then at some point switch over
to internal WAL-shipping, when we are sure that we are within same WAL
file on both master and slave ?

> I think that postgres should provide
> only WAL streaming, i.e. the master always sends *current* WAL data
> to the slave.
>
> Of course, the master has to send also the current WAL *file* in the
> initial sending just after the slave starts and connects with it.

I think that it needs to send all WAL files which slave does not yet
have, as else the slave will have gaps. On busy system you will generate
several new WAL files in the time it takes to make master copy, transfer
it to slave and apply WAL files generated during initial setup.

> Because, at the time, current WAL position might be in the middle of
> WAL file. Even if the master sends only current WAL data, the slave
> which don't have the corresponding WAL file can not handle it.

I agree, that making initial copy may be outside the scope of
Synchronous Log Shipping Replication, but slave catching up by
requesting all missing WAL files and applying these up to a point when
it can switch to Sync mode should be in. Else we gain very little from
this patch.

---------------
Hannu

On Wed, Sep 10, 2008 at 12:05 PM, Hannu Krosing <hannu(at)krosing(dot)net> wrote:
>
>
>> Because, at the time, current WAL position might be in the middle of
>> WAL file. Even if the master sends only current WAL data, the slave
>> which don't have the corresponding WAL file can not handle it.
>
> I agree, that making initial copy may be outside the scope of
> Synchronous Log Shipping Replication, but slave catching up by
> requesting all missing WAL files and applying these up to a point when
> it can switch to Sync mode should be in. Else we gain very little from
> this patch.
>

I agree. We should leave the initial backup acquisition out of the
scope atleast for the first phase, but provide mechanism to do initial
catch up, as it may get messy to do it completely outside of the core.

The slave will need to able to buffer the *current* WAL until it gets
the missing WAL files and then continue. Also we may not want the
master to be stuck while slave is doing the catchup.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

On Wed, 2008-09-10 at 09:35 +0300, Hannu Krosing wrote:
> On Wed, 2008-09-10 at 15:15 +0900, Fujii Masao wrote:
> > On Wed, Sep 10, 2008 at 12:26 AM, Heikki Linnakangas
> > <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > > If a slave falls behind, how does it catch up? I guess you're saying that it
> > > can't fall behind, because the master will block before that happens. Also
> > > in asynchronous replication? And what about when the slave is first set up,
> > > and needs to catch up with the master?
> >
> > The mechanism for the slave to catch up with the master should be
> > provided on the outside of postgres.
>
> So you mean that we still need to do initial setup (copy backup files
> and ship and replay WAL segments generated during copy) by external
> WAL-shipping tools, like walmgr.py, and then at some point switch over
> to internal WAL-shipping, when we are sure that we are within same WAL
> file on both master and slave ?
>
> > I think that postgres should provide
> > only WAL streaming, i.e. the master always sends *current* WAL data
> > to the slave.
> >
> > Of course, the master has to send also the current WAL *file* in the
> > initial sending just after the slave starts and connects with it.
>
> I think that it needs to send all WAL files which slave does not yet
> have, as else the slave will have gaps. On busy system you will generate
> several new WAL files in the time it takes to make master copy, transfer
> it to slave and apply WAL files generated during initial setup.
>
> > Because, at the time, current WAL position might be in the middle of
> > WAL file. Even if the master sends only current WAL data, the slave
> > which don't have the corresponding WAL file can not handle it.
>
> I agree, that making initial copy may be outside the scope of
> Synchronous Log Shipping Replication, but slave catching up by
> requesting all missing WAL files and applying these up to a point when
> it can switch to Sync mode should be in. Else we gain very little from
> this patch.

I agree with Hannu.

Any working solution needs to work for all required phases. If you did
it this way, you'd never catch up at all.

When you first make the copy, it will be made at time X. The point of
consistency will be sometime later and requires WAL data to make it
consistent. So you would need to do a PITR to get it to the point of
consistency. While you've been doing that, the primary server has moved
on and now there is a gap between primary and standby. You *must*
provide a facility to allow the standby to catch up with the primary.
Only sending *current* WAL is not a solution, and not acceptable.

So there must be mechanisms for sending past *and* current WAL data to
the standby, and an exact and careful mechanism for switching between
the two modes when the time is right. Replication is only synchronous
*after* the change in mode.

So the protocol needs to be something like:

1. Standby contacts primary and says it would like to catch up, but is
currently at point X (which is a point at, or after the first consistent
stopping point in WAL after standby has performed its own crash
recovery, if any was required).
2. primary initiates data transfer of old data to standby, starting at
point X
3. standby tells primary where it has got to periodically
4. at some point primary decides primary and standby are close enough
that it can now begin streaming "current WAL" (which is always the WAL
up to wal_buffers behind the the current WAL insertion point).

Bear in mind that unless wal_buffers > 16MB the final catchup will
*always* be less than one WAL file, so external file based mechanisms
alone could never be enough. So you would need wal_buffers >= 2000 to
make an external catch up facility even work at all.

This also probably means that receipt of WAL data on the standby cannot
be achieved by placing it in wal_buffers. So we probably need to write
it directly to the WAL files, then rely on the filesystem cache on the
standby to buffer the data for use by ReadRecord.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Wed, 2008-09-10 at 13:28 +0900, Fujii Masao wrote:
> On Tue, Sep 9, 2008 at 8:38 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > There's one thing I haven't figured out in this discussion. Does the write
> > to the disk happen before or after the write to the slave? Can you guarantee
> > that if a transaction is committed in the master, it's also committed in the
> > slave, or vice versa?
>

The write happens concurrently and independently on both.

Yes, you wait for the write *and* send pointer to be "flushed" before
you allow a synch commit with synch replication. (Definition of flushed
is changeable by parameters).

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Wed, 2008-09-10 at 12:24 +0530, Pavan Deolasee wrote:
> Also we may not want the master to be stuck while slave is doing the catchup.

No, since it may take hours, not seconds.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Tue, 2008-09-09 at 20:59 +0200, Zeugswetter Andreas OSB sIT wrote:
> All in all a useful streamer seems like a lot of work.

I mentioned some time ago an alternative idea of having the slave
connect through a normal SQL connection and call a function which
streams the WAL file from the point requested by the slave... wouldn't
that be feasible ? All the connection part would be already there, only
the streaming function should be implemented. It even could use SSL
connections if needed. Then you would have one normal backend per slave,
and they should access either the files directly or possibly some shared
area where the WAL is buffered for this purpose... the streaming
function could also take care of signaling the "up-to-dateness" of the
slaves in case of synchronous replication.

There could also be some system table infrastructure to track the
slaves. There could also be some functions to stream the files of the DB
through normal backends, so a slave could be bootstrapped all the way
from copying the files through a simple postgres backend connection...
that would make for the easiest possible setup of a slave: configure a
connection to the master, and hit "run"... and last but not least the
same interface could be used by a PITR repository client for archiving
the WAL stream and occasional file system snapshots.

Cheers,
Csaba.

Hi,

Simon Riggs wrote:
> 1. Standby contacts primary and says it would like to catch up, but is
> currently at point X (which is a point at, or after the first consistent
> stopping point in WAL after standby has performed its own crash
> recovery, if any was required).
> 2. primary initiates data transfer of old data to standby, starting at
> point X
> 3. standby tells primary where it has got to periodically
> 4. at some point primary decides primary and standby are close enough
> that it can now begin streaming "current WAL" (which is always the WAL
> up to wal_buffers behind the the current WAL insertion point).

Hm.. wouldn't it be simpler, to start streaming right away and "cache"
that on the standby until it can be applied? I.e. a protocol like:

1. - same as above -
2. primary starts streaming from live or hot data from it's current
position Y in the WAL stream, which is certainly after (or probably
equal to) X.
3. standby receives the hot stream from point Y on. It now knows it
misses 'cold' portions of the WAL from X to Y and requests that.
4. primary serves remaining 'cold' WAL chunks from its xlog / archive
from between X and Y.
5. standby applies 'cold' WAL, until done. Then proceeds with the cached
WAL segments from 'hot' streaming.

> Bear in mind that unless wal_buffers > 16MB the final catchup will
> *always* be less than one WAL file, so external file based mechanisms
> alone could never be enough.

Agreed.

> This also probably means that receipt of WAL data on the standby cannot
> be achieved by placing it in wal_buffers. So we probably need to write
> it directly to the WAL files, then rely on the filesystem cache on the
> standby to buffer the data for use by ReadRecord.

Makes sense, especially in case of cached WAL as outlined above. Is this
a problem in any way?

Regards

Markus Wanner

Simon Riggs wrote:
> On Wed, 2008-09-10 at 13:28 +0900, Fujii Masao wrote:
>> On Tue, Sep 9, 2008 at 8:38 PM, Heikki Linnakangas
>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>> There's one thing I haven't figured out in this discussion. Does the write
>>> to the disk happen before or after the write to the slave? Can you guarantee
>>> that if a transaction is committed in the master, it's also committed in the
>>> slave, or vice versa?
>
> The write happens concurrently and independently on both.
>
> Yes, you wait for the write *and* send pointer to be "flushed" before
> you allow a synch commit with synch replication. (Definition of flushed
> is changeable by parameters).

The thing that bothers me is the behavior when the synchronous slave
doesn't respond. A timeout has been discussed, after which the master
just gives up on sending, and starts acting as if there's no slave.
How's that different from asynchronous mode where WAL is sent to the
server concurrently when it's flushed to disk, but we don't wait for the
send to finish? ISTM that in both cases the only guarantee we can give
is that when a transaction is acknowledged as committed, it's committed
in the master but not necessarily in the slave.

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

On Wed, 2008-09-10 at 08:15 +0100, Simon Riggs wrote:

> Any working solution needs to work for all required phases. If you did
> it this way, you'd never catch up at all.
>
> When you first make the copy, it will be made at time X. The point of
> consistency will be sometime later and requires WAL data to make it
> consistent. So you would need to do a PITR to get it to the point of
> consistency. While you've been doing that, the primary server has moved
> on and now there is a gap between primary and standby. You *must*
> provide a facility to allow the standby to catch up with the primary.
> Only sending *current* WAL is not a solution, and not acceptable.
>
> So there must be mechanisms for sending past *and* current WAL data to
> the standby, and an exact and careful mechanism for switching between
> the two modes when the time is right. Replication is only synchronous
> *after* the change in mode.
>
> So the protocol needs to be something like:
>
> 1. Standby contacts primary and says it would like to catch up, but is
> currently at point X (which is a point at, or after the first consistent
> stopping point in WAL after standby has performed its own crash
> recovery, if any was required).
> 2. primary initiates data transfer of old data to standby, starting at
> point X
> 3. standby tells primary where it has got to periodically
> 4. at some point primary decides primary and standby are close enough
> that it can now begin streaming "current WAL" (which is always the WAL
> up to wal_buffers behind the the current WAL insertion point).
>
> Bear in mind that unless wal_buffers > 16MB the final catchup will
> *always* be less than one WAL file, so external file based mechanisms
> alone could never be enough. So you would need wal_buffers >= 2000 to
> make an external catch up facility even work at all.
>
> This also probably means that receipt of WAL data on the standby cannot
> be achieved by placing it in wal_buffers. So we probably need to write
> it directly to the WAL files, then rely on the filesystem cache on the
> standby to buffer the data for use by ReadRecord.

And this catchup may be needed to be done repeatedly, in case of network
failure.

I don't think that slave automatically becoming a master if it detects
network failure (as suggested elsewhere in this thread) is acceptable
solution, as it will more often than not result in two masters.

A better solution would be:

1. Slave just keeps waiting for new WAL records and confirming receipt
storing to disk and application.

2. Master is in one of at least two states

2.1 - Catchup - Async mode where it is sending old logs and wal
records to slave

2.2 - Sync Replication - Sync mode, where COMMIT does not return before
confirmation from WALSender.

Initial mode is Catchup which is promoted to Sync Replication when delay
of WAL application is reasonably small.

When Master detects network outage (== delay bigger than acceptable) it
will either just Send a NOTICE to all clients and fall back to Catchup,
or raise an ERROR (and still fall back to cathup)

This is the point where external HA / Heartbeat etc. solutions would
intervene and decide, what to do.

-----------------
Hannu

On Wed, Sep 10, 2008 at 1:40 PM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>
>
> The thing that bothers me is the behavior when the synchronous slave doesn't
> respond. A timeout has been discussed, after which the master just gives up
> on sending, and starts acting as if there's no slave. How's that different
> from asynchronous mode where WAL is sent to the server concurrently when
> it's flushed to disk, but we don't wait for the send to finish? ISTM that in
> both cases the only guarantee we can give is that when a transaction is
> acknowledged as committed, it's committed in the master but not necessarily
> in the slave.
>

I think there is one difference. Assuming that the timeouts happen
infrequently, most of the time the slave is in sync with the master
and that can be reported to the user. Whereas in async mode, the slave
will *always* be out of sync.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

On Wed, 2008-09-10 at 10:06 +0200, Markus Wanner wrote:
> Hi,
>
> Simon Riggs wrote:
> > 1. Standby contacts primary and says it would like to catch up, but is
> > currently at point X (which is a point at, or after the first consistent
> > stopping point in WAL after standby has performed its own crash
> > recovery, if any was required).
> > 2. primary initiates data transfer of old data to standby, starting at
> > point X
> > 3. standby tells primary where it has got to periodically
> > 4. at some point primary decides primary and standby are close enough
> > that it can now begin streaming "current WAL" (which is always the WAL
> > up to wal_buffers behind the the current WAL insertion point).
>
> Hm.. wouldn't it be simpler, to start streaming right away and "cache"
> that on the standby until it can be applied? I.e. a protocol like:

Good idea!

This makes everything simpler, as user has to do only 4 things

1. start slave in "receive WAL, dont apply" mode
2. start walshipping on master
3. copy files from master to slave.
4. restart slave in "receive WAL" mode

all else will happen automatically.

---------------
Hannu

On Wed, 2008-09-10 at 11:10 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Wed, 2008-09-10 at 13:28 +0900, Fujii Masao wrote:
> >> On Tue, Sep 9, 2008 at 8:38 PM, Heikki Linnakangas
> >> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> >>> There's one thing I haven't figured out in this discussion. Does the write
> >>> to the disk happen before or after the write to the slave? Can you guarantee
> >>> that if a transaction is committed in the master, it's also committed in the
> >>> slave, or vice versa?
> >
> > The write happens concurrently and independently on both.
> >
> > Yes, you wait for the write *and* send pointer to be "flushed" before
> > you allow a synch commit with synch replication. (Definition of flushed
> > is changeable by parameters).
>
> The thing that bothers me is the behavior when the synchronous slave
> doesn't respond. A timeout has been discussed, after which the master
> just gives up on sending, and starts acting as if there's no slave.
> How's that different from asynchronous mode where WAL is sent to the
> server concurrently when it's flushed to disk, but we don't wait for the
> send to finish? ISTM that in both cases the only guarantee we can give
> is that when a transaction is acknowledged as committed, it's committed
> in the master but not necessarily in the slave.

We should differentiate between what the WALsender does and what the
user does in response to a network timeout.

Saying "I want to wait for a synchronous commit and I am willing to wait
for ever to ensure it" leads to long hangs in some cases.

I was suggesting that some users may wish to wait up to time X before
responding to the commit. The WALSender may keep retrying long after
that point, but that doesn't mean all current users need to do that
also. The user would need to say whether the response to the timeout was
an error, or just accept and get on with it.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> Saying "I want to wait for a synchronous commit and I am willing to wait
> for ever to ensure it" leads to long hangs in some cases.

Sure. That's the fundamental problem with synchronous replication.
That's why many people choose asynchronous replication instead. Clearly
at some point you'll want to give up and continue without the slave, or
kill the master and fail over to the slave. I'm wondering how that's
different than the lag between master and server in asynchronous
replication from the client's point of view.

> I was suggesting that some users may wish to wait up to time X before
> responding to the commit. The WALSender may keep retrying long after
> that point, but that doesn't mean all current users need to do that
> also. The user would need to say whether the response to the timeout was
> an error, or just accept and get on with it.

I'm not sure I understand that paragraph. Who's the user? Do we need to
expose some new information to the client so that it can do something?

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

On Wed, 2008-09-10 at 10:06 +0200, Markus Wanner wrote:
> Hi,
>
> Simon Riggs wrote:
> > 1. Standby contacts primary and says it would like to catch up, but is
> > currently at point X (which is a point at, or after the first consistent
> > stopping point in WAL after standby has performed its own crash
> > recovery, if any was required).
> > 2. primary initiates data transfer of old data to standby, starting at
> > point X
> > 3. standby tells primary where it has got to periodically
> > 4. at some point primary decides primary and standby are close enough
> > that it can now begin streaming "current WAL" (which is always the WAL
> > up to wal_buffers behind the the current WAL insertion point).
>
> Hm.. wouldn't it be simpler, to start streaming right away and "cache"

The standby server won't come up until you have:
* copied the base backup
* sent it to standby server
* bring up standby, have it realise it is a replication partner and
begin requesting WAL from primary (in some way)

There will be a gap (probably) between the initial WAL files and the
current tail of wal_buffers by the time all of the above has happened.
We will then need to copy more WAL across until we get to a point where
the most recent WAL record available on standby is ahead of the tail of
wal_buffers on primary so that streaming can start.

If we start caching WAL right away we would need to have two receivers.
One to receive the missing WAL data and one to receive the current WAL
data. We can't apply the WAL until we have the earlier missing WAL data,
so cacheing it seems difficult. On a large server this might be GBs of
data. Seems easier to not cache current WAL and to have just a single
WALReceiver process that performs a mode change once it has caught up.
(And I should say "if it catches up", since it is possible that it never
actually will catch up, in practical terms, since this depends upon the
relative power of the servers involved.). So there's no need to store
more WAL on standby than is required to restart recovery from last
restartpoint. i.e. we stream WAL at all times, not just in normal
running mode.

Seems easiest to have:
* Startup process only reads next WAL record when the ReceivedLogPtr >
ReadRecPtr, so it knows nothing of how WAL is received. Startup process
reads directly from WAL files in *all* cases. ReceivedLogPtr is in
shared memory and accessed via spinlock. Startup process only ever reads
this pointer. (Notice that Startup process is modeless).
* WALReceiver reads data from primary and writes it to WAL files,
fsyncing (if ever requested to do so). WALReceiver updates
ReceivedLogPtr.

That is much simpler and more modular. Buffering of the WAL files is
handled by filesystem buffering.

If standby crashes, all data is safely written to WAL files and we
restart from correct place.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Wed, Sep 10, 2008 at 4:15 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Wed, 2008-09-10 at 09:35 +0300, Hannu Krosing wrote:
>> On Wed, 2008-09-10 at 15:15 +0900, Fujii Masao wrote:
>> > On Wed, Sep 10, 2008 at 12:26 AM, Heikki Linnakangas
>> > <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> > > If a slave falls behind, how does it catch up? I guess you're saying that it
>> > > can't fall behind, because the master will block before that happens. Also
>> > > in asynchronous replication? And what about when the slave is first set up,
>> > > and needs to catch up with the master?
>> >
>> > The mechanism for the slave to catch up with the master should be
>> > provided on the outside of postgres.
>>
>> So you mean that we still need to do initial setup (copy backup files
>> and ship and replay WAL segments generated during copy) by external
>> WAL-shipping tools, like walmgr.py, and then at some point switch over
>> to internal WAL-shipping, when we are sure that we are within same WAL
>> file on both master and slave ?
>>
>> > I think that postgres should provide
>> > only WAL streaming, i.e. the master always sends *current* WAL data
>> > to the slave.
>> >
>> > Of course, the master has to send also the current WAL *file* in the
>> > initial sending just after the slave starts and connects with it.
>>
>> I think that it needs to send all WAL files which slave does not yet
>> have, as else the slave will have gaps. On busy system you will generate
>> several new WAL files in the time it takes to make master copy, transfer
>> it to slave and apply WAL files generated during initial setup.
>>
>> > Because, at the time, current WAL position might be in the middle of
>> > WAL file. Even if the master sends only current WAL data, the slave
>> > which don't have the corresponding WAL file can not handle it.
>>
>> I agree, that making initial copy may be outside the scope of
>> Synchronous Log Shipping Replication, but slave catching up by
>> requesting all missing WAL files and applying these up to a point when
>> it can switch to Sync mode should be in. Else we gain very little from
>> this patch.
>
> I agree with Hannu.
>
> Any working solution needs to work for all required phases. If you did
> it this way, you'd never catch up at all.
>
> When you first make the copy, it will be made at time X. The point of
> consistency will be sometime later and requires WAL data to make it
> consistent. So you would need to do a PITR to get it to the point of
> consistency. While you've been doing that, the primary server has moved
> on and now there is a gap between primary and standby. You *must*
> provide a facility to allow the standby to catch up with the primary.
> Only sending *current* WAL is not a solution, and not acceptable.
>
> So there must be mechanisms for sending past *and* current WAL data to
> the standby, and an exact and careful mechanism for switching between
> the two modes when the time is right. Replication is only synchronous
> *after* the change in mode.
>
> So the protocol needs to be something like:
>
> 1. Standby contacts primary and says it would like to catch up, but is
> currently at point X (which is a point at, or after the first consistent
> stopping point in WAL after standby has performed its own crash
> recovery, if any was required).
> 2. primary initiates data transfer of old data to standby, starting at
> point X
> 3. standby tells primary where it has got to periodically
> 4. at some point primary decides primary and standby are close enough
> that it can now begin streaming "current WAL" (which is always the WAL
> up to wal_buffers behind the the current WAL insertion point).
>
> Bear in mind that unless wal_buffers > 16MB the final catchup will
> *always* be less than one WAL file, so external file based mechanisms
> alone could never be enough. So you would need wal_buffers >= 2000 to
> make an external catch up facility even work at all.
>
> This also probably means that receipt of WAL data on the standby cannot
> be achieved by placing it in wal_buffers. So we probably need to write
> it directly to the WAL files, then rely on the filesystem cache on the
> standby to buffer the data for use by ReadRecord.
>
> --
> Simon Riggs www.2ndQuadrant.com
> PostgreSQL Training, Services and Support
>
>

Umm.. I disagree with you ;)

Here is my initial setup sequence.

1) Start WAL receiver.
The current WAL file and subsequent ones will be transmitted by
WAL sender and WAL receiver. This transmission will not block
the following operation for initial setup, and vice versa. That is,
the slave can catch up with the master without blocking the master.
I cannot accept that WAL sender is blocked for initial setup.

2) Copy the missing history files from the master to the slave.

3) Prepare recovery.conf on the slave.
You have to configure pg_standby and set recovery_target_timeline to
'latest' or the current TLI of the master.

4) Start postgres.
The startup process and pg_standby start archive recovery. If there
are missing WAL files, pg_standby waits for it and WAL replay is
suspended.

5) Copy the missing WAL files from the master and the slave.
Of course, we don't need to copy the WAL files which are transmitted
by WAL sender and WAL receiver. Then, the recovery is resumed.

My sequence covers several cases :

* There is no missing WAL file.
* There is a lot of missing WAL file.
* There are missing history files. Failover always generates the gap of
history file because TLI is incremented when archive recovery is completed.
...

In your design, does not initial setup block the master?
Does your design cover above-mentioned case?

regards

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

On Wed, 2008-09-10 at 17:57 +0900, Fujii Masao wrote:

> I cannot accept that WAL sender is blocked for initial setup.

Yes, very important point. We definitely agree on that. The primary must
be able to continue working while all this setup is happening. No tables
are locked, all commits are allowed etc..

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Hi,

Le mercredi 10 septembre 2008, Heikki Linnakangas a écrit :
> Sure. That's the fundamental problem with synchronous replication.
> That's why many people choose asynchronous replication instead. Clearly
> at some point you'll want to give up and continue without the slave, or
> kill the master and fail over to the slave. I'm wondering how that's
> different than the lag between master and server in asynchronous
> replication from the client's point of view.

As a future user of this new facilities, the difference from client's POV is
simple : in normal mode of operation, we want a strong guarantee that any
COMMIT has made it to both the master and the slave at commit time. No lag
whatsoever.

You're considering lag as an option in case of failure, but I don't see this
as acceptable when you need sync commit. In case of network timeout, cluster
is down. So you want to either continue servicing in degraged mode or get the
service down while you repair the cluster, but neither of those choice can be
transparent to the admins, I'd argue.

Of course, main use case is high availability, which tends to say you do not
have the option to stop service, and seems to dictate continue servicing in
degraded mode: slave can't keep up (whatever the error domain), master is
alone, "advertise" to monitoring solutions and continue servicing.
And provide some way for the slave to "rejoin", maybe, too.

> I'm not sure I understand that paragraph. Who's the user? Do we need to
> expose some new information to the client so that it can do something?

Maybe with some GUCs where to set the acceptable "timeout" for WAL sync
process, and if reaching timeout is a warning or an error. With a userset GUC
we could event have replication-error-level transaction concurrent to non
critical ones...

Now what to do exactly in case of error remains to be decided...

HTH, Regards,
--
dim

On Wed, 2008-09-10 at 17:57 +0900, Fujii Masao wrote:

> Umm.. I disagree with you ;)

That's no problem and I respect your knowledge.

If we disagree, it is very likely because we have misunderstood each
other. Much has been written, so I will wait for it to all be read and
understood by you and others, and for me to read other posts and replies
also. I feel sure that after some thought a clear consensus will emerge,
and I feel hopeful that the feature can be done in the time available
with simple code changes.

So I will stop replying for a few hours to give everyone time (incl me).

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Hi,

Simon Riggs wrote:
> The standby server won't come up until you have:
> * copied the base backup
> * sent it to standby server
> * bring up standby, have it realise it is a replication partner and
> begin requesting WAL from primary (in some way)

Right. That was your assumption as well. Required before step 1 in both
cases.

> If we start caching WAL right away we would need to have two receivers.
> One to receive the missing WAL data and one to receive the current WAL
> data. We can't apply the WAL until we have the earlier missing WAL data,
> so cacheing it seems difficult.

You could use the same receiver process and just handle different
packets differently. I see no need for two separate receiver processes here.

> On a large server this might be GBs of
> data.

..if served from a log archive, correct. Without archiving, we are
limited to xlog anyway.

> Seems easier to not cache current WAL and to have just a single
> WALReceiver process that performs a mode change once it has caught up.
> (And I should say "if it catches up", since it is possible that it never
> actually will catch up, in practical terms, since this depends upon the
> relative power of the servers involved.). So there's no need to store
> more WAL on standby than is required to restart recovery from last
> restartpoint. i.e. we stream WAL at all times, not just in normal
> running mode.

Switching between streaming from files and 'live' streaming on the
active node seems difficult to me, because you need to make sure there's
no gap. That problem could be circumvented by handling this on the
standby. If you think switching on the active is simple enough, that's fine.

> Seems easiest to have:
> * Startup process only reads next WAL record when the ReceivedLogPtr >
> ReadRecPtr, so it knows nothing of how WAL is received. Startup process
> reads directly from WAL files in *all* cases. ReceivedLogPtr is in
> shared memory and accessed via spinlock. Startup process only ever reads
> this pointer. (Notice that Startup process is modeless).

Well, that's certainly easier for the standby, but requires mode
switching on the active.

Regards

Markus Wanner

On Wed, 2008-09-10 at 11:07 +0200, Dimitri Fontaine wrote:
> Hi,
>
> Le mercredi 10 septembre 2008, Heikki Linnakangas a écrit :
> > Sure. That's the fundamental problem with synchronous replication.
> > That's why many people choose asynchronous replication instead. Clearly
> > at some point you'll want to give up and continue without the slave, or
> > kill the master and fail over to the slave. I'm wondering how that's
> > different than the lag between master and server in asynchronous
> > replication from the client's point of view.
>
> As a future user of this new facilities, the difference from client's POV is
> simple : in normal mode of operation, we want a strong guarantee that any
> COMMIT has made it to both the master and the slave at commit time. No lag
> whatsoever.

Agreed.

> You're considering lag as an option in case of failure, but I don't see this
> as acceptable when you need sync commit. In case of network timeout, cluster
> is down. So you want to either continue servicing in degraged mode or get the
> service down while you repair the cluster, but neither of those choice can be
> transparent to the admins, I'd argue.
>
> Of course, main use case is high availability, which tends to say you do not
> have the option to stop service,

We have a number of choices, at the point of failure:
* Does the whole primary server stay up (probably)?
* Do we continue to allow new transactions in degraded mode? (which
increases the risk of transaction loss if we continue at that time).
(The answer sounds like it will be "of course, stupid" but this cluster
may be part of an even higher level HA mechanism, so the answer isn't
always clear).
* For each transaction that is trying to commit: do we want to wait
forever? If not, how long? If we stop waiting, do we throw ERROR, or do
we say, lets get on with another transaction.

If the server is up, yet all connections in a session pool are stuck
waiting for their last commits to complete then most sysadmins would
agree that the server is actually "down". Since no useful work is
happening, or can be initiated - even read only. We don't need to
address that issue in the same way for all transactions, is all I'm
saying.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

* Simon Riggs <simon(at)2ndQuadrant(dot)com> [080910 06:18]:

> We have a number of choices, at the point of failure:
> * Does the whole primary server stay up (probably)?

The only sane choice is the one the admin makes. Any "predetermined" choice
PG makes can (and will) be wrong in some situations.

> * Do we continue to allow new transactions in degraded mode? (which
> increases the risk of transaction loss if we continue at that time).
> (The answer sounds like it will be "of course, stupid" but this cluster
> may be part of an even higher level HA mechanism, so the answer isn't
> always clear).

The only sane choice is the one the admin makes. Any "predetermined" choice
PG makes can (and will) be wrong in some situations.

> * For each transaction that is trying to commit: do we want to wait
> forever? If not, how long? If we stop waiting, do we throw ERROR, or do
> we say, lets get on with another transaction.

The only sane choice is the one the admin makes. Any "predetermined" choice
PG makes can (and will) be wrong in some situations.

> If the server is up, yet all connections in a session pool are stuck
> waiting for their last commits to complete then most sysadmins would
> agree that the server is actually "down". Since no useful work is
> happening, or can be initiated - even read only. We don't need to
> address that issue in the same way for all transactions, is all I'm
> saying.

Sorry to sound like a broken record here, but the whole point is to
guarantee data safety. You can only start trading ACID for HA if you
have the ACID guarantees in the first place (and for replication, this
means across the cluster, including slaves)

So in that light, I think it's pretty obvious that if a slave is
considered part of an active synchronous replication cluster, in the
face of "network lag", or even network failure, the master *must* pretty
much halt all new commits in their tracks until that slave acknowledges
the commit. Yes that's going to cause a backup. That's the cost of a
synchronous replication.

But that means the admin has to be able to control whether a slave is
part of an active synchronous replication cluster or not. I hope that
control eventually is a lot more than a GUC that says "when a slave is X
seconds behind, abandon him).

I'ld dream of a "replication" interface where I could add new slaves on
the fly (and a nice tool that pg_start_backup()/sync/apply WAL to sync
then subscribe), get slave status (maybe syncing/active/abandoned), and
some average latency (i.e. something like svctm of iostat on your WAL
disk) and some way to control the slave degradation from active to
abandoned (like the above GUC, or maybe a callout/hook/script that runs
when latency > X, etc, or both).

And for async replication, you just have a "proxy" slave which does
nothing but subscribe to your master, always acknowledge WAL right away
so the master doesn't wait, and keep a local backlog of WAL it's
sending out to many clients. This proxy slave doesn't slow down the
master, but can feed clients accross slow WAN links (that may not have
the burst bandwidth to keep up with bursty master writes, but have agregate
bandwidth to keep pretty close to the master), or networks that drop out
for a period, etc.

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

On Wed, 2008-09-10 at 09:36 -0400, Aidan Van Dyk wrote:
> * Simon Riggs <simon(at)2ndQuadrant(dot)com> [080910 06:18]:
>
> > We have a number of choices, at the point of failure:
> > * Does the whole primary server stay up (probably)?
>
> The only sane choice is the one the admin makes. Any "predetermined" choice
> PG makes can (and will) be wrong in some situations.

We are in agreement then. Those questions were listed as arguments in
favour of a parameter to let the sysadmin choose. More than that, I was
saying this can be selected for individual transactions, not just for
the whole server as a whole (as other vendors do).

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Tue, Sep 9, 2008 at 10:55 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> Hi,
>
> ITAGAKI Takahiro wrote:
>>
>> Signals and locking, borrewed from Postgres-R, are now studied
>> for the purpose in the log shipping,
>
> Cool. Let me know if you have any questions WRT this imessages stuff.

If you're sure it's all right, I have a trivial question.

Which signal should we use for the notification to the backend from
WAL sender? The notable signals are already used.

Or, since a backend don't need to wait on select() unlike WAL sender,
ISTM that it's not so inconvenient to use a semaphore for that notification.

Your thought?

regards

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

Hi,

Fujii Masao wrote:
> On Tue, Sep 9, 2008 at 10:55 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
>> Hi,
>>
>> ITAGAKI Takahiro wrote:
>>> Signals and locking, borrewed from Postgres-R, are now studied
>>> for the purpose in the log shipping,
>> Cool. Let me know if you have any questions WRT this imessages stuff.
>
> If you're sure it's all right, I have a trivial question.

Well, I know it works for me and I think it could work for you, too.
That's all I'm saying.

> Which signal should we use for the notification to the backend from
> WAL sender? The notable signals are already used.

I'm using SIGUSR1, see src/backend/storage/ipc/imsg.c from Postgres-R,
line 232. That isn't is use for backends or the postmaster, AFAIK.

> Or, since a backend don't need to wait on select() unlike WAL sender,
> ISTM that it's not so inconvenient to use a semaphore for that notification.

They probably could, but not the WAL sender.

What's the benefit of semaphores? It seems pretty ugly to set up a
semaphore, lock that on the WAL sender, then claim it on the backend to
wait for it, and then release it on the WAL sender to notify the backend.

If all you want to do is to signal the backend, why not use signals ;-)
But maybe I'm missing something?

Regards

Markus Wanner

On Wed, 2008-09-10 at 17:57 +0900, Fujii Masao wrote:

> My sequence covers several cases :
>
> * There is no missing WAL file.
> * There is a lot of missing WAL file.

This is the likely case for any medium+ sized database.

> * There are missing history files. Failover always generates the gap
> of
> history file because TLI is incremented when archive recovery is
> completed.

Yes, but failover doesn't happen while we are configuring replication,
it can only happen after we have configured replication. It would be
theoretically possible to take a copy from one server and then try to
synchronise with a 3rd copy of the same server, but that seems perverse
and bug prone. So I advise that we only allow replication when the
timeline of the standby matches the timeline of the master, having it as
an explicit check.

> In your design, does not initial setup block the master?
> Does your design cover above-mentioned case?

The way I described it does not block the master. It does defer the
point at which we can start using synchronous replication, so perhaps
that is your objection. I think it is acceptable: good food takes time
to cook.

I have thought about the approach you've outlined, though it seems to me
now like a performance optimisation rather than something we must have.

IMHO it will be confusing to be transferring both old and new data at
the same time from master to slave. We will have two different processes
sending and two different processes receiving. You'll need to work
through about four times as many failure modes, all of which will need
testing. Diagnosing problems in it via the log hurts my head just
thinking about it. ISTM that will severely impact the initial robustness
of the software for this feature. Perhaps in time it is the right way.

Anyway, feels like we're getting close to some good designs. There isn't
much difference between what we're discussing here.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Wed, Sep 10, 2008 at 11:13 PM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
>> Which signal should we use for the notification to the backend from
>> WAL sender? The notable signals are already used.
>
> I'm using SIGUSR1, see src/backend/storage/ipc/imsg.c from Postgres-R, line
> 232. That isn't is use for backends or the postmaster, AFAIK.

Umm... backends have already used SIGUSR1. PostgresMain() sets up a signal
handler for SIGUSR1 as follows.

pqsignal(SIGUSR1, CatchupInterruptHandler);

Which signal should WAL sender send to backends?

>> Or, since a backend don't need to wait on select() unlike WAL sender,
>> ISTM that it's not so inconvenient to use a semaphore for that
>> notification.
>
> They probably could, but not the WAL sender.

Yes, since WAL sender waits on select(), it's convenient to use signal
for the notification *from backends to WAL sender*, I think too.

Best regards,

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

"Fujii Masao" <masao(dot)fujii(at)gmail(dot)com> writes:
> Which signal should WAL sender send to backends?

Sooner or later we shall have to bite the bullet and set up a
multiplexing system to transmit multiple event types to backends with
just one signal. We already did it for signals to the postmaster.

regards, tom lane

Hi,

Fujii Masao wrote:
> Umm... backends have already used SIGUSR1. PostgresMain() sets up a signal
> handler for SIGUSR1 as follows.

Uh.. right. Thanks for pointing that out. Maybe just use SIGPIPE for now?

> Yes, since WAL sender waits on select(), it's convenient to use signal
> for the notification *from backends to WAL sender*, I think too.

..and I'd say you better use the same for WAL sender to backend
communication, just for the sake of simplicity (and thus maintainability).

Regards

Markus Wanner

Hi,

Tom Lane wrote:
> Sooner or later we shall have to bite the bullet and set up a
> multiplexing system to transmit multiple event types to backends with
> just one signal. We already did it for signals to the postmaster.

Agreed. However, it's non-trivial if you want reliable queues (i.e. no
message skipped, as with signals) for varying message sizes. My
imessages stuff is certainly not perfect, yet. But it works to some
extent and provides exactly that functionality.

However, I'd be happy to work on improving it, if other projects start
using it as well. Anybody else interested? Use cases within Postgres
itself as of now?

Regards

Markus Wanner

On Thu, Sep 11, 2008 at 3:17 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Wed, 2008-09-10 at 17:57 +0900, Fujii Masao wrote:
>
>> My sequence covers several cases :
>>
>> * There is no missing WAL file.
>> * There is a lot of missing WAL file.
>
> This is the likely case for any medium+ sized database.

I'm sorry, but I could not understand what you mean.

>
>> * There are missing history files. Failover always generates the gap
>> of
>> history file because TLI is incremented when archive recovery is
>> completed.
>
> Yes, but failover doesn't happen while we are configuring replication,
> it can only happen after we have configured replication. It would be
> theoretically possible to take a copy from one server and then try to
> synchronise with a 3rd copy of the same server, but that seems perverse
> and bug prone. So I advise that we only allow replication when the
> timeline of the standby matches the timeline of the master, having it as
> an explicit check.

Umm... my explanation seems to have been unclear:(
Here is the case which I assume.

1) Configuration of replication, i.e. the master and the slave work fine.
2) The master fails down, then failover happens. When the slave becomes
the master, TLI is incremented, and new history file is generated.
3) In order to catch up with the new master, the server which was the master
from the first needs missing history file. At this time, it's
because there is
the gap of TLI in between two servers.

I think that this case would often happen. So, we should establish a certain
solution or procedure to the case where TLI of the master doesn't match
TLI of the slave. If we only allow the case where TLI of both servers is the
same, the configuration after failover always needs to get the base backup
on the new master. It's unacceptable for many users. But, I think that it's
the role of admin or external tools to copy history files to the slave from
the master.

>> In your design, does not initial setup block the master?
>> Does your design cover above-mentioned case?
>
> The way I described it does not block the master. It does defer the
> point at which we can start using synchronous replication, so perhaps
> that is your objection. I think it is acceptable: good food takes time
> to cook.

Yes. I understood your design.

> IMHO it will be confusing to be transferring both old and new data at
> the same time from master to slave. We will have two different processes
> sending and two different processes receiving. You'll need to work
> through about four times as many failure modes, all of which will need
> testing. Diagnosing problems in it via the log hurts my head just
> thinking about it. ISTM that will severely impact the initial robustness
> of the software for this feature. Perhaps in time it is the right way.

In my procedure, old WAL files are copyed by admin using scp, rsync
or other external tool. So, I don't think that my procedure makes a
problem more difficult. Since there are many setup cases, we should
not leave all procedures to postgres, I think.

> Anyway, feels like we're getting close to some good designs. There isn't
> much difference between what we're discussing here.

Yes. Thank you for your great ideas.

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

Fujii Masao wrote:
> I think that this case would often happen. So, we should establish a certain
> solution or procedure to the case where TLI of the master doesn't match
> TLI of the slave. If we only allow the case where TLI of both servers is the
> same, the configuration after failover always needs to get the base backup
> on the new master. It's unacceptable for many users. But, I think that it's
> the role of admin or external tools to copy history files to the slave from
> the master.

Hmm. There's more problems than the TLI with that. For the original
master to catch up by replaying WAL from the new slave, without
restoring from a full backup, the original master must not write to disk
*any* WAL that hasn't made it to the slave yet. That is certainly not
true for asynchronous replication, but it also throws off the idea of
flushing the WAL concurrently to the local disk and to the slave in
synchronous mode.

I agree that having to get a new base backup to get the old master catch
up with the new master sucks, so I hope someone sees a way around that.

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

Markus Wanner <markus(at)bluegap(dot)ch> writes:
> Tom Lane wrote:
>> Sooner or later we shall have to bite the bullet and set up a
>> multiplexing system to transmit multiple event types to backends with
>> just one signal. We already did it for signals to the postmaster.

> Agreed. However, it's non-trivial if you want reliable queues (i.e. no
> message skipped, as with signals) for varying message sizes.

No, that's not what I had in mind at all, just the ability to deliver
one of a specified set of event notifications --- ie, get around the
fact that Unix only gives us two user-definable signal types.

For signals sent from other backends, it'd be sufficient to put a
bitmask field into PGPROC entries, which the sender could OR bits into
before sending the one "real" signal event (either SIGUSR1 or SIGUSR2).

I'm not sure what to do if we need signals sent from processes that
aren't connected to shared memory; but maybe we need not cross that
bridge here.

(Also, I gather that the Windows implementation could already support
a bunch more signal types without much trouble.)

regards, tom lane

Hi,

Tom Lane wrote:
> No, that's not what I had in mind at all, just the ability to deliver
> one of a specified set of event notifications --- ie, get around the
> fact that Unix only gives us two user-definable signal types.

Ah, okay. And I already thought you'd like imessages :-(

> For signals sent from other backends, it'd be sufficient to put a
> bitmask field into PGPROC entries, which the sender could OR bits into
> before sending the one "real" signal event (either SIGUSR1 or SIGUSR2).

That might work for expanding the number of available signals, yes.

Regards

Markus Wanner

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> I'm not sure what to do if we need signals sent from processes that
> aren't connected to shared memory; but maybe we need not cross that
> bridge here.

Such as signals coming from the postmaster? Isn't that where most of them come
from though?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Gregory Stark wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
>> I'm not sure what to do if we need signals sent from processes that
>> aren't connected to shared memory; but maybe we need not cross that
>> bridge here.
>
> Such as signals coming from the postmaster? Isn't that where most of them come
> from though?

Uh.. no, such as signals *going to* the postmaster. That's where we
already have such a multiplexer in place, but not the other way around IIRC.

Regards

Markus Wanner

On Thu, 2008-09-11 at 18:17 +0300, Heikki Linnakangas wrote:
> Fujii Masao wrote:
> > I think that this case would often happen. So, we should establish a certain
> > solution or procedure to the case where TLI of the master doesn't match
> > TLI of the slave. If we only allow the case where TLI of both servers is the
> > same, the configuration after failover always needs to get the base backup
> > on the new master. It's unacceptable for many users. But, I think that it's
> > the role of admin or external tools to copy history files to the slave from
> > the master.
>
> Hmm. There's more problems than the TLI with that. For the original
> master to catch up by replaying WAL from the new slave, without
> restoring from a full backup, the original master must not write to disk
> *any* WAL that hasn't made it to the slave yet. That is certainly not
> true for asynchronous replication, but it also throws off the idea of
> flushing the WAL concurrently to the local disk and to the slave in
> synchronous mode.
>
> I agree that having to get a new base backup to get the old master catch
> up with the new master sucks, so I hope someone sees a way around that.

If we were going to recover from failed-over standby back to original
master just via WAL logs we would need all of the WAL files from the
point of failover. So you'd need to be storing all WAL file just in case
the old master recovers. I can't believe doing that would be the common
case, because its so impractical and most people would run out of disk
space and need to delete WAL files.

It should be clear that to make this work you must run with a base
backup that was derived correctly on the current master. You can do that
by re-copying everything, or you can do that by just shipping changed
blocks (rsync etc). So I don't see a problem in the first place.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2008-09-12 at 00:03 +0900, Fujii Masao wrote:

> In my procedure, old WAL files are copyed by admin using scp, rsync
> or other external tool. So, I don't think that my procedure makes a
> problem more difficult. Since there are many setup cases, we should
> not leave all procedures to postgres, I think.

So the procedure is

1. Startup WALReceiver to begin receiving WAL
2. Do some manual stuff
3. Initiate recovery

So either

* WALReceiver is not started by postmaster.
I don't think its acceptable that WALReceiver is not under the
postmaster. You haven't reduced the number of failure modes by doing
that, you've just swept the problem under the carpet and pretended its
not Postgres' problem.

* Postgres startup requires some form of manual process, as an
**intermediate** stage.

I don't think either of those is acceptable. It must just work.

Why not:
1. Same procedure as Warm Standby now
a) WAL archiving to standby starts
b) base backup

2. Startup standby, with additional option to stream WAL. WALReceiver
starts, connects to Primary. Primary issues log switch. Archiver turns
itself off after sending that last file. WALSender starts streaming
current WAL immediately after log switch.

3. Startup process on standby begins reading WAL from point mentioned by
backup_label. When it gets to last logfile shipped by primary's
archiver, it switches to reading WAL files written by WALReceiver.

So all automatic. Uses existing code. Synchronous replication starts
immediately. Also has the advantage that we do not get WAL bloat on
primary. Configuration is almost identical to current Warm Standby, so
little change for existing Postgres sysadmins.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Simon Riggs wrote:
> If we were going to recover from failed-over standby back to original
> master just via WAL logs we would need all of the WAL files from the
> point of failover. So you'd need to be storing all WAL file just in case
> the old master recovers. I can't believe doing that would be the common
> case, because its so impractical and most people would run out of disk
> space and need to delete WAL files.

Depends on the transaction volume and database size of course. It's
actually not any different from the scenario where the slave goes
offline for some reason. You have the the same decision there of how
long to keep the WAL files in the master, in case the slave wakes up.

I think we'll need an option to specify a maximum for the number of WAL
files to keep around. The DBA should set that to the size of the WAL
drive, minus some safety factor.

> It should be clear that to make this work you must run with a base
> backup that was derived correctly on the current master. You can do that
> by re-copying everything, or you can do that by just shipping changed
> blocks (rsync etc). So I don't see a problem in the first place.

Hmm, built-in rsync capability would be cool. Probably not in the first
phase, though..

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

On Fri, 2008-09-12 at 17:08 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
>
> > It should be clear that to make this work you must run with a base
> > backup that was derived correctly on the current master. You can do that
> > by re-copying everything, or you can do that by just shipping changed
> > blocks (rsync etc). So I don't see a problem in the first place.
>
> Hmm, built-in rsync capability would be cool. Probably not in the first
> phase, though..

We have it for WAL shipping, in form of GUC "archive_command" :)

Why not add full_backup_command ?

--------------
Hannu

On Fri, 2008-09-12 at 17:08 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:

> I think we'll need an option to specify a maximum for the number of WAL
> files to keep around. The DBA should set that to the size of the WAL
> drive, minus some safety factor.
>
> > It should be clear that to make this work you must run with a base
> > backup that was derived correctly on the current master. You can do that
> > by re-copying everything, or you can do that by just shipping changed
> > blocks (rsync etc). So I don't see a problem in the first place.
>
> Hmm, built-in rsync capability would be cool. Probably not in the first
> phase, though..

Built-in? Why? I mean make base backup using rsync. That way only
changed data blocks need be migrated, so much faster.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2008-09-12 at 17:24 +0300, Hannu Krosing wrote:
> On Fri, 2008-09-12 at 17:08 +0300, Heikki Linnakangas wrote:
> > Hmm, built-in rsync capability would be cool. Probably not in the first
> > phase, though..
>
> We have it for WAL shipping, in form of GUC "archive_command" :)
>
> Why not add full_backup_command ?

I see the current design is all master-push centered, i.e. the master is
in control of everything WAL related. That makes it hard to create a
slave which is simply pointed to the server and takes all it's data from
there...

Why not have a design where the slave is in control for it's own data ?
I mean the slave could ask for the base files (possibly through a
special function deployed on the master), then ask for the WAL stream
and so on. That would easily let a slave cascade too, as it could relay
the WAL stream and serve the base backup too... or have a special WAL
repository software with the same interface as a normal master, but
having a choice of base backups and WAL streams. Plus that a slave in
control approach would also allow multiple slaves at the same time for a
given master...

The way it would work would be something like:

* configure the slave with a postgres connection to the master;
* the slave will connect and set up some meta data on the master
identifying itself and telling the master to keep the WAL needed by this
slave, and also get some meta data about the master's details if needed;
* the slave will call a special function on the slave and ask for the
base backup to be streamed (potentially compressed with special
knowledge of postgres internals);
* once the base backup is streamed, or possibly in parallel, ask for
streaming the WAL files;
* when the base backup is finished, start applying the WAL stream, which
is cached in the meantime, and it it's streaming continues;
* keep the master updated about the state of the slave, so the master
can know if it needs to keep the WAL files which were not yet streamed;
* in case of network error, the slave connects again and starts to
stream the WAL from where it was left;
* in case of extended network outage, the master could decide to
unsubscribe the slave when a certain time-out happened;
* when the slave finds itself unsubscribed after a longer disconnection,
it could ask for a new base backup based on differences only... some
kind of built in rsync thingy;

The only downside of this approach is that the slave machine needs a
full postgres super user connection to the master. That could be a
security problem in certain scenarios. The master-centric scenario needs
a connection in the other direction, which might be seen as more secure,
I don't know for sure...

Cheers,
Csaba.

Simon Riggs wrote:
> Built-in? Why? I mean make base backup using rsync. That way only
> changed data blocks need be migrated, so much faster.

Yes, what I meant is that it would be cool to have that functionality
built-in, so that you wouldn't need to configure extra rsync scripts and
authentication etc.

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

Heikki Linnakangas wrote:
> Simon Riggs wrote:
>> Built-in? Why? I mean make base backup using rsync. That way only
>> changed data blocks need be migrated, so much faster.
>
> Yes, what I meant is that it would be cool to have that functionality
> built-in, so that you wouldn't need to configure extra rsync scripts
> and authentication etc.
>

If this were a nice pluggable library I'd agree, but AFAIK it's not, and
I don't see great value in reinventing the wheel.

cheers

andrew

Csaba Nagy wrote:
> Why not have a design where the slave is in control for it's own data ?
> I mean the slave could ask for the base files (possibly through a
> special function deployed on the master), then ask for the WAL stream
> and so on. That would easily let a slave cascade too, as it could relay
> the WAL stream and serve the base backup too... or have a special WAL
> repository software with the same interface as a normal master, but
> having a choice of base backups and WAL streams. Plus that a slave in
> control approach would also allow multiple slaves at the same time for a
> given master...

I totally agree with that.

> The only downside of this approach is that the slave machine needs a
> full postgres super user connection to the master. That could be a
> security problem in certain scenarios.

I think the master-slave protocol needs to be separate from the normal
FE/BE protocol, with commands like "send a new base backup", or
"subscribe to new WAL that's generated". A master-slave connection isn't
associated with any individual database, for example. We can keep the
permissions required for establishing a master-slave connection
different from super-userness. In particular, while the slave will be
able to read all data from the whole cluster, by receiving it in the WAL
and base backups, it doesn't need to be able to modify anything in the
master.

> The master-centric scenario needs
> a connection in the other direction, which might be seen as more secure,
> I don't know for sure...

Which one initiates the connection, the master or slave, is a different
question. I believe we've all assumed that it's the slave that connects
to the master, and I think that makes the most sense.

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

Hi,

Andrew Dunstan wrote:
> If this were a nice pluggable library I'd agree, but AFAIK it's not, and
> I don't see great value in reinventing the wheel.

I certainly agree.

However, I thought of it more like the archive_command, as proposed by
Hannu. That way we don't need to reinvent any wheel and still the
standby could trigger the base data synchronization itself.

Regards

Markus Wanner

On Fri, 2008-09-12 at 17:11 +0200, Csaba Nagy wrote:

> Why not have a design where the slave is in control for it's own data ?
> I mean the slave...

The slave only exists because it is a copy of the master. If you try to
"startup" a slave without first having taken a copy, how would you
bootstrap the slave? With what? To what? It sounds cool, but its not
practical.

I posted a workable suggestion today on another subthread.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, 2008-09-12 at 17:45 +0100, Simon Riggs wrote:
> On Fri, 2008-09-12 at 17:11 +0200, Csaba Nagy wrote:
>
> > Why not have a design where the slave is in control for it's own data ?
> > I mean the slave...
>
> The slave only exists because it is a copy of the master. If you try to
> "startup" a slave without first having taken a copy, how would you
> bootstrap the slave? With what? To what?

As I understand it, Csaba meant that slave would "bootstrap itself" by
connecting to master in some early phase of startup, requesting a
physical filesystem level copy of data, then commencing the startup in
Hot Standby mode.

If done that way, all the slave needs is a superuser level connection to
master database.

Of course this can also be done using little hot standby startup script
from slave, if shell access to master is provided,.

------------------
Hannu

Hannu Krosing escribió:
> On Fri, 2008-09-12 at 17:45 +0100, Simon Riggs wrote:
> > On Fri, 2008-09-12 at 17:11 +0200, Csaba Nagy wrote:
> >
> > > Why not have a design where the slave is in control for it's own data ?
> > > I mean the slave...
> >
> > The slave only exists because it is a copy of the master. If you try to
> > "startup" a slave without first having taken a copy, how would you
> > bootstrap the slave? With what? To what?
>
> As I understand it, Csaba meant that slave would "bootstrap itself" by
> connecting to master in some early phase of startup, requesting a
> physical filesystem level copy of data, then commencing the startup in
> Hot Standby mode.

Interesting ...

This doesn't seem all that difficult -- all you need is to start one
connection to get the WAL stream and save it somewhere; meanwhile a
second connection uses a combination of pg_file_read on master +
pg_file_write on slave to copy the data files over. When this step is
complete, recovery of the stored WAL commences.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, Sep 12, 2008 at 7:41 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Thu, 2008-09-11 at 18:17 +0300, Heikki Linnakangas wrote:
>> Fujii Masao wrote:
>> > I think that this case would often happen. So, we should establish a certain
>> > solution or procedure to the case where TLI of the master doesn't match
>> > TLI of the slave. If we only allow the case where TLI of both servers is the
>> > same, the configuration after failover always needs to get the base backup
>> > on the new master. It's unacceptable for many users. But, I think that it's
>> > the role of admin or external tools to copy history files to the slave from
>> > the master.
>>
>> Hmm. There's more problems than the TLI with that. For the original
>> master to catch up by replaying WAL from the new slave, without
>> restoring from a full backup, the original master must not write to disk
>> *any* WAL that hasn't made it to the slave yet. That is certainly not
>> true for asynchronous replication, but it also throws off the idea of
>> flushing the WAL concurrently to the local disk and to the slave in
>> synchronous mode.
>>
>> I agree that having to get a new base backup to get the old master catch
>> up with the new master sucks, so I hope someone sees a way around that.
>
> If we were going to recover from failed-over standby back to original
> master just via WAL logs we would need all of the WAL files from the
> point of failover. So you'd need to be storing all WAL file just in case
> the old master recovers. I can't believe doing that would be the common
> case, because its so impractical and most people would run out of disk
> space and need to delete WAL files.

No. The original master doesn't need all WAL files. It needs WAL file which
its pg_control points as latest checkpoint location and subsequent files.

> It should be clear that to make this work you must run with a base
> backup that was derived correctly on the current master. You can do that
> by re-copying everything, or you can do that by just shipping changed
> blocks (rsync etc). So I don't see a problem in the first place.

PITR doesn't always need a base backup. We can do PITR from the data
files just after crash if they aren't corrupted (i.e. not media crash).

As the situation demands, most users would like to choose the setup
procedure that bad influence on the cluster is smaller. They would choose
the procedure without a base backup if there are few WAL files to be
replayed. Meanwhile, they would use a base backup if the indispensable
WAL files have already been deleted. But, in that case, they might not take
new base backup and use old one (e.g. taken 2 days before).

Regards,

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

On Fri, Sep 12, 2008 at 12:17 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> Fujii Masao wrote:
>>
>> I think that this case would often happen. So, we should establish a
>> certain
>> solution or procedure to the case where TLI of the master doesn't match
>> TLI of the slave. If we only allow the case where TLI of both servers is
>> the
>> same, the configuration after failover always needs to get the base backup
>> on the new master. It's unacceptable for many users. But, I think that
>> it's
>> the role of admin or external tools to copy history files to the slave
>> from
>> the master.
>
> Hmm. There's more problems than the TLI with that. For the original master
> to catch up by replaying WAL from the new slave, without restoring from a
> full backup, the original master must not write to disk *any* WAL that
> hasn't made it to the slave yet. That is certainly not true for asynchronous
> replication, but it also throws off the idea of flushing the WAL
> concurrently to the local disk and to the slave in synchronous mode.

Yes.

If the master fails after writing WAL to disk and before sending it to
the slave,
at least latest WAL file would be inconsistent between both servers. So,
regardless of using a base backup, in a setup procedure, we need to delete
those inconsistent WAL files or overwrite them.

Regards,

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

Simon Riggs escribió:

> On Fri, 2008-09-12 at 17:08 +0300, Heikki Linnakangas wrote:

> > > It should be clear that to make this work you must run with a base
> > > backup that was derived correctly on the current master. You can do that
> > > by re-copying everything, or you can do that by just shipping changed
> > > blocks (rsync etc). So I don't see a problem in the first place.
> >
> > Hmm, built-in rsync capability would be cool. Probably not in the first
> > phase, though..
>
> Built-in? Why? I mean make base backup using rsync. That way only
> changed data blocks need be migrated, so much faster.

Why rsync? Just compare the LSNs ...

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Simon Riggs escribió:
>> On Fri, 2008-09-12 at 17:08 +0300, Heikki Linnakangas wrote:
>>>> It should be clear that to make this work you must run with a base
>>>> backup that was derived correctly on the current master. You can do that
>>>> by re-copying everything, or you can do that by just shipping changed
>>>> blocks (rsync etc). So I don't see a problem in the first place.
>>> Hmm, built-in rsync capability would be cool. Probably not in the first
>>> phase, though..
>> Built-in? Why? I mean make base backup using rsync. That way only
>> changed data blocks need be migrated, so much faster.
>
> Why rsync? Just compare the LSNs ...

True, that's much better. Only works for data files, though, so we'll
still need something else for clog etc. But the volume of the other
stuff is much smaller, so I support we don't need to bother delta
compressing them.

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

Fujii Masao wrote:
> On Fri, Sep 12, 2008 at 12:17 AM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> Hmm. There's more problems than the TLI with that. For the original master
>> to catch up by replaying WAL from the new slave, without restoring from a
>> full backup, the original master must not write to disk *any* WAL that
>> hasn't made it to the slave yet. That is certainly not true for asynchronous
>> replication, but it also throws off the idea of flushing the WAL
>> concurrently to the local disk and to the slave in synchronous mode.
>
> Yes.
>
> If the master fails after writing WAL to disk and before sending it to
> the slave,
> at least latest WAL file would be inconsistent between both servers. So,
> regardless of using a base backup, in a setup procedure, we need to delete
> those inconsistent WAL files or overwrite them.

And if you're unlucky, the changes in the latest WAL file might already
have been flushed to data files as well.

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

Simon Riggs wrote:
> Why not:
> 1. Same procedure as Warm Standby now
> a) WAL archiving to standby starts
> b) base backup
>
> 2. Startup standby, with additional option to stream WAL. WALReceiver
> starts, connects to Primary. Primary issues log switch. Archiver turns
> itself off after sending that last file. WALSender starts streaming
> current WAL immediately after log switch.
>
> 3. Startup process on standby begins reading WAL from point mentioned by
> backup_label. When it gets to last logfile shipped by primary's
> archiver, it switches to reading WAL files written by WALReceiver.
>
> So all automatic. Uses existing code. Synchronous replication starts
> immediately. Also has the advantage that we do not get WAL bloat on
> primary. Configuration is almost identical to current Warm Standby, so
> little change for existing Postgres sysadmins.

I totally agree. Requiring the master to be down for a significant time
to add a slave isn't going to keep people happy very long. We have the
technology now to allow warm standby slaves by using PITR, and it seems
a similar system can be used to setup slaves, and for cases when the
slave drops off and has to rejoin. The slave can use the existing
'restore_command' command to pull all WAL files it needs, and then the
slave needs to connect to the master and say it is ready for WAL files.
The master is going to need to send perhaps everything from the start of
the existing WAL file so the slave is sure to get all changes during the
switch from 'restore_command' to network-passed WAL info.

I can imagine the slave going in and out of network connectivity as long
as the required PITR files are still available.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Tue, 2008-09-09 at 09:11 +0100, Simon Riggs wrote:

> This gives us the Group Commit feature also, even if we are not using
> replication. So we can drop the commit_delay stuff.
>
> XLogBackgroundFlush() processes data page at a time if it can. That may
> not be the correct batch size for XLogBackgroundSend(), so we may need a
> tunable for the MTU. Under heavy load we need the Write and Send to act
> in a way to maximise throughput rather than minimise response time, as
> we do now.
>
> If wal_buffers overflows, we continue to hold WALInsertLock while we
> wait for WALWriter and WALSender to complete.
>
> We should increase default wal_buffers to 64.
>
> After (or during) XLogInsert backends will sleep in a proc queue,
> similar to LWlocks and protected by a spinlock. When preparing to
> write/send the WAL process should read the proc at the *tail* of the
> queue to see what the next LogwrtRqst should be. Then it performs its
> action and wakes procs up starting with the head of the queue. We would
> add LSN into PGPROC, so WAL processes can check whether the backend
> should be woken. The LSN field can be accessed without spinlocks since
> it is only ever set by the backend itself and only read while a backend
> is sleeping. So we access spinlock, find tail, drop spinlock then read
> LSN of the backend that (was) the tail.

I left off mentioning one other aspect of "Group Commit" behaviour that
is possible with the above design.

If we use a proc queue, then the we only wake up the *first* backend on
the queue. That lets other WAL processes continue quickly.

Reason for doing this is that the first backend can walk the commit
queue collecting xids. When we update the ProcArray we can then update
multiple backend's entries with a single request, rather than forcing
all of the backends to form a queue all queueing for exclusive lock.

When the first backend has updated procarray, then all backends updated
will be released at once.

Doing it that way will significantly reduce the number of exclusive lock
requests for commits, which is the main source of contention on the
procarray.

So that puts in batch setting behaviour for WALWriteLock and
ProcArrayLock. And I'm submitting patch for batch setting of clog
entries around ClogControlLock. So we should get a scalability boost
from all of this.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support