|
Home > Archive > PostgreSQL Discussion > December 2005 > out of memory during query execution
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
out of memory during query execution
|
|
| DANTE ALEXANDRA 2005-12-19, 9:23 am |
| 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_transa
ctions = 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
| |
| Chris Browne 2005-12-19, 9:23 am |
| 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_transa
ctions = 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/>
| |
| Seneca Cunningham 2005-12-19, 9:23 am |
| 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
| |
| Tom Lane 2005-12-19, 9:23 am |
| 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
| |
| DANTE ALEXANDRA 2005-12-20, 3:23 am |
| 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:
>
>
>
>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
| |
| DANTE ALEXANDRA 2005-12-20, 7:23 am |
| 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'::te
xt,
(orders. o_orderdate)::timest
amp 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:
>
>
>
>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
| |
| Martijn van Oosterhout 2005-12-20, 7:23 am |
| 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.
| |
| Seneca Cunningham 2005-12-20, 11:23 am |
| 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
| |
| Tom Lane 2005-12-20, 11:23 am |
| Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Dec 20, 2005 at 01:35:03PM +0100, DANTE ALEXANDRA wrote:
[color=darkred]
> 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
| |
| Kevin Murphy 2005-12-20, 11:23 am |
| 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
| |
| DANTE ALEXANDRA 2005-12-20, 11:23 am |
| Hello,
The part table contains 60000000 rows, so I think that the 96000 rows
estimated matches in part could match reality.
Currently, the lineitem table contains only one index :
TPCH=# \d lineitem
Table "public.lineitem"
Column | Type | Modifiers
-----------------+-----------------------+-----------
l_orderkey | bigint | not null
l_partkey | bigint | not null
l_suppkey | bigint | not null
l_linenumber | bigint | not null
l_quantity | numeric |
l_extendedprice | numeric |
l_discount | numeric |
l_tax | numeric | not null
l_returnflag | character(1) |
l_linestatus | character(1) |
l_shipdate | date |
l_commitdate | date |
l_receiptdate | date |
l_shipinstruct | character(25) |
l_shipmode | character(10) |
l_comment | character varying(44) |
Indexes:
"i_l_orderkey" btree (l_orderkey), tablespace "tb_index"
Tablespace: "tb_lit"
I think I will try to optimize PostGreSQL in a second time by creating
appropriate indexes.
I don't think that this index is on relevent column for this query.
Regards,
Alexandra DANTE
Martijn van Oosterhout a écrit :
>On Tue, Dec 20, 2005 at 01:35:03PM +0100, DANTE ALEXANDRA wrote:
>
>
>
>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,
>
>
---------------------------(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
| |
| Tom Lane 2005-12-20, 11:23 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| DANTE ALEXANDRA 2005-12-21, 7:23 am |
| Hello,
Thank you for all the answers I've got on this problem.
Tom, I've checked this morning if the logfile contains lines like
%s: %ld total in %ld blocks; %ld free (%ld chunks); %ld used
and it is the case.
As I launch a series of queries, I hope that the following lines are the
exact lines for the query which has finished with an "out of memory" :
TopMemoryContext: 40960 total in 4 blocks; 11248 free (9 chunks); 29712 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks);
6328 used
TopTransactionContex
t: 8192 total in 1 blocks; 7856 free (0 chunks); 336
used
MessageContext: 253952 total in 5 blocks; 7784 free (8 chunks); 246168 used
smgr relation table: 8192 total in 1 blocks; 2872 free (0 chunks); 5320 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 used
ExecutorState: 21586616 total in 25 blocks; 14624 free (33 chunks);
21571992 used
HashTableContext: 8192 total in 1 blocks; 8128 free (2 chunks); 64 used
HashBatchContext: 134152352 total in 22 blocks; 14859064 free (55434
chunks); 119293288 used
HashTableContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used
HashBatchContext: 108527800 total in 16 blocks; 1016512 free (24
chunks); 107511288 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used
AggContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 108608 free (0 chunks);
407488 used
i_o_orderkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
i_ps_partkey_suppkey
: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
i_l_orderkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_in
dex: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_type_typname_nsp_
index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_t
gname_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_statistic_relid_a
tt_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_memb
er_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role
_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulen
ame_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_proc_proname_args
_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_
l_r_n_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oid_inde
x: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_oid_index
: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_am_name_n
sp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_namespace_oid_ind
ex: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_namespace_nspname
_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_language_oid_inde
x: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_name_ind
ex: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_se
qno_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_index_indexrelid_
index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_authid_oid_index:
1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_rolname_in
dex: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_database_datname_
index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_oid_in
dex: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_name_n
sp_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_conversion_defaul
t_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_class_relname_nsp
_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_cast_source_targe
t_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_a
ttnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_a
ttnam_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_amproc_opc_proc_i
ndex: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_amop_opr_opc_inde
x: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amop_opc_strat_in
dex: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_aggregate_fnoid_i
ndex: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
MdSmgr: 24576 total in 2 blocks; 9712 free (1 chunks); 14864 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0
chunks); 4280 used
Timezones: 48408 total in 2 blocks; 5968 free (0 chunks); 42440 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ERROR: out of memory
DETAIL: Failed on request of size 148.
I hope this will answer to your question.
Thank you for your help.
Regards,
Alexandra DANTE
Tom Lane a écrit :
>Martijn van Oosterhout <kleptog@svana.org> writes:
>
>
>
>
>
>
>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 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| DANTE ALEXANDRA 2005-12-21, 7:23 am |
| Hello,
According to several answers, my problem could be due to the 32 bits build.
I hope I will try to make a 64 bits build in January, in order to give
you more explanations about the possible errors I will encounter.
I keep preciously your advice, and I hope I will be able to give you an
answer in January.
Thank you.
Regards,
Alexandra DANTE
Seneca Cunningham a écrit :
>DANTE ALEXANDRA wrote:
>
>
>
>Too bad, I may have been able to determine what had happened with the
>gcc build.
>
>
>
>
>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.
>
>
>
---------------------------(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 2005-12-21, 7:23 am |
| On Wed, Dec 21, 2005 at 10:22:05AM +0100, DANTE ALEXANDRA wrote:
> Hello,
>
> Thank you for all the answers I've got on this problem.
>
> Tom, I've checked this morning if the logfile contains lines like
> %s: %ld total in %ld blocks; %ld free (%ld chunks); %ld used
> and it is the case.
<snip>
> ExecutorState: 21586616 total in 25 blocks; 14624 free (33 chunks); 21571992 used
> HashTableContext: 8192 total in 1 blocks; 8128 free (2 chunks); 64 used
> HashBatchContext: 134152352 total in 22 blocks; 14859064 free (55434 chunks); 119293288 used
> HashTableContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used
> HashBatchContext: 108527800 total in 16 blocks; 1016512 free (24 chunks);107511288 used
These are by far the largest and seem to confirm that your total memory
usage is limited to not much more than 256MB, so what that other poster
said may be relevent. Whether that as reasonable amount for a hash
table to use in your context, I'll leave that to someone else...
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.
| |
| DANTE ALEXANDRA 2005-12-21, 7:23 am |
| Hello,
Just to give you explanation, I will try to explain you why I have set
the "work_mem" to 64MB.
I noticed that some of the queries have finished with an "out of memory"
because the file system on which temporary files were created was to
small. Consequently, I have increase the size of this file system and I
have set work_mem to 64MB. The query still continued to swap into
temporary files but did not finish with an "out of memory".
The next steps for me will be to :
- decrease the work_mem
- try to build PostGreSQL with 32 bits and the option "-bmaxdata:0x40000000"
- try to build PostGreSQL with 64 bits.
I will give you the results as soon as possible.
Thank you for your help.
Regards,
Alexandra DANTE
Tom Lane a écrit :
>Kevin Murphy <murphy@genome.chop.edu> writes:
>
>
>
>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
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| DANTE ALEXANDRA 2005-12-21, 11:23 am |
| 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:
>
>
>
>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
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Tom Lane 2005-12-21, 11:23 am |
| DANTE ALEXANDRA <ALEXANDRA.DANTE@BULL.NET> writes:
> 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" ?
Right, exactly. In this particular case I think the top-level sort is
not going to be using much memory because it won't see very many rows,
but potentially it could try to eat 64Mb just like each of the hashes.
Your log entries show that the hashes are actually eating over 100Mb
apiece. The memory space estimation for work_mem is not completely
accurate, and is not intended to be, but I would have liked to think
it would be closer than a factor-of-2 error. Might be worth looking
into exactly what's happening there.
> Last question, how can I see that my 32-bit AIX program being limited to
> 256MB of heap,
For that you need to talk to an AIX expert, which I'm not.
regards, tom lane
---------------------------(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
| |
| Seneca Cunningham 2005-12-21, 11:23 am |
| DANTE ALEXANDRA wrote:
> 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
It's actually less than 256MB of heap. When I saw your ./configure, I
noticed that no special options were passed to the linker, so you're
using the default 32-bit memory model. Even an unlimited ulimit does
not allow for more memory than the model sets aside.
You can try setting the environment variable LDR_CNTRL to
MAXDATA=0x40000000 (where the first digit is the number of 256MB
segments to allocate to heap, max 8) before starting the postmaster, at
the cost of reducing the amount of shared memory addressable by postgres.
Diagram of default memory model (Figure 3-3):
<http://www.redbooks.ibm.com/redbook...es/11-08-05.jpg>
The redbook it's from, "Developing and Porting C and C++ Applications on
AIX":
<http://www.redbooks.ibm.com/redbooks/SG245674/>
--
Seneca Cunningham
scunning@ca.afilias.info
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
|
|
|
|