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?
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"
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
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)
LANGUAGE plpgsql AS $$
FOR row IN
SELECT table_schema, table_name
WHERE table_type = 'BASE TABLE'
AND table_schema = 'public'
AND table_name LIKE 'appmap_states_%'
AND table_name < ('appmap_states_' || _date)
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);
--- 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.
for rec in (select table_name
where table_name like 'appmap_states_%'
and table_name < 'appmap_states_20160801'
execute immediate 'drop table '||rec.table_name;
3. Start the Introscope Enterprise Managers (Collectors and MOM)
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: