Subject: Re: slow queries, possibly disk io
From: Simon Riggs <simon@2ndquadrant.com>
To: Josh Close <narshe@gmail.com>
Cc: POSTGRES-PERFORMANCE <pgsql-performance@postgresql.org>
In-Reply-To: <4a0cafe205052705526fccfe75@mail.gmail.com>
References: <4a0cafe205052613242e0d4d10@mail.gmail.com>
	<42963C3F.4000905@arbash-meinel.com>
	<4a0cafe205052705526fccfe75@mail.gmail.com>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Wed, 01 Jun 2005 10:06:19 +0100
Message-Id: <1117616779.3844.903.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Fri, 2005-05-27 at 07:52 -0500, Josh Close wrote:
> > Setting shared buffers above something like 10-30% of memory is counter
> > productive.
> 
> What is the reason behind it being counter productive? If shared
> buffers are at 30%, should effective cache size be at 70%? How do
> those two relate?

They don't relate. 

shared_buffers = 50000 is enough. More than that will give bgwriter
issues.

effective_cache_size changes whether indexes are selected or not. Higher
settings favour indexed access.

> > 
> > Increasing sort_mem can help with various activities, but increasing it
> > too much can cause you to swap, which kills performance. The caution is
> > that you will likely use at least 1 sort_mem per connection, and can
> > likely use more than one if the query is complicated.
> 
> I have a max of 100 connections and 2 gigs of mem. Right now the sort
> mem is a 4 megs. How much higher could I put that?
> 

Please post your server hardware config all in one go. You have more
than 2 CPUs, yes?

Also, mention what bgwriter settings are. You may need to turn those
down a bit.

Best Regards, Simon Riggs