APM Postgres database continues to grow in memory usage and never appears to release storage or stabilize its size.

Document ID : KB000031023
Last Modified Date : 14/02/2018
Show Technical Document Details

 

Symptoms

The postgres processes are using a large amount of memory.

IntroscopeEnterpriseManager.log on the EM Collector running the TIM Collection Service (TCS) shows: org.postgresql.util.PSQLException: ERROR: out of memory 

 

Cause:

The EM log error is caused by an out of memory error while updating UserSessionIdMap (TS_US_SESSIONS_MAP table): 

Statement failed 'update com.timestock.tess.data.objects.UserSessionIdMap set softDelete =…. 

The high memory usage can be caused by high number of ts_us_sessions_map partitions. 

 

Solution:

1. Get a list of ts_us_session_map partitions use the pgAdmin tool & run this query against the APM DB (cemdb) using option Query/"Export to file" to create a .csv file

SELECT child.relname 

FROM pg_inherits 

JOIN pg_class parent ON pg_inherits.inhparent = parent.oid 

JOIN pg_class child ON pg_inherits.inhrelid = child.oid 

JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace 

JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace 

WHERE parent.relname='ts_us_sessions_map'; 

 

2a. Clean up the .csv file so that it only has the partition names i.e. ts_us_sessions_map_20151231_00 etc

b. Remove the the last 60 days worth of partitions from the  bottom of the list

c. Export the .csv file to a .txt file

 

3a. Use an editor like Notepad++ to do a mass replace & insert "drop table " at the beginning of each line and ";" at the end of each line.

b. Save as a .sql file which looks like

drop table ts_us_sessions_map_20151231_00;

drop table ts_us_sessions_map_20151231_01;

...

4a. Make a APM DB (cemdb) backup using the script dbbackup-postgres.sh(bat) which can be found below EM_HOME/install/database-scripts

The parameters are documented here in the APM wiki: Administrating > Configure Enterprise Manager > Manage the APM Database

b. Stop all EMs

c. Execute the .sql file against the APM DB (cemdb) using pgAdmin

d. Restart Postgres & the EMs

e. Confirm that Postgres high memory usage problem is resolved. 

 

Additional Information:

This problem can occur in releases prior to APM 9.5.1. In that release a fix has been implemented to cleanup ts_us_session_map partitions that are older than 60 days. See Defect ID 85377 in APM Release Notes Release 9.5.1