Database Administrators Asked by marc esher on January 4, 2022
Here’s behavior I can’t explain at all:
I have 5-replica-set sharded cluster.
I have a query that on 4 of the 5 RSs, it uses one index. But on one of the 5 RSs, it uses a different index. And that index is clearly a bad index. The consequence of this bad index is that queries grind to a halt.
On the 4 replica sets using a correct index, it’s picking an appropriate compound index. But on the bad replica set, it’s picking a single-key index.
When I run explain() on the query in question, I clearly see this behavior. I see an appropriate index “winning” on 4 of the 5, but on that 5th member I see that index in the “rejected” section.
Any help fixing mongo’s clock here?
Thank you!
Here’s the (redacted) output of explain(). I’ve kept all the details for a replica set that shows the correct index being chosen. I’ve also kept all the details for the bad one. I’ve redacted the other 3 since it’s basically duplicate and noise.
mongos> db.hmda_lar.find({da:2016,fb:1}).explain()
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SHARD_MERGE",
"shards" : [
{
"shardName" : "quRS1",
"connectionString" : "quRS1/mongod-l-e1a-p01:27017,mongod-l-e1d-p03:27017",
"serverInfo" : {
"host" : "mongod-l-e1a-p01",
"port" : 27017,
"version" : "3.0.15",
"gitVersion" : "b8ff507269c382bc100fc52f75f48d54cd42ec3b"
},
"plannerVersion" : 1,
"namespace" : "hmda.hmda_lar",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"da" : {
"$eq" : 2016
}
},
{
"fb" : {
"$eq" : 1
}
}
]
},
"winningPlan" : {
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"da" : NumberLong(1),
"89" : NumberLong(1),
"fb" : NumberLong(1)
},
"indexName" : "da_1_89_1_fb_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"da" : [
"[2016.0, 2016.0]"
],
"89" : [
"[MinKey, MaxKey]"
],
"fb" : [
"[1.0, 1.0]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"da" : {
"$eq" : 2016
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"fb" : NumberLong(1)
},
"indexName" : "fb_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"fb" : [
"[1.0, 1.0]"
]
}
}
}
}
},
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"da" : NumberLong(1),
"eb" : NumberLong(1),
"fb" : NumberLong(1),
"a14" : NumberLong(1),
"25" : NumberLong(1)
},
"indexName" : "da_1_eb_1_fb_1_a14_1_25_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"da" : [
"[2016.0, 2016.0]"
],
"eb" : [
"[MinKey, MaxKey]"
],
"fb" : [
"[1.0, 1.0]"
],
"a14" : [
"[MinKey, MaxKey]"
],
"25" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"fb" : {
"$eq" : 1
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"da" : NumberLong(1)
},
"indexName" : "da_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"da" : [
"[2016.0, 2016.0]"
]
}
}
}
}
}
]
},
{
"shardName" : "quRS2",
"connectionString" : "quRS2/mongod-l-e1a-p04:27017,mongod-l-e1d-p06:27017",
"serverInfo" : {
"host" : "mongod-l-e1a-p04",
"port" : 27017,
"version" : "3.0.15",
"gitVersion" : "b8ff507269c382bc100fc52f75f48d54cd42ec3b"
},
"plannerVersion" : 1,
"namespace" : "hmda.hmda_lar",
"indexFilterSet" : false,
..... basically the same as RS1 ......
},
{
"shardName" : "quRS3",
"connectionString" : "quRS3/mongod-l-e1a-p07:27017,mongod-l-e1d-p09:27017",
"serverInfo" : {
"host" : "mongod-l-e1a-p07",
"port" : 27017,
"version" : "3.0.15",
"gitVersion" : "b8ff507269c382bc100fc52f75f48d54cd42ec3b"
},
"plannerVersion" : 1,
"namespace" : "hmda.hmda_lar",
"indexFilterSet" : false,
.... basically the same as RS1 ......
},
{
!!!! Here's where it goes haywire !!!!!
"shardName" : "quRS4",
"connectionString" : "quRS4/mongod-l-e1a-p010:27017,mongod-l-e1d-p012:27017",
"serverInfo" : {
"host" : "mongod-l-e1a-p010",
"port" : 27017,
"version" : "3.0.15",
"gitVersion" : "b8ff507269c382bc100fc52f75f48d54cd42ec3b"
},
"plannerVersion" : 1,
"namespace" : "hmda.hmda_lar",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"da" : {
"$eq" : 2016
}
},
{
"fb" : {
"$eq" : 1
}
}
]
},
"winningPlan" : {
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"da" : {
"$eq" : 2016
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"fb" : NumberLong(1)
},
"indexName" : "fb_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"fb" : [
"[1.0, 1.0]"
]
}
}
}
}
},
"rejectedPlans" : [
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"da" : NumberLong(1),
"89" : NumberLong(1),
"fb" : NumberLong(1)
},
"indexName" : "da_1_89_1_fb_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"da" : [
"[2016.0, 2016.0]"
],
"89" : [
"[MinKey, MaxKey]"
],
"fb" : [
"[1.0, 1.0]"
]
}
}
}
}
},
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"da" : NumberLong(1),
"eb" : NumberLong(1),
"fb" : NumberLong(1),
"a14" : NumberLong(1),
"25" : NumberLong(1)
},
"indexName" : "da_1_eb_1_fb_1_a14_1_25_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"da" : [
"[2016.0, 2016.0]"
],
"eb" : [
"[MinKey, MaxKey]"
],
"fb" : [
"[1.0, 1.0]"
],
"a14" : [
"[MinKey, MaxKey]"
],
"25" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"fb" : {
"$eq" : 1
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"da" : NumberLong(1)
},
"indexName" : "da_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"da" : [
"[2016.0, 2016.0]"
]
}
}
}
}
}
]
},
{
"shardName" : "quRS5",
"connectionString" : "quRS5/mongod-l-e1a-p013:27017,mongod-l-e1d-p015:27017",
"serverInfo" : {
"host" : "mongod-l-e1a-p013",
"port" : 27017,
"version" : "3.0.15",
"gitVersion" : "b8ff507269c382bc100fc52f75f48d54cd42ec3b"
},
"plannerVersion" : 1,
"namespace" : "hmda.hmda_lar",
"indexFilterSet" : false,
..... basically the same as RS1 ....
},
"ok" : 1
}
I never did get a clear answer on why mongo was choosing such a bad index on one of the shards, but I got enough good feedback at https://groups.google.com/forum/#!topic/mongodb-user/w8gAsyrNvKY to work around it.
Answered by marc esher on January 4, 2022
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP