CODEWITHSUNDEEP
mongodbaggregation-frameworkmemory-limit
acube
acube

Reputation: 591

Mongo error when using aggregation: sort exceeded memory limit

I get the mongo error exceeded memory limit with error code 16819 when I use aggregation sort.

Im using mongo 2.6.

The query is as follows:

db.BASE_TABLE_CREATION_ExecuteHiveScript_26_V0.aggregate([
     { "$project" : { "visitor_localdate" : 1 , "_id" : 0}}, 
     { "$sort" : { "visitor_localdate" : -1}}
])

Upvotes: 49

Views: 78668

Answers (9)

sonia kaushal
sonia kaushal

Reputation: 565

In case you are using aggregate queries.Put an index on the field by which you are sorting and then use sort operator.

Note: Place the sort operator at the beginning of the pipeline or before the $project, $unwind, and $group aggregation operators. If $project, $unwind, or $group occur prior to the $sort operation, $sort cannot use any indexes.

https://docs.mongodb.com/manual/reference/operator/aggregation/sort

Upvotes: 34

DragoRoff
DragoRoff

Reputation: 65

For me worked a combination of factors:

  1. As already have been told I used { allowDiskUse: true } for my typeorm aggregation

  2. I had to put { $sort: {} } before the aggregation.

    [{ $sort: {} }, ...aggregation]

And finally it worked!

Upvotes: 2

Adlan Bousehaba
Adlan Bousehaba

Reputation: 31

For Mongoose

await Model.aggregate([{ $match: { foo: 'bar' } }]).allowDiskUse(true);

from https://mongoosejs.com/docs/api.html#query_Query-allowDiskUse

Upvotes: 3

Massimo Rebuglio
Massimo Rebuglio

Reputation: 341

For those looking for an answer for pymongo

and obtain AttributeError: 'dict' object has no attribute '_txn_read_preference'

This works:

db.coll.aggregate([], allowDiskUse=True)

Upvotes: 2

Charlie
Charlie

Reputation: 2263

In my scenerio, I fixed it by adding an index for the sorted column

Upvotes: 6

Nader Gharibian Fard
Nader Gharibian Fard

Reputation: 8115

To fix it, enable the allowDiskUse option in your query :

The solution

Reference: Memory Restrictions

In MongoDB, the maximum memory limit for in-sort is 100M, and if you perform a larger sort, you need to use the allowDiskUse option to write the data to a temporary file to sort.

Add the allowDiskUse option to the query:

db.bigdata.aggregate(
[
 {$group : {_id : "$range", total : { $sum : 1 }}},
 {$sort : {total : -1}}
],
 {allowDiskUse: true}
);

Upvotes: 5

Devkinandan Chauhan
Devkinandan Chauhan

Reputation: 1925

Use { allowDiskUse: true } just after aggregation pipeline, like below:

db.collectionOrView.aggregate([], { allowDiskUse: true });

Upvotes: 22

wdberkeley
wdberkeley

Reputation: 11671

You don't need aggregation for this at all. Use the query 

db.BASE_TABLE_CREATION_ExecuteHiveScript_26_V0.find({}, { "_id" : 0, "visitor_localdate" : 1 }).sort({ "visitor_localdate" : -1 })

and put an index on visitor_localdate. This is simpler and faster than aggregation.

Upvotes: 5

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236228

By default aggregation in MongoDB occurs in memory and pipeline stages have limit of 100 Mb RAM. Looks like you have exceeded this threshold. To handle large dataset you should enable aggregation pipeline stages to write data to temporary files. Use allowDiskUse option for that:

db.BASE_TABLE_CREATION_ExecuteHiveScript_26_V0.aggregate([
    { "$project" : { "visitor_localdate" : 1 , "_id" : 0}},
    { "$sort" : { "visitor_localdate" : -1}}
], { "allowDiskUse" : true })

Upvotes: 59

Related Questions