Re: is there a way to firmly cap postgres worker memory consumption?

I should add I had to move testing to a different VM, with less RAM and
different tunings; they are here: https://gist.github.com/skehlet/10207086

On Tue, Apr 8, 2014 at 4:08 PM, Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com> wrote:

> On Tue, Apr 8, 2014 at 12:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Once you've got a ulimit in place so that malloc eventually fails with
>> ENOMEM, the backend process should print a memory context dump on stderr
>> when it hits that. Make sure your logging setup captures the process
>> stderr someplace (logging_collector works for this, syslog does not).
>> Post the dump here when you've got it.
>>
>
> Thank you. For some reason I couldn't get it to trip with "ulimit -d
> 51200", but "ulimit -v 1572864" (1.5GiB) got me this in serverlog. I hope
> this is readable, if not it's also here:
> https://gist.github.com/skehlet/10206741
>
> TopMemoryContext: 123760 total in 16 blocks; 2720 free (7 chunks); 121040
> used
> TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
> Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks);
> 20496 used
> Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks);
> 12688 used
> TopTransactionContext: 8192 total in 1 blocks; 7368 free (0 chunks); 824
> used
> MessageContext: 4186112 total in 9 blocks; 633960 free (19 chunks);
> 3552152 used
> JoinRelHashTable: 8192 total in 1 blocks; 624 free (0 chunks); 7568
> used
> Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
> used
> smgr relation table: 24576 total in 2 blocks; 9808 free (4 chunks);
> 14768 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: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
> ExecutorState: 566208 total in 12 blocks; 195256 free (2 chunks);
> 370952 used
> TupleSort: 17817440 total in 36 blocks; 9326832 free (8801
> chunks); 8490608 used
> TupleSort: 17500568 total in 86 blocks; 12016040 free (2896
> chunks); 5484528 used
> TupleSort: 17616584 total in 86 blocks; 9975520 free (3649
> chunks); 7641064 used
> TupleSort: 10825056 total in 14 blocks; 3896400 free (45082
> chunks); 6928656 used
> TupleSort: 17459720 total in 67 blocks; 8495600 free (2226
> chunks); 8964120 used
> TupleSort: 10899360 total in 23 blocks; 2112448 free (23706
> chunks); 8786912 used
> TupleSort: 11908776 total in 15 blocks; 8344064 free (145642
> chunks); 3564712 used
> printtup: 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; 8160 free (0 chunks); 32 used
> ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
> ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 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; 8160 free (0 chunks); 32 used
> ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 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: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 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; 8160 free (0 chunks); 32 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; 8160 free (0 chunks); 32 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; 8160 free (0 chunks); 32 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; 8160 free (0 chunks); 32 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; 8160 free (0 chunks); 32 used
> ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> ExprContext: 812638208 total in 108 blocks; 183520 free (171
> chunks); 812454688 used
> AggContext: 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
> Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704
> used
> CacheMemoryContext: 1342128 total in 21 blocks; 424872 free (1 chunks);
> 917256 used
> pg_toast_16646_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008
> used
> d_mc2_fki: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used
> d_mc2_pkey: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used
> d_mcm2_pkey: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used
> d_mm2_fki: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
> d_mm2_index2: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used
> d_mm2_index1: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used
> d_mm2_pkey: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used
> d_m2_index1: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used
> d_m2_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
> pg_index_indrelid_index: 1024 total in 1 blocks; 152 free (0 chunks);
> 872 used
> pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 16 free (0
> chunks); 1008 used
> pg_db_role_setting_databaseid_rol_index: 1024 total in 1 blocks; 64
> free (0 chunks); 960 used
> pg_user_mapping_user_server_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_user_mapping_oid_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_type_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
> used
> pg_type_typname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks);
> 1008 used
> pg_ts_template_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_ts_template_tmplname_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_ts_parser_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_ts_parser_prsname_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_ts_dict_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
> used
> pg_ts_dict_dictname_index: 1024 total in 1 blocks; 64 free (0 chunks);
> 960 used
> pg_ts_config_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_ts_config_cfgname_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_ts_config_map_index: 3072 total in 2 blocks; 1784 free (2 chunks);
> 1288 used
> pg_tablespace_oid_index: 1024 total in 1 blocks; 152 free (0 chunks);
> 872 used
> pg_statistic_relid_att_inh_index: 3072 total in 2 blocks; 1736 free (2
> chunks); 1336 used
> pg_class_relname_nsp_index: 1024 total in 1 blocks; 16 free (0
> chunks); 1008 used
> pg_range_rngtypid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_proc_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
> used
> pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1736 free (2
> chunks); 1336 used
> pg_opfamily_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1784 free (2
> chunks); 1288 used
> pg_operator_oid_index: 1024 total in 1 blocks; 152 free (0 chunks);
> 872 used
> pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1736 free (2
> chunks); 1336 used
> pg_namespace_oid_index: 1024 total in 1 blocks; 152 free (0 chunks);
> 872 used
> pg_namespace_nspname_index: 1024 total in 1 blocks; 152 free (0
> chunks); 872 used
> pg_language_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_language_name_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_foreign_table_relid_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_foreign_server_oid_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_foreign_server_name_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 200 free
> (0 chunks); 824 used
> pg_event_trigger_oid_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_event_trigger_evtname_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_enum_typid_label_index: 1024 total in 1 blocks; 64 free (0 chunks);
> 960 used
> pg_enum_oid_index: 1024 total in 1 blocks; 200 free (0 chunks); 824
> used
> pg_default_acl_role_nsp_obj_index: 3072 total in 2 blocks; 1784 free
> (2 chunks); 1288 used
> pg_conversion_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_constraint_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_conversion_name_nsp_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_conversion_default_index: 3072 total in 2 blocks; 1784 free (2
> chunks); 1288 used
> pg_collation_oid_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_collation_name_enc_nsp_index: 3072 total in 2 blocks; 1784 free (2
> chunks); 1288 used
> pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1736 free (2
> chunks); 1336 used
> pg_cast_source_target_index: 1024 total in 1 blocks; 16 free (0
> chunks); 1008 used
> pg_auth_members_role_member_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_amop_fam_strat_index: 3072 total in 2 blocks; 1736 free (2 chunks);
> 1336 used
> pg_amop_opr_fam_index: 3072 total in 2 blocks; 1736 free (2 chunks);
> 1336 used
> pg_aggregate_fnoid_index: 1024 total in 1 blocks; 152 free (0 chunks);
> 872 used
> pg_auth_members_member_role_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_authid_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
> used
> pg_authid_rolname_index: 1024 total in 1 blocks; 200 free (0 chunks);
> 824 used
> pg_database_oid_index: 1024 total in 1 blocks; 152 free (0 chunks);
> 872 used
> pg_database_datname_index: 1024 total in 1 blocks; 200 free (0
> chunks); 824 used
> pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 16 free (0
> chunks); 1008 used
> pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 64 free (0
> chunks); 960 used
> pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1736 free (2
> chunks); 1336 used
> pg_opclass_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
> used
> pg_index_indexrelid_index: 1024 total in 1 blocks; 152 free (0
> chunks); 872 used
> pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 16 free (0
> chunks); 1008 used
> pg_class_oid_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
> used
> MdSmgr: 8192 total in 1 blocks; 6112 free (0 chunks); 2080 used
> ident parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> hba parser context: 3072 total in 2 blocks; 80 free (1 chunks); 2992 used
> LOCALLOCK hash: 24576 total in 2 blocks; 13920 free (4 chunks); 10656
> used
> Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
> ErrorContext: 8192 total in 1 blocks; 8160 free (3 chunks); 32 used
> 2014-04-08 23:00:14 GMT [31229]: [1-1] ERROR: out of memory
> 2014-04-08 23:00:14 GMT [31229]: [2-1] DETAIL: Failed on request of size
> 597.
> 2014-04-08 23:00:14 GMT [31229]: [3-1] STATEMENT: SELECT MESSAGE.ID,
> MESSAGE.SERVER_ID,
> MESSAGE.RECEIVED_DATE,
> MESSAGE.PROCESSED,
> MESSAGE.ORIGINAL_ID,
> MESSAGE.IMPORT_ID,
> MESSAGE.IMPORT_CHANNEL_ID,
> METADATA_IDS
> FROM
> ( SELECT M.ID,
> ARRAY_TO_STRING(ARRAY_AGG(MM.ID::CHARACTER VARYING), ',') AS
> METADATA_IDS
> FROM D_M2 M
> JOIN D_MM2 MM ON MM.MESSAGE_ID = M.ID
> LEFT JOIN D_MCM2 MCM ON MCM.MESSAGE_ID = MM.MESSAGE_ID
> AND MCM.METADATA_ID = MM.ID
> LEFT JOIN D_MC2 MC_1 ON MC_1.MESSAGE_ID = M.ID
> AND MC_1.IS_ENCRYPTED = FALSE
> AND MC_1.METADATA_ID = 0
> AND ((MM.ID = 0
> AND MC_1.CONTENT_TYPE = 1)
> OR (MM.ID > 0
> AND MC_1.CONTENT_TYPE = 4))
> LEFT JOIN D_MC2 MC_2 ON MC_2.MESSAGE_ID = M.ID
> AND MC_2.IS_ENCRYPTED = FALSE
> AND MC_2.METADATA_ID = MM.ID
> AND MC_2.CONTENT_TYPE = '2'
> LEFT JOIN D_MC2 MC_3 ON MC_3.MESSAGE_ID = M.ID
> AND MC_3.IS_ENCRYPTED = FALSE
> AND MC_3.METADATA_ID = MM.ID
> AND MC_3.CONTENT_TYPE = '3'
> LEFT JOIN D_MC2 MC_4 ON MC_4.MESSAGE_ID = M.ID
> AND MC_4.IS_ENCRYPTED = FALSE
> AND MC_4.METADATA_ID = MM.ID
> AND MC_4.CONTENT_TYPE = '4'
> LEFT JOIN D_MC2 MC_5 ON MC_5.MESSAGE_ID = M.ID
> AND MC_5.IS_ENCRYPTED = FALSE
> AND MC_5.METADATA_ID = MM.ID
> AND MC_5.CONTENT_TYPE = '5'
> LEFT JOIN D_MC2 MC_6 ON MC_6.MESSAGE_ID = M.ID
> AND MC_6.IS_ENCRYPTED = FALSE
> AND MC_6.METADATA_ID = MM.ID
> AND MC_6.CONTENT_TYPE = '6'
> LEFT JOIN D_MC2 MC_7 ON MC_7.MESSAGE_ID = M.ID
> AND MC_7.IS_ENCRYPTED = FALSE
> AND MC_7.METADATA_ID = MM.ID
> AND MC_7.CONTENT_TYPE = '7'
> LEFT JOIN D_MC2 MC_8 ON MC_8.MESSAGE_ID = M.ID
> AND MC_8.IS_ENCRYPTED = FALSE
> AND MC_8.METADATA_ID = MM.ID
> AND MC_8.CONTENT_TYPE ='8'
> WHERE M.ID <= '230716'
> AND ( M.ID::VARCHAR ILIKE '%' || 'rwar mea' || '%'
> OR MM.CONNECTOR_NAME ILIKE '%' || 'rwar mea' || '%'
> OR MC_1.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MC_2.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MC_3.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MC_4.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MC_5.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MC_6.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MC_7.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MC_8.CONTENT ILIKE '%' || 'rwar mea' || '%'
> OR MCM."SOURCE" ILIKE '%' || 'rwar mea' || '%'
> OR MCM."TYPE" ILIKE '%' || 'rwar mea' || '%' )
> GROUP BY M.ID
> ORDER BY M.ID DESC
> OFFSET 0 LIMIT 21 ) AS SUBQUERY
> INNER JOIN D_M2 MESSAGE ON SUBQUERY.ID = MESSAGE.ID
> ORDER BY MESSAGE.ID DESC
> ;
> 2014-04-08 23:00:14 GMT [31229]: [4-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.7", size 124600320
> 2014-04-08 23:00:14 GMT [31229]: [5-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.6", size 36167680
> 2014-04-08 23:00:14 GMT [31229]: [6-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.5", size 1073741824
> 2014-04-08 23:00:14 GMT [31229]: [7-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.4", size 823541760
> 2014-04-08 23:00:14 GMT [31229]: [8-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.3", size 5324800
> 2014-04-08 23:00:14 GMT [31229]: [9-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.2", size 402513920
> 2014-04-08 23:00:14 GMT [31229]: [10-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.1", size 38617088
> 2014-04-08 23:00:14 GMT [31229]: [11-1] LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp31229.0", size 5079040
>
>