APM Database Optimization

Products

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

Issue/Introduction

GA version of APM that was causing big 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.

One should take a look on size of tables to decide if cleanup procedure will be needed.

 

Environment

Release : 10.7.0

Component : Introscope

Resolution

Resolution: These problems are fixed in hotfixes, so please install latest available

Workaround: Utilize APM Database Optimization by cleanup 

Manual steps for cleanup database: 

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 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)
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.
5. Apply Hotfix as soon as possible
6. Follow cleanup procedure:
   1. Obtain cleanup utility sqlTools7.jar from Broadcom Support.  
   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 sqlTools6.jar cleanup "2017-09-15 00:00:00"
   4. Optimize tables in Postgres SQL:
      vacuum full analyze appmap_edges;
      vacuum full analyze appmap_vertices;
      vacuum full analyze appmap_attribs;

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