[SERVER-79039] Plan cache entries can be evicted and replanned continuously for certain workload patterns Created: 17/Jul/23 Updated: 01/Sep/23 Resolved: 17/Aug/23 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Nicholas Zolnierz | Assignee: | Chris Harris |
| Resolution: | Won't Do | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||
| Issue Links: |
|
||||
| Assigned Teams: |
Query Optimization
|
||||
| Participants: | |||||
| Description |
|
For certain patterns of queries, we can run into a cycle of cache->evict->replan->cache for the same query shape and same plan depending on the parameters. By default, the planner requires a 10x increase in works to trigger replanning, which manifests in the slow query logs as "replanReason":"cached plan was less efficient than expected: expected trial execution to take X reads but it took at least Y reads". However, when the higher work plan triggers the eviction, the cache entry remains in an "inactive" state. If the next query to activate it has a lower works value, then we'll use the new one. This puts us in a state where its like the higher work query never happened, and its possible to evict and replan again. Note that the notion of an inactive cache entry was put in place for the opposite case: when a potentially sub-optimal, very high works plan gets cached. See |
| Comments |
| Comment by Conchi Bueno [ 21/Jul/23 ] |
|
Hi christopher.harris@mongodb.com I've discussed this with william.byrne@mongodb.com. We agree the changes to the replanning behaviour are too risky but in terms of diagnosability we could consider adding the original plan when a replanning happens.
|