[SERVER-58712] Inconsistent update performance on collections with multiple secondary indexes on same key Created: 20/Jul/21 Updated: 29/Oct/23 Resolved: 29/Sep/22 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Query Execution |
| Affects Version/s: | None |
| Fix Version/s: | 5.0.15, 6.0.4, 6.2.0-rc0 |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Luis Osta (Inactive) | Assignee: | Ivan Fefer |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||||||||||||||||||
| Issue Links: |
|
||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||
| Backport Requested: |
v6.0, v5.0, v4.4
|
||||||||||||||||||||||||
| Steps To Reproduce: |
|
||||||||||||||||||||||||
| Sprint: | QE 2022-07-25, QE 2022-08-08, QE 2022-08-22, QE 2022-10-03 | ||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||
| Description |
|
Update performance appears to suffer greatly if there are multiple secondary indexes with the same first field in a key. This does not appear to have an obvious reason, because using a hint improves performance significantly. For example:
In the server ticket https://jira.mongodb.org/browse/SERVER-57095?filter=-1, adding the hint decreased the query time by several minutes. From 5 minutes without the hint to about 14 seconds after the hint was added. If you run the attach replication test you'll see that without the hint it takes 234,335 milliseconds and with the hint it takes 3,347 milliseconds. Seems to be related to https://jira.mongodb.org/browse/SERVER-57221 |
| Comments |
| Comment by Githook User [ 02/Dec/22 ] |
|
Author: {'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}Message: (cherry picked from commit 1d93d09948c29534f7c82950ba7771ac0f4da22c) |
| Comment by Githook User [ 02/Dec/22 ] |
|
Author: {'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}Message: (cherry picked from commit 1d93d09948c29534f7c82950ba7771ac0f4da22c) |
| Comment by Ivan Fefer [ 01/Dec/22 ] |
|
If we are backporting this ticket, then we also should backport this one https://jira.mongodb.org/browse/SERVER-70394 - a small fix for a small bug in this commit. |
| Comment by Githook User [ 29/Sep/22 ] |
|
Author: {'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}Message: |
| Comment by Githook User [ 28/Sep/22 ] |
|
Author: {'name': 'Ivan Fefer', 'email': 'fefer.ivan@gmail.com', 'username': 'Fefer-Ivan'}Message: |
| Comment by Ivan Fefer [ 27/Sep/22 ] |
|
I reproduced the issue using provided jstest. The reason is MultiPlanStage: when we don't add a hint, plans for both indexes are generated, but when MultiPlanStage is done choosing plan, rejected plans are still persist in memory and may hold some locks, slowing down updates. Fix should be easy: MultiPlanStage owns all children and need to delete them after plan is chosen. Some care need to be taken with backup plan logic. |
| Comment by Rushan Chen [ 01/Jul/22 ] |
|
As per discussion thread in related ticket of
Alternatively, restoreState for candidate plans ideally should be called just before the backup plan is chosen.
Keeping this ticket open so the perf gains in PM-2317 can be confirmed first.
|
| Comment by Ana Meza [ 19/Aug/21 ] |
|
We believe that is the same issue as |