|
We have a 3x3 MongoDB Cluster in Amazon EC2, we are querying from an Azure Worker Role and an Azure Web Worker Role.
We have about 10M documents stored, there are only 2 main collections:
The _id for controls is a string GUID.
The _id for controlIssues is a compound of: the control string guid and a guid for itself.
Therefore are indexes are as follows (below is our createDb() function for creating our sharded collections and index):
var createDb = function() {
|
var client = db.getMongo();
|
|
// Create collections.
|
var controlIt = client.getDB("controlIt");
|
controlIt.createCollection("controls");
|
controlIt.createCollection("controlIssues");
|
controlIt.createCollection("controlIssueMetaData");
|
controlIt.createCollection("controlIssueReasons");
|
controlIt.createCollection("issueStates");
|
|
// Sharding.
|
sh.enableSharding("controlIt");
|
sh.shardCollection("controlIt.controls", { _id: 1 }, { "unique": true });
|
sh.shardCollection("controlIt.controlIssues", { _id: 1 }, { "unique": true });
|
|
// Indexes on controls collection.
|
db.controls.ensureIndex({ "name": 1 }, { "background": true });
|
db.controls.ensureIndex({ "description": 1 }, { "background": true, "sparse": true });
|
db.controls.ensureIndex({ "active": 1 }, { "background": true, "sparse": true });
|
|
// Indexes on controlIssues collection.
|
db.controlIssues.ensureIndex({ "state.id": 1 }, { "background": true });
|
db.controlIssues.ensureIndex({ "state.treatAsClosed": 1 }, { "background": true, "sparse": true });
|
db.controlIssues.ensureIndex({ "attributes.id": 1 }, { "background": true, "sparse": true });
|
db.controlIssues.ensureIndex({ "attributes.value": 1 }, { "background": true, "sparse": true });
|
}
|
Below is an example "control", simple JSON:
{
|
"_id": "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea",
|
"typeId": -1,
|
"surrogateKey": "-600",
|
"name": "Sample SM Control D",
|
"shortName": "Sample SM Control D Short name",
|
"description": "Sample SM Control D Description",
|
"itemsForInvestigation": 0,
|
"escalationId": -1,
|
"effectiveStartDate": { "$date": "2015-02-18T00:00:00.000Z" },
|
"subscribingUserIds": [1,2,4,94],
|
"active": true,
|
"activeForNotifications": true,
|
"owner": 1,
|
"ownerDelegate": 94,
|
"owningTeamId": 7,
|
"ragThreshold":
|
{
|
"red":
|
{
|
"shortName": "R",
|
"ragCode": "1",
|
"lower": 100.01000,
|
"upper": 10000.00000
|
},
|
"amber":
|
{
|
"shortName": "A",
|
"ragCode": "2",
|
"lower": 0.01000,
|
"upper": 100.00000
|
},
|
"green":
|
{
|
"shortName": "G",
|
"ragCode": "3",
|
"lower": 0.00000,
|
"upper": 0.00000
|
}
|
}
|
}
|
Below is a "controlIssue" example, JSON:
{
|
"_id": {
|
"controlId": "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea",
|
"id": "8dff48e4-7b6f-4cf2-ba1e-dea889432e24"
|
},
|
"state": {
|
"id": "NEW",
|
"date": {
|
"$date": "2015-02-18T00:00:00.000Z"
|
},
|
"treatAsClosed": false
|
},
|
"stateHistory": [
|
{
|
"date": {
|
"$date": "2015-02-18T00:00:00.000Z"
|
},
|
"id": "NEW"
|
},
|
{
|
"date": {
|
"$date": "2015-02-19T00:00:00.000Z"
|
},
|
"id": "CLOSED"
|
}
|
],
|
"attributes": [
|
{
|
"id": "-1",
|
"value": "Test01"
|
},
|
{
|
"id": "109",
|
"value": "Test1"
|
},
|
{
|
"id": "110",
|
"value": "Name"
|
},
|
{
|
"id": "111",
|
"value": "LastName"
|
}
|
],
|
"commentary": [
|
{
|
"reasonId": "UNDER_INVESTIGATION",
|
"text": "Something entered by user",
|
"userId": 123,
|
"date": {
|
"$date": "2015-02-19T00:00:00.000Z"
|
}
|
}
|
]
|
}
|
As you can see, there is a parent child relationship between control and controlIssue, control being the parent.
Here is a query that takes a long time to run and obviously it shouldn't:
db.controlIssues.find({ "_id.controlId": "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea" }).limit(100)
|
Here is the explain of the above:
{
|
"clusteredType" : "ParallelSort",
|
"shards" : {
|
"ciShard_0/CI-0.acr-group.9122.mongodbdns.com:27000,CI-1.acr-group.9122.mongodbdns.com:27000,CI-5.acr-group.9122.mongodbdns.com:27000" : [
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "controlIt.controlIssues",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"winningPlan" : {
|
"stage" : "LIMIT",
|
"limitAmount" : 100,
|
"inputStage" : {
|
"stage" : "SHARDING_FILTER",
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"direction" : "forward"
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 0,
|
"executionTimeMillis" : 2205,
|
"totalKeysExamined" : 0,
|
"totalDocsExamined" : 3519658,
|
"executionStages" : {
|
"stage" : "LIMIT",
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 1930,
|
"works" : 3519660,
|
"advanced" : 0,
|
"needTime" : 3519659,
|
"needFetch" : 0,
|
"saveState" : 27497,
|
"restoreState" : 27497,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"limitAmount" : 100,
|
"inputStage" : {
|
"stage" : "SHARDING_FILTER",
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 1920,
|
"works" : 3519660,
|
"advanced" : 0,
|
"needTime" : 3519659,
|
"needFetch" : 0,
|
"saveState" : 27497,
|
"restoreState" : 27497,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"chunkSkips" : 0,
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 1840,
|
"works" : 3519659,
|
"advanced" : 0,
|
"needTime" : 3519659,
|
"needFetch" : 0,
|
"saveState" : 27497,
|
"restoreState" : 27497,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"direction" : "forward",
|
"docsExamined" : 3519658
|
}
|
}
|
},
|
"allPlansExecution" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "CI-0",
|
"port" : 27000,
|
"version" : "3.0.2",
|
"gitVersion" : "6201872043ecbbc0a4cc169b5482dcf385fc464f"
|
}
|
}
|
],
|
"ciShard_1/CI-12.acr-group.9122.mongodbdns.com:27000,CI-3.acr-group.9122.mongodbdns.com:27000,CI-6.acr-group.9122.mongodbdns.com:27000" : [
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "controlIt.controlIssues",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"winningPlan" : {
|
"stage" : "LIMIT",
|
"limitAmount" : 100,
|
"inputStage" : {
|
"stage" : "SHARDING_FILTER",
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"direction" : "forward"
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 0,
|
"executionTimeMillis" : 2350,
|
"totalKeysExamined" : 0,
|
"totalDocsExamined" : 3459816,
|
"executionStages" : {
|
"stage" : "LIMIT",
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 2080,
|
"works" : 3459818,
|
"advanced" : 0,
|
"needTime" : 3459817,
|
"needFetch" : 0,
|
"saveState" : 27029,
|
"restoreState" : 27029,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"limitAmount" : 100,
|
"inputStage" : {
|
"stage" : "SHARDING_FILTER",
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 2060,
|
"works" : 3459818,
|
"advanced" : 0,
|
"needTime" : 3459817,
|
"needFetch" : 0,
|
"saveState" : 27029,
|
"restoreState" : 27029,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"chunkSkips" : 0,
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 2000,
|
"works" : 3459817,
|
"advanced" : 0,
|
"needTime" : 3459817,
|
"needFetch" : 0,
|
"saveState" : 27029,
|
"restoreState" : 27029,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"direction" : "forward",
|
"docsExamined" : 3459816
|
}
|
}
|
},
|
"allPlansExecution" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "CI-12",
|
"port" : 27000,
|
"version" : "3.0.2",
|
"gitVersion" : "6201872043ecbbc0a4cc169b5482dcf385fc464f"
|
}
|
}
|
],
|
"ciShard_2/CI-2.acr-group.9122.mongodbdns.com:27000,CI-4.acr-group.9122.mongodbdns.com:27000,CI-7.acr-group.9122.mongodbdns.com:27001" : [
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "controlIt.controlIssues",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"winningPlan" : {
|
"stage" : "LIMIT",
|
"limitAmount" : 0,
|
"inputStage" : {
|
"stage" : "SHARDING_FILTER",
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"direction" : "forward"
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 100,
|
"executionTimeMillis" : 62,
|
"totalKeysExamined" : 0,
|
"totalDocsExamined" : 100100,
|
"executionStages" : {
|
"stage" : "LIMIT",
|
"nReturned" : 100,
|
"executionTimeMillisEstimate" : 40,
|
"works" : 100102,
|
"advanced" : 100,
|
"needTime" : 100001,
|
"needFetch" : 0,
|
"saveState" : 782,
|
"restoreState" : 782,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"limitAmount" : 0,
|
"inputStage" : {
|
"stage" : "SHARDING_FILTER",
|
"nReturned" : 100,
|
"executionTimeMillisEstimate" : 40,
|
"works" : 100101,
|
"advanced" : 100,
|
"needTime" : 100001,
|
"needFetch" : 0,
|
"saveState" : 782,
|
"restoreState" : 782,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"chunkSkips" : 0,
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"_id.controlId" : {
|
"$eq" : "9e5b2f2b-a1a0-4b51-b261-16c61cff53ea"
|
}
|
},
|
"nReturned" : 100,
|
"executionTimeMillisEstimate" : 40,
|
"works" : 100101,
|
"advanced" : 100,
|
"needTime" : 100001,
|
"needFetch" : 0,
|
"saveState" : 782,
|
"restoreState" : 782,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"direction" : "forward",
|
"docsExamined" : 100100
|
}
|
}
|
},
|
"allPlansExecution" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "CI-2",
|
"port" : 27000,
|
"version" : "3.0.2",
|
"gitVersion" : "6201872043ecbbc0a4cc169b5482dcf385fc464f"
|
}
|
}
|
]
|
},
|
"n" : 100,
|
"nscanned" : 0,
|
"nscannedObjects" : 7079574,
|
"millisShardTotal" : 4617,
|
"millisShardAvg" : 1539,
|
"numQueries" : 3,
|
"numShards" : 3,
|
"executionTimeMillis" : 2351
|
}
|
Below is our sh.status():
--- Sharding Status ---
|
sharding version: {
|
"_id" : 1,
|
"minCompatibleVersion" : 5,
|
"currentVersion" : 6,
|
"clusterId" : ObjectId("553a4a3173a8b2c25c74b403")
|
}
|
shards:
|
{ "_id" : "ciShard_0", "host" : "ciShard_0/CI-0.acr-group.9122.mongodbdns.com:27000,CI-1.acr-group.9122.mongodbdns.com:27000,CI-5.acr-group.9122.mongodbdns.com:27000" }
|
{ "_id" : "ciShard_1", "host" : "ciShard_1/CI-12.acr-group.9122.mongodbdns.com:27000,CI-3.acr-group.9122.mongodbdns.com:27000,CI-6.acr-group.9122.mongodbdns.com:27000" }
|
{ "_id" : "ciShard_2", "host" : "ciShard_2/CI-2.acr-group.9122.mongodbdns.com:27000,CI-4.acr-group.9122.mongodbdns.com:27000,CI-7.acr-group.9122.mongodbdns.com:27001" }
|
databases:
|
{ "_id" : "admin", "partitioned" : false, "primary" : "config" }
|
{ "_id" : "controlIt", "partitioned" : true, "primary" : "ciShard_2" }
|
controlIt.controlIssues
|
shard key: { "_id" : 1 }
|
chunks:
|
ciShard_2 175
|
ciShard_1 176
|
ciShard_0 177
|
too many chunks to print, use verbose if you want to force print
|
controlIt.controls
|
shard key: { "_id" : 1 }
|
chunks:
|
ciShard_1 1
|
ciShard_2 1
|
ciShard_0 1
|
{ "_id" : { "$minKey" : 1 } } -->> { "_id" : "e1542af1-308c-4bca-a28e-5a78c8a25a94" } on : ciShard_1 Timestamp(3, 0)
|
{ "_id" : "e1542af1-308c-4bca-a28e-5a78c8a25a94" } -->> { "_id" : "fb495ea8-bed8-440c-8b87-22926adf83c8" } on : ciShard_2 Timestamp(3, 1)
|
{ "_id" : "fb495ea8-bed8-440c-8b87-22926adf83c8" } -->> { "_id" : { "$maxKey" : 1 } } on : ciShard_0 Timestamp(2, 0)
|
{ "_id" : "test", "partitioned" : false, "primary" : "ciShard_1" }
|
{ "_id" : "ControlItDb", "partitioned" : false, "primary" : "ciShard_0" }
|
|