Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C39FB9FBF41 for ; Fri, 12 Oct 2007 17:00:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 07915-07-2 for ; Fri, 12 Oct 2007 16:59:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id F12D89FC09B for ; Fri, 12 Oct 2007 16:59:18 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9CJxGa1004145; Fri, 12 Oct 2007 15:59:16 -0400 (EDT) To: henk de wit cc: pgsql-performance@postgreSQL.org Subject: Re: Huge amount of memory consumed during transaction In-reply-to: References: <13678.1192114295@sss.pgh.pa.us> <15391.1192152419@sss.pgh.pa.us> Comments: In-reply-to henk de wit message dated "Fri, 12 Oct 2007 12:29:06 +0200" Date: Fri, 12 Oct 2007 15:59:16 -0400 Message-ID: <4144.1192219156@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/149 X-Sequence-Number: 27171 henk de wit 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