Re: Defaults for replication/backup

On Sun, Feb 14, 2016 at 2:00 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Sat, Feb 13, 2016 at 11:31 AM, Andres Freund <andres(at)anarazel(dot)de>
> wrote:
> > On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
> >> Magnus Hagander <magnus(at)hagander(dot)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.
>

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/