Re: auto-sizing wal_buffers

On Sun, Jan 16, 2011 at 9:32 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> I think we can be more specific on that last sentence; is there even any
>> *theoretical* benefit to settings above 16MB, the size of a WAL segment?
>
> IIRC there's a forced fsync at WAL segment switch, so no.

However other backends can still do WAL inserts while that fsync
takes place, as long as they can find available buffers to write into.
So that should not be too limiting--a larger wal_buffers make it more
likely they will find available buffers.

However if the background writer does not keep up under bulk loading
conditions, then the end of segment fsync will probably happen via
AdvanceXLInsertBuffer, which will be sitting on the WALInsertLock. So
that is obviously bad news.

Cheers,

Jeff