Need help finding reasons for slow queries

I have a standalone MongoDB instance running on a 2 CPU 8GB RAM server. The collection in question has ~38.5k documents with an uncompressed size of 77.7GB (24.7GB storage size) along with 4 indexes (total index size = 2.2MB).

Running aggregation queries on this collection takes almost a minute to complete. Normal find() queries take more than 5 minutes. Below is the explain plan execution stats for one of the aggregation query:

{
    "executionSuccess": true,
    "nReturned": 1180.0,
    "executionTimeMillis": 91587.0,
    "totalKeysExamined": 1180.0,
    "totalDocsExamined": 1180.0,
    "executionStages": {
        "stage": "PROJECTION_SIMPLE",
        "nReturned": 1180.0,
        "executionTimeMillisEstimate": 78623.0,
        "works": 1181.0,
        "advanced": 1180.0,
        "needTime": 0.0,
        "needYield": 0.0,
        "saveState": 1117.0,
        "restoreState": 1117.0,
        "isEOF": 1.0,
        "transformBy": {
            "created_on": 1.0,
            "estimated_time_of_completion": 1.0,
            "group_name": 1.0,
            "hash": 1.0,
            "name": 1.0,
            "processing": 1.0,
            "progress": 1.0,
            "total_credits_required_email": 1.0,
            "total_credits_required_email_uid": 1.0,
            "total_credits_required_uid": 1.0,
            "total_emails_found": 1.0,
            "total_members": 1.0,
            "unlocked_email": 1.0,
            "unlocked_email_uid": 1.0,
            "unlocked_uid": 1.0,
            "_id": 0.0
        },
        "inputStage": {
            "stage": "FETCH",
            "nReturned": 1180.0,
            "executionTimeMillisEstimate": 78590.0,
            "works": 1181.0,
            "advanced": 1180.0,
            "needTime": 0.0,
            "needYield": 0.0,
            "saveState": 1117.0,
            "restoreState": 1117.0,
            "isEOF": 1.0,
            "docsExamined": 1180.0,
            "alreadyHasObj": 0.0,
            "inputStage": {
                "stage": "IXSCAN",
                "nReturned": 1180.0,
                "executionTimeMillisEstimate": 92.0,
                "works": 1181.0,
                "advanced": 1180.0,
                "needTime": 0.0,
                "needYield": 0.0,
                "saveState": 1117.0,
                "restoreState": 1117.0,
                "isEOF": 1.0,
                "keyPattern": {
                    "user_email": 1.0
                },
                "indexName": "user_email",
                "isMultiKey": false,
                "multiKeyPaths": {
                    "user_email": [
                    ]
                },
                "isUnique": false,
                "isSparse": false,
                "isPartial": false,
                "indexVersion": 2.0,
                "direction": "forward",
                "indexBounds": {
                    "user_email": [
                        "[\"shayaanmomin.sm@gmail.com\", \"shayaanmomin.sm@gmail.com\"]"
                    ]
                },
                "keysExamined": 1180.0,
                "seeks": 1.0,
                "dupsTested": 0.0,
                "dupsDropped": 0.0
            }
        }
    }
}

I also monitored server resource usage while running these queries. CPU seems to fluctuate between 20% - 37% while RAM is constantly at about 90%. The indexes seems to be working properly. Not sure what else to look out for.

Is this because of insufficient server resources or some other reason?

It is lack of server resources. Double your RAM until you are happy with speed. What is your permanent storage?

500 GB SSD (AWS EBS)

1 Like