Re: [GENERAL] out of memory error

From: Richard Huxton <dev(at)archonet(dot)com>
To: Mark Striebeck <mstriebeck(at)vasoftware(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] out of memory error
Date: 2004-06-11 08:02:36
Message-ID: 40C9671C.6020202@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message lise chhay 2004-06-11 09:20:30 how to test the server webauth on mandrake
Previous Message akuppachi 2004-06-11 04:10:46 Re: Calculating size of the database tables

Browse pgsql-general by date

  From Date Subject
Next Message Daniel Devico 2004-06-11 09:25:13 Keep history of modifications
Previous Message Joseph Shraibman 2004-06-11 03:20:38 Re: index with LIKE