分片架构MongoDB集群查询慢案例

db.competitiveBoardInfo.findOne()

查询非常慢，需要2分钟左右。

如果在5个分片的mongod上执行，则很快几乎瞬间返回结果。

db.competitiveBoardInfo.find().limit(1); 同样的问题，mongos上查询很慢，mongod上查询很快。

在mongos上查看db.competitiveBoardInfo.find().limit(1)的执行计划：

mongos> db.competitiveBoardInfo.explain("executionStats").find().limit(1);
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SHARD_MERGE",
"shards" : [
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_PyfadzWh",
"connectionString" : "allawn-fourier_aaCeHQbz_shard_PyfadzWh/xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx",
"serverInfo" : {
"host" : "xx-xx-xx-xx.mongodb-fatpod-game-assistant.cp01",
"port" : xxxxx,
"version" : "3.6.13",
"gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
},
"plannerVersion" : 1,
"namespace" : "athletic-board.competitiveBoardInfo",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_fqlddrlg",
"connectionString" : "allawn-fourier_aaCeHQbz_shard_fqlddrlg/xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx",
"serverInfo" : {
"host" : "xx-xx-xx-xx.mongodb-fatpod-game-assistant.bjht",
"port" : xxxxx,
"version" : "3.6.13",
"gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
},
"plannerVersion" : 1,
"namespace" : "athletic-board.competitiveBoardInfo",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_kxpnljHS",
"connectionString" : "allawn-fourier_aaCeHQbz_shard_kxpnljHS/xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx",
"serverInfo" : {
"host" : "xx-xx-xx-xx.mongodb-fatpod-game-center.bjht",
"port" : xxxxx,
"version" : "3.6.13",
"gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
},
"plannerVersion" : 1,
"namespace" : "athletic-board.competitiveBoardInfo",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_QhcikTxV",
"connectionString" : "allawn-fourier_aaCeHQbz_shard_QhcikTxV/xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx",
"serverInfo" : {
"host" : "xx-xx-xx-xx.mongodb-fatpod-game-assistant.bjht",
"port" : xxxxx,
"version" : "3.6.13",
"gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
},
"plannerVersion" : 1,
"namespace" : "athletic-board.competitiveBoardInfo",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_1",
"connectionString" : "allawn-fourier_aaCeHQbz_shard_1/xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx,xx.xx.xx.xx:xxxxx",
"serverInfo" : {
"host" : "xx-xx-xx-xx.mongodb-fatpod-allawn-fourier.bjht",
"port" : xxxxx,
"version" : "3.6.13",
"gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
},
"plannerVersion" : 1,
"namespace" : "athletic-board.competitiveBoardInfo",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
}
]
}
},
"executionStats" : {
"nReturned" : 5,
"executionTimeMillis" : 95861,
"totalKeysExamined" : 0,
"totalDocsExamined" : 4170775,
"executionStages" : {
"stage" : "SHARD_MERGE",
"nReturned" : 5,
"executionTimeMillis" : 95861,
"totalKeysExamined" : 0,
"totalDocsExamined" : 4170775,
"totalChildMillis" : NumberLong(95799),
"shards" : [
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_PyfadzWh",
"executionSuccess" : true,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"chunkSkips" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1
}
}
}
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_fqlddrlg",
"executionSuccess" : true,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"chunkSkips" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1
}
}
}
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_kxpnljHS",
"executionSuccess" : true,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"chunkSkips" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1
}
}
}
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_QhcikTxV",
"executionSuccess" : true,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"chunkSkips" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1
}
}
}
},
{
"shardName" : "allawn-fourier_aaCeHQbz_shard_1",
"executionSuccess" : true,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 1,
"executionTimeMillisEstimate" : 95402,
"works" : 4170773,
"advanced" : 1,
"needTime" : 4170771,
"needYield" : 0,
"saveState" : 32584,
"restoreState" : 32584,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 1,
"inputStage" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 1,
"executionTimeMillisEstimate" : 95252,
"works" : 4170772,
"advanced" : 1,
"needTime" : 4170771,
"needYield" : 0,
"saveState" : 32584,
"restoreState" : 32584,
"isEOF" : 0,
"invalidates" : 0,
"chunkSkips" : 4170770,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 4170771,
"executionTimeMillisEstimate" : 92612,
"works" : 4170772,
"advanced" : 4170771,
"needTime" : 1,
"needYield" : 0,
"saveState" : 32584,
"restoreState" : 32584,
"isEOF" : 0,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 4170771
}
}
}
}
]
}
},
"ok" : 1,
"operationTime" : Timestamp(1628596210, 1),
"$clusterTime" : {
"clusterTime" : Timestamp(1628596210, 1),
"signature" : {
"hash" : BinData(0,"SqmalN2+9/e7SttygMPX4XgeqVU="),
"keyId" : NumberLong("6959940060162556053")
}
}
}
mongos>

---在执行很慢的分片allawn-fourier_aaCeHQbz_shard_1 上的执行计划。

allawn-fourier_aaCeHQbz_shard_1:PRIMARY> db.competitiveBoardInfo.explain("executionStats").find().limit(1);
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "athletic-board.competitiveBoardInfo",
"indexFilterSet" : false,
"parsedQuery" : {

},
"winningPlan" : {
"stage" : "LIMIT",
"limitAmount" : 1,
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "LIMIT",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"limitAmount" : 1,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 0,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 1
}
}
},
"serverInfo" : {
"host" : "xx-xx-xx-xx.mongodb-fatpod-allawn-fourier.bjht",
"port" : xxxxx,
"version" : "3.6.13",
"gitVersion" : "db3c76679b7a3d9b443a0e1b3e45ed02b88c539f"
},
"ok" : 1,
"operationTime" : Timestamp(1628597564, 2),
"$gleStats" : {
"lastOpTime" : Timestamp(0, 0),
"electionId" : ObjectId("7fffffff000000000000003a")
},
"$configServerState" : {
"opTime" : {
"ts" : Timestamp(1628597570, 1),
"t" : NumberLong(10)
}
},
"$clusterTime" : {
"clusterTime" : Timestamp(1628597570, 1),
"signature" : {
"hash" : BinData(0,"PXSgBRYLDg5D0xrkIza1uGYQ3RY="),
"keyId" : NumberLong("6959940060162556053")
}
}
}
allawn-fourier_aaCeHQbz_shard_1:PRIMARY>

可以看到当在mongs上执行时，在正常的4个分片上 "docsExamined" : 1 ，但最后一个分片allawn-fourier_aaCeHQbz_shard_1 上 "docsExamined" : 4170771，

正常的4个分片上"chunkSkips" : 0, 最后一个分片 "chunkSkips" : 4170770, 执行计划中的chunkSkips反映了集合中的孤儿文档数，说明最后一个分片中集合存在孤儿文档。

通过清理集合中的孤儿文档，问题解决。

var nextKey = { };
var result;

while ( nextKey != null ) {
  result = db.adminCommand( { cleanupOrphaned: "test.user", startingFromKey: nextKey } );

  if (result.ok != 1)
     print("Unable to complete at this time: failure or timeout.")

  printjson(result);

  nextKey = result.stoppedAtKey;
}

HunterHuang

公告