Re: Postgres 9.3 tuning advice

dushy wrote:
> 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