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?