Extremely slow queries on indexed multiKey field
35 views
Skip to first unread message
Gustavo Rodriguez
May 28, 2019, 8:32:48 PM5/28/19
to mongodb-user
Setup: MongoDB Cluster with MongoDB server version: 3.6.3
MongoDB shell version v3.6.0
10 Shards, 3 nodes per replica set, 16GB of RAM on each node, total Index
Size less than 10GB, which means that there's 50 times more RAM than the
indexes.
Amount of documents in the collection: 25M
Example document
{
countries: [ "co", "fr", "br" ],
... // other fields
}
Example slow queries I'm trying to run
db.collection.find({ countries: { $in: ["co"] } }, { "_id":1 }).sort({ "_id"
:-1 }).limit(50)
or
db.collection.find({ countries: { $all: ["co"] } }, { "_id":1 }).sort({
"_id":-1 }).limit(50)
Etc.
They all take from dozens of seconds (unusable) up to minutes. I'm starting
to wonder if there's a problem with the shards or I need to repair the
database, etc.
Tried many different combinations of the queries and even rebuilding the
field index, tried with hint, passing the field index, etc. Problem
persists.
One thing I noticed though is that the first time I run the query it takes
up to 2 minutes, but the second time, if done right away could take less
time. And if I wait, say 10 minutes it again takes a tremendous amount of
time to complete. Which probably means that it's using the cache but then
it clears it after a short while, but with 500GB of RAM it would be better
to keep the indexes or something.
Note: cluster is not running on SSDs, but with the amount of RAM it
shouldn't be a problem at the moment.
Explain output
db.collection.find({ countries:"co" }, { "_id":1 }).sort({ "_id": -1 }).
limit(50).explain('executionStats')
Output:
{ "queryPlanner":{
"mongosPlannerVersion":1,
"winningPlan":{
"stage":"SHARD_MERGE_SORT",
"shards":[
{
"shardName":"sh7rs7",
"connectionString":
"sh7rs7/node21:27017,node22:27017,node23:27017",
"serverInfo":{
"host":"mongo-cl1-s21",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh8rs8",
"connectionString":
"sh8rs8/node24:27017,node25:27017,node26:27017",
"serverInfo":{
"host":"mongo-cl1-s26",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
}
]
},
{
"shardName":"sh6rs6",
"connectionString":
"sh6rs6/node18:27017,node19:27017,node20:27017",
"serverInfo":{
"host":"mongo-cl1-s18",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh5rs5",
"connectionString":
"sh5rs5/node15:27017,node16:27017,node17:27017",
"serverInfo":{
"host":"mongo-cl1-s17",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh4rs4",
"connectionString":
"sh4rs4/node12:27017,node13:27017,node14:27017",
"serverInfo":{
"host":"mongo-cl1-s12",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh3rs3",
"connectionString":
"sh3rs3/node10:27017,node11:27017,node9:27017",
"serverInfo":{
"host":"mongo-cl1-s10",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh9rs9",
"connectionString":
"sh9rs9/node27:27017,node28:27017,node29:27017",
"serverInfo":{
"host":"mongo-cl1-s27",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
}
]
},
{
"shardName":"sh2rs2",
"connectionString":
"sh2rs2/node6:27017,node7:27017,node8:27017",
"serverInfo":{
"host":"mongo-cl1-s8",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
}
]
},
{
"shardName":"sh0rs0",
"connectionString":
"sh0rs0/node0:27017,node1:27017,node2:27017",
"serverInfo":{
"host":"mongo-cl1-s0",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh1rs1",
"connectionString":
"sh1rs1/node3:27017,node4:27017,node5:27017",
"serverInfo":{
"host":"mongo-cl1-s4",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
}
]
}
},
"executionStats":{
"nReturned":500,
"executionTimeMillis":16724,
"totalKeysExamined":92110,
"totalDocsExamined":92110,
"executionStages":{
"stage":"SHARD_MERGE_SORT",
"nReturned":500,
"executionTimeMillis":16724,
"totalKeysExamined":92110,
"totalDocsExamined":92110,
"totalChildMillis":NumberLong(121632),
"shards":[
{
"shardName":"sh7rs7",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":8356,
"works":9242,
"advanced":50,
"needTime":9191,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":8356,
"works":9242,
"advanced":50,
"needTime":9191,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":443988,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9189,
"executionTimeMillisEstimate":8316,
"works":9191,
"advanced":9189,
"needTime":1,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9189,
"executionTimeMillisEstimate":8316,
"works":9190,
"advanced":9189,
"needTime":0,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9189,
"executionTimeMillisEstimate":8256,
"works":9190,
"advanced":9189,
"needTime":0,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"docsExamined":9189,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9189,
"executionTimeMillisEstimate":20,
"works":9190,
"advanced":9189,
"needTime":0,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9189,
"seeks":1,
"dupsTested":9189,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh8rs8",
"executionSuccess":true,
"executionStages":{
"stage":"LIMIT",
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9931,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":1,
"invalidates":0,
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9930,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9930,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9930,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"docsExamined":9930,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9930,
"executionTimeMillisEstimate":20,
"works":9930,
"advanced":9930,
"needTime":0,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
},
"keysExamined":9930,
"seeks":1,
"dupsTested":0,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
},
{
"shardName":"sh6rs6",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":9968,
"works":8468,
"advanced":50,
"needTime":8417,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":9968,
"works":8468,
"advanced":50,
"needTime":8417,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":426152,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":8415,
"executionTimeMillisEstimate":9948,
"works":8417,
"advanced":8415,
"needTime":1,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":8415,
"executionTimeMillisEstimate":9948,
"works":8416,
"advanced":8415,
"needTime":0,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":8415,
"executionTimeMillisEstimate":9928,
"works":8416,
"advanced":8415,
"needTime":0,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"docsExamined":8415,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8415,
"executionTimeMillisEstimate":30,
"works":8416,
"advanced":8415,
"needTime":0,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":8415,
"seeks":1,
"dupsTested":8415,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh5rs5",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":11139,
"works":9914,
"advanced":50,
"needTime":9863,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":11139,
"works":9914,
"advanced":50,
"needTime":9863,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":481855,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9861,
"executionTimeMillisEstimate":11109,
"works":9863,
"advanced":9861,
"needTime":1,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9861,
"executionTimeMillisEstimate":11079,
"works":9862,
"advanced":9861,
"needTime":0,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9861,
"executionTimeMillisEstimate":11029,
"works":9862,
"advanced":9861,
"needTime":0,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"docsExamined":9861,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9861,
"executionTimeMillisEstimate":20,
"works":9862,
"advanced":9861,
"needTime":0,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9861,
"seeks":1,
"dupsTested":9861,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh4rs4",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":11632,
"works":8497,
"advanced":50,
"needTime":8446,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":11632,
"works":8497,
"advanced":50,
"needTime":8446,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":422140,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":8439,
"executionTimeMillisEstimate":11582,
"works":8446,
"advanced":8439,
"needTime":6,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":8439,
"executionTimeMillisEstimate":11572,
"works":8445,
"advanced":8439,
"needTime":5,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"chunkSkips":5,
"inputStage":{
"stage":"FETCH",
"nReturned":8444,
"executionTimeMillisEstimate":11542,
"works":8445,
"advanced":8444,
"needTime":0,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"docsExamined":8444,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8444,
"executionTimeMillisEstimate":30,
"works":8445,
"advanced":8444,
"needTime":0,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":8444,
"seeks":1,
"dupsTested":8444,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh3rs3",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":12908,
"works":9858,
"advanced":50,
"needTime":9807,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":12908,
"works":9858,
"advanced":50,
"needTime":9807,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":395432,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9805,
"executionTimeMillisEstimate":12908,
"works":9807,
"advanced":9805,
"needTime":1,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9805,
"executionTimeMillisEstimate":12888,
"works":9806,
"advanced":9805,
"needTime":0,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9805,
"executionTimeMillisEstimate":12858,
"works":9806,
"advanced":9805,
"needTime":0,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"docsExamined":9805,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9805,
"executionTimeMillisEstimate":10,
"works":9806,
"advanced":9805,
"needTime":0,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9805,
"seeks":1,
"dupsTested":9805,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh9rs9",
"executionSuccess":true,
"executionStages":{
"stage":"LIMIT",
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8783,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":1,
"invalidates":0,
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8782,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8782,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8782,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"docsExamined":8782,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8782,
"executionTimeMillisEstimate":10,
"works":8782,
"advanced":8782,
"needTime":0,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
},
"keysExamined":8782,
"seeks":1,
"dupsTested":0,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
},
{
"shardName":"sh2rs2",
"executionSuccess":true,
"executionStages":{
"stage":"LIMIT",
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9171,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":1,
"invalidates":0,
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9170,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9170,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9170,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"docsExamined":9170,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9170,
"executionTimeMillisEstimate":10,
"works":9170,
"advanced":9170,
"needTime":0,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
},
"keysExamined":9170,
"seeks":1,
"dupsTested":0,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
},
{
"shardName":"sh0rs0",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":14567,
"works":8907,
"advanced":50,
"needTime":8856,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":14567,
"works":8907,
"advanced":50,
"needTime":8856,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":584265,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":8854,
"executionTimeMillisEstimate":14527,
"works":8856,
"advanced":8854,
"needTime":1,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":8854,
"executionTimeMillisEstimate":14507,
"works":8855,
"advanced":8854,
"needTime":0,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":8854,
"executionTimeMillisEstimate":14467,
"works":8855,
"advanced":8854,
"needTime":0,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"docsExamined":8854,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8854,
"executionTimeMillisEstimate":30,
"works":8855,
"advanced":8854,
"needTime":0,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":8854,
"seeks":1,
"dupsTested":8854,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh1rs1",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":16549,
"works":9713,
"advanced":50,
"needTime":9662,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":16549,
"works":9713,
"advanced":50,
"needTime":9662,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":478403,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9660,
"executionTimeMillisEstimate":16499,
"works":9662,
"advanced":9660,
"needTime":1,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9660,
"executionTimeMillisEstimate":16499,
"works":9661,
"advanced":9660,
"needTime":0,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9660,
"executionTimeMillisEstimate":16429,
"works":9661,
"advanced":9660,
"needTime":0,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"docsExamined":9660,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9660,
"executionTimeMillisEstimate":20,
"works":9661,
"advanced":9660,
"needTime":0,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9660,
"seeks":1,
"dupsTested":9660,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
}
]
}
},
"ok":1,
"$clusterTime":{
"clusterTime":Timestamp(1559071991,
3 ),
"signature":{
"hash":BinData(0,
"1u7gD9bauuCB048rXdPUagCW7s0=" ),
"keyId":NumberLong("6662346986018045955")
}
},
"operationTime":Timestamp(1559071990,
1 )
}
MongoDB shell version v3.6.0
10 Shards, 3 nodes per replica set, 16GB of RAM on each node, total Index
Size less than 10GB, which means that there's 50 times more RAM than the
indexes.
Amount of documents in the collection: 25M
Example document
{
countries: [ "co", "fr", "br" ],
... // other fields
}
Example slow queries I'm trying to run
db.collection.find({ countries: { $in: ["co"] } }, { "_id":1 }).sort({ "_id"
:-1 }).limit(50)
or
db.collection.find({ countries: { $all: ["co"] } }, { "_id":1 }).sort({
"_id":-1 }).limit(50)
Etc.
They all take from dozens of seconds (unusable) up to minutes. I'm starting
to wonder if there's a problem with the shards or I need to repair the
database, etc.
Tried many different combinations of the queries and even rebuilding the
field index, tried with hint, passing the field index, etc. Problem
persists.
One thing I noticed though is that the first time I run the query it takes
up to 2 minutes, but the second time, if done right away could take less
time. And if I wait, say 10 minutes it again takes a tremendous amount of
time to complete. Which probably means that it's using the cache but then
it clears it after a short while, but with 500GB of RAM it would be better
to keep the indexes or something.
Note: cluster is not running on SSDs, but with the amount of RAM it
shouldn't be a problem at the moment.
Explain output
db.collection.find({ countries:"co" }, { "_id":1 }).sort({ "_id": -1 }).
limit(50).explain('executionStats')
Output:
{ "queryPlanner":{
"mongosPlannerVersion":1,
"winningPlan":{
"stage":"SHARD_MERGE_SORT",
"shards":[
{
"shardName":"sh7rs7",
"connectionString":
"sh7rs7/node21:27017,node22:27017,node23:27017",
"serverInfo":{
"host":"mongo-cl1-s21",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh8rs8",
"connectionString":
"sh8rs8/node24:27017,node25:27017,node26:27017",
"serverInfo":{
"host":"mongo-cl1-s26",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
}
]
},
{
"shardName":"sh6rs6",
"connectionString":
"sh6rs6/node18:27017,node19:27017,node20:27017",
"serverInfo":{
"host":"mongo-cl1-s18",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh5rs5",
"connectionString":
"sh5rs5/node15:27017,node16:27017,node17:27017",
"serverInfo":{
"host":"mongo-cl1-s17",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh4rs4",
"connectionString":
"sh4rs4/node12:27017,node13:27017,node14:27017",
"serverInfo":{
"host":"mongo-cl1-s12",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh3rs3",
"connectionString":
"sh3rs3/node10:27017,node11:27017,node9:27017",
"serverInfo":{
"host":"mongo-cl1-s10",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh9rs9",
"connectionString":
"sh9rs9/node27:27017,node28:27017,node29:27017",
"serverInfo":{
"host":"mongo-cl1-s27",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
}
]
},
{
"shardName":"sh2rs2",
"connectionString":
"sh2rs2/node6:27017,node7:27017,node8:27017",
"serverInfo":{
"host":"mongo-cl1-s8",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
}
]
},
{
"shardName":"sh0rs0",
"connectionString":
"sh0rs0/node0:27017,node1:27017,node2:27017",
"serverInfo":{
"host":"mongo-cl1-s0",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
},
{
"shardName":"sh1rs1",
"connectionString":
"sh1rs1/node3:27017,node4:27017,node5:27017",
"serverInfo":{
"host":"mongo-cl1-s4",
"port":27017,
"version":"3.6.3",
"gitVersion":"9586e557d54ef70f9ca4b43c26892cd55257e1a5"
},
"plannerVersion":1,
"namespace":"data.collection",
"indexFilterSet":false,
"parsedQuery":{
"countries":{
"$eq":"co"
}
},
"winningPlan":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"sortPattern":{
"_id":-1
},
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
}
}
}
}
}
}
},
"rejectedPlans":[
{
"stage":"LIMIT",
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"inputStage":{
"stage":"IXSCAN",
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
}
}
}
}
}
}
]
}
]
}
},
"executionStats":{
"nReturned":500,
"executionTimeMillis":16724,
"totalKeysExamined":92110,
"totalDocsExamined":92110,
"executionStages":{
"stage":"SHARD_MERGE_SORT",
"nReturned":500,
"executionTimeMillis":16724,
"totalKeysExamined":92110,
"totalDocsExamined":92110,
"totalChildMillis":NumberLong(121632),
"shards":[
{
"shardName":"sh7rs7",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":8356,
"works":9242,
"advanced":50,
"needTime":9191,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":8356,
"works":9242,
"advanced":50,
"needTime":9191,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":443988,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9189,
"executionTimeMillisEstimate":8316,
"works":9191,
"advanced":9189,
"needTime":1,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9189,
"executionTimeMillisEstimate":8316,
"works":9190,
"advanced":9189,
"needTime":0,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9189,
"executionTimeMillisEstimate":8256,
"works":9190,
"advanced":9189,
"needTime":0,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"docsExamined":9189,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9189,
"executionTimeMillisEstimate":20,
"works":9190,
"advanced":9189,
"needTime":0,
"needYield":0,
"saveState":391,
"restoreState":391,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9189,
"seeks":1,
"dupsTested":9189,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh8rs8",
"executionSuccess":true,
"executionStages":{
"stage":"LIMIT",
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9931,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":1,
"invalidates":0,
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9930,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9930,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"nReturned":50,
"executionTimeMillisEstimate":183,
"works":9930,
"advanced":50,
"needTime":9880,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"docsExamined":9930,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9930,
"executionTimeMillisEstimate":20,
"works":9930,
"advanced":9930,
"needTime":0,
"needYield":0,
"saveState":417,
"restoreState":417,
"isEOF":0,
"invalidates":0,
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
},
"keysExamined":9930,
"seeks":1,
"dupsTested":0,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
},
{
"shardName":"sh6rs6",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":9968,
"works":8468,
"advanced":50,
"needTime":8417,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":9968,
"works":8468,
"advanced":50,
"needTime":8417,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":426152,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":8415,
"executionTimeMillisEstimate":9948,
"works":8417,
"advanced":8415,
"needTime":1,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":8415,
"executionTimeMillisEstimate":9948,
"works":8416,
"advanced":8415,
"needTime":0,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":8415,
"executionTimeMillisEstimate":9928,
"works":8416,
"advanced":8415,
"needTime":0,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"docsExamined":8415,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8415,
"executionTimeMillisEstimate":30,
"works":8416,
"advanced":8415,
"needTime":0,
"needYield":0,
"saveState":441,
"restoreState":441,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":8415,
"seeks":1,
"dupsTested":8415,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh5rs5",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":11139,
"works":9914,
"advanced":50,
"needTime":9863,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":11139,
"works":9914,
"advanced":50,
"needTime":9863,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":481855,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9861,
"executionTimeMillisEstimate":11109,
"works":9863,
"advanced":9861,
"needTime":1,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9861,
"executionTimeMillisEstimate":11079,
"works":9862,
"advanced":9861,
"needTime":0,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9861,
"executionTimeMillisEstimate":11029,
"works":9862,
"advanced":9861,
"needTime":0,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"docsExamined":9861,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9861,
"executionTimeMillisEstimate":20,
"works":9862,
"advanced":9861,
"needTime":0,
"needYield":0,
"saveState":485,
"restoreState":485,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9861,
"seeks":1,
"dupsTested":9861,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh4rs4",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":11632,
"works":8497,
"advanced":50,
"needTime":8446,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":11632,
"works":8497,
"advanced":50,
"needTime":8446,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":422140,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":8439,
"executionTimeMillisEstimate":11582,
"works":8446,
"advanced":8439,
"needTime":6,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":8439,
"executionTimeMillisEstimate":11572,
"works":8445,
"advanced":8439,
"needTime":5,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"chunkSkips":5,
"inputStage":{
"stage":"FETCH",
"nReturned":8444,
"executionTimeMillisEstimate":11542,
"works":8445,
"advanced":8444,
"needTime":0,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"docsExamined":8444,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8444,
"executionTimeMillisEstimate":30,
"works":8445,
"advanced":8444,
"needTime":0,
"needYield":0,
"saveState":479,
"restoreState":479,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":8444,
"seeks":1,
"dupsTested":8444,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh3rs3",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":12908,
"works":9858,
"advanced":50,
"needTime":9807,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":12908,
"works":9858,
"advanced":50,
"needTime":9807,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":395432,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9805,
"executionTimeMillisEstimate":12908,
"works":9807,
"advanced":9805,
"needTime":1,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9805,
"executionTimeMillisEstimate":12888,
"works":9806,
"advanced":9805,
"needTime":0,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9805,
"executionTimeMillisEstimate":12858,
"works":9806,
"advanced":9805,
"needTime":0,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"docsExamined":9805,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9805,
"executionTimeMillisEstimate":10,
"works":9806,
"advanced":9805,
"needTime":0,
"needYield":0,
"saveState":529,
"restoreState":529,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9805,
"seeks":1,
"dupsTested":9805,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh9rs9",
"executionSuccess":true,
"executionStages":{
"stage":"LIMIT",
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8783,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":1,
"invalidates":0,
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8782,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8782,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"nReturned":50,
"executionTimeMillisEstimate":110,
"works":8782,
"advanced":50,
"needTime":8732,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"docsExamined":8782,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8782,
"executionTimeMillisEstimate":10,
"works":8782,
"advanced":8782,
"needTime":0,
"needYield":0,
"saveState":511,
"restoreState":511,
"isEOF":0,
"invalidates":0,
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
},
"keysExamined":8782,
"seeks":1,
"dupsTested":0,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
},
{
"shardName":"sh2rs2",
"executionSuccess":true,
"executionStages":{
"stage":"LIMIT",
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9171,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":1,
"invalidates":0,
"limitAmount":50,
"inputStage":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9170,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9170,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"filter":{
"countries":{
"$eq":"co"
}
},
"nReturned":50,
"executionTimeMillisEstimate":80,
"works":9170,
"advanced":50,
"needTime":9120,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"docsExamined":9170,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9170,
"executionTimeMillisEstimate":10,
"works":9170,
"advanced":9170,
"needTime":0,
"needYield":0,
"saveState":535,
"restoreState":535,
"isEOF":0,
"invalidates":0,
"keyPattern":{
"_id":1
},
"indexName":"_id_",
"isMultiKey":false,
"multiKeyPaths":{
"_id":[
]
},
"isUnique":true,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"backward",
"indexBounds":{
"_id":[
"[MaxKey, MinKey]"
]
},
"keysExamined":9170,
"seeks":1,
"dupsTested":0,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
},
{
"shardName":"sh0rs0",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":14567,
"works":8907,
"advanced":50,
"needTime":8856,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":14567,
"works":8907,
"advanced":50,
"needTime":8856,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":584265,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":8854,
"executionTimeMillisEstimate":14527,
"works":8856,
"advanced":8854,
"needTime":1,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":8854,
"executionTimeMillisEstimate":14507,
"works":8855,
"advanced":8854,
"needTime":0,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":8854,
"executionTimeMillisEstimate":14467,
"works":8855,
"advanced":8854,
"needTime":0,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"docsExamined":8854,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":8854,
"executionTimeMillisEstimate":30,
"works":8855,
"advanced":8854,
"needTime":0,
"needYield":0,
"saveState":590,
"restoreState":590,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":8854,
"seeks":1,
"dupsTested":8854,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
},
{
"shardName":"sh1rs1",
"executionSuccess":true,
"executionStages":{
"stage":"PROJECTION",
"nReturned":50,
"executionTimeMillisEstimate":16549,
"works":9713,
"advanced":50,
"needTime":9662,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"transformBy":{
"_id":1
},
"inputStage":{
"stage":"SORT",
"nReturned":50,
"executionTimeMillisEstimate":16549,
"works":9713,
"advanced":50,
"needTime":9662,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"sortPattern":{
"_id":-1
},
"memUsage":478403,
"memLimit":33554432,
"limitAmount":50,
"inputStage":{
"stage":"SORT_KEY_GENERATOR",
"nReturned":9660,
"executionTimeMillisEstimate":16499,
"works":9662,
"advanced":9660,
"needTime":1,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"inputStage":{
"stage":"SHARDING_FILTER",
"nReturned":9660,
"executionTimeMillisEstimate":16499,
"works":9661,
"advanced":9660,
"needTime":0,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"chunkSkips":0,
"inputStage":{
"stage":"FETCH",
"nReturned":9660,
"executionTimeMillisEstimate":16429,
"works":9661,
"advanced":9660,
"needTime":0,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"docsExamined":9660,
"alreadyHasObj":0,
"inputStage":{
"stage":"IXSCAN",
"nReturned":9660,
"executionTimeMillisEstimate":20,
"works":9661,
"advanced":9660,
"needTime":0,
"needYield":0,
"saveState":672,
"restoreState":672,
"isEOF":1,
"invalidates":0,
"keyPattern":{
"countries":1
},
"indexName":"countries_1",
"isMultiKey":true,
"multiKeyPaths":{
"countries":[
"countries"
]
},
"isUnique":false,
"isSparse":false,
"isPartial":false,
"indexVersion":2,
"direction":"forward",
"indexBounds":{
"countries":[
"[\"co\", \"co\"]"
]
},
"keysExamined":9660,
"seeks":1,
"dupsTested":9660,
"dupsDropped":0,
"seenInvalidated":0
}
}
}
}
}
}
}
]
}
},
"ok":1,
"$clusterTime":{
"clusterTime":Timestamp(1559071991,
3 ),
"signature":{
"hash":BinData(0,
"1u7gD9bauuCB048rXdPUagCW7s0=" ),
"keyId":NumberLong("6662346986018045955")
}
},
"operationTime":Timestamp(1559071990,
1 )
}
Gustavo Rodriguez
May 29, 2019, 6:51:11 PM5/29/19
to mongodb-user
I replaced all the disks with SSDs and the performance has been greatly improved.
Still takes up to 25 seconds for the query. I need this query to run in less than 3 seconds, which seems reasonable considering there's 30 total nodes and only 25M documents.
Should I add more shards, more replica set nodes, change the query, the data structures or something else?
Robert Cochran
May 29, 2019, 8:25:26 PM5/29/19
to mongodb-user
Hi,
You don't give details of your processor and motherboards on the systems you are using, but in my opinion you need multi-core Xeon-class processors, SuperMicro motherboards equipped with very high quality power supplies, and far more RAM. The new SSDs are a good first step.
I suggest you read the Production Notes.
Please note that I am not an employee of MongoDB, Inc. I am just another list user trying to be helpful.
Thanks so much
Bob
...
Reply all
Reply to author
Forward
0 new messages