Search times too long on a big index

Elastic Search
1

On one environment we have too high query execution times (greater than 10 seconds). The query is as follows:

{"query": {
    "bool": {
      "filter": [{"terms": {"id": [237601]}}],
      "must": [
        {
          "nested": {
            "path": "elements",
            "query": {
              "bool": {
                "filter": [{"term": {"elements.type.keyword": "page"}}],
                "must": [
                  {
                    "query_string": {
                      "max_determinized_states": 0,
                      "fuzziness": 0,
                      "auto_generate_synonyms_phrase_query": false,
                      "default_operator": "OR",
                      "fuzzy_max_expansions": 1,
                      "phrase_slop": 5,
                      "query": "\"fogli\" || \"repubblica italiana\"",
                      "fuzzy_transpositions": false,
                      "enable_position_increments": false,
                      "fields": [
                        "elements.text"
                      ],
                      "fuzzy_prefix_length": 0,
                      "escape": false
                    }
                  }
                ]
              }
            },
            "inner_hits": {
              "highlight": {
                "number_of_fragments": 0,
                "fields": {
                  "elements.text": {}
                }
              },
              "size": 100,
              "_source": [
                "elements.id",
                "elements.value",
                "elements.intaking_type",
                "elements.text"
              ]
            }
          }
        }
      ]
    }
},
  "_source": [
    "id",
    "file_name"
  ]
}'```

The installation (version 7.17.3) was done on an 8 node Kubernetes cluster. A number of pods equal to 3 has been configured. Each pod has a request equal to 3 cores and a memory space request equal to 6 GB.
The index is used for documents that contain multiple pages (on average a document contains 8 pages) which are saved using nested elements. The index scheme approximately reflects (except for the dynamic fields) the figure below.
![image|690x423](upload://w4js1Nb1uEIzyGf3apbxHt8XK9w.png)


The index currently contains 17M documents for a total of 1.4Tb. The index was configured with replication at 1 and shard at 0.

**Note that the query filters for only one document at a time.**
The requests that can arrive at the same time are certainly fewer than 10, but we have verified that the problem is present even if we process a single document.

We carried out a test, on the same environment, creating a new index of 17k documents. In this case the queries always respond in less than a second.

We created an index, on another environment, with 7M documents of the same type. In this environment we have better times by more than an order of magnitude. However, this index was built with 10k documents replicated (700 times) until reaching 7M.

Finally, we highlight two aspects of the customer's environment that could be important in the analysis:
- recently, due to the growing number of indexed documents, the PV of the nodes have been increased from 2Tb to 4Tb.
- during our analyzes we noticed that the CPU usage on Elastic pods is high even when the system is unloaded, i.e. no process/customer makes requests to Elastic.

Do you have any advice on how to proceed to understand/mitigate the problem?
2

Hi @gius_alt_01 :

You can use the profile API as a first stop to understand the time spent on queries and the differences between environments in this case

3

We have already tried what you propose. The query phase that weighs most in both environments are the text filters (children "PhraseQuery"), but obviously in the environment of our interest the execution times are considerably higher. I attach an example of profiling result.

{
  "took" : 7559,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "profile" : {
    "shards" : [
      {
        "id" : "[ZhdjYylLSQuANsp7RwOenw][dovalue-1-1][0]",
        "searches" : [
          {
            "query" : [
              {
                "type" : "BooleanQuery",
                "description" : """#id:{3005817} #ToParentBlockJoinQuery (+(elements.text:"consenso assistenza"~5 elements.text:"mutuo agrario"~5 elements.text:"repubblica italiana"~5 elements.text:"richiesto importo euro"~5 elements.text:"signori identità personale"~5) #_type:__elements)""",
                "time_in_nanos" : 4730171472,
                "breakdown" : {
                  "set_min_competitive_score_count" : 0,
                  "match_count" : 0,
                  "shallow_advance_count" : 0,
                  "set_min_competitive_score" : 0,
                  "next_doc" : 0,
                  "match" : 0,
                  "next_doc_count" : 0,
                  "score_count" : 0,
                  "compute_max_score_count" : 0,
                  "compute_max_score" : 0,
                  "advance" : 40799392,
                  "advance_count" : 348,
                  "score" : 0,
                  "build_scorer_count" : 697,
                  "create_weight" : 83502,
                  "shallow_advance" : 0,
                  "create_weight_count" : 1,
                  "build_scorer" : 4689288578
                },
                "children" : [
                  {
                    "type" : "PointInSetQuery",
                    "description" : "id:{3005817}",
                    "time_in_nanos" : 5803678,
                    "breakdown" : {
                      "set_min_competitive_score_count" : 0,
                      "match_count" : 0,
                      "shallow_advance_count" : 0,
                      "set_min_competitive_score" : 0,
                      "next_doc" : 0,
                      "match" : 0,
                      "next_doc_count" : 0,
                      "score_count" : 0,
                      "compute_max_score_count" : 0,
                      "compute_max_score" : 0,
                      "advance" : 102497,
                      "advance_count" : 349,
                      "score" : 0,
                      "build_scorer_count" : 1045,
                      "create_weight" : 500,
                      "shallow_advance" : 0,
                      "create_weight_count" : 1,
                      "build_scorer" : 5700681
                    }
                  },
                  {
                    "type" : "ESToParentBlockJoinQuery",
                    "description" : """ToParentBlockJoinQuery (+(elements.text:"consenso assistenza"~5 elements.text:"mutuo agrario"~5 elements.text:"repubblica italiana"~5 elements.text:"richiesto importo euro"~5 elements.text:"signori identità personale"~5) #_type:__elements)""",
                    "time_in_nanos" : 5654116231,
                    "breakdown" : {
                      "set_min_competitive_score_count" : 0,
                      "match_count" : 0,
                      "shallow_advance_count" : 0,
                      "set_min_competitive_score" : 0,
                      "next_doc" : 0,
                      "match" : 0,
                      "next_doc_count" : 0,
                      "score_count" : 0,
                      "compute_max_score_count" : 0,
                      "compute_max_score" : 0,
                      "advance" : 40607147,
                      "advance_count" : 349,
                      "score" : 0,
                      "build_scorer_count" : 1045,
                      "create_weight" : 63102,
                      "shallow_advance" : 0,
                      "create_weight_count" : 1,
                      "build_scorer" : 5613445982
                    },
                    "children" : [
                      {
                        "type" : "BooleanQuery",
                        "description" : """+(elements.text:"consenso assistenza"~5 elements.text:"mutuo agrario"~5 elements.text:"repubblica italiana"~5 elements.text:"richiesto importo euro"~5 elements.text:"signori identità personale"~5) #_type:__elements""",
                        "time_in_nanos" : 5651479030,
                        "breakdown" : {
                          "set_min_competitive_score_count" : 0,
                          "match_count" : 0,
                          "shallow_advance_count" : 0,
                          "set_min_competitive_score" : 0,
                          "next_doc" : 0,
                          "match" : 0,
                          "next_doc_count" : 0,
                          "score_count" : 0,
                          "compute_max_score_count" : 0,
                          "compute_max_score" : 0,
                          "advance" : 39995678,
                          "advance_count" : 1,
                          "score" : 0,
                          "build_scorer_count" : 1045,
                          "create_weight" : 44601,
                          "shallow_advance" : 0,
                          "create_weight_count" : 1,
                          "build_scorer" : 5611438751
                        },
                        "children" : [
                          {
                            "type" : "BooleanQuery",
                            "description" : """elements.text:"consenso assistenza"~5 elements.text:"mutuo agrario"~5 elements.text:"repubblica italiana"~5 elements.text:"richiesto importo euro"~5 elements.text:"signori identità personale"~5""",
                            "time_in_nanos" : 5645504526,
                            "breakdown" : {
                              "set_min_competitive_score_count" : 0,
                              "match_count" : 0,
                              "shallow_advance_count" : 0,
                              "set_min_competitive_score" : 0,
                              "next_doc" : 0,
                              "match" : 0,
                              "next_doc_count" : 0,
                              "score_count" : 0,
                              "compute_max_score_count" : 0,
                              "compute_max_score" : 0,
                              "advance" : 39982177,
                              "advance_count" : 1,
                              "score" : 0,
                              "build_scorer_count" : 1045,
                              "create_weight" : 28001,
                              "shallow_advance" : 0,
                              "create_weight_count" : 1,
                              "build_scorer" : 5605494348
                            },
                            "children" : [
                              {
                                "type" : "PhraseQuery",
                                "description" : """elements.text:"consenso assistenza"~5""",
                                "time_in_nanos" : 1826595107,
                                "breakdown" : {
                                  "set_min_competitive_score_count" : 0,
                                  "match_count" : 0,
                                  "shallow_advance_count" : 0,
                                  "set_min_competitive_score" : 0,
                                  "next_doc" : 0,
                                  "match" : 0,
                                  "next_doc_count" : 0,
                                  "score_count" : 0,
                                  "compute_max_score_count" : 0,
                                  "compute_max_score" : 0,
                                  "advance" : 18113388,
                                  "advance_count" : 1,
                                  "score" : 0,
                                  "build_scorer_count" : 1027,
                                  "create_weight" : 3900,
                                  "shallow_advance" : 0,
                                  "create_weight_count" : 1,
                                  "build_scorer" : 1808477819
                                }
                              },
                              {
                                "type" : "PhraseQuery",
                                "description" : """elements.text:"mutuo agrario"~5""",
                                "time_in_nanos" : 949268811,
                                "breakdown" : {
                                  "set_min_competitive_score_count" : 0,
                                  "match_count" : 0,
                                  "shallow_advance_count" : 0,
                                  "set_min_competitive_score" : 0,
                                  "next_doc" : 0,
                                  "match" : 0,
                                  "next_doc_count" : 0,
                                  "score_count" : 0,
                                  "compute_max_score_count" : 0,
                                  "compute_max_score" : 0,
                                  "advance" : 3004781,
                                  "advance_count" : 1,
                                  "score" : 0,
                                  "build_scorer_count" : 1033,
                                  "create_weight" : 1100,
                                  "shallow_advance" : 0,
                                  "create_weight_count" : 1,
                                  "build_scorer" : 946262930
                                }
                              },
                              {
                                "type" : "PhraseQuery",
                                "description" : """elements.text:"repubblica italiana"~5""",
                                "time_in_nanos" : 1507441729,
                                "breakdown" : {
                                  "set_min_competitive_score_count" : 0,
                                  "match_count" : 0,
                                  "shallow_advance_count" : 0,
                                  "set_min_competitive_score" : 0,
                                  "next_doc" : 0,
                                  "match" : 0,
                                  "next_doc_count" : 0,
                                  "score_count" : 0,
                                  "compute_max_score_count" : 0,
                                  "compute_max_score" : 0,
                                  "advance" : 5585150,
                                  "advance_count" : 1,
                                  "score" : 0,
                                  "build_scorer_count" : 1031,
                                  "create_weight" : 900,
                                  "shallow_advance" : 0,
                                  "create_weight_count" : 1,
                                  "build_scorer" : 1501855679
                                }
                              },
                              {
                                "type" : "PhraseQuery",
                                "description" : """elements.text:"richiesto importo euro"~5""",
                                "time_in_nanos" : 1333288194,
                                "breakdown" : {
                                  "set_min_competitive_score_count" : 0,
                                  "match_count" : 0,
                                  "shallow_advance_count" : 0,
                                  "set_min_competitive_score" : 0,
                                  "next_doc" : 0,
                                  "match" : 0,
                                  "next_doc_count" : 0,
                                  "score_count" : 0,
                                  "compute_max_score_count" : 0,
                                  "compute_max_score" : 0,
                                  "advance" : 7456601,
                                  "advance_count" : 1,
                                  "score" : 0,
                                  "build_scorer_count" : 1045,
                                  "create_weight" : 1000,
                                  "shallow_advance" : 0,
                                  "create_weight_count" : 1,
                                  "build_scorer" : 1325830593
                                }
                              },
                              {
                                "type" : "PhraseQuery",
                                "description" : """elements.text:"signori identità personale"~5""",
                                "time_in_nanos" : 18270966,
                                "breakdown" : {
                                  "set_min_competitive_score_count" : 0,
                                  "match_count" : 0,
                                  "shallow_advance_count" : 0,
                                  "set_min_competitive_score" : 0,
                                  "next_doc" : 0,
                                  "match" : 0,
                                  "next_doc_count" : 0,
                                  "score_count" : 0,
                                  "compute_max_score_count" : 0,
                                  "compute_max_score" : 0,
                                  "advance" : 5816857,
                                  "advance_count" : 1,
                                  "score" : 0,
                                  "build_scorer_count" : 1023,
                                  "create_weight" : 1101,
                                  "shallow_advance" : 0,
                                  "create_weight_count" : 1,
                                  "build_scorer" : 12453008
                                }
                              }
                            ]
                          },
                          {
                            "type" : "TermQuery",
                            "description" : "_type:__elements",
                            "time_in_nanos" : 2907022,
                            "breakdown" : {
                              "set_min_competitive_score_count" : 0,
                              "match_count" : 0,
                              "shallow_advance_count" : 0,
                              "set_min_competitive_score" : 0,
                              "next_doc" : 0,
                              "match" : 0,
                              "next_doc_count" : 0,
                              "score_count" : 0,
                              "compute_max_score_count" : 0,
                              "compute_max_score" : 0,
                              "advance" : 11500,
                              "advance_count" : 1,
                              "score" : 0,
                              "build_scorer_count" : 1044,
                              "create_weight" : 4500,
                              "shallow_advance" : 0,
                              "create_weight_count" : 1,
                              "build_scorer" : 2891022
                            }
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ],
            "rewrite_time" : 60001,
            "collector" : [
              {
                "name" : "SimpleTopScoreDocCollector",
                "reason" : "search_top_hits",
                "time_in_nanos" : 227312
              }
            ]
          }
        ],
        "aggregations" : [ ]
      }
    ]
  }
}
4

Hmm. Have you followed the recommendations we list for tuning for search speed? The high CPU points to potential resource issues.

In particular, it sounds like you may want to look at your settings for things like merging, caching, and replicas. Also you could experiment with different shard sizes to see if that helps.

As @Carlos_D mentioned, once you have narrowed down environmental issues, the profile API can help determine if there's a difference between different queries.

5

There's a lot of time being spent on build_scorer. Probably the high slop is causing this.

Can you please check the output of the validate API, including the rewrite=true parameter to check which Lucene query is the original query rewritten into?

6

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