The orphanProcessor job does not limit the number of records returned from the getOrphansByCondition or getOrphansByRelationRules functions causing nodes to run out of memory - Known Error

The orphanProcessor job does not limit the number of records returned from the getOrphansByCondition or getOrphansByRelationRules functions causing nodes to run out of memory Description The orphanProcessor job does not limit the number of records returned from the getOrphansByCondition or getOrphansByRelationRules functions causing nodes to run out of memory and eventually cancelling the job. This cause platform instability, slow performance, and incorrect/stale data in the CMDB Health module for correctness.

The "SNC.MetricProcessorScript.orphanProcessor(0)" shows up as an "ASYNC: Script Job" on a worker thread.

The job processes rules from the "CMDB Health Orphan Rule" [cmdb_health_orphan_rule] table.

The localhost log file may show "Large Table" warnings for the job:

2020-08-25 04:53:43 (565) worker.0 worker.0 txid=24b52e56dbb2 WARNING *** WARNING *** Large Table: Table handling an extremely large result set: 2140606 Query that got us here is: TABLENAME = cmdb_ci_lb_pool_member ENCODED_QUERY = u_active=true^u_active=true^sys_id>39bf24fcdb7254901e7ac082ba961980^sys_class_name=cmdb_ci_lb_pool_member^ORDERBYsys_id com.glide.db.QueryWarning.warn(QueryWarning.java:44) com.glide.db.DBQuery.loadResultSet(DBQuery.java:3227) com.glide.db.DBQuery.executeAndReturnTable(DBQuery.java:338) com.glide.db.DBAction.executeNormal(DBAction.java:241) com.glide.db.DBAction.executeAndReturnException(DBAction.java:195) com.glide.db.RDBMSQueryContext.executeQuery(RDBMSQueryContext.java:42) com.glide.db.DBQuery.execute(DBQuery.java:2664) com.glide.db.meta.Table.queryBasic(Table.java:331) com.glide.db.meta.Table.query(Table.java:205) com.glide.script.GlideRecordITable.query(GlideRecordITable.java:108) com.glide.script.GlideRecord.query0(GlideRecord.java:3314) com.glide.script.GlideRecord.query(GlideRecord.java:3033) com.snc.cmdb.health.OrphanProcessorUtil.getOrphansByCondition(OrphanProcessorUtil.java:134) com.snc.cmdb.health.OrphanProcessorUtil.getAllOrphan(OrphanProcessorUtil.java:694) com.snc.cmdb.health.OrphanProcessor.processRecords(OrphanProcessor.java:45) com.snc.cmdb.health.MetricProcessor.process(MetricProcessor.java:106) com.snc.cmdb.health.MetricProcessorScript.execute(MetricProcessorScript.java:35) com.snc.cmdb.health.MetricProcessorScript.jsStaticFunction_orphanProcessor(MetricProcessorScript.java:64)

And:

2020-08-25 05:03:22 (180) worker.0 worker.0 txid=24b52e56dbb2 WARNING *** WARNING *** Large Table: Table handling an extremely large result set: 2138619 Query that got us here is: TABLENAME = cmdb_ci_lb_pool_member ENCODED_QUERY = sys_class_name=cmdb_ci_lb_pool_member^u_active=true^sys_id>39bf24fcdb7254901e7ac082ba961980^ORDERBYsys_id com.glide.db.QueryWarning.warn(QueryWarning.java:44) com.glide.db.DBQuery.loadResultSet(DBQuery.java:3227) com.glide.db.DBQuery.executeAndReturnTable(DBQuery.java:338) com.glide.db.DBAction.executeNormal(DBAction.java:241) com.glide.db.DBAction.executeAndReturnException(DBAction.java:195) com.glide.db.RDBMSQueryContext.executeQuery(RDBMSQueryContext.java:42) com.glide.db.DBQuery.execute(DBQuery.java:2664) com.snc.cmdb.health.OrphanProcessorUtil.getOrphansByRelationRules(OrphanProcessorUtil.java:343) com.snc.cmdb.health.OrphanProcessorUtil.getAllOrphan(OrphanProcessorUtil.java:732) com.snc.cmdb.health.OrphanProcessor.processRecords(OrphanProcessor.java:45) com.snc.cmdb.health.MetricProcessor.process(MetricProcessor.java:106) com.snc.cmdb.health.MetricProcessorScript.execute(MetricProcessorScript.java:35) com.snc.cmdb.health.MetricProcessorScript.jsStaticFunction_orphanProcessor(MetricProcessorScript.java:64)

There is no limit being set on the number of records queried by these rules, and for some very large CMDB tables.

This can cause an excessive number of rows to be returned from the database to the application server, taking up precious memory resources, causing memory starvation for other threads and increased garbage collection activity, which can impact the response time for other transactions on the node.

Steps to Reproduce

Prerequisites:

1. Have a large CMDB table. 2. Have a "CMDB Health Orphan Rule" [cmdb_health_orphan_rule] rule that matches a lot of records in the large CMDB table. 3. Run the following in /sys.scripts.do:

SNC.MetricProcessorScript.orphanProcessor(0)

Expected behavior: The job will run without issues.

Actual behavior: The job causes memory issues and cancels.

Workaround This problem has been fixed. If you are able to upgrade, review the Fixed In or Intended Fix Version fields to determine whether any versions have a planned or permanent fix.

Workarounds:

Please check for the following:

1. Orphan by attribute conditions - returns > 500K records

If above is true, modify the current conditions to return less results, OR Add a Health Inclusion Rule If customer is trying to use the attribute conditions as a filter and it is the same as their Health Inclusion Rule, please remove it in the orphan rule. This attribute condition is not meant to be used as a filter. It is meant to match orphan CI's based on a condition. Check the documentation for more info on how orphan rule works: Orphan Rule 2. Orphan by relationship condition - returns >500K records

If above is true, please clean the orphans from cmdb_rel_ci before re-running the job, OR Add a Health Inclusion Rule, to start with a smaller subset, then do above clean up on those, then try again without the health inclusion rule Related Problem: PRB1424208