Hi all, I have a production database that sometimes runs out of memory=at nightly vacuum. The application runs typically with around 40 post=res connections but there are times when the connections increase because =f some queries going on. The reason is that the operations are slow, the t=rminals time out and try to reconnect using new connections. Some time ago=I started to have problems with too many connections being open so I lower=d the limit to 300 connections. It was all good until recently when even w=th 180 connections I am running out of memory on vacuum... So the connecti=ns increase to 180 and the system still runs properly for other 2 days but=then at nightly vacuum runs out of memory. The fix is to restart postgre= ... If I only close the connections the problem is still these so I need =o restart postgres. If I don't restart postgres then the system will run=out of memory on queries at a point... Another important thing is that d=ring vacuum at 1am nothing else is going on that server so all the connect=ons are idle. 2013-05-14 06:53:51.449 CST,"postgres","abrazo",8=41,"[local]",519233dc.2225,3,"VACUUM",2013-05-14 06:53:48 CST,174/67143,0,=RROR,53200,"out of memory","Failed on request of size 668141820.",,,,,"vac=um;",,,"psql" OS: SUSE Linux Enterprise Server 11 (x86_64) VER=ION = 11 PATCHLEVEL = 2 Suse compiled postgre version : =A0 =A0 =A0 version ----------------------------------------------=--------------------------------------------------------------------------=-- PostgreSQL 9.0.3 on x86_64-suse-linux-gnu, compiled by GCC gcc (SU=E Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit Postgre= configuration parameters: max_connections = 300 shared_buffers =3D 2GB maintenance_work_mem = 2GB effective_cache_size = 4GB wor=_mem = 2MB wal_buffers = 1MB checkpoint_segments = 16 au=ovacuum = on wal_level = hot_standby archive_mode = on a=chive_command = 'test -f /cbnDBscripts/tmp/PITR_primarydb_stop_backup ||=rsync --timeout=30 -atz %p spsnidb1:/data01/wal_files/%f' archive_time=ut = 60 free total =A0 used free shared buffer= cached Mem: 7880512 7825136 =A0 55376 0 72376 =A0 4537644 -/+ buffers/cache: 3215116 4665396 Swap:=A0 2097148 0 2097148 =etc/sysctl.conf kernel.shmmax=2684354560 kernel.shmall=26843545=0 vm.swappiness=0 vm.overcommit_memory=2 postgres log: TopM=moryContext: 179088 total in 18 blocks; 8608 free (13 chunks); 170480 used=0A TopTransactionContext: 24576 total in 2 blocks; 21888 free (43 chunk=); 2688 used Attopt cache: 57344 total in 3 blocks; 25824 free (5 ch=nks); 31520 used Type information cache: 24576 total in 2 blocks; 11=88 free (5 chunks); 12688 used MessageContext: 8192 total in 1 block=; 4952 free (1 chunks); 3240 used Operator class cache: 8192 total i= 1 blocks; 1680 free (0 chunks); 6512 used smgr relation table: 5734= total in 3 blocks; 21984 free (10 chunks); 35360 used TransactionAb=rtContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used P=rtal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used P=rtalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used =A0 PortalHeapMemory: 2048 total in 1 blocks; 2016 free (3 chunks); 32 u=ed Vacuum: 24576 total in 2 blocks; 15392 free (0 chunks=; 9184 used Relcache by OID: 24576 total in 2 blocks; 6592 free (3 c=unks); 17984 used CacheMemoryContext: 2390704 total in 22 blocks; 64=048 free (4 chunks); 1749656 used pk_cbntransaction: 2048 tota= in 1 blocks; 752 free (0 chunks); 1296 used rel_transactionre=ailerid_fk: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 rel_transaction_promotion_fk: 2048 total in 1 blocks; 752 free (0 ch=nks); 1296 used reference_64_fk: 2048 total in 1 blocks; 752 f=ee (0 chunks); 1296 used reference_49_fk: 2048 total in 1 bloc=s; 752 free (0 chunks); 1296 used reference_46_fk: 2048 total =n 1 blocks; 752 free (0 chunks); 1296 used reference_28_fk: 20=8 total in 1 blocks; 752 free (0 chunks); 1296 used ix_transac=ion_date: 2048 total in 1 blocks; 504 free (0 chunks); 1544 used =A0 ix_referenceid_transac: 2048 total in 1 blocks; 752 free (0 chunks); 12=6 used ix_onlinetick_cbntrans: 2048 total in 1 blocks; 752 fre= (0 chunks); 1296 used ix_drawid_transaction: 2048 total in 1 =locks; 752 free (0 chunks); 1296 used ix_cbntransaction_termin=lid_transactiondate: 2048 total in 1 blocks; 656 free (0 chunks); 1392 use= ix_cbntransaction_purchaseid: 2048 total in 1 blocks; 752 fre= (0 chunks); 1296 used pk_cardactivity: 2048 total in 1 blocks= 752 free (0 chunks); 1296 used pk_business_type: 2048 total i= 1 blocks; 752 free (0 chunks); 1296 used pg_toast_207856_inde=: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used pk_ba=k: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used ix_a=dit_1: 2048 total in 1 blocks; 504 free (0 chunks); 1544 used =g_toast_207843_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 us=d pk_itshipment: 2048 total in 1 blocks; 504 free (0 chunks); =544 used pk_itpack: 2048 total in 1 blocks; 504 free (0 chunks=; 1544 used pg_toast_25392890_index: 2048 total in 1 blocks; 6=6 free (0 chunks); 1392 used pk_drawpatterns: 2048 total in 1 =locks; 504 free (0 chunks); 1544 used pg_toast_50580_index: 20=8 total in 1 blocks; 656 free (0 chunks); 1392 used rel_draw_g=meid_fk: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 reference_29_fk: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used=0A pk_draw: 2048 total in 1 blocks; 752 free (0 chunks); 1296 use= pg_toast_62768_index: 2048 total in 1 blocks; 656 free (0 chu=ks); 1392 used pg_toast_39412_index: 2048 total in 1 blocks; 6=6 free (0 chunks); 1392 used ix_a_gamewinner_2006_7: 2048 tota= in 1 blocks; 752 free (0 chunks); 1296 used ix_a_gamewinner_2=06_6: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used i=_a_gamewinner_2006_5: 2048 total in 1 blocks; 752 free (0 chunks); 1296 us=d ix_a_gamewinner_2006_4: 2048 total in 1 blocks; 752 free (0 =hunks); 1296 used ix_a_gamewinner_2006_2: 2048 total in 1 bloc=s; 752 free (0 chunks); 1296 used ix_a_gamewinner_2006_1: 2048=total in 1 blocks; 656 free (0 chunks); 1392 used pk_a_gamewin=er_2006: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 pg_toast_39404_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392=used ix_a_gamewinner_2005_7: 2048 total in 1 blocks; 752 free =0 chunks); 1296 used ix_a_gamewinner_2005_6: 2048 total in 1 b=ocks; 752 free (0 chunks); 1296 used ix_a_gamewinner_2005_5: 2=48 total in 1 blocks; 752 free (0 chunks); 1296 used ix_a_game=inner_2005_4: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 ix_a_gamewinner_2005_2: 2048 total in 1 blocks; 752 free (0 chunks);=1296 used ix_a_gamewinner_2005_1: 2048 total in 1 blocks; 656 =ree (0 chunks); 1392 used pk_a_gamewinner_2005: 2048 total in = blocks; 752 free (0 chunks); 1296 used pg_toast_39428_index: =048 total in 1 blocks; 656 free (0 chunks); 1392 used pk_a_cbn=ransaction_2008: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 ix_cashdrawerbalance_1: 2048 total in 1 blocks; 656 free (0 chunk=); 1392 used pk_cashdrawerbalance: 2048 total in 1 blocks; 752=free (0 chunks); 1296 used pg_toast_62658_index: 2048 total in=1 blocks; 656 free (0 chunks); 1392 used pk_cashdrawer: 2048 t=tal in 1 blocks; 752 free (0 chunks); 1296 used pk_account: 20=8 total in 1 blocks; 752 free (0 chunks); 1296 used ix_cashdra=ertransaction_cashdrawerid_transdate: 2048 total in 1 blocks; 656 free (0 =hunks); 1392 used pk_cashdrawertransaction: 2048 total in 1 bl=cks; 752 free (0 chunks); 1296 used pg_toast_62672_index: 2048=total in 1 blocks; 656 free (0 chunks); 1392 used ix_cashdrawe=balanceitem_1: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 pg_toast_62666_index: 2048 total in 1 blocks; 656 free (0 chunks)= 1392 used pg_toast_22502278_index: 2048 total in 1 blocks; 65= free (0 chunks); 1392 used pg_toast_26623904_index: 2048 tota= in 1 blocks; 656 free (0 chunks); 1392 used pg_toast_26623898=index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used =k_dbversion: 2048 total in 1 blocks; 504 free (0 chunks); 1544 used =A0 pg_toast_39501_index: 2048 total in 1 blocks; 656 free (0 chunks); 1=92 used pk_a_matchgameplay_2009: 2048 total in 1 blocks; 752 f=ee (0 chunks); 1296 used pg_toast_39436_index: 2048 total in 1=blocks; 656 free (0 chunks); 1392 used pk_a_matchgameplay_2008= 2048 total in 1 blocks; 752 free (0 chunks); 1296 used pg_toa=t_39396_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used =A0 pk_a_matchgameplay_2007: 2048 total in 1 blocks; 752 free (0 chun=s); 1296 used pg_toast_39388_index: 2048 total in 1 blocks; 65= free (0 chunks); 1392 used pk_a_matchgameplay_2006: 2048 tota= in 1 blocks; 752 free (0 chunks); 1296 used pg_toast_39380_in=ex: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used pk_=_matchgameplay_2005: 2048 total in 1 blocks; 752 free (0 chunks); 1296 use= pk_accountforms: 2048 total in 1 blocks; 656 free (0 chunks);=1392 used pk_validzreporttypes: 2048 total in 1 blocks; 752 fr=e (0 chunks); 1296 used pg_toast_63590_index: 2048 total in 1 =locks; 656 free (0 chunks); 1392 used ix_validtransactiontype_=serdesc: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 pk_validtransactiontype: 2048 total in 1 blocks; 752 free (0 chunks); 1=96 used pk_validtransactionstatus: 2048 total in 1 blocks; 752=free (0 chunks); 1296 used pk_address: 2048 total in 1 blocks;=752 free (0 chunks); 1296 used pg_toast_62598_index: 2048 tota= in 1 blocks; 656 free (0 chunks); 1392 used pg_toast_62590_in=ex: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used pk_=ctivitycontext: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 pg_toast_25232699_index: 2048 total in 1 blocks; 656 free (0 chun=s); 1392 used pg_toast_25232693_index: 2048 total in 1 blocks;=656 free (0 chunks); 1392 used pg_toast_26623892_index: 2048 t=tal in 1 blocks; 656 free (0 chunks); 1392 used pg_toast_26623=86_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used =A0 pg_cast_oid_index: 2048 total in 1 blocks; 752 free (0 chunks); 1296 us=d pg_toast_507675_index: 2048 total in 1 blocks; 656 free (0 c=unks); 1392 used pk_a_cbntransaction_2013: 2048 total in 1 blo=ks; 752 free (0 chunks); 1296 used pg_toast_21922437_index: 20=8 total in 1 blocks; 656 free (0 chunks); 1392 used pk_dbcompa=e_tables: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 pg_toast_507667_index: 2048 total in 1 blocks; 656 free (0 chunks); 139= used pk_a_cbntransaction_2012: 2048 total in 1 blocks; 752 fr=e (0 chunks); 1296 used pg_toast_499794_index: 2048 total in 1=blocks; 656 free (0 chunks); 1392 used pg_inherits_relid_seqno=index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used =g_inherits_parent_index: 2048 total in 1 blocks; 752 free (0 chunks); 1296=used pg_toast_11474_index: 2048 total in 1 blocks; 656 free (0=chunks); 1392 used pg_toast_11469_index: 2048 total in 1 block=; 656 free (0 chunks); 1392 used pg_toast_11464_index: 2048 to=al in 1 blocks; 656 free (0 chunks); 1392 used pg_toast_2604_i=dex: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used pg=attrdef_oid_index: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used=0A pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 fr=e (0 chunks); 1440 used pg_toast_11459_index: 2048 total in 1 =locks; 656 free (0 chunks); 1392 used pg_toast_11454_index: 20=8 total in 1 blocks; 656 free (0 chunks); 1392 used pg_largeob=ect_loid_pn_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used=0A pg_default_acl_oid_index: 2048 total in 1 blocks; 752 free (0 =hunks); 1296 used pg_toast_2396_index: 2048 total in 1 blocks;=656 free (0 chunks); 1392 used pg_shdescription_o_c_index: 204= total in 1 blocks; 656 free (0 chunks); 1392 used pg_shdepend=reference_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used =A0 pg_shdepend_depender_index: 2048 total in 1 blocks; 440 free (0 c=unks); 1608 used pg_pltemplate_name_index: 2048 total in 1 blo=ks; 752 free (0 chunks); 1296 used pg_tablespace_spcname_index= 2048 total in 1 blocks; 752 free (0 chunks); 1296 used pg_toa=t_2964_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 used =A0 pg_depend_reference_index: 2048 total in 1 blocks; 456 free (0 chunk=); 1592 used pg_depend_depender_index: 2048 total in 1 blocks;=456 free (0 chunks); 1592 used pg_toast_2609_index: 2048 total=in 1 blocks; 656 free (0 chunks); 1392 used pg_description_o_c=o_index: 2048 total in 1 blocks; 504 free (0 chunks); 1544 used =A0 pg_toast_2620_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 =sed pg_trigger_oid_index: 2048 total in 1 blocks; 752 free (0 =hunks); 1296 used pg_trigger_tgconstraint_index: 2048 total in=1 blocks; 752 free (0 chunks); 1296 used pg_toast_2618_index: =048 total in 1 blocks; 656 free (0 chunks); 1392 used pg_rewri=e_oid_index: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 pg_largeobject_metadata_oid_index: 2048 total in 1 blocks; 752 free =0 chunks); 1296 used pg_amproc_oid_index: 2048 total in 1 bloc=s; 752 free (0 chunks); 1296 used pg_amop_oid_index: 2048 tota= in 1 blocks; 752 free (0 chunks); 1296 used pg_am_oid_index: =048 total in 1 blocks; 752 free (0 chunks); 1296 used pg_am_na=e_index: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used =A0 pg_toast_1262_index: 2048 total in 1 blocks; 656 free (0 chunks); 1392 =sed pg_toast_2606_index: 2048 total in 1 blocks; 656 free (0 c=unks); 1392 used pg_constraint_contypid_index: 2048 total in 1=blocks; 752 free (0 chunks); 1296 used pg_constraint_conrelid_=ndex: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used p=_constraint_conname_nsp_index: 2048 total in 1 blocks; 656 free (0 chunks)= 1392 used pg_toast_11484_index: 2048 total in 1 blocks; 656 f=ee (0 chunks); 1392 used pg_toast_11479_index: 2048 total in 1=blocks; 656 free (0 chunks); 1392 used pg_toast_1255_index: 20=8 total in 1 blocks; 656 free (0 chunks); 1392 used pg_toast_2=19_index: 2048 total in 1 blocks; 608 free (0 chunks); 1440 used =A0 pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1=44 used pg_db_role_setting_databaseid_rol_index: 2048 total in=1 blocks; 656 free (0 chunks); 1392 used pg_opclass_am_name_ns=_index: 3072 total in 2 blocks; 1448 free (4 chunks); 1624 used =A0 pg_foreign_data_wrapper_name_index: 3072 total in 2 blocks; 1744 free (= chunks); 1328 used pg_enum_oid_index: 3072 total in 2 blocks;=1744 free (3 chunks); 1328 used pg_class_relname_nsp_index: 30=2 total in 2 blocks; 1648 free (2 chunks); 1424 used pg_foreig=_server_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used=0A pg_statistic_relid_att_inh_index: 3072 total in 2 blocks; 1448=free (4 chunks); 1624 used pg_cast_source_target_index: 3072 t=tal in 2 blocks; 1600 free (2 chunks); 1472 used pg_language_n=me_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used =A0 pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks); =688 used pg_index_indexrelid_index: 3072 total in 2 blocks; 16=6 free (2 chunks); 1376 used pg_ts_template_tmplname_index: 30=2 total in 2 blocks; 1648 free (2 chunks); 1424 used pg_ts_con=ig_map_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used =A0 pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks=; 1376 used pg_foreign_data_wrapper_oid_index: 3072 total in 2=blocks; 1744 free (3 chunks); 1328 used pg_ts_dict_oid_index: =072 total in 2 blocks; 1744 free (3 chunks); 1328 used pg_conv=rsion_default_index: 3072 total in 2 blocks; 1432 free (3 chunks); 1640 us=d pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 143= free (3 chunks); 1640 used pg_trigger_tgrelid_tgname_index: 3=72 total in 2 blocks; 1600 free (2 chunks); 1472 used pg_enum_=ypid_label_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used=0A pg_ts_config_oid_index: 3072 total in 2 blocks; 1744 free (3 c=unks); 1328 used pg_user_mapping_oid_index: 3072 total in 2 bl=cks; 1744 free (3 chunks); 1328 used pg_opfamily_am_name_nsp_i=dex: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used p=_type_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used=0A pg_aggregate_fnoid_index: 3072 total in 2 blocks; 1744 free (3=chunks); 1328 used pg_constraint_oid_index: 3072 total in 2 bl=cks; 1744 free (3 chunks); 1328 used pg_rewrite_rel_rulename_i=dex: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 used p=_ts_parser_prsname_index: 3072 total in 2 blocks; 1648 free (2 chunks); 14=4 used pg_ts_config_cfgname_index: 3072 total in 2 blocks; 164= free (2 chunks); 1424 used pg_ts_parser_oid_index: 3072 total=in 2 blocks; 1744 free (3 chunks); 1328 used pg_operator_oid_i=dex: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used p=_namespace_nspname_index: 3072 total in 2 blocks; 1744 free (3 chunks); 13=8 used pg_ts_template_oid_index: 3072 total in 2 blocks; 1744 =ree (3 chunks); 1328 used pg_amop_opr_fam_index: 3072 total in=2 blocks; 1600 free (2 chunks); 1472 used pg_default_acl_role_=sp_obj_index: 3072 total in 2 blocks; 1496 free (4 chunks); 1576 used =A0 pg_ts_dict_dictname_index: 3072 total in 2 blocks; 1648 free (2 c=unks); 1424 used pg_type_typname_nsp_index: 3072 total in 2 bl=cks; 1648 free (2 chunks); 1424 used pg_opfamily_oid_index: 30=2 total in 2 blocks; 1744 free (3 chunks); 1328 used pg_class_=id_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used =A0 pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1496 free (4 ch=nks); 1576 used pg_attribute_relid_attnum_index: 3072 total in=2 blocks; 1600 free (2 chunks); 1472 used pg_proc_oid_index: 3=72 total in 2 blocks; 1744 free (3 chunks); 1328 used pg_langu=ge_oid_index: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used =A0 pg_namespace_oid_index: 3072 total in 2 blocks; 1744 free (3 chun=s); 1328 used pg_amproc_fam_proc_index: 3072 total in 2 blocks= 1384 free (2 chunks); 1688 used pg_foreign_server_name_index:=3072 total in 2 blocks; 1744 free (3 chunks); 1328 used pg_att=ibute_relid_attnam_index: 3072 total in 2 blocks; 1648 free (2 chunks); 14=4 used pg_conversion_oid_index: 3072 total in 2 blocks; 1744 f=ee (3 chunks); 1328 used pg_user_mapping_user_server_index: 30=2 total in 2 blocks; 1648 free (2 chunks); 1424 used pg_conver=ion_name_nsp_index: 3072 total in 2 blocks; 1648 free (2 chunks); 1424 use= pg_authid_oid_index: 3072 total in 2 blocks; 1696 free (2 chu=ks); 1376 used pg_auth_members_member_role_index: 3072 total i= 2 blocks; 1648 free (2 chunks); 1424 used pg_tablespace_oid_i=dex: 3072 total in 2 blocks; 1744 free (3 chunks); 1328 used p=_database_datname_index: 3072 total in 2 blocks; 1696 free (2 chunks); 137= used pg_auth_members_role_member_index: 3072 total in 2 block=; 1648 free (2 chunks); 1424 used pg_database_oid_index: 3072 =otal in 2 blocks; 1696 free (2 chunks); 1376 used pg_authid_ro=name_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376 used =dSmgr: 24576 total in 2 blocks; 9504 free (0 chunks); 15072 used LOC=LLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used =A0 Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used =A0 ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used =0A Any suggestions are greatly appreciated Thanks, ioana -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general