Mongo error when using aggregation: sort exceeded memory limit

Question

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}}
])

Sergey Berezovskiy · Answer 1 · 2014-10-15T06:32:41.033

53

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 })

edited Oct 15 '14 at 06:32

answered Oct 15 '14 at 06:27

Sergey Berezovskiy

232,247
41
429
459

Hi Sergey, I tried this. It dint worked.. I get the same Exception. – acube Oct 16 '14 at 10:21
@acube make sure you really tried this. If you'll look at github sources of mongodb, then you'll find [MongoDB Error Codes](https://github.com/mongodb/mongo/blob/master/docs/errors.md) and `16819` code has very clear explanation comment and solution steps: *Sort exceeded memory limit of bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.* – Sergey Berezovskiy Oct 16 '14 at 12:05
6

when using mongoose use this. `db.BASE_TABLE.aggregate([]).allowDiskUse(true);` – kheengz Jan 22 '21 at 13:02
See Sonia's answer below. While this will work, if you can move the sort earlier in the pipeline so that MongoDb can utilize an index, the query will not require a memory sort in the first place. This will make it much more performant on large data sets. – Kip Jul 26 '21 at 21:11
the actual solution is Sonia's answer - https://stackoverflow.com/a/55491963/4516910 – Avraham Shalev Jun 11 '23 at 12:36

score 27 · Answer 2 · edited Aug 31 '23 at 07:50

27

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

edited Aug 31 '23 at 07:50

Niki-Timofe

105
1
6

answered Apr 03 '19 at 09:41

sonia kaushal

495
6
5

thanks for this, another thing is that for large dataset, do stream and pause resume your stream – Ramakant Singh Apr 24 '20 at 13:47
2

Thanks for the **Note**, I ran into this exact problem of running unwind before sort and kept getting this error. swapping them around fixed it! – soapycat Sep 21 '20 at 03:19
Wow this is a great insight. Thank you – Manny Apr 22 '21 at 04:54
1

This is the actual solution. The `allowDiskUse` is just a workaround. – Vincz777 Nov 17 '22 at 08:28
link fix: https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort – dheeraj9499 Mar 29 '23 at 08:03

score 22 · Answer 3 · edited Apr 25 '19 at 08:35

22

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

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

edited Apr 25 '19 at 08:35

4b0

21,981
30
95
142

answered Apr 25 '19 at 08:32

Devkinandan Chauhan

1,785
1
17
42

score 4 · Answer 4 · answered Oct 15 '14 at 15:39

4

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.

answered Oct 15 '14 at 15:39

wdberkeley

11,531
1
28
23

2

I am getting this error :- "errmsg" : "Executor error during find command: OperationFailed: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.", – ak3191 Oct 12 '18 at 19:46

score 4 · Answer 5 · answered Mar 15 '21 at 08:44

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}
);

score 4 · Answer 6 · answered Dec 13 '21 at 03:23

4

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

answered Dec 13 '21 at 03:23

Charlie

2,141
3
19
35

score 3 · Answer 7 · answered Oct 24 '22 at 13:02

3

For Mongoose

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

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

answered Oct 24 '22 at 13:02

Adlan Bousehaba

31
3

score 2 · Answer 8 · answered Oct 31 '22 at 07:22

2

For me worked a combination of factors:

As already have been told I used { allowDiskUse: true } for my typeorm aggregation
I had to put { $sort: {} } before the aggregation.

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

And finally it worked!

answered Oct 31 '22 at 07:22

DragoRoff

55
4

Sorting definitely did it for me too but I had to sort by something. Thanks! – Alvaro Mendez Nov 16 '22 at 23:09

score 1 · Answer 9 · answered Dec 16 '21 at 23:36

1

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)

answered Dec 16 '21 at 23:36

Massimo Rebuglio

311
1
9

Mongo error when using aggregation: sort exceeded memory limit

9 Answers9

Linked

Related