Engine really slow after indexing ~50 Million documents

ammarhaider · November 25, 2021, 2:14pm

After indexing 50M documents, I am experiencing really slow search response. And when I query with some facet fields I get internal server error after waiting for 30+ seconds.

I increased the memory allocated to appsearch and enterprise search:
Following are the memory settings of Elasticsearch and enterprise search respectively:
ES_JAVA_OPTS=-Xms30g -Xmx30g
JAVA_OPTS=-Xms30g -Xmx30g

Other thing I tried was to make only some fields searchable from the appsearch dashboard (localhost:3002) that didn't work either.

http://localhost:3002/api/as/v1/engines/propertyeng/search

Request:

{
    "query": "",
    "facets": {
        "property_type": {
            "type": "value",
            "size": 100
        },
        "score": {
            "type": "value",
            "size": 100
        },
        "year_built": {
            "type": "value"
        },
        "beds": {
            "type": "value"
        },
        "bath": {
            "type": "value"
        },
        "units_count": {
            "type": "value"
        },
        "sale_price": {
            "type": "value"
        },
        "sale_value": {
            "type": "value"
        },
        "valuation_value": {
            "type": "value"
        },
        "est_equity": {
            "type": "value"
        },
        "loan_amount": {
            "type": "value"
        },
        "loan_type": {
            "type": "value"
        },
        "land_value": {
            "type": "value"
        },
        "open_mortgages": {
            "type": "value"
        },
        "owner_city": {
            "type": "value"
        },
        "est_ownership_length": {
            "type": "value"
        },
        "seller_state": {
            "type": "value"
        },
        "section_township_range": {
            "type": "value"
        },
        "sale_date": {
            "type": "value"
        },
        "conditions": {
            "type": "value"
        },
        "roof_material_type": {
            "type": "value"
        },
        "amenities": {
            "type": "value"
        },
        "sqft_land": {
            "type": "value"
        },
        "total_mortgage_balance": {
            "type": "value"
        },
        "sqft_building": {
            "type": "value"
        },
        "liens": {
            "type": "value"
        },
        "bankruptcy": {
            "type": "value"
        },
        "preforeclosure": {
            "type": "value"
        },
        "absentee": {
            "type": "value"
        },
        "judgement": {
            "type": "value"
        },
        "vacant": {
            "type": "value"
        },
        "inherited": {
            "type": "value"
        }
    },
    "page": {
        "size": 20,
        "current": 1
    }
}

Response:

{
    "errors": [
        "Internal server error. Please check your application server logs for more details and contact Elastic support if the problem persists"
    ]
}

oleksiy-elastic · November 25, 2021, 3:56pm

Hello, Ammar,

Sorry you're having issues with your App Search deployment.

I'm not sure about the internal server error since there is not enough information to troubleshoot that, so I'll focus on the performance question.

Here is what happens when you run a query like the one listed above:

Elasticsearch needs to scan ALL documents (since the query string is empty, it has to run a match_all query)
While scanning the whole document corpus, it has to build complex data structures to generate facets for all the fields you have listed in your query.

With a 50 million documents in the corpus, there is no way the query could be performed quickly.

Here are a few suggestions I can provide:

Remove facets

Reduce the number of facets, especially fields with high-cardinality (I'd recommend running the empty query with a single facet and benchmarking the results, then checking the next facet and so on until you understand which ones cause the slowness).
A single high cardinality field could account for the majority of a slow query.
- I see a few price/value/etc fields - what do you expect to happen when you facet on those? (prices tend to be pretty unique, so the value of the facet would not be particularly useful as far as I understand, but would be very hard to compute).

Reduce limits

Reduce the limits on the number of values returned for each facet, especially high-cardinality fields.

Split empty queries

If you cannot avoid running empty queries, try splitting those into two: one to fetch facets while limiting the number of search results to zero, and one to fetch the docs, without facets. This often leads to faster results by taking advantage of parallelization and aggregations caching.

Sizing

Bigger queries need bigger resources! You may need a larger deployment (in particular CPU for the underlying Elasticsearch nodes. This could include selecting a CPU-optimized deployment on Elastic Cloud or increasing node size/count).

I hope this helps.

ammarhaider · December 1, 2021, 10:35am

Thanks for your thorough analysis on my problem. I have removed all the facets and it is surely faster but my corpus is expected to go beyond 150M and I want a permanent robust solution.
What do you suggest? is it possible to optimize appsearch at this scale or should we go for Elasticsearch.
What should be the minimum server specifications to smoothly run appsearch for such huge corpus?

below is the docker-compose we are using for deployment.

version: '2.2'

services:
  es01:
    container_name: es01
    image: docker.elastic.co/elasticsearch/elasticsearch:7.15.0
    environment:
      - node.name=es01
      - discovery.seed_hosts=es01,es02
      - cluster.initial_master_nodes=es01,es02
      - ELASTIC_PASSWORD=$ELASTIC_PASSWORD 
      - "ES_JAVA_OPTS=-Xms8g -Xmx8g"
      - xpack.security.enabled=true
      - xpack.security.http.ssl.enabled=true
      - xpack.security.http.ssl.key=$CERTS_DIR/es01/es01.key
      - xpack.security.http.ssl.certificate_authorities=$CERTS_DIR/ca/ca.crt
      - xpack.security.http.ssl.certificate=$CERTS_DIR/es01/es01.crt
      - xpack.security.transport.ssl.enabled=true
      - xpack.security.transport.ssl.verification_mode=certificate 
      - xpack.security.transport.ssl.certificate_authorities=$CERTS_DIR/ca/ca.crt
      - xpack.security.transport.ssl.certificate=$CERTS_DIR/es01/es01.crt
      - xpack.security.transport.ssl.key=$CERTS_DIR/es01/es01.key
    volumes: ['data01:/usr/share/elasticsearch/data', 'certs:$CERTS_DIR']
    ports:
      - 9200:9200
    healthcheck:
      test: curl --cacert $CERTS_DIR/ca/ca.crt -s https://localhost:9200 >/dev/null; if [[ $$? == 52 ]]; then echo 0; else echo 1; fi
      interval: 30s
      timeout: 10s
      retries: 5
    
    networks:
      - elastic

  es02:
    container_name: es02
    image: docker.elastic.co/elasticsearch/elasticsearch:7.15.0
    environment:
      - node.name=es02
      - discovery.seed_hosts=es01,es02
      - cluster.initial_master_nodes=es01,es02
      - ELASTIC_PASSWORD=$ELASTIC_PASSWORD
      - "ES_JAVA_OPTS=-Xms8g -Xmx8g"
      - xpack.security.enabled=true
      - xpack.security.http.ssl.enabled=true
      - xpack.security.http.ssl.key=$CERTS_DIR/es02/es02.key
      - xpack.security.http.ssl.certificate_authorities=$CERTS_DIR/ca/ca.crt
      - xpack.security.http.ssl.certificate=$CERTS_DIR/es02/es02.crt
      - xpack.security.transport.ssl.enabled=true
      - xpack.security.transport.ssl.verification_mode=certificate 
      - xpack.security.transport.ssl.certificate_authorities=$CERTS_DIR/ca/ca.crt
      - xpack.security.transport.ssl.certificate=$CERTS_DIR/es02/es02.crt
      - xpack.security.transport.ssl.key=$CERTS_DIR/es02/es02.key
    volumes: ['data02:/usr/share/elasticsearch/data', 'certs:$CERTS_DIR']
    networks:
      - elastic

  wait_until_ready:
    image: docker.elastic.co/elasticsearch/elasticsearch:7.15.0
    command: /usr/bin/true
    depends_on: {"es01": {"condition": "service_healthy"}}
  
  ent-search:
    image: docker.elastic.co/enterprise-search/enterprise-search:7.15.0
    restart: unless-stopped
    depends_on:
      - "es01"
    environment:
      - "JAVA_OPTS=-Xms8g -Xmx8g"
      - "ENT_SEARCH_DEFAULT_PASSWORD="
      - "elasticsearch.username=elastic"
      - "elasticsearch.password="
      - "elasticsearch.host=https://es01:9200"
      - "elasticsearch.ssl.enabled=true"
      - "elasticsearch.ssl.certificate=$CERTS_DIR/ent-search/ent-search.crt"
      - "elasticsearch.ssl.certificate_authority=$CERTS_DIR/ca/ca.crt"
      - "elasticsearch.ssl.key=$CERTS_DIR/ent-search/ent-search.key"
      - "allow_es_settings_modification=true"
      - "secret_management.encryption_keys=[]"
      - "elasticsearch.startup_retry.interval=15"
      - "app_search.engine.total_fields.limit=256"
    ports:
      - 127.0.0.1:3002:3002
    volumes: ['ent-search:/usr/share/elasticsearch/data', 'certs:$CERTS_DIR']
    networks:
      - elastic

volumes: {"data01", "data02", "certs", "ent-search"}

networks:
  elastic:
    driver: bridge