Notifications

88 views

Description

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.

For example:

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.

Workaround

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:

  1. Navigate to System Definition - Tables & Columns
  2. From the list of Table Names, select Automation Error Message [automation_error_msg]
  3. In the Index Creator section, Move 'Sys ID' from the Available to Selected slushbucket.
  4. In the Index Creator section, Move 'Key' from the Available to Selected slushbucket.
  5. Click Create Index
  6. In the Create a New Index popup, click OK


Related Problem: PRB1332033

Seen In

There is no data to report.

Intended Fix Version

New York

Safe Harbor Statement

This "Intended Fix Version" information is meant to outline ServiceNow's general product direction and should not be relied upon in making a purchasing decision. The information provided here is for information purposes only and may not be incorporated into any contract. It is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. The development, release, and timing of any features or functionality described for our products remains at ServiceNow's sole discretion.

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2019-05-21 11:43:48
Published:2019-04-25