[SERVER-65044] Investigate perf regressions of the SBE lookup in microbenchmarks Created: 29/Mar/22  Updated: 27/Oct/23  Resolved: 12/Apr/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Task Priority: Major - P3
Reporter: Yoon Soo Kim Assignee: Yoon Soo Kim
Resolution: Gone away Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Sprint: QE 2022-04-18
Participants:
Linked BF Score: 135

 Description   

taking a look at BF-24743, I think it’s indeed the SBE lookup perf regressions.

Regressed test cases are Aggregation.Lookup / Aggregation.Lookup.LocalArray / Aggregation.IdentityView.Lookup / Aggregation.IdentityView.Lookup.LocalArray.

There are no indexes on the foreign collection, looking at the data populator (https://github.com/mongodb/mongo-perf/blob/b156b220d51a61c2a7496f44f5cc0a7d6b46c64a/testcases/pipelines.js#L873-L931).

We use basicLookupPopulator for “Aggregation.Lookup” and “Aggregation.IdentityView.Lookup” test cases and use basicArrayLookupPopulator for “Aggregation.Lookup.LocalArray” and “Aggregation.IdentityView.Lookup.LocalArray” test cases.

And the pipeline is {$lookup: {from: “#B_COLL_lookup”, localField: “foreignKey”, foreignField: “_id”, as: “match”}} and we don’t use allowDiskUse . So, they are pushed down to the SBE and it will be NLJ-based lookup.

Aggregation.Lookup / Aggregation.IdentityView.Lookup test cases are here:
https://github.com/mongodb/mongo-perf/blob/b156b220d51a61c2a7496f44f5cc0a7d6b46c64a/testcases/pipelines.js#L1005-L1017

Aggregation.Lookup.LocalArray / Aggregation.IdentityView.Lookup.LocalArray test cases are here:
https://github.com/mongodb/mongo-perf/blob/b156b220d51a61c2a7496f44f5cc0a7d6b46c64a/testcases/pipelines.js#L1050-L1062



 Comments   
Comment by Yoon Soo Kim [ 12/Apr/22 ]

This regression happened when we enable the feature on the all-feature-flags build variant and yet hadn't enabled the SBE INLJ. The query was using the classic INLJ but after enabling the feature on all-feature-flags build variant, the query started using the SBE NLJ which is supposed to be much slower than the INLJ.

Now we enabled the SBE INLJ and so we're seeing 160% ~ 570% perf improvements in the same workloads.

Closing this as "Gone away"

Generated at Thu Feb 08 06:01:44 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.