Atlas faster read write?

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})

  1. Compound indexing on [OpId,S_TIME,status,Sorter] (all ascending)
  2. Aleady Have 1 master and 2 secondary replica on Atlas
  3. Tried using .explain() but query seems optimized already (giving 30-40ms resp time)
  4. 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.

Hi @Nikunj_Guna and welcome to MongoDB community forums!!

Sharding in MongoDB allows you to scale your database to handle increased load to a nearly unlimited degree by providing increased read/write throughput , storage capacity , and high availability. This would be beneficial to manage the read and writes across the sharded cluster. Please note that, selection of the right shard key plays an important role in sharding of the collections.

Secondly, could you also reconsider the indexes which are created for the query executed and if it does improves the performance of the application in terms of the most frequently query being used.

Tor the average timings mentioned above, are these for the same query being executed multiple times?
If there are more than one query involved, can you help me with the exaplin() output for the slow and the fast queries being executed?

As you mentioned that you are using M50 paid tier cluster on Atlas, which gives you the leverage to use the built-in features like Performance Advisor, Real-Time Performance Panel, and Query Profiler to track operations and highlight slow/heavy spotted operations. Additionally, the Metrics tab provides many graphs that plot operations and number of connections.
It would be helpful for us to debug further, if you provide the above information or open a cloud support ticket with all information in place.

Regards
Aasawari

Thanks for replay,

Yes for now I am benchmarking using same queries.I have also tried different combinations of indexes but still slow. What confuses me is that I have collection containing 350million doc. But there is no issue there. I also tried keep same index in my collection since almost every field are present in both collection’s docs. But still no luck. I have already attached explain() o/p in post for slow query

here’s the explain for fast one in which 350million docs are there having similar structure and compound index.

{
  "explainVersion": "1",
  "queryPlanner": {
    "namespace": "LiveFeed.NSE_E_EQUITY_BKP",
    "indexFilterSet": false,
    "parsedQuery": {
      "$and": [
        { "Exch": { "$eq": "NSE" } },
        { "ScripId": { "$eq": "11536" } },
        { "Start_Time": { "$lt": 1374040852 } },
        { "Start_Time": { "$gt": 1373615400 } }
      ]
    },
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "Exch": 1,
          "ScripId": 1,
          "Start_Time": -1
        },
        "indexName": "sym",
        "isMultiKey": false,
        "multiKeyPaths": {
          "Exch": [],
          "ScripId": [],
          "Start_Time": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "Exch": ["[\"NSE\", \"NSE\"]"],
          "ScripId": ["[\"11536\", \"11536\"]"],
          "Start_Time": [
            "(1374040852, 1373615400)"
          ]
        }
      }
    },
    "rejectedPlans": [
      {
        "stage": "FETCH",
        "filter": {
          "$and": [
            { "Exch": { "$eq": "NSE" } },
            { "ScripId": { "$eq": "11536" } }
          ]
        },
        "inputStage": {
          "stage": "IXSCAN",
          "keyPattern": { "Start_Time": -1 },
          "indexName": "Start_Time",
          "isMultiKey": false,
          "multiKeyPaths": { "Start_Time": [] },
          "isUnique": false,
          "isSparse": false,
          "isPartial": false,
          "indexVersion": 2,
          "direction": "forward",
          "indexBounds": {
            "Start_Time": [
              "(1374040852, 1373615400)"
            ]
          }
        }
      }
    ]
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 1016,
    "executionTimeMillis": 2,
    "totalKeysExamined": 1016,
    "totalDocsExamined": 1016,
    "executionStages": {
      "stage": "FETCH",
      "nReturned": 1016,
      "executionTimeMillisEstimate": 0,
      "works": 1017,
      "advanced": 1016,
      "needTime": 0,
      "needYield": 0,
      "saveState": 1,
      "restoreState": 1,
      "isEOF": 1,
      "docsExamined": 1016,
      "alreadyHasObj": 0,
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": 1016,
        "executionTimeMillisEstimate": 0,
        "works": 1017,
        "advanced": 1016,
        "needTime": 0,
        "needYield": 0,
        "saveState": 1,
        "restoreState": 1,
        "isEOF": 1,
        "keyPattern": {
          "Exch": 1,
          "ScripId": 1,
          "Start_Time": -1
        },
        "indexName": "sym",
        "isMultiKey": false,
        "multiKeyPaths": {
          "Exch": [],
          "ScripId": [],
          "Start_Time": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "Exch": ["[\"NSE\", \"NSE\"]"],
          "ScripId": ["[\"11536\", \"11536\"]"],
          "Start_Time": [
            "(1374040852, 1373615400)"
          ]
        },
        "keysExamined": 1016,
        "seeks": 1,
        "dupsTested": 0,
        "dupsDropped": 0
      }
    },
    "allPlansExecution": [
      {
        "nReturned": 101,
        "executionTimeMillisEstimate": 0,
        "totalKeysExamined": 101,
        "totalDocsExamined": 101,
        "executionStages": {
          "stage": "FETCH",
          "nReturned": 101,
          "executionTimeMillisEstimate": 0,
          "works": 101,
          "advanced": 101,
          "needTime": 0,
          "needYield": 0,
          "saveState": 0,
          "restoreState": 0,
          "isEOF": 0,
          "docsExamined": 101,
          "alreadyHasObj": 0,
          "inputStage": {
            "stage": "IXSCAN",
            "nReturned": 101,
            "executionTimeMillisEstimate": 0,
            "works": 101,
            "advanced": 101,
            "needTime": 0,
            "needYield": 0,
            "saveState": 0,
            "restoreState": 0,
            "isEOF": 0,
            "keyPattern": {
              "Exch": 1,
              "ScripId": 1,
              "Start_Time": -1
            },
            "indexName": "sym",
            "isMultiKey": false,
            "multiKeyPaths": {
              "Exch": [],
              "ScripId": [],
              "Start_Time": []
            },
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 2,
            "direction": "forward",
            "indexBounds": {
              "Exch": ["[\"NSE\", \"NSE\"]"],
              "ScripId": [
                "[\"11536\", \"11536\"]"
              ],
              "Start_Time": [
                "(1374040852, 1373615400)"
              ]
            },
            "keysExamined": 101,
            "seeks": 1,
            "dupsTested": 0,
            "dupsDropped": 0
          }
        }
      },
      {
        "nReturned": 0,
        "executionTimeMillisEstimate": 0,
        "totalKeysExamined": 101,
        "totalDocsExamined": 101,
        "executionStages": {
          "stage": "FETCH",
          "filter": {
            "$and": [
              { "Exch": { "$eq": "NSE" } },
              { "ScripId": { "$eq": "11536" } }
            ]
          },
          "nReturned": 0,
          "executionTimeMillisEstimate": 0,
          "works": 101,
          "advanced": 0,
          "needTime": 101,
          "needYield": 0,
          "saveState": 1,
          "restoreState": 1,
          "isEOF": 0,
          "docsExamined": 101,
          "alreadyHasObj": 0,
          "inputStage": {
            "stage": "IXSCAN",
            "nReturned": 101,
            "executionTimeMillisEstimate": 0,
            "works": 101,
            "advanced": 101,
            "needTime": 0,
            "needYield": 0,
            "saveState": 1,
            "restoreState": 1,
            "isEOF": 0,
            "keyPattern": { "Start_Time": -1 },
            "indexName": "Start_Time",
            "isMultiKey": false,
            "multiKeyPaths": { "Start_Time": [] },
            "isUnique": false,
            "isSparse": false,
            "isPartial": false,
            "indexVersion": 2,
            "direction": "forward",
            "indexBounds": {
              "Start_Time": [
                "(1374040852, 1373615400)"
              ]
            },
            "keysExamined": 101,
            "seeks": 1,
            "dupsTested": 0,
            "dupsDropped": 0
          }
        }
      }
    ]
  },
  "command": {
    "find": "NSE_E_EQUITY_BKP",
    "filter": {
      "ScripId": "11536",
      "Exch": "NSE",
      "Start_Time": {
        "$gt": 1373615400,
        "$lt": 1374040852
      }
    },
    "sort": { "Start_Time": -1 },
    "skip": 0,
    "limit": 0,
    "maxTimeMS": 30000,
    "$db": "LiveFeed"
  },
  "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
}

The first explain you posted has an in-memory sort.

The best index for that query is one supporting the ESR “rule”

An index of {OpId:1, status:1, Sorter:1, S_TIME:1} should support the query well.

The equality OpId, status are first, followed by the sort order Sorter, then the range S_TIME.

Looking at the explain though that might only save you ~7ms as most of the time is spent fetching the documents(22ms).

@chris @Aasawari
I tried using ESR rule also different combinations , along with asc and desc both direction but still no luck.
{OpId:1, status:1, Sorter:1, S_TIME:1}
{OpId:1, status:1, Sorter:1, S_TIME:-1}
{status:1, OpId:1, Sorter:1, S_TIME:-1}
{status:1, OpId:1, Sorter:1, S_TIME:1}
{OpId:1, Sorter:1, S_TIME:1}
{OpId:1, Sorter:1, S_TIME:-1}
etc etc

Here’s the explain of {status:1, OpId:1, Sorter:1, S_TIME:1}

for query : {MarketStatus: ‘OPEN’,ScripId: ‘11536’,Start_Time: {$gt: 1374121800,$lt: 1374127252}}

{
  "explainVersion": "1",
  "queryPlanner": {
    "namespace": "LiveFeed.NSE_E_EQUITY_SEC_BKP",
    "indexFilterSet": false,
    "parsedQuery": {
      "$and": [
        { "MarketStatus": { "$eq": "OPEN" } },
        { "ScripId": { "$eq": "11536" } },
        { "Start_Time": { "$lt": 1374127252 } },
        { "Start_Time": { "$gt": 1374121800 } }
      ]
    },
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "MarketStatus": 1,
          "ScripId": 1,
          "Sorter": 1,
          "Start_Time": 1
        },
        "indexName": "MarketStatus_1_ScripId_1_Sorter_1_Start_Time_1",
        "isMultiKey": false,
        "multiKeyPaths": {
          "MarketStatus": [],
          "ScripId": [],
          "Sorter": [],
          "Start_Time": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "MarketStatus": [
            "[\"OPEN\", \"OPEN\"]"
          ],
          "ScripId": ["[\"11536\", \"11536\"]"],
          "Sorter": ["[MinKey, MaxKey]"],
          "Start_Time": [
            "(1374121800, 1374127252)"
          ]
        }
      }
    },
    "rejectedPlans": []
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 9064,
    "executionTimeMillis": 140,
    "totalKeysExamined": 69779,
    "totalDocsExamined": 9064,
    "executionStages": {
      "stage": "FETCH",
      "nReturned": 9064,
      "executionTimeMillisEstimate": 34,
      "works": 69779,
      "advanced": 9064,
      "needTime": 60714,
      "needYield": 0,
      "saveState": 69,
      "restoreState": 69,
      "isEOF": 1,
      "docsExamined": 9064,
      "alreadyHasObj": 0,
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": 9064,
        "executionTimeMillisEstimate": 33,
        "works": 69779,
        "advanced": 9064,
        "needTime": 60714,
        "needYield": 0,
        "saveState": 69,
        "restoreState": 69,
        "isEOF": 1,
        "keyPattern": {
          "MarketStatus": 1,
          "ScripId": 1,
          "Sorter": 1,
          "Start_Time": 1
        },
        "indexName": "MarketStatus_1_ScripId_1_Sorter_1_Start_Time_1",
        "isMultiKey": false,
        "multiKeyPaths": {
          "MarketStatus": [],
          "ScripId": [],
          "Sorter": [],
          "Start_Time": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "MarketStatus": [
            "[\"OPEN\", \"OPEN\"]"
          ],
          "ScripId": ["[\"11536\", \"11536\"]"],
          "Sorter": ["[MinKey, MaxKey]"],
          "Start_Time": [
            "(1374121800, 1374127252)"
          ]
        },
        "keysExamined": 69779,
        "seeks": 60715,
        "dupsTested": 0,
        "dupsDropped": 0
      }
    },
    "allPlansExecution": []
  },
  "command": {
    "find": "NSE_E_EQUITY_SEC_BKP",
    "filter": {
      "MarketStatus": "OPEN",
      "ScripId": "11536",
      "Start_Time": {
        "$gt": 1374121800,
        "$lt": 1374127252
      }
    },
    "sort": { "Sorter": 1 },
    "skip": 0,
    "limit": 0,
    "maxTimeMS": 30000,
    "$db": "LiveFeed"
  },
  "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
}