Skip site navigation (1) Skip section navigation (2)

Out of memory error during large hashagg

From: Casey Duncan <casey(at)pandora(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Out of memory error during large hashagg
Date: 2006-09-18 21:08:22
Message-ID: 3D188D2F-4AF3-4C2A-BF29-478EC0638FF7@pandora.com (view raw or flat)
Thread:
Lists: pgsql-bugs
I've reported variants of this in the past, but this case is entirely  
repeatable.

Executing this query:

select st_id, min(seed_id) as "initial_seed_id", count(*) as  
"seed_count"
from seed group by st_id;

The query plan and table stats are:

                               QUERY PLAN
-----------------------------------------------------------------------
HashAggregate  (cost=1362694.83..1365164.68 rows=164656 width=16)
    ->  Seq Scan on seed  (cost=0.00..964065.62 rows=53150562 width=16)


  relname | relpages |  reltuples
---------+----------+-------------
seed    |   428880 | 5.26984e+07

Environment:

Debian linux, kernel v. 2.6.13.1-20050914
Dual opterons w/8G RAM
Postgresql v 8.1.3

Pg Config:

shared_buffers = 50000
work_mem = 262144
maintenance_work_mem = 262144
max_fsm_pages = 100000

When the query blows up I get this spew in the server log:

TopMemoryContext: 40960 total in 5 blocks; 12192 free (11 chunks);  
28768 used
TopTransactionContext: 8192 total in 1 blocks; 5784 free (0 chunks);  
2408 used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks);  
6328 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks);  
3256 used
MessageContext: 24576 total in 2 blocks; 15080 free (4 chunks); 9496  
used
smgr relation table: 8192 total in 1 blocks; 1840 free (0 chunks);  
6352 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; 896 free (0 chunks); 128 used
ExecutorState: 8192 total in 1 blocks; 1928 free (17 chunks); 6264 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
AggContext: -2001739776 total in 285 blocks; 4784 free (141 chunks);  
-2001744560 used
TupleHashTable: 497279000 total in 70 blocks; 1772200 free (259  
chunks); 495506800 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
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 46448 free (6 chunks);  
469648 used
pg_shdepend_depender_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_depend_depender_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_depend_reference_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
seed_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
seed_station_music_id_idx: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
seed_music_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
seed_station_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
feedback_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
feedback_date_created_is_positive_idx: 1024 total in 1 blocks; 328  
free (0 chunks); 696 used
feedback_station_music_id_idx: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
feedback_station_id_idx: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
listener_segment_pkey: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
listener_expiration_date_idx: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
listener_default_web_name_key: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
listener_default_username_key: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
listener_default_pkey: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 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_tgname_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_auth_members_member_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_rulename_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_index: 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_nsp_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_namespace_oid_index: 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_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_inherits_relid_seqno_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_index: 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_index: 1024 total in 1 blocks; 392 free (0 chunks);  
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_conversion_default_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_target_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0  
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0  
chunks); 768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks);  
696 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks);  
768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0  
chunks); 632 used
MdSmgr: 8192 total in 1 blocks; 4768 free (1 chunks); 3424 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0  
chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
2006-09-17 21:31:42.753 PDT [d:radio_prod_full u:radio s: 
450e0813.6b5d 4] ERROR:  out of memory
2006-09-17 21:31:42.753 PDT [d:radio_prod_full u:radio s: 
450e0813.6b5d 5] DETAIL:  Failed on request of size 88.
2006-09-17 21:31:42.753 PDT [d:radio_prod_full u:radio s: 
450e0813.6b5d 6] STATEMENT:  select st_id, min(seed_id) as  
"initial_seed_id",
                 count(*) as "seed_count"
         from seed group by st_id;


If I execute "set enable_hashagg=off;" before running this query it  
completes successfully.

Thanks!

-Casey




Responses

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2006-09-18 21:41:26
Subject: Re: BUG #2632: createuser language poblem
Previous:From: Kiren PillayDate: 2006-09-18 21:03:14
Subject: BUG #2636: JDBC error behaviour incorrect

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group