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:
- Specify explicit hints for queries affected by this issue.
- Set query plan cache index filter to override default query planning.
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.
- is depended on by
-
SERVER-17895 Server should not clear collection plan cache periodically when write operations are issued
- Closed
- is duplicated by
-
SERVER-14525 Perf regression in 2.6.2 caused by not caching plans that tie during plan ranking
- Closed
-
SERVER-17054 Index not being utilized, on occasion.
- Closed
-
SERVER-15124 Eviction algorithm for bad cached plans needs to be more aggressive
- Closed
-
SERVER-17260 Wrong index is often chosen for query: { "foo": "fooValue", "_id": { "$gte": "idValue" } }, sort: { "_id": 1 }
- Closed
-
SERVER-17674 Sometimes QueryPlan use wrong index
- Closed
-
SERVER-18148 query picking wrong index?
- Closed
-
SERVER-18507 A query will take very long time in a sudden but it works well in most time.
- Closed
-
SERVER-21064 It's possible to fail on sort memory overflow even if there is an alternative plan that requires no sort.
- Closed
-
SERVER-68672 Query using shard key and document ID does not use available index
- Closed
-
SERVER-1116 qo recover from stale index
- Closed
-
SERVER-8599 Prevent bad query plans from being cached by adding additional safeguards
- Closed
-
SERVER-15226 Query plan cache should cache query plans that repeatedly tie with winning plan for a given shape
- Closed
- is related to
-
SERVER-16350 Wrong index choice when request contains _id in query + sort command
- Closed
- related to
-
SERVER-19835 SubplanStage should not cache plan for an individual $or branch if plans tie or there are no query results
- Closed
-
SERVER-15924 planCacheListPlans command can report incorrect averageScore and stdDevScore in "feedback" section
- Closed
-
SERVER-18777 CachedPlanStage replanning mechanism does not apply to rooted $or queries
- Backlog
-
SERVER-20139 Enable CachedPlanStage replanning by default in 3.0
- Closed