0

I want to fetch all data from a collection. Since running just find({}) crashes the mongo container (the liveness probe seams to fail because k8s reboot the container), I ask myself if using skip/limit will be better in order to fragment fetch into multiple parts.

Is that query optimized?

db.getCollection('collectionName').find({}).skip(300000).limit(200000)

Mongo logs gives: planSummary: COLLSCAN. Is that normal? Index on _id should be used, isn't it?

Here is some logs while request is performed:

2019-05-05T15:27:48.383+0000 I COMMAND [conn7] command off.TrunkOff command: getMore { getMore: 19694089771, collection: "TrunkOff", batchSize: 1000, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } originatingCommand: { find: "TrunkOff", filter: {}, skip: 350000, limit: 100000, returnKey: false, showRecordId: false, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } planSummary: COLLSCAN cursorid:19694089771 keysExamined:0 docsExamined:1000 numYields:15 nreturned:1000 reslen:10290608 locks:{ Global: { acquireCount: { r: 16 } }, Database: { acquireCount: { r: 16 } }, Collection: { acquireCount: { r: 16 } } } storage:{ data: { bytesRead: 10311365, timeReadingMicros: 300059 } } protocol:op_query 315ms
2019-05-05T15:28:21.281+0000 I COMMAND [conn6] command off.TrunkOff command: getMore { getMore: 19694089771, collection: "TrunkOff", batchSize: 1000, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } originatingCommand: { find: "TrunkOff", filter: {}, skip: 350000, limit: 100000, returnKey: false, showRecordId: false, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } planSummary: COLLSCAN cursorid:19694089771 keysExamined:0 docsExamined:1000 numYields:20 nreturned:1000 reslen:11985643 locks:{ Global: { acquireCount: { r: 21 } }, Database: { acquireCount: { r: 21 } }, Collection: { acquireCount: { r: 21 } } } storage:{ data: { bytesRead: 12016653, timeReadingMicros: 414440 } } protocol:op_query 427ms
2019-05-05T15:30:19.713+0000 I COMMAND [conn6] command off.TrunkOff command: getMore { getMore: 19694089771, collection: "TrunkOff", batchSize: 1000, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } originatingCommand: { find: "TrunkOff", filter: {}, skip: 350000, limit: 100000, returnKey: false, showRecordId: false, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } planSummary: COLLSCAN cursorid:19694089771 keysExamined:0 docsExamined:1000 numYields:13 nreturned:1000 reslen:9986640 locks:{ Global: { acquireCount: { r: 14 } }, Database: { acquireCount: { r: 14 } }, Collection: { acquireCount: { r: 14 } } } storage:{ data: { bytesRead: 10017227, timeReadingMicros: 270095 } } protocol:op_query 287ms
2019-05-05T15:30:44.023+0000 I COMMAND [conn7] command off.TrunkOff command: getMore { getMore: 19694089771, collection: "TrunkOff", batchSize: 1000, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } originatingCommand: { find: "TrunkOff", filter: {}, skip: 350000, limit: 100000, returnKey: false, showRecordId: false, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } planSummary: COLLSCAN cursorid:19694089771 keysExamined:0 docsExamined:1000 numYields:9 nreturned:1000 reslen:9228715 locks:{ Global: { acquireCount: { r: 10 } }, Database: { acquireCount: { r: 10 } }, Collection: { acquireCount: { r: 10 } } } storage:{ data: { bytesRead: 9247329, timeReadingMicros: 102849 } } protocol:op_query 110ms
2019-05-05T15:31:14.048+0000 I COMMAND [conn6] command off.TrunkOff command: getMore { getMore: 19694089771, collection: "TrunkOff", batchSize: 1000, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } originatingCommand: { find: "TrunkOff", filter: {}, skip: 350000, limit: 100000, returnKey: false, showRecordId: false, lsid: { id: UUID("02770ee3-0b54-4b38-9b8e-23063b514434") }, $db: "off" } planSummary: COLLSCAN cursorid:19694089771 keysExamined:0 docsExamined:1000 numYields:21 nreturned:1000 reslen:11922134 locks:{ Global: { acquireCount: { r: 22 } }, Database: { acquireCount: { r: 22 } }, Collection: { acquireCount: { r: 22 } } } storage:{ data: { bytesRead: 11958959, timeReadingMicros: 410002 } } protocol:op_query 428ms

What is a good way to traverse a collection from end to end?

Here is cpu/memory usage. cpu goes max Virtual memory is Gb more than mapped memory

Virtual memory is gbs upper than mapped memory. cacheSizeGb is 1.

  • What is the MongoDB version(x,y,z)? – Md Haidar Ali Khan May 5 '19 at 10:47
  • 1
    Can you elaborate on your "crashes the mongo container" comment and the specific version of MongoDB? I suspect your issue is related to detecting available RAM in your Docker deployment. As per the MongoDB Production Notes: If you run mongod in a container (e.g. lxc, cgroups, Docker, etc.) that does not have access to all of the RAM available in a system, you must set storage.wiredTiger.engineConfig.cacheSizeGB to a value less than the amount of RAM available in the container. The default is 50% of (available RAM -1 GB). – Stennie May 7 '19 at 5:39
  • 1
    Note: if you upgrade to MongoDB 4.0.9+ (or the upcoming 3.6.13 release when available), improved detection of available RAM within containers should no longer require manual adjustment of the cacheSizeGB setting. – Stennie May 7 '19 at 5:43
0

As per MongoDB documentation here For commonly issued queries, create indexes. If a query searches multiple fields, create a compound index. Scanning an index is much faster than scanning a collection. The indexes structures are smaller than the documents reference, and store references in order.

For Example :

If you have a posts collection containing blog posts, and if you regularly issue a query that sorts on the author_name field, then you can optimize the query by creating an index on the author_name field:

db.posts.createIndex( { author_name : 1 } )

Indexes also improve efficiency on queries that routinely sort on a given field.

If you regularly issue a query that sorts on the timestamp field, then you can optimize the query by creating an index on the timestamp field:

Creating this index:

db.posts.createIndex( { timestamp : 1 } )

Optimizes this query:

db.posts.find().sort( { timestamp : -1 } )

Because MongoDB can read indexes in both ascending and descending order, the direction of a single-key index does not matter.

Where Indexes support queries, update operations, and some phases of the aggregation pipeline.

Use Projections to Return Only Necessary Data

When you need only a subset of fields from documents, you can achieve better performance by returning only the fields you need:

For example, if in your query to the posts collection, you need only the timestamp, title, author, and abstract fields, you would issue the following command:

db.posts.find( {}, { timestamp : 1 , title : 1 , author : 1 , abstract : 1} ).sort( { timestamp : -1 } )

If you want to Limit the Number of Documents to Return or to Set the Starting Point of the Result Set then use limit or skip method.

Limit the Number of Documents to Return

The limit() method limits the number of documents in the result set. The following operation returns at most 5 documents in the bios collection:

db.bios.find().limit( 5 )

Set the Starting Point of the Result Set

The skip() method controls the starting point of the results set. The following operation skips the first 5 documents in the bios collection and returns all remaining documents:

db.bios.find().skip( 5 )

For more information on using projections, see Project Fields to Return from Query.

|improve this answer|||||
  • Thanks you for your message, but it answer not my question.The request is find({}). I want all data from start to end, no filters. There is off course an index on the _id field, it's mandatory. But mongo is crashing. My question is, does skip/limit ok with millions documents. – Slim May 6 '19 at 7:52
  • @Slim,The limit() function in MongoDB is used to specify the maximum number of results to be returned. – Md Haidar Ali Khan May 6 '19 at 8:09
  • @Slim,If you shall use both skip/limit statement in your query statement then you shall not fetch all data. Due to their behavior. – Md Haidar Ali Khan May 6 '19 at 8:20
  • Off course. I add explanations – Slim May 6 '19 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.