Re: out of memory woes

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