General Guidance to cleaning up APM Database for appmap tables

Products

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

Issue/Introduction

Backend Oracle db is 90% full, need clean up steps or any parameter change so we could reduce the retention & clean up old data

Cause

Many alert changes and monitored applications.

Environment

Release : 10.7.0 and later

APM

Resolution

These are Engineering recommendations:

appmap_attribs, appmap_edges, appmap_vertices should be taken care of by the sqlTools6/7.jar. Also set proper retention days to avoid future growing back. (introscope.apm.data.preserving.time). This requires an EM restart. Engineering recommends no more than 2 months.

 

appmap_states tables are really just for the alert states of nodes in Team Center. Usually historical alert states are not that important. So it shouldn't be a big impact to reduce retention days further. (introscope.apm.alert.preserving.time)  This requires a restart. 30 days of data or less should be sufficient.

 

at_evidences, at_stories, use same alert retention setting, also make sure introscope.triage.data.purging is set to true. But we have a known issue of pruning AT data, which is fixed in 10.7HF#49 (DE429024).

 

apm_geolocation is used by CEM only. It does not have a retention setting.

Optimize tables in Postgres SQL:
vacuum full analyze appmap_edges;
vacuum full analyze appmap_vertices;
vacuum full analyze appmap_attribs;

vacuum full analyze apm_geolocation;

 

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 apm_geolocation enable row movement;

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