Re: out of memory during query execution

ALEXANDRA(dot)DANTE(at)BULL(dot)NET (DANTE ALEXANDRA) writes:
> I am a PostGreSQL newbie. I work with the 8.1.0 release on AIX 5.3,
> with 300GB of datas.
> Some of the queries launched on this database finish with an "*out of
> memory*". The queries which have failed contain a lot of join (between
> 6 tables), sub-select and aggregate. For these queries, the log file
> contains :
> psql:Q9.sql:40: ERROR: out of memory
> DETAIL: Failed on request of size 148.
>
> On the server used, I got 3GB of memory and 1 CPU.
> The settings specified in the "postgresql.conf" are :
> # - Memory -
> shared_buffers = 12288 #temp_buffers = 1000
> #max_prepared_transactions = 5 work_mem = 65536
> maintenance_work_mem = 262144 max_stack_depth = 24574
>
> Are some of these values false?
> Is the "out of memory" error due to smaller memory available ?
> Has somenone ever seen this problem ?

We have seen this problem...

It's *probably* related to the memory model you're using.

I have thus far evaded *fully* understanding the details (and hope
that can persist!), but here are some of the things to consider:

- By default, AIX really prefers to build 32 bit binaries

- The sorta-hacks that IBM put in place on library segmentation (and
this stuff is quite ghastly) mean that any backend will likely have
quite a bit less than 2GB of even theoretically-available memory space.

The problem is probably that the memory model is throttling you to
*WAY* less than 2GB of memory.

You may want to try a 64 bit build. With GCC, this requires something
like the following ./configure incantation...

CC="gcc -maix64" LDFLAGS="-Wl,-bbigtoc" ./configure
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://cbbrowne.com/info/sgml.html
Rules of the Evil Overlord #86. "I will make sure that my doomsday
device is up to code and properly grounded."
<http://www.eviloverlord.com/>