From: Scott Marlowe <scott.marlowe@gmail.com>
To: henk de wit <henk53602@hotmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Huge amount of memory consumed during transaction
Date: Thu, 11 Oct 2007 09:27:23 -0500
Message-ID: <dcc563d10710110727p224f69aeoa54647185a70ebef@mail.gmail.com> (raw)
In-Reply-To: <BAY124-W276814F77A7A7ABE7596B1F5A70@phx.gbl>
References: <BAY124-W276814F77A7A7ABE7596B1F5A70@phx.gbl>
On 10/11/07, henk de wit <henk53602@hotmail.com> wrote:
>
> Hi,
>
> I'm running into a problem with PostgreSQL 8.2.4 (running on 32 bit Debian
> Etch/2x dual core C2D/8GB mem). The thing is that I have a huge transaction
> that does 2 things: 1) delete about 300.000 rows from a table with about 15
> million rows and 2) do some (heavy) calculations and re-insert a litlte more
> than 300.000 new rows.
>
> My problem is that this consumes huge amounts of memory. The transaction
> runs for about 20 minutes and during that transaction memory usage peaks to
> about 2GB. Over time, the more rows that are involved in this transaction,
> the higher the peak memory requirements.
How is the memory consumed? How are you measuring it? I assume you
mean the postgres process that is running the query uses the memory.
If so, which tool(s) are you using and what's the output that shows it
being used?
I believe that large transactions with foreign keys are known to cause
this problem.
> Lately we increased our shared_buffers to 1.5GB, and during this transaction
> we reached the process memory limit, causing an out of memory and a rollback
> of the transaction:
How much memory does this machine have? You do realize that
shared_buffers are not a generic postgresql memory pool, but
explicitly used to hold data from the discs. If you need to sort and
materialize data, that is done with memory allocated from the heap.
If you've given all your memory to shared_buffers, there might not be
any left.
How much swap have you got configured?
Lastly, what does explain <your query here> say?
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-performance@postgresql.org
Cc: scott.marlowe@gmail.com, henk53602@hotmail.com
Subject: Re: Huge amount of memory consumed during transaction
In-Reply-To: <dcc563d10710110727p224f69aeoa54647185a70ebef@mail.gmail.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