Out of memory error during large hashagg - Mailing list pgsql-bugs
From | Casey Duncan |
---|---|
Subject | Out of memory error during large hashagg |
Date | |
Msg-id | 3D188D2F-4AF3-4C2A-BF29-478EC0638FF7@pandora.com Whole thread Raw |
Responses |
Re: Out of memory error during large hashagg
Re: Out of memory error during large hashagg |
List | 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
pgsql-bugs by date: