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 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/
