MongoDB: Slow query, even with index -
i have webpage, uses mongodb storing , retrieving various measurements. suddenly, in point, webpage became sluggish became unusable. turns out, database culprit.
i searched , have not found solution problem, , apologize, pretty new mongodb , pulling hair out @ moment.
version of mongodb using 2.4.6, on vm machine 20gb ram, runs ubuntu server 12.04. there no replica or sharding set up.
firstly, set profiling level 2 , revealed 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 ran specific query .explain() , looks like, uses index should, takes long. ran same query on another, drastically weaker server , sput out results champ in 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, looked indexes of measurement collection , looked fine me:
> 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 rest of information of 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 adding new index, repairing whole database, reindex. doing wrong? appreciate desperately ran out of ideas.
update 1:
i added 2 indexes suggested neil lunn, of queries lot faster:
{ "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 } results i've got interesting (not sure though relevant)
next 2 queries differs "id" only. please notice, each query uses different index, why? should delete older 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 problem somewhere else? cause sudden "sluggishness"? have several other collections, queries slower also.
oh, , thing. on other server have, indexes same here before added new ones. yes, collection bit smaller several times faster.
then point here in both index , query ordering selections.
if @ earlier output .explain() see that there "min/max" range on "t" element in expression. "moving end" of evaluation, allow other filtering elements more important overall expression ( determine less possible matches of "e" main factor before scanning though "t" in "everything".
it's little bit dba, in nosql world believe becomes programmer problem.
you need construct "shortest match path" along selected keys in order effective scan. why altered results executes faster.
Comments
Post a Comment