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

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

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

> 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/g...ave/direct/01/

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



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

> > 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/g...ave/direct/01/

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



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

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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

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