資料庫版本是mongodb3.0.6
整個collection的欄位有100個左右，總記錄在1190萬條，資料庫的大小看下面的db.stats()資訊
查詢的陳述句：
db.HA.find({"firstPlaceOrderDate":{"$gt":"2016-03-27 23:05:04","$lt":"2016-05-27 23:05:04"},"channelType":"A"})
a、這個時間的記錄在48萬條，{"firstPlaceOrderDate":{"$gt":"2016-03-27 23:05:04","$lt":"2016-05-27 23:05:04"}
b、這個型別在這個collection的記錄有700多萬條，"channelType":"A"
c、channelType欄位大概有6個左右不同的值
d、已經創建firstPlaceOrderDate，channelType的單獨索引，以及firstPlaceOrderDate，channelType的復合索引，還有其他欄位的索引幾個
現在的問題是：
1、db.HA.find({"firstPlaceOrderDate":{"$gt":"2016-03-27 23:05:04","$lt":"2016-05-27 23:05:04"},"channelType":"A"})這個查詢出來的資訊在15萬條，但是很慢，慢的時候十幾分鐘，有時候又很快（幾秒），但是大部分查詢的是很慢的
2、db.HA.find({"firstPlaceOrderDate":{"$gt":"2016-03-27 23:05:04","$lt":"2016-05-27 23:05:04"}})這個查詢幾秒可以出來
3、db.HA.find({"channelType":"A"})這個查詢幾秒可以出來，但比上面一個稍慢
一個下午沒找出是什么問題，各位大牛分析一下是什么原因？
有4臺服務器，具體配置看羨慕的服務器資訊，分片資訊：
mongos> db.runCommand({listshards:1});
{
"shards" : [
{
"_id" : "shard1",
"host" : "shard1/192.168.1.227:27511,192.168.1.228:27511"
},
{
"_id" : "shard2",
"host" : "shard2/192.168.1.227:27512,192.168.1.228:27512"
},
{
"_id" : "shard3",
"host" : "shard3/192.168.1.229:27513,192.168.1.238:27513"
},
{
"_id" : "shard4",
"host" : "shard4/192.168.1.229:27514,192.168.1.238:27514"
}
],
"ok" : 1
}
資料庫狀態資訊：
db.stats()
{
"raw" : {
"shard1/192.168.1.227:27511,192.168.1.228:27511" : {
"db" : "crm",
"collections" : 3,
"objects" : 11929678,
"avgObjSize" : 3445.3434585577247,
"dataSize" : 41101838060,
"storageSize" : 20942548992,
"numExtents" : 0,
"indexes" : 19,
"indexSize" : 4298305536,
"ok" : 1,
"$gleStats" : {
"lastOpTime" : Timestamp(0, 0),
"electionId" : ObjectId("568812c286e5e0430ea23569")
}
},
"shard2/192.168.1.227:27512,192.168.1.228:27512" : {
"db" : "crm",
"collections" : 2,
"objects" : 0,
"avgObjSize" : 0,
"dataSize" : 0,
"storageSize" : 4135374848,
"numExtents" : 0,
"indexes" : 17,
"indexSize" : 769343488,
"ok" : 1,
"$gleStats" : {
"lastOpTime" : Timestamp(0, 0),
"electionId" : ObjectId("56fc2c4387261bb3a0de6623")
}
},
"shard3/192.168.1.229:27513,192.168.1.238:27513" : {
"db" : "crm",
"collections" : 2,
"objects" : 0,
"avgObjSize" : 0,
"dataSize" : 0,
"storageSize" : 4478758912,
"numExtents" : 0,
"indexes" : 17,
"indexSize" : 726364160,
"ok" : 1,
"$gleStats" : {
"lastOpTime" : Timestamp(0, 0),
"electionId" : ObjectId("56c0bd7a5a6026f459389796")
}
},
"shard4/192.168.1.229:27514,192.168.1.238:27514" : {
"db" : "crm",
"collections" : 2,
"objects" : 0,
"avgObjSize" : 0,
"dataSize" : 0,
"storageSize" : 3615166464,
"numExtents" : 0,
"indexes" : 17,
"indexSize" : 628764672,
"ok" : 1,
"$gleStats" : {
"lastOpTime" : Timestamp(0, 0),
"electionId" : ObjectId("567dfa3913931fbd3753d2b3")
}
}
},
"objects" : 11929678,
"avgObjSize" : 3445,
"dataSize" : 41101838060,
"storageSize" : 33171849216,
"numExtents" : 0,
"indexes" : 70,
"indexSize" : 6422777856,
"fileSize" : 0,
"extentFreeList" : {
"num" : 0,
"totalSize" : 0
},
"ok" : 1
}
服務器配置資訊
CPU
[root@BDMongo01 bin]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 62
Stepping: 4
CPU MHz: 1900.150
BogoMIPS: 3800.30
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 20480K
NUMA node0 CPU(s): 0-15
記憶體64G
top資訊
[root@BDMongo01 bin]# top
top - 10:51:14 up 118 days, 19:00, 2 users, load average: 5.38, 6.11, 6.42
Tasks: 291 total, 1 running, 290 sleeping, 0 stopped, 0 zombie
Cpu(s): 6.2%us, 5.6%sy, 0.0%ni, 78.0%id, 10.2%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 65921144k total, 65528876k used, 392268k free, 1572k buffers
Swap: 32964600k total, 3953260k used, 29011340k free, 4578808k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9377 mongodb 20 0 35.7g 29g 4760 S 187.2 47.5 8651:19 mongod
9397 mongodb 20 0 31.4g 27g 3692 S 1.0 43.7 2514:48 mongod
9007 mongodb 20 0 614m 59m 36m S 0.3 0.1 1208:31 mongod
9320 mongodb 20 0 327m 36m 2896 S 0.3 0.1 906:00.81 mongos
19173 root 20 0 15164 1392 944 R 0.3 0.0 0:00.04 top
1 root 20 0 19356 644 428 S 0.0 0.0 0:01.23 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.52 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:47.60 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:09.21 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:00.47 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:35.63 ksoftirqd/1
磁盤資訊
[root@BDMongo01 bin]# df -lh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_227-lv_root 885G 79G 762G 10% /
tmpfs 32G 0 32G 0% /dev/shm
/dev/sda1 485M 39M 421M 9% /boot
[root@BDMongo01 bin]# free
total used free shared buffers cached
Mem: 65921144 65570648 350496 0 1872 4569296
-/+ buffers/cache: 60999480 4921664
Swap: 32964600 3903920 29060680
uj5u.com熱心網友回復：
補充一下執行計劃資訊：
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "shard1",
"connectionString" : "shard1/192.168.1.227:27511,192.168.1.228:27511",
"serverInfo" : {
"host" : "BDMongo01",
"port" : 27511,
"version" : "3.0.6",
"gitVersion" : "1ef45a23a4c5e3480ac919b28afcba3c615488f2"
},
"plannerVersion" : 1,
"namespace" : "crm.HA",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"channelType" : {
"$eq" : "ebay"
}
},
{
"firstPlaceOrderDate" : {
"$lt" : "2016-05-27 23:05:04"
}
},
{
"firstPlaceOrderDate" : {
"$gt" : "2016-03-27 23:05:04"
}
}
]
},
"winningPlan" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"firstPlaceOrderDate" : 1,
"channelType" : 1
},
"indexName" : "firstPlaceOrderDate_1_channelType_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"firstPlaceOrderDate" : [
"(\"2016-03-27 23:05:04\", \"2016-05-27 23:05:04\")"
],
"channelType" : [
"[\"ebay\", \"ebay\"]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"channelType" : {
"$eq" : "ebay"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"firstPlaceOrderDate" : 1
},
"indexName" : "firstPlaceOrderDate_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"firstPlaceOrderDate" : [
"(\"2016-03-27 23:05:04\", \"2016-05-27 23:05:04\")"
]
}
}
}
},
{
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"firstPlaceOrderDate" : {
"$lt" : "2016-05-27 23:05:04"
}
},
{
"firstPlaceOrderDate" : {
"$gt" : "2016-03-27 23:05:04"
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"channelType" : 1
},
"indexName" : "channelType_1",
"isMultiKey" : false,
"direction" : "forward",
"indexBounds" : {
"channelType" : [
"[\"ebay\", \"ebay\"]"
]
}
}
}
}
]
}
]
}
},
"ok" : 1
}
uj5u.com熱心網友回復：
再補充一下由于是新手，不知道4臺服務器這樣分片是否正確？
mongos是3個的
[root@BDMongo01 bin]# ps aux | grep mongodb
/mongos -f /home/mongodb/mongos/mongod.conf --configdb 192.168.1.227:27501,192.168.1.228:27501,192.168.1.238:27501
/mongod -f /home/mongodb/shard1/mongod.conf --shardsvr --replSet shard1
/mongod -f /home/mongodb/shard2/mongod.conf --shardsvr --replSet shard2
/mongod -f /home/mongodb/shard3/mongod.conf --shardsvr --replSet shard3
[root@BDMongo02 bin]# ps aux | grep mongodb
/mongos -f /home/mongodb/mongos/mongod.conf --configdb 192.168.0.227:27501,192.168.0.228:27501,192.168.0.238:27501
/mongod -f /home/mongodb/shard1/mongod.conf --shardsvr --replSet shard1
/mongod -f /home/mongodb/shard2/mongod.conf --shardsvr --replSet shard2
/mongod -f /home/mongodb/shard4/mongod.conf --shardsvr --replSet shard4
[root@BDMongo03 bin]# ps aux | grep mongodb
/mongos -f /home/mongodb/mongos/mongod.conf --configdb 192.168.0.227:27501,192.168.0.228:27501,192.168.0.238:27501
/mongod -個的f /home/mongodb/shard1/mongod.conf --shardsvr --replSet shard1
/mongod -f /home/mongodb/shard3/mongod.conf --shardsvr --replSet shard3
/mongod -f /home/mongodb/shard4/mongod.conf --shardsvr --replSet shard4
[root@BDMongo04 bin]# ps aux | grep mongodb
/mongos -f /home/mongodb/mongos/mongod.conf --configdb 192.168.0.227:27501,192.168.0.228:27501,192.168.0.238:27501
/mongod -f /home/mongodb/shard2/mongod.conf --shardsvr --replSet shard2
/mongod -f /home/mongodb/shard3/mongod.conf --shardsvr --replSet shard3
/mongod -f /home/mongodb/shard4/mongod.conf --shardsvr --replSet shard4
uj5u.com熱心網友回復：
以下SQL 怎么變成MongoDB查詢？where
(
(
(' ' + con_FullName like '% C%')
and (' ' + con_FullName like '% F%'
)
and len(con_FullName) <> 13
)
or
(
(' ' + con_FullName like '% C%' or ' ' + con_FullName like '% F%')
and len(con_FullName) = 13)
)
uj5u.com熱心網友回復：
查詢里面你強制使用 firstPlaceOrderDate_1_channelType_1這個索引試試如果還是慢再把這個索引順序改一下，改成channelType_1_firstPlaceOrderDate_1
uj5u.com熱心網友回復：
樓主您好，您的問題解決了嗎？我也出現了類似的問題，collection集合記錄近700W，查詢欄位的索引也建立了，剛建立索引時，查詢速度非常快 不到一秒，但是等到第二天再以同樣的查詢條件去查的時候查詢速度變的非常慢，需要3-5秒。這個問題纏了好久了，敢問有沒有大神幫解決下啊。。。uj5u.com熱心網友回復：
復合索引建的有問題。應當equal 欄位在前，范圍查詢在后。
另外這個表有做sharding嗎？shard key是哪幾個？
uj5u.com熱心網友回復：
樓主的問題解決了么？我現在也遇到類似的問題，如果長時間不訪問資料庫，在第一次查詢時很慢，之后的查詢就正常了。uj5u.com熱心網友回復：
記憶體不夠嗎， 索引資料沒有在記憶體中，需要磁盤IO交換到記憶體uj5u.com熱心網友回復：
樓主解決了嗎，同樣遇到類似的問題，時間長不訪問第一次訪問速度很慢。然后再訪問速度就正常了轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103732.html
標籤：MongoDB