MongoDB find query taking more than 10 seconds

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?

Hello @Deep_Shah,

Here, the ratio of nReturned documents to totalDocsExamined is too high.
“nReturned” : 33288,
“executionTimeMillis” : 10819,
“totalKeysExamined” : 8784061,
“totalDocsExamined” : 8784061,

Index on the status field is being used, I would prefer if you can perform one test with Index field “status and date” and then check how query is performing.
Also if the index on date is already created, then filter date first then status.

4 Likes