APM database is growing fast - too many appmap_states tables. Is there a property to prune those tables.

Products

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

Issue/Introduction

The apm database is growing fast, approximately 1 GB per day. Is there any property or mechanism to reduce the number and size of the appmap_states tables?

Environment

APM 10.x

Cause

In releases 10.0-10.3 'states' tables are preserved by default for 365 days like other appmap data. This data retention is controlled by the EM property "introscope.apm.data.preserving.time".

In release 10.5+ 'states' tables are now preserved by default for 62 days. This data retention is controlled by the new independent EM property "introscope.apm.alert.preserving.time"

 

Resolution

Option A:

For releases 10.0-10.3 lower the value of introscope.apm.data.preserving.time to 30 or 60 days for example.

For releases 10.5+ lower the value of introscope.apm.alert.preserving.time to 30 or example.

The above properties are NOT hot configurable and will require an Enterprise Manager restart

 

Option B:

Delete all appmap_states tables older than a given date as below:

1. Stop all the Introscope Enterprise Managers (MOM  and collectors)

2. Run one of the below scripts

a) If you are using postgres, the below script delete all appmap_states tables older than given date in postgres, in this example older than 2016/07/01

--- defines a function which drops all APPMAP_STATES_* table
--- that are older than the given date in format 'YYYYMMDD'
CREATE OR REPLACE FUNCTION appmap_dropstates(IN _date TEXT)
  RETURNS void 
  LANGUAGE plpgsql AS $$
  DECLARE
    row     record;
  BEGIN
    FOR row IN 
        SELECT table_schema, table_name
          FROM information_schema.tables
          WHERE table_type = 'BASE TABLE'
            AND table_schema = 'public'
            AND table_name LIKE 'appmap_states_%'
            AND table_name < ('appmap_states_' || _date)
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
  END;
  $$;
 
--- this way the function can be execute, uncomment it first:
-- SELECT appmap_dropstates('20160701');


b) If you are using Oracle, the highlighted table name is the oldest table that will remain in database, all older will be dropped.

begin
  for rec in (select table_name 
              from   all_tables 
              where  table_name like 'appmap_states_%'
                and table_name < 'appmap_states_20160801'
             )
  loop
    execute immediate 'drop table '||rec.table_name;
  end loop;             
end;
 

3. Start the Introscope Enterprise Managers (Collectors and MOM)

Additional Information

1. The new property introscope.apm.alert.preserving.time is hidden in APM 10.5 but will be fully documented in the next release APM 10.7.

2. Oracle manages its indices itself, however, Postgres needs full vacuuming time to time. 

If you are using postgres, make sure auto vacuuming is turn on, Postgresql strongly recommends to use it and default option is ‘on’. 
More documentation can be found here: https://www.postgresql.org/docs/9.1/static/runtime-config-autovacuum.html

3. See also:

https://comm.support.ca.com/kb/how-to-purge-or-reduce-size-of-apm-database-in-postgres/KB000048617

https://comm.support.ca.com/kb/unable-to-purge-appmapstates-tables/KB000044847