Resolving "Out of Shared Memory" Errors for the Postgres database

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

Description:

The Postgres APM database is seeing "Out of Shared Memory Errors." What can be done about this?

 

Issue:

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.

<server01>:/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.
database_name> \q

 

 

 

Resolution

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

http://www.thegeekstuff.com/2010/08/ipcs-command-examples/

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.

5. Following the best practice of placing the APM database on its own server to eliminate memory and connection contention.6. Make sure there is enough memory on the system.
7. Increasing SWAP temporarily to provide enough virtual memory for the EM to start when the RAM request is issued by the JVM to the kernel.