From: Greg Smith <greg@2ndquadrant.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Got that new server, now it's time for config!
Date: Tue, 23 Mar 2010 00:12:09 -0400
Message-ID: <4BA83F99.5020607@2ndquadrant.com> (raw)
In-Reply-To: <ho8r9s$2i4p$1@news.hub.org>
References: <ho8r9s$2i4p$1@news.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, stonec.register@sympatico.ca
Subject: Re: Got that new server, now it's time for config!
In-Reply-To: <4BA83F99.5020607@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox