Re: Out of memory on SELECT in 8.3.5 - Mailing list pgsql-general

> Which is a longwinded way of saying "that doesn't seem to match the
> symptoms, but ..."  If you're not dead certain that your kernel is
> configured to allow *well* north of 7000 open files, you might consider
> cutting max_files_per_process in half at your next restart.

I think it is, looking at /proc/sys/fs/file-max I get

[root@170226-db7 64]# cat /proc/sys/fs/file-max
1573080

> OTOH ... wait a minute.  Have you maybe got the system configured to
> start denying memory requests before it gets into significant swapping?
> We typically suggest setting vm.overcommit_memory=2 on Linux, but
> I'm not sure whether that results in the kernel trying to avoid using
> swap at all.  Maybe this really is an issue with system-wide total
> memory use rather than any one process.

We do have vm.overcommit_memory set to 2 on this system.

I should add that now many queries and just transaction initiations are
failing on this system since yesterday. Now I just saw one of our
applications attempt to initiate a transaction (this is a Java app, so
through JDBC here) and it resulted in an out-of-memory error. This is all
I see in the logs:

[2009-02-09 13:52:13 CST lms_kia FATAL:  out of memory
TopMemoryContext: 53952 total in 6 blocks; 7048 free (7 chunks); 46904 used
  Relcache by OID: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  CacheMemoryContext: 91024 total in 4 blocks; 3856 free (9 chunks); 87168
used
  MdSmgr: 0 total in 0 blocks; 0 free (0 chunks); 0 used
  LOCALLOCK hash: 8192 total in 1 blocks; 3744 free (0 chunks); 4448 used
  Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
  ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used
2009-02-09 13:52:29 CST lms_kia FATAL:  out of memory
2009-02-09 13:52:29 CST lms_kia DETAIL:  Failed on request of size 2456.
TopMemoryContext: 97184 total in 11 blocks; 2216 free (7 chunks); 94968 used
  TopTransactionContext: 8192 total in 1 blocks; 7648 free (0 chunks); 544
used
  Prepared Queries: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
  Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
used
  Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
  MessageContext: 8192 total in 1 blocks; 6976 free (0 chunks); 1216 used
  smgr relation table: 24576 total in 2 blocks; 11840 free (4 chunks);
12736 used
  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
32 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: 122880 total in 17 blocks; 7640 free (102 chunks);
115240 used
      ExecutorState: 89312 total in 4 blocks; 1960 free (2 chunks); 87352
used
        HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext: 125820976 total in 26 blocks; 496 free (6
chunks); 125820480 used
        HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext: 524336 total in 1 blocks; 0 free (0 chunks);
524336 used
        TIDBitmap: 516096 total in 6 blocks; 222528 free (20 chunks);
293568 used
        TupleSort: 32816 total in 2 blocks; 7360 free (0 chunks); 25456 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: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 8192 total in 1 blocks; 8096 free (0 chunks); 96 used
        ExprContext: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        AggContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
          TupleHashTable: 8192 total in 1 blocks; 3744 free (0 chunks);
4448 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: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used
  CacheMemoryContext: 1341680 total in 21 blocks; 502840 free (2 chunks);
838840 used
    unnamed prepared statement: 57344 total in 3 blocks; 2008 free (2
chunks); 55336 used
    cl_event_crm_idx: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    cl_event_lead_id_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    cl_event_event_name_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    cl_event_crm_lead_id_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    cl_event_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    cl_event_first_response_pkey: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
    lead_reporting_data_delivery_meth_idx: 2048 total in 1 blocks; 752
free (0 chunks); 1296 used
    lead_reporting_data_typeflags_idx: 2048 total in 1 blocks; 600 free (0
chunks); 1448 used
    lead_reporting_data_unique_dealer_idx: 2048 total in 1 blocks; 752
free (0 chunks); 1296 used
    lead_reporting_data_unique_area_idx: 2048 total in 1 blocks; 752 free
(0 chunks); 1296 used
    lead_reporting_data_unique_region_idx: 2048 total in 1 blocks; 752
free (0 chunks); 1296 used
    lead_reporting_data_unique_all_idx: 2048 total in 1 blocks; 752 free
(0 chunks); 1296 used
    lead_reporting_data_oem_subtype_idx: 2048 total in 1 blocks; 752 free
(0 chunks); 1296 used
    lead_reporting_data_oem_type_idx: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
    lead_reporting_data_dealer_area_idx: 2048 total in 1 blocks; 752 free
(0 chunks); 1296 used
    lead_reporting_data_customer_lname_idx: 2048 total in 1 blocks; 752
free (0 chunks); 1296 used
    lead_reporting_data_customer_fname_idx: 2048 total in 1 blocks; 752
free (0 chunks); 1296 used
    lead_reporting_data_customer_postal_code_idx: 2048 total in 1 blocks;
752 free (0 chunks); 1296 used
    lead_reporting_data_dealer_region_area_idx: 2048 total in 1 blocks;
752 free (0 chunks); 1296 used
    lead_reporting_data_dealer_region_idx: 2048 total in 1 blocks; 752
free (0 chunks); 1296 used
    lead_reporting_data_dealer_code_idx: 2048 total in 1 blocks; 752 free
(0 chunks); 1296 used
    lead_reporting_data_reporting_date_idx: 2048 total in 1 blocks; 704
free (0 chunks); 1344 used
    lead_reporting_data_model_idx: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
    lead_reporting_data_pkey: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    config_xml_name_key: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
    config_xml_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    CachedPlan: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
    CachedPlanSource: 1024 total in 1 blocks; 664 free (1 chunks); 360 used
    ehcache_expiration_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    ehcache_pkey: 2048 total in 1 blocks; 608 free (0 chunks); 1440 used
    CachedPlan: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
    CachedPlanSource: 1024 total in 1 blocks; 664 free (1 chunks); 360 used
    region_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    dealer_external_system_id_idx: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
    dealer_dealer_code_key: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    dealer_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    xslt_style_sheet_pkey: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    pg_ts_dict_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
    pg_language_name_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_statistic_relid_att_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
    pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_namespace_nspname_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
    pg_opfamily_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
    pg_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks);
1688 used
    pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1576 free (3
chunks); 1496 used
    pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
    pg_cast_source_target_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
    pg_auth_members_role_member_index: 3072 total in 2 blocks; 1648 free
(2 chunks); 1424 used
    pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
    pg_ts_config_cfgname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
    pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_conversion_default_index: 3072 total in 2 blocks; 1432 free (3
chunks); 1640 used
    pg_language_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_enum_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328
used
    pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1576 free (3
chunks); 1496 used
    pg_ts_parser_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_database_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
    pg_conversion_name_nsp_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_class_relname_nsp_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
    pg_attribute_relid_attnam_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
    pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2
chunks); 1688 used
    pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1384 free (2
chunks); 1688 used
    pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
    pg_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
    pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_authid_rolname_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
    pg_auth_members_member_role_index: 3072 total in 2 blocks; 1648 free
(2 chunks); 1424 used
    pg_enum_typid_label_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_constraint_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_conversion_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks);
1328 used
    pg_ts_template_tmplname_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
    pg_ts_config_map_index: 3072 total in 2 blocks; 1624 free (3 chunks);
1448 used
    pg_namespace_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
    pg_type_typname_nsp_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
    pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
    pg_amop_opr_fam_index: 3072 total in 2 blocks; 1600 free (2 chunks);
1472 used
    pg_proc_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
    pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1624 free (3
chunks); 1448 used
    pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 free (3
chunks); 1328 used
  MdSmgr: 8192 total in 1 blocks; 6752 free (0 chunks); 1440 used
  LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
  Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
  ErrorContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used

Does this provide any useful information? I have other queries that are
failing as well, and I can provide explain output for those if it might
help.

Regards,
Matt