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

Re: Configuration Advice

From: Steve <cheetah(at)tanabi(dot)org>
To: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
Cc: Benjamin Minshall <minshall(at)intellicon(dot)biz>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Configuration Advice
Date: 2007-01-18 00:27:22
Message-ID: Pine.GSO.4.64.0701171924200.4471@kingcheetah.tanabi.org (view raw or flat)
Thread:
Lists: pgsql-performance
> Generally speaking, once you've gotten to the point of swapping, even a
> little, you've gone too far.  A better approach is to pick some
> conservative number, like 10-25% of your ram for shared_buffers, and 1
> gig or so for maintenance work_mem, and then increase them while
> exercising the system, and measure the difference increasing them makes.
>
> If going from 1G shared buffers to 2G shared buffers gets you a 10%
> increase, then good.  If going from 2G to 4G gets you a 1.2% increase,
> it's questionable.  You should reach a point where throwing more
> shared_buffers stops helping before you start swapping.  But you might
> not.
>
> Same goes for maintenance work mem.  Incremental changes, accompanied by
> reproduceable benchmarks / behaviour measurements are the way to
> determine the settings.
>
> Note that you can also vary those during different times of the day.
> you can have maint_mem set to 1Gig during the day and crank it up to 8
> gig or something while loading data.  Shared_buffers can't be changed
> without restarting the db though.
>

I'm currently benchmarking various configuration adjustments.  Problem is 
these tests take a really long time because I have to run the load 
process... which is like a 9 hour deal.  That's why I'm asking for advice 
here, because there's a lot of variables here and it's really time costly 
to test :)

I'm still working on the benchmarkings and by Friday I should have some 
interesting statistics to work with and maybe help figure out what's going 
on.


Thanks!

Steve

In response to

Responses

pgsql-performance by date

Next:From: SteveDate: 2007-01-18 00:32:34
Subject: Re: Configuration Advice
Previous:From: Chad WagnerDate: 2007-01-18 00:01:55
Subject: Re: Configuration Advice

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