Summary of recommendations/changes in Mapping

Following some of the recommendations presented on Monday 25th, here are some stats to help to make decision about how ES performance improves.

https://docs.google.com/document/d/11rdMT-ZoFpOmJY4ND1ujb5kwyiSZCMro7_47QbkiY9U

Region field

Change mapping for “regions” field, getting rid of “nested” type and using a plain “keyword” field (concatenating region_type and region) and then to use “regex” in aggregations.

Data changes

New mapping for regions:

"regions": {"type": "keyword"},
"region_type": {"type": "keyword"},

Data before:

"regions": [
      {
        "uri": "http://linked.data.gov.au/dataset/asgs2016/stateorterritory/5",
        "label": "Western Australia",
        "dataset": {
          "uri": "http://linked.data.gov.au/dataset/asgs2016/stateorterritory",
          "label": "States and territories"
        }
      },
      {
        "uri": "http://linked.data.gov.au/dataset/wwf-terr-ecoregions/14110",
        "label": "Southwest Australia savanna",
        "dataset": {
          "uri": "http://linked.data.gov.au/dataset/wwf-terr-ecoregions",
          "label": "WWF ecoregions"
        }
      },
      {
        "uri": "http://linked.data.gov.au/dataset/local-gov-areas-2011/56790",
        "label": "Northampton (S)",
        "dataset": {
          "uri": "http://linked.data.gov.au/dataset/local-gov-areas-2011",
          "label": "Local government areas"
        }
      },
      {
        "uri": "http://linked.data.gov.au/dataset/nrm-2017/5010",
        "label": "Northern Agricultural Region",
        "dataset": {
          "uri": "http://linked.data.gov.au/dataset/nrm-2017",
          "label": "NRM regions"
        }
      },
      {
        "uri": "http://linked.data.gov.au/dataset/capad-2018-terrestrial/BHA_26",
        "label": "Eurardy",
        "dataset": {
          "uri": "http://linked.data.gov.au/dataset/capad-2018-terrestrial",
          "label": "Terrestrial CAPAD regions"
        }
      },
      {
        "uri": "http://linked.data.gov.au/dataset/bioregion/GES01",
        "label": "Geraldton Hills",
        "dataset": {
          "uri": "http://linked.data.gov.au/dataset/bioregion",
          "label": "Subregions"
        }
      },
      {
        "uri": "http://linked.data.gov.au/dataset/bioregion/GES",
        "label": "Geraldton Sandplains",
        "dataset": {
          "uri": "http://linked.data.gov.au/dataset/bioregion/IBRA7",
          "label": "Bioregions"
        }
      }
],

Data after:

"regions": [
      "http://linked.data.gov.au/dataset/asgs2016/stateorterritory|http://linked.data.gov.au/dataset/asgs2016/stateorterritory/5",
      "http://linked.data.gov.au/dataset/wwf-terr-ecoregions|http://linked.data.gov.au/dataset/wwf-terr-ecoregions/14110",
      "http://linked.data.gov.au/dataset/local-gov-areas-2011|http://linked.data.gov.au/dataset/local-gov-areas-2011/56790",
      "http://linked.data.gov.au/dataset/nrm-2017|http://linked.data.gov.au/dataset/nrm-2017/5010",
      "http://linked.data.gov.au/dataset/capad-2018-terrestrial|http://linked.data.gov.au/dataset/capad-2018-terrestrial/BHA_26",
      "http://linked.data.gov.au/dataset/bioregion|http://linked.data.gov.au/dataset/bioregion/GES01",
      "http://linked.data.gov.au/dataset/bioregion/IBRA7|http://linked.data.gov.au/dataset/bioregion/GES"
],
"region_types": [
      "http://linked.data.gov.au/dataset/asgs2016/stateorterritory",
      "http://linked.data.gov.au/dataset/wwf-terr-ecoregions",
      "http://linked.data.gov.au/dataset/local-gov-areas-2011",
      "http://linked.data.gov.au/dataset/nrm-2017",
      "http://linked.data.gov.au/dataset/capad-2018-terrestrial",
      "http://linked.data.gov.au/dataset/bioregion",
      "http://linked.data.gov.au/dataset/bioregion/IBRA7"
]

Index size stats

Approach	No docs	No docs (incl. hidden docs)	Docs increase
Nested docs	2,563,630	27,284,158	x10.64278
Keyword	2,563,630	10,292,406	x4.014778

Old index is ~1.65 times greater than new index in terms of number of documents

ES Queries

Old query for regions aggregation:

{
    "aggs": {
        "nested_agg": {
            "nested": {
                "path": "regions"
            },
            "aggs": {
                "value": {
                    "terms": {
                        "field": "regions.dataset.uri",
                        "size": 1000
                    }
                }
            }
        }
    },
    "size": 0
}

{
    "aggs": {
        "nested_agg": {
            "nested": {
                "path": "regions"
            },
            "aggs": {
                "filtering": {
                    "filter": {
                        "term": {
                            "regions.dataset.uri": "http://linked.data.gov.au/dataset/asgs2016/stateorterritory"
                        }
                    },
                    "aggs": {
                        "value": {
                            "terms": {
                                "field": "regions.uri",
                                "size": 1000
                            }
                        }
                    }
                }
            }
        }
    },
    "size": 0
}

New queries:

{
    "aggs": {
        "regions": {
            "terms": {
                "field": "region_types"
            }
        }
    },
    "size": 0,
    "track_total_hits": true
}

{
    "aggs": {
        "regions": {
            "terms": {
                "field": "regions",
                "include": "http://linked.data.gov.au/dataset/bioregion\\|.*"
            }
        }
    },
    "size": 0,
    "track_total_hits": true
}

Requests time stats

Summary Excel:

JSONs with results of tests:

POSTMAN queries (collection) → Importable to Postman by anyone.

Not nested regions is slighly faster in most of the executions

Denormalise attributes

The aim of this recommendation is to get rid of nested fields (which create “hiden” Lucene documents) in order to prevent the uncontrolled growth in the size of the index.

Each attribute in the ”nested” field will be modelled as a specific column in the document, instead of nesting them in an “array/list” of documents.

This approach should:

Reduce the final number of documents.
Make aggregations by attribute value simpler and faster?

Index size stats

Approach	No docs	No docs (incl. hidden docs)	Docs increase
Nested docs	2,563,630	27,284,158	x10.64
Denormalised	2,563,630	19,580,736	x7.64

Old index is ~0.39 times greater than new index in terms of number of documents

Data before denormalisation:

{
  "_index": "plotdata_ecoplots-data-observations-ausplots2-aggregate-plant-20211026050100",
  "_type": "_doc",
  "_id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-obs-height-54494348-134833",
  "_score": 1,
  "_ignored": [
    "foi_attributes.value.value.float",
    "foi_attributes.value.value.integer",
    "foi_attributes.value.value.date"
  ],
  "_source": {
    "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-obs-height-54494348",
    ...
    "foi_attributes": [
      {
        "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-attr-transect-54494348",
        "attribute": {
          "label": "transect direction",
          "value": "http://linked.data.gov.au/def/tern-cv/d82a71d7-677a-45fe-968f-6dad4b8e8488"
        },
        "value": {
          "label": null,
          "value": "S1-N1",
          "type": "string"
        },
        "unit_of_measure": {
          "label": null,
          "value": null
        }
      },
      {
        "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-attr-point_number-54494348",
        "attribute": {
          "label": "transect point",
          "value": "http://linked.data.gov.au/def/tern-cv/08035bb8-d972-4110-b3e9-1939bc972ace"
        },
        "value": {
          "label": null,
          "value": "74",
          "type": "string"
        },
        "unit_of_measure": {
          "label": null,
          "value": null
        }
      },
      {
        "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-attr-species_name-54494348",
        "attribute": {
          "label": "scientific name",
          "value": "http://linked.data.gov.au/def/tern-cv/082c84fe-d923-4162-9b73-294b7a8a2dda"
        },
        "value": {
          "label": null,
          "value": "Acacia sibirica",
          "type": "string"
        },
        "unit_of_measure": {
          "label": null,
          "value": null
        }
      }
    ],
    ...
}

Date after denormalisation:

{
  "_index": "plotdata_ecoplots-data_foi",
  "_type": "_doc",
  "_id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-obs-height-54494348-134833",
  "_score": 1,
  "_source": {
    "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-obs-height-54494348",
    ...
    "foi_attr_tern:d82a71d7-677a-45fe-968f-6dad4b8e8488": {
      "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-attr-transect-54494348",
      "value": {
        "label": null,
        "value": "S1-N1",
        "type": "string"
      },
      "unit_of_measure": {
        "label": null,
        "value": null
      }
    },
    "foi_attr_tern:08035bb8-d972-4110-b3e9-1939bc972ace": {
      "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-attr-point_number-54494348",
      "value": {
        "label": null,
        "value": "74",
        "type": "string"
      },
      "unit_of_measure": {
        "label": null,
        "value": null
      }
    },
    "foi_attr_tern:082c84fe-d923-4162-9b73-294b7a8a2dda": {
      "id": "http://linked.data.gov.au/dataset/ausplots/point_intercept-attr-species_name-54494348",
      "value": {
        "label": null,
        "value": "Acacia sibirica",
        "type": "string"
      },
      "unit_of_measure": {
        "label": null,
        "value": null
      }
    },
    "foi_attributes": [
      "http://linked.data.gov.au/def/tern-cv/d82a71d7-677a-45fe-968f-6dad4b8e8488",
      "http://linked.data.gov.au/def/tern-cv/08035bb8-d972-4110-b3e9-1939bc972ace",
      "http://linked.data.gov.au/def/tern-cv/082c84fe-d923-4162-9b73-294b7a8a2dda"
    ]
    ...
  }
}

No nested fields at all

Following above practices (regions and attributes) we have got rid of all nested fields in the mapping (this is, regions, foi_attributes, obs_attributes and instr_attributes).

As it was expected, the number of documents in this new index matches the number of observations.

Total number of fields in alias

Denormalisation means having 1 field (which has sub fields) per attribute.

In order to control the number of fields (as ES has some limitations to mainly avoid “mapping explosion”, as well as internal limits in Lucene), this simple command count the number of fields in an index.

Using wc from mapping

curl -s -XGET https://es-test.tern.org.au/plotdata_ecoplots-data_no_nested/_mapping?pretty --header 'Authorization: Basic TOKEN' | grep type | wc -l

At 1/11/2021 with 1 full dataset ingested, the total number of fields is 261.

Question: Are limits set by index, aren’t they?

For this testing, all original indices have been transformed and merged into only 1 index, but the real data would be: 1 index per FOI and dataset.

Using _field_caps API

curl -s -XGET "https://es-test.tern.org.au/plotdata_ecoplots-data_no_nested/_field_caps?fields=*" | jq '.fields|length'

At 1/11/2021 with 1 full dataset ingested, the total number of fields is 290.

Denormalise regions

...
"region_types": [
  "http://linked.data.gov.au/dataset/local-gov-areas-2011",
  "http://linked.data.gov.au/dataset/nrm-2017",
  "http://linked.data.gov.au/dataset/bioregion/IBRA7",
  "http://linked.data.gov.au/dataset/bioregion",
  "http://linked.data.gov.au/dataset/asgs2016/stateorterritory",
  "http://linked.data.gov.au/dataset/wwf-terr-ecoregions"
],
"region:local-gov-areas-2011": "http://linked.data.gov.au/dataset/local-gov-areas-2011/32250",
"region:nrm-2017": "http://linked.data.gov.au/dataset/nrm-2017/3080",
"region:bioregion/IBRA7": "http://linked.data.gov.au/dataset/bioregion/GUP",
"region:bioregion": "http://linked.data.gov.au/dataset/bioregion/GUP01",
"region:asgs2016/stateorterritory": "http://linked.data.gov.au/dataset/asgs2016/stateorterritory/3",
"region:wwf-terr-ecoregions": "http://linked.data.gov.au/dataset/wwf-terr-ecoregions/12945",
...

ES document mapping:

"region:asgs2016/stateorterritory" : {
  "type" : "keyword"
},
"region:bioregion" : {
  "type" : "keyword"
},
"region:bioregion/IBRA7" : {
  "type" : "keyword"
},
"region:capad-2018-terrestrial" : {
  "type" : "keyword"
},
"region:local-gov-areas-2011" : {
  "type" : "keyword"
},
"region:nrm-2017" : {
  "type" : "keyword"
},
"region:wwf-terr-ecoregions" : {
  "type" : "keyword"
},
"region_types" : {
  "type" : "keyword"
},

Mapping generated dynamically using https://www.elastic.co/guide/en/elasticsearch/reference/current/dynamic-templates.html

"mappings" : {
  "dynamic_templates" : [
    {
      "region_as_keyword" : {
        "match" : "region:*",
        "mapping" : {
          "type" : "keyword"
        }
      }
    },
    ...
  ]
}

Force Merge API

Indices segments are merged after every indexing.

Disable refresh during indexing

Disabling index refresh makes indexing times notably faster (thoughput: ~1000 every two seconds).

1 refresh action is performed manually after indexing. Then the index segments are merged (force-merge).

Dynamic mapping

In order to ensure that the correct datatype is stored in ES for each attribute value, dynamic templating is performed during indexing following the defined rules:

"mappings" : {
  "dynamic_templates" : [
    {
      "region_as_keyword" : {
        "match" : "region:*",
        "mapping" : {
          "type" : "keyword"
        }
      }
    },
    {
      "attribute_field" : {
        "path_match" : "*_attr_*.attribute",
        "mapping" : {
          "type" : "keyword"
        }
      }
    },
    {
      "id_field" : {
        "path_match" : "*_attr_*.id",
        "mapping" : {
          "type" : "keyword"
        }
      }
    },
    {
      "unit_field" : {
        "path_match" : "*_attr_*.unit_of_measure",
        "mapping" : {
          "type" : "keyword"
        }
      }
    },
    {
      "value_label_field" : {
        "path_match" : "*_attr_*.value.label",
        "mapping" : {
          "type" : "text"
        }
      }
    },
    {
      "value_type_field" : {
        "path_match" : "*_attr_*.value.type",
        "mapping" : {
          "type" : "keyword"
        }
      }
    },
    {
      "value_value_field" : {
        "path_match" : "*_attr_*.value.value_float",
        "mapping" : {
          "coerce" : true,
          "doc_values" : true,
          "ignore_malformed" : true,
          "type" : "float"
        }
      }
    },
    {
      "value_value_field" : {
        "path_match" : "*_attr_*.value.value_int",
        "mapping" : {
          "coerce" : true,
          "doc_values" : true,
          "ignore_malformed" : true,
          "type" : "integer"
        }
      }
    },
    {
      "value_value_field" : {
        "path_match" : "*_attr_*.value.value_bool",
        "mapping" : {
          "normalizer" : "lowercase_normalizer",
          "type" : "keyword"
        }
      }
    },
    {
      "value_value_field" : {
        "path_match" : "*_attr_*.value.value_datetime",
        "mapping" : {
          "format" : "yyyy-MM-dd'T'HH:mm:ss'Z'||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||d/MM/yyyy||epoch_millis",
          "ignore_malformed" : "true",
          "type" : "date"
        }
      }
    },
    {
      "value_value_field" : {
        "path_match" : "*_attr_*.value.value_date",
        "mapping" : {
          "format" : "yyyy-MM-dd'T'HH:mm:ss'Z'||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||d/MM/yyyy||epoch_millis",
          "ignore_malformed" : "true",
          "type" : "date"
        }
      }
    },
    {
      "value_value_field" : {
        "path_match" : "*_attr_*.value.value_uri",
        "mapping" : {
          "type" : "keyword"
        }
      }
    },
    {
      "value_value_field" : {
        "path_match" : "*_attr_*.value.value_string",
        "mapping" : {
          "type" : "keyword"
        }
      }
    }
  ],
  ...
}

Clear cache API | Elasticsearch Guide [7.10] | Elastic

Clear cache after each test query to really test performance improvement!!!