Thread: Postgresql 9.3 tuning advice
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
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
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@gmail.com> wrote:
Hello all,Iam running a postgresql 9.0.13 master/slave instance in a write heavyworkload.The hardware is a Dell 720, E5530 - 8 core, 128GB RAM. The database (around250g 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 ischeckpoint_segments = 96Iam moving to postgresql 9.3 shortly and planning to tune the abovedirectives as below..effective_cache_size = 100GB # free+buffers is pretty consistent around 110to 120GB and pg_oscache_total is around 80GBcheckpoint_segments = 32 # 96 seems to long and all flushes seem to be onlydue to checkpoint_timeout with the older valueAdditionally iam turning off THB defrag on the OS as suggested by some posts on thelists. Though, My initial pgbench testing doesn't seem to indicate anyissues with THB defrag turned on/off.Iam not sure about shared_buffers and wal_buffers for this HW/OS & DB combination - iam inclined to leavethem to defaults. But based on this articleit looks there will be some advantages in tuning itWhat would be a good value (to start with atleast) for shared_buffers and wal_buffers ?Please let me know if additional information will help.TIAdushy
Regards,