Re: Postgresql 9.3 tuning advice

Genereal advice is to set up shared_buffers to 25% of total RAM. 75% RAM
for OS cache.
On my case (1.5 TB database, 145 GB RAM), setting shared_buffers bigger
than 8GB would give no significant performance impact.
On some cases, setting it low would be an advantage
http://www.depesz.com/2007/12/05/shared-buffers-and-their-impact-on-performance/

On Tue, Aug 12, 2014 at 10:25 PM, dushy <dushyanth(at)gmail(dot)com> wrote:

> 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
> checkpoint_segments = 32 # 96 seems to long and all flushes seem to be
> only
> due to checkpoint_timeout with the older value
>
> Additionally iam turning off THB defrag on the OS 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 for this HW/OS & DB
> combination - 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 (to start with atleast) for shared_buffers and
> wal_buffers ?
>
> Please let me know if additional information will help.
>
> TIA
> dushy
>
>

--
Regards,

Soni Maula Harriz