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 transactionthat 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.

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:

BEGIN
DELETE 299980
ERROR: out of memory
DETAIL: Failed on request of size 4194304.
ROLLBACK
DROP SEQUENCE

real 19m45.797s
user 0m0.024s
sys 0m0.000s

On my development machine, which has less than 2GB of memory, I can not even finish the transaction.

Is there a way to tell PG to start swapping to disk instead of using ram memory during such a transaction?

Thanks in advance for all help



__________________________________________________ _______________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/g...ave/direct/01/

henk de wit 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.

What exactly consumes all your memory? I'm assuming it's not just
straight SQL.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

henk de wit <henk53602@hotmail.com> writes:
> ERROR: out of memory
> DETAIL: Failed on request of size 4194304.

This error should have produced a map of per-context memory use in the
postmaster log. Please show us that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Oct 11, 2007, at 9:51 AM, Tom Lane wrote:

> henk de wit <henk53602@hotmail.com> writes:
>> ERROR: out of memory
>> DETAIL: Failed on request of size 4194304.
>
> This error should have produced a map of per-context memory use in the
> postmaster log. Please show us that.
>
> regards, tom lane

Tom, are there any docs anywhere that explain how to interpret those
per-context memory dumps? For example, when I see an autovacuum
context listed is it safe to assume that the error came from an
autovac operation, etc.?

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Erik Jones <erik@myemma.com> writes:
> Tom, are there any docs anywhere that explain how to interpret those =20
> per-context memory dumps?

No, not really. What you have to do is grovel around in the code and
see where contexts with particular names might get created.

> For example, when I see an autovacuum =20
> context listed is it safe to assume that the error came from an =20
> autovac operation, etc.?

Probably, but I haven't looked.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> 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?

It's periodically measured and recorded by a script from which the relevantparts are:

GET_VSZ="ps aux | grep $REQ_GREP | grep -v grep | grep -v $$ | awk '{print \$5}'
| sort -n | tail -n1";
GET_RSS="ps aux | grep $REQ_GREP | grep -v grep | grep -v $$ | awk '{print \$6}'
| sort -n | tail -n1";

From this I draw graphs using Cacti. I just checked a recent transaction; during this transaction which involved about 900.000 rows, VSZ peakes at 2.36GB, with RSS then peaking at 2.27GB. This memory usage is on top of a shared_buffers being set back to 320MB. Shortly after the transaction finished,memory usage indeed drops back to a nearly steady 320MB.
(btw, I mistyped the rows involved in the original post; the 2GB memory usage is for 900.000 rows, not 300.000).

After some more digging, I found out that the immense increase of memory usage started fairly recently (but before the increase of my shared_buffers, that just caused the out of memory exception).

E.g. for a transaction with 300.000 rows involved a few weeks back, the memory usage stayed at a rather moderate 546MB/408MB (including 320MB for shared_buffers), and for some 800.000 rows the memory usage peaked at 'only' 631/598. When I draw a graph of "rows involved" vs "memory usage" there is a direct relation; apart from a few exceptions its clearly that the more rowsare involved, the more memory is consumed.

I'll have to check what was exactly changed at the PG installation recently, but nevertheless even with the more moderate memory consumption it becomes clear that PG eventually runs out of memory when more and more rows are involved.

> I believe that large transactions with foreign keys are known to cause
> this problem.

As far as I can see there are no, or nearly no foreign keys involved in thetransaction I'm having problems with.

> How much memory does this machine have?

It's in the original post: 8GB

> If you've given all your memory to shared_buffers, there might not be
> any left.

I have of course not given all memory to shared_buffers. I tried to apply the rule of thumb of setting it to 1/4 of total memory. To be a little conservative, even a little less than that. 1/4 of 8GB is 2GB, so I tried with 1..5 to start. All other queries and small transactions run fine (we're talking about thousands upon thousands of queries and 100's of different ones. It's this huge transaction that occupies so much memory.

> Lastly, what does explain <your query here> say?

I can't really test that easily now and it'll be a huge explain anyway (thequery is almost 500 lines :X). I'll try to get one though.

__________________________________________________ _______________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/g...ave/direct/01/

> This error should have produced a map of per-context memory use in the> postmaster log.
> Please show us that.

I'm not exactly sure what to look for in the log. I'll do my best though and see what I can come up with.


__________________________________________________ _______________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/g...ave/direct/01/

henk de wit <henk53602@hotmail.com> writes:
> I'm not exactly sure what to look for in the log. I'll do my best though an=
> d see what I can come up with.

It'll be a bunch of lines like

TopMemoryContext: 49832 total in 6 blocks; 8528 free (6 chunks); 41304 used

immediately in front of the out-of-memory ERROR report.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Oct 11, 4:04 pm, henk53...@hotmail.com (henk de wit) 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.

I had a similar problem with a ~ 5 x 10^6 tuples table with
referential constraints. Having to delete ~ 10.000 of them
a simple
delete from T where C; would "never end".

I solved deleting tuples one by one:
select id from T where C; (I saved output in a file
"ID_todelete.txt").
Then wrote in a file single delete queries:
DELETE FROM T WHERE id=217;
DELETE FROM T WHERE id=336;
....

and finally feeding the file to postgres who executed succesfully and
in a fairly little time.

After this "proof of concept way" i wrote a stored procedure to delete
rows individually.

Of course this way could be not right for you, if you strictly need to
delete
ALL tuples in a single atomic operation.

Stefano.