Default search query at "Discover" view is very slow

[pavdmyt]

Kibana version:

Version: 6.3.2

Elasticsearch version:

6.3.2

Server OS version:

Running kibana in docker-image FROM alpine:3.5

Browser version:

Firefox 65.0 (64-bit) [users with other browsers experience the same issue too]

Browser OS version:

macOS Mojave 10.14.1

Original install method (e.g. download page, yum, from source, etc.):

Both Kibana and Elasticsearch are running inside kubernetes cluster (v1.12.2)

Describe the bug:

Using Kibana to search through log data stored in production Elasticsearch cluster. Default search from "Discover" view takes too long to provide the results. This is caused by the fact that generated search query contains date_histogram aggregation which slows the search multiple times.

Making default "Last 15 minutes" search at Discover view results in Query duration: 12623ms. 4h search takes approx 120s and makes whole cluster unresponsive. Searches for 8h time ranges and more fail with "502 bad gateway" error message.

Running default search query generated by kibana w\o date_histogram aggregation section makes all above-mentioned searches almost instant.

Steps to reproduce:

1. Run manually the query generated by Discover view (last 4h and setting "profile": true):

GET fluentd-*/_search
{
  "profile": true,
  "version": true,
  "size": 50,
  "sort": [
    {
      "@timestamp": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "5m",
        "time_zone": "Europe/Helsinki",
        "min_doc_count": 1
      }
    }
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    "@timestamp",
    "responseTimestamp",
    "time_iso",
    "timestamp"
  ],
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "gte": 1551446290379,
              "lte": 1551460690379,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "filter": [
        {
          "match_all": {}
        }
      ],
      "should": [],
      "must_not": []
    }
  },
  "highlight": {
    "pre_tags": [
      "@kibana-highlighted-field@"
    ],
    "post_tags": [
      "@/kibana-highlighted-field@"
    ],
    "fields": {
      "*": {}
    },
    "fragment_size": 2147483647
  }
}

The result is as follows:

{
  "took": 116788,
  "timed_out": false,
  "_shards": {
    "total": 2160,
    "successful": 2160,
    "skipped": 1815,
    "failed": 0
  },
  "hits": {
    "total": 137930822,
    "max_score": null,
    "hits": [

2. Run the same search query, but w\o date_histogram section:

GET fluentd-*/_search
{
  "profile": true,
  "version": true,
  "size": 50,
  "sort": [
    {
      "@timestamp": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],
  "_source": {
    "excludes": []
  },
  "stored_fields": [
    "*"
  ],
  "script_fields": {},
  "docvalue_fields": [
    "@timestamp",
    "responseTimestamp",
    "time_iso",
    "timestamp"
  ],
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "gte": 1551446290379,
              "lte": 1551460690379,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "filter": [
        {
          "match_all": {}
        }
      ],
      "should": [],
      "must_not": []
    }
  },
  "highlight": {
    "pre_tags": [
      "@kibana-highlighted-field@"
    ],
    "post_tags": [
      "@/kibana-highlighted-field@"
    ],
    "fields": {
      "*": {}
    },
    "fragment_size": 2147483647
  }
}

The result is as follows:

{
  "took": 2547,
  "timed_out": false,
  "_shards": {
    "total": 2160,
    "successful": 2160,
    "skipped": 1815,
    "failed": 0
  },
  "hits": {
    "total": 137946974,
    "max_score": null,
    "hits": [

116.8s in first case vs 2.5s in second.

Expected behavior:

The default search in Discovery view shouldn't be this slow and affect whole Elasticsearch cluster performance. There should be a way to customize the default search query to avoid building date_histogram.

[pavdmyt]

Trying to optimize date_histogram aggregation I've found that it can be very slow due to time zone setting: #18853.

Setting timezone (dateFormat:tz ) in Kibana from default Browser to Etc/GMT+2 I see search time improvements from 40% to 70% depending on index I'm searching.

[elasticmachine]

Pinging @elastic/kibana-app

[timroes]

I'll close this as a duplicate of #18853.

That issue outlines a lot of the reasons and what we can do and what we can't do. Elasticsearch 6.4 actually has performance improvements when you're looking into date ranges not crossing any Daylight Saving Time, so upgrading to 6.4+ should improve the situation.

[pavdmyt]

@timroes

That issue outlines a lot of the reasons and what we can do and what we can't do.

Can you please point me to some other issues/info regarding what we can do (except for upgrading to 6.4+ and switching to non-DST timezone)?

Thank you.

[timroes]

Unfortunately the answer to this is "nothing" (as kind of stated in the other issue). Searching data in time zones using daylight saving times, will be significantly slower (see also elastic/elasticsearch#28727). So without the optimization added in 6.4, the only thing you can do is using a timezone, that has no daylight saving time, and such not having those performance penalties. So the two options you pointed out are the only ones that will help to get rid of that issue.

[MattiDeGrauwe]

Hi @timroes,

We're also building an Elasticsearch cluster with millions of documents (per index).
The size of our cluster currently indicates 100M+ documents, but it will grow a lot bigger.

We're encountering the exact same issue as @pavdmyt, meaning that the discover tab loads awfully long (+-24s in Elasticsearch 7.3).

Is there any way to optimize the discover tab? As soon as we filter certain values out, it loads faster.
I can export snippets whenever you request some.

Thanks in advance!