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" : [ ]
}
}
}
}
}
}