16

Within Ubuntu I've created a MongoDB Db collection containing 1 million records, and when trying to run a sort command against it, I'm getting the following error:

 "$err" : "Runner error: Overflow sort stage buffered data usage of 33555002 bytes exceeds internal limit of 33554432 bytes",
 "code" : 17144

Please can anyone describe how to increase the internal limit so that I can execute this and some other large scale commands against the data?

Pabi
  • 7,401
  • 3
  • 40
  • 49
Jon295087
  • 705
  • 3
  • 8
  • 13
  • I doubt you can. The problem probably is with your statement: does EXPLAIN show you are using an INDEX? If not you need to add an INDEX to your table. – Rinzwind Jul 24 '14 at 08:43
  • Hi Rinzwind. I'm trying to compare performance, so I'm looking to run sorts of the data using noindex, primary index, and a secondary index. Are you suggesting it's not possible to run a sort of this scale without an index? – Jon295087 Jul 24 '14 at 09:09
  • Ah. Well I tend to examine performance by using EXPLAIN (as soon as EXPLAIN does not give me low record counts the performance is bad). You probably will need to limit your records if you want to do it like this. – Rinzwind Jul 24 '14 at 09:22
  • I've found that MongoDB will process around 25000 documents in this dataset, but falls over with anything greater... I guess it's just a limitation of the database? Thank you for your comments. – Jon295087 Jul 24 '14 at 20:20

2 Answers2

23

This can happen because of a bug like SERVER-13611 (so make sure you are on the latest version), or because you are trying to sort on a sparse index in 2.6, but more usually it is because you are simply attempting to sort too many records in memory without an index.

The specific limit you are hitting is intentional and is documented here - it cannot be changed, so you need to reduce the set of results, or use an index etc. to perform the sort.

Update (November 2014): The upcoming 2.8 release (2.8.0-rc0 as of writing this) does now allow this setting to be tweaked, as follows:

db.adminCommand({setParameter: 1, internalQueryExecMaxBlockingSortBytes: <limit in bytes>})

The default value is 32MiB (33554432 bytes) and should be adjusted with care - large in-memory sorts can cause your database to grind to a halt (which is why there was a limit in the first place).

Adam C
  • 1,929
2

I ran into the issue too when sorting and paginating 200K+ records. The easiest solution seems to be to add an index ( for the attributes you are sorting on.

max kaplan
  • 121