Sort exceeded memory limit of 33554432 bytes error even after adding index

Working with Data Java Frameworks
, , , ,
1

Hello,
I have a data set of around 90k records hosted on my Mongo Atlas Free tier account. I’m using Spring Boot with MongoTemplate to run paginated queries using Aggregation. One of my queries is like below:


 Executing aggregation: [{ "$match" : {}}, { "$facet" : { "objects" : [{ "$sort" : { "popularity" : -1}}, { "$skip" : 95510}, { "$limit" : 10}], "countFacet" : [{ "$count" : "count"}]}}, { "$project" : { "objects" : 1, "totalCount" : { "$arrayElemAt" : ["$countFacet.count", 0]}}}] in collection my_collection

I’m trying to get the last couple of records when sorted by popularity descending. But I’m faced with the error:
[Request processing failed: org.springframework.data.mongodb.UncategorizedMongoDbException: Command failed with error 292 (QueryExceededMemoryLimitNoDiskUseAllowed): 'PlanExecutor error during aggregation :: caused by :: Sort exceeded memory limit of 33554432 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.'

I’ve tried the following workarounds:

  1. Creating index on popularity column. Both single and composite indexes.
  2. Tried adding allowDiskUse: true but came to know that Free tier doesn’t support it.
  3. Tried creating index in Atlas UI and also from MongoShell but still no use.

Even after trying all the above, I’m still not able to make this work. I have around 5 fields based on which I will sort the data. I have created individual indexes on all 5 fields and a composite index containing all the fields. But for some reason, the indexes doesn’t seem to work.

Also, I make my connecting using the below string from Java :
mongodb+srv://<username>:<passowrd>@<hostname>/?retryWrites=true&w=majority

Please help me how to fix this as I’ve been breaking my head with this for over a week.

2

When using the facets it will not make use of indexes.
Instead of using using facets and skip, sort and filter using object id or similar.

3

For pagination, I kind of need to use facets to get the total objects returned etc. Is there any other way I can modify the query to return the same response but also make use of the indexes?

4

@Vishnu_Ramana Can you modify the aggregation operation to include the “allowDiskUse:true” option when invoking the aggregation.

5

You could try to $sort before the $facet.

You could also eliminate your empty $match

since it might throw off of the optimizer.

6

Just to be more specific about the above.

7

Thanks for the more complete anwer Steeve, I was out and about so just popped in the reply!

1 Like
8

Thank you all for your help. I will try the suggestions and get back.

9

Thanks, everyone. I was able to fix the issue by making 2 calls instead of one single call to Mongodb

The main reason I used the facet was to make a single call to get the results from the criteria and also the total results for that specific criteria. However, since this did not make use of indexes, my query started failing when the amount of data to process in memory exceeded what MongoDB is set to by default.

My resolution was to use 2 separate calls. One call for getting the results for the criteria which does not use facets and only has the limit sort and find criteria. Another call to only get the count of rows for that same criteria. Both the calls make use of indexes now so speed is not an issue. However, there are 2 round-trip calls to the application server and database which is unavoidable.

I hope this helps someone who stumbles across this in the future :slight_smile: