On Sun, Feb 14, 2016 at 2:00 AM, Robert Haas <robertmh...@gmail.com> wrote:

> On Sat, Feb 13, 2016 at 11:31 AM, Andres Freund <and...@anarazel.de>
> wrote:
> > On 2016-02-13 11:10:58 -0500, Tom Lane wrote:
> >> Magnus Hagander <mag...@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.

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

I could go with somethin glike

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/

Reply via email to