Elastic query is not fast and response time is high

Cluster setup:
I am running Elasticsearch cluster (2 master, 2 data and 1 voter) as docker containers.
Kibana setup:
I also have Kibana configured to connect to the elastic cluster.
Frontend:
I have an angular web application that has a search bar using which I can search for accounts etc and show the results in UI.
Backend:
My backend is a NodeJS REST api that connects to elastic cluster. I use @elastic/Elasticsearch NPM package to query Elasticsearch indices.
Issue:
When I search for an account by typing the account number or account name in the frontend, i make a call to the nodejs REST api in which I am using @elastic/Elasticsearch package to query for the search text or keyword across all the indices (accounts, groups, relationships) and return the results to the UI. My search query’s are lot slower and the response time is around 500 to 800 ms whereas I expect it to be less than 100 ms.

Is there a way to profile or triage this performance issue?

What are those expectations built on?

What does the query look like?
What is the mapping for them?
What are the node specs?
What is the output from the _cluster/stats?pretty&human API?

You can also capture the query leaving your REST API and go to Kibana - Dev Tools -> Profiler and analyze the query.

  1. Below is the output of search query:
{
    "total": {
      "value": 1576,
      "relation": "eq"
    },
    "took": 3048,
    "hits": [],
    "maxScore": null,
    "shards": {
      "total": 7,
      "successful": 7,
      "skipped": 0,
      "failed": 0
    },
    "facets": {
      "relationships": {
        "meta": {},
        "doc_count": 7,
        "relationships": {
          "hits": {
            "total": {
              "value": 7,
              "relation": "eq"
            },
            "max_score": 1,
            "hits": [
              {
                "_index": "relationships",
                "_type": "_doc",
                "_id": "MYDATA-1",
                "_score": 1,
                "_source": {
                  "relId": "MYDATA-1",
                  "relName": "TRENTON dummy data 1"
                },
                "highlight": {
                  "relName": [
                    "<em>TRENTON</em> dummy data 1"
                  ]
                }
              },
              {
                "_index": "relationships",
                "_type": "_doc",
                "_id": "MYDATA-2",
                "_score": 1,
                "_source": {
                  "reId": "MYDATA-2",
                  "relName": "TRENTON dummy data 2"
                },
                "highlight": {
                  "relName": [
                    "<em>TRENTON</em> dummy data 2"
                  ]
                }
              }
            ]
          }
        }
      },
      "accounts": {
        "meta": {},
        "doc_count": 242,
        "accounts": {
          "hits": {
            "total": {
              "value": 242,
              "relation": "eq"
            },
            "max_score": 1,
            "hits": [
              {
                "_index": "accounts",
                "_type": "_doc",
                "_id": "MY-ACC-DATA-1",
                "_score": 1,
                "_source": {
                  "accId": "MYDATA-1",
                  "accName": "TRENTON dummy data 1"
                },
                "highlight": {
                  "accName": [
                    "<em>TRENTON</em> dummy data 1"
                  ]
                }
              },
              {
                "_index": "accounts",
                "_type": "_doc",
                "_id": "MY-ACC-DATA-2",
                "_score": 1,
                "_source": {
                  "accId": "MYDATA-2",
                  "accName": "TRENTON dummy data 2"
                },
                "highlight": {
                  "accName": [
                    "<em>TRENTON</em> dummy data 2"
                  ]
                }
              }
            ]
          }
        }
      },
      "groups": {
        "meta": {},
        "doc_count": 0,
        "groups": {
          "hits": {
            "total": {
              "value": 0,
              "relation": "eq"
            },
            "max_score": null,
            "hits": []
          }
        }
      },
      "index": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "accounts",
            "doc_count": 242
          },
          {
            "key": "relationships",
            "doc_count": 7
          },
          {
            "key": "grops",
            "doc_count": 2
          }
        ]
      }
    },
    "totalMatches": 1576,
    "dynamicQuery": {
      "bool": {
        "must": {
          "query_string": {
            "query": "*trenton*",
            "fields": [
              "*"
            ]
          }
        }
      }
    },
    "totalTime": 3263
}
  1. Below is the mapping
accounts: {
  "mappings": {
    "_doc": {
      "properties": {
        "acct_title_txt": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "accId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "accName": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      }
    }
  }
}
relationships:
{
  "mappings": {
    "_doc": {
      "properties": {
        "relId": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "relName": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      }
    }
  }
}
  1. Below is the output of cluster stats:
{
  "_nodes": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "cluster_name": "es_cluster",
  "cluster_uuid": "random_hash",
  "timestamp": 1651710673241,
  "status": "green",
  "indices": {
    "count": 80,
    "shards": {
      "total": 160,
      "primaries": 80,
      "replication": 1.0,
      "index": {
        "shards": {
          "min": 2,
          "max": 2,
          "avg": 2.0
        },
        "primaries": {
          "min": 1,
          "max": 1,
          "avg": 1.0
        },
        "replication": {
          "min": 1.0,
          "max": 1.0,
          "avg": 1.0
        }
      }
    },
    "docs": {
      "count": 1971168,
      "deleted": 1197406
    },
    "store": {
      "size": "3.7gb",
      "size_in_bytes": 3987857060,
      "total_data_set_size": "3.7gb",
      "total_data_set_size_in_bytes": 3987857060,
      "reserved": "0b",
      "reserved_in_bytes": 0
    },
    "fielddata": {
      "memory_size": "63.5kb",
      "memory_size_in_bytes": 65032,
      "evictions": 0
    },
    "query_cache": {
      "memory_size": "20.3mb",
      "memory_size_in_bytes": 21310193,
      "total_count": 63035347,
      "hit_count": 2817397,
      "miss_count": 60217950,
      "cache_size": 7297,
      "cache_count": 127506,
      "evictions": 120209
    },
    "completion": {
      "size": "0b",
      "size_in_bytes": 0
    },
    "segments": {
      "count": 454,
      "memory": "8.3mb",
      "memory_in_bytes": 8708600,
      "terms_memory": "5mb",
      "terms_memory_in_bytes": 5334424,
      "stored_fields_memory": "226.8kb",
      "stored_fields_memory_in_bytes": 232304,
      "term_vectors_memory": "0b",
      "term_vectors_memory_in_bytes": 0,
      "norms_memory": "553.6kb",
      "norms_memory_in_bytes": 566912,
      "points_memory": "0b",
      "points_memory_in_bytes": 0,
      "doc_values_memory": "2.4mb",
      "doc_values_memory_in_bytes": 2574960,
      "index_writer_memory": "13.2mb",
      "index_writer_memory_in_bytes": 13876872,
      "version_map_memory": "0b",
      "version_map_memory_in_bytes": 0,
      "fixed_bit_set": "107.8kb",
      "fixed_bit_set_memory_in_bytes": 110400,
      "max_unsafe_auto_id_timestamp": 1651708803088,
      "file_sizes": {}
    },
    "mappings": {
      "field_types": [
        {
          "name": "binary",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "boolean",
          "count": 480,
          "index_count": 36,
          "script_count": 0
        },
        {
          "name": "byte",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "constant_keyword",
          "count": 30,
          "index_count": 10,
          "script_count": 0
        },
        {
          "name": "date",
          "count": 219,
          "index_count": 56,
          "script_count": 0
        },
        {
          "name": "date_nanos",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "date_range",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "double",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "double_range",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "float",
          "count": 466,
          "index_count": 32,
          "script_count": 0
        },
        {
          "name": "float_range",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "geo_point",
          "count": 2,
          "index_count": 2,
          "script_count": 0
        },
        {
          "name": "geo_shape",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "half_float",
          "count": 13,
          "index_count": 2,
          "script_count": 0
        },
        {
          "name": "integer",
          "count": 5,
          "index_count": 2,
          "script_count": 0
        },
        {
          "name": "integer_range",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "ip",
          "count": 11,
          "index_count": 11,
          "script_count": 0
        },
        {
          "name": "ip_range",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "keyword",
          "count": 2923,
          "index_count": 65,
          "script_count": 0
        },
        {
          "name": "long",
          "count": 2001,
          "index_count": 46,
          "script_count": 0
        },
        {
          "name": "long_range",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "nested",
          "count": 8,
          "index_count": 8,
          "script_count": 0
        },
        {
          "name": "object",
          "count": 1283,
          "index_count": 56,
          "script_count": 0
        },
        {
          "name": "shape",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "short",
          "count": 1,
          "index_count": 1,
          "script_count": 0
        },
        {
          "name": "text",
          "count": 2439,
          "index_count": 64,
          "script_count": 0
        },
        {
          "name": "version",
          "count": 8,
          "index_count": 8,
          "script_count": 0
        }
      ],
      "runtime_field_types": []
    },
    "analysis": {
      "char_filter_types": [],
      "tokenizer_types": [],
      "filter_types": [],
      "analyzer_types": [],
      "built_in_char_filters": [],
      "built_in_tokenizers": [],
      "built_in_filters": [],
      "built_in_analyzers": [
        {
          "name": "english",
          "count": 1,
          "index_count": 1
        }
      ]
    },
    "versions": [
      {
        "version": "7.15.0",
        "index_count": 32,
        "primary_shard_count": 32,
        "total_primary_size": "868.7mb",
        "total_primary_bytes": 910995300
      },
      {
        "version": "7.16.2",
        "index_count": 48,
        "primary_shard_count": 48,
        "total_primary_size": "990.8mb",
        "total_primary_bytes": 1038932015
      }
    ]
  },
  "nodes": {
    "count": {
      "total": 5,
      "coordinating_only": 0,
      "data": 3,
      "data_cold": 0,
      "data_content": 0,
      "data_frozen": 0,
      "data_hot": 0,
      "data_warm": 0,
      "ingest": 3,
      "master": 3,
      "ml": 0,
      "remote_cluster_client": 5,
      "transform": 0,
      "voting_only": 1
    },
    "versions": [
      "7.16.2"
    ],
    "os": {
      "available_processors": 5,
      "allocated_processors": 5,
      "names": [
        {
          "name": "Linux",
          "count": 5
        }
      ],
      "pretty_names": [
        {
          "pretty_name": "Red Hat Enterprise Linux Server 7.9 (Maipo)",
          "count": 5
        }
      ],
      "architectures": [
        {
          "arch": "amd64",
          "count": 5
        }
      ],
      "mem": {
        "total": "36.6gb",
        "total_in_bytes": 39321600000,
        "free": "14.2gb",
        "free_in_bytes": 15257305088,
        "used": "22.4gb",
        "used_in_bytes": 24064294912,
        "free_percent": 39,
        "used_percent": 61
      }
    },
    "process": {
      "cpu": {
        "percent": 52
      },
      "open_file_descriptors": {
        "min": 448,
        "max": 766,
        "avg": 616
      }
    },
    "jvm": {
      "max_uptime": "14.7d",
      "max_uptime_in_millis": 1276502845,
      "versions": [
        {
          "version": "11.0.9",
          "vm_name": "Java HotSpot(TM) 64-Bit Server VM",
          "vm_version": "11.0.9+7-LTS",
          "vm_vendor": "Oracle Corporation",
          "bundled_jdk": true,
          "using_bundled_jdk": false,
          "count": 5
        }
      ],
      "mem": {
        "heap_used": "7.3gb",
        "heap_used_in_bytes": 7862177080,
        "heap_max": "18.2gb",
        "heap_max_in_bytes": 19617218560
      },
      "threads": 246
    },
    "fs": {
      "total": "124.9gb",
      "total_in_bytes": 134131240960,
      "free": "119.7gb",
      "free_in_bytes": 128550281216,
      "available": "119.7gb",
      "available_in_bytes": 128550281216
    },
    "plugins": [],
    "network_types": {
      "transport_types": {
        "security4": 5
      },
      "http_types": {
        "security4": 5
      }
    },
    "discovery_types": {
      "zen": 5
    },
    "packaging_types": [
      {
        "flavor": "default",
        "type": "tar",
        "count": 5
      }
    ],
    "ingest": {
      "number_of_pipelines": 19,
      "processor_stats": {
        "conditional": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "convert": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "geoip": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "grok": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "gsub": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "remove": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "rename": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "script": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "set": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        },
        "set_security_user": {
          "count": 0,
          "failed": 0,
          "current": 0,
          "time": "0s",
          "time_in_millis": 0
        }
      }
    }
  }
}
  1. Node specs below:
    All 5 nodes have the same specs which is 7500MB RAM/0.7 core CPU

I did that. We have aliases loaded with data and we are querying the aliases. The performance on first execution is around 3 seconds and subsequent executions are around 500 ms to 800 ms

@warkolm/@stephenb - Were you able to review the responses from my cluster?

check all node are working could be reply the index any time; can check I/O Disk working , review nginx service if you using that to make reverse proxy

It looks like you are running a wildcard string query with leading wildcard for a lot of fields, which on a text or keyword type is among the most inefficient queries you can run as a lot of terms need to be checked. Think of it as the equivalent to a full table scan in a relational data base. I suspect you would see much better performance if you mapped your data as a wildcard type.

You also seem to have a lot of small shards which is very inefficient. The amount of data you have would easily fit in a single shard, so I would recommend changing this.

This sounds quite low, especially considering the number of shards you have. I would recommend increasing this and see if it makes a difference.

Thank you @Christian_Dahlqvist… Let me work on the changes suggested by you and get back on this same thread.