Hey, we occasionally seeing

Not enough merge buffer memory to execute this query.

when running GroupBy queries. Below is the example of one of those queries. To address the issue, we followed the suggestion provided in the error message: we added

druid.query.groupBy.maxOnDiskStorage=2gib

on all historicals, broker and middlemanager in addition to increased

druid.processing.buffer.sizeBytes=1gib

. We want to understand the root cause of the issue, and whether we mistuned our cluster or the query is poorly created (with respect to the schema). The query below is using

limitSpec

and we're returning only 5 records, but we're not sure where is that limit applied, on the broker or historical level. What's the recommendation would be when we encounter the error again? We seems cannot increase the buffer size beyond 2GiB of memory, and the only thing is left is to increase the spill on disk size, which affects the performance. Any insights/recommendations will be much appreciated

{
  "queryType": "groupBy",
  "dataSource": {
    "type": "table",
    "name": "user"
  },
  "intervals": {
    "type": "LegacySegmentSpec",
    "intervals": [
      "2024-09-24T23:48:12.948Z/2024-10-01T23:48:12.948Z"
    ]
  },
  "filter": {
    "type": "and",
    "fields": [
      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "nativeIdPaths",
          "value": null
        }
      },
      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "pageId",
          "value": null
        }
      },
      {
        "type": "selector",
        "dimension": "pageIds",
        "value": null
      },
      {
        "type": "selector",
        "dimension": "customerId",
        "value": "<>"
      },
      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "isExcluded",
          "value": "true"
        }
      }
    ]
  },
  "granularity": {
    "type": "all"
  },
  "dimensions": [
    {
      "type": "LegacyDimensionSpec",
      "dimension": "nativeIdPaths",
      "outputName": "nativeIdPaths",
      "outputType": "STRING"
    },
    {
      "type": "LegacyDimensionSpec",
      "dimension": "pageId",
      "outputName": "pageId",
      "outputType": "STRING"
    }
  ],
  "aggregations": [
    {
      "type": "count",
      "name": "count"
    },
    {
      "type": "thetaSketch",
      "name": "userCount",
      "fieldName": "userId",
      "size": 16384
    }
  ],
  "limitSpec": {
    "type": "default",
    "columns": [
      {
        "dimension": "count",
        "direction": "descending",
        "dimensionOrder": {
          "type": "lexicographic"
        }
      }
    ],
    "limit": 5
  }
}

Hi @Viktoryia Shabunia - instead of using

thetaSketch

Can you use a HLL? They will perform better and use much less memory.

{
  "type": "HLLSketchBuild",
  "name": "userCount",
  "fieldName": "userId",
  "lgK": 12,
  "tgtHllType": "HLL_4",
  "round": true
}

Interesting, so the thetaSketch is what causing the large memory footprint? I will talk to our engineers about it. Thank you for the insights

ThetaSketches do use a lot of memory. Normally it’s not a problem, but when you’re doing a high cardinality groupBy that effect is magnified.

Is it related to the high cardinality of userId, right and has nothing to do with the multi-value dimensions like nativeIdPaths and pageId, or it's a compound issue

Although there are not a lot returned, it needs to calculate the distinct count for all groups, which is a very heavy query. If you were only grouping by a single userId dimension, you could use a TopN query, which would run faster.

Got it, this make sense

Correct. The query/bytes is just the size of the data returned. However, both of those are performing pretty well it seems. You can disable caching to get a better measure of the performance, but there will always be slight variability.