# Memory problems while querying

**URL:** <https://discuss.elastic.co/t/memory-problems-while-querying/147099>\
**Category:** Elasticsearch\
**Created:** [September 3, 2018, 3:09pm UTC](https://discuss.elastic.co/t/memory-problems-while-querying/147099 "2018-09-03T15:09:16Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![00PAD00](https://avatars.discourse-cdn.com/v4/letter/0/ea666f/32.png) [@00PAD00](https://discuss.elastic.co/u/00PAD00)\
**Post date:** [September 3, 2018, 3:09pm UTC](https://discuss.elastic.co/t/memory-problems-while-querying/147099/1 "2018-09-03T15:09:16Z")

</div>

Hi,

I am running a query (below) on my cluster which is heavily impacting it since its status turns red. The cluster has 10 data nodes.

```
    {  
        'size':0,
        'query':{  
        'bool':{  
         'must_not':[],
         'must':[  
            {  
               'query_string':{  
                  'analyze_wildcard':'true',
                  'query':'ID:/2[0-9]{11}/ AND NOT timestamp:[1535414400000 TO 1535435999599]'
               }
            },
            {  
               'range':{  
                  'timestamp':{  
                     'gte':1535436000000,
                     'lte':1535437799999,
                     'format':'epoch_millis'
                  }
               }
            }
         ]
      }
   },
   '_source':{  
      'excludes':[  
      ]
   },
   'aggs':{  
      'devices':{  
         'terms':{  
            'field':'ID',
            'order':{  
               '_count':'desc'
            },
            'size':26810
         },
         'aggs':{  
            'timestamps':{  
               'date_histogram':{  
                  'field':'timestamp',
                  'interval':'15m',
                  'time_zone':'Europe/Berlin',
                  'min_doc_count':1
               },
               'aggs':{  
                  'field1':{  
                     'filters':{  
                        'filters':{  
                           'field1_meq-33_or_leq-63':{  
                              'query_string':{  
                                 'query':'FIELD1:<-63 OR FIELD1:>-33',
                                 'analyze_wildcard':'true'
                              }
                           },
                           'field1_total':{  
                              'query_string':{  
                                 'query':'*',
                                 'analyze_wildcard':'true'
                              }
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   }
}

```

The index has a daily format, which means that for the specified timestamp only one index is queried. It has 10 shards, each one being around 50GB and no replicas.

Each data node has 64GB of RAM and 32GB for heap size.

Below you can also see a picture of the JVM heap size for one of the data nodes, during the period the query is performed (around 11h50).

 ![JVM%20heap%20size](https://us1.discourse-cdn.com/elastic/original/3X/0/3/03c02c02f854041c19fa015296fc5b7bea5a84b5.png)

I have been checking multiple posts about these OOM problems to try to understand it. I believe the problem is the query itself, since it is aggregating over many documents (this time range has 5.235.160):

Can someone please help me troubleshoot this issue a bit more? Any other idea why this may be happening? Any solution, such as modifying the query into a "better" one?

Thank you in advance!

---

<div class="post-metadata">

**Author:** ![loren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loren/32/44942_2.png) [@loren](https://discuss.elastic.co/u/loren)\
**Post date:** [September 4, 2018, 4:00pm UTC](https://discuss.elastic.co/t/memory-problems-while-querying/147099/2 "2018-09-04T16:00:10Z")

</div>

The nested aggregations you are doing look strange to me, especially around the filters. Can you explain in words what the intent is there?

On the `query` portion at the top, there are simpler ways to go about this. It looks like you just want `ID`'s that start with `2` in a given date range.

1. Get rid of `must_not` and the source `excludes` since you aren't using them.
2. Change `must` to `filter` since you aren't scoring anything.
3. Delete `AND NOT timestamp...` since the `range` clause makes it impossible to ever match.
4. Change the remaining `query_string` to a simple [prefix query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-prefix-query.html).

Next I would delete the `field1` sub-agg, re-run the query, and see how your heap is behaving.

Finally, 26810 buckets is a lot, so consider [breaking the agg into partitions](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_filtering_values_with_partitions). That will help reduce memory pressure.

Good luck!

---

<div class="post-metadata">

**Author:** ![00PAD00](https://avatars.discourse-cdn.com/v4/letter/0/ea666f/32.png) [@00PAD00](https://discuss.elastic.co/u/00PAD00)\
**Post date:** [September 10, 2018, 8:58am UTC](https://discuss.elastic.co/t/memory-problems-while-querying/147099/3 "2018-09-10T08:58:11Z")

</div>

Hi Loren,

Thank you very much for your quick reply!

We followed your suggestions and we finally have a much lighter query that is not impacting the cluster!

Once again, thank you very much for your help!

---

<div class="post-metadata">

**Author:** ![loren](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/loren/32/44942_2.png) [@loren](https://discuss.elastic.co/u/loren)\
**Post date:** [September 10, 2018, 4:20pm UTC](https://discuss.elastic.co/t/memory-problems-while-querying/147099/4 "2018-09-10T16:20:40Z")

</div>

Great news! Glad to be of help.

---

<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:** [October 8, 2018, 4:27pm UTC](https://discuss.elastic.co/t/memory-problems-while-querying/147099/5 "2018-10-08T16:27:35Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.