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

Document ID : KB000094168
Last Modified Date : 02/05/2018
Show Technical Document Details
Issue:
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
 
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