From: Tom Lane <tgl@sss.pgh.pa.us>
To: henk de wit <henk53602@hotmail.com>
Cc: pgsql-performance@postgreSQL.org
Subject: Re: Huge amount of memory consumed during transaction
Date: Fri, 12 Oct 2007 15:59:16 -0400
Message-ID: <4144.1192219156@sss.pgh.pa.us> (raw)
In-Reply-To: <BAY124-W43C954E257CFF824F682CCF5A00@phx.gbl>
References: <BAY124-W276814F77A7A7ABE7596B1F5A70@phx.gbl>
<13678.1192114295@sss.pgh.pa.us>
<BAY124-W19797F87C0FFF00AECDBA8F5A70@phx.gbl>
<15391.1192152419@sss.pgh.pa.us>
<BAY124-W43C954E257CFF824F682CCF5A00@phx.gbl>
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
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: tgl@sss.pgh.pa.us, henk53602@hotmail.com
Subject: Re: Huge amount of memory consumed during transaction
In-Reply-To: <4144.1192219156@sss.pgh.pa.us>
* 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