Re: Defaults for replication/backup

On Sun, Feb 14, 2016 at 9:58 AM, Magnus Hagander <magnus(at)hagander(dot)net>
wrote:

>
>
> 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! :)
>

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/