Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-15225

CachedPlanStage should execute for trial period and re-plan if query performs poorly

    • Type: Icon: Improvement Improvement
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 3.0.4, 3.1.2
    • Affects Version/s: None
    • Component/s: Querying
    • Labels:
    • Fully Compatible
    • Quint Iteration 3.1.2

      Issue Status as of Jun 19, 2015

      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})

      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.

      Users affected by sub-optimal query plan selection may:

      MongoDB 3.0.3 and earlier.

      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.

            david.storch@mongodb.com David Storch
            rassi J Rassi
            2 Vote for this issue
            28 Start watching this issue