Re: Configuration Advice

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux