242 views

Description

The Event Management - "Node Count" scheduled job sometimes has a performance problem in some queries when the client has a big CMDB (more than 3 million records).

The code that causes the issue is in the isNodeLicensable function:

 var gr = new GlideRecord("cmdb_ci"); 
var qc = gr.addQuery("name", node); 
qc.addOrCondition("ip_address", node); 
qc.addOrCondition("fqdn", node); 
gr.query(); 

The query sent to the database is difficult for the database to optimize due to the following:

  • The OR conditions in the isNodeLicensable function
  • The "name" field is physically stored on the CMDB table
  • The "ip_adddress" and "fqdn" fields are defined on the CMDB_CI table

Steps to Reproduce

 

  1. Create a large CMDB with more than 10M records.

  2. Run the job.

    Note that the job takes a very long time to complete.

 

Workaround

If the following solution does not solve the issue, disable the Event Management - "Node Count”  job.

In the isNodeLicensable function, comment the lines indicated below and then insert the indicated lines:

Comment the following lines:

        //var gr = new GlideRecord("cmdb_ci");
        //var qc = gr.addQuery("name", node);
        //qc.addOrCondition("ip_address", node);
        //qc.addOrCondition("fqdn", node);
        //gr.query();

Add the following lines:

         // Create an array to hold the sys_id values 
        var array_sys_id = [];
 
        // First UNION - Filtering on name
        var gr1 = new GlideRecord('cmdb_ci');
        gr1.addQuery("name", node);
        gr1.query();
        while (gr1.next()) {
            array_sys_id.push(gr1.sys_id + '');
        }
 
        // Second UNION - Filtering on ip_address
        var gr2 = new GlideRecord('cmdb_ci');
        gr2.addQuery("ip_address", node);
        gr2.query();
        while (gr2.next()) {
            array_sys_id.push(gr2.sys_id + '');
        }
 
        // Third UNION - Filtering on fqdn
        var gr3 = new GlideRecord('cmdb_ci');
        gr3.addQuery("fqdn", node);
        gr3.query();
        while (gr3.next()) {
            array_sys_id.push(gr3.sys_id + '');
        }
 
        var gr = new GlideRecord("cmdb_ci");
        var qc = gr.addQuery("sys_id", "IN", array_sys_id);
        gr.query();

 


Related Problem: PRB964096

Seen In

There is no data to report.

Fixed In

Istanbul Patch 11
Jakarta Patch 7

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-05-10 18:25:19
Published:2017-11-17