On Wed, 2007-01-17 at 18:27, Steve wrote:
> > 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.
You can probably take a portion of what you're loading and make a
benchmark of the load process that is repeatable (same data, size,
etc...) each time, but only takes 30 minutes to an hour to run each
time. shortens your test iteration AND makes it reliably repeatable.