0

We have a collection of data which records the activities performed over a range of time period. the fields of importance are userid and created_at

many of the users are pretty important in our system in terms of their usage. they may perform a lot of activities and live in the system long enough (more than a few years). This means every user would've performed an average of 500 k activities while the remaining few users don't do much activity and their average number of activities is in the range of a few 100s

Now our goal is to achieve the below with respect to sharding the collection and distribute the data in such a way

  1. where all the shards are utilized for each and every user's activity data. That is, write distribution should be as uniform as possible
  2. where we are able to query the last N number of activities a given user has performed and achieve "read locality" by limiting the query execution to 1 or a few more shards

The shard key combinations we have are below

  1. userid - this will be hashed and routed to exactly one shard causing that shard to grow large
  2. created_at - this will be of range-sharding type but the query performance won't be great since we receive many activities throughout the day and year. So, having it as the left-most prefix doesn't narrow the keys/docs examined while doing a find on this collection
  3. user_id (hashed) and created_at (ranged) - this looks ideal to me where we let MongoDB to collocate documents created by a user in continuous range under a common shard but for some reason, all the documents for a given userid end up in only one shard. this is as good as the first shard key (of userid alone) mentioned above

what would be a better choice of shard key to route queries on a userid to fetch the last N number of activities to one (or a few) shard(s) while achieving most uniform write distribution?

I'm attaching the output of a small proof-of-code where we infer that the composite shard key #3 mentioned above is used to shard the collection consisting of about 10 k docs each having the userid but different created_at timestamps spanning for more than a year

The shard distribution is 0% vs 100% where shard 1 receives 0 docs while the shard 2 receives all the docs. I am expecting a ratio of at least 40% vs 60% if not 50%-50%. The blog on-selecting-a-shard-key-for-mongodb mentions it's possible to achieve a better read locality with this composite shard key combination but it doesn't work for me

[direct: mongos] > db.version()
5.0.13
[direct: mongos] > db.createCollection("CompoundHashedShardKeyTest");
{ ok: 1 }
[direct: mongos] > sh.shardCollection("ns.CompoundHashedShardKeyTest", {"userid":"hashed","created_at":1});
{
  collectionsharded: 'ns.CompoundHashedShardKeyTest',
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: xxx, i: 34 }),
    signature: {
      hash: Binary(Buffer.from("yyy", "hex"), 0),
      keyId: Long("zzz")
    }
  },
  operationTime: Timestamp({ t: xxx, i: 30 })
}
[direct: mongos] > db.CompoundHashedShardKeyTest.getIndexes();
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    key: { userid: 'hashed', created_at: 1 },
    name: 'userid_hashed_created_at_1'
  }
]
[direct: mongos] > sh.status()
shardingVersion
{
  _id: 1,
  minCompatibleVersion: 5,
  currentVersion: 6,
  clusterId: ObjectId("63522b61222fa91d4e09d19a")
}
---
shards
[
  {
    _id: 'mongos-01-shard-01',
    host: 'mongos-01-shard-01/mongos-01-shard-01-01.company.net:27017,mongos-01-shard-01-02.company.net:27017',
    state: 1,
    topologyTime: Timestamp({ t: xxx, i: 2 })
  },
  {
    _id: 'mongos-01-shard-02',
    host: 'mongos-01-shard-02/mongos-01-shard-02-01.company.net:27017,mongos-01-shard-02-02.company.net:27017',
    state: 1,
    topologyTime: Timestamp({ t: xxx, i: 2 })
  }
]
---
active mongoses
[ { '5.0.13': 3 } ]
---
autosplit
{ 'Currently enabled': 'yes' }
---
balancer
{
  'Currently enabled': 'yes',
  'Currently running': 'no',
  'Failed balancer rounds in last 5 attempts': 0,
  'Migration Results for the last 24 hours': 'No recent migrations'
}
---
databases
[
  {
    database: { _id: 'config', primary: 'config', partitioned: true },
    collections: {
      'config.system.sessions': {
        shardKey: { _id: 1 },
        unique: false,
        balancing: true,
        chunkMetadata: [
          { shard: 'mongos-01-shard-01', nChunks: 512 },
          { shard: 'mongos-01-shard-02', nChunks: 512 }
        ],
        chunks: [
          'too many chunks to print, use verbose if you want to force print'
        ],
        tags: []
      }
    }
  },
  {
    database: {
      _id: 'ns',
      primary: 'mongos-01-shard-02',
      partitioned: true,
      version: {
        uuid: new UUID("21a43572-5b9d-42d6-bd76-0a8ef5c21459"),
        timestamp: Timestamp({ t: xxx, i: 1 }),
        lastMod: 1
      }
    },
    collections: {
      'ns.CompoundHashedShardKeyTest': {
        shardKey: { userid: 'hashed', created_at: 1 },
        unique: false,
        balancing: true,
        chunkMetadata: [
          { shard: 'mongos-01-shard-01', nChunks: 2 },
          { shard: 'mongos-01-shard-02', nChunks: 2 }
        ],
        chunks: [
          { min: { userid: MinKey(), created_at: MinKey() }, max: { userid: Long("-4611686018427387902"), created_at: MinKey() }, 'on shard': 'mongos-01-shard-01', 'last modified': Timestamp({ t: 1, i: 0 }) },
          { min: { userid: Long("-4611686018427387902"), created_at: MinKey() }, max: { userid: Long("0"), created_at: MinKey() }, 'on shard': 'mongos-01-shard-01', 'last modified': Timestamp({ t: 1, i: 1 }) },
          { min: { userid: Long("0"), created_at: MinKey() }, max: { userid: Long("4611686018427387902"), created_at: MinKey() }, 'on shard': 'mongos-01-shard-02', 'last modified': Timestamp({ t: 1, i: 2 }) },
          { min: { userid: Long("4611686018427387902"), created_at: MinKey() }, max: { userid: MaxKey(), created_at: MaxKey() }, 'on shard': 'mongos-01-shard-02', 'last modified': Timestamp({ t: 1, i: 3 }) }
        ],
        tags: []
      }
    }
  }
]
[direct: mongos] > for(var i = 1; i < 10000; i++) {
...   var date = new Date(1640995200000 + i * 1000 * 60 * 60);
...   db.CompoundHashedShardKeyTest.insert({"userid":1},{"created_at":date});
...   print(i + "-" + date);
... }
1-Sat Jan 01 2022 01:00:00 GMT+0000 (Coordinated Universal Time)
2-Sat Jan 01 2022 02:00:00 GMT+0000 (Coordinated Universal Time)
3-Sat Jan 01 2022 03:00:00 GMT+0000 (Coordinated Universal Time)
4-Sat Jan 01 2022 04:00:00 GMT+0000 (Coordinated Universal Time)
5-Sat Jan 01 2022 05:00:00 GMT+0000 (Coordinated Universal Time)
6-Sat Jan 01 2022 06:00:00 GMT+0000 (Coordinated Universal Time)
7-Sat Jan 01 2022 07:00:00 GMT+0000 (Coordinated Universal Time)
8-Sat Jan 01 2022 08:00:00 GMT+0000 (Coordinated Universal Time)
9-Sat Jan 01 2022 09:00:00 GMT+0000 (Coordinated Universal Time)
10-Sat Jan 01 2022 10:00:00 GMT+0000 (Coordinated Universal Time)
...
9990-Tue Feb 21 2023 06:00:00 GMT+0000 (Coordinated Universal Time)
9991-Tue Feb 21 2023 07:00:00 GMT+0000 (Coordinated Universal Time)
9992-Tue Feb 21 2023 08:00:00 GMT+0000 (Coordinated Universal Time)
9993-Tue Feb 21 2023 09:00:00 GMT+0000 (Coordinated Universal Time)
9994-Tue Feb 21 2023 10:00:00 GMT+0000 (Coordinated Universal Time)
9995-Tue Feb 21 2023 11:00:00 GMT+0000 (Coordinated Universal Time)
9996-Tue Feb 21 2023 12:00:00 GMT+0000 (Coordinated Universal Time)
9997-Tue Feb 21 2023 13:00:00 GMT+0000 (Coordinated Universal Time)
9998-Tue Feb 21 2023 14:00:00 GMT+0000 (Coordinated Universal Time)
9999-Tue Feb 21 2023 15:00:00 GMT+0000 (Coordinated Universal Time)
[direct: mongos] > db.CompoundHashedShardKeyTest.getShardDistribution()
Shard mongos-01-shard-01 at mongos-01-shard-01/mongos-01-shard-01-01-company.net:27017,mongos-01-shard-01-02-company.net:27017
{
  data: '0B',
  docs: 0,
  chunks: 2,
  'estimated data per chunk': '0B',
  'estimated docs per chunk': 0
}
---
Shard mongos-01-shard-02 at mongos-01-shard-02/mongos-01-shard-02-01-company.net:27017,mongos-01-shard-02-02-company.net:27017
{
  data: '331KiB',
  docs: 9999,
  chunks: 2,
  'estimated data per chunk': '165KiB',
  'estimated docs per chunk': 4999
}
---
Totals
{
  data: '331KiB',
  docs: 9999,
  chunks: 4,
  'Shard mongos-01-shard-01': [ '0 % data', '0 % docs in cluster', '0B avg obj size on shard' ],
  'Shard mongos-01-shard-02': [
    '100 % data',
    '100 % docs in cluster',
    '34B avg obj size on shard'
  ]
}
[direct: mongos] >
2
  • The default chunk size in MongoDB is 128MiB (was 64MiB in version < 6.0). Unless your total amount of data is less than 128 MiByte you will not see any effects on sharding. Nov 5, 2022 at 11:12
  • @WernfriedDomscheit the moment I change the shard key to just the hashed value of the userid, the distribution is as uniform. also, we're using MongoDB v5 where the chunk size is still 64 MB. Moreover, you could see the getShardDistribution output in the post where two chunks are already created each with ~ 331 kiB
    – asgs
    Nov 7, 2022 at 6:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.