Troubleshooting Problematic Collection and Query

Hey all,

We’ve been having trouble with a particular collection that we use for matchmaking. Players will enter an event and matchmake against 3 random opponents based on the score of their matchmaking record. These events start/end at 10am PST, so a large portion of the user base is active and trying to matchmake around this time.

In this collection, we store various matchmaking records for different events that a user can participate in, so they will have one record that has their userId and the matchmakingId for that particular event. The record also has a score which is what we use to determine the strength of that particular user’s record. The collection currently has around 20 million records in it. And some events can have upwards of almost 1 million records associated with it.

When we try to matchmake for a user, we grab 6 random records (we reduce down to 3 in the application code) for this collection within a particular score range. Originally, we tried to do this using the aggregation pipeline but we found that it did not seem performant enough (although we’re willing to give an another shot if that’s the best way). Our current implementation now just has a timestamp inserted into the record of when that particular matchmaking record was match made against and we sort against that timestamp so we cycle through the records. This makes our query look something like this:

db.getCollection('matchmaking').find({matchmakingId:”<eventId>”, score:{$gte : <lowerRange>, $lte:<upperRange>}}).sort({“matchmakeTimestamp” : 1}).limit(6)

We have an index that supports this query in equality, sort, range order matchmakingId_1_lastMatchmakeTimestamp_1_score_-1

Within any given score range, there can be tens of thousands of records that fall in that range.

Our DB infrastructure is shared with threes shards, each with a primary and two secondary machines in each replica set, and we’re running Mongo version 3.6.23 (we know, it’s a bit outdated)

At the event start/end time (10am PST), our monitoring tools report that the load average is high and we see that our db.currentOps queue becomes large and filled up with matchmaking queries. We’ve tried to investigate why these matchmaking queries have been such a problem, and have tried to optimize our queries as much as possible, but it seems like there’s still some issue that occurs that causes things to get backed up and cause the whole system to become sluggish.

Is there something that we’re missing, or is there some other way that we can perform these matchmaking queries more efficiently? Or are the queries fine and we need to adjust something else? Is MongoDB just not suited for these types of queries? We’ve been trying to work on this problem for the better part of a year and we’re at a loss for what to do.

For additional context, this is an example query with the output of explaining the query with executionStats
db.getCollection('matchmaking').find({ matchmakingId: “<matchmakingId>”, score: { $lte: 1516, $gte: 1011 } }).sort( { lastMatchmakeTimestamp: 1 }).limit(6).explain("executionStats")

{
    "queryPlanner" : {
        "mongosPlannerVersion" : 1,
        "winningPlan" : {
            "stage" : "SHARD_MERGE_SORT",
            "shards" : [ 
                {
                    "shardName" : "rs3",
                    "connectionString" : "rs3/shard3a:27018,shard3b:27018,shard3c:27018",
                    "serverInfo" : {
                        "host" : "shard3a.hostname",
                        "port" : 27018,
                        "version" : "3.6.23",
                        "gitVersion" : "d352e6a4764659e0d0350ce77279de3c1f243e5c"
                    },
                    "plannerVersion" : 1,
                    "namespace" : "userdb.matchmaking",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ 
                            {
                                "matchmakingId" : {
                                    "$eq" : "<matchmakingId>"
                                }
                            }, 
                            {
                                "score" : {
                                    "$lte" : 1516.0
                                }
                            }, 
                            {
                                "score" : {
                                    "$gte" : 1011.0
                                }
                            }
                        ]
                    },
                    "winningPlan" : {
                        "stage" : "LIMIT",
                        "limitAmount" : 6,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "matchmakingId" : 1.0,
                                    "lastMatchmakeTimestamp" : 1.0,
                                    "score" : -1.0
                                },
                                "indexName" : "matchmakingId_1_lastMatchmakeTimestamp_1_score_-1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "matchmakingId" : [],
                                    "lastMatchmakeTimestamp" : [],
                                    "score" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "matchmakingId" : [ 
                                        "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                    ],
                                    "lastMatchmakeTimestamp" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "score" : [ 
                                        "[1516.0, 1011.0]"
                                    ]
                                }
                            }
                        }
                    },
                    "rejectedPlans" : [ 
                        {
                            "stage" : "SORT",
                            "sortPattern" : {
                                "lastMatchmakeTimestamp" : 1.0
                            },
                            "limitAmount" : 6,
                            "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR",
                                "inputStage" : {
                                    "stage" : "FETCH",
                                    "filter" : {
                                        "$and" : [ 
                                            {
                                                "score" : {
                                                    "$lte" : 1516.0
                                                }
                                            }, 
                                            {
                                                "score" : {
                                                    "$gte" : 1011.0
                                                }
                                            }
                                        ]
                                    },
                                    "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "matchmakingId" : 1.0,
                                            "userId" : 1.0
                                        },
                                        "indexName" : "matchmakingId_1_userId_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "matchmakingId" : [],
                                            "userId" : []
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "matchmakingId" : [ 
                                                "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                            ],
                                            "userId" : [ 
                                                "[MinKey, MaxKey]"
                                            ]
                                        }
                                    }
                                }
                            }
                        }
                    ]
                }, 
                {
                    "shardName" : "rs1",
                    "connectionString" : "rs1/shard1a:27018,shard1b:27018,shard1c:27018",
                    "serverInfo" : {
                        "host" : "shard1c.hostname",
                        "port" : 27018,
                        "version" : "3.6.5",
                        "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618"
                    },
                    "plannerVersion" : 1,
                    "namespace" : "userdb.matchmaking",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ 
                            {
                                "matchmakingId" : {
                                    "$eq" : "<matchmakingId>"
                                }
                            }, 
                            {
                                "score" : {
                                    "$lte" : 1516.0
                                }
                            }, 
                            {
                                "score" : {
                                    "$gte" : 1011.0
                                }
                            }
                        ]
                    },
                    "winningPlan" : {
                        "stage" : "LIMIT",
                        "limitAmount" : 6,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "matchmakingId" : 1.0,
                                    "lastMatchmakeTimestamp" : 1.0,
                                    "score" : -1.0
                                },
                                "indexName" : "matchmakingId_1_lastMatchmakeTimestamp_1_score_-1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "matchmakingId" : [],
                                    "lastMatchmakeTimestamp" : [],
                                    "score" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "matchmakingId" : [ 
                                        "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                    ],
                                    "lastMatchmakeTimestamp" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "score" : [ 
                                        "[1516.0, 1011.0]"
                                    ]
                                }
                            }
                        }
                    },
                    "rejectedPlans" : [ 
                        {
                            "stage" : "SORT",
                            "sortPattern" : {
                                "lastMatchmakeTimestamp" : 1.0
                            },
                            "limitAmount" : 6,
                            "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR",
                                "inputStage" : {
                                    "stage" : "FETCH",
                                    "filter" : {
                                        "$and" : [ 
                                            {
                                                "score" : {
                                                    "$lte" : 1516.0
                                                }
                                            }, 
                                            {
                                                "score" : {
                                                    "$gte" : 1011.0
                                                }
                                            }
                                        ]
                                    },
                                    "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "matchmakingId" : 1.0,
                                            "userId" : 1.0
                                        },
                                        "indexName" : "matchmakingId_1_userId_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "matchmakingId" : [],
                                            "userId" : []
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "matchmakingId" : [ 
                                                "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                            ],
                                            "userId" : [ 
                                                "[MinKey, MaxKey]"
                                            ]
                                        }
                                    }
                                }
                            }
                        }
                    ]
                }, 
                {
                    "shardName" : "rs2",
                    "connectionString" : "rs2/shard2a:27018,shard2b:27018,shard2c:27018",
                    "serverInfo" : {
                        "host" : "shard2c.hostname",
                        "port" : 27018,
                        "version" : "3.6.5",
                        "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618"
                    },
                    "plannerVersion" : 1,
                    "namespace" : "userdb.matchmaking",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ 
                            {
                                "matchmakingId" : {
                                    "$eq" : "<matchmakingId>"
                                }
                            }, 
                            {
                                "score" : {
                                    "$lte" : 1516.0
                                }
                            }, 
                            {
                                "score" : {
                                    "$gte" : 1011.0
                                }
                            }
                        ]
                    },
                    "winningPlan" : {
                        "stage" : "LIMIT",
                        "limitAmount" : 6,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                    "matchmakingId" : 1.0,
                                    "lastMatchmakeTimestamp" : 1.0,
                                    "score" : -1.0
                                },
                                "indexName" : "matchmakingId_1_lastMatchmakeTimestamp_1_score_-1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "matchmakingId" : [],
                                    "lastMatchmakeTimestamp" : [],
                                    "score" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "matchmakingId" : [ 
                                        "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                    ],
                                    "lastMatchmakeTimestamp" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "score" : [ 
                                        "[1516.0, 1011.0]"
                                    ]
                                }
                            }
                        }
                    },
                    "rejectedPlans" : [ 
                        {
                            "stage" : "SORT",
                            "sortPattern" : {
                                "lastMatchmakeTimestamp" : 1.0
                            },
                            "limitAmount" : 6,
                            "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR",
                                "inputStage" : {
                                    "stage" : "FETCH",
                                    "filter" : {
                                        "$and" : [ 
                                            {
                                                "score" : {
                                                    "$lte" : 1516.0
                                                }
                                            }, 
                                            {
                                                "score" : {
                                                    "$gte" : 1011.0
                                                }
                                            }
                                        ]
                                    },
                                    "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "matchmakingId" : 1.0,
                                            "userId" : 1.0
                                        },
                                        "indexName" : "matchmakingId_1_userId_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "matchmakingId" : [],
                                            "userId" : []
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "matchmakingId" : [ 
                                                "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                            ],
                                            "userId" : [ 
                                                "[MinKey, MaxKey]"
                                            ]
                                        }
                                    }
                                }
                            }
                        }
                    ]
                }
            ]
        }
    },
    "executionStats" : {
        "nReturned" : 18,
        "executionTimeMillis" : 195,
        "totalKeysExamined" : 12208,
        "totalDocsExamined" : 18,
        "executionStages" : {
            "stage" : "SHARD_MERGE_SORT",
            "nReturned" : 18,
            "executionTimeMillis" : 195,
            "totalKeysExamined" : 12208,
            "totalDocsExamined" : 18,
            "totalChildMillis" : NumberLong(314),
            "shards" : [ 
                {
                    "shardName" : "rs3",
                    "executionSuccess" : true,
                    "executionStages" : {
                        "stage" : "LIMIT",
                        "nReturned" : 6,
                        "executionTimeMillisEstimate" : 10,
                        "works" : 3950,
                        "advanced" : 6,
                        "needTime" : 3943,
                        "needYield" : 0,
                        "saveState" : 61,
                        "restoreState" : 61,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "limitAmount" : 6,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "nReturned" : 6,
                            "executionTimeMillisEstimate" : 10,
                            "works" : 3949,
                            "advanced" : 6,
                            "needTime" : 3943,
                            "needYield" : 0,
                            "saveState" : 61,
                            "restoreState" : 61,
                            "isEOF" : 0,
                            "invalidates" : 0,
                            "docsExamined" : 6,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 6,
                                "executionTimeMillisEstimate" : 10,
                                "works" : 3949,
                                "advanced" : 6,
                                "needTime" : 3943,
                                "needYield" : 0,
                                "saveState" : 61,
                                "restoreState" : 61,
                                "isEOF" : 0,
                                "invalidates" : 0,
                                "keyPattern" : {
                                    "matchmakingId" : 1.0,
                                    "lastMatchmakeTimestamp" : 1.0,
                                    "score" : -1.0
                                },
                                "indexName" : "matchmakingId_1_lastMatchmakeTimestamp_1_score_-1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "matchmakingId" : [],
                                    "lastMatchmakeTimestamp" : [],
                                    "score" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "matchmakingId" : [ 
                                        "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                    ],
                                    "lastMatchmakeTimestamp" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "score" : [ 
                                        "[1516.0, 1011.0]"
                                    ]
                                },
                                "keysExamined" : 3949,
                                "seeks" : 3944,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                            }
                        }
                    }
                }, 
                {
                    "shardName" : "rs1",
                    "executionSuccess" : true,
                    "executionStages" : {
                        "stage" : "LIMIT",
                        "nReturned" : 6,
                        "executionTimeMillisEstimate" : 20,
                        "works" : 3786,
                        "advanced" : 6,
                        "needTime" : 3779,
                        "needYield" : 0,
                        "saveState" : 59,
                        "restoreState" : 59,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "limitAmount" : 6,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "nReturned" : 6,
                            "executionTimeMillisEstimate" : 20,
                            "works" : 3785,
                            "advanced" : 6,
                            "needTime" : 3779,
                            "needYield" : 0,
                            "saveState" : 59,
                            "restoreState" : 59,
                            "isEOF" : 0,
                            "invalidates" : 0,
                            "docsExamined" : 6,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 6,
                                "executionTimeMillisEstimate" : 20,
                                "works" : 3785,
                                "advanced" : 6,
                                "needTime" : 3779,
                                "needYield" : 0,
                                "saveState" : 59,
                                "restoreState" : 59,
                                "isEOF" : 0,
                                "invalidates" : 0,
                                "keyPattern" : {
                                    "matchmakingId" : 1.0,
                                    "lastMatchmakeTimestamp" : 1.0,
                                    "score" : -1.0
                                },
                                "indexName" : "matchmakingId_1_lastMatchmakeTimestamp_1_score_-1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "matchmakingId" : [],
                                    "lastMatchmakeTimestamp" : [],
                                    "score" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "matchmakingId" : [ 
                                        "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                    ],
                                    "lastMatchmakeTimestamp" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "score" : [ 
                                        "[1516.0, 1011.0]"
                                    ]
                                },
                                "keysExamined" : 3785,
                                "seeks" : 3780,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                            }
                        }
                    }
                }, 
                {
                    "shardName" : "rs2",
                    "executionSuccess" : true,
                    "executionStages" : {
                        "stage" : "LIMIT",
                        "nReturned" : 6,
                        "executionTimeMillisEstimate" : 40,
                        "works" : 4475,
                        "advanced" : 6,
                        "needTime" : 4468,
                        "needYield" : 0,
                        "saveState" : 69,
                        "restoreState" : 69,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "limitAmount" : 6,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "nReturned" : 6,
                            "executionTimeMillisEstimate" : 40,
                            "works" : 4474,
                            "advanced" : 6,
                            "needTime" : 4468,
                            "needYield" : 0,
                            "saveState" : 69,
                            "restoreState" : 69,
                            "isEOF" : 0,
                            "invalidates" : 0,
                            "docsExamined" : 6,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 6,
                                "executionTimeMillisEstimate" : 40,
                                "works" : 4474,
                                "advanced" : 6,
                                "needTime" : 4468,
                                "needYield" : 0,
                                "saveState" : 69,
                                "restoreState" : 69,
                                "isEOF" : 0,
                                "invalidates" : 0,
                                "keyPattern" : {
                                    "matchmakingId" : 1.0,
                                    "lastMatchmakeTimestamp" : 1.0,
                                    "score" : -1.0
                                },
                                "indexName" : "matchmakingId_1_lastMatchmakeTimestamp_1_score_-1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "matchmakingId" : [],
                                    "lastMatchmakeTimestamp" : [],
                                    "score" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "matchmakingId" : [ 
                                        "[\"<matchmakingId>\", \"<matchmakingId>\"]"
                                    ],
                                    "lastMatchmakeTimestamp" : [ 
                                        "[MinKey, MaxKey]"
                                    ],
                                    "score" : [ 
                                        "[1516.0, 1011.0]"
                                    ]
                                },
                                "keysExamined" : 4474,
                                "seeks" : 4469,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                            }
                        }
                    }
                }
            ]
        }
    },
    "serverInfo" : {
        "host" : “host.address”,
        "port" : 27017,
        "version" : "3.6.23",
        "gitVersion" : "d352e6a4764659e0d0350ce77279de3c1f243e5c"
    },
    "ok" : 1.0,
    "operationTime" : Timestamp(1675365138, 1417),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1675365138, 1461),
        "signature" : {
            "hash" : { "$binary" : "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "$type" : "00" },
            "keyId" : NumberLong(0)
        }
    }
}