From: | Amitabh Kant <amitabhkant(at)gmail(dot)com> |
---|---|
To: | Greg Smith <greg(at)2ndquadrant(dot)com> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimizing Postgresql server and FreeBSD for heavy read and writes |
Date: | 2010-02-04 10:07:57 |
Message-ID: | 84b68b3d1002040207n14c217aam7e9c8349dcc3de9c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2010-02-04 10:41:13 | Re: Air-traffic benchmark |
Previous Message | Amitabh Kant | 2010-02-04 09:52:43 | Re: Optimizing Postgresql server and FreeBSD for heavy read and writes |