Postgres Pro
Downloads
Home   >   mailing lists

Re: Postgres 9.3 tuning advice - Mailing list pgsql-general

From dushy
Subject Re: Postgres 9.3 tuning advice
Date
Msg-id CA+Tzs5Q1xSKUr4m=SZ1Rqy6RiHbfA9NrxgvV5Yw8ENn5VZwqWg@mail.gmail.com
Whole thread Raw
In response to Re: Postgres 9.3 tuning advice  (Marti Raudsepp <marti@juffo.org>)
List pgsql-general
Tree view
Hello,

On Wed, Aug 13, 2014 at 5:28 PM, Marti Raudsepp <marti@juffo.org> wrote:
On Wed, Aug 13, 2014 at 9:44 AM, Albe Laurenz <laurenz.albe@wien.gv.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

pgsql-general by date:

Previous
From: Alban Hertroys
Date:
Subject: Re: Pass where clause to a function
Next
From: Adrian Klaver
Date:
Subject: Re: Trigger function cannot reference field name with capital letter