It may happen that if we run a sort operation on a collection, we encounter this error:

Error: error: {
“ok” : 0,
“errmsg” : “Executor error during find command :: caused by :: errmsg: \”Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.\””,
“code” : 96,
“codeName” : “OperationFailed”
}

When doing a sort MongoDb first attempts to retrieve the documents using the order specified in an index. When no index is available it will try to load the documents into memory and sort them there. The catch is that Mongo is configured by default to abort the operation when exceeding an usage of 32 MB. In that case we run into the error described above.

There are various ways to solve this. The most obvious one would be to add the missing index. Another way would be to modify the configuration settings to increase the RAM limit:

db.adminCommand({setParameter: 1, internalQueryExecMaxBlockingSortBytes: 335544320})

With the above command I have setted the RAM limit to 320 MB. If I wanted to check the RAM limit I would run this command:

db.runCommand( { getParameter : 1, "internalQueryExecMaxBlockingSortBytes" : 1 } )

Given that the above query returns the result in bytes you may want to use a conversion tool like this one.

Both commands must be executed in admin DB, otherwise the shell will return an unauthorised error.

Now if we do modify the RAM limit and try to run again the query we may notice – as mentioned before – that the performance can take quite a hit. We may have to wait a bit till the sorting is complete.

Another dirty alternative would be to use the aggregate command to the the sorting with the allowDiskUse set to true as stated in the MongoDB documentation:

Aggregation pipeline stages have maximum memory use limit. To handle large datasets, set allowDiskUse option to true to enable writing data to temporary files

What MongoDb is saying here is that aggregate like sort has a maximum of RAM configured, but when exceeded instead of abort the operation it will continue using disk file storage instead of RAM, if the allowDiskUse has been set to true.

db.getCollection('movies').aggregate( [
      { $sort : { year : 1} }
   ],
   { allowDiskUse: true }
)

As with modifying the RAM configuration this approach will do the trick, but performance wise is in the low side. After we have moved from fast memory processing, to slow disk storage.

If we want to get a good preformance when sorting collection we have to create an index:

db.movies.createIndex( { year: 1 } )

I created an index on the movies collection by year. Now running this query will not only avoid the ram exceeded error, but will be also sort significantly quicker:

db.getCollection('movies').find({}).sort({year:1})