pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Mead <scott.lists@enterprisedb.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: 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 09:08:17 -0400
Message-ID: <d3ab2ec81003230608y7172ef68k7c4b91442a553323@mail.gmail.com> (raw)
In-Reply-To: <4BA83F99.5020607@2ndquadrant.com>
References: <ho8r9s$2i4p$1@news.hub.org>
	<4BA83F99.5020607@2ndquadrant.com>

On Tue, Mar 23, 2010 at 12:12 AM, Greg Smith <greg@2ndquadrant.com> wrote:

> 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.


+1


I'm curious why you've set:
log_min_error_statement =  debug1
log_min_messages = debug1
client_min_messages =  debug1

Although not directly addressing the problem of using index scans, this is
going to be causing lots of message verbosity, possibly (based on your rate)
enough to clobber the disks more than you need to.

-Scott M




>
>
>  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
>

view thread (4+ messages)

Message-ID: <d3ab2ec81003230608y7172ef68k7c4b91442a553323@mail.gmail.com>
Permalink:  ../d3ab2ec81003230608y7172ef68k7c4b91442a553323@mail.gmail.com/
Also on:    postgresql.org/message-id/d3ab2ec81003230608y7172ef68k7c4b91442a553323@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.lists@enterprisedb.com, greg@2ndquadrant.com, stonec.register@sympatico.ca
  Subject: Re: Got that new server, now it's time for config!
  In-Reply-To: <d3ab2ec81003230608y7172ef68k7c4b91442a553323@mail.gmail.com>
  References: <ho8r9s$2i4p$1@news.hub.org> <4BA83F99.5020607@2ndquadrant.com> <d3ab2ec81003230608y7172ef68k7c4b91442a553323@mail.gmail.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