High number of Insert / Delete operation in the discovery_cloud_temp_results table impacts system performance. - Support and Troubleshooting

High number of Insert / Delete operation in the discovery_cloud_temp_results table impacts system performance. Issue The high number of insert and delete operations on the discovery_cloud_temp_results table result in the following issues:

Read replica lag High disk growth Long-running ASYNC: Discovery

Release This issue is identified in OP5, NY9 and before versions.This is fixed in Paris, OP6 and NY P10 using PRB1409119 and PRB1393341

Cause Duplicate records were being inserted and deleted from discovery_cloud _temp_results table, which was impacting 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_result table.

3. For all versions (optional)

There is a scheduled clean up job on 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.