Slow date_histogram after upgrading to 7.3.0

[brenuart]

Elasticsearch version (bin/elasticsearch --version):
Version: 7.3.0, Build: default/rpm/de777fa/2019-07-24T18:30:11.767338Z, JVM: 12.0.1

Plugins installed: []
repository-s3

JVM version (java -version):
JVM shipped with Elasticsearch (rpm)

OS version (uname -a if on a Unix-like system):
Linux es-hot-03.aegaeon-it.com 4.9.120-xxxx-std-ipv6-64 #327490 SMP Thu Aug 16 10:11:35 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

Description of the problem including expected versus actual behavior:
We noticed a severe degradation of date_histogram performance after upgrading from Elasticsearch 6.8.2 to the latest 7.3.0.

The table below shows the execution time of the same query on the same cluster before and after the upgrade. The first execution happens after caches are cleared as follows:

• ES caches are cleared with POST _cache/clear
• Filesystem caches are invalidated by doing a cat of all files found in /var/lib/elasticsearch do /dev/null (that's the best we found...)

Timings are as follows:

ES Version   Timings (ms)
------------------------------------
6.8.2        10388,  5797,  7319
7.3.0        21553, 15138, 14833

As you notice 7.3.0 is at least twice slower than 6.8.2.

The dataset is made of about 650m documents spread evenly between 15 indexes. Indexes have 3 shards each and no replica. The cluster is made of three nodes, each with 7Gb RAM (of each 4Gb is allocated to the heap) and 2 vCPUs at 3.1Ghz. There is no activity on the cluster besides this test query.

The query targets only half of the documents (using a date_range) and builds a date_histogram with buckets of 3h. This query is actually what KIbana's Discover panel will do...

{
  "size": 500,
  "sort": [
    {
      "@timestamp": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "3h",
        "time_zone": "Europe/Berlin",
        "min_doc_count": 1
      }
    }
  },
  "docvalue_fields": [
    {
      "field": "@timestamp",
      "format": "date_time"
    }
  ],
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2019-01-09T00:00:00.000Z",
              "lte": "2019-01-15T00:00:00.000Z"
            }
          }
        }
      ]
    }
  }
}

We are very surprised by this drop of performance...
Did we forgot to change some configurations parameters when doing the upgrade or is it a regression in ES itself ?

[dliappis]

Thanks very much for your interest in Elasticsearch.

This appears to be a user question, and we'd like to direct these kinds of things to the forums. If you can stop by there, we'd appreciate it. This allows us to use GitHub for verified bug reports, feature requests, and pull requests.

There's an active community in the forums that should be able to help get an answer to your question. As such, I hope you don't mind that I close this.

Finally note that we track performance across releases in https://elasticsearch-benchmarks.elastic.co and you can see results between 6.8 and 7.3 e.g. for date_histogram using the nyc_taxis track in https://elasticsearch-benchmarks.elastic.co/#tracks/nyc-taxis/release.

[brenuart]

I'm linking here to the forum post I created: https://discuss.elastic.co/t/slow-date-histogram-after-upgrading-to-7-3-0-on-dense-indexes/196475.

Please note that we could not reproduce the issue with the nyc_taxis dataset but with a custom one. The reason (as described in the post) is dates in the nyc_taxis dataset are not close enough to each other to trigger the issue.

[spinscale]

reopening, as a performance issue between 6.8 and 7.3 seems to exist and needs to be evaluated.

Thanks for opening @brenuart!

[spinscale]

See https://discuss.elastic.co/t/slow-date-histogram-after-upgrading-to-7-3-0-on-dense-indexes/196475/8?u=spinscale to download the four datasets mentioned in https://discuss.elastic.co/t/slow-date-histogram-after-upgrading-to-7-3-0-on-dense-indexes/196475/5

https://drive.google.com/drive/folders/1ZIQjh00zOFyLoLY8vTEo2Tm2T0ZJ4AnD

[elasticmachine]

Pinging @elastic/es-analytics-geo

[vboulaye]

Hi,

We are in the process of upgrading our ELK stack from 6.8 to 7.3 and we see exactly the same behaviour.

In our logs the @timestamps are very close from each other and we see a huge degradation of the response time on the default query of kibana.
It is 4 to 5 times slower to run the default query ( >30s for the last 7 days.)

I do not have a nice dataset like @brenuart created to reproduce the issue, but ran some test queries that show the problem (839ms in ES6.8, 4683ms in ES7.3):

In ES 6.8

GET log-appli-2019.09.06/_search
{ 
  "size": 1,
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "3h",
        "time_zone": "Europe/Berlin",
        "min_doc_count": 1
      }
    }
  }, 
  "timeout": "30000ms"
}
=>
{
  "took" : 839,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 53079313,
    "max_score" : 1.0,
    "hits" : [
...

in ES 7.3

GET /log-appli-2019.09.06/_search
{
  "size": 1,
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "@timestamp",
        "fixed_interval": "3h",
        "time_zone": "Europe/Berlin",
        "min_doc_count": 1
      }
    }
  },
  "timeout": "30000ms"
}


=>

{
  "took" : 4683,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : 1.0,
    "hits" : [
...

[polyfractal]

Thanks for the extra details @vboulaye! We're actively looking into this issue right now, will update as we diagnose.

Out of curiosity, would you mind trying those queries again without the time_zone parameter? We've seen performance issues with time zone conversion in the past (#28727). I don't think that's the case here but it would be good to rule out.

[brenuart]

@polyfractal We did the test with and without timezone with both versions. 7.3 is indeed a few seconds slower when a timezone is set.
But if you look at my report posted in the forum (https://discuss.elastic.co/t/slow-date-histogram-after-upgrading-to-7-3-0-on-dense-indexes/196475), the queries we used for testing don’t have this parameter set. This was on purpose to reduce the amount of variable factors in the test case...
Timezone is apparently not the (principal) culprit here.

[polyfractal]

Noted, thanks for the clarification!