Inconsistent Cosmos DB graph RU cost for the same query

Question

We're using a CosmosDB Graph API instance provisioned with 120K RUs. We've setup a consistent partitioning structure using the /partition_key property.

When querying our graph using Gremlin, we've noticed that some queries use an unreasonably high amount of RUs when compared to other queries. The queries themselves are the same, but for the partition_key value itself.

The following query costs 23.25 RUs, for example:

g.V().has('partition_key', 'xxx')

Whereas the same query with a different partition_key value costs 4.14 RUs:

g.V().has('partition_key', 'yyy')

Looking at the .exectionProfile() results for both queries; they look similar.

The expensive query which costs 23.25 RUs (xxx):

[
  {
    "gremlin": "g.V().has('partition_key', 'xxx').executionProfile()",
    "activityId": "ec181c9d-59a1-4849-9c08-111d6b465b88",
    "totalTime": 12,
    "totalResourceUsage": 19.8,
    "metrics": [
      {
        "name": "GetVertices",
        "time": 12.324,
        "stepResourceUsage": 19.8,
        "annotations": {
          "percentTime": 98.78,
          "percentResourceUsage": 100
        },
        "counts": {
          "resultCount": 1
        },
        "storeOps": [
          {
            "fanoutFactor": 1,
            "count": 1,
            "size": 848,
            "storageCount": 1,
            "storageSize": 791,
            "time": 12.02,
            "storeResourceUsage": 19.8
          }
        ]
      },
      {
        "name": "ProjectOperator",
        "time": 0.15259999999999962,
        "stepResourceUsage": 0,
        "annotations": {
          "percentTime": 1.22,
          "percentResourceUsage": 0
        },
        "counts": {
          "resultCount": 1
        }
      }
    ]
  }
]

The cheap query which costs 4.14 RUs (yyy):

[
  {
    "gremlin": "g.V().has('partition_key', 'yyy').executionProfile()",
    "activityId": "841e1c37-471c-461e-b784-b53893a3c349",
    "totalTime": 6,
    "totalResourceUsage": 3.08,
    "metrics": [
      {
        "name": "GetVertices",
        "time": 5.7595,
        "stepResourceUsage": 3.08,
        "annotations": {
          "percentTime": 98.71,
          "percentResourceUsage": 100
        },
        "counts": {
          "resultCount": 1
        },
        "storeOps": [
          {
            "fanoutFactor": 1,
            "count": 1,
            "size": 862,
            "storageCount": 1,
            "storageSize": 805,
            "time": 5.4,
            "storeResourceUsage": 3.08
          }
        ]
      },
      {
        "name": "ProjectOperator",
        "time": 0.07500000000000018,
        "stepResourceUsage": 0,
        "annotations": {
          "percentTime": 1.29,
          "percentResourceUsage": 0
        },
        "counts": {
          "resultCount": 1
        }
      }
    ]
  }
]

The results both queries return a single vertex of about the same size.

Can someone please help explain why this is so? And why one is significantly more expensive than the other? Is there some aspect that I don't understand about Cosmos DB partitioning?

Edit 1: We also did some experimentation by adding other query parameters, such as id and also label. An id clause did indeed reduce the cost of the expensive query from ~23 RUs to ~4.57 RUs. The problem with this approach is that in general it makes all other queries less efficient (i.e. it increases RUs).

For example, other queries (like the fast one in this ticket) go from ~4.14 RUs to ~4.80 RUs, with the addition of an id clause. So that's not really feasible as 99% of queries would be worse off. We need to find the root cause.

Edit 2: Queries are run on the same container using the Data Explorer tool in Azure Portal. Here's the partition distribution graph:

user_965841 · Answer

The "issue" you're describing can be related to the size boundaries of physical partitions (PP) and logical partition (LP). Cosmos DB allows "infinite" scaling based on its partitioning architecture. But scaling performance and data growth highly depends on logical partition strategy. Microsoft highly recommend to have as granular LP key as possible so data will be equally distributed across PPs.

Initially when creating container for 120k RUs you will end-up with 12 PP - 10k RUs limit per physical partition. Once you start loading data it is possible to end-up with more PP. Following scenarios might lead to "split":

size of the LP (total size of data per your partition key) is larger than 20GB
size of PP (total sum of all LP sizes stored in this PP) is larger than 50GB

As per documentation

A physical partition split simply creates a new mapping of logical partitions to physical partitions.

Based on the PP storage allocation it looks like you had multiple "splits" resulting in provisioning of ~20 PPs. Every time "split" occurs Cosmos DB creates two new PPs and split existing PP equally between newly created. Once this process is finished "skewed" PP is deleted. You can roughly guess number of splits by PP id's on the Metrics chart you provided (you would have id: [1-12] if no splits happened).

Splits potentially can result in higher RU consumption due to request fan-out and cross-partition queries.

Inconsistent Cosmos DB graph RU cost for the same query

1 Answers