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:

Previous
From: "Neil Hepworth"
Date:
Subject: Re: High CPU Usage - PostgreSQL 7.3
Next
From: "Thomas Radnetter"
Date:
Subject: Performance Problem between Ora 10g and Psql