Huge number of select query operation on discovery_cloud_temp_result table hampering system's performance


Description

This will issue will result in a huge number of select query operations on the discovery_cloud_temp_result table, that can cause the following –

  1. Read replica lag
  2. High disk growth
  3. Long-running ASYNC: Discovery
  4. High CPU Usage

This issue is observed in Paris, Orlando Patch 6, Newyork patch 10, and earlier and fixed in Quebec, Paris patch 4, and Orlando patch10.


Steps to Reproduce

1. Trigger cloud discovery for cloud provider have a high number of resources using CDU.
2. Monitor the number of select queries on discovery_cloud_temp_result table.

Workaround

There is no workaround for this PRB. This is the actual fix.

1. Go to System Definition -> Script Includes

2. Open Script Include "CloudResourceDiscoveryCountHandler".

3. Please make the following changes in the script include.

             CloudResourceDiscoveryCountHandler.cache = new GlideLRUCache(10000);

                 cacheCloudResourcesDiscovered : function(outputPayload, discoStatusID) {
                 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') {
                                 var key  = element.sysId+" : "+element.className + " : " + discoStatusID;
                                 // if record is not present in the cache
                                 if(!CloudResourceDiscoveryCountHandler.cache.get(key)) {
                                       //Check if the record already exists in table.
                                       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();
                                       }
                            CloudResourceDiscoveryCountHandler.cache.put(key, true);  // add record to cache
                     }

                  }
          });
    }
},

4. Save the script include.


Related Problem: PRB1430051