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 - Known Error

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:

there are a large number of records in Query Builder Results [cmdb_qb_result_base] (and the u_cmdb_qb_result tables that extends from this table) (>200,000) that reference Configuration items [cmdb_ci] directly or through CI Relationships [cmdb_rel_ci] there are large changes to the CIs and/or relationships, for example, via Discovery jobs 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

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

Workaround The results of the queries from Configuration > CMDB Query Builder are stored in tables: u_cmdb_qb_result_* (which extend from cmdb_qb_result_base. One table for each query). If you refer to the Dictionary records for the Table starts with u_cmdb_qb_result (see URL below), you may see references to cmdb_rel_ci and/or cmdb_ci: https:// /sys_dictionary_list.do?sysparm_query=nameSTARTSWITHu_cmdb_qb_result%5Einternal_type%3Dreference%5Eelement!%3Dquery%5EORelement%3DNULL&sysparm_view= When there is a large number of deletes of CIs [cmdb_ci] and/or changes to CI Relationships [cmdb_rel_ci], it will trigger updates (see SQL below) for the related records in tables: u_cmdb_qb_result_* due to the default cascade rule (the default is "--None--" for references). The update SQLs cannot be optimized further as the required indexes are already in place. The performance impact would be due to the large influx of such updates running at the same time (if there is a large number of records in the u_cmdb_qb_result_* tables). 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' The workaround has 2 steps: 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 . Name: glide.cmdb.query.update_results_on_cascade_deletion Type: true|false Value: false 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. 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".

https:// /sys_dictionary_list.do?sysparm_query=nameSTARTSWITHu_cmdb_qb_result%5Einternal_type%3Dreference%5Eelement!%3Dquery%5EORelement%3DNULL&sysparm_view= 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.

PRB1702705 will take care of fixing all the existing and new result tables to have this fix (do not update result table data when a CI or a CI relationship is deleted - by setting those relevant references to None) Related Problem: PRB1369519