问题描述:

Mongodb version is 2.0.

I have collection like this structure:

 {

"_id" : ObjectId("4fc1225eec13839b46f400e4"),

"wb" : {

"trip_plan" : [{

"op_sta" : "242H",

"in_tm" : ISODate("2012-07-30T018:00:00Z"),

"out_tm" : ISODate("2012-07-30T19:02:00Z"),

}, {

"op_sta" : "10563",

"in_tm" : ISODate("2012-07-30T20:00:00Z"),

"out_tm" : ISODate("2012-07-30T21:00:00Z")

}, {

"op_sta" : "10451",

"in_tm" : ISODate("2012-07-31T06:00:00Z"),

"out_tm" : ISODate("2012-07-31T08:00:00Z"),

}]

}

}

trip_plan arr is big. average length is about 30-40. but I have no way to get only matched record in version 2.0.

My application is kind of write heavy application.

collection stats like :

 {

"ns" : "itcp.car_location",

"count" : 476509,

"size" : 11794651412,

"avgObjSize" : 24752.21121112088,

"storageSize" : 13324562416,

"numExtents" : 34,

"nindexes" : 17,

"lastExtentSize" : 2146426864,

"paddingFactor" : 1.009999999878954,

"flags" : 0,

"totalIndexSize" : 2178233568,

"indexSizes" : {

"_id_" : 22418592,

"wb.wb_sn_1" : 19516112,

"next_wb.wb_sn_1" : 7914368,

"wb.trip_plan.op_sta_1_wb.trip_plan.in_trn_1_wb.trip_plan.in_orgn_dt_1" : 315757120,

"next_wb.trip_plan.op_sta_1_next_wb.trip_plan.in_trn_1_next_wb.trip_plan.in_orgn_dt_1" : 84065632,

"wb.trip_plan.op_sta_1_wb.trip_plan.out_trn_1_wb.trip_plan.out_orgn_dt_1" : 319387264,

"next_wb.trip_plan.op_sta_1_next_wb.trip_plan.out_trn_1_next_wb.trip_plan.out_orgn_dt_1" : 95487504,

"last_op-last_evt_type-status"" : 30758112,

"modify_ts" : 22091552,

"ActlInTrn" : 245263648,

"ActlOutTrn" : 243301408,

"eq_nr_init" : 23105376,

"event_os_trn_orgnDt" : 97719552,

"wb_ibTrn_os_ts" : 255475472,

"wb_obTrn_os_ts" : 243056128,

"next_wb_ibTrn_os_ts" : 76658176,

"next_wb_ObTrn_os_ts" : 76257552

},

"ok" : 1

}

My Index like this :

wb_obTrn_os_ts

{

"wb.trip_plan.op_sta" : 1,

"wb.trip_plan.out_tm":1

}

when I do the query by the below

 db.car_location.find({ "wb.trip_plan": {$elemMatch: {"op_sta" :"148H",

"out_tm" : {$gt: ISODate("2012-08-02T04:00:00.000Z"),

$lt: ISODate("2012-08-03T04:00:00.000Z")}}}} ).explain();

... ... {

"cursor" : "BtreeCursor wb_obTrn_os_ts",

"nscanned" : 45624,

"nscannedObjects" : 45624,

"n" : 2889,

"millis" : 378954,

"nYields" : 3858,

"nChunkSkips" : 0,

"isMultiKey" : true,

"indexOnly" : false,

"indexBounds" : {

"wb.trip_plan.op_sta" : [

[

"148H",

"148H"

]

],

"wb.trip_plan.out_tm" : [

[

{

"$minElement" : 1

},

{

"$maxElement" : 1

}

]

]

}

}

My mongodb server RAM is 16G .

from the explain , you can see it hitted index and has no scanAndOrder.

It takes too long to get result !!

I was already blocked by this issue for days!!

Thanks a lot for your rescue!!

insert query update delete getmore command flushes mapped vsize res faults locked % idx miss % qr|qw ar|aw netIn netOut conn set repl time

1 50 26 0 6 48 0 76.3g 154g 11.9g 223 15.3 0 8|3 9|3 147k 525k 74 abc2g_repl_set M 11:48:33

0 57 40 0 3 44 0 76.3g 154g 11.9g 158 12.4 0 6|4 8|4 101k 624k 74 abc2g_repl_set M 11:48:34

0 43 30 0 2 36 0 76.3g 154g 11.9g 84 13.2 0 5|1 7|1 93k 442k 74 abc2g_repl_set M 11:48:35

0 5 6 0 6 17 0 76.3g 154g 12g 281 2.4 0 1|1 6|0 13k 63k 74 abc2g_repl_set M 11:48:36

0 38 9 0 7 8 0 76.3g 154g 12.1g 256 2.1 0 0|0 5|0 41k 183k 74 abc2g_repl_set M 11:48:37

0 44 0 0 0 29 0 76.3g 154g 12.1g 359 0 0 0|0 5|0 5k 208k 74 abc2g_repl_set M 11:48:38

0 17 0 0 0 1 0 76.3g 154g 12.1g 316 0 0 0|0 6|0 1k 74k 74 abc2g_repl_set M 11:48:39

0 27 3 0 2 11 0 76.3g 154g 12.2g 225 0.3 0 3|0 5|0 12k 161k 74 abc2g_repl_set M 11:48:40

0 29 5 0 4 11 0 76.3g 154g 12.2g 275 3.2 0 0|0 5|0 24k 251k 74 abc2g_repl_set M 11:48:41

0 42 7 0 6 27 0 76.3g 154g 12.2g 197 12.4 0 0|0 5|0 14k 245k 74 abc2g_repl_set M 11:48:42

which part of information should I pay attention ?

网友答案:

@Johny Wang

Are you able to solve this? here is few quick query/try you can do

  1. do you really want $elemMatch? ( you know best about how it is structured!)
  2. Can you try aggregate ? when having $and $elemMatch on two different field (in complex query) Mongodb query optimizer is behaving weird. You can try pipelining these two operation and it will be superfast (atleast in most of my queries, of similar type!)
相关阅读:
Top