Hints to improve performance for numerous agregations with high cardinalities


(Ivan Monnier) #1

Searching with numerous agregations, each with high cardinalities, can be very slow (10-15s).
Kibana dashboards involving those same aggregations are slow too (over 10s), but they contain multiple visualizations.
Could you help us by providing some hints to improve the situation ?

I know that this is a very old debated problem. All the posts I could find are old. We are using Elasticsearch 6.2 and I think that an update would be useful.

Field cache seems to be involved since the first query is far longer (10-20s) that the following ones (with then seem to be randomly long 7s to short 3s).
We tried different methods to warm up the index with not much success.

We are editors for an annotator performing names entities recognition.
These named entities are transformed in fields.
These fields provides numerous aggregations/facets, each with high cardinalities.

Here is the JSON query asking for facets:

{"from":0,"size":50,"query":{"query_string":{"query":"( tout:(internet) )","default_field":"tout","fields":[],"type":"best_fields","default_operator":"and","max_determinized_states":10000,"enable_position_increments":true,"fuzziness":"AUTO","fuzzy_prefix_length":0,"fuzzy_max_expansions":50,"phrase_slop":0,"escape":false,"auto_generate_synonyms_phrase_query":true,"fuzzy_transpositions":true,"boost":1.0}},"explain":true,"stored_fields":["Site_url","Site_nom","ETAT","Article_titre","Article_date_edition","Article_description","Article_url","DATE_COLLECT","ID_QES","BASE_ID","LOGIN_USER_COLLECT","Article_langue"],"sort":[{"DATE_COLLECT":{"order":"desc"}}],"aggregations":{"Site_nom.verbatim":{"terms":{"field":"Site_nom.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"Article_langue.verbatim":{"terms":{"field":"Article_langue.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"Article_categories.verbatim":{"terms":{"field":"Article_categories.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"DATE_COLLECT":{"date_histogram":{"field":"DATE_COLLECT","interval":"1d","offset":0,"order":{"_key":"asc"},"keyed":false,"min_doc_count":0}},"QES_Person.verbatim":{"terms":{"field":"QES_Person.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Persontitre.verbatim":{"terms":{"field":"QES_Persontitre.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Company.verbatim":{"terms":{"field":"QES_Company.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Organization.verbatim":{"terms":{"field":"QES_Organization.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Organonoff.verbatim":{"terms":{"field":"QES_Organonoff.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Location.verbatim":{"terms":{"field":"QES_Location.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Unlocalized.verbatim":{"terms":{"field":"QES_Unlocalized.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Event.verbatim":{"terms":{"field":"QES_Event.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_ConceptCategorized.verbatim":{"terms":{"field":"QES_ConceptCategorized.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Coldconcept.verbatim":{"terms":{"field":"QES_Coldconcept.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Country.verbatim":{"terms":{"field":"QES_Country.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Region.verbatim":{"terms":{"field":"QES_Region.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_City.verbatim":{"terms":{"field":"QES_City.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}},"QES_Product.verbatim":{"terms":{"field":"QES_Product.verbatim","size":20,"min_doc_count":1,"shard_min_doc_count":0,"show_term_doc_count_error":false,"order":[{"_count":"desc"},{"_key":"asc"}]}}}}

As you can see they are numerous.

Here is our configuration:

  • 56 Millions documents
  • 3 indexes 2016, 2017, 2018, each with 10 shards,
  • on 5 nodes.

Each nodes is :

  • Physical machines DELL R430
  • Linux Debian Stretch
  • Elasticsearch 6.2
  • 12 CPU/24 threads,
  • 64 GB RAM
  • 4 x 2TB SATA disk in RAID5
  • JVM with -Xmx=32 GB

I know that going to SSD disk and adding more nodes would improve things but I think that there must be some other tracks to follow before that.

Could you help us on this ?


(David Pilato) #2

@jimczi or @jpountz do you think you could help?