# Simple queries takes lots of time and uses 100% cpu **URL:** \ **Category:** Elasticsearch\ **Created:** [November 2, 2019, 3:06pm UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243 "2019-11-02T15:06:13Z")\ **Posts on this page:** 15\ **Page:** 2
**Author:** ![aimarjg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimarjg/32/50430_2.png) [@aimarjg](https://discuss.elastic.co/u/aimarjg)\ **Post date:** [November 14, 2019, 9:25am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/21 "2019-11-14T09:25:57Z")
@DavidTurner: that's exactly what I'm saying - queries executed in devtools are done in seconds, while in discovery tab it takes ~10 times more. Test done right now: **1. Query in discovery tab:** Request code: [https://raw.githubusercontent.com/aimarpl/kb\_queries/master/query\_discovery](https://raw.githubusercontent.com/aimarpl/kb_queries/master/query_discovery) While executing, all CPUs at 100%: ![query_discovery](https://us1.discourse-cdn.com/elastic/original/3X/0/a/0ad5cfaf625d83eb783b9585700ff15626d77315.png) Result after ~15 seconds (first query failed after two minutes, so I had to re-run it and then got response in 15 secs): ![query_discovery_result](https://us1.discourse-cdn.com/elastic/original/3X/8/c/8cc85b3dca60725cf3572d045ff4f278f527e808.png) **2. Query in devtools tab:** Request code: [https://raw.githubusercontent.com/aimarpl/kb\_queries/master/query\_devtools](https://raw.githubusercontent.com/aimarpl/kb_queries/master/query_devtools) While executing & result (result provided almost instantly with almost 0 CPU load): ![query_devtools](https://us1.discourse-cdn.com/elastic/original/3X/a/7/a776b29c94edf57d79399c8ad43cf96321d19148.png) It's crazy... ---
**Author:** ![aimarjg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimarjg/32/50430_2.png) [@aimarjg](https://discuss.elastic.co/u/aimarjg)\ **Post date:** [November 14, 2019, 9:48am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/22 "2019-11-14T09:48:45Z")
I have even disabled highlighting in kibana settings to check on performance then, but... it has no impact at all (it was suggested in one of answers above). ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 14, 2019, 9:58am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/23 "2019-11-14T09:58:21Z")
I see the discover vs console examples are querying different date ranges and timestamps - to further diagnose we need to get these requests as close to each other as possible. The other thing to ensure is that caching is not taking effect. In console you can do this: ``` GET myindex/_search?request_cache=false ``` Another difference between Discover and Console is that Discover will route user requests to the same choice of replica using the `preference` parameter (you can see this value using the Browser's network tools). In your Console test, set `?preference=xxxx` with the same session id used by the Discover tab. ---
**Author:** ![aimarjg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimarjg/32/50430_2.png) [@aimarjg](https://discuss.elastic.co/u/aimarjg)\ **Post date:** [November 14, 2019, 11:21am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/24 "2019-11-14T11:21:44Z")
Good catch, indeed timestamps were a little bit different... I just did another test applying your suggestions into it: 1. Query from Discovery Request code: [https://raw.githubusercontent.com/aimarpl/kb\_queries/master/discovery\_query2](https://raw.githubusercontent.com/aimarpl/kb_queries/master/discovery_query2) **Execution:** ![query_discovery](https://us1.discourse-cdn.com/elastic/original/3X/8/3/834ae681aefcb193f368fde8a973c31ed4dffe5e.png) **Result:** ![query_discovery_result](https://us1.discourse-cdn.com/elastic/original/3X/f/2/f2008f931ed0c662d295690211cfa0ad23a24c36.png) It took 16 seconds 1. Query from dev tools: Request code: [https://raw.githubusercontent.com/aimarpl/kb\_queries/master/devtools\_query2](https://raw.githubusercontent.com/aimarpl/kb_queries/master/devtools_query2) Result: ![query_devtools](https://us1.discourse-cdn.com/elastic/original/3X/c/6/c6dd4628af466e262c1a9de9b0f8c529a2beb7ca.png) It took around 1-2 seconds * * * about replicas used - It's a single node with 5 shards and just 1 replica so I believe it does not matter, does it? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 14, 2019, 11:32am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/25 "2019-11-14T11:32:21Z")
> Indexes are created daily (winlogbeat-YYYY-MM-dd) wi with 1 shard, 0 replicas. > It's a single node with 5 shards and just 1 replica Are we talking about a different setup now? ---
**Author:** ![aimarjg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimarjg/32/50430_2.png) [@aimarjg](https://discuss.elastic.co/u/aimarjg)\ **Post date:** [November 14, 2019, 11:35am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/26 "2019-11-14T11:35:18Z")
No, yes... a little 😉 Few posts above I tried to reconfigure it from 1 shard and 0 replicas to 5 shards and 1 repl - I thought that maybe then it will run some search jobs in pararell increasing the overall performance, but... it had no impact. > [@aimarjg](#): > > I had an idea of enabling more shards (now i have 5 instead of 1) for this index so then it does search in 5 threads in parallel instead of 1, but I don't know if it actually helps on single node ES instance. Does it? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 14, 2019, 12:09pm UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/27 "2019-11-14T12:09:13Z")
> [@aimarjg](#): > > about replicas used - It's a single node with 5 shards and just 1 replica so I believe it does not matter, does it? If you only have a single node you'll never get any replicas (there's no added resilience or throughput benefits in allocating replica shards to the same node as the primary shard). So I guess we can rule out any `preference` difference between Discover and Console. It might be worth noting [Kibana's description of timings](https://discuss.elastic.co/t/what-is-query-duration-and-request-duration-in-kibana/50553). I wonder if there's added overhead in the unpacking of all that JSON in the 500 doc results (did we already establish performance was linear with the `size` setting for hits?) ---
**Author:** ![aimarjg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimarjg/32/50430_2.png) [@aimarjg](https://discuss.elastic.co/u/aimarjg)\ **Post date:** [November 14, 2019, 12:36pm UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/28 "2019-11-14T12:36:08Z")
ah, then, basing on last query output it looks like ES query takes only 54ms - super fast and then preparing all json docs takes 16460ms ...correct? I would test that with different sizes as you proposed, but doing that in devtools/console would not give me results I'm looking for (preparing docs). If I do that from the discovery tab in kibana I can change size in advanced settings, but.... results are cached and each subsequent query for same data is faster. How do I disable cache over there? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 14, 2019, 12:56pm UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/29 "2019-11-14T12:56:49Z")
If the suspicion is that it's all the JSON marshalling that takes the time the elasticsearch caches may be somewhat irrelevant. That said, there's an API you can use to [clear the caches](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-clearcache.html) ---
**Author:** ![aimarjg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimarjg/32/50430_2.png) [@aimarjg](https://discuss.elastic.co/u/aimarjg)\ **Post date:** [November 14, 2019, 1:49pm UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/30 "2019-11-14T13:49:11Z")
ok, good hint **first query, 500 results** - query time: 170ms - request time: 27339ms **second query after clearing the cache with following:** POST /winlogbeat-2019.11.14/\_cache/clear POST /winlogbeat-2019.11.13/\_cache/clear Result: # POST /winlogbeat-2019.11.14/\_cache/clear { "\_shards" : { "total" : 5, "successful" : 5, "failed" : 0 } } ``` # POST /winlogbeat-2019.11.13/_cache/clear { "_shards" : { "total" : 5, "successful" : 5, "failed" : 0 } } ``` Query time: 129ms Request time: 634ms so... even after clearing cache, second query is very fast (for the same data); how is that possible?! ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 14, 2019, 2:04pm UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/31 "2019-11-14T14:04:52Z")
There's OS-level filesystem caches to consider too but that does look like a suspiciously large difference. ---
**Author:** ![Alfredo5520](https://avatars.discourse-cdn.com/v4/letter/a/3bc359/32.png) [@Alfredo5520](https://discuss.elastic.co/u/Alfredo5520)\ **Post date:** [November 23, 2019, 7:49am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/32 "2019-11-23T07:49:29Z")
After analyzing the website type and traffic, we tweak the key settings in MySQL servers that helps to ensure optimal resource usage. This includes connection limits, buffer size and query cache size, and more. ---
**Author:** ![aimarjg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aimarjg/32/50430_2.png) [@aimarjg](https://discuss.elastic.co/u/aimarjg)\ **Post date:** [November 30, 2019, 7:44pm UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/33 "2019-11-30T19:44:36Z")
@Mark_Harwood, @DavidTurner - first of all, thank for you all your hints here 🙂 finally some good news here 🙂 Got it fixed - I have disabled filterEditor:suggestValues in Kibana settings so KQL won't suggest values in discovery search and problem is gone. **filterEditor:suggestValues enabled:** |Query time|271ms| |Request time|53354ms| **filterEditor:suggestValues disabled:** |Query time|380ms| |Request time|1718ms| Finally it all works as it should, question is - shouldn't this feature get reported as a buggy? As I read in other threads it looks up all indicies instead of those specified in time range picker which causes such CPU load. Anyway - I hope that this workaround will help others having same issue with ELK 7. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [December 2, 2019, 9:38am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/34 "2019-12-02T09:38:20Z")
Glad to hear you identified the bottleneck. I added a [note](https://github.com/elastic/kibana/issues/46054#issuecomment-560313866) to the relevant Kibana issue. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 30, 2019, 9:38am UTC](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243/35 "2019-12-30T09:38:21Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed. [Previous page](https://discuss.elastic.co/t/simple-queries-takes-lots-of-time-and-uses-100-cpu/206243.md?page=1)