The Postgres APM database is seeing "Out of Shared Memory Errors." What can be done about this?
Customer has a MOM and an APM Postgres Database on the same server. Shared Memory Errors appear when doing one of two things:
1. Starting the EM and the APM Database is running an update query that consumes most of the EM memory. The query is the following
update ts_us_sessions_map set ts_soft_delete=true, ts_ageout_time='<date>' where ts_soft_delete=false and ts_last_update<'<date>' and ts_app_id=1;
However, the database table involved is empty and executing the following query returns a value of zero:
select count (*) from ts_us_sessions_map;
2. Running the query directly on the APM database.
apmdes01:/opt/wily_data/data/bin> PGUSER=<user> PGPASSWORD="<password>" psql -q -d <database_name>
database_name=> update ts_us_sessions_map set ts_soft_delete=true, ts_ageout_time='<date>' where ts_soft_delete=false and ts_last_update<'<date>' and ts_app_id=1;
WARNING: out of shared memory
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
Here are some things to investigate should this issue occurs
1. Increase max_locks_per_transaction to at least 200.
2. If on a UNIX system, run the ipcs command to check the Postgres shared memory.
3. Increase the APM pool connections (c3p0) as needed
4. See if Session information is needed for the first place. Session map tables can get very large.