ERROR: out of memory, running aggregate query
От | Casey Duncan |
---|---|
Тема | ERROR: out of memory, running aggregate query |
Дата | |
Msg-id | 9C14CB9A-E1C9-4C9A-8F85-D2934F8A7C24@pandora.com обсуждение исходный текст |
Список | pgsql-bugs |
I have the following query which ran fine on 7.4 and fails consistently now that I've upgraded to 8.1.3. Here is the query and the plan (I can't explain analyze because of the memory error): prod=> explain select count(*) from prod-> (select st_id from sd prod(> group by st_id having count(*) > 1) prod-> as multi_sd; QUERY PLAN ------------------------------------------------------------------------ ---- Aggregate (cost=558194.08..558194.09 rows=1 width=0) -> HashAggregate (cost=555076.97..556777.21 rows=113349 width=4) Filter: (count(*) > 1) -> Seq Scan on sd (cost=0.00..428498.65 rows=25315665 width=4) (4 rows) Here is what I see in the back-end logs: TopMemoryContext: 61976 total in 6 blocks; 10712 free (16 chunks); 51264 used TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 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; 9520 free (1 chunks); 15056 used smgr relation table: 8192 total in 1 blocks; 808 free (0 chunks); 7384 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: 24576 total in 2 blocks; 18736 free (11 chunks); 5840 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used AggContext: -1846550528 total in 304 blocks; 5232 free (158 chunks); -1846555760 used TupleHashTable: 587456536 total in 80 blocks; 2776520 free (298 chunks); 584680016 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 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; 106504 free (1 chunks); 409592 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used seed_station_music_id_idx: 1024 total in 1 blocks; 328 free (0 chunks); 696 used seed_station_id: 1024 total in 1 blocks; 392 free (0 chunks); 632 used seed_music_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used seed_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used listener_to_station_station_id_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used listener_to_station_pkey: 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 feedback_music_id_idx: 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_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 listener_web_name_key: 1024 total in 1 blocks; 392 free (0 chunks); 632 used listener_username_key: 1024 total in 1 blocks; 392 free (0 chunks); 632 used listener_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_constraint_conrelid_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_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_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; 5128 free (0 chunks); 3064 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-05-04 06:13:17.614 PDT [d:prod u:casey s:4459d0a1.54c 3] ERROR: out of memory 2006-05-04 06:13:17.614 PDT [d:prod u:casey s:4459d0a1.54c 4] DETAIL: Failed on request of size 80. This line in particular looks suspicious to me: AggContext: -1846550528 total in 304 blocks; 5232 free (158 chunks); -1846555760 used Here are some of the memory-related config settings for this server: shared_buffers = 20000 # min 16 or max_connections*2, 8KB each work_mem = 8192 # 8 Mb maintenance_work_mem = 131072 # 128 Mb The machine itself is a dual opteron with 8GB of RAM running debian linux. The postgres backend process running this query bloats up to roughly 3GB resident or so before blowing up. Let me know if I can provide any other details or perform other tests. Thanks much. -Casey
В списке pgsql-bugs по дате отправления: