pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedout of memory
23+ messages / 10 participants
[nested] [flat]
* out of memory
@ 2006-02-14 15:24 martial.bizel@free.fr
0 siblings, 1 reply; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-14 15:24 UTC (permalink / raw)
To: pgsql-performance
Hello,
I've error "out of memory" with these traces :
TopMemoryContext: 32768 total in 3 blocks; 5152 free (1 chunks); 27616 used
TopTransactionContext: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used
MessageContext: 24576 total in 2 blocks; 2688 free (14 chunks); 21888 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 8192 total in 1 blocks; 3936 free (0 chunks); 4256 used
PortalHeapMemory: 23552 total in 5 blocks; 1160 free (4 chunks); 22392 used
ExecutorState: 8192 total in 1 blocks; 3280 free (4 chunks); 4912 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
ExecutorState: 24576 total in 2 blocks; 11264 free (14 chunks); 13312 used
ExprContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used
AggContext: -1976573952 total in 287 blocks; 25024 free (414 chunks);
-1976598976 used
DynaHashTable: 503439384 total in 70 blocks; 6804760 free (257 chunks);
496634624 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 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
CacheMemoryContext: 516096 total in 6 blocks; 126648 free (2 chunks); 389448
used
test_query: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
test_date: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
query_string_query_string_key: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
query_string_pkey: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_index_indrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_conversion_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_language_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384
used
pg_conversion_default_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_language_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_group_sysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_namespace_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_proc_proname_args_nsp_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_opclass_am_name_nsp_index: 2048 total in 1 blocks; 768 free (0 chunks); 1280
used
pg_group_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_index_indexrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_operator_oprname_l_r_n_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_opclass_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 320 free (0 chunks); 704
used
pg_type_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 320 free (0 chunks);
704 used
pg_class_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
MdSmgr: 8192 total in 1 blocks; 5712 free (0 chunks); 2480 used
DynaHash: 8192 total in 1 blocks; 6912 free (0 chunks); 1280 used
DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used
DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
DynaHashTable: 8192 total in 1 blocks; 3016 free (0 chunks); 5176 used
DynaHashTable: 8192 total in 1 blocks; 4040 free (0 chunks); 4152 used
DynaHashTable: 24576 total in 2 blocks; 13240 free (4 chunks); 11336 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ErrorContext: 8192 total in 1 blocks; 8176 free (6 chunks); 16 used
2006-02-14 16:06:14 [25816] ERROR: out of memory
DETAIL: Failed on request of size 88.
ERROR: out of memory
DETAIL: Failed on request of size 88.
Anybody could help me ??
Thanks a lot
MB
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 15:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: martial.bizel@free.fr
0 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2006-02-14 15:32 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: pgsql-performance
martial.bizel@free.fr writes:
> I've error "out of memory" with these traces :
Doing what?
> AggContext: -1976573952 total in 287 blocks; 25024 free (414 chunks);
> -1976598976 used
> DynaHashTable: 503439384 total in 70 blocks; 6804760 free (257 chunks);
> 496634624 used
I'd guess that a HashAgg operation ran out of memory ...
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 16:03 martial.bizel@free.fr
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-14 16:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks for your response,
I've made this request :
SELECT query_string, DAY.ocu from search_data.query_string,
(SELECT SUM(occurence) as ocu, query
FROM daily.queries_detail_statistics
WHERE date >= '2006-01-01' AND date <= '2006-01-30'
AND portal IN (1,2)
GROUP BY query
ORDER BY ocu DESC
LIMIT 1000) as DAY
WHERE DAY.query=id;
and after few minutes, i've error "out of memory" with this execution plan :
Nested Loop (cost=8415928.63..8418967.13 rows=1001 width=34)
-> Subquery Scan "day" (cost=8415928.63..8415941.13 rows=1000 width=16)
-> Limit (cost=8415928.63..8415931.13 rows=1000 width=12)
-> Sort (cost=8415928.63..8415932.58 rows=1582 width=12)
Sort Key: sum(occurence)
-> HashAggregate (cost=8415840.61..8415844.56 rows=1582
width=12)
-> Seq Scan on queries_detail_statistics
(cost=0.00..8414056.00 rows=356922 width=12)
Filter: ((date >= '2006-01-01'::date) AND (date
<= '2006-01-30'::date) AND (((portal)::text = '1'::text) OR ((portal)::text =
'2'::text)))
-> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
rows=1 width=34)
Index Cond: ("outer".query = query_string.id)
(10 rows)
if HashAgg operation ran out of memory, what can i do ?
thanks a lot
martial
> martial.bizel@free.fr writes:
> > I've error "out of memory" with these traces :
>
> Doing what?
>
> > AggContext: -1976573952 total in 287 blocks; 25024 free (414 chunks);
> > -1976598976 used
> > DynaHashTable: 503439384 total in 70 blocks; 6804760 free (257 chunks);
> > 496634624 used
>
> I'd guess that a HashAgg operation ran out of memory ...
>
> regards, tom lane
>
> ---------------------------(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
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 16:06 Scott Marlowe <smarlowe@g2switchworks.com>
parent: martial.bizel@free.fr
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2006-02-14 16:06 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, 2006-02-14 at 10:03, martial.bizel@free.fr wrote:
> Thanks for your response,
SNIP
> if HashAgg operation ran out of memory, what can i do ?
1: Don't top post.
2: Have you run analyze? Normally when hash agg runs out of memory, the
planner THOUGHT the hash agg would fit in memory, but it was larger than
expected. This is commonly a problem when you haven't run analyze.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 16:15 martial.bizel@free.fr
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-14 16:15 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Yes, I've launched ANALYZE command before sending request.
I precise that's postgres version is 7.3.4
> On Tue, 2006-02-14 at 10:03, martial.bizel@free.fr wrote:
> > Thanks for your response,
>
> SNIP
>
> > if HashAgg operation ran out of memory, what can i do ?
>
> 1: Don't top post.
>
> 2: Have you run analyze? Normally when hash agg runs out of memory, the
> planner THOUGHT the hash agg would fit in memory, but it was larger than
> expected. This is commonly a problem when you haven't run analyze.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 16:21 Scott Marlowe <smarlowe@g2switchworks.com>
parent: martial.bizel@free.fr
1 sibling, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2006-02-14 16:21 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, 2006-02-14 at 10:15, martial.bizel@free.fr wrote:
> Yes, I've launched ANALYZE command before sending request.
> I precise that's postgres version is 7.3.4
So what does explain analyze show for this query, if anything? Can you
increase your sort_mem or shared_buffers (I forget which hash_agg uses
off the top of my head...) if necessary to make it work. Note you can
increase sort_mem on the fly for a given connection.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 16:32 martial.bizel@free.fr
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-14 16:32 UTC (permalink / raw)
To: pgsql-performance
command explain analyze crash with the "out of memory" error
I precise that I've tried a lot of values from parameters shared_buffer and
sort_mem
now, in config file, values are :
sort_mem=32768
and shared_buffer=30000
server has 4Go RAM.
and kernel.shmmax=307200000
> On Tue, 2006-02-14 at 10:15, martial.bizel@free.fr wrote:
> > Yes, I've launched ANALYZE command before sending request.
> > I precise that's postgres version is 7.3.4
>
> So what does explain analyze show for this query, if anything? Can you
> increase your sort_mem or shared_buffers (I forget which hash_agg uses
> off the top of my head...) if necessary to make it work. Note you can
> increase sort_mem on the fly for a given connection.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 16:50 Scott Marlowe <smarlowe@g2switchworks.com>
parent: martial.bizel@free.fr
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2006-02-14 16:50 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: pgsql-performance
On Tue, 2006-02-14 at 10:32, martial.bizel@free.fr wrote:
> command explain analyze crash with the "out of memory" error
>
> I precise that I've tried a lot of values from parameters shared_buffer and
> sort_mem
>
> now, in config file, values are :
> sort_mem=32768
> and shared_buffer=30000
OK, on the command line, try increasing the sort_mem until hash_agg can
work. With a 4 gig machine, you should be able to go as high as needed
here, I'd think. Try as high as 500000 or so or more. Then when
explain analyze works, compare the actual versus estimated number of
rows.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 17:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: martial.bizel@free.fr
1 sibling, 1 reply; 23+ messages in thread
From: Tom Lane @ 2006-02-14 17:36 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
martial.bizel@free.fr writes:
> Yes, I've launched ANALYZE command before sending request.
> I precise that's postgres version is 7.3.4
Can't possibly be 7.3.4, that version didn't have HashAggregate.
How many distinct values of "query" actually exist in the table?
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-14 17:47 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2006-02-14 17:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: martial.bizel@free.fr; pgsql-performance
On Tue, 2006-02-14 at 11:36, Tom Lane wrote:
> martial.bizel@free.fr writes:
> > Yes, I've launched ANALYZE command before sending request.
> > I precise that's postgres version is 7.3.4
>
> Can't possibly be 7.3.4, that version didn't have HashAggregate.
>
> How many distinct values of "query" actually exist in the table?
I thought that looked odd.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-15 08:34 martial.bizel@free.fr
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-15 08:34 UTC (permalink / raw)
To: pgsql-performance
Good morning,
I've increased sort_mem until 2Go !!
and the error "out of memory" appears again.
Here the request I try to pass with her explain plan,
Nested Loop (cost=2451676.23..2454714.73 rows=1001 width=34)
-> Subquery Scan "day" (cost=2451676.23..2451688.73 rows=1000 width=16)
-> Limit (cost=2451676.23..2451678.73 rows=1000 width=12)
-> Sort (cost=2451676.23..2451684.63 rows=3357 width=12)
Sort Key: sum(occurence)
-> HashAggregate (cost=2451471.24..2451479.63 rows=3357
width=12)
-> Index Scan using test_date on
queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12)
Index Cond: ((date >= '2006-01-01'::date) AND
(date <= '2006-01-30'::date))
Filter: (((portal)::text = '1'::text) OR
((portal)::text = '2'::text))
-> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
rows=1 width=34)
Index Cond: ("outer".query = query_string.id)
(11 rows)
Any new ideas ?,
thanks
MB.
> On Tue, 2006-02-14 at 10:32, martial.bizel@free.fr wrote:
> > command explain analyze crash with the "out of memory" error
> >
> > I precise that I've tried a lot of values from parameters shared_buffer and
> > sort_mem
> >
> > now, in config file, values are :
> > sort_mem=32768
> > and shared_buffer=30000
>
> OK, on the command line, try increasing the sort_mem until hash_agg can
> work. With a 4 gig machine, you should be able to go as high as needed
> here, I'd think. Try as high as 500000 or so or more. Then when
> explain analyze works, compare the actual versus estimated number of
> rows.
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* out of memory
@ 2006-02-15 15:55 martial.bizel@free.fr
0 siblings, 1 reply; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-15 15:55 UTC (permalink / raw)
To: pgsql-performance
Good morning,
I've increased sort_mem until 2Go !!
and the error "out of memory" appears again.
Here the request I try to pass with her explain plan,
Nested Loop (cost=2451676.23..2454714.73 rows=1001 width=34)
-> Subquery Scan "day" (cost=2451676.23..2451688.73 rows=1000 width=16)
-> Limit (cost=2451676.23..2451678.73 rows=1000 width=12)
-> Sort (cost=2451676.23..2451684.63 rows=3357 width=12)
Sort Key: sum(occurence)
-> HashAggregate (cost=2451471.24..2451479.63 rows=3357
width=12)
-> Index Scan using test_date on
queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12)
Index Cond: ((date >= '2006-01-01'::date) AND
(date <= '2006-01-30'::date))
Filter: (((portal)::text = '1'::text) OR
((portal)::text = '2'::text))
-> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
rows=1 width=34)
Index Cond: ("outer".query = query_string.id)
(11 rows)
Any new ideas ?,
thanks
MB.
> On Tue, 2006-02-14 at 10:32, martial.bizel@free.fr wrote:
> > command explain analyze crash with the "out of memory" error
> >
> > I precise that I've tried a lot of values from parameters shared_buffer and
> > sort_mem
> >
> > now, in config file, values are :
> > sort_mem=32768
> > and shared_buffer=30000
>
> OK, on the command line, try increasing the sort_mem until hash_agg can
> work. With a 4 gig machine, you should be able to go as high as needed
> here, I'd think. Try as high as 500000 or so or more. Then when
> explain analyze works, compare the actual versus estimated number of
> rows.
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-15 16:45 martial.bizel@free.fr
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-15 16:45 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
You're right, release is 7.4.7.
there's twenty millions records "query"
> On Tue, 2006-02-14 at 11:36, Tom Lane wrote:
> > martial.bizel@free.fr writes:
> > > Yes, I've launched ANALYZE command before sending request.
> > > I precise that's postgres version is 7.3.4
> >
> > Can't possibly be 7.3.4, that version didn't have HashAggregate.
> >
> > How many distinct values of "query" actually exist in the table?
>
> I thought that looked odd.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-15 16:50 Scott Marlowe <smarlowe@g2switchworks.com>
parent: martial.bizel@free.fr
0 siblings, 1 reply; 23+ messages in thread
From: Scott Marlowe @ 2006-02-15 16:50 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: pgsql-performance
On Wed, 2006-02-15 at 09:55, martial.bizel@free.fr wrote:
> Good morning,
>
>
>
>
> I've increased sort_mem until 2Go !!
> and the error "out of memory" appears again.
>
> Here the request I try to pass with her explain plan,
>
> Nested Loop (cost=2451676.23..2454714.73 rows=1001 width=34)
> -> Subquery Scan "day" (cost=2451676.23..2451688.73 rows=1000 width=16)
> -> Limit (cost=2451676.23..2451678.73 rows=1000 width=12)
> -> Sort (cost=2451676.23..2451684.63 rows=3357 width=12)
> Sort Key: sum(occurence)
> -> HashAggregate (cost=2451471.24..2451479.63 rows=3357
> width=12)
> -> Index Scan using test_date on
> queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12)
> Index Cond: ((date >= '2006-01-01'::date) AND
> (date <= '2006-01-30'::date))
> Filter: (((portal)::text = '1'::text) OR
> ((portal)::text = '2'::text))
> -> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
> rows=1 width=34)
> Index Cond: ("outer".query = query_string.id)
> (11 rows)
OK, so it looks like something is horrible wrong here. Try running the
explain analyze query after running the following:
set enable_hashagg=off;
and see what you get then.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-15 17:18 martial.bizel@free.fr
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 23+ messages in thread
From: martial.bizel@free.fr @ 2006-02-15 17:18 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-performance
Here the result with hashAgg to false :
Nested Loop (cost=2487858.08..2490896.58 rows=1001 width=34) (actual
time=1028044.781..1030251.260 rows=1000 loops=1)
-> Subquery Scan "day" (cost=2487858.08..2487870.58 rows=1000 width=16)
(actual time=1027996.748..1028000.969 rows=1000 loops=1)
-> Limit (cost=2487858.08..2487860.58 rows=1000 width=12) (actual
time=1027996.737..1027999.199 rows=1000 loops=1)
-> Sort (cost=2487858.08..2487866.47 rows=3357 width=12)
(actual time=1027996.731..1027998.066 rows=1000 loops=1)
Sort Key: sum(occurence)
-> GroupAggregate (cost=2484802.05..2487661.48 rows=3357
width=12) (actual time=810623.035..914550.262 rows=19422774 loops=1)
-> Sort (cost=2484802.05..2485752.39 rows=380138
width=12) (actual time=810612.248..845427.013 rows=36724340 loops=1)
Sort Key: query
-> Index Scan using test_date on
queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12) (actual
time=25.393..182029.205 rows=36724340 loops=1)
Index Cond: ((date >= '2006-01-01'::date)
AND (date <= '2006-01-30'::date))
Filter: (((portal)::text = '1'::text) OR
((portal)::text = '2'::text))
-> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
rows=1 width=34) (actual time=2.244..2.246 rows=1 loops=1000)
Index Cond: ("outer".query = query_string.id)
Total runtime: 1034357.390 ms
(14 rows)
thanks
table daily has 250 millions records
and field query (bigint) 2 millions, occurence is int.
request with HashAggregate is OK when date is restricted about 15 days like :
SELECT query_string, DAY.ocu from search_data.query_string,
(SELECT SUM(occurence) as ocu, query
FROM daily.queries_detail_statistics
WHERE date >= '2006-01-01' AND date <= '2006-01-15'
AND portal IN (1,2)
GROUP BY query
ORDER BY ocu DESC
LIMIT 1000) as DAY
WHERE DAY.query=id;
> On Wed, 2006-02-15 at 09:55, martial.bizel@free.fr wrote:
> > Good morning,
> >
> >
> >
> >
> > I've increased sort_mem until 2Go !!
> > and the error "out of memory" appears again.
> >
> > Here the request I try to pass with her explain plan,
> >
> > Nested Loop (cost=2451676.23..2454714.73 rows=1001 width=34)
> > -> Subquery Scan "day" (cost=2451676.23..2451688.73 rows=1000
> width=16)
> > -> Limit (cost=2451676.23..2451678.73 rows=1000 width=12)
> > -> Sort (cost=2451676.23..2451684.63 rows=3357 width=12)
> > Sort Key: sum(occurence)
> > -> HashAggregate (cost=2451471.24..2451479.63
> rows=3357
> > width=12)
> > -> Index Scan using test_date on
> > queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12)
> > Index Cond: ((date >= '2006-01-01'::date)
> AND
> > (date <= '2006-01-30'::date))
> > Filter: (((portal)::text = '1'::text) OR
> > ((portal)::text = '2'::text))
> > -> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
> > rows=1 width=34)
> > Index Cond: ("outer".query = query_string.id)
> > (11 rows)
>
> OK, so it looks like something is horrible wrong here. Try running the
> explain analyze query after running the following:
>
> set enable_hashagg=off;
>
> and see what you get then.
>
> ---------------------------(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
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2006-02-15 17:38 Scott Marlowe <smarlowe@g2switchworks.com>
parent: martial.bizel@free.fr
0 siblings, 0 replies; 23+ messages in thread
From: Scott Marlowe @ 2006-02-15 17:38 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: pgsql-performance
On Wed, 2006-02-15 at 11:18, martial.bizel@free.fr wrote:
> Here the result with hashAgg to false :
> Nested Loop (cost=2487858.08..2490896.58 rows=1001 width=34) (actual
> time=1028044.781..1030251.260 rows=1000 loops=1)
> -> Subquery Scan "day" (cost=2487858.08..2487870.58 rows=1000 width=16)
> (actual time=1027996.748..1028000.969 rows=1000 loops=1)
> -> Limit (cost=2487858.08..2487860.58 rows=1000 width=12) (actual
> time=1027996.737..1027999.199 rows=1000 loops=1)
> -> Sort (cost=2487858.08..2487866.47 rows=3357 width=12)
> (actual time=1027996.731..1027998.066 rows=1000 loops=1)
> Sort Key: sum(occurence)
> -> GroupAggregate (cost=2484802.05..2487661.48 rows=3357
> width=12) (actual time=810623.035..914550.262 rows=19422774 loops=1)
> -> Sort (cost=2484802.05..2485752.39 rows=380138
> width=12) (actual time=810612.248..845427.013 rows=36724340 loops=1)
> Sort Key: query
> -> Index Scan using test_date on
> queries_detail_statistics (cost=0.00..2449570.55 rows=380138 width=12) (actual
> time=25.393..182029.205 rows=36724340 loops=1)
> Index Cond: ((date >= '2006-01-01'::date)
> AND (date <= '2006-01-30'::date))
> Filter: (((portal)::text = '1'::text) OR
> ((portal)::text = '2'::text))
> -> Index Scan using query_string_pkey on query_string (cost=0.00..3.01
> rows=1 width=34) (actual time=2.244..2.246 rows=1 loops=1000)
> Index Cond: ("outer".query = query_string.id)
> Total runtime: 1034357.390 ms
OK, in the index scan using test_date, you get 36724340 when the planner
expects 380138. That's off by a factor of about 10, so I'm guessing
that your statistics aren't reflecting what's really in your db. You
said before you'd run analyze, so I'd try increasing the stats target on
that column and rerun analyze to see if things get any better.
^ permalink raw reply [nested|flat] 23+ messages in thread
* out of memory
@ 2010-06-13 13:25 AI Rumman <rummandba@gmail.com>
0 siblings, 2 replies; 23+ messages in thread
From: AI Rumman @ 2010-06-13 13:25 UTC (permalink / raw)
To: pgsql-performance
Whenever I run this query, I get out of memory error:
explain analyze
*select *
email_track.count AS "Emails_Access_Count",
activity.subject AS "Emails_Subject",
crmentity.crmid AS EntityId_crmentitycrmid
*from *
(select * from crmentity where deleted = 0 and createdtime between (now() -
interval '6 month') and now() ) as crmentity
inner join
(select * from activity where activitytype = 'Emails' and date_start
between (now() - interval '6 month') and now()) as activity
on crmentity.crmid=activity.activityid
inner join emaildetails on emaildetails.emailid = crmentity.crmid
inner join vantage_email_track on
vantage_email_track.mailid=emaildetails.emailid
left join seactivityrel on seactivityrel.activityid = emaildetails.emailid
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2010-06-13 13:59 Anj Adu <fotographs@gmail.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: Anj Adu @ 2010-06-13 13:59 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
Can you provide these details
work_mem
How much physical memory there is on your system
Most out of memory errors are associated with a high work_mem setting
On Sun, Jun 13, 2010 at 6:25 AM, AI Rumman <rummandba@gmail.com> wrote:
> Whenever I run this query, I get out of memory error:
>
>
> explain analyze
> select
> email_track.count AS "Emails_Access_Count",
> activity.subject AS "Emails_Subject",
> crmentity.crmid AS EntityId_crmentitycrmid
> from
> (select * from crmentity where deleted = 0 and createdtime between (now() -
> interval '6 month') and now() ) as crmentity
> inner join
> (select * from activity where activitytype = 'Emails' and date_start
> between (now() - interval '6 month') and now()) as activity
> on crmentity.crmid=activity.activityid
> inner join emaildetails on emaildetails.emailid = crmentity.crmid
> inner join vantage_email_track on
> vantage_email_track.mailid=emaildetails.emailid
> left join seactivityrel on seactivityrel.activityid = emaildetails.emailid
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2010-06-14 02:10 Kenichiro Tanaka <ketanaka@ashisuto.co.jp>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: Kenichiro Tanaka @ 2010-06-14 02:10 UTC (permalink / raw)
To: pgsql-performance
Hello
I think this SQL returns the following error.
ERROR: missing FROM-clause entry for table "email_track"
LINE 3: email_track.count AS "Emails_Access_Count",
^
For a fact ,this SQL does not have the "email_trac" table in from-clause.
1)Is this SQL right?
2)If the SQL is right, can you write how make your table?
I'd like to try your SQL.
3)What version do you use?
This is my test.
================================================================
--PostgreSQL8.4.4. for CentOS5.3
begin;
create schema email_track;
create table email_track.crmentity
(crmid int,
deleted int,
createdtime date);
create table email_track.activity
(activityid int,
activitytype varchar(100),
date_start date,
subject varchar(100));
create table email_track.emaildetails
(emailid int
);
create table email_track.vantage_email_track
(vantage_email_track int,
mailid int);
create table email_track.seactivityrel
(activityid int);
set search_path to email_track,public;
explain analyze
select
email_track.count AS "Emails_Access_Count",
activity.subject AS "Emails_Subject",
crmentity.crmid AS EntityId_crmentitycrmid
from
(select * from crmentity where deleted = 0 and createdtime between
(now() - interval '6 month') and now() ) as crmentity
inner join
(select * from activity where activitytype = 'Emails' and date_start
between (now() - interval '6 month') and now()) as activity
on crmentity.crmid=activity.activityid
inner join emaildetails on emaildetails.emailid = crmentity.crmid
inner join vantage_email_track on
vantage_email_track.mailid=emaildetails.emailid
left join seactivityrel on seactivityrel.activityid = emaildetails.emailid;
ERROR: missing FROM-clause entry for table "email_track" at character 24
STATEMENT: explain analyze
select
email_track.count AS "Emails_Access_Count",
activity.subject AS "Emails_Subject",
crmentity.crmid AS EntityId_crmentitycrmid
from
(select * from crmentity where deleted = 0 and createdtime
between (now() - interval '6 month') and now() ) as crmentity
inner join
(select * from activity where activitytype = 'Emails' and
date_start between (now() - interval '6 month') and now()) as activity
on crmentity.crmid=activity.activityid
inner join emaildetails on emaildetails.emailid = crmentity.crmid
inner join vantage_email_track on
vantage_email_track.mailid=emaildetails.emailid
left join seactivityrel on seactivityrel.activityid =
emaildetails.emailid;
ERROR: missing FROM-clause entry for table "email_track"
LINE 3: email_track.count AS "Emails_Access_Count",
^
--can not reproduce.
abort;
================================================================
(2010/06/13 22:25), AI Rumman wrote:
> Whenever I run this query, I get out of memory error:
>
>
> explain analyze
> *select *
> email_track.count AS "Emails_Access_Count",
> activity.subject AS "Emails_Subject",
> crmentity.crmid AS EntityId_crmentitycrmid
> *from *
> (select * from crmentity where deleted = 0 and createdtime between
> (now() - interval '6 month') and now() ) as crmentity
> inner join
> (select * from activity where activitytype = 'Emails' and date_start
> between (now() - interval '6 month') and now()) as activity
> on crmentity.crmid=activity.activityid
> inner join emaildetails on emaildetails.emailid = crmentity.crmid
> inner join vantage_email_track on
> vantage_email_track.mailid=emaildetails.emailid
> left join seactivityrel on seactivityrel.activityid = emaildetails.emailid
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* out of memory
@ 2012-10-30 09:14 Mahavir Trivedi <mahavir.trivedi@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Mahavir Trivedi @ 2012-10-30 09:14 UTC (permalink / raw)
To: pgsql-performance
hi
i have sql file (it's size are 1GB )
when i execute it then the String is 987098801 bytr too long for encoding
conversion error occured .
pls give me solution about
i have XP 64-bit with 8 GB RAM shared_buffer 1GB check point = 34
with thanks
mahavir
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: out of memory
@ 2012-10-30 10:08 Tatsuo Ishii <ishii@postgresql.org>
parent: Mahavir Trivedi <mahavir.trivedi@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Tatsuo Ishii @ 2012-10-30 10:08 UTC (permalink / raw)
To: mahavir.trivedi@gmail.com; +Cc: pgsql-performance; pgsql-hackers@postgresql.org
> i have sql file (it's size are 1GB )
> when i execute it then the String is 987098801 bytr too long for encoding
> conversion error occured .
> pls give me solution about
You hit the upper limit of internal memory allocation limit in
PostgreSQL. IMO, there's no way to avoid the error except you use
client encoding identical to backend.
Hackers:
The particular limit seem to be set considering TOAST(from
include/utils/memutils.h):
* XXX This is deliberately chosen to correspond to the limiting size
* of varlena objects under TOAST. See VARSIZE_4B() and related macros
* in postgres.h. Many datatypes assume that any allocatable size can
* be represented in a varlena header.
IMO the SQL string size limit is totally different from
TOAST. Shouldn't we have different limit for SQL string?
(MAX_CONVERSION_GROWTH is different story, of course)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: [HACKERS] out of memory
@ 2012-11-05 17:27 Robert Haas <robertmhaas@gmail.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 23+ messages in thread
From: Robert Haas @ 2012-11-05 17:27 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: mahavir.trivedi@gmail.com; pgsql-performance; pgsql-hackers@postgresql.org
On Tue, Oct 30, 2012 at 6:08 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> i have sql file (it's size are 1GB )
>> when i execute it then the String is 987098801 bytr too long for encoding
>> conversion error occured .
>> pls give me solution about
>
> You hit the upper limit of internal memory allocation limit in
> PostgreSQL. IMO, there's no way to avoid the error except you use
> client encoding identical to backend.
We recently had a customer who suffered a failed in pg_dump because
the quadruple-allocation required by COPY OUT for an encoding
conversion exceeded allocatable memory. I wonder whether it would be
possible to rearrange things so that we can do a "streaming" encoding
conversion. That is, if we have a large datum that we're trying to
send back to the client, could we perhaps chop off the first 50MB or
so, do the encoding on that amount of data, send the data to the
client, lather, rinse, repeat?
Your recent work to increase the maximum possible size of large
objects (for which I thank you) seems like it could make these sorts
of issues more common. As objects get larger, I don't think we can go
on assuming that it's OK for peak memory utilization to keep hitting
5x or more.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: [PERFORM] out of memory
@ 2012-11-05 17:30 John R Pierce <pierce@hogranch.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: John R Pierce @ 2012-11-05 17:30 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
On 11/05/12 9:27 AM, Robert Haas wrote:
> That is, if we have a large datum that we're trying to
> send back to the client, could we perhaps chop off the first 50MB or
> so, do the encoding on that amount of data, send the data to the
> client, lather, rinse, repeat?
I'd suggest work_mem sized chunks for this?
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2012-11-05 17:30 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-14 15:24 out of memory martial.bizel@free.fr
2006-02-14 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-14 16:03 ` martial.bizel@free.fr
2006-02-14 16:06 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-14 16:15 ` martial.bizel@free.fr
2006-02-14 16:21 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-14 16:32 ` martial.bizel@free.fr
2006-02-14 16:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-15 08:34 ` martial.bizel@free.fr
2006-02-14 17:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-14 17:47 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-15 16:45 ` martial.bizel@free.fr
2006-02-15 15:55 out of memory martial.bizel@free.fr
2006-02-15 16:50 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-02-15 17:18 ` martial.bizel@free.fr
2006-02-15 17:38 ` Scott Marlowe <smarlowe@g2switchworks.com>
2010-06-13 13:25 out of memory AI Rumman <rummandba@gmail.com>
2010-06-13 13:59 ` Anj Adu <fotographs@gmail.com>
2010-06-14 02:10 ` Kenichiro Tanaka <ketanaka@ashisuto.co.jp>
2012-10-30 09:14 out of memory Mahavir Trivedi <mahavir.trivedi@gmail.com>
2012-10-30 10:08 ` Tatsuo Ishii <ishii@postgresql.org>
2012-11-05 17:27 ` Robert Haas <robertmhaas@gmail.com>
2012-11-05 17:30 ` John R Pierce <pierce@hogranch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox