Re: Got that new server, now it's time for config!

Carlo Stonebanks wrote:

So, we have the hardware, we have the O/S - but I think our config leaves much to be desired. Typically, our planner makes nad decisions, picking seq scan over index scan, where index scan has a better result.

You're not setting effective_cache_size, so I wouldn't expect it to ever choose an index scan given the size of your data set.  The planner thinks that anything bigger than 128MB isn't likely to fit in RAM by default, which favors sequential scans.  That parameter should probably be >24GB on your server, so it's off by more than two orders of magnitude.

wal_sync_method = open_sync

This is a scary setting to be playing with on Linux when using ext3 filesystems due to general kernel bugginess in this area.  See http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php for an example.  I wouldn't change this from the default in your position if using that filesystem.

I'd drastically increase effective_cache_size, put wal_sync_method back to the default, and then see how things go for a bit before tweaking anything else.  Nothing else jumped out as bad in your configuration besides the extremely high logging levels, haven't looked at it that carefully yet though.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance