Re: Hot Standby and handling max_standby_delay

We need to calculate a more accurate time since WAL arrived to make
max_standby_delay sensible in all cases. Difficult to know exactly when
to record new timestamps for received WAL. So, proposal is...

if (Base time is earlier than WAL record time)
standby_delay = WAL record time - Base time
else
standby_delay = now() - Base time

When standby_mode = off we record new base time when a new WAL file
arrives.

When standby_mode = on we record new base time each time we do
XLogWalRcvFlush(). We also record a new base time on first entry to the
main for loop in XLogRecv(), i.e. each time we start writing a new burst
of streamed WAL data.

So in either case, when we are waiting for new input we reset the timer
as soon as new WAL is received. The resolution/accuracy of standby_delay
will be no more than the time taken to replay a single file. This
shouldn't matter, since sane settings of max_standby_delay are either 0
or a number like 5-20 (seconds).

Which means if we are busy we don't record many new times, whereas if we
are working in sporadic bursts we keep up with the latest time of
receipt. This also works when we are performing an archive_recovery for
an old backup.

Startup process will access base time each time it begins to wait and
calculate current standby_delay before comparing against
max_standby_delay.

Comments?

--
Simon Riggs www.2ndQuadrant.com

Simon Riggs wrote:
> We need to calculate a more accurate time since WAL arrived to make
> max_standby_delay sensible in all cases. Difficult to know exactly when
> to record new timestamps for received WAL. So, proposal is...
>
> if (Base time is earlier than WAL record time)
> standby_delay = WAL record time - Base time
> else
> standby_delay = now() - Base time
>
> When standby_mode = off we record new base time when a new WAL file
> arrives.
>
> When standby_mode = on we record new base time each time we do
> XLogWalRcvFlush(). We also record a new base time on first entry to the
> main for loop in XLogRecv(), i.e. each time we start writing a new burst
> of streamed WAL data.
>
> So in either case, when we are waiting for new input we reset the timer
> as soon as new WAL is received. The resolution/accuracy of standby_delay
> will be no more than the time taken to replay a single file. This
> shouldn't matter, since sane settings of max_standby_delay are either 0
> or a number like 5-20 (seconds).

That would change the meaning of max_standby_delay. Currently, it's the
delay between *generating* and applying a WAL record, your proposal
would change it to mean delay between receiving and applying it. That
seems a lot less useful to me.

With the current definition, I would feel pretty comfortable setting it
to say 15 minutes, knowing that if the standby falls behind for any
reason, as soon as the connection is re-established or
archiving/restoring fixed, it will catch up quickly, blowing away any
read-only queries if required. With your new definition, the standby
would in the worst case pause for 15 minutes at every WAL file.

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

On Fri, 2010-01-15 at 20:50 +0200, Heikki Linnakangas wrote:

> > So in either case, when we are waiting for new input we reset the timer
> > as soon as new WAL is received. The resolution/accuracy of standby_delay
> > will be no more than the time taken to replay a single file. This
> > shouldn't matter, since sane settings of max_standby_delay are either 0
> > or a number like 5-20 (seconds).
>
> That would change the meaning of max_standby_delay. Currently, it's the
> delay between *generating* and applying a WAL record, your proposal
> would change it to mean delay between receiving and applying it. That
> seems a lot less useful to me.

Remember that this proposal is about responding to your comments. You
showed that the time difference between generating and applying a WAL
record lacked useful meaning in cases where the generation was not
smooth and continuous. So, taking your earlier refutation as still
observing a problem, I definitely do redefine the meaning of
max_standby_delay. As you say "standby delay" means the difference
between receive and apply.

The bottom line here is: are you willing to dismiss your earlier
observation of difficulties? I don't think you can...

> With the current definition, I would feel pretty comfortable setting it
> to say 15 minutes, knowing that if the standby falls behind for any
> reason, as soon as the connection is re-established or
> archiving/restoring fixed, it will catch up quickly, blowing away any
> read-only queries if required. With your new definition, the standby
> would in the worst case pause for 15 minutes at every WAL file.

Yes, it does. And I know you're thinking along those lines because we
are concurrently discussing how to handle re-connection after updates.

The alternative is this: after being disconnected for 15 minutes we
reconnect. For the next X minutes the standby will be almost unusable
for queries while we catch up again.

---

So, I'm left with thinking that both of these ways are right, in
different circumstances and with different priorities.

If your priority is High Availability, then you are willing to give up
the capability for long-ish queries when that conflicts with the role of
HA server. (delay = apply - generate). If your priority is a Reporting
Server, then you are willing to give up HA capability in return for
relatively uninterrupted querying (delay = apply - receive).

Do we agree the two goals are mutually exclusive? If so, I think we need
another parameter to express those configuration goals.

Also, I think we need some ways to explicitly block recovery to allow
queries to run, and some ways to explicitly block queries so recovery
can run.

Perhaps we need a way to block new queries on a regular basis, so that
recovery gets a chance to run. Kind of time-slicing algorithm, like OS.
That way we could assign a relative priority to each.

Hmmm.

--
Simon Riggs www.2ndQuadrant.com

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Fri, 2010-01-15 at 20:50 +0200, Heikki Linnakangas wrote:
> Yes, it does. And I know you're thinking along those lines because we
> are concurrently discussing how to handle re-connection after updates.

With my State Machine proposal, we could only apply max_standby_delay if
in sync state, and cancel query unconditionally otherwise.

> The alternative is this: after being disconnected for 15 minutes we
> reconnect. For the next X minutes the standby will be almost unusable
> for queries while we catch up again.

That's it. And it could be the cause of another GUC, do we want to give
priority to catching-up to get back in sync, or to running queries. That
would affect to when we apply max_standby_delay, and when set to prefer
running queries it'd apply in any state as soon as we accept connections.

Regards,
--
dim

On Sat, 2010-01-16 at 14:08 +0100, Dimitri Fontaine wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > On Fri, 2010-01-15 at 20:50 +0200, Heikki Linnakangas wrote:
> > Yes, it does. And I know you're thinking along those lines because we
> > are concurrently discussing how to handle re-connection after updates.
>
> With my State Machine proposal, we could only apply max_standby_delay if
> in sync state, and cancel query unconditionally otherwise.
>
> > The alternative is this: after being disconnected for 15 minutes we
> > reconnect. For the next X minutes the standby will be almost unusable
> > for queries while we catch up again.
>
> That's it. And it could be the cause of another GUC, do we want to give
> priority to catching-up to get back in sync, or to running queries. That
> would affect to when we apply max_standby_delay, and when set to prefer
> running queries it'd apply in any state as soon as we accept connections.

Agreed.

I'm wondering if it wouldn't just be easier to put in a plugin for
recovery conflict handling, so the user can decide what to do
themselves. That seems like a better plan than chewing through these
issues now.

--
Simon Riggs www.2ndQuadrant.com

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> I'm wondering if it wouldn't just be easier to put in a plugin for
> recovery conflict handling, so the user can decide what to do
> themselves. That seems like a better plan than chewing through these
> issues now.

Making it a plugin doesn't solve anything. This is not the kind of
thing where people can come up with some random policy and it will
work well. Anyone competent to invent a better policy would be quite
capable of modifying the source to suit themselves.

regards, tom lane

On Fri, 2010-01-15 at 20:50 +0200, Heikki Linnakangas wrote:

> That would change the meaning of max_standby_delay. Currently, it's the
> delay between *generating* and applying a WAL record, your proposal
> would change it to mean delay between receiving and applying it. That
> seems a lot less useful to me.

It would be good if there was a keepalive WAL record with a timestamp on
it generated every N seconds while in streaming mode.

--
Simon Riggs www.2ndQuadrant.com

On Sat, 2010-01-16 at 11:37 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > I'm wondering if it wouldn't just be easier to put in a plugin for
> > recovery conflict handling, so the user can decide what to do
> > themselves. That seems like a better plan than chewing through these
> > issues now.
>
> Making it a plugin doesn't solve anything. This is not the kind of
> thing where people can come up with some random policy and it will
> work well. Anyone competent to invent a better policy would be quite
> capable of modifying the source to suit themselves.

Agreed, with some regrets.

--
Simon Riggs www.2ndQuadrant.com

On Sat, 2010-01-16 at 18:20 +0000, Simon Riggs wrote:
> On Sat, 2010-01-16 at 11:37 -0500, Tom Lane wrote:
> > Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> > > I'm wondering if it wouldn't just be easier to put in a plugin for
> > > recovery conflict handling, so the user can decide what to do
> > > themselves. That seems like a better plan than chewing through these
> > > issues now.
> >
> > Making it a plugin doesn't solve anything. This is not the kind of
> > thing where people can come up with some random policy and it will
> > work well. Anyone competent to invent a better policy would be quite
> > capable of modifying the source to suit themselves.
>
> Agreed, with some regrets.

Although I agree in principle, I have to say that a plugin might make
sense. Yes... the person is good enough to modify the code, but should
they? A plugin allows them to make those decisions without running a
custom code base for core.

Just a thought.

Joshua D. Drake

>
> --
> Simon Riggs www.2ndQuadrant.com
>
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

Simon Riggs wrote:
> On Fri, 2010-01-15 at 20:50 +0200, Heikki Linnakangas wrote:
>
>>> So in either case, when we are waiting for new input we reset the timer
>>> as soon as new WAL is received. The resolution/accuracy of standby_delay
>>> will be no more than the time taken to replay a single file. This
>>> shouldn't matter, since sane settings of max_standby_delay are either 0
>>> or a number like 5-20 (seconds).
>> That would change the meaning of max_standby_delay. Currently, it's the
>> delay between *generating* and applying a WAL record, your proposal
>> would change it to mean delay between receiving and applying it. That
>> seems a lot less useful to me.
>
> Remember that this proposal is about responding to your comments. You
> showed that the time difference between generating and applying a WAL
> record lacked useful meaning in cases where the generation was not
> smooth and continuous.

Yeah, I remember that. What the DBA cares about is the time between a
commit record being generated in the master, and the same record being
applied in the standby. That's easy to explain and tune for, and that's
what max_standby_delay should be. Let's not redefine it into something
less useful just because there's corner cases where we can't calculate
it easily.

The standby would really need to know the timestamp of the *next* commit
record in the WAL. Next from the record that's being applied. We don't
want to peek ahead, and we might not even have received the next commit
record yet even if it's already been generated in master, so we
approximate the timestamp of next commit record with timetamp of
previous commit record.

> It would be good if there was a keepalive WAL record with a timestamp on
> it generated every N seconds while in streaming mode.

Yeah, that would help. In streaming replication we could also send such
timestamp as a separate message, not within WAL.

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

On Sun, 2010-01-17 at 22:57 +0200, Heikki Linnakangas wrote:
>
> > It would be good if there was a keepalive WAL record with a
> timestamp on it generated every N seconds while in streaming mode.
>
> Yeah, that would help. In streaming replication we could also send
> such timestamp as a separate message, not within WAL.

Is that something you're working on?

Do we need a new record type for that, is there a handy record type to
bounce from?

--
Simon Riggs www.2ndQuadrant.com

Simon Riggs wrote:
> On Sun, 2010-01-17 at 22:57 +0200, Heikki Linnakangas wrote:
>>> It would be good if there was a keepalive WAL record with a
>> timestamp on it generated every N seconds while in streaming mode.
>>
>> Yeah, that would help. In streaming replication we could also send
>> such timestamp as a separate message, not within WAL.
>
> Is that something you're working on?

No.

> Do we need a new record type for that, is there a handy record type to
> bounce from?

After starting streaming, slices of WAL are sent as CopyData messages.
The CopyData payload begins with an XLogRecPtr, followed by the WAL
data. That payload format needs to be extended with a 'message type'
field and a new message type for the timestamps need to be added.

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

On Mon, 2010-01-18 at 08:28 +0200, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Sun, 2010-01-17 at 22:57 +0200, Heikki Linnakangas wrote:
> >>> It would be good if there was a keepalive WAL record with a
> >> timestamp on it generated every N seconds while in streaming mode.
> >>
> >> Yeah, that would help. In streaming replication we could also send
> >> such timestamp as a separate message, not within WAL.
> >
> > Is that something you're working on?
>
> No.

How accurate is this now? With regard to remaining items of work.
http://wiki.postgresql.org/wiki/Streaming_Replication

--
Simon Riggs www.2ndQuadrant.com

On Mon, 2010-01-18 at 08:28 +0200, Heikki Linnakangas wrote:

> > Do we need a new record type for that, is there a handy record type to
> > bounce from?
>
> After starting streaming, slices of WAL are sent as CopyData messages.
> The CopyData payload begins with an XLogRecPtr, followed by the WAL
> data. That payload format needs to be extended with a 'message type'
> field and a new message type for the timestamps need to be added.

It wouldn't be a good use of all of our time for me to work on this. I
have zero unallocated time remaining and you'd still need to review what
I'd written, in any case.

--
Simon Riggs www.2ndQuadrant.com

On Mon, Jan 18, 2010 at 5:45 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> How accurate is this now? With regard to remaining items of work.
> http://wiki.postgresql.org/wiki/Streaming_Replication

Not accurate. I'll correct that and provide the link from
"v8.5 Open Items page" to that.

Regards,

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

On Mon, Jan 18, 2010 at 6:35 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Mon, Jan 18, 2010 at 5:45 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> How accurate is this now? With regard to remaining items of work.
>> http://wiki.postgresql.org/wiki/Streaming_Replication
>
> Not accurate. I'll correct that and provide the link from
> "v8.5 Open Items page" to that.

I listed the TODO items that need to be addressed for v8.5.
http://wiki.postgresql.org/wiki/Streaming_Replication#v8.5

If you find any other TODO items, please add them to the list.

Regards,

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

On Mon, 2010-01-18 at 20:18 +0900, Fujii Masao wrote:
> On Mon, Jan 18, 2010 at 6:35 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> > On Mon, Jan 18, 2010 at 5:45 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >> How accurate is this now? With regard to remaining items of work.
> >> http://wiki.postgresql.org/wiki/Streaming_Replication
> >
> > Not accurate. I'll correct that and provide the link from
> > "v8.5 Open Items page" to that.
>
> I listed the TODO items that need to be addressed for v8.5.
> http://wiki.postgresql.org/wiki/Streaming_Replication#v8.5
>
> If you find any other TODO items, please add them to the list.

What were the blockers that prevented sync rep from being included? I
must have missed the discussion on that part.

--
Simon Riggs www.2ndQuadrant.com

Fujii Masao wrote:
> On Mon, Jan 18, 2010 at 6:35 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Mon, Jan 18, 2010 at 5:45 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> How accurate is this now? With regard to remaining items of work.
>>> http://wiki.postgresql.org/wiki/Streaming_Replication
>> Not accurate. I'll correct that and provide the link from
>> "v8.5 Open Items page" to that.
>
> I listed the TODO items that need to be addressed for v8.5.
> http://wiki.postgresql.org/wiki/Streaming_Replication#v8.5
>
> If you find any other TODO items, please add them to the list.

Thanks! That's very useful.

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

Simon Riggs wrote:
> What were the blockers that prevented sync rep from being included? I
> must have missed the discussion on that part.

For one, figuring out how to send back the notifications about WAL
applied in standby, and all the IPC required for that.

Streaming replication is a complex enough patch in just asynchronous
mode. Including synchronous mode would certainly have meant missing 8.5,
we just don't have the resources to review all at once. Even if we did,
splitting the project into smaller increments is a good idea anyway.

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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Simon Riggs wrote:
>> Do we need a new record type for that, is there a handy record type to
>> bounce from?

> After starting streaming, slices of WAL are sent as CopyData messages.
> The CopyData payload begins with an XLogRecPtr, followed by the WAL
> data. That payload format needs to be extended with a 'message type'
> field and a new message type for the timestamps need to be added.

Whether or not anyone bothers with the timestamp message, I think adding
a message type header is a Must Fix item. A protocol with no provision
for extension is certainly going to bite us in the rear before long.

regards, tom lane

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Simon Riggs wrote:
>>> Do we need a new record type for that, is there a handy record type to
>>> bounce from?
>
>> After starting streaming, slices of WAL are sent as CopyData messages.
>> The CopyData payload begins with an XLogRecPtr, followed by the WAL
>> data. That payload format needs to be extended with a 'message type'
>> field and a new message type for the timestamps need to be added.
>
> Whether or not anyone bothers with the timestamp message, I think adding
> a message type header is a Must Fix item. A protocol with no provision
> for extension is certainly going to bite us in the rear before long.

Agreed a message type header is a good idea, although we don't expect
streaming replication and the protocol to work across different major
versions anyway.

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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> Whether or not anyone bothers with the timestamp message, I think adding
>> a message type header is a Must Fix item. A protocol with no provision
>> for extension is certainly going to bite us in the rear before long.

> Agreed a message type header is a good idea, although we don't expect
> streaming replication and the protocol to work across different major
> versions anyway.

Speaking of which, just where is the defense that makes sure that
walsender and walreceiver are compatible? We should be checking not
only version, but all of the configuration variables that are embedded
in pg_control.

regards, tom lane

Tom Lane wrote:
> Speaking of which, just where is the defense that makes sure that
> walsender and walreceiver are compatible? We should be checking not
> only version, but all of the configuration variables that are embedded
> in pg_control.

That happens at startup when pg_control is read, before streaming
starts. Remember that you need to start with a base backup.

We also check that the system_identifier in the standby matches that in
the primary, when the connection is established. That protects you from
starting streaming from wrong base backup.

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