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

Broken index after mongodb version upgrade to 3.6

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.6.12
    • Component/s: None
    • Environment:
      Heroku with mlab add-on.
      Upgraded from mongodb v3.4.20 -> v3.6.12

      Problem

      After upgrading mongodb from v3.4.20 -> v3.6.12 (on mlab add-on for heroku), we have sudden dramatic performance regression on the mongodb. CPU usage went from <10% to 99%, Disk IOPS significantly increased and all burst credits are used up within minutes, which brought down our service (application on heroku). 

      Investigation

      Our investigation found that the existing index (multi-key) seems to be broken/corrupted after the mongodb upgrade, which caused inefficient query execution plan to be generated & huge # of document examined for all queries.

      Resolution / Workaround

      We dropped the index and re-created the same index. The issue seems to be resolved afterward.

      Question

      1. Is it specific problem for multi-key index?
      2. Any indicators for broken/corrupted index?
      3. Are we expected/required to rebuild index after certain operation (such as db version upgrade)?

       


      Collection: "runs"

      1. of documents: 1.8M
        Size: 130GB, Storage Size: 28GB

       

      Index:

      { 
       "tags" : 1, 
       "startTS" : -1
       }

      Note: "tags" = array of text (huge amount of distant values)

       

      Query:

       db.getCollection('runs')
      .find({"tags":{"$all":["approval-approved"]}})
      .sort({startTS: -1})
      .limit(100)

       

      Execution Plan (problematic)

      12 mins - 1M keys examined & 1M docs examined.

      "executionStats" : {
             "executionSuccess" : true, 
             "nReturned" : 100.0, 
             "executionTimeMillis" : 725879.0, 
             "totalKeysExamined" : 1081144.0, 
             "totalDocsExamined" : 1081144.0, 
             "executionStages" : {
                 "stage" : "SORT", 
                 "nReturned" : 100.0, 
                 "executionTimeMillisEstimate" : 722927.0, 
                 "works" : 1081247.0, 
                 "advanced" : 100.0, 
                 "needTime" : 1081146.0, 
                 "needYield" : 0.0, 
                 "saveState" : 35987.0, 
                 "restoreState" : 35987.0, 
                 "isEOF" : 1.0, 
                 "invalidates" : 0.0, 
                 "sortPattern" : {
                     "startTS" : -1.0
                 }, 
                 "memUsage" : 8236467.0, 
                 "memLimit" : 33554432.0, 
                 "limitAmount" : 100.0, 
                 "inputStage" : {
                     "stage" : "SORT_KEY_GENERATOR", 
                     "nReturned" : 1081144.0, 
                     "executionTimeMillisEstimate" : 721437.0, 
                     "works" : 1081146.0, 
                     "advanced" : 1081144.0, 
                     "needTime" : 1.0, 
                     "needYield" : 0.0, 
                     "saveState" : 35987.0, 
                     "restoreState" : 35987.0, 
                     "isEOF" : 1.0, 
                     "invalidates" : 0.0, 
                     "inputStage" : {
                         "stage" : "FETCH", 
                         "nReturned" : 1081144.0, 
                         "executionTimeMillisEstimate" : 719697.0, 
                         "works" : 1081145.0, 
                         "advanced" : 1081144.0, 
                         "needTime" : 0.0, 
                         "needYield" : 0.0, 
                         "saveState" : 35987.0, 
                         "restoreState" : 35987.0, 
                         "isEOF" : 1.0, 
                         "invalidates" : 0.0, 
                         "docsExamined" : 1081144.0, 
                         "alreadyHasObj" : 0.0, 
                         "inputStage" : {
                             "stage" : "IXSCAN", 
                             "nReturned" : 1081144.0, 
                             "executionTimeMillisEstimate" : 2644.0, 
                             "works" : 1081145.0, 
                             "advanced" : 1081144.0, 
                             "needTime" : 0.0, 
                             "needYield" : 0.0, 
                             "saveState" : 35987.0, 
                             "restoreState" : 35987.0, 
                             "isEOF" : 1.0, 
                             "invalidates" : 0.0, 
                             "keyPattern" : {
                                 "tags" : 1.0, 
                                 "startTS" : -1.0
                             }, 
                             "indexName" : "tags_1_startTS_-1", 
                             "isMultiKey" : true, 
                             "isUnique" : false, 
                             "isSparse" : false, 
                             "isPartial" : false, 
                             "indexVersion" : 1.0, 
                             "direction" : "forward", 
                             "indexBounds" : {
                                 "tags" : [
                                     "[\"approval-approved\", \"approval-approved\"]"
                                 ], 
                                 "startTS" : [
                                     "[MaxKey, MinKey]"
                                 ]
                             }, 
                             "keysExamined" : 1081144.0, 
                             "seeks" : 1.0, 
                             "dupsTested" : 1081144.0, 
                             "dupsDropped" : 0.0, 
                             "seenInvalidated" : 0.0                   }
                     }
                 }
             }
         }

       

      Execution Plan (After re-creating same index) 

      5 ms - 100 keys examined & 100 docs examined. And I noticed that multiKeyPaths is present in this executionStats, but not in the problematic one above.

      "executionStats" : {
              "executionSuccess" : true, 
              "nReturned" : 100.0, 
              "executionTimeMillis" : 5.0, 
              "totalKeysExamined" : 100.0, 
              "totalDocsExamined" : 100.0, 
              "executionStages" : {
                  "stage" : "LIMIT", 
                  "nReturned" : 100.0, 
                  "executionTimeMillisEstimate" : 0.0, 
                  "works" : 101.0, 
                  "advanced" : 100.0, 
                  "needTime" : 0.0, 
                  "needYield" : 0.0, 
                  "saveState" : 2.0, 
                  "restoreState" : 2.0, 
                  "isEOF" : 1.0, 
                  "invalidates" : 0.0, 
                  "limitAmount" : 100.0, 
                  "inputStage" : {
                      "stage" : "FETCH", 
                      "nReturned" : 100.0, 
                      "executionTimeMillisEstimate" : 0.0, 
                      "works" : 100.0, 
                      "advanced" : 100.0, 
                      "needTime" : 0.0, 
                      "needYield" : 0.0, 
                      "saveState" : 2.0, 
                      "restoreState" : 2.0, 
                      "isEOF" : 0.0, 
                      "invalidates" : 0.0, 
                      "docsExamined" : 100.0, 
                      "alreadyHasObj" : 0.0, 
                      "inputStage" : {
                          "stage" : "IXSCAN", 
                          "nReturned" : 100.0, 
                          "executionTimeMillisEstimate" : 0.0, 
                          "works" : 100.0, 
                          "advanced" : 100.0, 
                          "needTime" : 0.0, 
                          "needYield" : 0.0, 
                          "saveState" : 2.0, 
                          "restoreState" : 2.0, 
                          "isEOF" : 0.0, 
                          "invalidates" : 0.0, 
                          "keyPattern" : {
                              "tags" : 1.0, 
                              "startTS" : -1.0
                          }, 
                          "indexName" : "tags_1_startTS_-1", 
                          "isMultiKey" : true, 
                          "multiKeyPaths" : {
                              "tags" : [
                                  "tags"
                              ], 
                              "startTS" : [
      
                              ]
                          }, 
                          "isUnique" : false, 
                          "isSparse" : false, 
                          "isPartial" : false, 
                          "indexVersion" : 2.0, 
                          "direction" : "forward", 
                          "indexBounds" : {
                              "tags" : [
                                  "[\"approval-approved\", \"approval-approved\"]"
                              ], 
                              "startTS" : [
                                  "[MaxKey, MinKey]"
                              ]
                          }, 
                          "keysExamined" : 100.0, 
                          "seeks" : 1.0, 
                          "dupsTested" : 100.0, 
                          "dupsDropped" : 0.0, 
                          "seenInvalidated" : 0.0
                      }
                  }
              }
          }
      

       

            Assignee:
            eric.sedor@mongodb.com Eric Sedor
            Reporter:
            chujacky1128@gmail.com Jackie Chu
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: