[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: File update_secondary_index_perf.js    
Issue Links:
Backports
Depends
is depended on by SERVER-57221 Inconsistent delete performance on co... Closed
Related
is related to SERVER-57221 Inconsistent delete performance on co... Closed
is related to SERVER-57095 Increase number of zones in reshardin... Closed
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v6.0, v5.0, v4.4
Steps To Reproduce:

buildscripts/resmoke.py run jstests/replsets/update_secondary_index_perf.js >> log.txt

Sprint: QE 2022-07-25, QE 2022-08-08, QE 2022-08-22, QE 2022-10-03
Participants:
Case:

 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:

  • Say collection has two secondary indexes {a: 1, b: 1}

    and

    {a: 1, c: 1}

    with 100K documents

  • Perform a multi update on {a: <value>}
  • This query takes 4 minutes. If an index is dropped or a hint is used, the same query performance improves to around 200ms.
  • As additional indexes are added, performance degrades linearly

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: SERVER-58712: Fix update performance on collections with multiple secondary indexes on same key

(cherry picked from commit 1d93d09948c29534f7c82950ba7771ac0f4da22c)
Branch: v5.0
https://github.com/mongodb/mongo/commit/e0464c5e9031f851e955ce5ad4583b3c9caa0d74

Comment by Githook User [ 02/Dec/22 ]

Author:

{'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}

Message: SERVER-58712: Fix update performance on collections with multiple secondary indexes on same key

(cherry picked from commit 1d93d09948c29534f7c82950ba7771ac0f4da22c)
Branch: v6.0
https://github.com/mongodb/mongo/commit/184d528d491daa53a06230e316f097b625d6e9dd

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: SERVER-58712: Fix update performance on collections with multiple secondary indexes on same key
Branch: master
https://github.com/mongodb/mongo/commit/1d93d09948c29534f7c82950ba7771ac0f4da22c

Comment by Githook User [ 28/Sep/22 ]

Author:

{'name': 'Ivan Fefer', 'email': 'fefer.ivan@gmail.com', 'username': 'Fefer-Ivan'}

Message: SERVER-58712: Add UpdateWithSecondaryIndexes workload (#739)
Branch: master
https://github.com/mongodb/genny/commit/569579530a9f60abc8af265d48a1ae60538d8586

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 SERVER-57221PM-2317 could also help this issue.

 

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 SERVER-57221 and the two should be addressed together if possible 

Generated at Thu Feb 08 05:45:15 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.