-
Type:
Question
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Performance
-
None
-
None
-
None
-
None
-
None
-
None
-
None
-
None
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:
- controls
- controlIssues
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" }