Mongo - ошибка Exec: OperationFailed: операция сортировки использует больше, чем максимальное количество 33554432 байт ОЗУ

У меня есть документ монго, как показано ниже.

{
  "_id":"1234",
  "empId" : "1234"
  "manager":"456",
  "name" :"Mike",
  "mgrHierarchy":"456>678>789>901>999",
  "mgrs": [
      "456",
      "678",
      "789",
      "901",
      "999"
   ]
}

Цель состоит в том, чтобы получить иерархию для данного empId. Использование mgrHierarchy для сортировки, чтобы можно было легко получить иерархию.

mgrs - будут все менеджеры от 1-го уровня до верхнего уровня.

Mgrs и mgrHierarchy будут хранить одно и то же в другом формате.

Иметь индекс по empId, mgrHierarchy, mgrs, mgrs.0, mgrs.1, mgrs.2, mgrs.3, mgrs.4, .... Всего в этой коллекции около 20 индексов.

Запрос :-

{ "$or" : [ { "mgrs.2" : "901"} , { "_id" : { "$in" : [ "901"]}} , { "mgrs.0" : "901"} , { "mgrs.1" : "901"} , { "mgrs.4" : "901"} , { "mgrs.3" : "901"}]}).sort({ mgrHierarchy: 1 }

Этот счетчик запросов возвращает 100 тыс.

Этот запрос возвращает ошибку памяти.

Exec error: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit., state: FAILURE.

вывод объяснения("executionStats"),

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.test",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$or" : [
                {
                    "_id" : {
                        "$eq" : "901"
                    }
                },
                {
                    "mgrs.0" : {
                        "$eq" : "901"
                    }
                },
                {
                    "mgrs.1" : {
                        "$eq" : "901"
                    }
                },
                {
                    "mgrs.2" : {
                        "$eq" : "901"
                    }
                },
                {
                    "mgrs.3" : {
                        "$eq" : "901"
                    }
                },
                {
                    "mgrs.4" : {
                        "$eq" : "901"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SUBPLAN",
            "inputStage" : {
                "stage" : "PROJECTION",
                "transformBy" : {

                },
                "inputStage" : {
                    "stage" : "SORT",
                    "sortPattern" : {
                        "mgrHierarchy" : 1
                    },
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "inputStage" : {
                            "stage" : "FETCH",
                            "inputStage" : {
                                "stage" : "OR",
                                "inputStages" : [
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "_id" : 1
                                        },
                                        "indexName" : "_id_",
                                        "isMultiKey" : false,
                                        "isUnique" : true,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 1,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "_id" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        }
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "mgrs.0" : 1
                                        },
                                        "indexName" : "mgrs.0_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "mgrs.0" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.0" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        }
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "mgrs.1" : 1
                                        },
                                        "indexName" : "mgrs.1_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "mgrs.1" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.1" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        }
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "mgrs.2" : 1
                                        },
                                        "indexName" : "mgrs.2_1",
                                        "isMultiKey" : true,
                                        "multiKeyPaths" : {
                                            "mgrs.2" : [
                                                "mgrs"
                                            ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.2" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        }
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "mgrs.3" : 1
                                        },
                                        "indexName" : "mgrs.3_1",
                                        "isMultiKey" : true,
                                        "multiKeyPaths" : {
                                            "mgrs.3" : [
                                                "mgrs"
                                            ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.3" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        }
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                            "mgrs.4" : 1
                                        },
                                        "indexName" : "mgrs.4_1",
                                        "isMultiKey" : true,
                                        "multiKeyPaths" : {
                                            "mgrs.4" : [
                                                "mgrs"
                                            ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.4" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : false,
        "errorMessage" : "Exec error: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit., state: FAILURE",
        "errorCode" : 96,
        "nReturned" : 0,
        "executionTimeMillis" : 1120,
        "totalKeysExamined" : 47184,
        "totalDocsExamined" : 47184,
        "executionStages" : {
            "stage" : "SUBPLAN",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 1109,
            "works" : 47191,
            "advanced" : 0,
            "needTime" : 47190,
            "needYield" : 0,
            "saveState" : 2067,
            "restoreState" : 2067,
            "isEOF" : 0,
            "invalidates" : 0,
            "inputStage" : {
                "stage" : "PROJECTION",
                "nReturned" : 0,
                "executionTimeMillisEstimate" : 232,
                "works" : 47191,
                "advanced" : 0,
                "needTime" : 47190,
                "needYield" : 0,
                "saveState" : 371,
                "restoreState" : 371,
                "isEOF" : 0,
                "invalidates" : 0,
                "transformBy" : {

                },
                "inputStage" : {
                    "stage" : "SORT",
                    "nReturned" : 0,
                    "executionTimeMillisEstimate" : 221,
                    "works" : 47191,
                    "advanced" : 0,
                    "needTime" : 47190,
                    "needYield" : 0,
                    "saveState" : 371,
                    "restoreState" : 371,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "sortPattern" : {
                        "mgrHierarchy" : 1
                    },
                    "memUsage" : 33555113,
                    "memLimit" : 33554432,
                    "inputStage" : {
                        "stage" : "SORT_KEY_GENERATOR",
                        "nReturned" : 47184,
                        "executionTimeMillisEstimate" : 201,
                        "works" : 47190,
                        "advanced" : 47184,
                        "needTime" : 6,
                        "needYield" : 0,
                        "saveState" : 371,
                        "restoreState" : 371,
                        "isEOF" : 0,
                        "invalidates" : 0,
                        "inputStage" : {
                            "stage" : "FETCH",
                            "nReturned" : 47184,
                            "executionTimeMillisEstimate" : 141,
                            "works" : 47189,
                            "advanced" : 47184,
                            "needTime" : 5,
                            "needYield" : 0,
                            "saveState" : 371,
                            "restoreState" : 371,
                            "isEOF" : 0,
                            "invalidates" : 0,
                            "docsExamined" : 47184,
                            "alreadyHasObj" : 0,
                            "inputStage" : {
                                "stage" : "OR",
                                "nReturned" : 47184,
                                "executionTimeMillisEstimate" : 91,
                                "works" : 47189,
                                "advanced" : 47184,
                                "needTime" : 5,
                                "needYield" : 0,
                                "saveState" : 371,
                                "restoreState" : 371,
                                "isEOF" : 0,
                                "invalidates" : 0,
                                "dupsTested" : 47184,
                                "dupsDropped" : 0,
                                "recordIdsForgotten" : 0,
                                "inputStages" : [
                                    {
                                        "stage" : "IXSCAN",
                                        "nReturned" : 1,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 2,
                                        "advanced" : 1,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 371,
                                        "restoreState" : 371,
                                        "isEOF" : 1,
                                        "invalidates" : 0,
                                        "keyPattern" : {
                                            "_id" : 1
                                        },
                                        "indexName" : "_id_",
                                        "isMultiKey" : false,
                                        "isUnique" : true,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 1,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "_id" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        },
                                        "keysExamined" : 1,
                                        "seeks" : 1,
                                        "dupsTested" : 0,
                                        "dupsDropped" : 0,
                                        "seenInvalidated" : 0
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "nReturned" : 22,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 23,
                                        "advanced" : 22,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 371,
                                        "restoreState" : 371,
                                        "isEOF" : 1,
                                        "invalidates" : 0,
                                        "keyPattern" : {
                                            "mgrs.0" : 1
                                        },
                                        "indexName" : "mgrs.0_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "mgrs.0" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.0" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        },
                                        "keysExamined" : 22,
                                        "seeks" : 1,
                                        "dupsTested" : 0,
                                        "dupsDropped" : 0,
                                        "seenInvalidated" : 0
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "nReturned" : 349,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 350,
                                        "advanced" : 349,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 371,
                                        "restoreState" : 371,
                                        "isEOF" : 1,
                                        "invalidates" : 0,
                                        "keyPattern" : {
                                            "mgrs.1" : 1
                                        },
                                        "indexName" : "mgrs.1_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                            "mgrs.1" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.1" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        },
                                        "keysExamined" : 349,
                                        "seeks" : 1,
                                        "dupsTested" : 0,
                                        "dupsDropped" : 0,
                                        "seenInvalidated" : 0
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "nReturned" : 2859,
                                        "executionTimeMillisEstimate" : 0,
                                        "works" : 2860,
                                        "advanced" : 2859,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 371,
                                        "restoreState" : 371,
                                        "isEOF" : 1,
                                        "invalidates" : 0,
                                        "keyPattern" : {
                                            "mgrs.2" : 1
                                        },
                                        "indexName" : "mgrs.2_1",
                                        "isMultiKey" : true,
                                        "multiKeyPaths" : {
                                            "mgrs.2" : [
                                                "mgrs"
                                            ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.2" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        },
                                        "keysExamined" : 2859,
                                        "seeks" : 1,
                                        "dupsTested" : 2859,
                                        "dupsDropped" : 0,
                                        "seenInvalidated" : 0
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "nReturned" : 16475,
                                        "executionTimeMillisEstimate" : 40,
                                        "works" : 16476,
                                        "advanced" : 16475,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 371,
                                        "restoreState" : 371,
                                        "isEOF" : 1,
                                        "invalidates" : 0,
                                        "keyPattern" : {
                                            "mgrs.3" : 1
                                        },
                                        "indexName" : "mgrs.3_1",
                                        "isMultiKey" : true,
                                        "multiKeyPaths" : {
                                            "mgrs.3" : [
                                                "mgrs"
                                            ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.3" : [
                                                "[\"901\", \"901\"]"
                                            ]
                                        },
                                        "keysExamined" : 16475,
                                        "seeks" : 1,
                                        "dupsTested" : 16475,
                                        "dupsDropped" : 0,
                                        "seenInvalidated" : 0
                                    },
                                    {
                                        "stage" : "IXSCAN",
                                        "nReturned" : 27478,
                                        "executionTimeMillisEstimate" : 20,
                                        "works" : 27478,
                                        "advanced" : 27478,
                                        "needTime" : 0,
                                        "needYield" : 0,
                                        "saveState" : 371,
                                        "restoreState" : 371,
                                        "isEOF" : 0,
                                        "invalidates" : 0,
                                        "keyPattern" : {
                                            "mgrs.4" : 1
                                        },
                                        "indexName" : "mgrs.4_1",
                                        "isMultiKey" : true,
                                        "multiKeyPaths" : {
                                            "mgrs.4" : [
                                                "mgrs"
                                            ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                            "mgrs.4" : [
                                                "[\"901\", \"822717775\"]"
                                            ]
                                        },
                                        "keysExamined" : 27478,
                                        "seeks" : 1,
                                        "dupsTested" : 27478,
                                        "dupsDropped" : 0,
                                        "seenInvalidated" : 0
                                    }
                                ]
                            }
                        }
                    }
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "test",
        "port" : 10904,
        "version" : "3.4.14",
        "gitVersion" : "fd954412dfc10e4d1e3e2dd4fac040f8b476b268"
    },
    "ok" : 1
}

У меня есть индексы для всех полей, не знаю, почему до сих пор возникает эта ошибка. Любой указатель для этого.

🤔 А знаете ли вы, что...
MongoDB может использоваться для хранения временных рядов данных, например, для IoT-приложений...


3 184