Re: Postgres configuration for 64 CPUs, 128 GB RAM...

On Tue, 17 Jul 2007, Marc Mamin wrote:

> Moreover the configuration should be fail-proof as I won't be able to
> attend the tests.

This is unreasonable.  The idea that you'll get a magic perfect
configuration in one shot suggests a fundamental misunderstanding of how
work like this is done.  If there's any way you could adjust things so
that, say, you were allowed to give at least 4 different tuning setups and
you got a report back with each of the results for them, that would let
you design a much better test set.

> Posgres version: 8.2.1

This has already been mentioned, but it really is critical for your type
of test to run 8.2.4 instead so I wanted to emphasize it.  There is a
major scalability bug in 8.2.1.  I'm going to ignore the other things that
other people have already commented on (all the suggestions Stephan and
Jim already made are good ones you should heed) and try to fill in the
remaining gaps instead.

> # I use the default for the bgwriter as I couldnt find recommendation on
> those

The defaults are so small that it will barely do anything on a server of
your size.  Tuning it properly so that it's effective but doesn't waste a
lot of resources is tricky, which is why you haven't found such
recommendations--they're fairly specific to what you're doing and require
some testing to get right.  If you want to see an example from a big
server, look at

http://www.spec.org/jAppServer2004/results/res2007q3/jAppServer2004-20070606-00065.html#DBDatabase_SW_Config0

That's tuned for a very specific benchmark though.  Here's a fairly
generic set of parameters that would be much more aggressive than the
defaults, while not going so far as to waste too many resources if the
writer is just getting in the way on your server:

bgwriter_delay = 200ms
bgwriter_lru_percent = 3.0
bgwriter_lru_maxpages = 500
bgwriter_all_percent = 1.0
bgwriter_all_maxpages = 250

> #WAL
> fsync = on
> #use default
> #wal_sync_method

I'd expect wal_sync_method=open_datasync would outperfom the default, but
you'd really want to test both ways here to be sure.  The fact that the
Sun results I referenced above use the default of fdatasync makes me
hesitate to recommend that change too strongly, as I haven't worked with
this particular piece of hardware. See
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm for more
information about this parameter.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD