Need advise to increase memory to run $push command with large data set

Hi ,

I got this error after I run this code

‘$push used too much memory and cannot spill to disk. Memory limit: 104857600 bytes’, ‘code’: 146, ‘codeName’: ‘ExceededMemoryLimit’

the code I ran is quite expensive query in term of memory usage due to $group and $push however I got 64 GB of RAM in this server for MongoDB only. So question is how can I increase this limit because by default It’s seem $push is limited RAM to 100 MB.

“allowDiskUse” is set to true but still have this error

Additional info: this MongoDB version is “4.4.1”.

db.collection.aggregate(
    [
//        {
//            "$limit": 200000.0
//        },
        {
            "$match": {
                "$and": 
                    [
					    { "TID": /XP/ },
                        { "timestamp": { "$gte": "2021-05-26 00:00:00" } },
                        { "timestamp": { "$lte": "2021-05-27 23:59:59" } },
                        { "Col": /^\d.*$/  },
                        { "MT0": /^\d.*$/ },
                        { "Row": /^\d.*$/ },
                        { "TRM": /^\d.*$/ },
                        { "WRLFTR_0": /^\d.*$/ },
                        { "WRLFTR_1": /^\d.*$/ }
                    ]
            }
        },
        {
            "$group": {
                "_id": {
                    "TID": "$TID",
                    "Opt": "$Opt",
                    "DSN1": "$DSN1",
                    "DSN2": "$DSN2",
                    "Col": "$Col",
                    "Row": "$Row",
                    "CSN": "$CSN",
					"PID": {
						"$switch": {
							"branches": [
								{ 
									"case": { "$eq": [ "$Opt","S5" ] }, "then": "6350"
								},
								{
									"case": { "$eq": [ "$Opt","FUNC" ] }, "then": "6400"
								},
								{
									"case": { "$eq": [ "$Opt","STR" ] }, "then": "6600"
								},
								{
									"case": { "$eq": [ "$Opt","FIN" ] }, "then": "6800"
								},
								{ 
									"case": { "$eq": [ "$Opt", "FTG" ] }, "then": "9000"
								}
							],
							"default": "$_id.Opt"
						}
					}, 
                    "CID": {
                      "$switch": {
                          "branches": [
                          {
                              "case": 	{ "$and": 
											[ 	
												{ "$gte": [ {"$toDecimal": "$Col"}, 1.0 ] },
												{ "$lte": [ {"$toDecimal": "$Col"}, 12.0 ] }
											]
										},
                              "then": 1.0
                          },
                          {
                              "case": 	{ "$and": 
											[ 	
												{ "$gte": [ {"$toDecimal": "$Col"}, 13.0 ] },
												{ "$lte": [ {"$toDecimal": "$Col"}, 24.0 ] }
											]
										},
                              "then": 2.0
                          },
                          {
                              "case": 	{ "$and": 
											[ 	
												{ "$gte": [ {"$toDecimal": "$Col"}, 25.0 ] },
												{ "$lte": [ {"$toDecimal": "$Col"}, 36.0 ] }
											]
										},
                              "then": 3.0
                          },
                          {
                              "case": 	{ "$and": 
											[ 	
												{ "$gte": [ {"$toDecimal": "$Col"}, 37.0 ] },
												{ "$lte": [ {"$toDecimal": "$Col"}, 48.0 ] }
											]
										},
                              "then": 4.0
                          },
                          {
                              "case": 	{ "$and": 
											[ 	
												{ "$gte": [ {"$toDecimal": "$Col"}, 49.0 ] },
												{ "$lte": [ {"$toDecimal": "$Col"}, 60.0 ] }
											]
										},
                              "then": 5.0
                          },	
                          {
                              "case": 	{ "$and": 
											[ 	
												{ "$gte": [ {"$toDecimal": "$Col"}, 61.0 ] },
												{ "$lte": [ {"$toDecimal": "$Col"}, 72.0 ] }
											]
										},
                              "then": 6.0
                          },						  
                      ],
                      "default": 0.0
                  }
              },                                    
                },
                "details": {
                    "$push": {                           // partition over
                        "MT0": "$MT0",
                        "WRLFTR_0": "$WRLFTR_0",
                        "WRLFTR_1": "$WRLFTR_1",
						"TRM": "$TRM",
                        "timestamp": "$timestamp"
                    }
                },
            }
        },
        {
            "$sort": {
                "_id": 1.0,
                "timestamp": 1.0                        // order by timestamp
            }
        },
        {
            "$unwind": {
                "path": "$details",
                "includeArrayIndex": "array_idx"        // row_number
            }
        },
        {
            "$match": {                                // select only order 30 - 180 in array
                "$and": [
                    {
                        "array_idx": {
                            "$gte": 30.0
                        }
                    },
                    {
                        "array_idx": {
                            "$lte": 180.0
                        }
                    }
                ]
            }
        },
        {
            "$sort": {                                // sort TRM for percentile calculation
                "details.TRM": 1.0
            }
        },        
        {
            "$group": {                                // group parameter back to array by partition "_id" 
                "_id": "$_id",
                "timestamp": { "$push": {"$toDate":"$details.timestamp"} },
                "MT0": { "$push": {"$toDouble":"$details.MT0"} },
                "TRM": { "$push": {"$toDouble":"$details.TRM"} },
                "WRLFTR_0": { "$push": {"$toDouble":"$details.WRLFTR_0"} },
                "WRLFTR_1": { "$push": {"$toDouble":"$details.WRLFTR_1"} },
            }
        },			
        {                                               // reporting "_id" and calculating 
            "$project": {
                "_id": 0,
                "TID": "$_id.TID",
                "Col": {
                    "$toDecimal": "$_id.Col"
                },
                "Row": {
                    "$toDecimal": "$_id.Row"
                },
                "CSN": {
                    "$substr": [
                        "$_id.CSN",
                        2.0,
                        -6.0
                    ]
                },
                "Opt": "$_id.Opt",
                "PID": "$_id.PID",                 
                "DSN1": "$_id.DSN1",
                "DSN2": "$_id.DSN2",
                "CID": "$_id.CID",                
                "MAX_TRM": {
                    "$max": "$TRM"
                },
                "Q3_TRM": { 
                    "$arrayElemAt": [ "$TRM", {"$floor": { "$multiply": [0.75,{"$size": "$TRM"}] }} ]  
                },
                "AVG_TRM": {
                    "$avg": "$TRM"
                },
                "MAX_WRLFTR_0": {
                    "$max": "$WRLFTR_0"
                },
                "MAX_WRLFTR_1": {
                    "$max": "$WRLFTR_1"
                },
                "MAX_MT0": {
                    "$max": "$MT0"
                },
                "MAX_timestamp": {
                    "$max": "$timestamp"
                },
                "DAY_ID":{"$concat":
								[
									{"$substr":[{"$toString":{"$max": "$timestamp"}}, 0, 4]}, 
									{"$substr":[{"$toString":{"$max": "$timestamp"}}, 5, 2]},
									{"$substr":[{"$toString":{"$max": "$timestamp"}},8,2]} 
								]
						},
            }
        },
    ],
    {
        "allowDiskUse": true
    }
);

Maybe this one will work.

db.adminCommand({setParameter: 1, internalQueryMaxPushBytes: 1048576000});

Will try and update.