Hi Experts,
Recently we got a performance issue that findOne query is very slow when run in mongos ,while fast when run in mongod. Our environment is shard architecture with 5 shards.
I found when run in mongos, will scan 1 row in 4 shards while many rows in fifth shard allawn-fourier_aaCeHQbz_shard_1, on this shard “docsExamined” : 4170771 , so lead the slow run in mongos, details as below:
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.mongodb-fatpod-game-assistant.cp01",
"port" : xx,
"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:xxxx,xx.xx.xx.xx:xx",
"serverInfo" : {
"host" : "xx.mongodb-fatpod-game-assistant.bjht",
"port" : xx,
"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:xx,xx.xx.xx.xx:xx,xx.xx.xx.xx:xx",
"serverInfo" : {
"host" : "xx.mongodb-fatpod-game-center.bjht",
"port" : xx,
"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:xx,xx.xx.xx.xx:xx,xx.xx.xx.xx:xx",
"serverInfo" : {
"host" : "xx.mongodb-fatpod-game-assistant.bjht",
"port" : xx,
"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",
"serverInfo" : {
"host" : "xx.mongodb-fatpod-allawn-fourier.bjht",
"port" : xx,
"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>
findOne() and find().limit(1) same result,
I want to know why so many rows needed to scan when I run find().limit(1), seems this is a common issue in shard cluster.
And how to reslove it, many thanks!