This document provides some guidelines on how to purge and/or reduce the size of an APM database maintained in Postgresql and includes suggestions for optimizing CEM data retention settings.
All supported Application Performance Management releases.
How to purge or reduce the size of a Postgresql APM database
You can manually execute a "vacuum" process to clean all tables using pgAdmin.
Suggestions for Optimizing CEM Data Retention Settings
1. From CA APM Sizing and Performance Guide: APM Sizing and Performance Guide > APM Database CA CEM Data Retention Considerations
APM Database CA CEM Data Retention Considerations
You can configure the length of time that various kinds of monitored user data are retained in the APM database. These settings are ultimately a business decision you base on the requirements at your organization. However, make retention setting decisions with an understanding of the capacity consequences.
Increasing data retention increases the duration of daily statistics aggregation runs. This situation can result in the following performance issues:
Retention settings have a direct impact on APM database disk space requirements. Periodically monitor available space on the database data disk. If the percentage of available space drops below 25 percent, possible remedial actions include:
One common cause for a database bottleneck is that the resources available to the APM database, primarily memory, are inadequate for the data retention settings.
Reducing the data retention period has temporary side effects on database maintenance tasks. The side effects include longer execution times and higher memory requirements for aggregation and cleanup tasks during the 24-hour period following a reduction in retention times.
Wait from 24 to 72 hours before assessing the effect of a change on retention settings, so that all aggregation, cleanup, and maintenance tasks have run. If older data is being kept for historical purposes and not being included in ongoing analysis or reporting, consider backing up and archiving data. Coordinate these tasks with reduced retention periods.
2. Useful queries to help you troubleshoot the issue.
Run the below query in the Postgres instance to find out the size of the APM database (default name is cemdb):
SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where pg_database.datname='cemdb';
Run this query under database cemdb to find the largest tables:
SELECT relname,relfilenode,relpages,(relpages*8/(1024*1024)) as disk_space_in_GB FROM pg_class ORDER BY relpages DESC;
Validate whether the tables were auto vacuumed/analyzed or not by running this query under database cemdb:
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables;