Re: synchronous_commit = apply

[Combining replies to emails from different authors into one message]

On Wed, Sep 2, 2015 at 2:21 PM, Jaime Casanova <
jaime(dot)casanova(at)2ndquadrant(dot)com> wrote:

> On 1 September 2015 at 20:25, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
> wrote:

> As a quick weekend learning exercise/hack I recently went looking into how
> > we could support $SUBJECT. I discovered we already report the apply
> > progress back to the master, and the synchronous waiting facility seemed
> to
> > be all ready to support this. In fact it seemed a little too easy so
> > something tells me it must be wrong! But anyway, please see the attached
> > toy POC patch which does that.
>
> i haven't seen the patch, but probably is as easy as you see it...
> IIRC, Simon proposed a patch for this a few years ago and this was
> actually contempleted from the beggining in the design of SR.
>

Ah, thanks, that certainly explains that. The source code practically had
big arrows pointing to the place to type. I don't want to step on anyone's
toes, so if Simon or anyone else is actively working on this, please let me
know, I'll happily cease and desist.

On Thu, Sep 3, 2015 at 12:35 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Sep 1, 2015 at 9:25 PM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> > The next problem is that the master can be waiting quite a long time for
> a
> > reply from the remote walreceiver containing the desired apply LSN: in
> the
> > best case it learns of apply progress from replies to subsequent
> unrelated
> > records (which might be very soon on a busy system but still involves
> > waiting for the next transaction's WAL flush), and in the worst case it
> > needs to wait for wal_receiver_status_interval (10 seconds by default),
> > which makes for a long COMMIT delay. I was thinking that the solution to
> > that may be to teach StartupLOG to signal the walreceiver after it
> updates
> > XLogCtl->lastReplayedEndRecPtr, which should cause walrcv_receive to be
> > interrupted and return early, and then walreceiver could send a reply if
> it
> > sees that lastReplayedEndRecPtr has moved. Maybe that would generate an
> > unacceptably high frequency of signals, and maybe there is a better form
> of
> > IPC for this.
>
> Yeah, that could be a problem, as could reply volume. If you've got a
> bunch of heap inserts of narrow rows into some table, you don't really
> want to send a reply after each one. That would be a lot of replies,
> and nobody can really care about them anyway, at least not for
> synchronous_commit purposes. But what if you only sent a signal when
> the just-replayed record was a COMMIT record? I suppose that could
> still be a lot of replies on something like a full-tilt pgbench
> workload, but even in that case it would help a lot.
>

Here's a version that does that. It's still ugly POC code for now -- the
flow control in walreceiver.c probably needs a bit of refactoring so it
doesn't have to do the same work in two different places, and it needs some
thought about how it balances time spent write wal and sending replies.
But ... it seems to work for simple tests.

I have also attached a test program. Here are some numbers I measured with
master and standby running on my laptop using that program:

synchronous_commit loops Time TPS
off 10000 0.841s 11890
local 10000 1.869s 5350
remote_write 10000 3.123s 3202
on 10000 3.085s 3241
apply 10000 3.361s 2975

If you run it with "--check" you can see that the changes are not always
immediately visible in anything below "apply" and are always visible in
"apply". (I can't explain why "on" consistently beats "remote_write" on my
machine by a small margin... Maybe something to do with being an assert
build.)

On Thu, Sep 3, 2015 at 12:02 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>
> One idea is to change the standby so that it manages the locations
> that the backends in "apply" mode are waiting for in the master,
> and to make the startup process wake the walreceiver up whenever
> the replay location reaches either of those locations. In this idea,
> walreceiver sends back the "apply" location to the master only when
> needed.
>

Hmm. So maybe commit records could have a flag saying 'someone is waiting
for this to commit to apply', and the startup process's apply loop would
only bother to signal the walreceiver if it sees that flag. I will try
that.

--
Thomas Munro
http://www.enterprisedb.com