Large number of Query Builder results with references to CI Relationships may cause performance issues when there are a lot of changes to the configuration items


Description

This KB will apply if the following applies:

The CIs referenced in the results of the CMDB Query Builder Results are updated to reflect the changes of the CIs so that the results for existing queries remain correct (not stale).

Due to the large changes of the CIs, a large number of updates may be triggered for the table: cmdb_qb_result_base which may then cause "High CPU Load" on the database instance and/or "Replication Lag" alerts. When the updates stop (for example when the Discovery jobs completed), the performance issues will also subside. The issue may reoccur when the next discovery schedule runs.

As the records in Query Builder Results [cmdb_qb_result_base] are also cleaned up by the Daily job: "CMDB Query Builder query results clean up" (System Definition > Scheduled Jobs), the performance impact to the instance may not be occurring frequently or at each execution of a Discovery Schedule.

Steps to Reproduce

  1. Create a large number of Query Builder Results [cmdb_qb_result_base] using CMDB Query Builder that references a large number of configuration items
  2. Delete the Configuration items that are referenced in the query results to trigger cascade deletes

 

Workaround

https://<instance.service-now.com>/sys_dictionary_list.do?sysparm_query=nameSTARTSWITHu_cmdb_qb_result%5Ereference%3Dcmdb_rel_ci%5EORreference%3Dcmdb_ci%5Ereference_cascade_rule%3D

UPDATE cmdb_qb_result_base SET `a_ref_10`= NULL WHERE cmdb_qb_result_base.`sys_class_path` LIKE '/!8%' AND cmdb_qb_result_base.`a_ref_10` = '177fa40f1b7cdc50bb4f75d6cc4bcbb2'
    1. Create the following system property as below (only available in releases and onwards in the "Fixed In" section). Please be mindful of the cache flush that will be triggered. For more information, please refer to the documentation: Add a System Property.

The  "Reference Cascade Rule" for new Dictionary records (Type: references) of u_cmdb_qb_result_* tables will then be set to "None" (Note: "None" is different from "--None--". Please see screenshot below). Please also note that the Dictionary records for the existing u_cmdb_qb_result_* tables will not be modified.

2. If immediate relief is required or if the affected instance's release is prior to that in the "Fixed In" section, for the existing u_cmdb_qb_result_* tables, open each of the records from the URL below and change the Reference Cascade Rule from "--None--" to "None". However, if the instance is not currently impacted and there are no discovery jobs scheduled in the next 2 days, you can also choose to take no action as the data in the existing u_cmdb_qb_result_* tables will be cleaned up by the daily job: "CMDB Query Builder query results clean up".

https://<instance.service-now.com>/sys_dictionary_list.do?sysparm_query=nameSTARTSWITHu_cmdb_qb_result%5Ereference%3Dcmdb_rel_ci%5EORreference%3Dcmdb_ci%5Ereference_cascade_rule%3D

Please note that the u_cmdb_qb_result_* tables that are related to the CMDB Query builder queries that are not saved will be removed after 1 day (approx). However, if there are the queries are saved, although the data are cleared, the table(s) will not be removed. As such, if you are still impacted (probably because the saved queries are run again and/or new tables are created from new queries), you may still want to perform the change to the Reference Cascade Rule to mitigate the impact.


Related Problem: PRB1369519