TEC597519: How to purge or reduce the size of a Postgresql APM database and optimize CEM data retention

Document created by CA Employee on May 25, 2016Last modified by SamCreek on May 25, 2016
Version 2Show Document
  • View in full screen mode

Author: CA


Document ID:  TEC597519

Last Modified Date:  8/20/2013

  • Products
    • CA Application Performance Management
  • Components


Description: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.Solution:How to purge or reduce the size of a Postgresql APM databaseYou can manually execute a "vacuum" process to clean all tables using pgAdmin.

Suggestions for Optimizing CEM Data Retention Settings

  • An APM database can grow rapidly if it contains a large number of invalid defects.

  • For testing purposes, try changing the defect retention settings (Incident Management Settings) in the CEM Console, from the default of 7 days to 2 days.

  • As aggregation cycles occur overnight, database size will decrease.

    image: http://search.ca.com/assets/SiteAssets/TEC597519_Embeded/597519e.gifFigure 5

  • In the CEM console, ensure that the "Capture Comprehensive Defect Details" check box is not selected on the Setup > Domain page.

  • When "Capture Comprehensive Defect Details" is checked, the CEM > Incident Management > Defect details page can display additional data about query and post parameters and request and response bodies.

  • For testing purpose, disable it to reduce the amount of data retained.

    image: http://search.ca.com/assets/SiteAssets/TEC597519_Embeded/597519f.gifFigure 6

  • Gradually reduce most Data Retention Settings in the CEM UI > Setup > Domain tab by about 20-30% per day until a satisfactory level is reached.

  • "Make Users Inactive after" is the exception. Keeping this setting at a higher value will prevent new user records from being created unnecessarily.

    image: http://search.ca.com/assets/SiteAssets/TEC597519_Embeded/597519g.gifFigure 7

From The CA APM Sizing and Performance Guide:"CA CEM Data Retention ConsiderationsYou 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:More contention for resources with the Collector that runs the daily stats aggregation service.Reduced Collector capacity.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:If the APM database logs are not on separate disks, move the logs to separate disks.If you plan to have the logs continue to share the data disk, delete old log files.Reduce retention periodsUpgrade to a larger capacity disk subsystem.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."

  • A final tip is to check the 'badfiles' directory in the APM installation and delete the files found there.

  • By default, the data files sent to the Enterprise Manager from the TIMs are stored in this directory: cem/tess/badfiles




Search the Entire CA APM Knowledge Base