Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-58712

Inconsistent update performance on collections with multiple secondary indexes on same key

    XMLWordPrintableJSON

Details

    • Fully Compatible
    • ALL
    • v6.0, v5.0, v4.4
    • Hide

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

      Show
      buildscripts/resmoke.py run jstests/replsets/update_secondary_index_perf.js >> log.txt
    • QE 2022-07-25, QE 2022-08-08, QE 2022-08-22, QE 2022-10-03

    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

      Attachments

        Activity

          People

            ivan.fefer@mongodb.com Ivan Fefer
            luis.osta@mongodb.com Luis Osta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            20 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: