Re: out of memory during query execution

Hello Tom,

I've got others questions on work-mem parameter.
On the "http://www.powerpostgresql.com/Downloads/annotated_conf_80.html"
web site, I've read that the work-mem specifies the amount of memory to
be used by internal sort operations ans hash tables before switching to
temporary disk files. Moreover, for a complex query, several sort or
hash operations might be running in parallel; each one will be allowed
to use as much memory as this value specifies before it starts to put
into temporary files.

In my case, does this mean that each one of the two hashs and sorts will
take 64MB, so 192MB ?
What do you want to say with "so the two hashes and sort would think
they could use 3/4ths of the available heap" ?

Last question, how can I see that my 32-bit AIX program being limited to
256MB of heap, as the user "pg_810" used to launch the postmaster got
when I execute the "ulimit -a" command :
$ ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        unlimited
memory(kbytes)       unlimited
coredump(blocks)     unlimited
nofiles(descriptors) 2000

Thank you very much for your help.
Regards,
Alexandra DANTE


Tom Lane a écrit :

>Kevin Murphy <murphy@genome.chop.edu> writes:
>
>
>>I'm certainly not an AIX expert, but I remember my 32-bit AIX programs
>>being limited to 256MB of heap by default.
>>
>>
>
>Hmm ... if that's the case then it'd probably explain the problem.
>Alexandra had work_mem set to 64MB, so the two hashes and sort would
>think they could use 3/4ths of the available heap; given that there
>are other needs and our management of memory-use limitations is fairly
>sloppy, that could easily translate into running out.
>
>So the answer is either to increase the available heap or reduce
>work_mem to a smaller fraction of it.
>
>            regards, tom lane
>
>
>