Downloaded 27 times
![EX db.runCommand( { planCacheSetFilter: "orders", query: { status: "A" }, indexes: [ { cust_id: 1, status: 1 }, { status: 1, order_date: -1 } ] } ) IndexFilter determines how the query optimizer will use index for a type of query. Thquery optimizer will ignore the index set by the 1. explain.queryPlanner.indexFilterSet The stage of the optimal execution plan 2. explain.queryPlanner.winningPlan.stage COLLSCAN: Full table scan IXSCAN: Index scan FETCH: Retrieve the specified document based on the indexEmpty text SHARD_MERGE: Return each fragment to the data for merge SORT: Indicates that sorting is done in memor LIMIT: Use limit to limit the number of returns SKIP: Skip using skip IDHACK: Query for _id SHARDING_FILTER: Querying fragmented data by mongos COUNT: Count operation using db.coll.explain().count() COUNTSCAN: Count does not use the stage return when using Count for index COUNT_SCAN: Count uses the stage to return when the index is counted SUBPLA: Stage return of $or query not used to index TEXT: Stage return when using full-text index for queryPROJECTION: The return of the stage when the return field is qualified STAGE executionStats Metrics](https://image.slidesharecdn.com/introduction-to-mongodb-execution-plan-optimizer-final-191115020708/75/Introduction-to-Mongodb-execution-plan-and-optimizer-11-2048.jpg)
![The planner ask plan each time for the next document via call to work() If the plan can give supply document it respond with document, Otherwise the plan respond with needTime If all the all the document have been retrieved then isEOF = 1 Work - Advanced - isEOF [ Algorithm ] "works": 18667 "advanced": 18666 "needTime": 0](https://image.slidesharecdn.com/introduction-to-mongodb-execution-plan-optimizer-final-191115020708/75/Introduction-to-Mongodb-execution-plan-and-optimizer-14-2048.jpg)
![WorkingSet workingSet; PlanStage* rootStage = makeQueryPlan(&workingSet, ...); while (!rootStage->isEOF()) { WorkingSetID result; switch(rootStage->work(&result)) { case PlanStage::ADVANCED: // do something with result WorkingSetMember* member = workingSet.get(result); cout << "Result: " << member->obj << std::endl; break; case PlanStage::IS_EOF: // All done. Will fall out of while loop. break; Work - Advanced - isEOF [ Code ] Cont . . case PlanStage::NEED_TIME: // Need more time. break; case PlanStage::FAILURE: // Throw exception or return error. break; } if (shouldYield) { // Occasionally yield. stage->saveState(); stage->restoreState(); } }](https://image.slidesharecdn.com/introduction-to-mongodb-execution-plan-optimizer-final-191115020708/75/Introduction-to-Mongodb-execution-plan-and-optimizer-15-2048.jpg)
![numWorks baseScore common.advanced workUnits productivity(Plan Produce range[0, 1]) noFetchBonus noSortBonus noIxisectBonus epsilon tieBreakers Scorea MongoDB Plan Ranking Terms](https://image.slidesharecdn.com/introduction-to-mongodb-execution-plan-optimizer-final-191115020708/75/Introduction-to-Mongodb-execution-plan-and-optimizer-22-2048.jpg)
Uploaded byMydbops
Introduction to Mongodb execution plan and optimizer
The document provides an overview of MongoDB's execution plans and optimizers, including how queries are processed and analyzed during execution. It explains various modes of the `explain()` function, detailing the process of query planning, execution stages, and metrics for analyzing performance. Additionally, the document discusses plan caching and its impacts on efficiency when executing queries in MongoDB.
Introduction to Mongodb execution plan and optimizer
- 1. Introduction to MongoDB ExecutionPlan & Optimizer
- 2. Interested in OpenSource technologies Active MongoDB Community Contributor Tech Speaker/Blogger Senior DevOps and MongoDB Consultant Manosh Malai About Me
- 3. Consulting Services Managed Services Focuses on MySQLand MongoDB Mydbops Services
- 4. 250 + ClientsIn 3 Yrs. of Operations Our Clients
- 5. MongoDB Execution PlanAnalysis MongoDB Optimizer and Plan Cache Query in the MongoDB Execution Layer Agenda
- 6. Query In TheMongoDB Execution Layer Client net listener thread_conn Query Decode Operation Context Catalog MapLayer Canonical Plan Plan Executor Optimizer RecordStore & Cursor Snapshot
- 7. A client connectsto MongoDB and creates a new thread to handle all requests. Some Query(Such as insert) does not need to execute the plan, It will directly interact with the engine through RecordStore. Query performs simple processing (standardization) and builds some context data structures into CanonicalQuery. The Plan module is qualified for generating multiple execution plans for the Query, then delivering it to the Optimizer to select the best one and surrendering it to the PlanExecutor. PlanExecutor step by step according to the execution plan to obtain the final data (or perform the update to modify the data). Query Statement Execution Algorithm
- 8. Current version ofExplain has three mode: queryPlanner executionStats allPlansExecution db.collection.find().explain() Execution Plan Analysis MongoDB Execution Plan Analysis
- 9. queryPlanner is thedefault mode of the current version of explain. The queryPlanner mode does not actually query the query statement. Performs an execution plan analysis for the query statement and selects the winning plan. queryPlanner.winningPlan.stage: COLLSCAN Without Index queryPlanner.winningPlan.stage: FETCH With Index queryPlanner Mode
- 10. queryPlanner + executionStatsinformation of the evaluated query. With Index explain.queryPlanner.indexFilterSet explain.queryPlanner.winningPlan.stage explain.executionStats.nReturned explain.executionStats.executionTimeMillis explain.executionStats.totalKeysExamined explain.executionStats.totalDocsExamined explain.executionStats.executionStages.advanced explain.executionStats.executionStages.works explain.executionStats.executionStages.inputStage.docsExamined explain.executionStats.executionStages.isEOF executionStats Mode
- 11. EX db.runCommand( { planCacheSetFilter: "orders", query:{ status: "A" }, indexes: [ { cust_id: 1, status: 1 }, { status: 1, order_date: -1 } ] } ) IndexFilter determines how the query optimizer will use index for a type of query. Thquery optimizer will ignore the index set by the 1. explain.queryPlanner.indexFilterSet The stage of the optimal execution plan 2. explain.queryPlanner.winningPlan.stage COLLSCAN: Full table scan IXSCAN: Index scan FETCH: Retrieve the specified document based on the indexEmpty text SHARD_MERGE: Return each fragment to the data for merge SORT: Indicates that sorting is done in memor LIMIT: Use limit to limit the number of returns SKIP: Skip using skip IDHACK: Query for _id SHARDING_FILTER: Querying fragmented data by mongos COUNT: Count operation using db.coll.explain().count() COUNTSCAN: Count does not use the stage return when using Count for index COUNT_SCAN: Count uses the stage to return when the index is counted SUBPLA: Stage return of $or query not used to index TEXT: Stage return when using full-text index for queryPROJECTION: The return of the stage when the return field is qualified STAGE executionStats Metrics
- 12. executionStats Metrics The numberof returns returned by the query 3. explain.executionStats.nReturned Overall execution time 4. explain.executionStats.executionTimeMillis Number of index scans 5. explain.executionStats.totalKeysExamined Document scans 6. explain.executionStats.totalDocsExamined
- 13. executionStats.inputStage Metrics 8. explain.executionStats.executionStages.works 7.explain.executionStats.executionStages.advanced 9. explain.executionStats.executionStage.isEOF
- 14. The planner askplan each time for the next document via call to work() If the plan can give supply document it respond with document, Otherwise the plan respond with needTime If all the all the document have been retrieved then isEOF = 1 Work - Advanced - isEOF [ Algorithm ] "works": 18667 "advanced": 18666 "needTime": 0
- 15. WorkingSet workingSet; PlanStage* rootStage= makeQueryPlan(&workingSet, ...); while (!rootStage->isEOF()) { WorkingSetID result; switch(rootStage->work(&result)) { case PlanStage::ADVANCED: // do something with result WorkingSetMember* member = workingSet.get(result); cout << "Result: " << member->obj << std::endl; break; case PlanStage::IS_EOF: // All done. Will fall out of while loop. break; Work - Advanced - isEOF [ Code ] Cont . . case PlanStage::NEED_TIME: // Need more time. break; case PlanStage::FAILURE: // Throw exception or return error. break; } if (shouldYield) { // Occasionally yield. stage->saveState(); stage->restoreState(); } }
- 16. As the namesuggests, the allPlanExxecution mode is to perform all execution plans in the executionStats mode. It will show all candidate Rejected Plans. allPlansExecution Mode
- 17. Plan Optimizer andPlan Cache
- 18. Find Matching Cache Entry Empty text EvaluatePlan Performance Generate Candidate Plans Query Empty text Evaluate Candidate Plans Choose Winning Plans Create Cache Entry Generate Result Documents Evict Cache Entry NO MATCH MATCH FAIL PASS Plan Optimiser
- 19. QueryPlanner Canonical Query CanonicalQuery * No of Indexes based on predicate Conditions = Candidate Plane Select a Wining Plane and Add it Plane Cache Ranking the Candidate Plane
- 20. db.setLogLevel(2,'query') db.people.getPlanCache().clear() db.people.find({"birthday": { $gte:new ISODate("2016-03-22T11:34:15Z")}}, {first_name:1, birthday:1, employer:1, email:1, ssn: 1}) Executing Query
- 21. 1. Scoring queryplan: IXSCAN { birthday: 1, snn: 1 } planHitEOF=0 1. score(2.0002) = baseScore(1) + productivity((101 advanced)/(101 works) = 1) + tieBreakers(0 noFetchBonus + 0.0001 noSortBonus + 0.0001 noIxisectBonus = 0.0002) 2. Scoring query plan: IXSCAN { birthday: 1 } planHitEOF=0 1. score(2.0002) = baseScore(1) + productivity((101 advanced)/(101 works) = 1) + tieBreakers(0 noFetchBonus + 0.0001 noSortBonus + 0.0001 noIxisectBonus = 0.0002) 3. Scoring query plan: IXSCAN { birthday: -1 } planHitEOF=0 1. score(2.0002) = baseScore(1) + productivity((101 advanced)/(101 works) = 1) + tieBreakers(0 noFetchBonus + 0.0001 noSortBonus + 0.0001 noIxisectBonus = 0.0002) 1. Winning plan: IXSCAN { birthday: 1, snn: 1 } 1 1. Relevant index 0 is kp: { birthday: 1.0, snn: 1.0 } name: 'birthday_1_snn_1' io: { v: 2, key: { birthday: 1.0, snn: 1.0 }, name: "birthday_1_snn_1", ns: "test.people" } 2. Relevant index 1 is kp: { birthday: 1.0 } name: 'birthday_1' io: { v: 2, key: { birthday: 1.0 }, name: "birthday_1", ns: "test.people" } 3. Relevant index 2 is kp: { birthday: -1.0 } name: 'birthday_asc' io: { v: 2, key: { birthday: -1.0 }, name: "birthday_asc", ns: "test.people" } MongoDB Execution Plan Scoring 2 3
- 22. numWorks baseScore common.advanced workUnits productivity(Plan Produce range[0,1]) noFetchBonus noSortBonus noIxisectBonus epsilon tieBreakers Scorea MongoDB Plan Ranking Terms
- 23. numWorks - AdvancedWork Units Query Returns 102 document no of document <= 101YES NO numWorks = 101 numWorks = no of document advanced = numWorks works = numWorks + 1 advanced = numWorks works = numWorks
- 24. advanced/wor kUnits 1 productivity baseScore 3 noFetchBonus noSortBonus noIxisectBonus 2 Default= 0 or epsilon = std::min(1.0 / static_cast(10 * workUnits), 1e-4) tieBreakers Score ANSWER Flow Chart
- 25. Index Re-build willevict the plan cache Add or Delete Index will evict the plan cache The MongoD process restart will evict the plan cache internalQueryCacheEvictionRatio * _decisionWorks will do the plan cache eviction MongoDB Plan Cache - Eviction internalQueryCacheEvictionRatio: 10.0 _decisionWorks: The number of work cycles taken to decide on a winning plan when the plan was first
- 26.
- 27.
- 28.