Slow transform performance

Elastic Stack Elasticsearch
3

Of course. I'm running ES 7.8 in a cluster of 12 nodes (3 masters, 6 hot and 3 cold). All the relevant data is on these hot nodes which have 12 vCPUs each and are backed by SSD storage.

The data is simple logs of player activity and the transform I am performing simply looks for the first and last log (@timestamp.min and @timestamp.max) and associates them with a userId, so we can see when a user first joined and were last active. The transform appears to be running on a cold node which only has 2 vCPUs and is HDD backed but it doesn't appear to be limiting it in any significant way (currently 1% cpu usage).

Here are the stats for the transform.

{
  "count" : 1,
  "transforms" : [
    {
      "id" : "user-total-play-timeframe",
      "state" : "indexing",
      "node" : {
        "id" : "VKyCmhWCQw6EeD2Y2a_Irg",
        "name" : "elasticsearch-data-cold-1",
        "ephemeral_id" : "5OKGvo-MTcGwmRGNcAXVUw",
        "transport_address" : "10.4.6.5:9300",
        "attributes" : { }
      },
      "stats" : {
        "pages_processed" : 4783,
        "documents_processed" : 43789906,
        "documents_indexed" : 2391500,
        "trigger_count" : 6,
        "index_time_in_ms" : 309535,
        "index_total" : 4783,
        "index_failures" : 0,
        "search_time_in_ms" : 78265914,
        "search_total" : 4784,
        "search_failures" : 1,
        "processing_time_in_ms" : 26082,
        "processing_total" : 4783,
        "exponential_avg_checkpoint_duration_ms" : 0.0,
        "exponential_avg_documents_indexed" : 0.0,
        "exponential_avg_documents_processed" : 0.0
      },
      "checkpointing" : {
        "last" : {
          "checkpoint" : 0
        },
        "next" : {
          "checkpoint" : 1,
          "position" : {
            "indexer_position" : {
              "user_id" : "1194832109"
            }
          },
          "checkpoint_progress" : {
            "docs_remaining" : 480120322,
            "total_docs" : 523910228,
            "percent_complete" : 8.358284236436019,
            "docs_indexed" : 2391500,
            "docs_processed" : 43789906
          },
          "timestamp_millis" : 1593695873672
        },
        "operations_behind" : 766723173
      }
    }
  ]
}