Re: increasing the default WAL segment size

On Thu, Aug 25, 2016 at 7:45 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Aug 25, 2016 at 1:43 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > On 08/25/2016 01:12 PM, Robert Haas wrote:
> >>> I agree that #4 is best. I'm not sure it's worth the cost. I'm not
> worried
> >>> > at all about the risk of master/slave sync thing, per previous
> statement.
> >>> > But if it does have performance implications, per Andres suggestion,
> then
> >>> > making it configurable at initdb time probably comes with a cost
> that's not
> >>> > worth paying.
> >> At this point it's hard to judge, because we don't have any idea what
> >> the cost might be. I guess if we want to pursue this approach,
> >> somebody will have to code it up and benchmark it. But what I'm
> >> inclined to do for starters is put together a patch to go from 16MB ->
> >> 64MB. Committing that early this cycle will give us time to
> >> reconsider if that turns out to be painful for reasons we haven't
> >> thought of yet. And give tool authors time to make adjustments, if
> >> any are needed.
> >
> > The one thing I'd be worried about with the increase in size is folks
> > using PostgreSQL for very small databases. If your database is only
> > 30MB or so in size, the increase in size of the WAL will be pretty
> > significant (+144MB for the base 3 WAL segments). I'm not sure this is
> > a real problem which users will notice (in today's scales, 144MB ain't
> > much), but if it turns out to be, it would be nice to have a way to
> > switch it back *just for them* without recompiling.
>
> I think you may be forgetting that "the base 3 WAL segments" is no
> longer the default configuration. checkpoint_segments=3 is history;
> we now have max_wal_size=1GB, which is a maximum of 64 WAL segments,
> not 3.
>
>
And obviously that'd be 16 files if we increase the wal segment size. So
the actual maximum size doesn't change, except you can currently set
max_wal_size to something lower than 64Mb. If we change, the minimum value
would become 64Mb, I assume.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/