Bug 610495

Summary: Perf: Async deletion of resource slow due to call time data deletion
Product: [Other] RHQ Project Reporter: Heiko W. Rupp <hrupp>
Component: MonitoringAssignee: RHQ Project Maintainer <rhq-maint>
Status: NEW --- QA Contact: Mike Foley <mfoley>
Severity: high Docs Contact:
Priority: low    
Version: 4.0.0CC: jshaughn
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: All   
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---
Bug Depends On:    
Bug Blocks: 620933    

Description Heiko W. Rupp 2010-07-02 04:54:35 EDT
@NamedQueries( { @NamedQuery(name = CallTimeDataKey.QUERY_DELETE_BY_RESOURCES, query = "DELETE CallTimeDataKey ctdk WHERE ctdk.schedule IN ( SELECT ms FROM MeasurementSchedule ms WHERE ms.resource.id IN ( :resourceIds ) )") })

forces the database into full table scans per resource id deleted (actually, only one resource id is passed in, but this does not matter here).

Putting an index on rhq_calltime_data_value.key_id speeds up the deletion dramatically (from multiple minutes in my case to several seconds)

CREATE INDEX ct_key_index
  ON rhq_calltime_data_value
  USING btree
Comment 1 Jay Shaughnessy 2014-05-29 17:12:07 EDT
This index was never put in place. Still valid, I guess.  Maybe not relevant as I thin calltime is moving to cassandra.