# Query Performance

**URL:** <https://discuss.elastic.co/t/query-performance/18130>\
**Category:** Elasticsearch\
**Created:** [June 16, 2014, 11:40am UTC](https://discuss.elastic.co/t/query-performance/18130 "2014-06-16T11:40:38Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 16, 2014, 11:40am UTC](https://discuss.elastic.co/t/query-performance/18130/1 "2014-06-16T11:40:38Z")

</div>

Hi All,

I am trying to improve my ES query performance. The goal is to get response  
times for 3 related queries under a second!. In my test i have seen 90th  
percentile response time (_took time_) for combined 3 queries to be ~1.8  
seconds. Here are the details:

_Cluster_:

- 5 Machines, 5 Shards, Currently on m3.2xlarge. (Had started with less  
powerful boxes and went up one by one, started from m3.large)
- 4 indexes.
  - one index with _~90 million_ recrods (total \*19.3 GB \*on all shards  
_._)
  - one with _~24 million_ (total _6GB_ on all shards.)
  - Other two are in 780K and 340K ( total _160MB_ and _190MB_)

- All _fields_ in the larger indexes are _integers_.
- Record size is small-ish.
- indexes are _compressed_.
- I have given _15 GB to ES_ instances.
- Indexes are stored on _EBS_ volumes. Each instance has _250GB_ volume  
with it. (Keeping SSDs as last resort)

The indexes are not changing (for now, in future they would change once a  
day). So no indexing is taking place while we query. _Therefore_, I have  
tried things like _reducing number of segments_ in the two larger indexes.  
That helped to a point.

_Querying Technique_:

- use python ES client.
- _3 small instance_ forking _10 threads_ at the same time.
- Each thread would fire _3 queries_ before reporting a time.
- At time there would be _~100 concurren_t queries on the machines. settles  
around ~50-60.
- I take _'took'_ time from ES response to measure times.
- I _discard 100 records_ before measuring times.
- A total of _5000 unique users_ are used for which 3 ES queries would be  
fired. A total of _4900 users' times_ are measured.

_Observations_:

- RAM is never under stress. Well below 15 GB allotted.
- CPU comes under strain, goes upto 85-95 region on all instances during  
the tests.

_Queries_:

_1. On an index with ~24 Million records_:

res = es.search( index="index1",  
body={"query":{"bool":{"must":[{"term":{"cid":value}}]}}}, sort=[  
"source:desc", "cdate:desc" ], size=100, fields=["wiid"], \_source="true")

i parse results of these queries to get certain fields out and pass on to  
the 2nd query. Lets call those fields as: _q1.field1_ and _q2.field2_

_2. On an index with ~90 million records:_

res1 = es.search(index="index2",  
body={"query":{"filtered":{"filter":{"bool":{"must":{"terms":{"_col\_a_":  
_q1.field1_}},"must\_not":{"terms":{"_col\_b_":_q1.field1_  
}}}}}},"aggs":{"i2B":{"terms":{"field":"_col\_b_", "size": 1000  
,"shard\_size":10000, "order" : { "mss.sum":"desc"}  
},"aggs":{"mss":{"stats":{"script":"ca = \_source._col\_a_;  
index=wiids.indexOf(ca); sval=0; if(index!=-1) sval=svalues.get(index);  
else sval=-1; return \_source.\*col\_x\*\*sval; ","params":{"wiids":_q1.field1_  
,"svalues":_q1.field2_}}},"simSum":{"stats":{"script":"return \_source.  
_col\_x_ "}}}}}}, size=1)

- it uses _filtered query_.
- uses _2 aggregations_
- uses _script in aggregation_.
- use _shard\_size_

Again, i parse results and get a filed out. Lets call that field as:  
_q2.field1_

1. _On an index with ~340K records:_

res2 = es.search(index="index3", body= { "query" : { "filtered" : {  
"query":{ "terms":{ "wiid":_q2.field1_ } }, "filter" : { "bool" : {  
"must" : [ { "range" : {"isInRange": { "gte" : 10 } } } , { "term" : {  
"isCondA" : "false" } } , { "term" : { "isCondB" : "false"} }, { "term" : {  
"isCondC" : "false" } } ] } } } } } , size=1000)

Please let me know if any other information would help you help me.

Query 2 above is doing aggregations and using a custom script. This is  
where times reach few seconds, like 2-3 seconds or even 4+ seconds at  
times.

I can move to a high end CPU machine and may be the performance would  
improve. Wanted to check if there is anything else that i am missing.

Thanks!  
Ravi

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/9148ddfb-1a72-49db-b716-f2f9405392e4%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9148ddfb-1a72-49db-b716-f2f9405392e4%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 17, 2014, 10:33am UTC](https://discuss.elastic.co/t/query-performance/18130/2 "2014-06-17T10:33:38Z")

</div>

Hello All,

Any help one this please?

Thanks!  
Ravi

On Monday, 16 June 2014 12:40:38 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:

> Hi All,
> 
> I am trying to improve my ES query performance. The goal is to get  
> response times for 3 related queries under a second!. In my test i have  
> seen 90th percentile response time (_took time_) for combined 3 queries  
> to be ~1.8 seconds. Here are the details:
> 
> _Cluster_:
> 
> - 5 Machines, 5 Shards, Currently on m3.2xlarge. (Had started with less  
> powerful boxes and went up one by one, started from m3.large)
> - 4 indexes.
> - one index with _~90 million_ recrods (total \*19.3 GB \*on all shards  
> _._)
> - one with _~24 million_ (total _6GB_ on all shards.)
> - Other two are in 780K and 340K ( total _160MB_ and _190MB_)
> 
> - All _fields_ in the larger indexes are _integers_.
> - Record size is small-ish.
> - indexes are _compressed_.
> - I have given _15 GB to ES_ instances.
> - Indexes are stored on _EBS_ volumes. Each instance has _250GB_ volume  
> with it. (Keeping SSDs as last resort)
> 
> The indexes are not changing (for now, in future they would change once a  
> day). So no indexing is taking place while we query. _Therefore_, I have  
> tried things like _reducing number of segments_ in the two larger  
> indexes. That helped to a point.
> 
> _Querying Technique_:
> 
> - use python ES client.
> - _3 small instance_ forking _10 threads_ at the same time.
> - Each thread would fire _3 queries_ before reporting a time.
> - At time there would be _~100 concurren_t queries on the machines.  
> settles around ~50-60.
> - I take _'took'_ time from ES response to measure times.
> - I _discard 100 records_ before measuring times.
> - A total of _5000 unique users_ are used for which 3 ES queries would be  
> fired. A total of _4900 users' times_ are measured.
> 
> _Observations_:
> 
> - RAM is never under stress. Well below 15 GB allotted.
> - CPU comes under strain, goes upto 85-95 region on all instances during  
> the tests.
> 
> _Queries_:
> 
> _1. On an index with ~24 Million records_:
> 
> res = es.search( index="index1",  
> body={"query":{"bool":{"must":[{"term":{"cid":value}}]}}}, sort=[  
> "source:desc", "cdate:desc" ], size=100, fields=["wiid"], \_source="true")
> 
> i parse results of these queries to get certain fields out and pass on to  
> the 2nd query. Lets call those fields as: _q1.field1_ and _q2.field2_
> 
> _2. On an index with ~90 million records:_
> 
> res1 = es.search(index="index2",  
> body={"query":{"filtered":{"filter":{"bool":{"must":{"terms":{"_col\_a_":  
> _q1.field1_}},"must\_not":{"terms":{"_col\_b_":_q1.field1_  
> }}}}}},"aggs":{"i2B":{"terms":{"field":"_col\_b_", "size": 1000  
> ,"shard\_size":10000, "order" : { "mss.sum":"desc"}  
> },"aggs":{"mss":{"stats":{"script":"ca = \_source._col\_a_;  
> index=wiids.indexOf(ca); sval=0; if(index!=-1) sval=svalues.get(index);  
> else sval=-1; return \_source.\*col\_x\*\*sval; ","params":{"wiids":_q1.field1_  
> ,"svalues":_q1.field2_}}},"simSum":{"stats":{"script":"return \_source.  
> _col\_x_ "}}}}}}, size=1)
> 
> - it uses _filtered query_.
> - uses _2 aggregations_
> - uses _script in aggregation_.
> - use _shard\_size_
> 
> Again, i parse results and get a filed out. Lets call that field as:  
> _q2.field1_
> 
> 1. _On an index with ~340K records:_
> 
> res2 = es.search(index="index3", body= { "query" : { "filtered" : {  
> "query":{ "terms":{ "wiid":_q2.field1_ } }, "filter" : { "bool" : {  
> "must" : [ { "range" : {"isInRange": { "gte" : 10 } } } , { "term" : {  
> "isCondA" : "false" } } , { "term" : { "isCondB" : "false"} }, { "term" : {  
> "isCondC" : "false" } } ] } } } } } , size=1000)
> 
> Please let me know if any other information would help you help me.
> 
> Query 2 above is doing aggregations and using a custom script. This is  
> where times reach few seconds, like 2-3 seconds or even 4+ seconds at  
> times.
> 
> I can move to a high end CPU machine and may be the performance would  
> improve. Wanted to check if there is anything else that i am missing.
> 
> Thanks!  
> Ravi

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/cfbbdbd9-dcc7-4f74-a116-798e5bab750c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/cfbbdbd9-dcc7-4f74-a116-798e5bab750c%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ElasticSearch\_Users\_](https://avatars.discourse-cdn.com/v4/letter/e/b4bc9f/32.png) [@ElasticSearch\_Users\_](https://discuss.elastic.co/u/ElasticSearch_Users_)\
**Post date:** [June 17, 2014, 2:28pm UTC](https://discuss.elastic.co/t/query-performance/18130/4 "2014-06-17T14:28:21Z")

</div>

For the first query, since you don't care about the \_score, move the bool  
query into a filter. If you only need field1 and field2 and your \_source is  
big, might be able to save some network payload using source filtering only  
for those 2 fields.

For the second query, if you have a lot RAM and say col\_a and col\_b are not  
big values (long strings) and not high cardinality, you can try to switch  
all \_source.col\_a (or \_source.blah) to doc['col\_a'].value in your scripts.  
This syntax will load the field values into memory and should perform  
faster than \_source.blah. And your last stats agg (simSum), not sure why  
that needs to be a script - can it just be a stats-field on col\_x? Also if  
the second query does not need to return hits (i.e. you only need info from  
the aggs), you can set search\_type=count to further optimize it.

For the third query, if you don't care about \_score, move the query part  
into the filter part.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/38d33be0-286d-4dc3-95e3-8b3fadb7f4df%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/38d33be0-286d-4dc3-95e3-8b3fadb7f4df%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 17, 2014, 2:36pm UTC](https://discuss.elastic.co/t/query-performance/18130/5 "2014-06-17T14:36:39Z")

</div>

Hi Georgi,

Thanks for your response. I clear the caches before each test i.e. a test  
for 5000 unique ids. During the test period, cache size reaches 2.5-3 gb  
for filter cache and 120+ mb for field cache.

The response time (90th percentile) for query 1 is about 85-100  
milliseconds. The max i see is about 375 milliseconds. Ideally i should  
avoid sorting but in this case i need this sorting. And given the times are  
mostly under 100 ms, i guess that sort is fine.

Please let me know if you think otherwise.

Thanks!  
Ravi

On Tuesday, 17 June 2014 15:06:49 UTC+1, Georgi Ivanov wrote:

> Does the response time improve when caches are full ?
> 
> Can you try query without sort and see of things get better ?
> 
> I found that sorting in ES is not good idea sometimes.
> 
> Georgi
> 
> On Monday, June 16, 2014 1:40:38 PM UTC+2, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> 
> > Hi All,
> > 
> > I am trying to improve my ES query performance. The goal is to get  
> > response times for 3 related queries under a second!. In my test i have  
> > seen 90th percentile response time (_took time_) for combined 3 queries  
> > to be ~1.8 seconds. Here are the details:
> > 
> > _Cluster_:
> > 
> > - 5 Machines, 5 Shards, Currently on m3.2xlarge. (Had started with less  
> > powerful boxes and went up one by one, started from m3.large)
> > - 4 indexes.
> > - one index with _~90 million_ recrods (total _19.3 GB on all  
> > shards._)
> > - one with _~24 million_ (total _6GB_ on all shards.)
> > - Other two are in 780K and 340K ( total _160MB_ and _190MB_)
> > 
> > - All _fields_ in the larger indexes are _integers_.
> > - Record size is small-ish.
> > - indexes are _compressed_.
> > - I have given _15 GB to ES_ instances.
> > - Indexes are stored on _EBS_ volumes. Each instance has _250GB_ volume  
> > with it. (Keeping SSDs as last resort)
> > 
> > The indexes are not changing (for now, in future they would change once a  
> > day). So no indexing is taking place while we query. _Therefore_, I have  
> > tried things like _reducing number of segments_ in the two larger  
> > indexes. That helped to a point.
> > 
> > _Querying Technique_:
> > 
> > - use python ES client.
> > - _3 small instance_ forking _10 threads_ at the same time.
> > - Each thread would fire _3 queries_ before reporting a time.
> > - At time there would be _~100 concurren_t queries on the machines.  
> > settles around ~50-60.
> > - I take _'took'_ time from ES response to measure times.
> > - I _discard 100 records_ before measuring times.
> > - A total of _5000 unique users_ are used for which 3 ES queries would  
> > be fired. A total of _4900 users' times_ are measured.
> > 
> > _Observations_:
> > 
> > - RAM is never under stress. Well below 15 GB allotted.
> > - CPU comes under strain, goes upto 85-95 region on all instances during  
> > the tests.
> > 
> > _Queries_:
> > 
> > _1. On an index with ~24 Million records_:
> > 
> > res = es.search( index="index1",  
> > body={"query":{"bool":{"must":[{"term":{"cid":value}}]}}}, sort=[  
> > "source:desc", "cdate:desc" ], size=100, fields=["wiid"], \_source="true")
> > 
> > i parse results of these queries to get certain fields out and pass on to  
> > the 2nd query. Lets call those fields as: _q1.field1_ and _q2.field2_
> > 
> > _2. On an index with ~90 million records:_
> > 
> > res1 = es.search(index="index2",  
> > body={"query":{"filtered":{"filter":{"bool":{"must":{"terms":{"_col\_a_":  
> > _q1.field1_}},"must\_not":{"terms":{"_col\_b_":_q1.field1_  
> > }}}}}},"aggs":{"i2B":{"terms":{"field":"_col\_b_", "size": 1000  
> > ,"shard\_size":10000, "order" : { "mss.sum":"desc"}  
> > },"aggs":{"mss":{"stats":{"script":"ca = \_source._col\_a_;  
> > index=wiids.indexOf(ca); sval=0; if(index!=-1) sval=svalues.get(index);  
> > else sval=-1; return \_source.\*col\_x\*\*sval; ","params":{"wiids":  
> > _q1.field1_,"svalues":_q1.field2_}}},"simSum":{"stats":{"script":"return  
> > \_source._col\_x_ "}}}}}}, size=1)
> > 
> > - it uses _filtered query_.
> > - uses _2 aggregations_
> > - uses _script in aggregation_.
> > - use _shard\_size_
> > 
> > Again, i parse results and get a filed out. Lets call that field as:  
> > _q2.field1_
> > 
> > 1. _On an index with ~340K records:_
> > 
> > res2 = es.search(index="index3", body= { "query" : { "filtered" : {  
> > "query":{ "terms":{ "wiid":_q2.field1_ } }, "filter" : { "bool" : {  
> > "must" : [ { "range" : {"isInRange": { "gte" : 10 } } } , { "term" : {  
> > "isCondA" : "false" } } , { "term" : { "isCondB" : "false"} }, { "term" : {  
> > "isCondC" : "false" } } ] } } } } } , size=1000)
> > 
> > Please let me know if any other information would help you help me.
> > 
> > Query 2 above is doing aggregations and using a custom script. This is  
> > where times reach few seconds, like 2-3 seconds or even 4+ seconds at  
> > times.
> > 
> > I can move to a high end CPU machine and may be the performance would  
> > improve. Wanted to check if there is anything else that i am missing.
> > 
> > Thanks!  
> > Ravi

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/0eb83736-b4e9-476c-adb2-87991dfaa313%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0eb83736-b4e9-476c-adb2-87991dfaa313%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 17, 2014, 2:52pm UTC](https://discuss.elastic.co/t/query-performance/18130/6 "2014-06-17T14:52:31Z")

</div>

Hi Binh,

thanks for helping.

My record size for 1st query is 4 fields. 3 of them integers and a date. so  
the \_source is not big enough to raise concerns. I will anyways try your  
suggestion and report any improvements here.

For the 2nd query: i have 15gb of RAM. only 20% of which gets utilised  
during the tests. Thanks for all three suggestion, Will definitely try that  
and come back here. Good catch for using script in simSum, thanks. I need  
just the sum of that field, which does not need a script. Will change that  
and see what happens.

For the 3rd query, i do not care about the \_score of returned values. Will  
give that a try as well.

Thanks a lot.

Ravi

On Tuesday, 17 June 2014 15:28:21 UTC+1, Binh Ly wrote:

> For the first query, since you don't care about the \_score, move the bool  
> query into a filter. If you only need field1 and field2 and your \_source is  
> big, might be able to save some network payload using source filtering only  
> for those 2 fields.
> 
> For the second query, if you have a lot RAM and say col\_a and col\_b are  
> not big values (long strings) and not high cardinality, you can try to  
> switch all \_source.col\_a (or \_source.blah) to doc['col\_a'].value in your  
> scripts. This syntax will load the field values into memory and should  
> perform faster than \_source.blah. And your last stats agg (simSum), not  
> sure why that needs to be a script - can it just be a stats-field on col\_x?  
> Also if the second query does not need to return hits (i.e. you only need  
> info from the aggs), you can set search\_type=count to further optimize it.
> 
> For the third query, if you don't care about \_score, move the query part  
> into the filter part.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/1d6e9de2-a063-4e41-8068-87f195faaf87%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/1d6e9de2-a063-4e41-8068-87f195faaf87%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 17, 2014, 5:19pm UTC](https://discuss.elastic.co/t/query-performance/18130/7 "2014-06-17T17:19:40Z")

</div>

Hi Binh,

Did some tests and here are the findings:

Moving to c3.4xlarge reduces time by 300 ms. So that takes overall 90th  
percentile down to ~1.5 seconds. CPU still in high 80s-90s.

Making all queries filtered and removing script from 2nd queries' 2nd  
aggregation reduced CPU footprint (high 50s-60s) and improved overall  
timings by close to 200 ms. I am at ~1.3 seconds for all 3 queries.

I guess only next steps now is to play with shard size? or more machines?

Thanks!  
Ravi

On Tuesday, 17 June 2014 15:52:31 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:

> Hi Binh,
> 
> thanks for helping.
> 
> My record size for 1st query is 4 fields. 3 of them integers and a date.  
> so the \_source is not big enough to raise concerns. I will anyways try your  
> suggestion and report any improvements here.
> 
> For the 2nd query: i have 15gb of RAM. only 20% of which gets utilised  
> during the tests. Thanks for all three suggestion, Will definitely try that  
> and come back here. Good catch for using script in simSum, thanks. I need  
> just the sum of that field, which does not need a script. Will change that  
> and see what happens.
> 
> For the 3rd query, i do not care about the \_score of returned values. Will  
> give that a try as well.
> 
> Thanks a lot.
> 
> Ravi
> 
> On Tuesday, 17 June 2014 15:28:21 UTC+1, Binh Ly wrote:
> 
> > For the first query, since you don't care about the \_score, move the bool  
> > query into a filter. If you only need field1 and field2 and your \_source is  
> > big, might be able to save some network payload using source filtering only  
> > for those 2 fields.
> > 
> > For the second query, if you have a lot RAM and say col\_a and col\_b are  
> > not big values (long strings) and not high cardinality, you can try to  
> > switch all \_source.col\_a (or \_source.blah) to doc['col\_a'].value in your  
> > scripts. This syntax will load the field values into memory and should  
> > perform faster than \_source.blah. And your last stats agg (simSum), not  
> > sure why that needs to be a script - can it just be a stats-field on col\_x?  
> > Also if the second query does not need to return hits (i.e. you only need  
> > info from the aggs), you can set search\_type=count to further optimize it.
> > 
> > For the third query, if you don't care about \_score, move the query part  
> > into the filter part.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/a3d880d3-5b33-465a-bb60-3245b7b87854%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a3d880d3-5b33-465a-bb60-3245b7b87854%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 18, 2014, 10:18am UTC](https://discuss.elastic.co/t/query-performance/18130/8 "2014-06-18T10:18:57Z")

</div>

btw, changing search\_type to count did not have much impact on the timings.

On Tuesday, 17 June 2014 18:19:40 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:

> Hi Binh,
> 
> Did some tests and here are the findings:
> 
> Moving to c3.4xlarge reduces time by 300 ms. So that takes overall 90th  
> percentile down to ~1.5 seconds. CPU still in high 80s-90s.
> 
> Making all queries filtered and removing script from 2nd queries' 2nd  
> aggregation reduced CPU footprint (high 50s-60s) and improved overall  
> timings by close to 200 ms. I am at ~1.3 seconds for all 3 queries.
> 
> I guess only next steps now is to play with shard size? or more machines?
> 
> Thanks!  
> Ravi
> 
> On Tuesday, 17 June 2014 15:52:31 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> 
> > Hi Binh,
> > 
> > thanks for helping.
> > 
> > My record size for 1st query is 4 fields. 3 of them integers and a date.  
> > so the \_source is not big enough to raise concerns. I will anyways try your  
> > suggestion and report any improvements here.
> > 
> > For the 2nd query: i have 15gb of RAM. only 20% of which gets utilised  
> > during the tests. Thanks for all three suggestion, Will definitely try that  
> > and come back here. Good catch for using script in simSum, thanks. I need  
> > just the sum of that field, which does not need a script. Will change that  
> > and see what happens.
> > 
> > For the 3rd query, i do not care about the \_score of returned values.  
> > Will give that a try as well.
> > 
> > Thanks a lot.
> > 
> > Ravi
> > 
> > On Tuesday, 17 June 2014 15:28:21 UTC+1, Binh Ly wrote:
> > 
> > > For the first query, since you don't care about the \_score, move the  
> > > bool query into a filter. If you only need field1 and field2 and your  
> > > \_source is big, might be able to save some network payload using source  
> > > filtering only for those 2 fields.
> > > 
> > > For the second query, if you have a lot RAM and say col\_a and col\_b are  
> > > not big values (long strings) and not high cardinality, you can try to  
> > > switch all \_source.col\_a (or \_source.blah) to doc['col\_a'].value in your  
> > > scripts. This syntax will load the field values into memory and should  
> > > perform faster than \_source.blah. And your last stats agg (simSum), not  
> > > sure why that needs to be a script - can it just be a stats-field on col\_x?  
> > > Also if the second query does not need to return hits (i.e. you only need  
> > > info from the aggs), you can set search\_type=count to further optimize it.
> > > 
> > > For the third query, if you don't care about \_score, move the query part  
> > > into the filter part.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/7c1c1e05-89f8-41ca-a2ac-6019af325ef9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/7c1c1e05-89f8-41ca-a2ac-6019af325ef9%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 18, 2014, 4:49pm UTC](https://discuss.elastic.co/t/query-performance/18130/9 "2014-06-18T16:49:44Z")

</div>

Hello All,

As per continued experimentation, i changed

\*indices.cache.filter.size \*from default 20% to 30% on all of my boxes.

I can now see increased memory usage and i see increased cache usage. I see  
my cache jumped from 2.9GB to 4.4 GB which is accurate as allocated RAM is  
15GB.

Even though RAM usage has increased on all machines, i do not see any  
performance improvement. How is that possible? Any clues as to what i might  
be doing wrong here?

Thanks!  
Ravi

On Wednesday, 18 June 2014 11:18:57 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:

> btw, changing search\_type to count did not have much impact on the  
> timings.
> 
> On Tuesday, 17 June 2014 18:19:40 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> 
> > Hi Binh,
> > 
> > Did some tests and here are the findings:
> > 
> > Moving to c3.4xlarge reduces time by 300 ms. So that takes overall 90th  
> > percentile down to ~1.5 seconds. CPU still in high 80s-90s.
> > 
> > Making all queries filtered and removing script from 2nd queries' 2nd  
> > aggregation reduced CPU footprint (high 50s-60s) and improved overall  
> > timings by close to 200 ms. I am at ~1.3 seconds for all 3 queries.
> > 
> > I guess only next steps now is to play with shard size? or more machines?
> > 
> > Thanks!  
> > Ravi
> > 
> > On Tuesday, 17 June 2014 15:52:31 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> > 
> > > Hi Binh,
> > > 
> > > thanks for helping.
> > > 
> > > My record size for 1st query is 4 fields. 3 of them integers and a date.  
> > > so the \_source is not big enough to raise concerns. I will anyways try your  
> > > suggestion and report any improvements here.
> > > 
> > > For the 2nd query: i have 15gb of RAM. only 20% of which gets utilised  
> > > during the tests. Thanks for all three suggestion, Will definitely try that  
> > > and come back here. Good catch for using script in simSum, thanks. I need  
> > > just the sum of that field, which does not need a script. Will change that  
> > > and see what happens.
> > > 
> > > For the 3rd query, i do not care about the \_score of returned values.  
> > > Will give that a try as well.
> > > 
> > > Thanks a lot.
> > > 
> > > Ravi
> > > 
> > > On Tuesday, 17 June 2014 15:28:21 UTC+1, Binh Ly wrote:
> > > 
> > > > For the first query, since you don't care about the \_score, move the  
> > > > bool query into a filter. If you only need field1 and field2 and your  
> > > > \_source is big, might be able to save some network payload using source  
> > > > filtering only for those 2 fields.
> > > > 
> > > > For the second query, if you have a lot RAM and say col\_a and col\_b are  
> > > > not big values (long strings) and not high cardinality, you can try to  
> > > > switch all \_source.col\_a (or \_source.blah) to doc['col\_a'].value in your  
> > > > scripts. This syntax will load the field values into memory and should  
> > > > perform faster than \_source.blah. And your last stats agg (simSum), not  
> > > > sure why that needs to be a script - can it just be a stats-field on col\_x?  
> > > > Also if the second query does not need to return hits (i.e. you only need  
> > > > info from the aggs), you can set search\_type=count to further optimize it.
> > > > 
> > > > For the third query, if you don't care about \_score, move the query  
> > > > part into the filter part.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/f1619774-75a5-49fa-bf17-476b8bbf17f6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/f1619774-75a5-49fa-bf17-476b8bbf17f6%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![ravimbhatt](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@ravimbhatt](https://discuss.elastic.co/u/ravimbhatt)\
**Post date:** [June 19, 2014, 1:12pm UTC](https://discuss.elastic.co/t/query-performance/18130/10 "2014-06-19T13:12:42Z")

</div>

Hi All,

One thing i forgot to mention is that my 3rd query, which takes input from  
the 2nd query, gets close to 500-1000 values from 2nd query. So the \*terms \*query  
gets 500-100 values. The 90th percentile for the third query comes out to  
be ~350 ms.

Thanks!  
Ravi

On Wednesday, 18 June 2014 17:49:45 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:

> Hello All,
> 
> As per continued experimentation, i changed
> 
> \*indices.cache.filter.size \*from default 20% to 30% on all of my boxes.
> 
> I can now see increased memory usage and i see increased cache usage. I  
> see my cache jumped from 2.9GB to 4.4 GB which is accurate as allocated RAM  
> is 15GB.
> 
> Even though RAM usage has increased on all machines, i do not see any  
> performance improvement. How is that possible? Any clues as to what i might  
> be doing wrong here?
> 
> Thanks!  
> Ravi
> 
> On Wednesday, 18 June 2014 11:18:57 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> 
> > btw, changing search\_type to count did not have much impact on the  
> > timings.
> > 
> > On Tuesday, 17 June 2014 18:19:40 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> > 
> > > Hi Binh,
> > > 
> > > Did some tests and here are the findings:
> > > 
> > > Moving to c3.4xlarge reduces time by 300 ms. So that takes overall 90th  
> > > percentile down to ~1.5 seconds. CPU still in high 80s-90s.
> > > 
> > > Making all queries filtered and removing script from 2nd queries' 2nd  
> > > aggregation reduced CPU footprint (high 50s-60s) and improved overall  
> > > timings by close to 200 ms. I am at ~1.3 seconds for all 3 queries.
> > > 
> > > I guess only next steps now is to play with shard size? or more  
> > > machines?
> > > 
> > > Thanks!  
> > > Ravi
> > > 
> > > On Tuesday, 17 June 2014 15:52:31 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> > > 
> > > > Hi Binh,
> > > > 
> > > > thanks for helping.
> > > > 
> > > > My record size for 1st query is 4 fields. 3 of them integers and a  
> > > > date. so the \_source is not big enough to raise concerns. I will anyways  
> > > > try your suggestion and report any improvements here.
> > > > 
> > > > For the 2nd query: i have 15gb of RAM. only 20% of which gets utilised  
> > > > during the tests. Thanks for all three suggestion, Will definitely try that  
> > > > and come back here. Good catch for using script in simSum, thanks. I need  
> > > > just the sum of that field, which does not need a script. Will change that  
> > > > and see what happens.
> > > > 
> > > > For the 3rd query, i do not care about the \_score of returned values.  
> > > > Will give that a try as well.
> > > > 
> > > > Thanks a lot.
> > > > 
> > > > Ravi
> > > > 
> > > > On Tuesday, 17 June 2014 15:28:21 UTC+1, Binh Ly wrote:
> > > > 
> > > > > For the first query, since you don't care about the \_score, move the  
> > > > > bool query into a filter. If you only need field1 and field2 and your  
> > > > > \_source is big, might be able to save some network payload using source  
> > > > > filtering only for those 2 fields.
> > > > > 
> > > > > For the second query, if you have a lot RAM and say col\_a and col\_b  
> > > > > are not big values (long strings) and not high cardinality, you can try to  
> > > > > switch all \_source.col\_a (or \_source.blah) to doc['col\_a'].value in your  
> > > > > scripts. This syntax will load the field values into memory and should  
> > > > > perform faster than \_source.blah. And your last stats agg (simSum), not  
> > > > > sure why that needs to be a script - can it just be a stats-field on col\_x?  
> > > > > Also if the second query does not need to return hits (i.e. you only need  
> > > > > info from the aggs), you can set search\_type=count to further optimize it.
> > > > > 
> > > > > For the third query, if you don't care about \_score, move the query  
> > > > > part into the filter part.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/717e6183-37e7-4542-8371-a8f35382db32%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/717e6183-37e7-4542-8371-a8f35382db32%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Cedric\_Hourcade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cedric_hourcade/32/1451_2.png) [@Cedric\_Hourcade](https://discuss.elastic.co/u/Cedric_Hourcade)\
**Post date:** [June 19, 2014, 4:07pm UTC](https://discuss.elastic.co/t/query-performance/18130/11 "2014-06-19T16:07:05Z")

</div>

Hello,

Can you isolate your slow queries and check if they are slow even when  
running them independently ? Check how many documents are matched by theses  
queries, if they are millions that would explain.

Also you are using a Terms filter with hundreds of entries. If theses  
entries are different for each query, you may want to set the filter  
"execution" to "bool" (or "fielddata" ?) to cache the terms individually  
rather than just the combination of them : { "terms" : { "execution":  
"bool", ... } }

Cédric Hourcade  
[ced@wal.fr](mailto:ced@wal.fr)

Le jeudi 19 juin 2014 15:12:43 UTC+2, [ravim...@gmail.com](mailto:ravim...@gmail.com) a écrit :

> Hi All,
> 
> One thing i forgot to mention is that my 3rd query, which takes input from  
> the 2nd query, gets close to 500-1000 values from 2nd query. So the \*terms  
> \*query gets 500-100 values. The 90th percentile for the third query comes  
> out to be ~350 ms.
> 
> Thanks!  
> Ravi
> 
> On Wednesday, 18 June 2014 17:49:45 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> 
> > Hello All,
> > 
> > As per continued experimentation, i changed
> > 
> > \*indices.cache.filter.size \*from default 20% to 30% on all of my boxes.
> > 
> > I can now see increased memory usage and i see increased cache usage. I  
> > see my cache jumped from 2.9GB to 4.4 GB which is accurate as allocated RAM  
> > is 15GB.
> > 
> > Even though RAM usage has increased on all machines, i do not see any  
> > performance improvement. How is that possible? Any clues as to what i might  
> > be doing wrong here?
> > 
> > Thanks!  
> > Ravi
> > 
> > On Wednesday, 18 June 2014 11:18:57 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> > 
> > > btw, changing search\_type to count did not have much impact on the  
> > > timings.
> > > 
> > > On Tuesday, 17 June 2014 18:19:40 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> > > 
> > > > Hi Binh,
> > > > 
> > > > Did some tests and here are the findings:
> > > > 
> > > > Moving to c3.4xlarge reduces time by 300 ms. So that takes overall 90th  
> > > > percentile down to ~1.5 seconds. CPU still in high 80s-90s.
> > > > 
> > > > Making all queries filtered and removing script from 2nd queries' 2nd  
> > > > aggregation reduced CPU footprint (high 50s-60s) and improved overall  
> > > > timings by close to 200 ms. I am at ~1.3 seconds for all 3 queries.
> > > > 
> > > > I guess only next steps now is to play with shard size? or more  
> > > > machines?
> > > > 
> > > > Thanks!  
> > > > Ravi
> > > > 
> > > > On Tuesday, 17 June 2014 15:52:31 UTC+1, [ravim...@gmail.com](mailto:ravim...@gmail.com) wrote:
> > > > 
> > > > > Hi Binh,
> > > > > 
> > > > > thanks for helping.
> > > > > 
> > > > > My record size for 1st query is 4 fields. 3 of them integers and a  
> > > > > date. so the \_source is not big enough to raise concerns. I will anyways  
> > > > > try your suggestion and report any improvements here.
> > > > > 
> > > > > For the 2nd query: i have 15gb of RAM. only 20% of which gets utilised  
> > > > > during the tests. Thanks for all three suggestion, Will definitely try that  
> > > > > and come back here. Good catch for using script in simSum, thanks. I need  
> > > > > just the sum of that field, which does not need a script. Will change that  
> > > > > and see what happens.
> > > > > 
> > > > > For the 3rd query, i do not care about the \_score of returned values.  
> > > > > Will give that a try as well.
> > > > > 
> > > > > Thanks a lot.
> > > > > 
> > > > > Ravi
> > > > > 
> > > > > On Tuesday, 17 June 2014 15:28:21 UTC+1, Binh Ly wrote:
> > > > > 
> > > > > > For the first query, since you don't care about the \_score, move the  
> > > > > > bool query into a filter. If you only need field1 and field2 and your  
> > > > > > \_source is big, might be able to save some network payload using source  
> > > > > > filtering only for those 2 fields.
> > > > > > 
> > > > > > For the second query, if you have a lot RAM and say col\_a and col\_b  
> > > > > > are not big values (long strings) and not high cardinality, you can try to  
> > > > > > switch all \_source.col\_a (or \_source.blah) to doc['col\_a'].value in your  
> > > > > > scripts. This syntax will load the field values into memory and should  
> > > > > > perform faster than \_source.blah. And your last stats agg (simSum), not  
> > > > > > sure why that needs to be a script - can it just be a stats-field on col\_x?  
> > > > > > Also if the second query does not need to return hits (i.e. you only need  
> > > > > > info from the aggs), you can set search\_type=count to further optimize it.
> > > > > > 
> > > > > > For the third query, if you don't care about \_score, move the query  
> > > > > > part into the filter part.

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/99179994-33be-4753-8dd9-8c3db1fc5be5%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/99179994-33be-4753-8dd9-8c3db1fc5be5%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 1:21am UTC](https://discuss.elastic.co/t/query-performance/18130/12 "2017-07-06T01:21:09Z")

</div>