SEO

Hello,

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 ?

Thank you for your help.

Regards,
Alexandra DANTE

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

Chris Browne wrote:
> 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

You'll also want to export OBJECT_MODE=64 before the ./configure line.

--
Seneca Cunningham
scunning@ca.afilias.info

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

DANTE ALEXANDRA <ALEXANDRA.DANTE@BULL.NET> 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.

Hmm ... what ulimit settings are you running the postmaster under?
Could we see the EXPLAIN plans for some of the failing queries?

regards, tom lane

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

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

Hello,

Thank you for your answer.
The person is charge of building PostGreSQL 8.1.0 has done a 32 bit
build and has used the "cc_r" compiler.
This person does not succeed to build PostGreSQL 8.1.0 with "gcc" and 64
bits. Unfortunatly, I don't have the errors or the logs of the 64 bits
build and I can't tell you what error occurs.

The build done was realized in 32 bits, with the cc_r compiler.
To build POstGreSQL, a rpm was done and the ".spec" file contained the
following instructions :
export OBJECT_MODE=32
../configure CC=/usr/vac/bin/cc_r CFLAGS="-O2 -qmaxmem=-1 -qsrcmsg
-qlargepage" --enable-thread-safety
--without-readline --prefix=%{buildroot}%{prefix}
gmake -j 4
unset OBJECT_MODE

Do you think that my problems of "out of memory" are due to the 32 bits
build ?
Do you think that I must build PostGreSQL wih 64 bits to solve this error ?

Thank you for your help.
Regards,
Alexandra DANTE


Chris Browne a écrit :

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hello,

The postmaster is launched by the user "pg_810" who is not the root user.
When I launch the "ulimit -a" command, I've got :
$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) unlimited
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 2000

You will find below the explain plan of one of the queries which has
finished with "out of memory". This query contains aggregate and a
sub-select with 6 joins :
QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------
---------
GroupAggregate (cost=103283274.03..103283274.07 rows=1 width=76)
-> Sort (cost=103283274.03..103283274.04 rows=1 width=76)
Sort Key: nation.n_name, date_part('year'::text,
(orders.o_orderdate)::timestamp without time zone)
-> Nested Loop (cost=2447049.00..103283274.02 rows=1 width=76)
Join Filter: ("outer".s_nationkey = "inner".n_nationkey)
-> Nested Loop (cost=2447049.00..103283272.45 rows=1
width=55)
-> Nested Loop (cost=2447049.00..103283267.25
rows=1 width=59)
-> Hash Join (cost=2447049.00..103256685.03
rows=4800 width=80)
Hash Cond: ("outer".l_suppkey =
"inner".s_suppkey)
-> Hash Join
(cost=2311445.00..102985544.04 rows=2880228 width=64)
Hash Cond: ("outer".l_partkey =
"inner".p_partkey)
-> Seq Scan on lineitem
(cost=0.00..69142803.64 rows=1800142464 width=56)
-> Hash
(cost=2311205.00..2311205.00 rows=96000 width=8)
-> Seq Scan on part
(cost=0.00..2311205.00 rows=96000 width=8)
Filter:
((p_name)::text ~~ '%green%'::text)
-> Hash (cost=110525.00..110525.00
rows=3000000 width=16)
-> Seq Scan on supplier
(cost=0.00..110525.00 rows=3000000 width=16)
-> Index Scan using i_ps_partkey_suppkey on
partsupp (cost=0.00..5.52 rows=1 width=27)
Index Cond: ((partsupp.ps_partkey =
"outer".l_partkey) AND (partsupp.ps_suppkey = "outer".l_s
uppkey))
-> Index Scan using i_o_orderkey on orders
(cost=0.00..5.19 rows=1 width=12)
Index Cond: (orders.o_orderkey =
"outer".l_orderkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=37)
(22 rows)

Regards,
Alexandra DANTE

Tom Lane a écrit :

>DANTE ALEXANDRA <ALEXANDRA.DANTE@BULL.NET> 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.
>>
>>
>
>Hmm ... what ulimit settings are you running the postmaster under?
>Could we see the EXPLAIN plans for some of the failing queries?
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, Dec 20, 2005 at 01:35:03PM +0100, DANTE ALEXANDRA wrote:
> You will find below the explain plan of one of the queries which has
> finished with "out of memory". This query contains aggregate and a
> sub-select with 6 joins :

1. Firstly, it could be the Hash node. Does the estimated number of
matches in part (96000 rows) match reality?

2. Secondly, looks like lineitem could use an index on partkey. Maybe it
could then use a more efficient join?

Do you have indexes on the relevent columns?

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDqAFlIB7bNG8LQkwRAgxZAJ4ngpLOLjZ3sP/rAjv80XY+jWNc8QCfS051
HjKuw4pwWJuF1wFP1N26740=
=+xrY
-----END PGP SIGNATURE-----

DANTE ALEXANDRA wrote:
> The person is charge of building PostGreSQL 8.1.0 has done a 32 bit
> build and has used the "cc_r" compiler.
> This person does not succeed to build PostGreSQL 8.1.0 with "gcc" and 64
> bits. Unfortunatly, I don't have the errors or the logs of the 64 bits
> build and I can't tell you what error occurs.

Too bad, I may have been able to determine what had happened with the
gcc build.

> The build done was realized in 32 bits, with the cc_r compiler.
> To build POstGreSQL, a rpm was done and the ".spec" file contained the
> following instructions :
> export OBJECT_MODE=32
> ./configure CC=/usr/vac/bin/cc_r CFLAGS="-O2 -qmaxmem=-1 -qsrcmsg
> -qlargepage" --enable-thread-safety
> --without-readline --prefix=%{buildroot}%{prefix}
> gmake -j 4
> unset OBJECT_MODE
>
> Do you think that my problems of "out of memory" are due to the 32 bits
> build ?
> Do you think that I must build PostGreSQL wih 64 bits to solve this error ?

It is quite likely that the out of memory errors are due to your use of
the default 32-bit memory model. In that model, a single 256MB memory
segment contains your heap, stack, thread stacks, and other per-process,
non-shared-library data. Switching to 64-bit would stop the errors if
this is true. It is also possible to adjust the amount of space
available to a 32-bit process' heap with the -bmaxdata linker option,
but the largest heap size that I would consider safe with 32-bit is 2GB
and comes with the cost of reducing the amount of shared memory
available to the process.

Setting OBJECT_MODE to 64 before the ./configure and gmake should result
in a 64-bit build, but I don't have a copy of IBM's compiler to test
with. I would be interested in seeing the errors output by the 64-bit
gcc build if another build is attempted.

--
Seneca Cunningham
scunning@ca.afilias.info

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

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Dec 20, 2005 at 01:35:03PM +0100, DANTE ALEXANDRA wrote:
>> You will find below the explain plan of one of the queries which has
>> finished with "out of memory". This query contains aggregate and a
>> sub-select with 6 joins :

> 1. Firstly, it could be the Hash node. Does the estimated number of
> matches in part (96000 rows) match reality?

Actually, the hash on "supplier" (3000000 rows) looks like a bigger
risk. But if this is 8.1 then there is code in there to spill oversize
hash tables to disk, so I don't understand where the memory is going.

The "out of memory" failure should have provoked a MemoryContextStats
report in the postmaster log. Are there a bunch of lines like
%s: %ld total in %ld blocks; %ld free (%ld chunks); %ld used
and if so could we see 'em?

regards, tom lane

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

I'm certainly not an AIX expert, but I remember my 32-bit AIX programs
being limited to 256MB of heap by default. When I linked, I think I had
to ask for more maximum data page space using something like:

-bmaxdata:0x40000000

(which asks for 1GB, I believe)

-Kevin Murphy


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