Upgrade to 7.x aggregation performance degradation

We are currently in process of migrating our Cloud tenants to 7.16.3 in preparation for an internal 8.6 Elasticsearch upgrade of our software. However, we have noticed that our aggregation queries have suffered a hard unexplainable hit in terms of performance.

We could see when migrating our test cluster with 3 nodes and around 1613 total primary shards our aggregation queries a lot of times decreased by up to 200% in performance. From my personal investigation, it seems to be connected to aggregations on keyword fields which is probably not the best thing to do anyway. However, we were surprised about the huge jump in aggregation times between 2 ES versions.

I prepared a small example based on one of our tenants which has an index size of 22.5GB split into 3 primary shards and 2 replicas. Since we don't have an identical cluster to compare this to anymore we have set up a dedicated 6.8 ES cluster to compare with. This only holds around 33 primary shards but I hope it is still comparable.

Index Mapping:

{
  "CustomerA_export_Article" : {
    "mappings" : {
      "properties" : {
        "booleanFields" : {
          "type" : "nested",
          "include_in_parent" : true,
          "properties" : {
            "aggregationValue" : {
              "type" : "keyword"
            },
            "attrId" : {
              "type" : "keyword"
            },
            "formattedValue" : {
              "type" : "keyword",
              "normalizer" : "lcandasciifolding_normalizer"
            },
            "itemId" : {
              "type" : "long"
            },
            "languageId" : {
              "type" : "long"
            },
            "referenceId" : {
              "type" : "long"
            },
            "referenceTargetId" : {
              "type" : "long"
            },
            "searchType" : {
              "type" : "keyword",
              "index" : false
            },
            "tableId" : {
              "type" : "keyword"
            },
            "value" : {
              "type" : "boolean"
            }
          }
        },
        "dateFields" : {
          "type" : "nested",
          "include_in_parent" : true,
          "properties" : {
            "aggregationValue" : {
              "type" : "keyword"
            },
            "attrId" : {
              "type" : "keyword"
            },
            "formattedValue" : {
              "type" : "keyword",
              "normalizer" : "lcandasciifolding_normalizer"
            },
            "itemId" : {
              "type" : "long"
            },
            "languageId" : {
              "type" : "long"
            },
            "referenceId" : {
              "type" : "long"
            },
            "referenceTargetId" : {
              "type" : "long"
            },
            "searchType" : {
              "type" : "keyword",
              "index" : false
            },
            "tableId" : {
              "type" : "keyword"
            },
            "value" : {
              "type" : "keyword"
            }
          }
        },
        "numericFields" : {
          "type" : "nested",
          "include_in_parent" : true,
          "properties" : {
            "aggregationValue" : {
              "type" : "keyword"
            },
            "attrId" : {
              "type" : "keyword"
            },
            "formattedValue" : {
              "type" : "keyword",
              "normalizer" : "lcandasciifolding_normalizer"
            },
            "itemId" : {
              "type" : "long"
            },
            "languageId" : {
              "type" : "long"
            },
            "referenceId" : {
              "type" : "long"
            },
            "referenceTargetId" : {
              "type" : "long"
            },
            "searchType" : {
              "type" : "keyword",
              "index" : false
            },
            "tableId" : {
              "type" : "keyword"
            },
            "value" : {
              "type" : "double"
            }
          }
        },
        "references" : {
          "type" : "nested",
          "include_in_parent" : true,
          "properties" : {
            "languageId" : {
              "type" : "long"
            },
            "referenceId" : {
              "type" : "long"
            },
            "referenceTargetId" : {
              "type" : "long"
            },
            "referenceTargetType" : {
              "type" : "keyword"
            }
          }
        },
        "simpleSearchText" : {
          "type" : "nested",
          "include_in_parent" : true,
          "properties" : {
            "languageId" : {
              "type" : "long"
            },
            "value" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 10922,
                  "normalizer" : "lcandasciifolding_normalizer"
                }
              },
              "analyzer" : "whitespace_analyzer"
            }
          }
        },
        "textFields" : {
          "type" : "nested",
          "include_in_parent" : true,
          "properties" : {
            "aggregationValue" : {
              "type" : "keyword",
              "ignore_above" : 10922
            },
            "attrId" : {
              "type" : "keyword"
            },
            "formattedValue" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 10922,
                  "normalizer" : "lcandasciifolding_normalizer"
                }
              },
              "analyzer" : "whitespace_analyzer"
            },
            "itemId" : {
              "type" : "long"
            },
            "languageId" : {
              "type" : "long"
            },
            "referenceId" : {
              "type" : "long"
            },
            "referenceTargetId" : {
              "type" : "long"
            },
            "searchType" : {
              "type" : "keyword",
              "index" : false
            },
            "tableId" : {
              "type" : "keyword"
            },
            "value" : {
              "type" : "text",
              "analyzer" : "whitespace_analyzer"
            }
          }
        }
      }
    }
  }
}

Aggregation Query: (EDIT: I accidentally uploaded a stripped down query, however the full query is too big for this post. It is a basically a repetition of more of the "textFields -> aggregationValue" buckets)

GET CustomerA_export_*/_search
{
  "size": "0",
  "aggs": {
    "index_buckets_Article": {
      "filter": {
        "bool": {
          "must": [
            {
              "terms": {
                "_index": [
                  "CustomerA_export_Article"
                ]
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "bool": {
                      "must": [
                        {
                          "nested": {
                            "path": "simpleSearchText",
                            "query": {
                              "bool": {
                                "must": [
                                  {
                                    "bool": {
                                      "should": [
                                        {
                                          "match": {
                                            "simpleSearchText.languageId": 1
                                          }
                                        },
                                        {
                                          "match": {
                                            "simpleSearchText.languageId": "0"
                                          }
                                        }
                                      ]
                                    }
                                  },
                                  {
                                    "wildcard": {
                                      "simpleSearchText.value.keyword": "*small*"
                                    }
                                  }
                                ]
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "aggs": {
        "attribute_6611": {
          "nested": {
            "path": "textFields"
          },
          "aggs": {
            "nested_6611": {
              "filter": {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "textFields.attrId": {
                          "value": "6611"
                        }
                      }
                    },
                    {
                      "bool": {
                        "should": [
                          {
                            "match": {
                              "textFields.languageId": "1"
                            }
                          },
                          {
                            "match": {
                              "textFields.languageId": "0"
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              },
              "aggs": {
                "values": {
                  "terms": {
                    "field": "textFields.aggregationValue",
                    "size": 1000,
                    "order": {
                      "_key": "asc"
                    }
                  }
                }
              }
            }
          }
        },
        "attribute_6849": {
          "nested": {
            "path": "textFields"
          },
          "aggs": {
            "nested_6849": {
              "filter": {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "textFields.attrId": {
                          "value": "6849"
                        }
                      }
                    },
                    {
                      "bool": {
                        "should": [
                          {
                            "match": {
                              "textFields.languageId": "1"
                            }
                          },
                          {
                            "match": {
                              "textFields.languageId": "0"
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              },
              "aggs": {
                "values": {
                  "terms": {
                    "field": "textFields.aggregationValue",
                    "size": 1000,
                    "order": {
                      "_key": "asc"
                    }
                  }
                }
              }
            }
          }
        },
        "attribute_6918": {
          "nested": {
            "path": "textFields"
          },
          "aggs": {
            "nested_6918": {
              "filter": {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "textFields.attrId": {
                          "value": "6918"
                        }
                      }
                    },
                    {
                      "bool": {
                        "should": [
                          {
                            "match": {
                              "textFields.languageId": "1"
                            }
                          },
                          {
                            "match": {
                              "textFields.languageId": "0"
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              },
              "aggs": {
                "values": {
                  "terms": {
                    "field": "textFields.aggregationValue",
                    "size": 1000,
                    "order": {
                      "_key": "asc"
                    }
                  }
                }
              }
            }
          }
        },
        "attribute_6920": {
          "nested": {
            "path": "textFields"
          },
          "aggs": {
            "nested_6920": {
              "filter": {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "textFields.attrId": {
                          "value": "6920"
                        }
                      }
                    },
                    {
                      "bool": {
                        "should": [
                          {
                            "match": {
                              "textFields.languageId": "1"
                            }
                          },
                          {
                            "match": {
                              "textFields.languageId": "0"
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              },
              "aggs": {
                "values": {
                  "terms": {
                    "field": "textFields.aggregationValue",
                    "size": 1000,
                    "order": {
                      "_key": "asc"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

This aggregation query takes 573ms on our 6.8 instance but 1138ms on our 7.1 instance when doing a POST _cache/clear beforehand. The more aggregations on our "keyword" field i add the more the performance between versions differentiates. Therefore my question would be if there are any known changes that could be causing this?

Here are the results of the query on both instances...
...6.8:

{
  "took" : 556,
  "timed_out" : false,
  "_shards" : {
    "total" : 6,
    "successful" : 6,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 434226,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "index_buckets_Article" : {
      "doc_count" : 1,
      "attribute_7043" : {
        "doc_count" : 30,
        "nested_7043" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_9154" : {
        "doc_count" : 30,
        "nested_9154" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7151" : {
        "doc_count" : 30,
        "nested_7151" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_9159" : {
        "doc_count" : 30,
        "nested_9159" : {
          "doc_count" : 1,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "Track Pants~~||~~15469",
                "doc_count" : 1
              }
            ]
          }
        }
      },
      "attribute_8477" : {
        "doc_count" : 40,
        "nested_8477" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_9147" : {
        "doc_count" : 30,
        "nested_9147" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7198" : {
        "doc_count" : 40,
        "nested_7198" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7121" : {
        "doc_count" : 40,
        "nested_7121" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7055" : {
        "doc_count" : 30,
        "nested_7055" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6849" : {
        "doc_count" : 30,
        "nested_6849" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6918" : {
        "doc_count" : 30,
        "nested_6918" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6611" : {
        "doc_count" : 30,
        "nested_6611" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_8419" : {
        "doc_count" : 30,
        "nested_8419" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7209" : {
        "doc_count" : 30,
        "nested_7209" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6933" : {
        "doc_count" : 30,
        "nested_6933" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7208" : {
        "doc_count" : 30,
        "nested_7208" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7207" : {
        "doc_count" : 30,
        "nested_7207" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7119" : {
        "doc_count" : 40,
        "nested_7119" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6920" : {
        "doc_count" : 30,
        "nested_6920" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7206" : {
        "doc_count" : 30,
        "nested_7206" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6921" : {
        "doc_count" : 30,
        "nested_6921" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      }
    }
  }
}

...7.1:

{
  "took" : 1138,
  "timed_out" : false,
  "_shards" : {
    "total" : 6,
    "successful" : 6,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "index_buckets_Article" : {
      "meta" : { },
      "doc_count" : 1,
      "attribute_7043" : {
        "doc_count" : 30,
        "nested_7043" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_9154" : {
        "doc_count" : 30,
        "nested_9154" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7151" : {
        "doc_count" : 30,
        "nested_7151" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_9159" : {
        "doc_count" : 30,
        "nested_9159" : {
          "doc_count" : 1,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "Track Pants~~||~~15469",
                "doc_count" : 1
              }
            ]
          }
        }
      },
      "attribute_8477" : {
        "doc_count" : 40,
        "nested_8477" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_9147" : {
        "doc_count" : 30,
        "nested_9147" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7198" : {
        "doc_count" : 40,
        "nested_7198" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7121" : {
        "doc_count" : 40,
        "nested_7121" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7055" : {
        "doc_count" : 30,
        "nested_7055" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6849" : {
        "doc_count" : 30,
        "nested_6849" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6918" : {
        "doc_count" : 30,
        "nested_6918" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6611" : {
        "doc_count" : 30,
        "nested_6611" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_8419" : {
        "doc_count" : 30,
        "nested_8419" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7209" : {
        "doc_count" : 30,
        "nested_7209" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6933" : {
        "doc_count" : 30,
        "nested_6933" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7208" : {
        "doc_count" : 30,
        "nested_7208" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7207" : {
        "doc_count" : 30,
        "nested_7207" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7119" : {
        "doc_count" : 40,
        "nested_7119" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6920" : {
        "doc_count" : 30,
        "nested_6920" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_7206" : {
        "doc_count" : 30,
        "nested_7206" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      },
      "attribute_6921" : {
        "doc_count" : 30,
        "nested_6921" : {
          "doc_count" : 0,
          "values" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [ ]
          }
        }
      }
    }
  }
}

Unfortunately still struggling with the root cause of this issue.

I can definitely see a huge performance gap the more aggregations of textFields.aggregationValue (type keyword) i add. However i can not find a reason why this should behave so much differently between 6.8 and 7.16

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