Обсуждение: Sending notifications from the master to the standby

People have always expressed interest in $subject, so I wondered how
hard it could possibly be and came up with the attached patch.

Notifications that are generated on the master and are forwarded to
the standby can be used as a convenient way to find out which changes
have already made it to the standby. The idea would be that you run a
transaction on the master, add a "NOTIFY changes_made", and listen on
the standby for this event. Once it gets delivered, you know that your
transaction got replayed to the standby.

Note that this feature is only about LISTEN on the standby, it still
doesn't allow sending NOTIFYs out from the standby.

As a reminder, the current implementation of notifications
(LISTEN/NOTIFY) in a few words is:

- a transaction that executes "NOTIFY channel, payload" adds the
transaction to backend-local memory
- upon commit, it inserts the notifications along with its transaction
id into a large SLRU mapped ring buffer and signals any listening
backend

- each backend that's listening has a pointer into this ring buffer.
After each transaction, the backend starts reading from this pointer
position to the end of the ring buffer. It delivers all matching
notifications to its frontend if the transaction that has inserted
them is known to have committed.

In the patch I added a new WAL message type, XLOG_NOTIFY that writes
out WAL records when the notifications are written into the pages of
the SLRU ring buffer. Whenever an SLRU page is found to be full, a new
WAL record will be created, that's just a more or less arbitrary form
of batching a bunch of them together but that's easy to do and most
often, I think there won't be more than at most a few record per
transaction anyway.

The recovery process on the client side adds the notifications into
the standby's SLRU ring buffer and once the last notification has been
added (which might be after a couple more WAL records), it signals the
listening backends.

Theoretically we could also run into a full queue situation on the
standby: Imagine a long-running transaction doesn't advance its
pointer in the ring buffer and no new notifications can be stored in
the buffer. The patch introduces a new type of recovery conflict for
this reason.

One further optimization (that is not included for now) would be to
keep track of how many backends are actually listening on some channel
and if nobody is listening, discard incoming notifications.

Joachim Wieland <joe@mcknight.de> writes:
> [ send NOTIFYs to slaves by means of: ]
> In the patch I added a new WAL message type, XLOG_NOTIFY that writes
> out WAL records when the notifications are written into the pages of
> the SLRU ring buffer. Whenever an SLRU page is found to be full, a new
> WAL record will be created, that's just a more or less arbitrary form
> of batching a bunch of them together but that's easy to do and most
> often, I think there won't be more than at most a few record per
> transaction anyway.

I'm having a hard time wrapping my mind around why you'd do it that way.
ISTM there are two fairly serious problems:

1. Emitting WAL records for NOTIFY traffic results in significantly
more overhead, with no benefit whatever, for existing non-replicated
NOTIFY-using applications.  Those folk are going to see a performance
degradation, and they're going to complain.

2. Batching NOTIFY traffic will result in a delay in receipt, which will
annoy anybody who's trying to make actual use of the notifications on
standby servers.  The worst case here happens if notify traffic on the
master is bursty: the last few messages in a burst might not get to the
slave for a long time, certainly long after the commits that the
messages were supposed to be telling people about.

So this design is non-optimal both for existing uses and for the
proposed new uses, which means nobody will like it.  You could
ameliorate #1 by adding a GUC that determines whether NOTIFY actually
writes WAL, but that's pretty ugly.  In any case ISTM that problem #2
means this design is basically broken.


I wonder whether it'd be practical to not involve WAL per se in this
at all, but to transmit NOTIFY messages by having walsender processes
follow the notify stream (as though they were listeners) and send the
notify traffic as a separate message stream interleaved with the WAL
traffic.  We already have, as of a few days ago, the concept of
additional traffic in the walsender stream besides the WAL data itself,
so adding notify traffic as another message type should be
straightforward.  It might be a bit tricky to get walreceivers to inject
the data into the slave-side ring buffer at the right time, ie, not
until after the commit a given message describes has been replayed;
but I don't immediately see a reason to think that's infeasible.

Going in this direction would mean that slave-side LISTEN only works
when using walsender/walreceiver, and not with old-style log shipping.
But personally I don't see a problem with that.  If you're trying to
LISTEN you probably want pretty up-to-date data anyway.
        regards, tom lane

On Tue, Jan 10, 2012 at 5:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joachim Wieland <joe@mcknight.de> writes:
>> [ send NOTIFYs to slaves by means of: ]

Good idea.

> I wonder whether it'd be practical to not involve WAL per se in this
> at all, but to transmit NOTIFY messages by having walsender processes
> follow the notify stream (as though they were listeners) and send the
> notify traffic as a separate message stream interleaved with the WAL
> traffic.  We already have, as of a few days ago, the concept of
> additional traffic in the walsender stream besides the WAL data itself,
> so adding notify traffic as another message type should be
> straightforward.

Also good idea.

> It might be a bit tricky to get walreceivers to inject
> the data into the slave-side ring buffer at the right time, ie, not
> until after the commit a given message describes has been replayed;
> but I don't immediately see a reason to think that's infeasible.

When transaction commits it would use full-size commit records and set
a (new) flag in xl_xact_commit.xinfo to show the commit is paired with
notify traffic.

Get messages in walreceiver.c XLogWalRcvProcessMsg() and put them in a
shared hash table. Messages would need to contain xid of notifying
transaction and other info needed for LISTEN.

When we hit xact.c xact_redo_commit() on standby we'd check for
messages in the hash table if the notify flag is set and execute the
normal notify code as if the NOTIFY had run locally on the standby. We
can sweep the hash table clean of any old messages each time we run
ProcArrayApplyRecoveryInfo()

Add new message type to walprotocol.h. Message code 'L' appears to be
available.

Suggest we add something to initial handshake from standby to say
"please send me notify traffic", which we can link to a parameter that
defines size of standby_notify_buffer. We don't want all standbys to
receive such traffic unless they really want it and pg_basebackup
probably doesn't want it either.

If you wanted to get really fancy you could send only some of the
traffic to each standby based on a hash or roundrobin algorithm, so we
can spread the listeners across multiple standbys.

I'll be your reviewer, if you want.

> Going in this direction would mean that slave-side LISTEN only works
> when using walsender/walreceiver, and not with old-style log shipping.
> But personally I don't see a problem with that.  If you're trying to
> LISTEN you probably want pretty up-to-date data anyway.

Which fits the expected use case also.

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

On Tue, Jan 10, 2012 at 12:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> So this design is non-optimal both for existing uses and for the
> proposed new uses, which means nobody will like it.  You could
> ameliorate #1 by adding a GUC that determines whether NOTIFY actually
> writes WAL, but that's pretty ugly.  In any case ISTM that problem #2
> means this design is basically broken.

I chose to do it this way because it seemed like the most natural way
to do it (which of course doesn't mean it's the best)  :-). I agree
that there should be a way to avoid the replication of the NOTIFYs.
Regarding your second point though, remember that on the master we
write notifications to the queue in pre-commit. And we also don't
interleave notifications of different transactions. So once the commit
record makes it to the standby, all the notifications are already
there, just as on the master. In a burst of notifications, both
solutions should more or less behave the same way but yes, the one
involving the WAL file would be slower as it goes to the file system
and back.

> I wonder whether it'd be practical to not involve WAL per se in this
> at all, but to transmit NOTIFY messages by having walsender processes
> follow the notify stream (as though they were listeners) and send the
> notify traffic as a separate message stream interleaved with the WAL
> traffic.

Agreed, having walsender/receiver work as NOTIFY proxies is kinda smart...


Joachim

On Tue, Jan 10, 2012 at 12:56 PM, Joachim Wieland <joe@mcknight.de> wrote:

> I chose to do it this way because it seemed like the most natural way
> to do it (which of course doesn't mean it's the best)  :-).

If its any consolation its exactly how I would have done it also up
until about 2 months ago, and I remember discussing almost exactly the
design you presented with someone in Rome last year.

Anyway its a good feature, so I hope you have time.

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

Simon Riggs <simon@2ndQuadrant.com> writes:
> On Tue, Jan 10, 2012 at 5:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It might be a bit tricky to get walreceivers to inject
>> the data into the slave-side ring buffer at the right time, ie, not
>> until after the commit a given message describes has been replayed;
>> but I don't immediately see a reason to think that's infeasible.

> [ Simon sketches a design for that ]

Seems a bit overcomplicated.  I was just thinking of having walreceiver
note the WAL endpoint at the instant of receipt of a notify message,
and not release the notify message to the slave ring buffer until WAL
replay has advanced that far.  You'd need to lay down ground rules about
how the walsender times the insertion of notify messages relative to
WAL in its output.  But I don't see the need for either explicit markers
in the WAL stream or a hash table.  Indeed, a hash table scares me
because it doesn't clearly guarantee that notifies will be released in
arrival order.

> Suggest we add something to initial handshake from standby to say
> "please send me notify traffic",

+1 on that.
        regards, tom lane

On Tue, Jan 10, 2012 at 4:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On Tue, Jan 10, 2012 at 5:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> It might be a bit tricky to get walreceivers to inject
>>> the data into the slave-side ring buffer at the right time, ie, not
>>> until after the commit a given message describes has been replayed;
>>> but I don't immediately see a reason to think that's infeasible.
>
>> [ Simon sketches a design for that ]
>
> Seems a bit overcomplicated.  I was just thinking of having walreceiver
> note the WAL endpoint at the instant of receipt of a notify message,
> and not release the notify message to the slave ring buffer until WAL
> replay has advanced that far.  You'd need to lay down ground rules about
> how the walsender times the insertion of notify messages relative to
> WAL in its output.

You have to store the messages somewhere until they're needed. If that
somewhere isn't on the standby, very close to the Startup process then
its going to be very slow. Putting a marker in the WAL stream
guarantees arrival order. The hash table was just a place to store
them until they're needed, could be a ring buffer as well.

Inserts into the slave ring buffer already have an xid on them, so the
test will probably already cope with messages inserted but for which
the parent xid has not committed. The only problem is coping with
possible out of sequence messages.

> But I don't see the need for either explicit markers
> in the WAL stream or a hash table.  Indeed, a hash table scares me
> because it doesn't clearly guarantee that notifies will be released in
> arrival order.

The hash table is clearly not the thing providing an arrival order
guarantee, it was just a cache.

You have a few choices: (1) you either send the message while holding
an exclusive lock, or (2) you send them as they come and buffer them,
then reorder them using the WAL log sequence since that matches the
original commit sequence. Or (3) add a sequence number to the messages
sent by WALSender, so that the WALReceiver can buffer them locally and
insert them in the correct order into the normal ring buffer - so in
(3) the message sequence and the WAL sequence match, but the mechanism
is different.

(1) is out because the purpose of offloading to the standby is to give
the master more capcity. If we slow it down in order to serve the
standby we're doing things the wrong way around.

I was choosing (2), maybe you prefer (3) or another design entirely.
They look very similar to me and about the same complexity, its just
copying data and preserving sequence.

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

On Tue, Jan 10, 2012 at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> [ Tom sketches a design ]
> Seems a bit overcomplicated.  I was just thinking of having walreceiver
> note the WAL endpoint at the instant of receipt of a notify message,
> and not release the notify message to the slave ring buffer until WAL
> replay has advanced that far.

How about this: We mark a notify message specially if it is the last
message sent by a transaction and also add a flag to
commit/abort-records, indicating whether or not the transaction has
sent notifys. Now if such a last message is being put into the regular
ring buffer on the standby and the xid is known to have committed or
aborted, signal the backends. Also signal from a commit/abort-record
if the flag is set.

If the notify messages make it to the standby first, we just put
messages of a not-yet-committed transaction into the queue, just as on
the master. Listeners will get signaled when the commit record
arrives. If the commit record arrives first, we signal, but the
listeners won't find anything (at least not the latest notifications).
When the last notify of that transaction finally arrives, the
transaction is known to have committed and the listeners will get
signaled.

What could still happen is that the standby receives notifys, the
commit message and more notifys. Listeners would still eventually get
all the messages but potentially not all of them at once. Is this a
problem? If so, then we could add a special "stop reading"-record into
the queue before we write the notifys, that we subsequently change
into a "continue reading"-record once all notifications are in the
queue. Readers would treat a "stop reading" record just like a
not-yet-committed transaction and ignore a "continue reading" record.


>> Suggest we add something to initial handshake from standby to say
>> "please send me notify traffic",
>
> +1 on that.

From what you said I imagined this walsender listener as a regular
listener that listens on the union of all sets of channels that
anybody is listening on on the standby, with the LISTEN transaction on
the standby return from commit once the listener is known to have been
set up on the master.


Joachim

Joachim Wieland <joe@mcknight.de> writes:
> On Tue, Jan 10, 2012 at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Simon Riggs <simon@2ndQuadrant.com> writes:
>>> Suggest we add something to initial handshake from standby to say
>>> "please send me notify traffic",

>> +1 on that.

> From what you said I imagined this walsender listener as a regular
> listener that listens on the union of all sets of channels that
> anybody is listening on on the standby, with the LISTEN transaction on
> the standby return from commit once the listener is known to have been
> set up on the master.

This seems vastly overcomplicated too.  I'd just vote for a simple
yes/no flag, so that receivers that have no interest in notifies don't
have to deal with them.
        regards, tom lane

BTW ... it occurs to me to ask whether we really have a solid use-case
for having listeners attached to slave servers.  I have personally never
seen an application for LISTEN/NOTIFY in which the listeners were
entirely read-only.  Even if there are one or two cases out there, it's
not clear to me that supporting it is worth the extra complexity that
seems to be needed.
        regards, tom lane

On Wed, Jan 11, 2012 at 4:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> BTW ... it occurs to me to ask whether we really have a solid use-case
> for having listeners attached to slave servers.  I have personally never
> seen an application for LISTEN/NOTIFY in which the listeners were
> entirely read-only.  Even if there are one or two cases out there, it's
> not clear to me that supporting it is worth the extra complexity that
> seems to be needed.

The idea is to support external caches that re-read the data when it changes.

If we can do that from the standby then we offload from the master.

Yes, there are other applications for LISTEN/NOTIFY and we wouldn't be
able to support them all with this.

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

Tom,

> BTW ... it occurs to me to ask whether we really have a solid use-case
> for having listeners attached to slave servers.  I have personally never
> seen an application for LISTEN/NOTIFY in which the listeners were
> entirely read-only.  Even if there are one or two cases out there, it's
> not clear to me that supporting it is worth the extra complexity that
> seems to be needed.

Actually, I've seen requests for it from my clients and on IRC.  Not
sure how serious those are, but users have brought it up.  Certainly
users intuitively think they should be able to LISTEN on a standby, and
are surprised when they find out they can't.

The basic idea is that if we can replicate LISTENs, then you can use
replication as a simple distributed (and lossy) queueing system.  This
is especially useful if the replica is geographically distant, and there
are a lot of listeners.

The obvious first use case for this is for cache invalidation.  For
example, we have one application where we're using Redis to queue cache
invalidation messages; if LISTEN/NOTIFY were replicated, we could use it
instead and simplify our infrastructure.

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

Josh Berkus <josh@agliodbs.com> writes:
>> BTW ... it occurs to me to ask whether we really have a solid use-case
>> for having listeners attached to slave servers.  I have personally never
>> seen an application for LISTEN/NOTIFY in which the listeners were
>> entirely read-only.  Even if there are one or two cases out there, it's
>> not clear to me that supporting it is worth the extra complexity that
>> seems to be needed.

> The basic idea is that if we can replicate LISTENs, then you can use
> replication as a simple distributed (and lossy) queueing system.

Well, this is exactly what I don't believe.  A queueing system requires
that recipients be able to remove things from the queue.  You can't do
that on a slave server, because you can't make any change in the
database that would be visible to other users.

> The obvious first use case for this is for cache invalidation.

Yeah, upthread Simon pointed out that propagating notifies would be
useful for flushing caches in applications that watch the database in a
read-only fashion.  I grant that such a use-case is technically possible
within the limitations of a slave server; I'm just dubious that it's a
sufficiently attractive use-case to justify the complexity and future
maintenance costs of the sort of designs we are talking about.  Or in
other words: so far, cache invalidation is not the "first" use-case,
it's the ONLY POSSIBLE use-case.  That's not useful enough.
        regards, tom lane

> Yeah, upthread Simon pointed out that propagating notifies would be
> useful for flushing caches in applications that watch the database in a
> read-only fashion.  I grant that such a use-case is technically possible
> within the limitations of a slave server; I'm just dubious that it's a
> sufficiently attractive use-case to justify the complexity and future
> maintenance costs of the sort of designs we are talking about.  Or in
> other words: so far, cache invalidation is not the "first" use-case,
> it's the ONLY POSSIBLE use-case.  That's not useful enough.

Well, cache invalidation is a pretty common task; probably more than 50%
of all database applications need to do it.  Note that we're not just
talking about memcached for web applications here.  For example, one of
the companies quoted for PostgreSQL 9.0 release uses LISTEN/NOTIFY to
inform remote devices (POS systems) that there's new data available for
them. That's a form of cache invalidation.  It's certainly a more common
design pattern than using XML in the database.

However, there's the question of whether or not this patch actually
allows a master-slave replication system to support more Listeners more
efficiently than having them all simply listen to the master.  And what
impact it has on the performance of LISTEN/NOTIFY on standalone systems.

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

On 11 January 2012 23:51, Josh Berkus <josh@agliodbs.com> wrote:
>
>> Yeah, upthread Simon pointed out that propagating notifies would be
>> useful for flushing caches in applications that watch the database in a
>> read-only fashion.  I grant that such a use-case is technically possible
>> within the limitations of a slave server; I'm just dubious that it's a
>> sufficiently attractive use-case to justify the complexity and future
>> maintenance costs of the sort of designs we are talking about.  Or in
>> other words: so far, cache invalidation is not the "first" use-case,
>> it's the ONLY POSSIBLE use-case.  That's not useful enough.
>
> Well, cache invalidation is a pretty common task; probably more than 50%
> of all database applications need to do it.

I agree that it would be nice to support this type of cache
invalidation - without commenting on the implementation, I think that
the concept is very useful, and of immediate benefit to a significant
number of people.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On Wed, Jan 11, 2012 at 11:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> The obvious first use case for this is for cache invalidation.
>
> Yeah, upthread Simon pointed out that propagating notifies would be
> useful for flushing caches in applications that watch the database in a
> read-only fashion.  I grant that such a use-case is technically possible
> within the limitations of a slave server; I'm just dubious that it's a
> sufficiently attractive use-case to justify the complexity and future
> maintenance costs of the sort of designs we are talking about.  Or in
> other words: so far, cache invalidation is not the "first" use-case,
> it's the ONLY POSSIBLE use-case.  That's not useful enough.

Many people clearly do think this is useful.

I personally don't think it will be that complex. I'm willing to
review and maintain it if the patch works the way we want it to.

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

> Many people clearly do think this is useful.

It also comes under the heading of "avoiding surprising behavior".  That
is, users instinctively expect to be able to LISTEN on standbys, and are
surprised when they can't.

> I personally don't think it will be that complex. I'm willing to
> review and maintain it if the patch works the way we want it to.
> 

I think we need some performance testing for the review for it to be valid.

1) How does this patch affect the speed and throughput of LISTEN/NOTIFY
on a standalone server?

2) Can we actually attach more LISTENers to multiple standbys than we
could to a single Master?

Unfortunately, I don't have an application which can LISTEN in a way
which doesn't eclipse any differences in througput or response time we
would see on the DB side.  Does anyone?

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