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;
