From: Richard Huxton <dev@archonet.com>
To: Mark Striebeck <mstriebeck@vasoftware.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] out of memory error
Date: Fri, 11 Jun 2004 09:02:36 +0100
Message-ID: <40C9671C.6020202@archonet.com> (raw)
In-Reply-To: <40C90778.4090308@vasoftware.com>
References: <40C90778.4090308@vasoftware.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: dev@archonet.com, mstriebeck@vasoftware.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] out of memory error
In-Reply-To: <40C9671C.6020202@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox