Thread: Re: History of WAL_LEVEL (archive vs hot_standby)

Re: History of WAL_LEVEL (archive vs hot_standby)

From
David Johnston
Date:
shamccoy wrote
> Hello.  I've been doing some benchmarks on performance / size differences
> between actions when wal_level is set to either archive or hot_standby. 
> I'm not seeing a ton of difference.  I've read some posts about
> discussions as to whether this parameter should be simplified and remove
> or merge these 2 values.
> 
> I'd like to understand the historic reason between have the extra
> "hot_standby" value.  Was it to introduce replication and not disturb the
> already working "archive" value?  If I'm new to Postgres, is there any
> strategic reason to use "archive" at this point if replication is
> something I'll be using in the future?  I'm not seeing any downside to
> "hot_standby" unless I'm missing something fundamental.
> 
> Thanks,
> Shawn

There is a semantic difference in that "archive" is limited to "wal
shipping" to a dead-drop area for future PITR.  "hot_standby" implies that
the wal is being sent to another running system that is immediately reading
in the information to maintain an exact live copy of the master.

As I think both can be used for PITR I don't believe there is much downside,
technically or with resources, to using hot_standby instead of archive; but
I do not imagine it having any practical benefit either.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/History-of-WAL-LEVEL-archive-vs-hot-standby-tp5797717p5797720.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



Re: History of WAL_LEVEL (archive vs hot_standby)

From
Josh Berkus
Date:
On 03/27/2014 03:06 PM, David Johnston wrote:
> As I think both can be used for PITR I don't believe there is much downside,
> technically or with resources, to using hot_standby instead of archive; but
> I do not imagine it having any practical benefit either.

Actually, "hot_standby" does have to write some extra records to the WAL
which "archive" does not.  I don't know that anyone has checked the
actual volume difference between the two, though, which would probably
also vary by workload.

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



Re: History of WAL_LEVEL (archive vs hot_standby)

From
Noah Misch
Date:
On Thu, Mar 27, 2014 at 03:06:02PM -0700, David Johnston wrote:
> shamccoy wrote
> > Hello.  I've been doing some benchmarks on performance / size differences
> > between actions when wal_level is set to either archive or hot_standby. 
> > I'm not seeing a ton of difference.  I've read some posts about
> > discussions as to whether this parameter should be simplified and remove
> > or merge these 2 values.
> > 
> > I'd like to understand the historic reason between have the extra
> > "hot_standby" value.  Was it to introduce replication and not disturb the
> > already working "archive" value?  

I think so.

> > If I'm new to Postgres, is there any
> > strategic reason to use "archive" at this point if replication is
> > something I'll be using in the future?  I'm not seeing any downside to
> > "hot_standby" unless I'm missing something fundamental.

Probably not.  You might manage to construct a benchmark in which the extra
master-side work is measurable, but it wouldn't be easy.

> There is a semantic difference in that "archive" is limited to "wal
> shipping" to a dead-drop area for future PITR.  "hot_standby" implies that
> the wal is being sent to another running system that is immediately reading
> in the information to maintain an exact live copy of the master.
> 
> As I think both can be used for PITR I don't believe there is much downside,
> technically or with resources, to using hot_standby instead of archive; but
> I do not imagine it having any practical benefit either.

wal_level=archive vs. hot_standby is orthogonal to the mechanism for
transmitting WAL and time of applying WAL.  Rather, it dictates whether a
PostgreSQL cluster replaying that WAL can accept read-only connections during
recovery.  You can send wal_level=archive log data over streaming replication,
even synchronous streaming replication.  However, any standby will be unable
to accept connections until failover ends recovery.  On the flip side, if you
use wal_level=hot_standby, a backup undergoing PITR can accept read-only
connections while applying years-old WAL from an archive.  That is useful for
verifying, before you end recovery, that you have replayed far enough.

-- 
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com



Re: History of WAL_LEVEL (archive vs hot_standby)

From
David Johnston
Date:
Noah Misch-2 wrote
> On Thu, Mar 27, 2014 at 03:06:02PM -0700, David Johnston wrote:
>> shamccoy wrote
>> > Hello.  I've been doing some benchmarks on performance / size
>> differences
>> > between actions when wal_level is set to either archive or hot_standby. 
>> > I'm not seeing a ton of difference.  I've read some posts about
>> > discussions as to whether this parameter should be simplified and
>> remove
>> > or merge these 2 values.
>> > 
>> > I'd like to understand the historic reason between have the extra
>> > "hot_standby" value.  Was it to introduce replication and not disturb
>> the
>> > already working "archive" value?  
> 
> I think so.
> 
>> > If I'm new to Postgres, is there any
>> > strategic reason to use "archive" at this point if replication is
>> > something I'll be using in the future?  I'm not seeing any downside to
>> > "hot_standby" unless I'm missing something fundamental.
> 
> Probably not.  You might manage to construct a benchmark in which the
> extra
> master-side work is measurable, but it wouldn't be easy.
> 
>> There is a semantic difference in that "archive" is limited to "wal
>> shipping" to a dead-drop area for future PITR.  "hot_standby" implies
>> that
>> the wal is being sent to another running system that is immediately
>> reading
>> in the information to maintain an exact live copy of the master.
>> 
>> As I think both can be used for PITR I don't believe there is much
>> downside,
>> technically or with resources, to using hot_standby instead of archive;
>> but
>> I do not imagine it having any practical benefit either.
> 
> wal_level=archive vs. hot_standby is orthogonal to the mechanism for
> transmitting WAL and time of applying WAL.  Rather, it dictates whether a
> PostgreSQL cluster replaying that WAL can accept read-only connections
> during
> recovery.  You can send wal_level=archive log data over streaming
> replication,
> even synchronous streaming replication.  However, any standby will be
> unable
> to accept connections until failover ends recovery.  On the flip side, if
> you
> use wal_level=hot_standby, a backup undergoing PITR can accept read-only
> connections while applying years-old WAL from an archive.  That is useful
> for
> verifying, before you end recovery, that you have replayed far enough.

Went looking for this in the docs...

http://www.postgresql.org/docs/9.3/interactive/runtime-config-wal.html#GUC-WAL-LEVEL

So I guess, no-restore/offline/online would be good names (and maybe
wal_restore_mode instead of wal_level) if we started from scratch.  Note
that no-restore does not preclude same-system recovery.

Just something to help me remember...

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/History-of-WAL-LEVEL-archive-vs-hot-standby-tp5797717p5797733.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



Re: History of WAL_LEVEL (archive vs hot_standby)

From
David Johnston
Date:
David Johnston wrote
> 
> Noah Misch-2 wrote
>> On Thu, Mar 27, 2014 at 03:06:02PM -0700, David Johnston wrote:
>>> shamccoy wrote
>>> > Hello.  I've been doing some benchmarks on performance / size
>>> differences
>>> > between actions when wal_level is set to either archive or
>>> hot_standby. 
>>> > I'm not seeing a ton of difference.  I've read some posts about
>>> > discussions as to whether this parameter should be simplified and
>>> remove
>>> > or merge these 2 values.
>>> > 
>>> > I'd like to understand the historic reason between have the extra
>>> > "hot_standby" value.  Was it to introduce replication and not disturb
>>> the
>>> > already working "archive" value?  
>> 
>> I think so.
>> 
>>> > If I'm new to Postgres, is there any
>>> > strategic reason to use "archive" at this point if replication is
>>> > something I'll be using in the future?  I'm not seeing any downside to
>>> > "hot_standby" unless I'm missing something fundamental.
>> 
>> Probably not.  You might manage to construct a benchmark in which the
>> extra
>> master-side work is measurable, but it wouldn't be easy.
>> 
>>> There is a semantic difference in that "archive" is limited to "wal
>>> shipping" to a dead-drop area for future PITR.  "hot_standby" implies
>>> that
>>> the wal is being sent to another running system that is immediately
>>> reading
>>> in the information to maintain an exact live copy of the master.
>>> 
>>> As I think both can be used for PITR I don't believe there is much
>>> downside,
>>> technically or with resources, to using hot_standby instead of archive;
>>> but
>>> I do not imagine it having any practical benefit either.
>> 
>> wal_level=archive vs. hot_standby is orthogonal to the mechanism for
>> transmitting WAL and time of applying WAL.  Rather, it dictates whether a
>> PostgreSQL cluster replaying that WAL can accept read-only connections
>> during
>> recovery.  You can send wal_level=archive log data over streaming
>> replication,
>> even synchronous streaming replication.  However, any standby will be
>> unable
>> to accept connections until failover ends recovery.  On the flip side, if
>> you
>> use wal_level=hot_standby, a backup undergoing PITR can accept read-only
>> connections while applying years-old WAL from an archive.  That is useful
>> for
>> verifying, before you end recovery, that you have replayed far enough.
> Went looking for this in the docs...
> 
> http://www.postgresql.org/docs/9.3/interactive/runtime-config-wal.html#GUC-WAL-LEVEL
> 
> So I guess, no-restore/offline/online would be good names (and maybe
> wal_restore_mode instead of wal_level) if we started from scratch.  Note
> that no-restore does not preclude same-system recovery.
> 
> Just something to help me remember...
> 
> David J.

Slightly tangential but are the locking operations associated with the
recent bugfix generated in both (all?) modes or only hot_standby?  I thought
it strange that transient locking operations were output with WAL though I
get it if they are there to support read-only queries.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/History-of-WAL-LEVEL-archive-vs-hot-standby-tp5797717p5797735.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



Re: History of WAL_LEVEL (archive vs hot_standby)

From
Kyotaro HORIGUCHI
Date:
Hi,

> > Went looking for this in the docs...
> > 
> > http://www.postgresql.org/docs/9.3/interactive/runtime-config-wal.html#GUC-WAL-LEVEL
> > 
> > So I guess, no-restore/offline/online would be good names (and maybe
> > wal_restore_mode instead of wal_level) if we started from scratch.  Note
> > that no-restore does not preclude same-system recovery.
> > 
> > Just something to help me remember...
> > 
> > David J.
> 
> Slightly tangential but are the locking operations associated with the
> recent bugfix generated in both (all?) modes or only hot_standby?  I thought
> it strange that transient locking operations were output with WAL though I
> get it if they are there to support read-only queries.

Putting aside the naming:), I have caught by the discussion about
the differences of wal records to be emitted among the wal
levels. I grep'ed 'wal_level' for whole backend but all it showed
was for checking of some options in postgresql.conf against other
options in postgresql.conf and that in control file. None of them
seems to care it for the purpose of controlling how/what wal
records to emit or record construction, except for
WAL_LEVEL_LOGICAL.

As far as I could see, I doubt that there is any difference in
emitted wal records amoung wal levels, (except for logical
changeset).

I came to want to try to run streaming replication with wal_level
= minimal but no time for now:(


regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



Re: History of WAL_LEVEL (archive vs hot_standby)

From
Amit Langote
Date:
On Fri, Mar 28, 2014 at 12:16 PM, David Johnston <polobo@yahoo.com> wrote:
>
> Slightly tangential but are the locking operations associated with the
> recent bugfix generated in both (all?) modes or only hot_standby?  I thought
> it strange that transient locking operations were output with WAL though I
> get it if they are there to support read-only queries.
>

IIUC, XLogStandbyInfoActive() is used at places where it is thought
that the WAL record being written at that point would be required on a
standby for correct hot standby operation (comments at these call
sites are helpful).

/* Do we need to WAL-log information required only for Hot Standby and
logical replication? */
#define XLogStandbyInfoActive() (wal_level >= WAL_LEVEL_HOT_STANDBY)

--
Amit



Re: History of WAL_LEVEL (archive vs hot_standby)

From
Noah Misch
Date:
On Thu, Mar 27, 2014 at 08:16:13PM -0700, David Johnston wrote:
> Slightly tangential but are the locking operations associated with the
> recent bugfix generated in both (all?) modes or only hot_standby?

All modes.

> I thought
> it strange that transient locking operations were output with WAL though I
> get it if they are there to support read-only queries.

It is unintuitive.  This comment in heap_lock_tuple() attempts to explain:
/* * XLOG stuff.  You might think that we don't need an XLOG record because * there is no state change worth restoring
aftera crash.  You would be * wrong however: we have just written either a TransactionId or a * MultiXactId that may
neverhave been seen on disk before, and we need * to make sure that there are XLOG entries covering those ID numbers. *
Elsethe same IDs might be re-used after a crash, which would be * disastrous if this page made it to disk before the
crash. Essentially * we have to enforce the WAL log-before-data rule even in this case. * (Also, in a PITR log-shipping
or2PC environment, we have to have XLOG * entries for everything anyway.) */
 

Another reason not mentioned is torn pages.  Locking a tuple updates t_xmax,
t_infomask2 and t_infomask.  It's possible for t_xmax to fall on one side of a
page tear and the infomasks to fall on the other side.  Writing t_xmax without
writing the corresponding infomasks could cause the tuple to be considered
deleted, not merely locked, after a crash.