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
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.
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
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
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.
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