Thread: Defaults for replication/backup
I know we've had many discussions about the defaults, so hey, let's bring out the paint-cans and do the bikeshed all over again.
I would suggest a couple of changes to the default values in parameters related to backup and replication.
The reason for this is to make it "easier to do the right thing by default", meaning the defaults should be more suitable for new users to use our tools in the default config.
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
And in pg_hba.conf, we enable the replication connections by default for the superuser on local/localhost.
This will make it possible to actually take a proper backup (not just a dump) off a default config cluster without having to do a restart first. And it allows you to set up archiving with pg_receivexlog for full PITR.
I think the overhead of the two max parameters is really small, but they're both dependent on the default wal_level. Yes, we've discussed it before. I think it's time to make the change now.
I'm sure there are many more settings we could discuss the defaults for, but this particular biikeshed is mine so I get to decide the initial borders, even if I don't get to decide the colors :P
--
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
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
On Sat, Feb 13, 2016 at 2:39 PM, Andres Freund <andres@anarazel.de> 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.
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.
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.
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
On Sat, Feb 13, 2016 at 4:16 PM, Andres Freund <andres@anarazel.de> 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 <magnus@hagander.net> 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
On Sat, Feb 13, 2016 at 4:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> 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 <magnus@hagander.net> writes:
> On Sat, Feb 13, 2016 at 4:52 PM, Tom Lane <tgl@sss.pgh.pa.us> 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
On 2016-02-13 11:10:58 -0500, Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> 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
On Sat, Feb 13, 2016 at 11:31 AM, Andres Freund <andres@anarazel.de> wrote: > On 2016-02-13 11:10:58 -0500, Tom Lane wrote: >> Magnus Hagander <magnus@hagander.net> 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
On Sun, Feb 14, 2016 at 2:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:
-- I think that there are definitely people for whom #1 is an issue, butOn Sat, Feb 13, 2016 at 11:31 AM, Andres Freund <andres@anarazel.de> wrote:
> On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
>> Magnus Hagander <magnus@hagander.net> 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.
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! :)
On Sun, Feb 14, 2016 at 9:58 AM, Magnus Hagander <magnus@hagander.net> wrote:
On Sun, Feb 14, 2016 at 2:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:I think that there are definitely people for whom #1 is an issue, butOn Sat, Feb 13, 2016 at 11:31 AM, Andres Freund <andres@anarazel.de> wrote:
> On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
>> Magnus Hagander <magnus@hagander.net> 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.
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=ephemeralwhich 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).
On Wed, Apr 20, 2016 at 2:04 PM, Magnus Hagander <magnus@hagander.net> 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
On Thu, Apr 21, 2016 at 7:20 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Wed, Apr 20, 2016 at 2:04 PM, Magnus Hagander <magnus@hagander.net> 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 <magnus@hagander.net> writes:
> On Thu, Apr 21, 2016 at 7:20 PM, Robert Haas <robertmhaas@gmail.com> 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
On Sun, Apr 24, 2016 at 5:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.net> writes:
> On Thu, Apr 21, 2016 at 7:20 PM, Robert Haas <robertmhaas@gmail.com> 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.