CA Tuesday Tip: APM Database: Owner's Manual - Maintenance
Introduction
This month's Tip (#40 for me) reviews the often overlooked area of APM database maintenance. Next month, we will finish up the series by looking at database optimization.
Currently the best documentation in this area is selected Knowledge Base articles. I am working on a Tech Note on this topic that should be out shortly. Once released, it will be stored in the Best Practices Documents area of the APM Community site.
Proactive Activities
There are some things that an APM database administrator can do to ensure continuous database health.
1) Review and Implement Database Vendor best practices. These can be found in the following links:
http://www.postgresql.org/docs/9.3/interactive/ This is an example of on-line Postgres database administration documentation.
http://docs.oracle.com/cd/E16655_01/index.htm this is an example of on-line Oracle database administration documentation.
2) Frequently review the growth in the overall APM database as well as particular tables. This is discussed in the following links:
http://wiki.postgresql.org/wiki/Disk_Usage
http://stackoverflow.com/questions/10006294/simplify-an-postgres-sql-query-for-listing-table-and-index-sizes
http://www.dba-oracle.com/t_script_oracle_table_size.htm
Typically the largest tables are defect-related (such as meta-data), users, and session information. Depending which table is oversized, will determine what the cleanup strategy will be.
3) Check the Postgres logs to see if the vacuuming process which soft deletes various (but not all database tables) is working. A good KB on doing manuals vacuuming is
https://comm.support.ca.com/?legacyid=TEC597519 This same KB talks about database retention settings.
4) Review and Change APM Database as needed:
The following link covers Postgres settings that should be occasionally reviewed for performance: https://comm.support.ca.com/?legacyid=TEC610832
One APM setting worth configuring is the Oracle APM tablespace size warning message -- https://comm.support.ca.com/?legacyid=TEC606480
5) Every few days, look in the EM and Database logs for errors and rectify them. For example, you may suddenly see APM database index errors. A fix for that can be found here:
https://comm.support.ca.com/?legacyid=TEC610831
Data Retention Settings
There are four types of data which have data retention configurable settings: events, session partitions, defects and stats. These include:
- User/UserGroup Statistics -- See Setup>Domain>Data Retention Settings. Please contact APM Support if you need a script to purge inactive users.
- Defects --See Setup>Incident Management Settings
- Events -- System > Events, and then click on the Event Manager table to configure events.
- Session Map -- See Administration - > Business Applications -> General (under specific application)-> Session Timeout These are partition tables which get dropped according to configuration. The default value is 60 (minutes).
Note also there are other configurable changes:
- In the <EM_HOME>tess-default.properties/tess-customer-properties, there is a setting that impacts how long concurrent session data is kept in the APM database:
# Concurrent session data is removed from the database when it is older than this value (2-24)
concurrentSessionData.purgeAfter_Months=3
There are other tables that are not configurable by design and do not include soft delete record fields at the present time. These include the following:
• Appmap
• Quartz (Report Scheduler)
• Audit Records
• Daily/Hourly Aggregation logs
• Service Start Times
• Hourly Defect Re-aggregation
• Session Usage Data
Finally, there are many other tables in this category that should never be deleted. These include most of the configuration settings found in the APM CE MOM GUI such as domain settings.
This is just a start on this topic that I will cover in more detail in the tech note. Your feedback is especially welcomed.
My thanks to Sustaining Engineering particularly Sivasankari Gnanasekaran for providing information on this topic.
Questions for Discussion:
1) What best practices have worked for you in terms of APM database maintenance?
2) If you are running APM 9.1.x and Postgres would you be willing to provide to test a new script that does additional APM database cleanup? Please contact me at hallett.german@ca.com
3) What other database topics would you like to be covered in Tuesday Tips?