Question posted in 
I am experiencing very slow performance when running the following query against a sharded collection (MongoDb 4.2.1):
db.dc.find({
'hierarchy.company': 'PICCOLO_SRL',
"hierarchy.brand": 'PICCOLO_PICCOLO',
'dcheader.doc_date': {
$gte: 1569016800,
$lte: 1571695140
},
'dcheader.cardnr': '0461000402452'})
The query uses the following index, that is not the shard index:
"key" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.doc_date" : 1,
"dcheader.cardnr" : 1
}
The collection contains 4.5M docs and its size is 20.2GB. The shard index is hashed type and the shard is balanced.
The query execution plan is the following:
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SHARD_MERGE",
"shards" : [
{
"shardName" : "rsshard1",
"connectionString" : "rsshard1/kcc1prod.sipos.it:27020,kcc2prod.sipos.it:27020,kcc3prod.sipos.it:27020",
"serverInfo" : {
"host" : "kcc2prod.sipos.it",
"port" : 27020,
"version" : "4.2.1",
"gitVersion" : "edf6d45851c0b9ee15548f0f847df141764a317e"
},
"plannerVersion" : 1,
"namespace" : "kaneda.dc",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"dcheader.cardnr" : {
"$eq" : "0461000402452"
}
},
{
"hierarchy.brand" : {
"$eq" : "PICCOLO_PICCOLO"
}
},
{
"hierarchy.company" : {
"$eq" : "PICCOLO_SRL"
}
},
{
"dcheader.doc_date" : {
"$lte" : 1571695140
}
},
{
"dcheader.doc_date" : {
"$gte" : 1569016800
}
}
]
},
"winningPlan" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.doc_date" : 1,
"dcheader.cardnr" : 1
},
"indexName" : "IndexCardnrDocDate",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.doc_date" : [ ],
"dcheader.cardnr" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"dcheader.doc_date" : [
"[1569016800.0, 1571695140.0]"
],
"dcheader.cardnr" : [
"["0461000402452", "0461000402452"]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"dcheader.cardnr" : {
"$eq" : "0461000402452"
}
},
{
"dcheader.doc_date" : {
"$lte" : 1571695140
}
},
{
"dcheader.doc_date" : {
"$gte" : 1569016800
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"hierarchy.zone" : 1,
"hierarchy.pdv" : 1,
"dcheader.idscontrino" : 1
},
"indexName" : "IndexIdScontrino",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"hierarchy.zone" : [ ],
"hierarchy.pdv" : [ ],
"dcheader.idscontrino" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"hierarchy.zone" : [
"[MinKey, MaxKey]"
],
"hierarchy.pdv" : [
"[MinKey, MaxKey]"
],
"dcheader.idscontrino" : [
"[MinKey, MaxKey]"
]
}
}
}
},
{
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"dcheader.cardnr" : {
"$eq" : "0461000402452"
}
},
{
"dcheader.doc_date" : {
"$lte" : 1571695140
}
},
{
"dcheader.doc_date" : {
"$gte" : 1569016800
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.deptnr" : 1,
"dcheader.tillid" : 1,
"dcheader.numdocumentogestionale" : 1,
"dcheader.fiscalprinternr" : 1
},
"indexName" : "IndexDcDocGestionale",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.deptnr" : [ ],
"dcheader.tillid" : [ ],
"dcheader.numdocumentogestionale" : [ ],
"dcheader.fiscalprinternr" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"dcheader.deptnr" : [
"[MinKey, MaxKey]"
],
"dcheader.tillid" : [
"[MinKey, MaxKey]"
],
"dcheader.numdocumentogestionale" : [
"[MinKey, MaxKey]"
],
"dcheader.fiscalprinternr" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
{
"shardName" : "rsshard2",
"connectionString" : "rsshard2/kcc1prod.sipos.it:27021,kcc2prod.sipos.it:27021,kcc3prod.sipos.it:27021",
"serverInfo" : {
"host" : "kcc3prod.sipos.it",
"port" : 27021,
"version" : "4.2.1",
"gitVersion" : "edf6d45851c0b9ee15548f0f847df141764a317e"
},
"plannerVersion" : 1,
"namespace" : "kaneda.dc",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"dcheader.cardnr" : {
"$eq" : "0461000402452"
}
},
{
"hierarchy.brand" : {
"$eq" : "PICCOLO_PICCOLO"
}
},
{
"hierarchy.company" : {
"$eq" : "PICCOLO_SRL"
}
},
{
"dcheader.doc_date" : {
"$lte" : 1571695140
}
},
{
"dcheader.doc_date" : {
"$gte" : 1569016800
}
}
]
},
"winningPlan" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.doc_date" : 1,
"dcheader.cardnr" : 1
},
"indexName" : "IndexCardnrDocDate",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.doc_date" : [ ],
"dcheader.cardnr" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"dcheader.doc_date" : [
"[1569016800.0, 1571695140.0]"
],
"dcheader.cardnr" : [
"["0461000402452", "0461000402452"]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"dcheader.cardnr" : {
"$eq" : "0461000402452"
}
},
{
"dcheader.doc_date" : {
"$lte" : 1571695140
}
},
{
"dcheader.doc_date" : {
"$gte" : 1569016800
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"hierarchy.zone" : 1,
"hierarchy.pdv" : 1,
"dcheader.idscontrino" : 1
},
"indexName" : "IndexIdScontrino",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"hierarchy.zone" : [ ],
"hierarchy.pdv" : [ ],
"dcheader.idscontrino" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"hierarchy.zone" : [
"[MinKey, MaxKey]"
],
"hierarchy.pdv" : [
"[MinKey, MaxKey]"
],
"dcheader.idscontrino" : [
"[MinKey, MaxKey]"
]
}
}
}
},
{
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"dcheader.cardnr" : {
"$eq" : "0461000402452"
}
},
{
"dcheader.doc_date" : {
"$lte" : 1571695140
}
},
{
"dcheader.doc_date" : {
"$gte" : 1569016800
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.deptnr" : 1,
"dcheader.tillid" : 1,
"dcheader.numdocumentogestionale" : 1,
"dcheader.fiscalprinternr" : 1
},
"indexName" : "IndexDcDocGestionale",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.deptnr" : [ ],
"dcheader.tillid" : [ ],
"dcheader.numdocumentogestionale" : [ ],
"dcheader.fiscalprinternr" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"dcheader.deptnr" : [
"[MinKey, MaxKey]"
],
"dcheader.tillid" : [
"[MinKey, MaxKey]"
],
"dcheader.numdocumentogestionale" : [
"[MinKey, MaxKey]"
],
"dcheader.fiscalprinternr" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
}
]
}
},
"executionStats" : {
"nReturned" : 0,
"executionTimeMillis" : 170263,
"totalKeysExamined" : 555702,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "SHARD_MERGE",
"nReturned" : 0,
"executionTimeMillis" : 170263,
"totalKeysExamined" : 555702,
"totalDocsExamined" : 0,
"totalChildMillis" : 255673,
"shards" : [
{
"shardName" : "rsshard1",
"executionSuccess" : true,
"executionStages" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 0,
"executionTimeMillisEstimate" : 1447,
"works" : 202303,
"advanced" : 0,
"needTime" : 202301,
"needYield" : 0,
"saveState" : 6882,
"restoreState" : 6882,
"isEOF" : 1,
"chunkSkips" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 0,
"executionTimeMillisEstimate" : 1428,
"works" : 202302,
"advanced" : 0,
"needTime" : 202301,
"needYield" : 0,
"saveState" : 6882,
"restoreState" : 6882,
"isEOF" : 1,
"docsExamined" : 0,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 1428,
"works" : 202302,
"advanced" : 0,
"needTime" : 202301,
"needYield" : 0,
"saveState" : 6882,
"restoreState" : 6882,
"isEOF" : 1,
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.doc_date" : 1,
"dcheader.cardnr" : 1
},
"indexName" : "IndexCardnrDocDate",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.doc_date" : [ ],
"dcheader.cardnr" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"dcheader.doc_date" : [
"[1569016800.0, 1571695140.0]"
],
"dcheader.cardnr" : [
"["0461000402452", "0461000402452"]"
]
},
"keysExamined" : 202302,
"seeks" : 202302,
"dupsTested" : 0,
"dupsDropped" : 0,
"indexDef" : {
"indexName" : "IndexCardnrDocDate",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.doc_date" : [ ],
"dcheader.cardnr" : [ ]
},
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.doc_date" : 1,
"dcheader.cardnr" : 1
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"direction" : "forward"
}
}
}
}
},
{
"shardName" : "rsshard2",
"executionSuccess" : true,
"executionStages" : {
"stage" : "SHARDING_FILTER",
"nReturned" : 0,
"executionTimeMillisEstimate" : 1677,
"works" : 353401,
"advanced" : 0,
"needTime" : 353399,
"needYield" : 0,
"saveState" : 12612,
"restoreState" : 12612,
"isEOF" : 1,
"chunkSkips" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 0,
"executionTimeMillisEstimate" : 1660,
"works" : 353400,
"advanced" : 0,
"needTime" : 353399,
"needYield" : 0,
"saveState" : 12612,
"restoreState" : 12612,
"isEOF" : 1,
"docsExamined" : 0,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 0,
"executionTimeMillisEstimate" : 1656,
"works" : 353400,
"advanced" : 0,
"needTime" : 353399,
"needYield" : 0,
"saveState" : 12612,
"restoreState" : 12612,
"isEOF" : 1,
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.doc_date" : 1,
"dcheader.cardnr" : 1
},
"indexName" : "IndexCardnrDocDate",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.doc_date" : [ ],
"dcheader.cardnr" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"hierarchy.company" : [
"["PICCOLO_SRL", "PICCOLO_SRL"]"
],
"hierarchy.brand" : [
"["PICCOLO_PICCOLO", "PICCOLO_PICCOLO"]"
],
"dcheader.doc_date" : [
"[1569016800.0, 1571695140.0]"
],
"dcheader.cardnr" : [
"["0461000402452", "0461000402452"]"
]
},
"keysExamined" : 353400,
"seeks" : 353400,
"dupsTested" : 0,
"dupsDropped" : 0,
"indexDef" : {
"indexName" : "IndexCardnrDocDate",
"isMultiKey" : false,
"multiKeyPaths" : {
"hierarchy.company" : [ ],
"hierarchy.brand" : [ ],
"dcheader.doc_date" : [ ],
"dcheader.cardnr" : [ ]
},
"keyPattern" : {
"hierarchy.company" : 1,
"hierarchy.brand" : 1,
"dcheader.doc_date" : 1,
"dcheader.cardnr" : 1
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"direction" : "forward"
}
}
}
}
}
]
}
},
"ok" : 1,
"operationTime" : Timestamp(1571924213, 6),
"$clusterTime" : {
"clusterTime" : Timestamp(1571924213, 6),
"signature" : {
"hash" : BinData(0,"YOMe4acOH+iIfoHlsq/dCErNiZc="),
"keyId" : 6708447541475672066
}
}
}
As you can see, it seems like the SHARD_MERGE stage takes about 168000 millis: can anyone explain why?
Please note that I have never had such bad performances using a previuos MongoDb version (3.6).
Also I have not found detailed information about the SHARD_MERGE stage: which process does perform this task (mongod or mongos)? What happens during this stage?
My architecture is composed of three Centos 7 machines, each one hosts three mongod processes (shard1 replica set, shard2 replica set and config replica set) and a mongos process.
2
Answers
The stages shown in that explain plan, in order of execution are:
at mongod shard:
IXSCAN – compare the query against the index values
FETCH – retrieve full documents
SHARDING_FILTER – eliminate any documents not owned by the shard (i.e. orphans)
at mongos:
SHARD_MERGE – wait for, and then combine the result sets from the shards
The large disparity between the execution time on each shard and the SHARD_MERGE stage would suggest that either one shard started much later than the other, something delayed the response, or perhaps there is some clock skew going on here.
You can try to change the index order (if possible) to this.
As a general rule of thumb index fields should be in the following order.
In your case Range field (doc_date) is before the Equality Field (cardnr).