Out of Memory Problem. - Mailing list pgsql-performance
From | nicky |
---|---|
Subject | Out of Memory Problem. |
Date | |
Msg-id | 44B4A6F6.8030609@valuecare.nl Whole thread Raw |
List | pgsql-performance |
Hello Everyone, I'm trying to find out/understand what causes my 'out of memory' error. I do not have enough experience with such logs to understand what is wrong or how to fix it. So i hope someone can point me in the right direction. The 'rpt.rpt_verrichting' table contains about 8.5 million records and the 'rpt.rpt_dbc_traject' table contains 700k records. It's part of a nightly process, so there is only 1 user active. The server PostgreSQL 8.1.4 is running on, has 4GB Ram, OS FreeBSD 6.1-Stable. postgresql.conf shared_buffers = 8192 work_mem = 524288 maintenance_work_mem = 524288 effective_cache_size = 104858 Resource limits (current): cputime infinity secs filesize infinity kB datasize 1048576 kB <---- could this be a problem? stacksize 131072 kB <---- could this be a problem? coredumpsize infinity kB memoryuse infinity kB memorylocked infinity kB maxprocesses 5547 openfiles 11095 sbsize infinity bytes vmemoryuse infinity kB Thanks in advance. _*The Query that is causing the out of memory error.*_ LOG: statement: insert into rpt.rpt_verrichting_dbc ( verrichting_id , verrichting_secid , dbcnr , vc_dbcnr ) select t1.verrichting_id , t1.verrichting_secid , t1.dbcnr , max(t1.vc_dbcnr) as vc_dbcnr from rpt.rpt_verrichting t1 , rpt.rpt_dbc_traject t00 where t1.vc_patientnr = t00.vc_patientnr and t1.vc_agb_specialisme_nr_toek = t00.agb_specialisme_nr and t1.verrichtingsdatum between t00.begindat_dbc and COALESCE(t00.einddat_dbc, t00.begindat_dbc + interval '365 days') group by t1.verrichting_id , t1.verrichting_secid , t1.dbcnr ; _*An EXPLAIN for the query:*_ Subquery Scan "*SELECT*" (cost=1837154.04..1839811.72 rows=106307 width=74) -> HashAggregate (cost=1837154.04..1838482.88 rows=106307 width=56) -> Merge Join (cost=1668759.55..1836090.97 rows=106307 width=56) Merge Cond: ((("outer".vc_patientnr)::text = "inner"."?column8?") AND ("outer".agb_specialisme_nr = "inner".vc_agb_specialisme_nr_toek)) Join Filter: (("inner".verrichtingsdatum >= "outer".begindat_dbc) AND ("inner".verrichtingsdatum <= COALESCE("outer".einddat_dbc, ("outer".begindat_dbc + '365 days'::interval)))) -> Index Scan using rpt_dbc_traject_idx1 on rpt_dbc_traject t00 (cost=0.00..84556.01 rows=578274 width=37) -> Sort (cost=1668759.55..1689806.46 rows=8418765 width=79) Sort Key: (t1.vc_patientnr)::text, t1.vc_agb_specialisme_nr_toek -> Seq Scan on rpt_verrichting t1 (cost=0.00..302720.65 rows=8418765 width=79) _*Out of memory log.*_ TopMemoryContext: 16384 total in 2 blocks; 3824 free (4 chunks); 12560 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 TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used MessageContext: 122880 total in 4 blocks; 64568 free (4 chunks); 58312 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; 896 free (0 chunks); 128 used ExecutorState: 8192 total in 1 blocks; 5304 free (1 chunks); 2888 used ExecutorState: 562316108 total in 94 blocks; 528452720 free (2593154 chunks); 33863388 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; 8176 free (0 chunks); 16 used AggContext: 399499264 total in 58 blocks; 5928 free (110 chunks); 399493336 used TupleHashTable: 109109272 total in 23 blocks; 2468576 free (70 chunks); 106640696 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 Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used CacheMemoryContext: 516096 total in 6 blocks; 83448 free (0 chunks); 432648 used rpt_dbc_traject_idx1: 1024 total in 1 blocks; 328 free (0 chunks); 696 used rpt_dbc_traject_pk: 1024 total in 1 blocks; 392 free (0 chunks); 632 used rpt_verrichting_idx2: 1024 total in 1 blocks; 392 free (0 chunks); 632 used rpt_verrichting_idx1: 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; 7312 free (0 chunks); 880 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 (4 chunks); 16 used ERROR: out of memory DETAIL: Failed on request of size 98. *Indexes* CREATE INDEX rpt_verrichting_idx1 ON rpt.rpt_verrichting USING btree (dbcnr) TABLESPACE rpt_index; CREATE INDEX rpt_verrichting_idx2 ON rpt.rpt_verrichting USING btree (vc_patientnr) TABLESPACE rpt_index; CREATE INDEX rpt_dbc_traject_idx1 ON rpt.rpt_dbc_traject USING btree (vc_patientnr, agb_specialisme_nr) TABLESPACE rpt_index_all;
pgsql-performance by date: