Re: Optimizing Postgresql server and FreeBSD for heavy read and writes

On Thu, Feb 4, 2010 at 3:29 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> Robert Haas wrote:
>
> On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhkant(at)gmail(dot)com> <amitabhkant(at)gmail(dot)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