Re: out of memory during query execution - Mailing list pgsql-general

From Chris Browne
Subject Re: out of memory during query execution
Date
Msg-id 60mzixkwhp.fsf@dba2.int.libertyrms.com
Whole thread Raw
In response to out of memory during query execution  (DANTE ALEXANDRA <ALEXANDRA.DANTE@BULL.NET>)
Responses Re: out of memory during query execution
Re: out of memory during query execution
List pgsql-general
ALEXANDRA.DANTE@BULL.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/>

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: is this a bug or I am blind?
Next
From: Tom Lane
Date:
Subject: Re: is this a bug or I am blind?