Re: out of memory woes - Mailing list pgsql-general

Thank you all for the replies. Overcommit is indeed disabled - the
reason we disabled it is that this very same process caused the Linux
oom-killer to kill processes. This was perhaps two months ago. The
setting was changed to, and is currently set to: vm.overcommit_memory=2
...All has been well until recently. I will research overcommit
further.

Tom, below is the information you requested.

Thank you,
Mark

/* SCHEMA DEFINITIONS */

dev_stage=> \d sm_mbs_geo_pool_distribution
                                              Table
"secmaster.sm_mbs_geo_pool_distribution"
       Column        |         Type          |
               Modifiers

---------------------+-----------------------+--------------------------------------------------------------------------------------------
 geo_distribution_id | integer               | not null default
nextval('sm_mbs_geo_pool_distribution_geo_distribution_id_seq'::regclass)
 issue_id            | integer               | not null
 pool_prefix         | character varying(2)  | not null
 pool_number         | character varying(7)  | not null
 distribution_type   | character varying(20) |
 state               | character varying(3)  |
 as_of_date          | date                  | not null
 loan_count          | integer               |
 loan_count_ratio    | numeric(5,2)          |
 percent_of_upb      | numeric(5,2)          |
 aggregate_upb       | numeric(16,3)         | not null
 cusip               | character(9)          |
 agency              | character(3)          |
 origination_year    | character(4)          |
Indexes:
    "sm_mbs_geo_pool_distribution_pkey" PRIMARY KEY, btree
(geo_distribution_id)
    "sm_mbs_geo_pool_distribution_id_state_upb" btree (issue_id, state,
percent_of_upb) CLUSTER
    "sm_mbs_geo_pool_distribution_state_lcnt" btree (issue_id, state,
loan_count)
    "sm_mbs_geo_pool_distribution_state_pct_idx" btree (state,
percent_of_upb, loan_count)
Foreign-key constraints:
    "sm_mbs_geo_pool_distribution_issue_id_fkey" FOREIGN KEY (issue_id)
REFERENCES sm_mbs_pool_detail(issue_id) ON DELETE CASCADE

dev_stage=> \d sm_mbs_loan_distribution
                                           Table
"secmaster.sm_mbs_loan_distribution"
        Column        |     Type      |
       Modifiers

----------------------+---------------+-----------------------------------------------------------------------------------------
 rec_type             | character(1)  | not null
 agency               | character(3)  | not null
 pool_number          | character(7)  | not null
 cusip                | character(9)  | not null
 issue_id             | integer       | not null
 eff_date             | date          | not null
 value                | character(6)  | not null
 display_sort_order   | character(4)  | not null
 rpb                  | numeric(20,2) | not null
 pct_rpb              | numeric(5,2)  | not null
 loans                | integer       | not null
 loan_distribution_id | integer       | not null default
nextval('sm_mbs_loan_distribution_loan_distribution_id_seq'::regclass)
Indexes:
    "sm_mbs_loan_distribution_pkey" PRIMARY KEY, btree
(loan_distribution_id)
    "sm_mbs_loan_distribution_idx" UNIQUE, btree (issue_id, rec_type,
value)
    "sm_mbs_loan_distribution_idx1" btree (rec_type, value, pct_rpb)
    "sm_mbs_loan_distribution_rec_type_loans" btree (issue_id,
rec_type, value, loans)
    "sm_mbs_loan_distribution_rec_type_pct_rpb" btree (issue_id,
rec_type, value, pct_rpb) CLUSTER
    "sm_mbs_loan_distribution_rec_type_rpb" btree (issue_id, rec_type,
value, rpb)


/* MEMORY CONTEXT DUMP AND ERROR MESSAGE */

TopMemoryContext: 58800 total in 7 blocks; 6480 free (6 chunks); 52320
used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks);
6512 used
SPI Plan: 3072 total in 2 blocks; 1256 free (0 chunks); 1816 used
SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used
SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used
SPI Plan: 3072 total in 2 blocks; 1256 free (0 chunks); 1816 used
SPI Plan: 3072 total in 2 blocks; 424 free (0 chunks); 2648 used
SPI Plan: 3072 total in 2 blocks; 1696 free (0 chunks); 1376 used
PL/PgSQL function context: 24576 total in 2 blocks; 8464 free (7
chunks); 16112 used
PLpgSQL function cache: 22064 total in 2 blocks; 3744 free (0 chunks);
18320 used
CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
TopTransactionContext: 8192 total in 1 blocks; 5984 free (0 chunks);
2208 used
CurTransactionContext: 24576 total in 2 blocks; 12400 free (1 chunks);
12176 used
ExecutorState: 24576 total in 2 blocks; 4496 free (1 chunks); 20080
used
ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
SPI Exec: 528474160 total in 69 blocks; 309634880 free (9674592
chunks); 218839280 used
ExecutorState: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Proc: 24576 total in 2 blocks; 16728 free (16 chunks); 7848 used
MessageContext: 8192 total in 1 blocks; 5040 free (3 chunks); 3152 used

smgr relation table: 24576 total in 2 blocks; 13904 free (4 chunks);
10672 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 744 free (0 chunks); 280 used
ExecutorState: 8192 total in 1 blocks; 5256 free (6 chunks); 2936 used
ExprContext: 8192 total in 1 blocks; 8160 free (8 chunks); 32 used
Relcache by OID: 24576 total in 2 blocks; 14912 free (3 chunks); 9664
used
CacheMemoryContext: 1040384 total in 7 blocks; 457176 free (16 chunks);
583208 used
sm_mbs_geo_pool_distribution_id_state_upb: 1024 total in 1 blocks; 208
free (0 chunks); 816 used
pg_description_o_c_o_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_attrdef_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_shdepend_depender_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_depend_depender_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_depend_reference_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_index_indrelid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 216 free (0 chunks);
808 used
pg_type_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_proc_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 16 free (0
chunks); 1008 used
pg_operator_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_opclass_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_namespace_oid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 312 free (0
chunks); 712 used
pg_language_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_language_name_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_authid_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_authid_rolname_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_database_datname_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_conversion_oid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_conversion_default_index: 1024 total in 1 blocks; 16 free (0
chunks); 1008 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_class_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_cast_source_target_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 216 free (0 chunks); 808
used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
MdSmgr: 8192 total in 1 blocks; 7104 free (1 chunks); 1088 used
LockTable (locallock hash): 24576 total in 2 blocks; 15984 free (5
chunks); 8592 used
Timezones: 52560 total in 2 blocks; 3744 free (0 chunks); 48816 used
ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used
TopMemoryContext: 58800 total in 7 blocks; 9120 free (27 chunks); 49680
used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks);
6512 used
SPI Plan: 3072 total in 2 blocks; 1256 free (0 chunks); 1816 used
SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used
SPI Plan: 7168 total in 3 blocks; 3448 free (0 chunks); 3720 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1504 free (0 chunks); 1568 used
SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1568 free (0 chunks); 1504 used

SPI Plan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
SPI Plan: 3072 total in 2 blocks; 1600 free (0 chunks); 1472 used
SPI Plan: 3072 total in 2 blocks; 1256 free (0 chunks); 1816 used
SPI Plan: 3072 total in 2 blocks; 424 free (0 chunks); 2648 used
SPI Plan: 3072 total in 2 blocks; 1696 free (0 chunks); 1376 used
PL/PgSQL function context: 24576 total in 2 blocks; 8464 free (7
chunks); 16112 used
PLpgSQL function cache: 22064 total in 2 blocks; 3744 free (0 chunks);
18320 used
CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
TopTransactionContext: 8192 total in 1 blocks; 6288 free (5 chunks);
1904 used
ExecutorState: 24576 total in 2 blocks; 4496 free (1 chunks); 20080
used
ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
SPI Exec: 528474160 total in 69 blocks; 309634880 free (9674592
chunks); 218839280 used
ExecutorState: 8192 total in 1 blocks; 7664 free (0 chunks); 528 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Proc: 24576 total in 2 blocks; 15728 free (9 chunks); 8848 used
MessageContext: 8192 total in 1 blocks; 5040 free (3 chunks); 3152 used
smgr relation table: 24576 total in 2 blocks; 13904 free (4 chunks);
10672 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
PortalHeapMemory: 1024 total in 1 blocks; 744 free (0 chunks); 280 used
ExecutorState: 8192 total in 1 blocks; 5256 free (6 chunks); 2936 used
ExprContext: 8192 total in 1 blocks; 8160 free (8 chunks); 32 used
Relcache by OID: 24576 total in 2 blocks; 14912 free (3 chunks); 9664
used
CacheMemoryContext: 1040384 total in 7 blocks; 463544 free (49 chunks);
576840 used
pg_description_o_c_o_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_attrdef_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_shdepend_depender_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_depend_depender_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_depend_reference_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_index_indrelid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 216 free (0 chunks);
808 used
pg_type_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_proc_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 16 free (0
chunks); 1008 used
pg_operator_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_opclass_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 208 free (0
chunks); 816 used
pg_namespace_oid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 312 free (0
chunks); 712 used
pg_language_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_language_name_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_authid_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_authid_rolname_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_database_datname_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_conversion_oid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_conversion_default_index: 1024 total in 1 blocks; 16 free (0
chunks); 1008 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_class_oid_index: 1024 total in 1 blocks; 312 free (0 chunks); 712
used
pg_cast_source_target_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 216 free (0
chunks); 808 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 216 free (0 chunks); 808
used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 208 free (0 chunks);
816 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 312 free (0 chunks);
712 used
MdSmgr: 8192 total in 1 blocks; 7200 free (4 chunks); 992 used
LockTable (locallock hash): 24576 total in 2 blocks; 15984 free (5
chunks); 8592 used
Timezones: 52560 total in 2 blocks; 3744 free (0 chunks); 48816 used
ErrorContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
<2006-12-13 14:41:46 EST>ERROR:  out of memory
<2006-12-13 14:41:46 EST>DETAIL:  Failed on request of size 20.

<2006-12-13 14:41:46 EST>CONTEXT:  SQL statement "SELECT   $1 [ $2 ]"
  PL/pgSQL function "cluster_load_tables" line 31 at raise