[SERVER-15225] CachedPlanStage should execute for trial period and re-plan if query performs poorly Created: 11/Sep/14  Updated: 30/Aug/22  Resolved: 14/Apr/15

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

Type: Improvement Priority: Major - P3
Reporter: J Rassi Assignee: David Storch
Resolution: Done Votes: 2
Labels: 2426
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by SERVER-17895 Server should not clear collection pl... Closed
Duplicate
is duplicated by SERVER-14525 Perf regression in 2.6.2 caused by no... Closed
is duplicated by SERVER-17054 Index not being utilized, on occasion. Closed
is duplicated by SERVER-15124 Eviction algorithm for bad cached pla... Closed
is duplicated by SERVER-17260 Wrong index is often chosen for query... Closed
is duplicated by SERVER-17674 Sometimes QueryPlan use wrong index Closed
is duplicated by SERVER-18148 query picking wrong index? Closed
is duplicated by SERVER-18507 A query will take very long time in a... Closed
is duplicated by SERVER-21064 It's possible to fail on sort memory ... Closed
is duplicated by SERVER-68672 Query using shard key and document ID... Closed
is duplicated by SERVER-1116 qo recover from stale index Closed
is duplicated by SERVER-8599 Prevent bad query plans from being ca... Closed
is duplicated by SERVER-15226 Query plan cache should cache query p... Closed
Related
related to SERVER-19835 SubplanStage should not cache plan fo... Closed
related to SERVER-15924 planCacheListPlans command can report... Closed
related to SERVER-18777 CachedPlanStage replanning mechanism ... Backlog
related to DOCS-6401 Updates to query plan revision as of ... Closed
related to SERVER-20139 Enable CachedPlanStage replanning by ... Closed
is related to SERVER-16350 Wrong index choice when request conta... Closed
Backwards Compatibility: Fully Compatible
Backport Completed:
Sprint: Quint Iteration 3.1.2
Participants:

 Description   
Issue Status as of Jun 19, 2015

ISSUE SUMMARY
The query optimizer caches plans for each query shape and reuses these plans for a time. In situations where the performance of the cached plan is poor for a particular instance of the query shape, the optimizer may select a plan with poor performance and fail to evict the cache entry. This behavior may impact deployments where two queries with the same shape have different performance characteristics if they have different selectivity.

This improvement makes the query planner evaluate the cost of the cached query plan, and if the cost of this plan is too high, the query planner switches to a more efficient plan. This more efficient plan is then cached for future use.

This improvement is not enabled by default. To enable by default set the internalQueryCacheReplanningEnabled parameter to true using the setParameter command on a running system, or at start time using the setParameter commandline option or setParameter in the configuration file.

For example, to enable using setParameter:

db.runCommand({setParameter: 1, internalQueryCacheReplanningEnabled: true})

This improvement can be disabled as follows:

db.runCommand({setParameter: 1, internalQueryCacheReplanningEnabled: false})

USER IMPACT
Users may observe some queries intermittently exhibiting poor performance if multiple queries with the same shape are differently selective with regards to the distribution of documents in the index. Some queries will take longer and may report as slow in the logs if they select a non-optimal plan but will behave optimally in the output of explain.

WORKAROUNDS
Users affected by sub-optimal query plan selection may:

AFFECTED VERSIONS
MongoDB 3.0.3 and earlier.

FIX VERSION
The improvement is included in the 3.0.4 production release, but not enabled by default.

Original description

As of 2.6.0, query plans that are executed from the plan cache are considered for cache eviction only after the plan finishes execution. Instead, it should be possible to evict plans from the cache before they finish executing, in the case where the performance of the plan is much worse than when it was first cached.

In order to do this, we will need to investigate alternative plan cache eviction policies. This is because the current eviction policy accesses statistics from the last 20 executions of the cached plan (which won't be available) in order to make an eviction decision. The current eviction policy also does not perform well, in practice.



 Comments   
Comment by Githook User [ 03/Jun/15 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-15225 CachedPlanStage can replan poorly performing queries after a trial period

This is a minimal backport of the rewrite to the CachedPlanStage. The functionality is behind
a flag and turned off by default. It can be enabled with the internalQueryCacheReplanningEnabled
setParameter.
Branch: v3.0
https://github.com/mongodb/mongo/commit/f80286e0203fa0aa0873bff2963ce0aa81a51383

Comment by Githook User [ 17/Apr/15 ]

Author:

{u'username': u'jrassi', u'name': u'Jason Rassi', u'email': u'rassi@10gen.com'}

Message: SERVER-15225 Fix leak of 'qs' in prepareExecution() cached plan path
Branch: master
https://github.com/mongodb/mongo/commit/7745af75e699fa94051000b801769e1a6310e898

Comment by Githook User [ 14/Apr/15 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-15225 CachedPlanStage can replan poorly performing queries after a trial period
Branch: master
https://github.com/mongodb/mongo/commit/81a1f70b87b3f3754931829b11faa0a53df64527

Generated at Thu Feb 08 03:37:22 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.