Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-18454

Slow Read Performance - MongoDB 3 with WiredTiger Storage Engine.

    XMLWordPrintableJSON

Details

    • Icon: Question Question
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • None
    • Performance
    • None

    Description

      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" }
      

      Attachments

        Activity

          People

            sam.kleinman Sam Kleinman (Inactive)
            pjking@infusion.com Peter John King
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: