I have around 10 collections each have approx 20 million docs and increasing at speed of around 700-1000 writes per second at the same time 700-1000 read per second is made by querying through my api. Avg query time is 30-40ms per. Issue is that after 150 req/sec (using jmeter). My Avg response time is 3-7 sec which is really bad. I have M50 paid cluster (1 primary 2 secondary)
Below are the possible optimization that I have tried.
eg query: {OpId: ‘12394’,S_TIME: {$gt: 1873703716,$lt: 1973711146},time: ‘ACTIVE’}.sort({Sorter:1})
- Compound indexing on [OpId,S_TIME,status,Sorter] (all ascending)
- Aleady Have 1 master and 2 secondary replica on Atlas
- Tried using .explain() but query seems optimized already (giving 30-40ms resp time)
- Tried using redis to cache data. Initially it makes response fast but since data of each key is bigger and as more key accumulates redis create issue as it is single threaded and does blocking operation
Here’s the query explain
{
"explainVersion": "1",
"queryPlanner": {
"namespace": "Test.StockData",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{ "status": { "$eq": "Active" } },
{ "OpId": { "$eq": "11536" } },
{ "S_TIME": { "$lt": 1374085800 } },
{ "S_TIME": { "$gt": 1374049700 } }
]
},
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "FETCH",
"inputStage": {
"stage": "SORT",
"sortPattern": { "Sorter": 1 },
"memLimit": 104857600,
"type": "default",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"OpId": 1,
"S_TIME": 1,
"status": 1,
"Sorter": 1
},
"indexName": "OpId_1_S_TIME_1_status_1_Sorter_1",
"isMultiKey": false,
"multiKeyPaths": {
"OpId": [],
"S_TIME": [],
"status": [],
"Sorter": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"OpId": ["[\"11536\", \"11536\"]"],
"S_TIME": [
"(1374049700, 1374085800)"
],
"status": [
"[\"Active\", \"Active\"]"
],
"Sorter": ["[MinKey, MaxKey]"]
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 5674,
"executionTimeMillis": 33,
"totalKeysExamined": 5675,
"totalDocsExamined": 5674,
"executionStages": {
"stage": "FETCH",
"nReturned": 5674,
"executionTimeMillisEstimate": 22,
"works": 11350,
"advanced": 5674,
"needTime": 5675,
"needYield": 0,
"saveState": 11,
"restoreState": 11,
"isEOF": 1,
"docsExamined": 5674,
"alreadyHasObj": 0,
"inputStage": {
"stage": "SORT",
"nReturned": 5674,
"executionTimeMillisEstimate": 7,
"works": 11350,
"advanced": 5674,
"needTime": 5675,
"needYield": 0,
"saveState": 11,
"restoreState": 11,
"isEOF": 1,
"sortPattern": { "Sorter": 1 },
"memLimit": 104857600,
"type": "default",
"totalDataSizeSorted": 453920,
"usedDisk": false,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 5674,
"executionTimeMillisEstimate": 1,
"works": 5675,
"advanced": 5674,
"needTime": 0,
"needYield": 0,
"saveState": 11,
"restoreState": 11,
"isEOF": 1,
"keyPattern": {
"OpId": 1,
"S_TIME": 1,
"status": 1,
"Sorter": 1
},
"indexName": "OpId_1_S_TIME_1_status_1_Sorter_1",
"isMultiKey": false,
"multiKeyPaths": {
"OpId": [],
"S_TIME": [],
"status": [],
"Sorter": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"OpId": ["[\"11536\", \"11536\"]"],
"S_TIME": [
"(1374049700, 1374085800)"
],
"status": [
"[\"Active\", \"Active\"]"
],
"Sorter": ["[MinKey, MaxKey]"]
},
"keysExamined": 5675,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
},
"allPlansExecution": []
},
"command": {
"find": "StockData",
"filter": {
"OpId": "11536",
"S_TIME": {
"$gt": 1374049700,
"$lt": 1374085800
},
"status": "Active"
},
"sort": { "Sorter": 1 },
"skip": 0,
"limit": 0,
"maxTimeMS": 30000,
"$db": "Test"
},
"serverInfo": {
"host": "ip-10-23-52-181.ap-south-1.compute.internal",
"port": 7210,
"version": "5.0.2",
"gitVersion": "6d9ec525e78465dcecadcff99cce953d380fedc8"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600
},
"ok": 1
}
What shall I do to get around atleast 1000 req per seconds with avg resp time of less then 150-200ms.