On Thu, Feb 4, 2010 at 3:29 AM, Greg Smith <g...@2ndquadrant.com> wrote:
> Robert Haas wrote: > > On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhk...@gmail.com> > <amitabhk...@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
