分片架构MongoDB集群查询慢案例
分片架构集群,有5个分片,在mongos上查询一个集合,查询语句为:
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;
}