1 year ago
#74801
Carlos Torrecillas
MongoDb C# query performance
I have the MongoDB C# Driver 2.14.1 and a Mongo Server v3.6 running on a Docker container. I'm running an easy (I think) query over 3 collections. The biggest collection has 400k documents, each document averaged 64Kb. I'm filtering by two fields and returning the results in a cursor to reduce overhead. The results are sorted by descending by a field that is configured as an index as well. This is happening on my production server which is taking nearly 1GB of memory (not being released afterwards). The following queries explained (run on the server):
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "table1",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "Location1"
}
},
{
"Date" : {
"$gt" : ISODate("2021-01-18T23:00:00Z")
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "Location1"
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Date" : -1
},
"indexName" : "Date_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Date" : [ ]
},
"isUnique" : false,
"isSparse" : true,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"[new Date(9223372036854775807), new Date(1611010800000))"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 2,
"totalKeysExamined" : 7,
"totalDocsExamined" : 7,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "Location1"
}
}
]
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 8,
"advanced" : 1,
"needTime" : 6,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 7,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 7,
"executionTimeMillisEstimate" : 0,
"works" : 8,
"advanced" : 7,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"Date" : -1
},
"indexName" : "Date_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Date" : [ ]
},
"isUnique" : false,
"isSparse" : true,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"[new Date(9223372036854775807), new Date(1611010800000))"
]
},
"keysExamined" : 7,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "7aa23bd7fe20",
"port" : 27018,
"version" : "3.6.18",
"gitVersion" : "2005f25eed7ed88fa698d9b800fe536bb0410ba4"
},
"ok" : 1
}
The second query:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "table2",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "02"
}
},
{
"Date" : {
"$gt" : ISODate("2021-01-18T23:00:00Z")
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "02"
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Date" : -1
},
"indexName" : "Date_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Date" : [ ]
},
"isUnique" : false,
"isSparse" : true,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"[new Date(9223372036854775807), new Date(1611010800000))"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 63,
"totalKeysExamined" : 9896,
"totalDocsExamined" : 9896,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "02"
}
}
]
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 50,
"works" : 9897,
"advanced" : 1,
"needTime" : 9895,
"needYield" : 0,
"saveState" : 77,
"restoreState" : 77,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 9896,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 9896,
"executionTimeMillisEstimate" : 0,
"works" : 9897,
"advanced" : 9896,
"needTime" : 0,
"needYield" : 0,
"saveState" : 77,
"restoreState" : 77,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"Date" : -1
},
"indexName" : "Date_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Date" : [ ]
},
"isUnique" : false,
"isSparse" : true,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"[new Date(9223372036854775807), new Date(1611010800000))"
]
},
"keysExamined" : 9896,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "7aa23bd7fe20",
"port" : 27018,
"version" : "3.6.18",
"gitVersion" : "2005f25eed7ed88fa698d9b800fe536bb0410ba4"
},
"ok" : 1
}
And the third:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "table3",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "52"
}
},
{
"Date" : {
"$gt" : ISODate("2021-01-18T23:00:00Z")
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "52"
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Date" : -1
},
"indexName" : "Date_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Date" : [ ]
},
"isUnique" : false,
"isSparse" : true,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"[new Date(9223372036854775807), new Date(1611010800000))"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 30,
"executionTimeMillis" : 934,
"totalKeysExamined" : 366370,
"totalDocsExamined" : 366370,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"Type" : {
"$eq" : "Type1"
}
},
{
"LocationId" : {
"$eq" : "52"
}
}
]
},
"nReturned" : 30,
"executionTimeMillisEstimate" : 889,
"works" : 366371,
"advanced" : 30,
"needTime" : 366340,
"needYield" : 0,
"saveState" : 2862,
"restoreState" : 2862,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 366370,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 366370,
"executionTimeMillisEstimate" : 160,
"works" : 366371,
"advanced" : 366370,
"needTime" : 0,
"needYield" : 0,
"saveState" : 2862,
"restoreState" : 2862,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"Date" : -1
},
"indexName" : "Date_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"Date" : [ ]
},
"isUnique" : false,
"isSparse" : true,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"Date" : [
"[new Date(9223372036854775807), new Date(1611010800000))"
]
},
"keysExamined" : 366370,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "7aa23bd7fe20",
"port" : 27018,
"version" : "3.6.18",
"gitVersion" : "2005f25eed7ed88fa698d9b800fe536bb0410ba4"
},
"ok" : 1
}
Now with my little knowledge of the "explainPlan" I would have imagined that the performance is OK, and not many records get returned on each query. Having said that I can see a pattern when I run the code via the .NET application (C# code) that makes the CPU go through the roof and right after the memory follows in the Mongo server Docker container.
I don't know if there is misconfigured or whether I can do something to improve the performance of it because I'm quite puzzle with the performance on just that query - I think is this query because the rest of the queries with similar amount of data on different parts of the system work quite well. Besides that, is there any mechanism or tool I can use to diagnose the memory consumption in my Mongo server?
mongodb
mongodb-query
mongodb-.net-driver
0 Answers
Your Answer