[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: File repro.js    
Issue Links:
Related
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 SERVER-32452 for more details on that.



 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.

 

 

Generated at Thu Feb 08 06:39:55 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.