Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0D697633829 for ; Tue, 23 Mar 2010 10:08:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 09284-09 for ; Tue, 23 Mar 2010 13:08:19 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og108.obsmtp.com (exprod7og108.obsmtp.com [64.18.2.169]) by mail.postgresql.org (Postfix) with SMTP id A893563263A for ; Tue, 23 Mar 2010 10:08:19 -0300 (ADT) Received: from source ([74.125.82.41]) by exprod7ob108.postini.com ([64.18.6.12]) with SMTP ID DSNKS6i9QR3J/trL7YRFlwItAA5ZWXdXPP1s@postini.com; Tue, 23 Mar 2010 06:08:18 PDT Received: by mail-ww0-f41.google.com with SMTP id 39so1456768wwb.28 for ; Tue, 23 Mar 2010 06:08:17 -0700 (PDT) MIME-Version: 1.0 Received: by 10.216.90.9 with SMTP id d9mr1357773wef.95.1269349697106; Tue, 23 Mar 2010 06:08:17 -0700 (PDT) In-Reply-To: <4BA83F99.5020607@2ndquadrant.com> References: <4BA83F99.5020607@2ndquadrant.com> Date: Tue, 23 Mar 2010 09:08:17 -0400 Message-ID: Subject: Re: Got that new server, now it's time for config! From: Scott Mead To: Greg Smith Cc: Carlo Stonebanks , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6da7de3cab432048277814a X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/291 X-Sequence-Number: 38116 --0016e6da7de3cab432048277814a Content-Type: text/plain; charset=ISO-8859-1 On Tue, Mar 23, 2010 at 12:12 AM, Greg Smith 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 > --0016e6da7de3cab432048277814a Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Tue, Mar 23, 2010 at 12:12 AM, Greg Smith= <greg@2ndquad= rant.com> wrote:
Carlo Stonebanks wrote:
So, we have the hardware, we have the O/S - but I think our config leaves m= uch 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. =A0The planner = thinks that anything bigger than 128MB isn't likely to fit in RAM by de= fault, which favors sequential scans. =A0That parameter should probably be = >24GB on your server, so it's off by more than two orders of magnitu= de.

+1


I'm curi= ous why you've set:
log_min_error_statement =3D =A0debug1<= /div> log_mi= n_messages =3D debug1
client_min_messages = =3D =A0debug1

Although not dir= ectly addressing the problem of using index scans, this is going to be caus= ing lots of message verbosity, possibly (based on your rate) enough to clob= ber the disks more than you need to.

-Scott M<= /div>


=A0


wal_sync_method =3D open_sync

This is a scary setting to be playing with on Linux when using ext3 filesys= tems due to general kernel bugginess in this area. =A0See http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php f= or an example. =A0I wouldn't change this from the default in your posit= ion 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 anyth= ing else. =A0Nothing else jumped out as bad in your configuration besides t= he extremely high logging levels, haven't looked at it that carefully y= et though.

--
Greg Smith =A02ndQuadrant US =A0Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com =A0 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

--0016e6da7de3cab432048277814a--