AnswerBun.com

MongoDB different members of cluster choosing different index

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
}

One Answer

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

Add your own answers!

Related Questions

Users on same server connect to MySQL with different hosts

1  Asked on December 12, 2021 by crm-dev

   

MSSQL TempDb full

2  Asked on December 8, 2021

     

MySQL: order tables by entity relations?

0  Asked on December 8, 2021

 

Schema level encryption in Postgres

0  Asked on December 8, 2021 by turgs

     

How to use GROUP BY on a CLOB Column

1  Asked on December 8, 2021 by dallincha

     

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir