论坛首页 → 综合技术论坛 →

MongoDB查询时被锁住的问题

全部 Linux 数据库敏捷编程数据结构软件测试项目管理 Oracle 编程综合互联网 Erlang MySQL

浏览 4621 次

锁定老帖子主题：MongoDB查询时被锁住的问题

精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)

作者

正文

ldd600
等级:
性别:
文章: 52
积分: 220
来自: 上海

发表时间：2011-04-25 最后修改：2011-04-25

相关推荐:

更多相关推荐

数据库

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:new Date(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, 2092240k used, 1955896k free, 213088k buffers

Swap: 3068404k total, 0k used, 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.3 16.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:new NumberLong("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) }

………………….

浮点数一般用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})

> 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端使用时不会出现问题，java driver会将long型转换成NumberLong查询

5总结

看样子block是由于orderby+索引+NumberLong用double值引起的。为什么呢？查不到应该也不至于block住，应该是mongoDB用索引查询时的问题。

声明：ITeye文章版权属于作者，受法律保护。没有作者书面许可不得转载。

推荐链接

返回顶楼

论坛首页 → 综合技术版

跳转论坛: