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

Resolution

To remedy this, one should take a look on size of tables to decide if cleanup procedure will be needed. 

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.