[SERVER-34785] The Cache Plan cause poor performance Created: 02/May/18 Updated: 08/Jun/18 Resolved: 14/May/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 3.2.19, 3.6.4 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | zbdba | Assignee: | Kelsey Schubert |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Operating System: | ALL | ||||||||
| Participants: | |||||||||
| Description |
|
Hi All Our MongoDB had poor perfomance when this query appear,this query like this:
this collection have some indexes:
and every columns filter result count:
this collection has 553055549 records. So MongoDB choose the operation.des_url_1 index and it's perfect.but we saw many slow query after this query,such as :
I learned about the plan cache mechanism by reading the MongoDB source code,MongoDB continue uses operation.des_url_1 Index scan internalQueryCacheEvictionRatio *works times for every similar queries, execute the replan mechanism if it exceeds 101 times advanced status. Otherwise, use the plan in the cache.If operation.des_url filter result count is less than 180999*10 will return IS_EOF status, will directly use the plan in the cache. So after this query, we need to scan 180999*10 times for every similar queries,But all queries operation.des_url filter result count is less than 180999*10.so all queries get IS_EOF status,and directly use the plan in the cache. All similar queries are used operation.des_url_1 index,But it needs to scan hundreds of thousands of times,Actually using operation.sha1Code_1 The index only needs to scan several times. The operation.sha1Code filter result count:
I think this is the main reason for our large number of slow queries,The mechanism to determine whether to use the plan in the cache is too simple,In some scenarios it may cause poor performance. |
| Comments |
| Comment by Kelsey Schubert [ 14/May/18 ] |
|
Hi zbdba, Thank you for the report. I believe that Kind regards, |