Need help in improving search speed (about 20s) (elastic 6.3)

Elastic Stack Elasticsearch
1

Hello community,

I have an elastic search cluster split around 2 nodes (and given 4gb ram to elastic on each). There I have an index containing about 80 million docs. I have split this across 10 shards. There are many aggregations and searches I do, after reading at many I places I converted many long fields to string keyword fields with eager loading of cardinals enabled. After all this my results are still terrible (20-30 seconds) and knowing elastic search this should have been less than a sec. Please help in finding out what I am doing wrong and how can I fix this.

Index settings

 "settings": {
    "index": {
      "refresh_interval": "-1",
      "number_of_shards": "10",
      "translog": {
        "durability": "async"
      },
      "provided_name": "a",
      "creation_date": "1558624380928",
      "priority": "100",
      "number_of_replicas": "1",
      "uuid": "6PyhVEhFTcSNxhqyUE5SaQ",
      "version": {
        "created": "6030299"
      }
    }
  },

index mapping
https://pastebin.com/rgJvEAvT

query

POST /doc/doc/_search?pretty
{ 
  "profile": "true",
  "from": 0,
  "size": 20,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "a.keyword": "hello"
          }
        },{
          "term": {
            "b.keyword": 260464
          }
        }
      ]
    }
  },
  "aggs": {
    "a": {
      "terms": {
        "field": "a.keyword"
      }
    },
    "b": {
      "terms": {
        "field": "b.keyword"
      }
    },
    "c": {
      "terms": {
        "field": "c.keyword"
      }
    },
    "d": {
      "terms": {
        "field": "d.keyword"
      }
    },
    "e": {
      "terms": {
        "field": "d.keyword"
      }
    },
    "f": {
      "terms": {
        "field": "e.keyword"
      }
    },
    "g": {
      "terms": {
        "field": "f.keyword"
      }
    },
    "h": {
      "terms": {
        "field": "g.keyword"
      }
    },
    "_id_count": {
      "value_count": {
        "field": "_id"
      }
    }
  },
  "sort": [
    {
      "updated_at": {
        "order": "desc"
      }
    }
  ]
}

profile of one shard
https://pastebin.com/AjJR1Baz

2

The query you shared and the query which is profiled seem different +a:a +b:b + c:c. Could you explain that? BTW could you run the profiler with ?human parameter so the times should be more readable?

3

@dadoonet you got me :slight_smile: , the data is a bit sensitive. I didn't know there was a human param and was used to convert nano secs to secs. Thank you for your time!

https://pastebin.com/2PaURqTQ

4

Was the search cancelled ? How took that one?

5

Nope I didn't cancel it, it seems to have done it. the results also appear to be fine

  "took": 7896,
  "timed_out": false,
  "_shards": {
    "total": 10,
    "successful": 10,
    "skipped": 0,
    "failed": 0
  },
6

What kind of hardware configuration do you have ?

7

Both the nodes have following configuration

Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                2
On-line CPU(s) list:   0,1
Thread(s) per core:    2
Core(s) per socket:    1
Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 85
Model name:            Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
Stepping:              4
CPU MHz:               2500.000
BogoMIPS:              5000.00
Hypervisor vendor:     KVM
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              1024K
L3 cache:              33792K
NUMA node0 CPU(s):     0,1

Both have 8gm ram each and 4gb allocated to ElasticSearch. During the setup we also increased the max number of open files to 65k. The complete output of node stats is here:
https://pastebin.com/avGNmfa5

8

What is the total size of the data on disk? What type of disk do you have? What does CPU usage look like when you are running a slow query?

9

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