Small fixes to the Matrix backfilling history script pt. 5 - Use efficient indexes for database queries
Small fixes to the Matrix backfilling history script pt. 5 - Use efficient indexes for database queries
Even after the change in !2322 (merged), we were still seeing long-running queries for rooms with 0/1 messages in some cases:
db.currentOp({ "active" : true, "secs_running" : { "$gt" : 3 } })
{
"desc" : "conn475052551",
"threadId" : "140682839373568",
"connectionId" : 475052551,
"client" : "10.0.10.122:44004",
"active" : true,
"opid" : 1423629372,
"secs_running" : 807,
"microsecs_running" : NumberLong(807225963),
"op" : "query",
"ns" : "gitter.chatmessages",
"query" : {
"find" : "chatmessages",
"filter" : {
"parentId" : {
"$exists" : false
},
"toTroupeId" : ObjectId("54db9e0f15522ed4b3dbe638"),
"_id" : {
"$lt" : ObjectId("619041aa9d20982e4f19eb62")
}
},
"sort" : {
"_id" : 1
},
"batchSize" : 256
},
"numYields" : 255698,
"locks" : {
"Global" : "r",
"Database" : "r",
"Collection" : "r"
},
"waitingForLock" : false,
"lockStats" : {
"Global" : {
"acquireCount" : {
"r" : NumberLong(511398)
}
},
"Database" : {
"acquireCount" : {
"r" : NumberLong(255699)
},
"acquireWaitCount" : {
"r" : NumberLong(85)
},
"timeAcquiringMicros" : {
"r" : NumberLong(421926)
}
},
"Collection" : {
"acquireCount" : {
"r" : NumberLong(255699)
}
}
}
},Follow-up to !2313 (merged) and !2322 (merged)
Part of #2609 (closed)
Query performance
They now both use a simple IXSCAN instead of a inputStage -> SORT_KEY_GENERATOR, etc
| Before | After |
|
|
Main message cursor
db.chatmessages.find({
"toTroupeId" : ObjectId("63b023ed85441f66183d580d"),
"_id" : {
"$lt" : ObjectId("63b023f50ed16d5104361fcf")
}
}).sort({ "_id": 1 });.explain() query plan
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "gitter.chatmessages",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"toTroupeId" : {
"$eq" : ObjectId("63b023ed85441f66183d580d")
}
},
{
"_id" : {
"$lt" : ObjectId("63b023f50ed16d5104361fcf")
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"toTroupeId" : 1,
"_id" : 1
},
"indexName" : "toTroupeId_1__id_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"toTroupeId" : [
"[ObjectId('63b023ed85441f66183d580d'), ObjectId('63b023ed85441f66183d580d')]"
],
"_id" : [
"[ObjectId('000000000000000000000000'), ObjectId('63b023f50ed16d5104361fcf'))"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"toTroupeId" : {
"$eq" : ObjectId("63b023ed85441f66183d580d")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_id" : 1
},
"indexName" : "_id_",
"isMultiKey" : false,
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"_id" : [
"[ObjectId('000000000000000000000000'), ObjectId('63b023f50ed16d5104361fcf'))"
]
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_id" : {
"$lt" : ObjectId("63b023f50ed16d5104361fcf")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"toTroupeId" : 1,
"sent" : -1
},
"indexName" : "toTroupeId_1_sent_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"toTroupeId" : [
"[ObjectId('63b023ed85441f66183d580d'), ObjectId('63b023ed85441f66183d580d')]"
],
"sent" : [
"[MaxKey, MinKey]"
]
}
}
}
}
}
]
},
"serverInfo" : {
// ...
},
"ok" : 1
}Threaded reply message cursor
db.chatmessages.find({
"_id" : {
"$lt" : ObjectId("6393aa01300f6c2f18b39ffd")
},
"parentId": ObjectId('6393aa01300f6c2f18b39fed')
}).sort({ "_id": 1 });.explain() query plan
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "gitter.chatmessages",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"parentId" : {
"$eq" : ObjectId("6393aa01300f6c2f18b39fed")
}
},
{
"_id" : {
"$lt" : ObjectId("6393aa01300f6c2f18b39ffd")
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"parentId" : 1,
"_id" : 1
},
"indexName" : "parentId_1__id_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"parentId" : [
"[ObjectId('6393aa01300f6c2f18b39fed'), ObjectId('6393aa01300f6c2f18b39fed')]"
],
"_id" : [
"[ObjectId('000000000000000000000000'), ObjectId('6393aa01300f6c2f18b39ffd'))"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"parentId" : {
"$eq" : ObjectId("6393aa01300f6c2f18b39fed")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_id" : 1
},
"indexName" : "_id_",
"isMultiKey" : false,
"isUnique" : true,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"_id" : [
"[ObjectId('000000000000000000000000'), ObjectId('6393aa01300f6c2f18b39ffd'))"
]
}
}
},
{
"stage" : "SORT",
"sortPattern" : {
"_id" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"_id" : {
"$lt" : ObjectId("6393aa01300f6c2f18b39ffd")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"parentId" : 1,
"sent" : -1
},
"indexName" : "parentId_1_sent_-1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"parentId" : [
"[ObjectId('6393aa01300f6c2f18b39fed'), ObjectId('6393aa01300f6c2f18b39fed')]"
],
"sent" : [
"[MaxKey, MinKey]"
]
}
}
}
}
}
]
},
"serverInfo" : {
// ...
},
"ok" : 1
}Todo
-
Add indexes to production
db.chatmessages.createIndex({
toTroupeId: 1,
_id: 1
}, {
background: true
});db.chatmessages.createIndex({
parentId: 1,
_id: 1
}, {
background: true
});db.chatmessages.getIndexes()
...
toTroupeId_1__id_1
parentId_1__id_1Edited by Eric Eastwood