Date: Thu, 7 Jun 2007 15:26:58 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: Getting Slow
In-Reply-To: <14D118F0-20F6-4096-93D7-E6CB2893E895@sweetwater.com>
Message-ID: <Pine.GSO.4.64.0706071515450.2676@westnet.com>
References: <14D118F0-20F6-4096-93D7-E6CB2893E895@sweetwater.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Thu, 7 Jun 2007, Joe Lester wrote:

> Memory: 4GB RAM
>
> shared_buffers = 10000
> work_mem = 2048
> effective_cache_size = 30000

With these parameters, your server has 80MB dedicated to its internal 
caching, is making query decisions assuming the operating system only has 
240MB of memory available for its caching, and is only allowing individual 
clients to have a tiny amount of memory to work with before they have to 
swap things to disk.  You're not giving it anywhere close to enough memory 
to effectively work with a 5GB database, and your later reports show 
you're barely using 1/2 the RAM in this system usefully.

Multiply all these parameters by 10X, restart your server, and then you'll 
be in the right ballpark for a system with 4GB of RAM.  There might be 
some other tuning work left after that, but these values are so far off 
that until you fix them it's hard to say what else needs to be done.  See 
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm for more 
information on this topic.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD