Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

pgsql-performance by date

Next:From: Simon RiggsDate: 2010-02-04 10:41:13
Subject: Re: Air-traffic benchmark
Previous:From: Amitabh KantDate: 2010-02-04 09:52:43
Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group