MongoDB查询时被锁住的问题
1. 背景
mongoDB版本1.8.1
collection stats
> db.user.gift.stats()
{
"ns" : "statgame.user.gift",
"count" : 650,
"size" : 162804,
"avgObjSize" : 250.46769230769232,
"storageSize" : 696320,
"numExtents" : 4,
"nindexes" : 3,
"lastExtentSize" : 524288,
"paddingFactor" : 1.0099999999999576,
"flags" : 1,
"totalIndexSize" : 180224,
"indexSizes" : {
"_id_" : 40960,
"day_1_userId_1_fromId_1_sceneId_1" : 81920,
"idx_modified_time_long" : 57344
},
"ok" : 1
}
indexes
> db.user.gift.getIndexes()
[
{
"name" :"_id_",
"ns" :"statgame.user.gift",
"key" : {
"_id" : 1
}
},
{
"name" :"day_1_userId_1_fromId_1_sceneId_1",
"ns" :"statgame.user.gift",
"unique" : true,
"key" : {
"day" : 1,
"userId" :1,
"fromId" :1,
"sceneId" :1
}
},
{
"name" :"idx_modified_time_long",
"ns" :"statgame.user.gift",
"key" : {
"modifiedTimeLong" : -1
}
}
]
stored data的结构
> db.user.gift.find().limit(1)
{ "_id" :ObjectId("4dad7840c8b4ebc1064a1f25"), "day" :ISODate("2011-04-18T16:00:00Z"), "earnMoney" :NumberLong(18), "earnTimes" : NumberLong(1), "fromId" :NumberLong(17441), "modifiedTimeLong" :NumberLong("1303214144458"), "receiveMoney" :NumberLong(30), "receiveTimes" : NumberLong(1), "sceneId": NumberLong("216172782113787850"), "userId" :NumberLong(4037) }
2．在shell查询时不小心将sceneId的值直接输入了，mongoDB认为shell输入的都是double类型，而sceneId是长整型。216172782113787850为58bit。
db.user.gift.find({$query:{day:{$gte:newDate(2011,03,05),$lte:new Date()}, sceneId:216172782113787850},$orderby:{day:-1}})
这时这个查询会被block住。查看currentOp的执行情况。
> db.currentOp()
{
"inprog" : [
{
"opid" :756,
"active" :true,
"lockType": "read",
"waitingForLock" : false,
"secs_running" : 597,
"op" :"query",
"ns" :"statgame.user.gift",
"query" : {
"$query" : {
"day" : {
"$gte" : ISODate("2011-04-04T16:00:00Z"),
"$lte" : ISODate("2011-04-25T03:22:09.948Z")
},
"sceneId" :216172782113787840
},
"$orderby" : {
"day" : -1
}
},
"client" :"127.0.0.1:58166",
"desc" :"conn"
}
]
}
用top看系统资源使用情况：
top - 11:34:04 up 2:57, 6 users, load average: 1.67,1.13, 0.64
Tasks: 145 total, 3 running, 141 sleeping, 1 stopped, 0 zombie
Cpu(s): 50.2%us, 0.2%sy, 0.0%ni, 49.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4048136k total, 2092240kused, 1955896k free, 213088k buffers
Swap: 3068404k total, 0kused, 3068404k free, 936064k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3527 root 15 0 333m 21m 20m R 99.6 0.5 12:58.16 mongod
7050 root 19 0 1604m 655m 8512 S 2.316.6 2:53.05 java
占CPU接近100%，泪奔。
使用new NumberLong(“sceneId”)来查询，没有问题
>db.user.gift.find({$query:{day:{$gte:new Date(2011,03,05),$lte:new Date()},sceneId:newNumberLong("216172782113787850")},$orderby:{day:-1}})
{ "_id" :ObjectId("4db00226c8b4ebc1064a8b76"), "day" :ISODate("2011-04-19T16:00:00Z"), "earnMoney" :NumberLong(0), "earnTimes" : NumberLong(0), "fromId" : NumberLong(4124),"modifiedTimeLong" : NumberLong("1303380518261"),"receiveMoney" : NumberLong(50), "receiveTimes" :NumberLong(1), "sceneId" :NumberLong("216172782113787850"), "userId" :NumberLong(4124) }
{ "_id" :ObjectId("4db00226c8b4ebc1064a8b75"), "day" :ISODate("2011-04-19T16:00:00Z"), "earnMoney" :NumberLong(1005), "earnTimes" : NumberLong(113), "fromId": NumberLong(4042), "modifiedTimeLong" :NumberLong("1303380518320"), "receiveMoney" :NumberLong(3350), "receiveTimes" : NumberLong(113), "sceneId": NumberLong("216172782113787850"), "userId" :NumberLong(4124) }
………………..
将userId的值直接输入查询，userId目前小于4字节。没有问题。4字节整数是可以输入直接查询的。
>db.user.gift.find({$query:{day:{$gte:new Date(2011,03,05),$lte:new Date()},userId:4124},$orderby:{day:-1}})
{ "_id" :ObjectId("4db0021cc8b4ebc1064a86f3"), "day" :ISODate("2011-04-20T16:00:00Z"), "earnMoney" :NumberLong(1503), "earnTimes" : NumberLong(167), "fromId": NumberLong(4042), "modifiedTimeLong" :NumberLong("1303380509069"), "receiveMoney" :NumberLong(5010), "receiveTimes" : NumberLong(167),"sceneId" : NumberLong("792633972503933553"),"userId" : NumberLong(4124) }
{ "_id" :ObjectId("4db00226c8b4ebc1064a8b76"), "day" :ISODate("2011-04-19T16:00:00Z"), "earnMoney" :NumberLong(0), "earnTimes" : NumberLong(0), "fromId" :NumberLong(4124), "modifiedTimeLong" :NumberLong("1303380518261"), "receiveMoney" :NumberLong(50), "receiveTimes" : NumberLong(1), "sceneId": NumberLong("216172782113787850"), "userId" :NumberLong(4124) }
{ "_id" :ObjectId("4db00226c8b4ebc1064a8b75"), "day" :ISODate("2011-04-19T16:00:00Z"), "earnMoney" :NumberLong(1005), "earnTimes" : NumberLong(113), "fromId": NumberLong(4042), "modifiedTimeLong" :NumberLong("1303380518320"), "receiveMoney" :NumberLong(3350), "receiveTimes" : NumberLong(113),"sceneId" : NumberLong("216172782113787850"),"userId" : NumberLong(4124) }
………………….
浮点数一般用52bit的数表示精度，所以52bit的整型应该都没关系。
3.继续检查查询的哪步操作使得它block住了。
不用索引：dropIndex，或者查询时去掉day，用216172782113787850值可以查到。
> db.user.gift.find({sceneId:216172782113787850})
{ "cursor" :"BasicCursor", "nscanned" : 650,"nscannedObjects" : 650, "n" : 0, "millis" : 0,"nYields" : 0, "nChunkSkips" : 0, "isMultiKey": false, "indexOnly" : false, "indexBounds" : { },"allPlans" : [ { "cursor" : "BasicCursor","indexBounds" : { } } ], "oldPlan" : { "cursor": "BasicCursor", "indexBounds" : { } } }
>db.user.gift.find({$query:{sceneId:new NumberLong(216172782113787850)},$explain:true})
{ "cursor" :"BasicCursor", "nscanned" : 650, "nscannedObjects": 650, "n" : 0, "millis" : 0, "nYields" : 0,"nChunkSkips" : 0, "isMultiKey" : false,"indexOnly" : false, "indexBounds" : { },"allPlans" : [ { "cursor" : "BasicCursor","indexBounds" : { } } ], "oldPlan" : { "cursor": "BasicCursor", "indexBounds" : { } } }
>db.user.gift.find({sceneId:216172782113787850}})
Mon Apr 25 13:55:47 SyntaxError: missing) after argument list (shell):1
> db.user.gift.find({sceneId:216172782113787850})
{ "_id" :ObjectId("4dad7840c8b4ebc1064a1f26"), "day" :ISODate("2011-04-18T16:00:00Z"), "earnMoney" :NumberLong(4218), "earnTimes" : NumberLong(35), "fromId": NumberLong(262), "modifiedTimeLong" :NumberLong("1303214144775"), "receiveMoney" :NumberLong(10505), "receiveTimes" : NumberLong(35),"sceneId" : NumberLong("216172782113787850"),"userId" : NumberLong(4042)
………………….
看样子在做全表扫描时，mongoDB将每行NumberLong转成了double和sceneId的值进行了比较。
不用orderby：用216172782113787850值可以查到，
>db.user.gift.find({$query:{day:{$gte:new Date(2011,03,05),$lte:new Date()},sceneId:216172782113787850}})
用916172782113787850查，不存在也不会死
>db.user.gift.find({$query:{day:{$gte:new Date(2011,03,05),$lte:new Date()},sceneId:916172782113787850},$orderby:{day:-1}})
4.java端
java端使用时不会出现问题，javadriver会将long型转换成NumberLong查询
5总结
看样子block是由于orderby+索引+NumberLong用double值引起的。为什么呢？查不到应该也不至于block住，应该是mongoDB用索引查询时的问题。
页:
[1]