Add an index on the cmdb_ci.comments field to improve cloud discovery performance - Known Error

Add an index on the cmdb_ci.comments field to improve cloud discovery performance Description Discovery patterns team queries the cmdb_ci.comments field extensively during cloud discovery and this has been causing a lot of slowness for very large cloud discoveries. To improve database performance and cloud discovery performance optimize cmdb_ci table by indexing the comments field.

Steps to Reproduce

1. Run AWS/Azure Cloud discovery on a large customer clone instance for 500 or more Service Accounts. 2. Navigate to Slow Query and look for Example URL that contains ASYNC: Discovery - Sensors Pattern Launch 3. The average execution time for this query is usally quite high (see attached screen shot for reference)

Workaround Workaround would be to add the index manually if there are slow queries from cloud discovery that query cmdb_ci.comments field with significantly large execution time. Steps to add the index would be:

1. Make note of the number of records in cmdb_ci table. Navigate to System Definition -> Tables -> cmdb_ci, click on the 'Database Indexes' related list and search for 'comments' under columns. If it exists, do nothing.

2. If it does not exist, click on new, select the column 'comments' from the list and click on 'Create Index' to add the index manually before the upgrade window.Most often cmdb_ci table holds a very large number of records, therefore any changes to cmdb_ci table will take a long time (usually a few hours if it has >10 Million records).

3. After creating the index, verify its existence by navigating to System Definition > Tables > cmdb_ci > Database Indexes and confirming 'comments' is listed. Monitor query execution times in the Slow Query log to ensure improvement.

Note: Creating an index on a large table like cmdb_ci (over 10 million records) may temporarily impact system performance. Schedule this operation during a maintenance window if possible.