Replication/backup defaults

On 31 December 2016 at 15:00, Magnus Hagander <magnus@hagander.net> wrote:

Cycling back to this topic again, but this time at the beginning of a CF.

Here's an actual patch to change:

max_wal_senders=10
max_replication_slots=20

+1

If that doesn't fly, it seems easy enough to introduce a
"min_reserve_limit" GUC that defaults to 10 that gives a lower bound
on the amount of memory we reserve for many of those shmem allocs;
that can be set to 0 for people that want the old behaviour. Then we
can allow changes up to the memory limit without a restart.

wal_level=replica

This is more problematic because it changes behaviours.

A more useful approach would be to bring all the things needed to
enable replication into one ALTER SYSTEM command, so people have just
one thing they need to execute and it will work out the details and
locations for you.
That way we can maintain the defaults yet make it easier to enable in
a useful way.

Based on feedback from last year
(/messages/by-id/CABUevEwfV7zDutescm2PHGvsJdYA0RWHFMTRGhwrJPGgSbzZDQ@mail.gmail.com):

There were requests for benchmarks of performance difference. Tomas has
promised to run a couple of benchmarks on his standard benchmarking setups
to give numbers on that. Thanks Tomas, please pipe in with your results when
you have them!

Security considerations about pg_hba.conf -- I avoided those by not actually
changing pg_hba.conf. Since pg_hba.conf can be changed on a reload instead
of a restart it's a lot easier to deal with. I still think changing it to
allow "postgres" the same type of connections as it does for regular users
would not be a security problem, but again thanks to it only needing a
reload it's not as big an issue.

There was the idea to have multiple sets of defaults to choose from at
initdb time. I don't see a problem having that, but it's been another year
and nobody built it. I don't think not having that is an excuse for the
current defaults. And implementing something like that is in no way hindered
by
changing the current defaults.

We were too close to beta1 -- this is why I'm sending it earlier this time
:) (Even though I intended to do it already back in September, better now
than even later)

Finally, there's the argument that we're already shaking up a number of
other things with version 10, so this is a good time to do this one as well.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 2, 2017 at 10:17 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 31 December 2016 at 15:00, Magnus Hagander <magnus@hagander.net> wrote:

Cycling back to this topic again, but this time at the beginning of a CF.

Here's an actual patch to change:

max_wal_senders=10
max_replication_slots=20

+1

If that doesn't fly, it seems easy enough to introduce a
"min_reserve_limit" GUC that defaults to 10 that gives a lower bound
on the amount of memory we reserve for many of those shmem allocs;
that can be set to 0 for people that want the old behaviour. Then we
can allow changes up to the memory limit without a restart.

wal_level=replica

This is more problematic because it changes behaviours.

You can't actually change the other two without changing wal_level.

A more useful approach would be to bring all the things needed to
enable replication into one ALTER SYSTEM command, so people have just
one thing they need to execute and it will work out the details and
locations for you.
That way we can maintain the defaults yet make it easier to enable in
a useful way.

Sure, that would be great - the core being the ability to change these
things without a restart. But I would argue for not letting perfection get
in the way of progress, and do this anyway. I doubt there is any way the
bigger change is going to get done for 10 at this point, so we should give
people the ability to do backups off a default installation already.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 2 January 2017 at 09:21, Magnus Hagander <magnus@hagander.net> wrote:

On Mon, Jan 2, 2017 at 10:17 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 31 December 2016 at 15:00, Magnus Hagander <magnus@hagander.net> wrote:

Cycling back to this topic again, but this time at the beginning of a
CF.

Here's an actual patch to change:

max_wal_senders=10
max_replication_slots=20

+1

If that doesn't fly, it seems easy enough to introduce a
"min_reserve_limit" GUC that defaults to 10 that gives a lower bound
on the amount of memory we reserve for many of those shmem allocs;
that can be set to 0 for people that want the old behaviour. Then we
can allow changes up to the memory limit without a restart.

wal_level=replica

This is more problematic because it changes behaviours.

You can't actually change the other two without changing wal_level.

You could, but we currently disallow it.

A more useful approach would be to bring all the things needed to
enable replication into one ALTER SYSTEM command, so people have just
one thing they need to execute and it will work out the details and
locations for you.
That way we can maintain the defaults yet make it easier to enable in
a useful way.

Sure, that would be great - the core being the ability to change these
things without a restart. But I would argue for not letting perfection get
in the way of progress, and do this anyway. I doubt there is any way the
bigger change is going to get done for 10 at this point, so we should give
people the ability to do backups off a default installation already.

We could fairly easily change wal_level without restart; its been
discussed for many years.

The problem from my perspective is that you're immediately turning off
the performance benefits for initial bulk loads.

Arguing how that isn't a problem looks at least as time consuming as
fixing the problem.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 2, 2017 at 10:32 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 2 January 2017 at 09:21, Magnus Hagander <magnus@hagander.net> wrote:

On Mon, Jan 2, 2017 at 10:17 AM, Simon Riggs <simon@2ndquadrant.com>

wrote:

On 31 December 2016 at 15:00, Magnus Hagander <magnus@hagander.net>

wrote:

Cycling back to this topic again, but this time at the beginning of a
CF.

Here's an actual patch to change:

max_wal_senders=10
max_replication_slots=20

+1

If that doesn't fly, it seems easy enough to introduce a
"min_reserve_limit" GUC that defaults to 10 that gives a lower bound
on the amount of memory we reserve for many of those shmem allocs;
that can be set to 0 for people that want the old behaviour. Then we
can allow changes up to the memory limit without a restart.

wal_level=replica

This is more problematic because it changes behaviours.

You can't actually change the other two without changing wal_level.

You could, but we currently disallow it.

I always assumed we disallowed it because we would have to write actual
code to make it safe.

A more useful approach would be to bring all the things needed to
enable replication into one ALTER SYSTEM command, so people have just
one thing they need to execute and it will work out the details and
locations for you.
That way we can maintain the defaults yet make it easier to enable in
a useful way.

Sure, that would be great - the core being the ability to change these
things without a restart. But I would argue for not letting perfection

get

in the way of progress, and do this anyway. I doubt there is any way the
bigger change is going to get done for 10 at this point, so we should

give

people the ability to do backups off a default installation already.

We could fairly easily change wal_level without restart; its been
discussed for many years.

The problem from my perspective is that you're immediately turning off
the performance benefits for initial bulk loads.

We've had this discussion many times over. Please see for example the
thread I referenced.

The conclusion has been that our defaults should really allow people to
take backups of their systems, and they currently don't.

Making things run faster is tuning, and people should expect to do that if
they need things to run faster. But being able to make a backup is pretty
fundamental.

Arguing how that isn't a problem looks at least as time consuming as

fixing the problem.

Please do submit a patch for it. I don't know exactly what's involved in
that part, I just know that people have been complaining about this at
least since 9.0 was released so our track record of actually fixing it
isn't very good.

I'm not arguing that it's not a problem, btw. I'm arguing that until we can
solve the problem, we're much better off letting people do backups and set
up things like replication than optimizing for a usecase that many never
hit.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 2 January 2017 at 09:39, Magnus Hagander <magnus@hagander.net> wrote:

Please do submit a patch for it.

The way this is supposed to go is someone submits a patch and they
receive feedback, then act on that feedback. If I was able to get away
with deflecting all review comments with a simple "you fix it if you
don't like it" there would be considerably more patches with my name
on it accepted, but probably no further forward in real terms because
of the loose ends it creates.

In this case, simply changing the default will remove a whole class of
performance optimization that we have educated people to expect. I'm
sorry to point this out but removing that will cause many real changes
for people's systems that we will not be thanked for, even though I
understand your reasoning and wish the same goals to be achieved.

I'm willing to assist in a project to allow changing wal_level online
in this release. Please let's follow that path.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 2, 2017 at 10:48 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 2 January 2017 at 09:39, Magnus Hagander <magnus@hagander.net> wrote:

Please do submit a patch for it.

The way this is supposed to go is someone submits a patch and they
receive feedback, then act on that feedback. If I was able to get away
with deflecting all review comments with a simple "you fix it if you
don't like it" there would be considerably more patches with my name
on it accepted, but probably no further forward in real terms because
of the loose ends it creates.

Fair enough.

It's just that people keep saying that this is easy, and have said so for a
long time, but nobody has written a patch for it.

In this case, simply changing the default will remove a whole class of
performance optimization that we have educated people to expect. I'm
sorry to point this out but removing that will cause many real changes
for people's systems that we will not be thanked for, even though I
understand your reasoning and wish the same goals to be achieved.

My claim here is that a lot *fewer* people have come to expect this
performance optimization, than would (quite reasonably) expect that backups
should work on a system without taking it down for restart to reconfigure
it to support that.

I run into that all the time. I hear complaints about that all the time. I
have not heard a single user complain about performance loss after enabling
backups.

And how many people that rely on this optimization don't do any *other*
optimization on their system *anyway*, that would cause them to require a
restart anyway? It's not like we're taking away their ability to enable the
optimization, it's just not on by default.

I'm willing to assist in a project to allow changing wal_level online
in this release. Please let's follow that path.

Sure thing, I will be happy to help test and review such a patch.

I will still argue that the *default* should be wal_level=replica though.
Because once we have such a patch, it's trivial to re-enable this
performance optimization (at the cost of backups and replication).

//Magnus

On 2017-01-02 11:05:05 +0100, Magnus Hagander wrote:

My claim here is that a lot *fewer* people have come to expect this
performance optimization, than would (quite reasonably) expect that backups
should work on a system without taking it down for restart to reconfigure
it to support that.

+1

As evidenced by the fact that a large fraction of those optimizations
are actually currently entirely broken. Without anybody noticing for
years:
http://archives.postgresql.org/message-id/20150702220524.GA9392%40svana.org

Greetings,

Andres Freund

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2 January 2017 at 10:13, Andres Freund <andres@anarazel.de> wrote:

On 2017-01-02 11:05:05 +0100, Magnus Hagander wrote:

My claim here is that a lot *fewer* people have come to expect this
performance optimization, than would (quite reasonably) expect that backups
should work on a system without taking it down for restart to reconfigure
it to support that.

+1

As evidenced by the fact that a large fraction of those optimizations
are actually currently entirely broken. Without anybody noticing for
years:
http://archives.postgresql.org/message-id/20150702220524.GA9392%40svana.org

No, the optimization works, but there is a bug in it that makes it
unsafe, not the same thing as entirely broken. That clearly needs to
be fixed, but it does not prevent the performance benefit, so that
argument is invalid.

We must listen to feedback, not just try to blast through it.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2017-01-02 10:31:28 +0000, Simon Riggs wrote:

We must listen to feedback, not just try to blast through it.

Not agreeing with your priorities isn't "blasting through feedback".

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2 January 2017 at 09:48, Simon Riggs <simon@2ndquadrant.com> wrote:

I'm willing to assist in a project to allow changing wal_level online
in this release. Please let's follow that path.

wal_level looks like one of the easier ones to change without a server restart

There are actions to take in either direction, up or down. My initial
thoughts on the pseudocode would be...

reset wal_level so all new transactions see that value
/* actions after setting new value */
if (old_wal_level < new_wal_level) /* going up */
get list of running transactions (perhaps only those using no-WAL-opt)
else /* coming down */
{
if (old_wal_level == logical)
disconnect logical replication and disallow logical slots
if (new_wal_level == minimal)
disconnect streaming replication and disallow physical slots
}
wait for a checkpoint (fast checkpoint if no other transactions actions active)
if (list)
wait for list of running xacts to complete
wait for a checkpoint (fast checkpoint if no other transactions actions active)
XLogReportParameters()

So it looks easier to go up than down, which is good since that is the
important direction.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2 January 2017 at 09:39, Magnus Hagander <magnus@hagander.net> wrote:

The conclusion has been that our defaults should really allow people to take
backups of their systems, and they currently don't.

Making things run faster is tuning, and people should expect to do that if
they need things to run faster. But being able to make a backup is pretty
fundamental.

In the hope of making things better in 10.0, I remove my objection. If
people want to use wal_level = minimal they can restart their server
and they can find that out in the release notes.

Should we set wal_level = replica or wal_level = logical as the
default for 10.0?

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Jan 2, 2017 at 10:55 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

In the hope of making things better in 10.0, I remove my objection. If
people want to use wal_level = minimal they can restart their server
and they can find that out in the release notes.

Should we set wal_level = replica or wal_level = logical as the
default for 10.0?

replica sounds like a better default to me as most users use at least
archiving. Logical decoding is still fresh though, and its use is not
that wide. Have there been any study on its performance impact
compared to replica by the way?
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

On 12/31/2016 04:00 PM, Magnus Hagander wrote:

Cycling back to this topic again, but this time at the beginning of a CF.

Here's an actual patch to change:

wal_level=replica
max_wal_senders=10
max_replication_slots=20

Based on feedback from last year
(/messages/by-id/CABUevEwfV7zDutescm2PHGvsJdYA0RWHFMTRGhwrJPGgSbzZDQ@mail.gmail.com):

There were requests for benchmarks of performance difference. Tomas
has promised to run a couple of benchmarks on his standard
benchmarking setups to give numbers on that. Thanks Tomas, please
pipe in with your results when you have them!

As promised, I'm running some benchmarks, and I have some early results
to report. And perhaps we can discuss whether we need to test some
additional workloads.

I'm 100% on board with the idea that we should switch to wal_level which
allows taking backups or setting-up a streaming replica, as long as it
does not cause severe performance regression in common workloads.

So while it'd be trivial to construct workloads demonstrating the
optimizations in wal_level=minimal (e.g. initial loads doing CREATE
TABLE + COPY + CREATE INDEX in a single transaction), but that would be
mostly irrelevant I guess.

Instead, I've decided to run regular pgbench TPC-B-like workload on a
bunch of different scales, and measure throughput + some xlog stats with
each of the three wal_level options.

Note: I tweaked the code a bit to allow archiving with "minimal" WAL
level, to allow computing WAL stats on the archived segments (instead of
keeping all segments in the data directory).

As usual, I'm running it on two machines - a small old one (i5-2500k box
with 4 cores and 8GB of RAM) and a new one (2x e5-2620v4 with 16/32
cores, 64GB of RAM). Both machines have SSD-based storage.

The clusters on both machines were reasonably tuned, see 'settings.log'
for each run. The tests are fairly long, covering multiple checkpoints
etc. In other words, the results should be fairly stable.

The scripts/results/stats/configs are available here:

* https://bitbucket.org/tvondra/wal-levels-e2620-v4/src
* https://bitbucket.org/tvondra/wal-levels-i5/src

So far I only have results for the smallest data sets (50 on i5 and 100
on e5), which easily fits into shared_buffers in both cases, and the
numbers look like this:

minimal replica standby
------------------------------------------------
i5-2500k 5884 5896 5873
e5-2620v4 23968 24393 24259

So the performance penalty of replica/standby WAL levels on this
workload is pretty much non-existent - for the larger machine those
levels are actually a tad faster than 'minimal', but the difference is
within 2% (so might easily be noise).

I'll push results for larger ones once those tests complete (possibly
tomorrow).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/03/2017 01:34 PM, Michael Paquier wrote:

On Mon, Jan 2, 2017 at 10:55 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

In the hope of making things better in 10.0, I remove my objection.
If people want to use wal_level = minimal they can restart their
server and they can find that out in the release notes.

Should we set wal_level = replica or wal_level = logical as the
default for 10.0?

replica sounds like a better default to me as most users use at
least archiving. Logical decoding is still fresh though, and its use
is not that wide. Have there been any study on its performance
impact compared to replica by the way?

I've just posted results for some benchmarks I'm running. Those are some
simple pgbench tests, nothing special, and according to those results
the performance impact (logical vs. replica) is negligible. I can run
additional tests with other workloads, of course.

While we can probably construct workloads where the difference is
measurable, I'm not sure performance impact is the main concern here. As
you point out, we have 'replica' since 9.0 effectively, while logical is
much newer, so perhaps there are some hidden bugs? It'd be embarrassing
to pick 'logical' and hurt everyone, even if they don't get any benefit
from wal_level=logical.

So +1 to 'replica' and allowing switching to 'logical' without restart.
That should not be extremely difficult, as the main culprit seems to be
max_wal_senders/max_replication_slots requiring shared memory. But with
'replica' we already have those enabled/allocated, unlike when switching
from 'minimal' to 'replica'.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/31/16 10:00 AM, Magnus Hagander wrote:

max_wal_senders=10
max_replication_slots=20

How about we default max_replication_slots to -1, which means to use the
same value as max_wal_senders?

I think this would address the needs of 99% of users. If we do like you
suggest, there are going to be very many users who forget to adjust
these two values together, and very many who will do it but will be
puzzled and annoyed by it. Since we're now pushing the use of
replication slots even more (your pg_basebackup change, upcoming logical
replication), I think this could be a major source of misconfigurations.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jan 4, 2017 at 3:43 PM, Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:

On 12/31/16 10:00 AM, Magnus Hagander wrote:

max_wal_senders=10
max_replication_slots=20

How about we default max_replication_slots to -1, which means to use the
same value as max_wal_senders?

I think this would address the needs of 99% of users. If we do like you
suggest, there are going to be very many users who forget to adjust
these two values together, and very many who will do it but will be
puzzled and annoyed by it. Since we're now pushing the use of
replication slots even more (your pg_basebackup change, upcoming logical
replication), I think this could be a major source of misconfigurations.

But you don't necessarily want to adjust them together, do you? They are
both capped by max_connections, but I don't think they have any other
direct relation between each other?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 1/4/17 9:46 AM, Magnus Hagander wrote:

How about we default max_replication_slots to -1, which means to use the
same value as max_wal_senders?

But you don't necessarily want to adjust them together, do you? They are
both capped by max_connections, but I don't think they have any other
direct relation between each other?

I think the most usual case is that you use approximately one
replication slot per wal sender slot. So it would be a good default to
make them equal.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 3 January 2017 at 12:34, Michael Paquier <michael.paquier@gmail.com> wrote:

On Mon, Jan 2, 2017 at 10:55 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

In the hope of making things better in 10.0, I remove my objection. If
people want to use wal_level = minimal they can restart their server
and they can find that out in the release notes.

Should we set wal_level = replica or wal_level = logical as the
default for 10.0?

replica sounds like a better default to me as most users use at least
archiving. Logical decoding is still fresh though, and its use is not
that wide. Have there been any study on its performance impact
compared to replica by the way?

Magnus' arguments should also be applied to wal_level = logical since
users will be surprised if they cannot use the logical replication
features we are adding as a main feature of 10.0. Why go through the
same pain again?

And if preventing their use is acceptable for the user, we should
treat it as a performance feature to reduce the wal_level.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 01/03/2017 11:56 PM, Tomas Vondra wrote:

Hi,

...

I'll push results for larger ones once those tests complete (possibly
tomorrow).

I just pushed additional results (from the additional scales) to the git
repositories. On the larger (16/32-cores) machine with 2x e5-2620, the
results look like this

scale minimal replica logical
-----------------------------------------------------
100 23968 24393 24393
1000 23412 23656 23794
10000 5283 5320 5197

and on the smaller one (i5-2500k with 4 cores) I got this:

scale minimal replica logical
-----------------------------------------------------
50 5884 5896 5873
400 5324 5342 5478
1000 5341 5439 5425

The scales were chosen so that the smallest one fits into shared
buffers, the medium exceeds shared buffers but still fits into RAM, and
the largest scale exceeds RAM.

The results seem to confirm that for this workload (regular pgbench),
there's very little difference between the different WAL levels.
Actually, the 'replica' seems a tad faster than 'minimal', but the
difference may be easily due to noise.

I've also looked at the amount of WAL actually produced, by doing
pgbench runs throttled to the same throughput, and counting the number
of archived WAL segments & running pg_xlogdump. Interestingly enough,
those two metrics differ quite a bit - for example for scale 1000 (on
the 32-core machine), the 2h runs produced these number of WAL segments:

minimal: 5515 (88.2GB)
replica: 5587 (89.4GB)
logical: 6058 (96.9GB)

so 'replica' adds ~1.3% and 'logical' ~9.8%. But per pg_xlogdump, the
WAL amounts are only 73.3GB, 73.9GB and 74.4GB - a difference of only
~1.5% between minimal and logical. The values are also much lower than
raw WAL size, so I assume it's because pg_xlogdump ignores some extra
overhead, present in the segments. Moreover, the sequential nature of
WAL writes means even the +10% is not a big deal (unless it results in
saturating the bandwidth, but running on >90% is a bad idea anyway).

My conclusion from these results is that using 'wal_level=replica' by
default seems fine. Perhaps even wal_level=logical would be OK, but
that's probably a too big step for 10.0.

Any ideas how to construct a plausible workload where the differences
are significantly larger? Running the tests on non-SSD storage might
also be useful.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers