Postgres 9.3 tuning advice

Discussion:

dushy

2014-08-12 15:18:26 UTC

Hello all,

Iam running a postgresql 9.0.13 master/slave instance in a write heavy
workload.

The hardware is a Dell 720, E5530 - 8 core, 128GB RAM. The database (around
250g with indexes/bloat etc) is sitting on flashcache device with 2 fusion-
io PCIe MLC SSDs as frontend and a MD3200 based RAID10 (14*1TB SATA disks)
as backend. OS is centos 6.2 with kernel 2.6.32-431.20.3.el6.x86_64.

Currently, the performance related configuration is mostly default i,e
shared_buffers,
effective_cache_size. The only directive that seems different is
checkpoint_segments = 96

Iam moving to postgresql 9.3 shortly and planning to tune the above
directives as below..

effective_cache_size = 100GB # free+buffers is pretty consistent around 110
to 120GB and pg_oscache_total is around 80GB consistently
checkpoint_segments = 32 # 96 seems to long and all flushes seem to be only
due to checkpoint_timeout

Additionally iam turning off THB defrag as suggested by some posts on the
lists. Though, My initial pgbench testing doesn't seem to indicate any
issues with THB defrag turned on/off.

Iam not sure about shared_buffers and wal_buffers - iam inclined to leave
them to defaults. But based on this article
(http://rhaas.blogspot.in/2012/03/tuning-sharedbuffers-and-walbuffers.html)
it looks there will be some advantages in tuning it

What would be a good value for shared_buffers and wal_buffers ?

Please let me know if additional information will help.

TIA
dushy

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Albe Laurenz

2014-08-13 06:44:43 UTC

Permalink

Post by dushy
Iam running a postgresql 9.0.13 master/slave instance in a write heavy
workload.
The hardware is a Dell 720, E5530 - 8 core, 128GB RAM. The database (around
250g with indexes/bloat etc) is sitting on flashcache device with 2 fusion-
io PCIe MLC SSDs as frontend and a MD3200 based RAID10 (14*1TB SATA disks)
as backend. OS is centos 6.2 with kernel 2.6.32-431.20.3.el6.x86_64.
Currently, the performance related configuration is mostly default i,e
shared_buffers,
effective_cache_size. The only directive that seems different is
checkpoint_segments = 96
Iam moving to postgresql 9.3 shortly and planning to tune the above
directives as below..
effective_cache_size = 100GB # free+buffers is pretty consistent around 110
to 120GB and pg_oscache_total is around 80GB consistently
checkpoint_segments = 32 # 96 seems to long and all flushes seem to be only
due to checkpoint_timeout
Additionally iam turning off THB defrag as suggested by some posts on the
lists. Though, My initial pgbench testing doesn't seem to indicate any
issues with THB defrag turned on/off.
Iam not sure about shared_buffers and wal_buffers - iam inclined to leave
them to defaults. But based on this article
(http://rhaas.blogspot.in/2012/03/tuning-sharedbuffers-and-walbuffers.html)
it looks there will be some advantages in tuning it
What would be a good value for shared_buffers and wal_buffers ?
Please let me know if additional information will help.

The frequently heard advice for setting shared_buffers is 25% of RAM, but
with memory as big as that that may be too much (it can lead to checkpoint
I/O spikes and greater overhead in managing shared buffers).
Try with something like 8 or 16 GB.
Ideally you should test, use pg_buffercache to inspect shared buffers
and see what setting works best for you.

Set wal_buffers to 16MB so that a whole WAL segment will fit.

The best tuning strategy would be to stuff another 128 GB RAM into
the machine and have your DB in RAM.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/ma

Marti Raudsepp

2014-08-13 11:58:40 UTC

Permalink

Post by Albe Laurenz
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.

Regards,
Marti

--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

dushy

2014-08-14 09:13:52 UTC

Permalink

Hello,

Post by Marti Raudsepp

Post by Albe Laurenz
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