Histogram Aggregation Performance issue on large dataset

Hello,

I have a quick question - How you guys handle working with histogram aggregation on very large datasets? When I display the same histogram on like < 7 days there's no performance issue at all.

However, the larger the time range I set, the more time it takes to get the results. Is it a common practice to store aggregated result in MySQL when it has already been processed by ElasticSearch to improve performance?

Update:

  1. We have a cluster of 3 nodes, 8 GB of RAM each.
  2. We have a monthly index rotation with 45 million documents in each one.
  3. 4 CPU Core for each node.

Could you give an example or a request you are running which you see this issue for? Also:

  • What kind of response times are you seeing for this request?
  • How many documents match your query (i.e. what is hits.total) ?
  • What version of Elasticsearch are you using?
  • How many documents are in the index(es) you are searching?