Given the sheer number of insertion and deletion operations in the discovery_cloud_temp_results table, the following issues have been identified-
- Read replica lag
- High disk growth
- Long running ASYNC: Discovery
Release/Environment
This issue is identified in OP5, NY9 and before versions.This is fixed in Paris, OP6 and NY P10 using PRB1409119 and PRB1393341
Cause
Multiple duplicate records, which were of no use, were inserted into the discovery_cloud _temp_results table. Then deletions of the enormous number of duplicate records also hindered system performance.
Resolution
1. For Instance version NY P8 or OP2 and before
Go to Business rule "Update Cloud Resource Count" (sys id - 5d260a8cdbd8bb00d2c8f1471d961996) and replace
line 47. cloudResourceDiscoveryCountHandler.cleanUpCachedDiscoveryResults(statusSysID);
with following
if (previous.state != current.state) {
gs.info("Cleaning up the table discovery_cloud_temp_results for status sys_id " + statusSysID);
cloudResourceDiscoveryCountHandler.cleanUpCachedDiscoveryResults(statusSysID);
}
This should reduce delete operations.
2. For Instance version NY P9 or OP5 and before
Go to Script include "CloudResourceDiscoveryCountHandler" (sys id - ac4e0d8867b3130022646c706785efc2) and replace line 63 to 80
var gr;
var rawPayload = global.JSON.parse(outputPayload);
var finalCIsList = this.fetchAllChildCIsForParentCI('cmdb_ci');
if (JSUtil.notNil(rawPayload) && rawPayload.hasOwnProperty('items')) {
rawPayload.items.forEach(function(element) {
if(finalCIsList.indexOf(element.className) != -1
&& element.sysId
&& element.sysId != 'Unknown') {
gr = new GlideRecord('discovery_cloud_temp_results');
gr.initialize();
gr.setValue('ci_name', element.className);
gr.setValue('status', discoStatusID);
gr.setValue('ci_sys_id', element.sysId);
gr.insert();
}
});
}
with following
var selectGR,insertGR;
var rawPayload = global.JSON.parse(outputPayload);
var finalCIsList = this.fetchAllChildCIsForParentCI('cmdb_ci');
if (JSUtil.notNil(rawPayload) && rawPayload.hasOwnProperty('items')) {
rawPayload.items.forEach(function(element) {
if(finalCIsList.indexOf(element.className) != -1 && element.sysId && element.sysId != 'Unknown') {
//Check if the record already exists.
selectGR = new GlideRecord('discovery_cloud_temp_results');
selectGR.addQuery('ci_sys_id', element.sysId);
selectGR.addQuery('ci_name', element.className);
selectGR.addQuery('status', discoStatusID);
selectGR.query();
if (!selectGR.next()) {
insertGR = new GlideRecord('discovery_cloud_temp_results');
insertGR.initialize();
insertGR.setValue('ci_name', element.className);
insertGR.setValue('status', discoStatusID);
insertGR.setValue('ci_sys_id', element.sysId);
insertGR.insert();
}
}
});
}
This change will make sure that no duplicate records are inserted into discovery_cloud_temp_results table.
3. For instance version Paris and before
Adding step 2 changes can result in a huge number of select queries. To improve System's performance please add ci_sys_id,ci_name, and status as an index for discovery_cloud_temp_result table.
For this go to System Definition -> Tables. Select table as name "discovery_cloud_temp_results".
Go to the Database Indexes section, select new and select CI Sys Id, CI Name, and Discovery Status in the slush bucket(in the same order), then click on Create Index.
After creating the index, here is how it would look.
4. For all version
There is a scheduled clean up job on the discovery_cloud_temp_result table which gets triggered every 5 days. If there are a high number of records for the discovery status which are completed, you can decrease the schedule clean up job interval from 5 to 3 days.
For this type sys_auto_flush_list.do in left Navigation bar and then enter "discovery_cloud_temp_results" in the table name column.Update age in seconds from 432000 to 259200.
