em.log floods with : "SQLState 23505" and "duplicate key value violates unique constraint ts_defects_reaggr_int_intidx"

Products

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

Issue/Introduction

There is huge amount of SQL errors in the em.log:
 
WARNING: SQL Error: 0, SQLState: 23505
Feb 28, 2018 8:51:44 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: duplicate key value violates unique constraint "ts_defects_reaggr_int_intidx"
Detail: Key (ts_interval)=(...) already exists.
...
 
It is repeating in em.log and the file keeps growing 10 Gb per day.
What is the meaning and how to prevent it?
 

Cause

The APM database is overloaded by a huge amount of defects being processed.
 
Here is an example in the EM log:
[com.timestock.tess.services.scheduler.DefectAggregationTask] Aggregating 287743 defects in interval from 18-4-2017 11:00 to 18-4-2017 11:59

Because of the high number of defects being processed the defect reaggregation is also lagging.
 
"ts_defects_reaggr_intervals" table stores the defects that came in during aggregation of the interval the defect belongs to.
 
These SEVERE messages keep coming because DefectAggregationTask is a scheduler and keeps on running
 

Environment

Release: CEMUGD00200-10.5-Introscope to CA Application-Performance Management-Upgrade Mai
Component:

Resolution

1. Go to the CEM console:
-Reduce the number of defects defined, check if too many defects have been defined
-Reevaluate the defect threshold requirements, check if the defects thresholds are too low
-Disable some transactions
 
2. Remove the "console" output in all the <EM_HOME>/config/IntroscopeEnterpriseManager.properties:
From:
log4j.logger.Manager=INFO, console, logfile
To:
log4j.logger.Manager=INFO, logfile
 
Another option can be to customize the EM_HOME/bin/EMCtrl.sh to redirect the standard out to '/dev/null'
However, this will disable the em.log
 
If the problem persists, gather the below diagnostic files and contact CA Support.
 
1. postgres logs availables from POSTGRES_HOME\data\pg_log\*
 
2. output of the below queries:
select * from ts_defects_reaggr_intervals order by ts_interval
select tablename from pg_tables where tablename like 'ts_us_sessions_map_20%' order by tablename;
 
3. Enable EM DEBUG logging in the TIM Collector EM for a few minutes only (10 minutes)
Open the <EM_HOME>/config/IntroscopeEnterpriseManager.properties
Set log4j.logger.Manager=DEBUG, logfile
This is a hot property there is no need to restart the EM.
 

Additional Information

https://docops.ca.com/ca-apm/10-7/en/ca-apm-sizing-and-performance/ca-cem-capacity-planning#CACEMCapacityPlanning-MoreFactorsAffectingCACEMCapacityinaCollector