-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: 3.6.12
-
Component/s: None
-
Labels:
-
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
- Is it specific problem for multi-key index?
- Any indicators for broken/corrupted index?
- Are we expected/required to rebuild index after certain operation (such as db version upgrade)?
Collection: "runs"
- 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 } } } }
- duplicates
-
SERVER-19402 Change semantics of sorting by array fields in find and aggregate
-
- Closed
-