Re: [HACKERS] Defaults for replication/backup

[Magnus Hagander]

On Sun, Apr 24, 2016 at 5:50 PM, Tom Lane  wrote:

> Magnus Hagander  writes:
> > On Thu, Apr 21, 2016 at 7:20 PM, Robert Haas 
> wrote:
> >> I think we are far too close to beta1 to begin bikeshedding this.
> >> Changing the defaults is not going to be uncontroversial, and it's not
> >> something I think we should rush into.
>
> > I think that may be a case of the good old letting perfection get in the
> > way of progress. We can be pretty sure that whatever we decide to do (now
> > or later) will not be perfect. But we can be equally sure that it will be
> > better than what we have today in most cases. But that's just me...
>
> I'm with Robert.  Such a discussion is morally indistinguishable from a
> new feature, and so we shouldn't consider it at this point in the cycle
> --- even if it seemed likely that we could easily get to a consensus,
> which I doubt.  If we'd dealt with the issue back in February when the
> previous discussion happened, that'd be fine, but now's not the time
> to decide it has to be resolved for 9.6.
>
>

Fair enough, I'll drop it for now. And try to figure out how to move it
along again once we've opened the next branch.


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

Re: [HACKERS] Defaults for replication/backup

[Tom Lane]

Magnus Hagander  writes:
> On Thu, Apr 21, 2016 at 7:20 PM, Robert Haas  wrote:
>> I think we are far too close to beta1 to begin bikeshedding this.
>> Changing the defaults is not going to be uncontroversial, and it's not
>> something I think we should rush into.

> I think that may be a case of the good old letting perfection get in the
> way of progress. We can be pretty sure that whatever we decide to do (now
> or later) will not be perfect. But we can be equally sure that it will be
> better than what we have today in most cases. But that's just me...

I'm with Robert.  Such a discussion is morally indistinguishable from a
new feature, and so we shouldn't consider it at this point in the cycle
--- even if it seemed likely that we could easily get to a consensus,
which I doubt.  If we'd dealt with the issue back in February when the
previous discussion happened, that'd be fine, but now's not the time
to decide it has to be resolved for 9.6.

regards, tom lane


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

Re: [HACKERS] Defaults for replication/backup

[Magnus Hagander]

On Thu, Apr 21, 2016 at 7:20 PM, Robert Haas  wrote:

> On Wed, Apr 20, 2016 at 2:04 PM, Magnus Hagander 
> wrote:
> > So what more do we need to just get going with this? Given feature freeze
> > we're perhaps too late to actually build the parameter feature for
> initdb,
> > but we could still change the defaults (and then we could add such a
> > parameter for next release).
>
> I think we are far too close to beta1 to begin bikeshedding this.
> Changing the defaults is not going to be uncontroversial, and it's not
> something I think we should rush into.
>
>
I think that may be a case of the good old letting perfection get in the
way of progress. We can be pretty sure that whatever we decide to do (now
or later) will not be perfect. But we can be equally sure that it will be
better than what we have today in most cases. But that's just me...

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

Re: [HACKERS] Defaults for replication/backup

[Robert Haas]

On Wed, Apr 20, 2016 at 2:04 PM, Magnus Hagander  wrote:
> So what more do we need to just get going with this? Given feature freeze
> we're perhaps too late to actually build the parameter feature for initdb,
> but we could still change the defaults (and then we could add such a
> parameter for next release).

I think we are far too close to beta1 to begin bikeshedding this.
Changing the defaults is not going to be uncontroversial, and it's not
something I think we should rush into.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Defaults for replication/backup

[Magnus Hagander]

On Sun, Feb 14, 2016 at 9:58 AM, Magnus Hagander 
wrote:

>
>
> On Sun, Feb 14, 2016 at 2:00 AM, Robert Haas 
> wrote:
>
>> On Sat, Feb 13, 2016 at 11:31 AM, Andres Freund 
>> wrote:
>> > On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
>> >> Magnus Hagander  writes:
>> >> > The big thing is, IIRC, that we turn off the optimizations in
>> >> > min_wal_level. *most* people will see no impact of their regular
>> >> > application runtime from that, but it might definitely have an
>> effect on
>> >> > data loads and such. For normal runtime, there should be very close
>> to zero
>> >> > difference, no?
>> >>
>> >> I was asking for a demonstration of that, not just handwaving.  Even if
>> >> it was measured years ago, I wouldn't assume the comparison would be
>> >> the same on current Postgres.
>> >
>> > Well, let's look at what the difference between wal_level's are:
>> > 1) the (currently broken) optimization of not WAL logging COPY et al,
>> >for newly created relations.
>> > 2) relation AccessExclusiveLocks are WAL logged on >= hot_standby
>> > 3) Subtransaction assignment records are generated for >= hot_standby
>> >after 64 records.
>> > 4) checkpoints and bgwriter occasionally generate XLOG_RUNNING_XACTS
>> >records
>> > 5) btreevacuum() and _bt_getbuf() sometimes do additional WAL logging on
>> >>= hot_standby
>> > 6) Once per vacuum we issue a XLOG_HEAP2_CLEANUP_INFO
>> >
>> >
>> > 1) obviously can have performance impact; but only in a relatively
>> > narrow set of cases. I doubt any of the others really play that major a
>> > role.  But I really think minor efficiency differences are besides the
>> > point. Making backups and replication easier has a far bigger positive
>> > impact, for far more users.
>>
>> I think that there are definitely people for whom #1 is an issue, but
>> maybe that's not a sufficient reason not to change the default.
>>
>
> There definitely are people. I'd say most of those would already be tuning
> their config in different ways as well, so changing it is a lot lower cost
> for them. And there's fewer of them.
>
>
>
>> As a thought experiment, how about teaching initdb how to tailor the
>> configuration to a couple of common scenarios via some new flag?  I'll
>> call it --setup although that's probably not best.
>>
>> --setup=replication   --- Preconfigure for replication.
>> --setup=standalone  --- Preconfigure for standalone mode.
>> --setup=ephemeral  --- Preconfigure for an ephemeral instance that
>> doesn't need durability because we'll blow it up soon.
>>
>> Whichever mode we make the default, I think this kind of thing would
>> make life easier for users.
>>
>
> I'd like to reiterate that this is not just about replication, it's even
> more about decent backups. As soon as your database goes to the point that
> pg_dump is not a great solution for backup (and that happens pretty
> quickly, given the performance of pg_restore), the response is "oh, go
> change these 3 parameters in your config and then restart the db
> disconnecting all your users" which gives interesting reactions from
> people...
>
> I could go with somethin glike
> --setup=small
> --setup=normal
> --setup=ephemeral
>
> which would be a more proper mapping I think. Of course, this would also
> give us the ability to bikeshed about three different colors, one in each
> predefined set! :)
>


So what more do we need to just get going with this? Given feature freeze
we're perhaps too late to actually build the parameter feature for initdb,
but we could still change the defaults (and then we could add such a
parameter for next release).

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

Re: [HACKERS] Defaults for replication/backup

[Magnus Hagander]

On Sun, Feb 14, 2016 at 2:00 AM, Robert Haas  wrote:

> On Sat, Feb 13, 2016 at 11:31 AM, Andres Freund 
> wrote:
> > On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
> >> Magnus Hagander  writes:
> >> > The big thing is, IIRC, that we turn off the optimizations in
> >> > min_wal_level. *most* people will see no impact of their regular
> >> > application runtime from that, but it might definitely have an effect
> on
> >> > data loads and such. For normal runtime, there should be very close
> to zero
> >> > difference, no?
> >>
> >> I was asking for a demonstration of that, not just handwaving.  Even if
> >> it was measured years ago, I wouldn't assume the comparison would be
> >> the same on current Postgres.
> >
> > Well, let's look at what the difference between wal_level's are:
> > 1) the (currently broken) optimization of not WAL logging COPY et al,
> >for newly created relations.
> > 2) relation AccessExclusiveLocks are WAL logged on >= hot_standby
> > 3) Subtransaction assignment records are generated for >= hot_standby
> >after 64 records.
> > 4) checkpoints and bgwriter occasionally generate XLOG_RUNNING_XACTS
> >records
> > 5) btreevacuum() and _bt_getbuf() sometimes do additional WAL logging on
> >>= hot_standby
> > 6) Once per vacuum we issue a XLOG_HEAP2_CLEANUP_INFO
> >
> >
> > 1) obviously can have performance impact; but only in a relatively
> > narrow set of cases. I doubt any of the others really play that major a
> > role.  But I really think minor efficiency differences are besides the
> > point. Making backups and replication easier has a far bigger positive
> > impact, for far more users.
>
> I think that there are definitely people for whom #1 is an issue, but
> maybe that's not a sufficient reason not to change the default.
>

There definitely are people. I'd say most of those would already be tuning
their config in different ways as well, so changing it is a lot lower cost
for them. And there's fewer of them.



> As a thought experiment, how about teaching initdb how to tailor the
> configuration to a couple of common scenarios via some new flag?  I'll
> call it --setup although that's probably not best.
>
> --setup=replication   --- Preconfigure for replication.
> --setup=standalone  --- Preconfigure for standalone mode.
> --setup=ephemeral  --- Preconfigure for an ephemeral instance that
> doesn't need durability because we'll blow it up soon.
>
> Whichever mode we make the default, I think this kind of thing would
> make life easier for users.
>

I'd like to reiterate that this is not just about replication, it's even
more about decent backups. As soon as your database goes to the point that
pg_dump is not a great solution for backup (and that happens pretty
quickly, given the performance of pg_restore), the response is "oh, go
change these 3 parameters in your config and then restart the db
disconnecting all your users" which gives interesting reactions from
people...

I could go with somethin glike
--setup=small
--setup=normal
--setup=ephemeral

which would be a more proper mapping I think. Of course, this would also
give us the ability to bikeshed about three different colors, one in each
predefined set! :)

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

Re: [HACKERS] Defaults for replication/backup

[Michael Paquier]

On Sat, Feb 13, 2016 at 10:15 PM, Magnus Hagander wrote:
> So, I suggest the following changes to the defaults:
> wal_level=hot_standby
> max_wal_senders=10
> max_replication_slots=10

10 seems a bit high. I would think that max_wal_senders and
max_replication_slots set at 3 are sufficient enough, giving enough
room for simple deployments willing to use replication.

> And in pg_hba.conf, we enable the replication connections by default for the
> superuser on local/localhost.

+1 on that.
-- 
Michael


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

Re: [HACKERS] Defaults for replication/backup

[Andres Freund]

On 2016-02-13 22:37:33 +0900, Michael Paquier wrote:
> On Sat, Feb 13, 2016 at 10:15 PM, Magnus Hagander wrote:
> > So, I suggest the following changes to the defaults:
> > wal_level=hot_standby
> > max_wal_senders=10
> > max_replication_slots=10

+1. I'm inclined to set slots a bit higher than senders. They're pretty
cheap...

I think this is way past due.

> 10 seems a bit high. I would think that max_wal_senders and
> max_replication_slots set at 3 are sufficient enough, giving enough
> room for simple deployments willing to use replication.

But they're relatively cheap to enable, so why set them low?


Andres


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

Re: [HACKERS] Defaults for replication/backup

[Magnus Hagander]

On Sat, Feb 13, 2016 at 2:39 PM, Andres Freund  wrote:

> On 2016-02-13 22:37:33 +0900, Michael Paquier wrote:
> > On Sat, Feb 13, 2016 at 10:15 PM, Magnus Hagander wrote:
> > > So, I suggest the following changes to the defaults:
> > > wal_level=hot_standby
> > > max_wal_senders=10
> > > max_replication_slots=10
>
> +1. I'm inclined to set slots a bit higher than senders. They're pretty
> cheap...
>

So something like 10/20?



> I think this is way past due.
>
> > 10 seems a bit high. I would think that max_wal_senders and
> > max_replication_slots set at 3 are sufficient enough, giving enough
> > room for simple deployments willing to use replication.
>
> But they're relatively cheap to enable, so why set them low?
>
>
That's my reasoning as well. I think you will be hard pressed to even
measure the overhead between 3 and 10. The big difference will be in
leaving 0 and in particular setting wal_level.

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

Re: [HACKERS] Defaults for replication/backup

[Joshua D. Drake]

On 02/13/2016 05:37 AM, Michael Paquier wrote:

On Sat, Feb 13, 2016 at 10:15 PM, Magnus Hagander wrote:

So, I suggest the following changes to the defaults:
wal_level=hot_standby
max_wal_senders=10
max_replication_slots=10


10 seems a bit high. I would think that max_wal_senders and
max_replication_slots set at 3 are sufficient enough, giving enough
room for simple deployments willing to use replication.


Q. Is there a demonstrable, noticeable downside to 10?
A. No

Great, let's make it easier for everyone.

+1




And in pg_hba.conf, we enable the replication connections by default for the
superuser on local/localhost.


+1 on that.


+1

JD



--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


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

Re: [HACKERS] Defaults for replication/backup

[Joshua D. Drake]

Hello,

I would like to add the idea of having archiving on by default. Not 
everyone uses streaming replication, some people use PITR. The one that 
I see is archive_command and I am not sure how to deal with that.


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


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

Re: [HACKERS] Defaults for replication/backup

[Andres Freund]

Hi,

On 2016-02-13 07:13:55 -0800, Joshua D. Drake wrote:
> I would like to add the idea of having archiving on by default. Not everyone
> uses streaming replication, some people use PITR. The one that I see is
> archive_command and I am not sure how to deal with that.

Since that requires additional setup anyway, I don't really see a
comparable effect of such a move.

Andres


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

Re: [HACKERS] Defaults for replication/backup

[Magnus Hagander]

On Sat, Feb 13, 2016 at 4:16 PM, Andres Freund  wrote:

> Hi,
>
> On 2016-02-13 07:13:55 -0800, Joshua D. Drake wrote:
> > I would like to add the idea of having archiving on by default. Not
> everyone
> > uses streaming replication, some people use PITR. The one that I see is
> > archive_command and I am not sure how to deal with that.
>
> Since that requires additional setup anyway, I don't really see a
> comparable effect of such a move.
>

Also, you can reasonably do PITR without using
archive_command/archive_mode. Just use pg_receivexlog...

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

Re: [HACKERS] Defaults for replication/backup

[Tom Lane]

Magnus Hagander  writes:
> Yes, these changes will increase some of the default overhead. My argument
> against that is that anybody who actually cares about that overhead is
> going to be tuning their database *anyway*, so they can just change things
> back to the old defaults.

> So, I suggest the following changes to the defaults:

> wal_level=hot_standby
> max_wal_senders=10
> max_replication_slots=10

It would be easier to sell this if we had some numbers for the amount of
overhead it would add for people *not* using the features.  I do not think
I've ever seen, eg, pgbench results with different wal_level and all else
the same.

> And in pg_hba.conf, we enable the replication connections by default for
> the superuser on local/localhost.

Potential security implications?

regards, tom lane


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

Re: [HACKERS] Defaults for replication/backup

[Magnus Hagander]

On Sat, Feb 13, 2016 at 4:52 PM, Tom Lane  wrote:

> Magnus Hagander  writes:
> > Yes, these changes will increase some of the default overhead. My
> argument
> > against that is that anybody who actually cares about that overhead is
> > going to be tuning their database *anyway*, so they can just change
> things
> > back to the old defaults.
>
> > So, I suggest the following changes to the defaults:
>
> > wal_level=hot_standby
> > max_wal_senders=10
> > max_replication_slots=10
>
> It would be easier to sell this if we had some numbers for the amount of
> overhead it would add for people *not* using the features.  I do not think
> I've ever seen, eg, pgbench results with different wal_level and all else
> the same.
>

That's going to be extremely workload dependent. For example, I'd expect
the overhead to be very close to 0 on a pgbench SELECT only benchmark :)

The big thing is, IIRC, that we turn off the optimizations in
min_wal_level. *most* people will see no impact of their regular
application runtime from that, but it might definitely have an effect on
data loads and such. For normal runtime, there should be very close to zero
difference, no?




> > And in pg_hba.conf, we enable the replication connections by default for
> > the superuser on local/localhost.
>
> Potential security implications?
>
>
Since we already allow superuser login from that same target, I don't see
any.


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

Re: [HACKERS] Defaults for replication/backup

[Tom Lane]

Magnus Hagander  writes:
> On Sat, Feb 13, 2016 at 4:52 PM, Tom Lane  wrote:
>> It would be easier to sell this if we had some numbers for the amount of
>> overhead it would add for people *not* using the features.  I do not think
>> I've ever seen, eg, pgbench results with different wal_level and all else
>> the same.

> That's going to be extremely workload dependent. For example, I'd expect
> the overhead to be very close to 0 on a pgbench SELECT only benchmark :)

> The big thing is, IIRC, that we turn off the optimizations in
> min_wal_level. *most* people will see no impact of their regular
> application runtime from that, but it might definitely have an effect on
> data loads and such. For normal runtime, there should be very close to zero
> difference, no?

I was asking for a demonstration of that, not just handwaving.  Even if
it was measured years ago, I wouldn't assume the comparison would be
the same on current Postgres.

regards, tom lane


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

Re: [HACKERS] Defaults for replication/backup

[Andres Freund]

On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
> Magnus Hagander  writes:
> > The big thing is, IIRC, that we turn off the optimizations in
> > min_wal_level. *most* people will see no impact of their regular
> > application runtime from that, but it might definitely have an effect on
> > data loads and such. For normal runtime, there should be very close to zero
> > difference, no?
> 
> I was asking for a demonstration of that, not just handwaving.  Even if
> it was measured years ago, I wouldn't assume the comparison would be
> the same on current Postgres.

Well, let's look at what the difference between wal_level's are:
1) the (currently broken) optimization of not WAL logging COPY et al,
   for newly created relations.
2) relation AccessExclusiveLocks are WAL logged on >= hot_standby
3) Subtransaction assignment records are generated for >= hot_standby
   after 64 records.
4) checkpoints and bgwriter occasionally generate XLOG_RUNNING_XACTS
   records
5) btreevacuum() and _bt_getbuf() sometimes do additional WAL logging on
   >= hot_standby
6) Once per vacuum we issue a XLOG_HEAP2_CLEANUP_INFO


1) obviously can have performance impact; but only in a relatively
narrow set of cases. I doubt any of the others really play that major a
role.  But I really think minor efficiency differences are besides the
point. Making backups and replication easier has a far bigger positive
impact, for far more users.

- Andres


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

Re: [HACKERS] Defaults for replication/backup

[Robert Haas]

On Sat, Feb 13, 2016 at 11:31 AM, Andres Freund  wrote:
> On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
>> Magnus Hagander  writes:
>> > The big thing is, IIRC, that we turn off the optimizations in
>> > min_wal_level. *most* people will see no impact of their regular
>> > application runtime from that, but it might definitely have an effect on
>> > data loads and such. For normal runtime, there should be very close to zero
>> > difference, no?
>>
>> I was asking for a demonstration of that, not just handwaving.  Even if
>> it was measured years ago, I wouldn't assume the comparison would be
>> the same on current Postgres.
>
> Well, let's look at what the difference between wal_level's are:
> 1) the (currently broken) optimization of not WAL logging COPY et al,
>for newly created relations.
> 2) relation AccessExclusiveLocks are WAL logged on >= hot_standby
> 3) Subtransaction assignment records are generated for >= hot_standby
>after 64 records.
> 4) checkpoints and bgwriter occasionally generate XLOG_RUNNING_XACTS
>records
> 5) btreevacuum() and _bt_getbuf() sometimes do additional WAL logging on
>>= hot_standby
> 6) Once per vacuum we issue a XLOG_HEAP2_CLEANUP_INFO
>
>
> 1) obviously can have performance impact; but only in a relatively
> narrow set of cases. I doubt any of the others really play that major a
> role.  But I really think minor efficiency differences are besides the
> point. Making backups and replication easier has a far bigger positive
> impact, for far more users.

I think that there are definitely people for whom #1 is an issue, but
maybe that's not a sufficient reason not to change the default.

As a thought experiment, how about teaching initdb how to tailor the
configuration to a couple of common scenarios via some new flag?  I'll
call it --setup although that's probably not best.

--setup=replication   --- Preconfigure for replication.
--setup=standalone  --- Preconfigure for standalone mode.
--setup=ephemeral  --- Preconfigure for an ephemeral instance that
doesn't need durability because we'll blow it up soon.

Whichever mode we make the default, I think this kind of thing would
make life easier for users.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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