Slow queries on automation_error_msg prolong Discovery Sensor processing. This single query can be many times longer than the time the rest of the sensor takes to run, prolonging Discovery Schedules, and impacting Scheduler Worker thread availability for the instance as a whole.
In London a change was made in relation to memory usage PRB1292562 that now has the ErrorManager querying automation_error_msg, based on the sys_id + key fields. These fields are not indexed, resulting in that query being quite slow.
To solve this a compound index should be create on the sys_id + key fields
Steps to Reproduce
Run Discovery, and monitor the app node logs for Database query times for automation_error_msg while Sensors run.
2019-03-24 04:40:46 (908) worker.7 worker.7 txid=c248e48bdb10 Name: ASYNC: Discovery - Sensors
2019-03-24 04:40:46 (930) worker.7 worker.7 txid=c248e48bdb10 Processing sensor: HTTP - Classify (10.195.20.5)
2019-03-24 04:40:59 (928) worker.7 worker.7 txid=c248e48bdb10 Time: 0:00:12.164 id: xxxxx [glide.7] for: UPDATE automation_error_msg SET `status` = '3' WHERE automation_error_msg.`sys_id` NOT IN ('a4e16c66db083b00e6fdad17489619ce' , 'cbe368eadbc47700f728227b4b961929' , 'd8f320a6db8cf70044f7bb1a6896190b') AND automation_error_msg.`key` = '10.195.20.5' /* xxxxxx, gs:glide.scheduler.worker.7, tx:c248e48bdb10ff084c8370ba6896194e */
2019-03-24 04:40:59 (941) worker.7 worker.7 txid=c248e48bdb10 Processed sensors in 0:00:13.018 (HTTP - Classify)
2019-03-24 04:40:59 (954) worker.7 worker.7 txid=c248e48bdb10 Completed: ASYNC: Discovery - Sensors in 0:00:13.042, next occurrence is null
In that case 93% of the Sensor processing time was this single query.
A fix is planned, and we recommend subscribing to the article for future information or upgrading to listed releases that offer the fix.
Customers can add an index themselves to improve the speed of these queries. Log into the instance as an 'admin' role user and perform these steps:
- Navigate to System Definition - Tables & Columns
- From the list of Table Names, select Automation Error Message [automation_error_msg]
- In the Index Creator section, Move 'Sys ID' from the Available to Selected slushbucket.
- In the Index Creator section, Move 'Key' from the Available to Selected slushbucket.
- Click Create Index
- In the Create a New Index popup, click OK
Related Problem: PRB1332033