[SERVER-27829] Reduce performance impact of DocumentSourceCursor batching Created: 26/Jan/17  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Backlog - Query Execution
Resolution: Unresolved Votes: 0
Labels: QFB, performance, query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-31072 reorder $limit before $lookup in pipe... Closed
is related to SERVER-47370 Projection pushdown can hinder perfor... Backlog
Assigned Teams:
Query Execution
Sprint: Query 2017-05-29
Participants:
Case:

 Description   

On a large collection with index on "orderdate" compare find and equivalent aggregate first batches:

command: find { find: "orders", filter: { orderdate: { $gte: new Date(759024000000) } }, sort: { orderdate: -1.0 }, projection: { _id: 0.0, orderdate: 1.0 } } planSummary: IXSCAN { orderdate: 1 } cursorid:53712819499 keysExamined:101 docsExamined:0 numYields:0 nreturned:101 reslen:2903  0ms
command: aggregate { aggregate: "orders", pipeline: [ { $match: { orderdate: { $gte: new Date(759024000000) } } }, { $sort: { orderdate: -1.0 } }, { $project: { _id: 0.0, orderdate: 1.0 } } ], cursor: {} } planSummary: IXSCAN { orderdate: 1 } cursorid:52250968997 keysExamined:20972 docsExamined:0 numYields:163 nreturned:101 reslen:2903 11ms

The main performance difference seems to come from keysExamined which is 101 in case of find and equal to I'm not sure what in the second case (there are 2406 distinct orderdate values, collection size is 1500000 and this filter reduces it to 1034289 documents).



 Comments   
Comment by Asya Kamsky [ 27/Jan/17 ]

It looks at the keys for first 4MBs worth of documents, vs find 101 keys. Note it caused aggregation to take 11ms vs 0ms for find.

Comment by David Storch [ 27/Jan/17 ]

As part of the work to make find and agg share performance properties, we should look into getting rid of batching inside DocumentSourceCursor. I don't think it would be hard to make pipelines like this fully streaming.

Comment by Charlie Swanson [ 27/Jan/17 ]

This is because the cursor stage of the aggregation will internally batch results. The current batch size is 4MB, but was recently reduced from 16MB in SERVER-27406, so it's possible your version is still using 16MB. To confirm this is the problem, you can change the buffer size via a server parameter:

> db.adminCommand({setParameter: 1, internalDocumentSourceCursorBatchSizeBytes: <new batch size>})

Of course this is a tradeoff, we use a large batch size to hide the overhead of dropping and re-acquiring the lock. A query will hold the lock until it has filled up its first batch to return to the user, but an aggregation will only hold a lock during the cursor stage. If we only returned one document at a time from the cursor stage, we would have to re-acquire and drop the lock each time a result is requested from the cursor, which would add a lot of overhead. The internal buffering/batching is meant to mitigate this overhead.

If you are only interested in time to first 101, you could add a $limit after the $sort, and it should be passed through to the query layer. You wouldn't be able to ask for more results from that cursor of course.

Generated at Thu Feb 08 04:16:21 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.