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
- where all the shards are utilized for each and every user's activity data. That is, write distribution should be as uniform as possible
- 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
- userid - this will be hashed and routed to exactly one shard causing that shard to grow large
- 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
- 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] >
getShardDistributionoutput in the post where two chunks are already created each with ~ 331 kiB