Details
-
Bug
-
Resolution: Duplicate
-
Major - P3
-
None
-
3.6.12
-
None
-
Heroku with mlab add-on.
Upgraded from mongodb v3.4.20 -> v3.6.12
Description
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 |
}
|
}
|
}
|
}
|
Attachments
Issue Links
- duplicates
-
SERVER-19402 Change semantics of sorting by array fields in find and aggregate
-
- Closed
-