We have a visualisation that uses the below given query to fetch the data.
GET commerce-access-console-logs-*/_search
{
"aggs": {
"3": {
"filters": {
"filters": {
"LUX-SearchProducts": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"should": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/products\\/serpsearch",
"lenient": true
}
},
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/products\\/searchProducts",
"lenient": true
}
}
],
"minimum_should_match": 1
}
}
],
"should": [],
"must_not": []
}
},
"LUX-SearchProducts-Error": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"bool": {
"should": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/products\\/serpsearch",
"lenient": true
}
},
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/products\\/searchProducts",
"lenient": true
}
}
],
"minimum_should_match": 1
}
},
{
"bool": {
"must_not": {
"bool": {
"should": [
{
"match": {
"responseCode": "200"
}
}
],
"minimum_should_match": 1
}
}
}
}
]
}
}
],
"should": [],
"must_not": []
}
},
"LUX-CartDetails": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/",
"lenient": true
}
},
{
"query_string": {
"query": "*cartDetails*"
}
},
{
"bool": {
"must_not": {
"bool": {
"should": [
{
"query_string": {
"fields": [
"request"
],
"query": "*APItest@gmail\\.com*"
}
}
],
"minimum_should_match": 1
}
}
}
}
]
}
}
],
"should": [],
"must_not": []
}
},
"LUX-CartDetails-Error": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/",
"lenient": true
}
},
{
"query_string": {
"query": "*cartDetails*"
}
},
{
"bool": {
"must_not": {
"bool": {
"should": [
{
"query_string": {
"fields": [
"request"
],
"query": "*APItest@gmail\\.com*"
}
}
],
"minimum_should_match": 1
}
}
}
},
{
"bool": {
"must_not": {
"bool": {
"should": [
{
"match": {
"responseCode": "200"
}
}
],
"minimum_should_match": 1
}
}
}
}
]
}
}
],
"should": [],
"must_not": []
}
},
"LUX-Payment Intent": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/",
"lenient": true
}
},
{
"query_string": {
"query": "*collectPaymentOrder*"
}
}
]
}
}
],
"should": [],
"must_not": []
}
},
"LUX-Payment Intent-Error": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/",
"lenient": true
}
},
{
"query_string": {
"query": "*collectPaymentOrder*"
}
},
{
"bool": {
"must_not": {
"bool": {
"should": [
{
"match": {
"responseCode": "200"
}
}
],
"minimum_should_match": 1
}
}
}
}
]
}
}
],
"should": [],
"must_not": []
}
},
"LUX-CustomerLogin": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/",
"lenient": true
}
},
{
"query_string": {
"query": "*customerLogin*"
}
}
]
}
}
],
"should": [],
"must_not": []
}
},
"LUX-CustomerLogin-Error": {
"bool": {
"must": [],
"filter": [
{
"bool": {
"filter": [
{
"multi_match": {
"type": "phrase",
"query": "\\/marketplacewebservices\\/v2\\/lux\\/",
"lenient": true
}
},
{
"query_string": {
"query": "*customerLogin*"
}
},
{
"bool": {
"must_not": {
"bool": {
"should": [
{
"match": {
"responseCode": "200"
}
}
],
"minimum_should_match": 1
}
}
}
}
]
}
}
],
"should": [],
"must_not": []
}
}
}
},
"aggs": {
"2": {
"avg": {
"field": "responseTimes"
}
}
}
}
},
"size": 0,
"fields": [
{
"field": "@timestamp",
"format": "date_time"
}
],
"script_fields": {},
"stored_fields": [
"*"
],
"runtime_mappings": {},
"_source": {
"excludes": []
},
"query": {
"bool": {
"must": [],
"filter": [
{
"range": {
"@timestamp": {
"format": "strict_date_optional_time",
"gte": "2022-12-08T08:09:07.023Z",
"lte": "2022-12-09T08:09:07.023Z"
}
}
}
],
"should": [],
"must_not": []
}
}
}
But for some reason this query is taking a lot of time to execute, sometimes 10 mins and sometime 15 mins. The CPU utilization of Elasticsearch goes upto 60-70% when this query is being executed.
We have a single node cluster and for the given time frame that above given query is searching for, we have 151,180,367 logs in elasticsearch in the given index.
Our system specs are:
m5.8xLarge EC2 instance with 32 CPU cores, 128 GB ram and a 7.2 TB gp3 volume attached.
Please also let me know what else details I need to share here.