pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHuge amount of memory consumed during transaction
16+ messages / 6 participants
[nested] [flat]
* Huge amount of memory consumed during transaction
@ 2007-10-11 14:04 henk de wit <henk53602@hotmail.com>
0 siblings, 3 replies; 16+ messages in thread
From: henk de wit @ 2007-10-11 14:04 UTC (permalink / raw)
To: pgsql-performance
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.
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/go/onm00200471ave/direct/01/=
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-11 14:16 Richard Huxton <dev@archonet.com>
parent: henk de wit <henk53602@hotmail.com>
2 siblings, 0 replies; 16+ messages in thread
From: Richard Huxton @ 2007-10-11 14:16 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-11 14:27 Scott Marlowe <scott.marlowe@gmail.com>
parent: henk de wit <henk53602@hotmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2007-10-11 14:27 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
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?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-11 14:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: henk de wit <henk53602@hotmail.com>
2 siblings, 2 replies; 16+ messages in thread
From: Tom Lane @ 2007-10-11 14:51 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-11 16:51 Erik Jones <erik@myemma.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Erik Jones @ 2007-10-11 16:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: henk de wit <henk53602@hotmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-11 17:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2007-10-11 17:49 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: henk de wit <henk53602@hotmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-11 23:21 henk de wit <henk53602@hotmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: henk de wit @ 2007-10-11 23:21 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
> 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 relevant parts 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";
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-11 23:23 henk de wit <henk53602@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: henk de wit @ 2007-10-11 23:23 UTC (permalink / raw)
To: pgsql-performance
> 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/go/onm00200471ave/direct/01/=
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-12 01:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: henk de wit <henk53602@hotmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2007-10-12 01:26 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-12 19:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2007-10-12 19:59 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
henk de wit <henk53602@hotmail.com> writes:
> I indeed found them in the logs. Here they are:
It looks to me like you have work_mem set optimistically large. This
query seems to be doing *many* large sorts and hashes:
> HashBatchContext: 262144236 total in 42 blocks; 3977832 free (40 chunks); 258166404 used
> TupleSort: 9429016 total in 11 blocks; 1242544 free (16 chunks); 8186472 used
> HashBatchContext: 262144236 total in 42 blocks; 3977832 free (40 chunks); 258166404 used
> TupleSort: 9429016 total in 11 blocks; 674376 free (20 chunks); 8754640 used
> TupleSort: 9429016 total in 11 blocks; 245496 free (9 chunks); 9183520 used
> TupleSort: 17817624 total in 12 blocks; 3007648 free (14 chunks); 14809976 used
> TupleSort: 276878852 total in 44 blocks; 243209288 free (1727136 chunks); 33669564 used
> TupleSort: 37740568 total in 14 blocks; 5139552 free (21 chunks); 32601016 used
> HashBatchContext: 2105428 total in 9 blocks; 271912 free (7 chunks); 1833516 used
> HashBatchContext: 4202580 total in 10 blocks; 927408 free (13 chunks); 3275172 used
> TupleSort: 75489304 total in 18 blocks; 7909776 free (29 chunks); 67579528 used
> TupleSort: 9429016 total in 11 blocks; 155224 free (16 chunks); 9273792 used
> TupleSort: 46129176 total in 15 blocks; 5787984 free (19 chunks); 40341192 used
> TupleSort: 62906392 total in 17 blocks; 8340448 free (16 chunks); 54565944 used
> HashBatchContext: 2105428 total in 9 blocks; 271912 free (7 chunks); 1833516 used
> TupleSort: 134209560 total in 24 blocks; 4506232 free (41 chunks); 129703328 used
> TupleSort: 18866200 total in 12 blocks; 2182552 free (17 chunks); 16683648 used
> HashBatchContext: 2105428 total in 9 blocks; 271912 free (7 chunks); 1833516 used
> HashBatchContext: 4202580 total in 10 blocks; 927408 free (13 chunks); 3275172 used
> TupleSort: 37740568 total in 14 blocks; 1239480 free (21 chunks); 36501088 used
> TupleSort: 4710424 total in 10 blocks; 307496 free (15 chunks); 4402928 used
> TupleSort: 27254808 total in 13 blocks; 6921864 free (17 chunks); 20332944 used
> TupleSort: 134209560 total in 25 blocks; 6873024 free (39 chunks); 127336536 used
> TupleSort: 39837720 total in 15 blocks; 3136080 free (34 chunks); 36701640 used
and you just plain don't have enough memory for that large a multiple of
work_mem.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-12 21:09 henk de wit <henk53602@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: henk de wit @ 2007-10-12 21:09 UTC (permalink / raw)
To: pgsql-performance
> It looks to me like you have work_mem set optimistically large. This
> query seems to be doing *many* large sorts and hashes:
I
have work_mem set to 256MB. Reading in PG documentation I now realize
that "several sort or hash operations might be running in parallel". So
this is most likely the problem, although I don't really understand why
memory never seems to increase for any of the other queries (not
executed in a transaction). Some of these are at least the size of the
query that is giving problems.
Btw, is there some way to determine up front how many sort or hash operations will be running in parallel for a given query?
Regards
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-12 21:39 Erik Jones <erik@myemma.com>
parent: henk de wit <henk53602@hotmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Erik Jones @ 2007-10-12 21:39 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
On Oct 12, 2007, at 4:09 PM, henk de wit wrote:
> > It looks to me like you have work_mem set optimistically large. This
> > query seems to be doing *many* large sorts and hashes:
>
> I have work_mem set to 256MB. Reading in PG documentation I now
> realize that "several sort or hash operations might be running in
> parallel". So this is most likely the problem, although I don't
> really understand why memory never seems to increase for any of the
> other queries (not executed in a transaction). Some of these are at
> least the size of the query that is giving problems.
Wow. That's inordinately high. I'd recommend dropping that to 32-43MB.
>
> Btw, is there some way to determine up front how many sort or hash
> operations will be running in parallel for a given query?
Explain is your friend in that respect.
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-12 21:48 henk de wit <henk53602@hotmail.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 16+ messages in thread
From: henk de wit @ 2007-10-12 21:48 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-performance
> > I have work_mem set to 256MB.
> Wow. That's inordinately high. I'd recommend dropping that to 32-43MB.
Ok, it seems I was totally wrong with the work_mem setting. I'll adjust it to a more saner level. Thanks a lot for the advice everyone!
> Explain is your friend in that respect.
It shows all the operators, but it doesn't really say that these all will actually run in parallel right? Of course I guess it would give a good idea about what the upper bound is.
regards
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/=
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-12 22:13 Erik Jones <erik@myemma.com>
parent: henk de wit <henk53602@hotmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Erik Jones @ 2007-10-12 22:13 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
On Oct 12, 2007, at 4:48 PM, henk de wit wrote:
> > > I have work_mem set to 256MB.
> > Wow. That's inordinately high. I'd recommend dropping that to
> 32-43MB.
>
> Ok, it seems I was totally wrong with the work_mem setting. I'll
> adjust it to a more saner level. Thanks a lot for the advice everyone!
>
> > Explain is your friend in that respect.
>
> It shows all the operators, but it doesn't really say that these
> all will actually run in parallel right? Of course I guess it would
> give a good idea about what the upper bound is.
You can determine what runs in parellel based on the indentation of
the output. Items at the same indentation level under the same
"parent" line will run in parallel
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-17 20:40 Alvaro Herrera <alvherre@commandprompt.com>
parent: henk de wit <henk53602@hotmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Alvaro Herrera @ 2007-10-17 20:40 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
henk de wit escribió:
> > 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 relevant parts 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";
Huh, this seems really ugly, have you tried something like just
$ ps -o cmd:50,vsz,rss -C postmaster
CMD VSZ RSS
/pgsql/install/00head/bin/postmaster 51788 3992
postgres: writer process 51788 1060
postgres: wal writer process 51788 940
postgres: autovacuum launcher process 51924 1236
postgres: stats collector process 22256 896
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Huge amount of memory consumed during transaction
@ 2007-10-17 23:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2007-10-17 23:28 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: henk de wit <henk53602@hotmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
Alvaro Herrera <alvherre@commandprompt.com> writes:
> henk de wit escribió:
>>> 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 relevant parts 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";
On many variants of Unix, this is going to lead to a totally misleading
number. The first problem is that shared buffers will be counted
multiple times (once for each PG process). The second problem is that,
depending on platform, a specific page of shared memory is counted
against a process only after it first touches that page. This means
that when you run a big seqscan, or anything else that touches a lot of
buffers, the reported size of the process gradually increases from just
its local memory space to its local memory space plus the total size
of the Postgres shared buffer arena. This change in the reported size
is *utterly meaningless* in terms of actual memory consumption.
It's not easy to get useful measurements from "ps" when dealing with
heavy memory sharing. There have been some discussions recently of
alternative tools that let you get a clearer picture; check the
PG list archives.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2007-10-17 23:28 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-11 14:04 Huge amount of memory consumed during transaction henk de wit <henk53602@hotmail.com>
2007-10-11 14:16 ` Richard Huxton <dev@archonet.com>
2007-10-11 14:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-11 23:21 ` henk de wit <henk53602@hotmail.com>
2007-10-17 20:40 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-10-17 23:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-11 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-11 16:51 ` Erik Jones <erik@myemma.com>
2007-10-11 17:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-11 23:23 ` henk de wit <henk53602@hotmail.com>
2007-10-12 01:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-12 19:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-12 21:09 ` henk de wit <henk53602@hotmail.com>
2007-10-12 21:39 ` Erik Jones <erik@myemma.com>
2007-10-12 21:48 ` henk de wit <henk53602@hotmail.com>
2007-10-12 22:13 ` Erik Jones <erik@myemma.com>
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