I have a webpage that uses MongoDB to store and retrieve various dimensions. Suddenly, at some point, my webpage became so sluggish that it became unusable. It turns out my database is the culprit.
I searched and did not find a solution for my problem, and I apologize since I am pretty new to MongoDB and am pulling my hair out right now.
The version of MongoDB I am using is 2.4.6 on a VM machine with 20 GB of RAM, which runs the Ubuntu 12.04 server. No replicas or splinters.
First, I set the profiling level to 2 and showed the slowest query:
db.system.profile.find().sort({"millis":-1}).limit(1).pretty() { "op" : "query", "ns" : "station.measurement", "query" : { "$query" : { "e" : { "$gte" : 0 }, "id" : "180" }, "$orderby" : { "t" : -1 } }, "ntoreturn" : 1, "ntoskip" : 0, "nscanned" : 3295221, "keyUpdates" : 0, "numYield" : 6, "lockStats" : { "timeLockedMicros" : { "r" : NumberLong(12184722), "w" : NumberLong(0) }, "timeAcquiringMicros" : { "r" : NumberLong(5636351), "w" : NumberLong(5) } }, "nreturned" : 0, "responseLength" : 20, "millis" : 6549, "ts" : ISODate("2015-03-16T08:57:07.772Z"), "client" : "127.0.0.1", "allUsers" : [ ], "user" : "" }
I executed this specific request with .explain () and it seems to use the index as it should, but it takes too much time. I also used the same query on my other, very weak server and posted the results like a champion in a second.
> db.measurement.find({"id":"180", "e":{$gte:0}}).sort({"t":-1}).explain() { "cursor" : "BtreeCursor id_1_t_-1_e_1", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 0, "nscanned" : 660385, "nscannedObjectsAllPlans" : 1981098, "nscannedAllPlans" : 3301849, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 7, "nChunkSkips" : 0, "millis" : 7243, "indexBounds" : { "id" : [ [ "180", "180" ] ], "t" : [ [ { "$maxElement" : 1 }, { "$minElement" : 1 } ] ], "e" : [ [ 0, 1.7976931348623157e+308 ] ] }, "server" : "station:27017" }
Next, I looked at the dimension collection indexes, and it looked great:
> db.measurement.getIndexes() [ { "v" : 1, "key" : { "_id" : 1 }, "ns" : "station.measurement", "name" : "_id_" }, { "v" : 1, "key" : { "t" : 1 }, "ns" : "station.measurement", "name" : "t_1" }, { "v" : 1, "key" : { "id" : 1, "d" : 1, "_id" : -1 }, "ns" : "station.measurement", "name" : "id_1_d_1__id_-1" }, { "v" : 1, "key" : { "id" : 1, "t" : -1, "e" : 1 }, "ns" : "station.measurement", "name" : "id_1_t_-1_e_1" }, { "v" : 1, "key" : { "id" : 1, "t" : -1, "e" : -1 }, "ns" : "station.measurement", "name" : "id_1_t_-1_e_-1" } ]
Here is the rest of the information in my collection:
> db.measurement.stats() { "ns" : "station.measurement", "count" : 157835456, "size" : 22377799512, "avgObjSize" : 141.77929395027692, "storageSize" : 26476834672, "numExtents" : 33, "nindexes" : 5, "lastExtentSize" : 2146426864, "paddingFactor" : 1.0000000000028617, "systemFlags" : 0, "userFlags" : 0, "totalIndexSize" : 30996614096, "indexSizes" : { "_id_" : 6104250656, "t_1" : 3971369360, "id_1_d_1__id_-1" : 8397896640, "id_1_t_-1_e_1" : 6261548720, "id_1_t_-1_e_-1" : 6261548720 }, "ok" : 1 }
I tried to add a new index, restoring the whole database, reindexing. What am I doing wrong? I really appreciate any help, as I desperately ended up with ideas.
UPDATE 1:
I added two indexes suggested by Neil Lun, some of them are faster than LOT:
{ "v" : 1, "key" : { "id" : 1, "e" : 1, "t" : -1 }, "ns" : "station.measurement", "name" : "id_1_e_1_t_-1", "background" : true }, { "v" : 1, "key" : { "id" : 1, "e" : -1, "t" : -1 }, "ns" : "station.measurement", "name" : "id_1_e_-1_t_-1", "background" : true }
The results that I have are interesting (not sure, although they are relevant)
The following two queries differ only in "id". Please note that each query uses a different index, why? Should I delete old ones?
> db.measurement.find({"id":"119", "e":{$gte:0}}).sort({"t":-1}).explain() { "cursor" : "BtreeCursor id_1_t_-1_e_1", "isMultiKey" : false, "n" : 840747, "nscannedObjects" : 840747, "nscanned" : 1047044, "nscannedObjectsAllPlans" : 1056722, "nscannedAllPlans" : 1311344, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 4, "nChunkSkips" : 0, "millis" : 3730, "indexBounds" : { "id" : [ [ "119", "119" ] ], "t" : [ [ { "$maxElement" : 1 }, { "$minElement" : 1 } ] ], "e" : [ [ 0, 1.7976931348623157e+308 ] ] }, "server" : "station:27017" } > db.measurement.find({"id":"180", "e":{$gte:0}}).sort({"t":-1}).explain() { "cursor" : "BtreeCursor id_1_e_1_t_-1", "isMultiKey" : false, "n" : 0, "nscannedObjects" : 0, "nscanned" : 0, "nscannedObjectsAllPlans" : 0, "nscannedAllPlans" : 45, "scanAndOrder" : true, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 0, "indexBounds" : { "id" : [ [ "180", "180" ] ], "e" : [ [ 0, 1.7976931348623157e+308 ] ], "t" : [ [ { "$maxElement" : 1 }, { "$minElement" : 1 } ] ] }, "server" : "station:27017" }
Could the problem be somewhere else? What can cause this sudden "slowness"? I have several other collections where queries are also getting slower.
Oh and one more thing. On this other server, my indexes are the same as here before I added new ones. Yes, the collection is a little smaller, but several times faster.