Re: Getting Slow

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD