Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 91FD3161CA37
	for <pgsql-admin@postgresql.org>; Mon, 20 Feb 2012 13:10:11 -0400 (AST)
Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <Kevin.Grittner@wicourts.gov>) id 1RzWkc-0007IS-9z
	for pgsql-admin@postgresql.org; Mon, 20 Feb 2012 17:10:11 +0000
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Mon, 20 Feb 2012 11:09:57 -0600
Message-Id: <4F422A0402000025000458DA@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.2 
Date: Mon, 20 Feb 2012 11:09:56 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Marco Cassiano" <mcassiano@manord.com>,
 <pgsql-admin@postgresql.org>
Subject: Re: R: Out of memory...
References: <BC53C974C3B9E542BC0A9BD4C5B1168A0E9326E0@NEWMAIL.manord.com>
	<4F3E6BA3020000250004581A@gw.wicourts.gov>
	<BC53C974C3B9E542BC0A9BD4C5B1168A08BBCA28@NEWMAIL.manord.com>
In-Reply-To: <BC53C974C3B9E542BC0A9BD4C5B1168A08BBCA28@NEWMAIL.manord.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Pg-Spam-Score: -4.2 (----)
X-Archive-Number: 201202/84
X-Sequence-Number: 39144

"Cassiano, Marco" <mcassiano@manord.com> wrote:
 
> "autovacuum_max_workers";"7"
> "autovacuum_naptime";"10min"
> "autovacuum_vacuum_cost_delay";"20ms"
> "autovacuum_vacuum_cost_limit";"200"
 
You've made autovacuum a little less aggressive for small,
heavily-updated tables with the 10min naptime, even with 7 workers. 
That's some cause for concern, but probably not the immediate
problem.
 
> "fsync";"off"
> "full_page_writes";"off"
 
With those settings, be prepared to go to your last known good
backup if the OS or hardware crashes.
 
> "maintenance_work_mem";"300MB"
 
Each of your seven autovacuum processes can be using that, for 2.1GB
in autovacuum work space.
 
> "max_connections";"250"
> "temp_buffers";"8MB"
> "work_mem";"12MB
 
If you had all connections busy, using temp tables and doing sorts
or hash tables, you could hit 5GB or more in space for that.  On top
of the shared_buffers and maintenance work memory, you could hit 8GB
or more in RAM usage pretty easily.  You might want to consider a
connection pool to allow the 250 client connections to be limited to
a pool of 20 or 30 database connections on the server side.
 
Are you using the INET data type?  There is a bug introduced in
9.1.2 which can leak memory when using that type.  If that's your
problem, you might want to fall back to 9.1.1 and/or install 9.1.3
as soon as it becomes available.
 
-Kevin