Hi fellow community members,
We have been using MongoDB for over a year and a half. Recently, in the past week, we have observed that the query which took about milliseconds to complete is taking more than 10 seconds to return data.
After observing slow performance we switched to c5.large instance type which comes with:
**2 Dedicated Cores of Intel(R) Xeon(R) Platinum 8124M CPU @ 3.00GHz **
4GB RAM
Also tried switching the EBS volume from General Purpose (gp2) to Provisioned IOPS SSD ( io1
) with value of 600, but to no luck.
Please find more information below:
Collection with data like:
{
"_id" : ObjectId("5daff2657ca2680c8ace2d2e"),
"cron" : "peer_comparison_profit_loss",
"co_code" : "1",
"co_name" : "Company A",
"status" : "Pass",
"date" : ISODate("2019-10-23T00:00:00.000Z")
}
/* 2 */
{
"_id" : ObjectId("5daff2657ca2680c8ace2d2f"),
"cron" : "peer_comparison_financial_ratio",
"co_code" : "1",
"co_name" : "Company A",
"status" : "Pass",
"date" : ISODate("2019-10-23T00:00:00.000Z")
}
/* 3 */
{
"_id" : ObjectId("5daff2657ca2680c8ace2d30"),
"cron" : "price_nse",
"co_code" : "1",
"co_name" : "Company A",
"status" : "Pass",
"date" : ISODate("2019-10-23T00:00:00.000Z")
}
/* 4 */
{
"_id" : ObjectId("5daff2657ca2680c8ace2d31"),
"cron" : "peer_comparison_profit_loss",
"co_code" : "2",
"co_name" : "Company B",
"status" : "Pass",
"date" : ISODate("2019-10-23T00:00:00.000Z")
}
/* 5 */
{
"_id" : ObjectId("5daff2657ca2680c8ace2d32"),
"cron" : "peer_comparison_financial_ratio",
"co_code" : "2",
"co_name" : "Company B",
"status" : "Pass",
"date" : ISODate("2019-10-23T00:00:00.000Z")
}
/* 6 */
{
"_id" : ObjectId("5daff2657ca2680c8ace2d33"),
"cron" : "price_nse",
"co_code" : "2",
"co_name" : "Company B",
"status" : "Fail",
"date" : ISODate("2019-10-23T00:00:00.000Z")
}
Total Records: 13798861
Query:
db.cron_status.find({“status” : “Pass”,
“date” : ISODate(“2020-06-05T00:00:00.000Z”)}).explain(“executionStats”)
Execution Stats:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "sma.cron_status",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"date" : {
"$eq" : ISODate("2020-06-05T00:00:00.000Z")
}
},
{
"status" : {
"$eq" : "Pass"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"date" : {
"$eq" : ISODate("2020-06-05T00:00:00.000Z")
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"status" : 1.0
},
"indexName" : "status_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[\"Pass\", \"Pass\"]"
]
}
}
},
"rejectedPlans" : []
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 33288,
"executionTimeMillis" : 10819,
"totalKeysExamined" : 8784061,
"totalDocsExamined" : 8784061,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"date" : {
"$eq" : ISODate("2020-06-05T00:00:00.000Z")
}
},
"nReturned" : 33288,
"executionTimeMillisEstimate" : 683,
"works" : 8784062,
"advanced" : 33288,
"needTime" : 8750773,
"needYield" : 0,
"saveState" : 68625,
"restoreState" : 68625,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 8784061,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 8784061,
"executionTimeMillisEstimate" : 191,
"works" : 8784062,
"advanced" : 8784061,
"needTime" : 0,
"needYield" : 0,
"saveState" : 68625,
"restoreState" : 68625,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"status" : 1.0
},
"indexName" : "status_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"status" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"status" : [
"[\"Pass\", \"Pass\"]"
]
},
"keysExamined" : 8784061,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "localhost",
"port" : 27017,
"version" : "4.0.10",
"gitVersion" : "c389e7f69f637f7a1ac3cc9fae843b635f20b766"
},
"ok" : 1.0
}
This is just an example of a single collection we are facing with, there are multiple collections having slow query performance.
Let me know if you require further information.
Can someone please help us or guide us in the right direction?