京公网安备11010502063487号“Sort exceeded memory limit of 33554432 bytes” 是 MongoDB 老版本里最常见的报错,教程总说“加 allowDiskUse”或“建索引”。这篇用 MongoDB 9.0.4 在本机重新核对:新版本里它什么时候还会出现、$sort 和 $group 的原文有什么区别、四种修法各自的耗时,以及错误里的数字能告诉你什么。
getParameter allowDiskUseByDefault = True
getParameter internalQueryMaxBlockingSortMemoryUsageBytes = 104857600
getParameter internalQuerySlotBasedExecutionMaxStaticIndexScanIntervals = 1000
find().sort({x: 1}):ok,返回 600000 个文档,用时 1.06 s
explain executionStats:stages = ['SORT', 'COLLSCAN'],usedDisk = True9.0.4 上 allowDiskUseByDefault 默认真,超过 100 MB 的排序默认成功。
find().sort().allowDiskUse(false):OperationFailure code=292 codeName=QueryExceededMemoryLimitNoDiskUseAllowed: Executor error during find command: t.big :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting.,用时 0.08 s
aggregate([{$sort: {x: 1}}], allowDiskUse: false):OperationFailure code=292 codeName=QueryExceededMemoryLimitNoDiskUseAllowed: Executor error during aggregate command on namespace: t.big :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting.,用时 0.08 s
aggregate $group(按 x 分组并 $push 全部 p),allowDiskUse: false:OperationFailure code=292 codeName=QueryExceededMemoryLimitNoDiskUseAllowed: Executor error during aggregate command on namespace: t.big :: caused by :: Exceeded memory limit for group, but didn't allow external spilling; pass allowDiskUse:true to opt in,用时 0.35 s
同一个 $group,allowDiskUse: true:ok,返回 599841 个分组,用时 3.08 s显式 allowDiskUse(false),或被关掉默认落盘时;$sort 与 $group 措辞不同。
建索引 {x: 1} 用时 0.4 s
有索引后 find().sort({x: 1}).allowDiskUse(false):ok,返回 600000 个,用时 1.15 s;执行计划 stages = ['FETCH', 'IXSCAN'],totalDocsExamined = 600000
无索引 + limit(10),allowDiskUse(false):ok,返回 10 个,用时 0.09 s;stages = ['SORT', 'COLLSCAN']
无索引 + 投影只要 x(不返回 200 字节的 p,排序的数据量小了),allowDiskUse(false):ok,返回 600000 个,用时 0.90 s
无索引 + 默认(允许落盘)排序 120 MB:ok,用时 1.04 s(作为对照)
把 internalQueryMaxBlockingSortMemoryUsageBytes 调到 400 MB,allowDiskUse(false):ok,返回 600000 个,用时 0.75 s索引、limit、缩小投影、调大上限,各有代价;索引省内存不一定更快。
allowDiskUseByDefault=false,find().sort({x: 1}):OperationFailure code=292 codeName=QueryExceededMemoryLimitNoDiskUseAllowed: Executor error during find command: t.big :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting.,用时 0.09 s
同样设置下加 allowDiskUse(true):ok,返回 600000 个,用时 1.26 s
上限调成 1 MB,排序 20 万个文档,allowDiskUse(false):OperationFailure code=292 codeName=QueryExceededMemoryLimitNoDiskUseAllowed: Executor error during find command: t.big :: caused by :: Sort exceeded memory limit of 1048576 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.很多旧部署的设置;错误里的数字是生效的上限。