Shulou(Shulou.com)06/01 Report--

Impact of MongoDB Plan caching

After the MongoDB 2.6 replication set Primary created the index, it was found that Secondary's query did not take the latest index.

It is normal after temporarily clearing the schedule cache for the collection. The author observes that when there are performance problems, the statement does not follow the optimal execution plan.

For MongoDB 3.0.3 and earlier, it can be resolved in the following two ways:

1. Explicitly specify hints for the query.

two。 Set the query plan cache index filter to override the default query plan.

Fixed in version 3.0.4.

SERVER-15225,SERVER-20139

Https://jira.mongodb.org/browse/SERVER-15225

The refresh problem of the execution plan cache, for the same kind of query, the execution plan will not be verified if there is cache, and the possible execution of the same kind of query will be different if the conditions are different.

It can be solved by setting the internalQueryCacheReplanningEnabled parameter

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 the 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 totrue using the setParameter command on a running system, or at start time using the setParameter commandline option orsetParameter 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})

3.0.4 you can use this parameter. It is disabled by default.

Inquiry plan

Given an available index, the MongoDB query optimizer processes the query and selects the query plan that is most efficient for a query. The query plan is used by the query system each time the query is executed.

The query optimizer caches only query plans that appear to have multiple feasible plans.

When the content of the collection changes, the query optimizer re-evaluates the query plan to ensure the optimal query plan. You can specify the index evaluated by the optimizer by using an index filter.

For a given query, you can use the explain () method to view the statistics of the query plan.

Query plan revision

As the collection changes over time, the query optimizer deletes the query plan and re-evaluates it in one of the following situations:

1. The collection receives 1000 writes.

2. The reIndex rebuilds the index.

3. You add or delete an index.

4. The mongod process restarts.

Cache query Plan Interface

2.6 New Featur

MongoDB provides query plan caching commands and methods to view and modify cached query plans.

Db.runCommand ({planCacheListFilters: Product}) db.runCommand ({planCacheListPlans: "Product", query: {Path: / ^ 9-1-6 (-\ d +) * $/, "Status": {$lt: 4}) db.runCommand ({planCacheClear: "Product", query: {Path: / ^ 9-1-6 (-\ d +) * $/ "Status": {$lt: 4}) db.runCommand ({planCacheClear: "Product"})

Index filter

2.6 new function.

The index filter determines which index the optimizer evaluates as a query shape. A query shape consists of a query, a sort, and a mapping description. If there is an index filter for a given query shape, the optimizer will only consider those indexes specified in the filter.

MongoDB ignores hint () when there is an index filter in the query shape. If you want to know if MongoDB uses an index filter for a query, you can check the explain.filterSet field of the explain () output.

The index filter only affects the index evaluated by the optimizer, and it is possible that the optimizer will still choose collection scanning as the preferred solution for a given query shape.

Index filters exist only in the server process and are not saved after shutdown. MongoDB also provides a command to manually delete the filter.

Because the index filter overrides the operation expected by the optimizer and the hint () method, use the index filter appropriately.

Db.runCommand ({planCacheSetFilter: "orders", query: {item: "ABC"}, projection: {quantity: 1, _ id: 0}, sort: {order_date: 1}, indexes: [{item: 1, order_date: 1, quantity: 1}]})

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.