[SERVER-54095] Improve SBE perf for the bestbuy_query benchmark suite Created: 28/Jan/21  Updated: 24/Apr/21  Resolved: 24/Apr/21

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

Type: Task Priority: Major - P3
Reporter: Drew Paroski Assignee: Drew Paroski
Resolution: Done Votes: 0
Labels: qexec-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-51655 Investigate sys-perf benchmark perfor... Closed
Participants:

 Description   

When SBE is enabled, the find() commands from the find_project-noAgg and find_project_limit_skip-noAgg benchmarks (from the bestbuy_query suite) are noticeably slower vs. the classic execution engine.

Instructions on how to run the find() commands:

1) Download the bestbuy dataset.

2) Start mongod. If you want to enabled SBE, pass '--setParameter "featureFlagSBE=true"' on the command line.

3) Load the bestbuy dataset using the following command: 

mongorestore --gzip --archive=bestbuyproducts.bson.gz 

4) Launch the mongo shell and run the following commands to prepare your environment:

> function millis(command) { const t1 = new Date(); const result = command(); const t2 = new Date(); print("time: " + (t2 - t1) + "ms"); return (t2-t1); }
> use bestbuy

5) Run the following commands multiple times to measure the performance of the find() commands:

## find_project-noAgg:
> millis(() => db.products.find({"type":"Music"}, {"_id":1.0,"sku":1.0,"productId":1.0,"name":1.0,"source":1.0,"type":1.0,"startDate":1.0,"new":1.0,"active":1.0,"lowPriceGuarantee":1.0,"activeUpdateDate":1.0,"regularPrice":1.0,"salePrice":1.0,"onSale":1.0,"planPrice":1.0,"priceWithPlan":1.0,"advertisedPriceRestriction":1.0,"minimumDisplayPrice":1.0,"restrictedSalePrice":1.0,"icrType":1.0,"icrStartDate":1.0,"icrEndDate":1.0,"priceRestriction":1.0,"priceUpdateDate":1.0,"digital":1.0,"preowned":1.0,"carrierPlans":1.0,"technologyCode":1.0,"carrierModelNumber":1.0,"earlyTerminationFees":1.0,"outletCenter":1.0,"secondaryMarket":1.0,"frequentlyPurchasedWith":1.0,"accessories":1.0,"relatedProducts":1.0,"techSupportPlans":1.0,"crossSell":1.0,"salesRankShortTerm":1.0,"salesRankMediumTerm":1.0,"salesRankLongTerm":1.0,"bestSellingRank":1.0,"url":1.0,"spin360Url":1.0,"mobileUrl":1.0,"affiliateUrl":1.0,"addToCartUrl":1.0,"affiliateAddToCartUrl":1.0,"linkShareAffiliateUrl":1.0,"linkShareAffiliateAddToCartUrl":1.0,"search":1.0,"upc":1.0,"productTemplate":1.0,"categoryPath":1.0,"lists":1.0,"customerReviewCount":1.0,"customerReviewAverage":1.0,"customerTopRated":1.0,"format":1.0,"freeShipping":1.0,"freeShippingEligible":1.0,"hardGoodType":1.0,"inStoreAvailability":1.0,"inStoreAvailabilityText":1.0,"inStoreAvailabilityTextHtml":1.0,"inStoreAvailabilityUpdateDate":1.0,"itemUpdateDate":1.0,"onlineAvailability":1.0,"onlineAvailabilityText":1.0,"onlineAvailabilityTextHtml":1.0,"onlineAvailabilityUpdateDate":1.0,"releaseDate":1.0,"shippingCost":1.0,"shipping":1.0,"shippingLevelsOfService":1.0,"specialOrder":1.0,"shortDescription":1.0,"shortDescriptionHtml":1.0,"class":1.0,"classId":1.0,"subclass":1.0,"subclassId":1.0,"department":1.0,"departmentId":1.0,"buybackPlans":1.0,"protectionPlans":1.0,"productFamilies":1.0,"description":1.0,"manufacturer":1.0,"modelNumber":1.0,"image":1.0,"largeFrontImage":1.0,"mediumImage":1.0,"thumbnailImage":1.0,"largeImage":1.0,"alternateViewsImage":1.0,"angleImage":1.0,"backViewImage":1.0,"energyGuideImage":1.0,"leftViewImage":1.0,"accessoriesImage":1.0,"remoteControlImage":1.0,"rightViewImage":1.0,"topViewImage":1.0,"condition":1.0,"inStorePickup":1.0,"friendsAndFamilyPickup":1.0,"homeDelivery":1.0,"quantityLimit":1.0,"fulfilledBy":1.0,"bundledIn":1.0,"color":1.0,"depth":1.0,"dollarSavings":1.0,"percentSavings":1.0,"tradeInValue":1.0,"height":1.0,"orderable":1.0,"weight":1.0,"shippingWeight":1.0,"width":1.0,"warrantyLabor":1.0,"warrantyParts":1.0,"longDescription":1.0,"longDescriptionHtml":1.0,"details":1.0,"includedItemList":1.0,"features":1.0,"offers":1.0,"marketplace":1.0,"listingId":1.0,"sellerId":1.0,"shippingRestrictions":1.0}).itcount())

## find_project_limit_skip-noAgg:
> millis(() => db.products.find({"type":"Music"}, {"_id":1.0,"sku":1.0,"productId":1.0,"name":1.0,"source":1.0,"type":1.0,"startDate":1.0,"new":1.0,"active":1.0,"lowPriceGuarantee":1.0,"activeUpdateDate":1.0,"regularPrice":1.0,"salePrice":1.0,"onSale":1.0,"planPrice":1.0,"priceWithPlan":1.0,"advertisedPriceRestriction":1.0,"minimumDisplayPrice":1.0,"restrictedSalePrice":1.0,"icrType":1.0,"icrStartDate":1.0,"icrEndDate":1.0,"priceRestriction":1.0,"priceUpdateDate":1.0,"digital":1.0,"preowned":1.0,"carrierPlans":1.0,"technologyCode":1.0,"carrierModelNumber":1.0,"earlyTerminationFees":1.0,"outletCenter":1.0,"secondaryMarket":1.0,"frequentlyPurchasedWith":1.0,"accessories":1.0,"relatedProducts":1.0,"techSupportPlans":1.0,"crossSell":1.0,"salesRankShortTerm":1.0,"salesRankMediumTerm":1.0,"salesRankLongTerm":1.0,"bestSellingRank":1.0,"url":1.0,"spin360Url":1.0,"mobileUrl":1.0,"affiliateUrl":1.0,"addToCartUrl":1.0,"affiliateAddToCartUrl":1.0,"linkShareAffiliateUrl":1.0,"linkShareAffiliateAddToCartUrl":1.0,"search":1.0,"upc":1.0,"productTemplate":1.0,"categoryPath":1.0,"lists":1.0,"customerReviewCount":1.0,"customerReviewAverage":1.0,"customerTopRated":1.0,"format":1.0,"freeShipping":1.0,"freeShippingEligible":1.0,"hardGoodType":1.0,"inStoreAvailability":1.0,"inStoreAvailabilityText":1.0,"inStoreAvailabilityTextHtml":1.0,"inStoreAvailabilityUpdateDate":1.0,"itemUpdateDate":1.0,"onlineAvailability":1.0,"onlineAvailabilityText":1.0,"onlineAvailabilityTextHtml":1.0,"onlineAvailabilityUpdateDate":1.0,"releaseDate":1.0,"shippingCost":1.0,"shipping":1.0,"shippingLevelsOfService":1.0,"specialOrder":1.0,"shortDescription":1.0,"shortDescriptionHtml":1.0,"class":1.0,"classId":1.0,"subclass":1.0,"subclassId":1.0,"department":1.0,"departmentId":1.0,"buybackPlans":1.0,"protectionPlans":1.0,"productFamilies":1.0,"description":1.0,"manufacturer":1.0,"modelNumber":1.0,"image":1.0,"largeFrontImage":1.0,"mediumImage":1.0,"thumbnailImage":1.0,"largeImage":1.0,"alternateViewsImage":1.0,"angleImage":1.0,"backViewImage":1.0,"energyGuideImage":1.0,"leftViewImage":1.0,"accessoriesImage":1.0,"remoteControlImage":1.0,"rightViewImage":1.0,"topViewImage":1.0,"condition":1.0,"inStorePickup":1.0,"friendsAndFamilyPickup":1.0,"homeDelivery":1.0,"quantityLimit":1.0,"fulfilledBy":1.0,"bundledIn":1.0,"color":1.0,"depth":1.0,"dollarSavings":1.0,"percentSavings":1.0,"tradeInValue":1.0,"height":1.0,"orderable":1.0,"weight":1.0,"shippingWeight":1.0,"width":1.0,"warrantyLabor":1.0,"warrantyParts":1.0,"longDescription":1.0,"longDescriptionHtml":1.0,"details":1.0,"includedItemList":1.0,"features":1.0,"offers":1.0,"marketplace":1.0,"listingId":1.0,"sellerId":1.0,"shippingRestrictions":1.0}).skip(100000).limit(100000).itcount())

Here is how long each of the find() commands above took on my local machine when SBE was enabled vs. when SBE was disabled (as of 1/28/2021):
  29753ms (SBE) vs. 25391ms (classic) -- find_project-noAgg
  2429ms (SBE) vs. 2119ms (classic) – find_project_limit_skip-noAgg

Note: The examples above will print out the total amount of time that each "find(..).itcount()" shell command takes from the client's perspective. If you are interested in how long each individual mongo command takes on the server, consider using the slow query timer.


Generated at Thu Feb 08 05:32:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.