[SERVER-4566] In new aggregation framework, $sort, $limit in the pipeline seems loading all the matched data into memory. When we tried to improve the performance by leveraging multi thread aggregation, this makes it much slower than single thread Created: 27/Dec/11 Updated: 11/Jul/16 Resolved: 30/Jan/12 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 2.1.1 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Xiaofeng Wu | Assignee: | Chris Westin |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
OSX 10.6, Java, mongodb is built from Dec. 15 master branch. |
||
| Issue Links: |
|
||||||||||||
| Operating System: | OS X | ||||||||||||
| Participants: | |||||||||||||
| Description |
|
Overall new aggregation framework is faster than map-reduce in our application. But when I tried to further improve the performance by sending multiple aggregation commands in multiple threads, then get the final aggregation done in app space, I noticed each thread will load lot of data to memory which makes each command take minutes to finish. (I do have index on sort key) |
| Comments |
| Comment by Chris Westin [ 30/Jan/12 ] |
|
Short-term fix was |
| Comment by Chris Westin [ 29/Dec/11 ] |
|
No, you want to specify the sort, but the optimization I'm referring to will convert that into an index scan, so the data won't be loaded into memory to be sorted. Given the two indexes you have, they will compete over the predicate on sId vs the sort on intId. The predicate will want to use the composite index, while the sort will want to use the plain intId index. At present, it seems like the optimizer will choose the composite index, which means you'll still have to sort all the data in order to do it this way. If you have multiple $sort/$skip/$limit threads running in parallel, they will all do that. You may have to wait for $out to collect the output of the sort after it has been done once, and then use that as the input for the next stage. For this particular case, there's a different optimization that seems like it would be a better idea. If there were a composite index on <sId, aid>, then we could take advantage of SERVER-4507 (but note that's further off in the future). |
| Comment by Xiaofeng Wu [ 29/Dec/11 ] |
|
Thanks for your explanation. I have a composite index for sId and intId, and another index for intId. Can I just skip $sort? |
| Comment by Chris Westin [ 28/Dec/11 ] |
|
This is not surprising, a sort requires all the data in order to be carried out. However, if you have the required indexes, that step can be skipped, and the optimization described by Just to be sure that optimization will apply in this case, can you tell me what indexes you've got on the surveyResponse collection? |
| Comment by Xiaofeng Wu [ 28/Dec/11 ] |
|
I have tested with 3 threads, 4 threads. |
| Comment by Eliot Horowitz (Inactive) [ 28/Dec/11 ] |
|
How many were you running in parallel? |
| Comment by Xiaofeng Wu [ 28/Dec/11 ] |
|
full command for multi thread aggregation: db.runCommand( { aggregate : "surveyResponse", pipeline : [ , { status: -50 }, { status: -60 } ] } }, , { $limit:1000}, , { $unwind : "$questions.answers"}, full command without using multi thread: db.runCommand( { aggregate : "surveyResponse", pipeline : [ , { status: -50 }, { status: -60 } ] } }, , { $unwind : "$questions.answers"}, |
| Comment by Eliot Horowitz (Inactive) [ 28/Dec/11 ] |
|
Can you send the full command? |
| Comment by Xiaofeng Wu [ 27/Dec/11 ] |
|
Here're the commands we generated in different threads: , { status: -50 }, { status: -60 } ] } }, , db.runCommand( { aggregate : "myCollection", pipeline : [ , { status: -50 }, { status: -60 } ] } }, , { $limit:1000}, db.runCommand( { aggregate : "myCollection", pipeline : [ , { status: -50 }, { status: -60 } ] } }, , { $limit:1000}, |