StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMongoDB: very slow queries inside index
text
Body
copied!<p>I've seen number of similar questions, but they didn't resolve my issue (<a href="https://stackoverflow.com/questions/8250172/unreasonably-slow-mongodb-query-even-though-the-query-is-simple-and-aligned-to">1</a>, <a href="https://stackoverflow.com/questions/8720535/slow-mongodb-query-can-you-explain-why">2</a>, <a href="https://stackoverflow.com/questions/9734962/simple-mongodb-query-very-slow-although-index-is-set">3</a>).</p> <p>I have collection with almost 2M documents. My problem: very simple query (find({id: 53})) runs almost 5 minutes (see everything related info below).</p> <p>I tried to add index by the "id" field, tried to hint, <em>scanAndOrder</em> is false. I have anough memory (for now no swap and only 600MB totaly is used on server (still about 1GB is free)). It is debian 7 and mongodb 2.4.7.</p> <p>Maybe this matters: yesterday I ran repairDatabase and after that noticed the issue (but I cannot say for sure if it was before). But anyway I ran full validate on the collection and everything seems to be ok. Also yesterday I added one more index ({id: 1}). Also today I reindexed the collection.</p> <p>During all such long queries my disk is used very extensively.</p> <pre><code>> db.results.count() 1819411 > db.results.stats() { "ns" : "spider.results", "count" : 1819411, "size" : 26662218768, "avgObjSize" : 14654.313273911172, "storageSize" : 28309077856, "numExtents" : 34, "nindexes" : 4, "lastExtentSize" : 2146426864, "paddingFactor" : 1, "systemFlags" : 0, "userFlags" : 0, "totalIndexSize" : 207245248, "indexSizes" : { "_id_" : 53144000, "datetime_-1" : 45793776, "id_1_datetime_1" : 62513696, "id_1" : 45793776 }, "ok" : 1 } > db.results.getIndices() [ { "v" : 1, "key" : { "_id" : 1 }, "ns" : "spider.results", "name" : "_id_" }, { "v" : 1, "key" : { "datetime" : -1 }, "ns" : "spider.results", "name" : "datetime_-1" }, { "v" : 1, "key" : { "id" : 1, "datetime" : 1 }, "unique" : true, "ns" : "spider.results", "name" : "id_1_datetime_1" }, { "v" : 1, "key" : { "id" : 1 }, "ns" : "spider.results", "name" : "id_1" } ] > db.results.validate(true) { "ns" : "spider.results", "firstExtent" : "0:258000 ns:spider.results", "lastExtent" : "18:2000 ns:spider.results", "extentCount" : 34, "extents" : [ { "loc" : "0:258000", "xnext" : "0:25a000", "xprev" : "null", "nsdiag" : "spider.results", "size" : 8192, "firstRecord" : "0:2580b0", "lastRecord" : "0:2598b0" }, { "loc" : "0:25a000", "xnext" : "0:29d000", "xprev" : "0:258000", "nsdiag" : "spider.results", "size" : 274432, "firstRecord" : "0:25a0b0", "lastRecord" : "0:29c6b0" }, { "loc" : "0:29d000", "xnext" : "0:3a9000", "xprev" : "0:25a000", "nsdiag" : "spider.results", "size" : 1097728, "firstRecord" : "0:29d0b0", "lastRecord" : "0:3a6c30" }, { "loc" : "0:3a9000", "xnext" : "0:7d9000", "xprev" : "0:29d000", "nsdiag" : "spider.results", "size" : 4390912, "firstRecord" : "0:3a90b0", "lastRecord" : "0:7d8ab0" }, { "loc" : "0:7d9000", "xnext" : "0:d81000", "xprev" : "0:3a9000", "nsdiag" : "spider.results", "size" : 5931008, "firstRecord" : "0:7d90b0", "lastRecord" : "0:d7fcb0" }, { "loc" : "0:d81000", "xnext" : "0:1524000", "xprev" : "0:7d9000", "nsdiag" : "spider.results", "size" : 8007680, "firstRecord" : "0:d810b0", "lastRecord" : "0:15238b0" }, { "loc" : "0:1524000", "xnext" : "0:1f74000", "xprev" : "0:d81000", "nsdiag" : "spider.results", "size" : 10813440, "firstRecord" : "0:15240b0", "lastRecord" : "0:1f73ab0" }, { "loc" : "0:1f74000", "xnext" : "1:2000", "xprev" : "0:1524000", "nsdiag" : "spider.results", "size" : 14598144, "firstRecord" : "0:1f740b0", "lastRecord" : "0:2d5f6b0" }, { "loc" : "1:2000", "xnext" : "1:12ce000", "xprev" : "0:1f74000", "nsdiag" : "spider.results", "size" : 19709952, "firstRecord" : "1:20b0", "lastRecord" : "1:12cd8b0" }, { "loc" : "1:12ce000", "xnext" : "1:2c2f000", "xprev" : "1:2000", "nsdiag" : "spider.results", "size" : 26611712, "firstRecord" : "1:12ce0b0", "lastRecord" : "1:2c2eab0" }, { "loc" : "1:2c2f000", "xnext" : "1:4e72000", "xprev" : "1:12ce000", "nsdiag" : "spider.results", "size" : 35926016, "firstRecord" : "1:2c2f0b0", "lastRecord" : "1:4e719b0" }, { "loc" : "1:4e72000", "xnext" : "2:2000", "xprev" : "1:2c2f000", "nsdiag" : "spider.results", "size" : 48500736, "firstRecord" : "1:4e720b0", "lastRecord" : "1:7cb27b0" }, { "loc" : "2:2000", "xnext" : "2:3e74000", "xprev" : "1:4e72000", "nsdiag" : "spider.results", "size" : 65478656, "firstRecord" : "2:20b0", "lastRecord" : "2:3e71d30" }, { "loc" : "2:3e74000", "xnext" : "3:2000", "xprev" : "2:2000", "nsdiag" : "spider.results", "size" : 88399872, "firstRecord" : "2:3e740b0", "lastRecord" : "2:92c1ab0" }, { "loc" : "3:2000", "xnext" : "3:71d2000", "xprev" : "2:3e74000", "nsdiag" : "spider.results", "size" : 119341056, "firstRecord" : "3:20b0", "lastRecord" : "3:71d1ab0" }, { "loc" : "3:71d2000", "xnext" : "3:10b78000", "xprev" : "3:2000", "nsdiag" : "spider.results", "size" : 161112064, "firstRecord" : "3:71d20b0", "lastRecord" : "3:10b70ab0" }, { "loc" : "3:10b78000", "xnext" : "4:2000", "xprev" : "3:71d2000", "nsdiag" : "spider.results", "size" : 217501696, "firstRecord" : "3:10b780b0", "lastRecord" : "3:1dae29b0" }, { "loc" : "4:2000", "xnext" : "4:11809000", "xprev" : "3:10b78000", "nsdiag" : "spider.results", "size" : 293629952, "firstRecord" : "4:20b0", "lastRecord" : "4:118088b0" }, { "loc" : "4:11809000", "xnext" : "5:2000", "xprev" : "4:2000", "nsdiag" : "spider.results", "size" : 396402688, "firstRecord" : "4:118090b0", "lastRecord" : "4:29212930" }, { "loc" : "5:2000", "xnext" : "5:1fe5d000", "xprev" : "4:11809000", "nsdiag" : "spider.results", "size" : 535146496, "firstRecord" : "5:20b0", "lastRecord" : "5:1fe5ca30" }, { "loc" : "5:1fe5d000", "xnext" : "6:2000", "xprev" : "5:2000", "nsdiag" : "spider.results", "size" : 722448384, "firstRecord" : "5:1fe5d0b0", "lastRecord" : "5:4af553b0" }, { "loc" : "6:2000", "xnext" : "7:2000", "xprev" : "5:1fe5d000", "nsdiag" : "spider.results", "size" : 975306752, "firstRecord" : "6:20b0", "lastRecord" : "6:3a21d6b0" }, { "loc" : "7:2000", "xnext" : "8:2000", "xprev" : "6:2000", "nsdiag" : "spider.results", "size" : 1316667392, "firstRecord" : "7:20b0", "lastRecord" : "7:4e7ab0b0" }, { "loc" : "8:2000", "xnext" : "9:2000", "xprev" : "7:2000", "nsdiag" : "spider.results", "size" : 1777504256, "firstRecord" : "8:20b0", "lastRecord" : "8:69f278b0" }, { "loc" : "9:2000", "xnext" : "10:2000", "xprev" : "8:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "9:20b0", "lastRecord" : "9:7fefdab0" }, { "loc" : "10:2000", "xnext" : "11:2000", "xprev" : "9:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "10:20b0", "lastRecord" : "10:7feff630" }, { "loc" : "11:2000", "xnext" : "12:2000", "xprev" : "10:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "11:20b0", "lastRecord" : "11:7feffa30" }, { "loc" : "12:2000", "xnext" : "13:2000", "xprev" : "11:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "12:20b0", "lastRecord" : "12:7feffb30" }, { "loc" : "13:2000", "xnext" : "14:2000", "xprev" : "12:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "13:20b0", "lastRecord" : "13:7fefb7b0" }, { "loc" : "14:2000", "xnext" : "15:2000", "xprev" : "13:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "14:20b0", "lastRecord" : "14:7feffb30" }, { "loc" : "15:2000", "xnext" : "16:2000", "xprev" : "14:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "15:20b0", "lastRecord" : "15:7fefba30" }, { "loc" : "16:2000", "xnext" : "17:2000", "xprev" : "15:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "16:20b0", "lastRecord" : "16:7feff9b0" }, { "loc" : "17:2000", "xnext" : "18:2000", "xprev" : "16:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "17:20b0", "lastRecord" : "17:7feff9b0" }, { "loc" : "18:2000", "xnext" : "null", "xprev" : "17:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "18:20b0", "lastRecord" : "18:1f8328b0" } ], "datasize" : 26662218768, "nrecords" : 1819411, "lastExtentSize" : 2146426864, "padding" : 1, "firstExtentDetails" : { "loc" : "0:258000", "xnext" : "0:25a000", "xprev" : "null", "nsdiag" : "spider.results", "size" : 8192, "firstRecord" : "0:2580b0", "lastRecord" : "0:2598b0" }, "lastExtentDetails" : { "loc" : "18:2000", "xnext" : "null", "xprev" : "17:2000", "nsdiag" : "spider.results", "size" : 2146426864, "firstRecord" : "18:20b0", "lastRecord" : "18:1f8328b0" }, "objectsFound" : 1819411, "invalidObjects" : 0, "bytesWithHeaders" : 26691329344, "bytesWithoutHeaders" : 26662218768, "deletedCount" : 14, "deletedSize" : 1617742528, "nIndexes" : 4, "keysPerIndex" : { "spider.results.$_id_" : 1819411, "spider.results.$datetime_-1" : 1819411, "spider.results.$id_1_datetime_1" : 1819411, "spider.results.$id_1" : 1819411 }, "valid" : true, "errors" : [ ], "ok" : 1 } > db.results.find({id:6}).explain() { "cursor" : "BtreeCursor id_1_datetime_1", "isMultiKey" : false, "n" : 47402, "nscannedObjects" : 47402, "nscanned" : 47402, "nscannedObjectsAllPlans" : 47402, "nscannedAllPlans" : 47402, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 248, "nChunkSkips" : 0, "millis" : 254485, "indexBounds" : { "id" : [ [ 6, 6 ] ], "datetime" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "ip-10-137-56-52:27017" } </code></pre> <p>The only thing I haven't done yet is backup with repair and then restore. But I am not sure it will help...</p> <p>Any suggestions?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload