search cancel

APM Database Optimization

book

Article ID: 238336

calendar_today

Updated On:

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;