Notifications

15 views

Description

When using CMDB search, some search terms are extremely slow to provide results, and will sit at 'In Progress 0%' for up to a minute or more before starting to return any results. Example: 'sas install' or 'office install'. Individually searching for 'sas' or 'install' or 'office' run at normal speed. Also searching "install sas" run at normal speed.

Cause

"CMDB search" plugin is activated. 

In the current scenario when "sas install" keyword is used for search, it converts the keyword to *sas install*. Thus triggering database query for all the relevant keywords that starts with sas (*sas) and all the relevant keywords that end with install (install *). This returns huge number of results for *sas causing the instance to process results far more than the one's returned from other keywords(*install sas*).

Stack trace noticed is as below:

main,Default-thread-14,5,attrs=(session_id=7BA5E1561BCCC054657D6577BC4BCB16)
java.lang.Object.wait(Native Method)
java.lang.Thread.join(Thread.java:1252)
java.lang.Thread.join(Thread.java:1326)
com.glide.ts.query.TSQuery.awaitWorkers(TSQuery.java:237)
com.glide.ts.query.TSQuery.runQuery(TSQuery.java:154)
com.glide.ts.query.TSQuery.execute0(TSQuery.java:112)
com.glide.ts.query.TSQuery.execute(TSQuery.java:79)
com.glide.ts.service.TSQueryContext.executeQuery(TSQueryContext.java:45)
com.glide.db.DBQuery.execute(DBQuery.java:2744)
com.snc.cmdb.query.processor.QueryProcessUtil.getCiSysIds(QueryProcessUtil.java:65)
com.snc.cmdb.query.persistence.ClassNode.getStartNodeSysIds(ClassNode.java:55)
com.snc.cmdb.query.processor.QueryBuilderProcessor.processOneBatchQuery(QueryBuilderProcessor.java:212)
com.snc.cmdb.query.processor.QueryBuilderProcessor.processQueryGraphInternal(QueryBuilderProcessor.java:179)
com.snc.cmdb.query.processor.QueryBuilderProcessor.processQueryGraph(QueryBuilderProcessor.java:132)
com.snc.cmdb.query.api.CmdbQuery.buildAndExecuteQueryGraph(CmdbQuery.java:297)
com.snc.cmdb.query.api.CmdbQuery.execute(CmdbQuery.java:272)
com.snc.cmdb.query.api.CmdbQuery.jsFunction_execute(CmdbQuery.java:468)
sun.reflect.GeneratedMethodAccessor2669.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138)
org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:670)
org.mozilla.javascript.FunctionObject.call(FunctionObject.java:614)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2582)
org.mozilla.javascript.optimizer.OptRuntime.callProp0(OptRuntime.java:85)
org.mozilla.javascript.gen.search_cmdb_strategy_ac43578a67d313001e802dbd2685efbe_script_4193._c_script_0(search_cmdb_strategy.ac43578a67d313001e802dbd2685efbe.script:27)
org.mozilla.javascript.gen.search_cmdb_strategy_ac43578a67d313001e802dbd2685efbe_script_4193.call(search_cmdb_strategy.ac43578a67d313001e802dbd2685efbe.script)
org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:563)
org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3429)
org.mozilla.javascript.gen.search_cmdb_strategy_ac43578a67d313001e802dbd2685efbe_script_4193.call(search_cmdb_strategy.ac43578a67d313001e802dbd2685efbe.script)
org.mozilla.javascript.gen.search_cmdb_strategy_ac43578a67d313001e802dbd2685efbe_script_4193.exec(search_cmdb_strategy.ac43578a67d313001e802dbd2685efbe.script)
com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:279)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:118)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:82)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:309)
com.glide.script.fencing.GlideScopedEvaluator.evaluateScript(GlideScopedEvaluator.java:168)
com.snc.cmdb_search.strategy.StrategyExecutor.matchAndExecuteStrategy(StrategyExecutor.java:47)
com.snc.cmdb_search.impl.CmdbSearch.search(CmdbSearch.java:47)
com.glide.globalsearch.GroupSearchProvider.attachSearchResults(GroupSearchProvider.java:75)
com.glide.globalsearch.GroupSearchProvider.searchGroups(GroupSearchProvider.java:48)
com.glide.globalsearch.GlobalSearchService.searchGroupsForText(GlobalSearchService.java:91)
com.glide.globalsearch.GlobalSearchService.searchGroupsForText(GlobalSearchService.java:72)
sun.reflect.GeneratedMethodAccessor25180.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
com.glide.rest.handler.impl.ServiceHandlerImpl.invokeService(ServiceHandlerImpl.java:44)
com.glide.rest.processors.RESTAPIProcessor.process(RESTAPIProcessor.java:290)
com.glide.processors.AProcessor.runProcessor(AProcessor.java:531)
com.glide.processors.AProcessor.processTransaction(AProcessor.java:229)
com.glide.processors.ProcessorRegistry.process0(ProcessorRegistry.java:188)
com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:177)
com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:31)
com.glide.sys.Transaction.run(Transaction.java:2147)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:748)

 

- From the logs noticed that call is getting timed out after 45 seconds. (Transaction Quota rule was setup accordingly)

2019-09-20 09:18:07 (539) Default-thread-10 5543ECC91B804414657D6577BC4BCBF6 txid=40c56c451bc0 WARNING *** WARNING *** *** Script: query: "sas install" reached timeout, narrow your search or increase timeout using cmdb.search.timeout java.lang.RuntimeException: CMDB Query Execution timed out. Timeout value: 45000 ms. Execution Time: 0 ms

- Total SQL time is very less compared to waiting time.

2019-09-20 09:18:07 (546) Default-thread-10 5543ECC91B804414657D6577BC4BCBF6 txid=40c56c451bc0 EXCESSIVE *** End #5220733 /api/now/globalsearch/search, user: tejas.thacker@snc, total time: 0:00:47.841, processing time: 0:00:47.841, SQL time: 0:00:02.457 (count: 153), source: 199.91.136.60 , type:rest, method:GET, api_name:now/globalsearch, resource:now/globalsearch/search, version:Default, user_id:tejas.thacker@snc, response_status:200

 

Resolution

  • We can add the stop word to improve performance.
  • When CMDB Search finds a stop word in your search text, that word is ignored.
  • To view stop words, navigate to Configuration > CMDB Search > Stop Words.
  • Here adding stop word "sas" for CMDB search makes global search much faster than earlier for "sas install".

Article Information

Last Updated:2019-11-07 09:46:44
Published:2019-11-07