Date: Mon, 23 Oct 2006 16:36:41 -0500
From: "Jim C. Nasby" <jim@nasby.net>
To: Richard Huxton <dev@archonet.com>
Cc: Mike <akiany@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Vacuum and Memory Loss
Message-ID: <20061023213641.GU26892@nasby.net>
References: <1161307454.955144.107400@m73g2000cwd.googlegroups.com>
	<453C8147.60609@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <453C8147.60609@archonet.com>
User-Agent: Mutt/1.5.13 (2006-08-11)

On Mon, Oct 23, 2006 at 09:45:59AM +0100, Richard Huxton wrote:
> Mike wrote:
> >Hello friends,
> >
> >I am responsible for maintaining a high volume website using postgresql
> >8.1.4. Given the amount of reads and writes, I vacuum full the server a
> >few times a week around 1, 2 AM shutting down the site for a few
> >minutes. The next day morning around 10 - 11 AM the server slows down
> >to death. It used to be that the error 'Too many clients' would be
> >recorded, until I increased the number of clients it can handle, and
> >now it simply slows down to death having lots and lots of postmaster
> >processes running:
> >
> >Tasks: 665 total,  10 running, 655 sleeping,   0 stopped,   0 zombie
> >Cpu(s): 14.9% us, 16.7% sy,  0.0% ni,  0.0% id, 68.4% wa,  0.0% hi,
> >0.0% si
> >Mem:   2074932k total,  2051572k used,    23360k free,     2736k
> >buffers
> >Swap:  2096440k total,  1844448k used,   251992k free,   102968k cached
> 
> This seems to be saying you have 1.8GB of swap in use. I'd start by 
> checking with vmstat whether you're actively swapping. If so, you're 
> overallocating memory.

Which could easily be caused by a combination of trying to handle too
many database connections at once and setting work_mem too high.

I've often gone into client sites to find they've set the database up to
accept hundreds or thousands of connections, even though the hardware
they're running on would most likely fall over if they actually had that
many simultaneously active connections. In many cases, increasing the
number of connections the the database will hurt performance rather than
help it, because you're now asking an already overloaded server to do
even more work.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)