[SERVER-7694] external sort for find command Created: 16/Nov/12 Updated: 09/Oct/19 Resolved: 02/Oct/19 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | 4.3.1 |
| Type: | New Feature | Priority: | Major - P3 |
| Reporter: | Aaron Staple | Assignee: | David Storch |
| Resolution: | Done | Votes: | 12 |
| Labels: | query_triage | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| Sprint: | Query 2019-10-07 | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
Right now when query results must be sorted before being returned, a top-N sort is performed in memory with a memory footprint cap enforced. The goal for this ticket is move to external sorting if too much memory is required, see discussion in |
| Comments |
| Comment by David Storch [ 02/Oct/19 ] | |
|
As of development version 4.3.1 (which will evolve into the 4.4 GA release), users can opt into allowing disk use for sorting in a find operation. This ability has existed for many releases for aggregate operations: see this documentation page showing an example of an agg pipeline that requires an external sort. The same functionality is now available for find operations. In the shell, the allowDiskUse:true parameter to the find command can be set using the following syntax:
By default in versions >=4.3.1, mongod will begin spilling data to disk once the memory requirements exceed 100MB. This maximum memory consumption threshold can be configured at runtime or at startup using the internalQueryExecMaxBlockingSortBytes setParameter (see this page for details on how to configure setParameters). If internalQueryExecMaxBlockingSortBytes is exceeded when allowDiskUse is true, data will be spilled to disk during execution of the sort; if allowDiskUse is false, the query will fail. As part of this change, the execution statistics reported by the explain command for the SORT stage have changed slightly:
| |
| Comment by Githook User [ 02/Oct/19 ] | |
|
Author: {'username': 'dstorch', 'email': 'david.storch@mongodb.com', 'name': 'David Storch'}Message: For find commands that request a sort, the query execution | |
| Comment by Luke Thompson [ 10/Feb/14 ] | |
|
Thanks dan@10gen.com, cool, that's a great step. I think that it doesn't address the core of the problem though. We (and probably lots of others) may have written code and deployed it to production which is basically a ticking time-bomb because of this limitation. Right now, our datasets are small enough that the sort is occurring in memory, but as the data grows we near the memory cap. One day a single record will be added to a collection, and the app will start crashing. A warning in the log that a query is using a large amount of resources for sorting and should have an index would be great, but the DB should keep responding even if the query slows when sort data sets no longer fit in memory. | |
| Comment by Daniel Pasette (Inactive) [ 10/Feb/14 ] | |
|
lthompson@infomedia.com.au, starting in the 2.6 MongoDB release, you should be able to use the aggregation framework for unindexed sorts. See | |
| Comment by Luke Thompson [ 31/Jan/14 ] | |
|
Thanks in advance for addressing this. I just had a commercial support ticket (CS-10484) to clear up why this behaviour was happening: | |
| Comment by Jeffrey Yemin [ 26/Apr/13 ] | |
|
As | |
| Comment by Eliot Horowitz (Inactive) [ 18/Jan/13 ] | |
|
@aaron - yes, exactly. | |
| Comment by Aaron Staple [ 17/Jan/13 ] | |
|
|