How to optimize the APM database that is growing larger?

Products

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

Issue/Introduction

Our APM DB size is growing quickly and is affecting the performance of the environment so we are looking for a way to optimize the APM database.

How can we optimize the database when the database size is growing very quickly?

Environment

10.7

Resolution

There were some issues in the GA version of APM that was causing a large amount of topological changes to be stored.  As a result, the database size was growing very quickly and was making complexity of query evaluation hard.

To remedy this, one should take a look on size of tables to decide if cleanup procedure will be needed. These problems are fixed in hotfixes, so please install latest available.

Cleanup decision:

1. Execute SQL
   
   1. Postgres:
      

      select external_id, history from (
      select vertex_id, count(1) as history from appmap_vertices
      where start_time > (CURRENT_DATE - INTERVAL '5 day')
      group by vertex_id
      order by 2 desc
      limit 20) t
      join appmap_id_mappings m on t.vertex_id=m.vertex_id;

   2. 
      

      Oracle:

      select external_id, history from (
      select vertex_id, count(1) as history from appmap_vertices
      where start_time > (sysdate - 5)
      group by vertex_id
      order by 2 desc) t
      join appmap_id_mappings m on t.vertex_id=m.vertex_id
      where rownum < 20;

2. If this command gives many history values greater than 100 results, then you are experiencing many changes in topology
3. Another way is to list number of records in appmap_vertices (1M rows is warning) or appmap_attribs (10M rows is warning):
   
   • select count(*) from appmap_vertices;
   • select count(*) from appmap_attribs;
   • select count(*) from appmap_edges;
4. Another way is to get size of appmap_attribs table. If the size is greater than 4GB then it should be cleaned as well. To get the table size in MB:
   
   • select relname, (relpages * 8) / 1024 as size_mb from pg_class where relname = 'appmap_attribs'; (Postgres)
   • select bytes/1024/1024 MB from user_segments where segment_name='APPMAP_ATTRIBS'; (Oracle)
5. Apply the most recent Hotfix as soon as possible
6. Follow cleanup procedure: 
   
   1. Download attached cleanup utility "sqlTools7.jar"
   2. Put utility in EM_HOME folder
   3. Execute cleanup that will delete topological history before some date (14 days before current time). For example "2017-09-15 00:00:00":
      jre/bin/java -jar sqlTools7.jar cleanup "2017-09-15 00:00:00"
   
   
   
   1. Optimize tables in Postgres SQL:
      vacuum full analyze appmap_edges;
      vacuum full analyze appmap_vertices;
      vacuum full analyze appmap_attribs;
   2. 
      

      Optimize tables in Oracle:
      alter table appmap_edges enable row movement;
      alter table appmap_vertices enable row movement;
      alter table appmap_attribs enable row movement;

      alter table appmap_edges shrink space compact;
      alter table appmap_vertices shrink space compact;
      alter table appmap_attribs shrink space compact;

There is no need to stop the Enterprise Manager, it will just slow down a little bit the database.

Additional Information

- How can we tune Postgres database for Windows box with 16GB Memory?
https://knowledge.broadcom.com/external/article?articleId=16515 

- How can we tune Oracle database with 64GB Memory?
https://knowledge.broadcom.com/external/article?articleId=16409

- How can we tune Postgres database for Linux box with 16GB Memory?
https://knowledge.broadcom.com/external/article?articleId=16381

- How to purge or reduce the size of a Postgresql APM database and optimize CEM data retention.
https://knowledge.broadcom.com/external/article?articleId=9486

- APM database is growing fast - too many appmap_states tables. Is there a property to prune those tables.
https://knowledge.broadcom.com/external/article?articleId=4232

- Oracle 11gR2 APM database is growing very large
https://knowledge.broadcom.com/external/article?articleId=20013