阅读中文版本

MongoDB Sort Exceeded Memory Limit — allowDiskUse Needed

Fix "Sort exceeded memory limit" by allowing disk use, adding a supporting index, and reducing the sorted result set.

mongosh --eval "db.orders.aggregate([{ $sort: { total: -1 } }]).toArray()"

mongosh --eval "db.orders.find({}).sort({ total: -1 }).explain('executionStats').queryPlanner.winningPlan"

Symptoms

The error “MongoDB Sort Exceeded Memory Limit — allowDiskUse Needed” points at a specific failure mode. This guide documents the symptoms you can observe, the commands that isolate the cause, the root cause, the fix, and the checks that prove the fix works.

Observed symptoms include:

• MongoServerError: Sort exceeded memory limit of 104857600 bytes
• An aggregation or find with sort fails on large collections
• The same query works on a small dataset

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Confirm the stage and limit from the error

mongosh --eval "db.orders.aggregate([{ $sort: { total: -1 } }]).toArray()"

The error names the sort stage and the byte limit, confirming an in-memory sort.

Step 2: Check index usage for the sort

mongosh --eval "db.orders.find({}).sort({ total: -1 }).explain('executionStats').queryPlanner.winningPlan"

A SORT stage (not an index scan) shows the sort is not covered by an index.

Likely contributors, in order of frequency:

• The sort cannot use an index and is performed in memory beyond the 100MB limit
• A blocking stage such as $group precedes $sort, defeating index usage
• The result set is inherently large and unsupported by an index

Root Cause

MongoDB performs a sort in memory when no index can supply the order, and aborts it once it exceeds the internal 100MB limit. This is common when a blocking stage precedes $sort or the sort field is unindexed.

The Fix

Add allowDiskUse(true) for the operation, create an index whose prefix matches the sort key, move $match/$sort before $group, and limit the result set with $limit.

Watch out: allowDiskUse trades memory for disk I/O and slows the query; use it as a stopgap while adding the right index.

// allow disk use as a stopgap
const results = await db.collection('orders')
  .aggregate([{ $sort: { total: -1 } }, { $limit: 1000 }], { allowDiskUse: true })
  .toArray();

// the real fix: index supporting the sort, and limit the set
db.orders.createIndex({ total: -1, _id: -1 });

// push $match/$sort before $group when possible
db.orders.aggregate([
  { $match: { status: 'paid' } },
  { $sort: { total: -1 } },
  { $limit: 1000 }
]);

Verify and Prevent

Rerun the query and confirm it completes; verify the winning plan uses an IXSCAN and reports no SORT stage.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.