Message-ID: <40C9671C.6020202@archonet.com>
Date: Fri, 11 Jun 2004 09:02:36 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.6 (X11/20040502)
MIME-Version: 1.0
To: Mark Striebeck <mstriebeck@vasoftware.com>
Cc: pgsql-admin@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [GENERAL] out of memory error
References: <40C90778.4090308@vasoftware.com>
In-Reply-To: <40C90778.4090308@vasoftware.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Mark Striebeck wrote:
> Hi,
> 
> we are using Postgres with a J2EE application (JBoss) and get 
> intermittent "out of memory" errors on the Postgres database. We are 
> running on a fairly large Linux server (Dual 3GHz, 2GB Ram) with the 
> following parameters:
> 
> shared_buffers = 8192
> sort_mem = 8192
> effective_cache_size = 234881024
> random_page_cost = 2

The effective_cache_size is measured in disk-blocks not bytes, so you'll 
want to reduce that. It should be whatever the typical "cached" readout 
of top is, divided by 8k.

> (everything else is default)
> 
> The error message in the log is:
> 
> Jun 10 17:20:04 cruisecontrol-rhea postgres[6856]: [6-1] ERROR:  53200: 
> out of memory
> Jun 10 17:20:04 cruisecontrol-rhea postgres[6856]: [6-2] DETAIL:  Failed 
> on request of size 208.
> Jun 10 17:20:04 cruisecontrol-rhea postgres[6856]: [6-3] LOCATION:  
> AllocSetAlloc, aset.c:700

What is the system's overall memory usage at this time? Is there one 
postgresql backend using all this memory?

> All failures are with the following query (again, it only fails every 
> now and then). The query returns only a few results:
[snip]
> Can anyone see anything dangerous about this query?

The only thing that struck me was you had 11 tables in the join which 
means the geqo query-planner will kick in (assuming default config 
values). If you can reproduce it regularly, you could try increasing 
geqo_threshold and see if that had any effect.

 > What's the best way to analyze this further?

1. Monitor memory usage when you run the query, see which process is 
using what.
2. Get EXPLAIN ANALYSE for that query, there may be something unusual in 
the plan.
3. Finally, might have to attach a debugger to a backend, but we'll need 
to know what to look for first.

--
   Richard Huxton
   Archonet Ltd