On Thu, Feb 4, 2010 at 3:29 AM, Greg Smith
<greg@2ndquadrant.com> wrote:
Robert Haas wrote:
On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhkant@gmail.com> wrote:
work_mem = 160MB # pg_generate_conf wizard 2010-02-03
Overall these settings look sane, but this one looks like an
exception. That is an enormous value for that parameter...
Yeah, I think I need to retune the suggestions for that parameter. The idea behind the tuning profile used in the "web" and "OLTP" setups is that you're unlikely to have all the available connections doing something involving sorting at the same time with those workloads, and when it does happen you want it to use the fastest approach possible even if that takes more RAM so the client waiting for a response is more likely to get one on time. That's why the work_mem figure in those situations is set very aggressively: total_mem / connections, so on a 16GB server that comes out to the 160MB seen here. I'm going to adjust that so that it's capped a little below (total_mem - shared_buffers) / connections instead.
Thanks Robert & Greg. From what others have suggested, I am going in for the following changes:
/boot/loader.conf:
kern.ipc.semmni=512
kern.ipc.semmns=1024
kern.ipc.semmnu=512
/etc/sysctl.conf:
kern.ipc.shm_use_phys=1
kern.ipc.shmmax=4089446400
kern.ipc.shmall=1050000
kern.maxfiles=16384
kern.ipc.semmsl=1024
kern.ipc.semmap=512
vfs.ufs.dirhash_maxmem=4194304
vfs.read_max=32
/usr/local/pgsql/data/postgresql.conf:
maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
work_mem = 110MB # pg_generate_conf wizard 2010-02-03 Reduced as per Robert/Greg suggestions
wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
max_connections = 100 # pg_generate_conf wizard 2010-02-03
Hope this works out good in my case.
With regards
Amitabh Kant