Re: Postgres 9.3 tuning advice

Hello,

On Wed, Aug 13, 2014 at 5:28 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:

> On Wed, Aug 13, 2014 at 9:44 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
> wrote:
> > Set wal_buffers to 16MB so that a whole WAL segment will fit.
>
> No need, wal_buffers is automatically tuned now. If your
> shared_buffers is 512MB or larger, wal_buffers will be 16MB.
>
>
Thanks everyone,

The settings that seem to work well for our workload/hw are

shared_buffers = 2GB
wal_buffers = 16MB
effective_cache_size = 100GB
checkpoint_segments = 32

* With shared_buffers set to default and wal_buffers set to 16MB - bgwriter
stats indicates most writes were due to backend_buffers and not checkpoint
buffers
* With shared_buffers set to 2GB and wal_buffers set to 16MB - bgwriter
stats indicates most writes were due to checkpoint_buffers and not backend
buffers
* Increasing wal_buffers beyond 16mb did not show any improvements

I also found good advice about shared_buffers and wal_buffers on this
slides : http://2ndquadrant.com/media/pdfs/talks/MonitoringBufferCache.pdf

Few aspects that iam confused about are -

a. what is the relation between shared_buffers and wal_buffers when
wal_buffers is set manually ?
b. What else is shared_buffers used for ?
c. Why does increasing shared_buffers to 2GB from default while leaving
wal_buffers constant at 16MB increase checkpoint buffer writes ?

tia
dushy