mongodb - Mongodb搜索分片键很慢
问题描述
我使用bitnami的图表设置了一个带有3个分片的mongodb：mongodb-sharded ，我遇到了两个问题。
我的分片集合有超过 3800 万条记录。
第一个问题是一些 mongodb pod 占用了很多内存，一个节点 32G 的整个内存超过了 10G。我从 stackoverflow 中发现了一些类似的问题，我会尝试解决这个问题。
另一个问题是：当我进行与分片键匹配的搜索时，它非常慢，有时会超时。
这是我收藏的索引：
ice-shard-mongodb-sharded-shard-0:PRIMARY> db.LogInfo.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 2,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
}
},
{
"v" : 2,
"key" : {
"Properties.Time" : 1
},
"name" : "Properties.Time_1",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 2,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
}
},
{
"v" : 2,
"key" : {
"Properties.Plot" : 1,
"Properties.Time" : 1
},
"name" : "Properties.Plot_1_Properties.Time_1",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 2,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
}
},
{
"v" : 2,
"key" : {
"Properties.Scenario" : 1,
"Properties.Time" : 1
},
"name" : "Properties.Scenario_1_Properties.Time_1",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 2,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
}
},
{
"v" : 2,
"key" : {
"Properties.SecsFunction" : 1,
"Properties.Time" : 1
},
"name" : "Properties.SecsFunction_1_Properties.Time_1",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 2,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
}
},
{
"v" : 2,
"key" : {
"Properties.EapId" : "hashed",
"Properties.Time" : 1
},
"name" : "Properties.EapId_hashed_Properties.Time_1"
}
]
键“Properties.EapId_hashed_Properties.Time_1”是分片键。
如果我搜索
db.LogInfo.find({"Properties.SecsFunction": "s6f11", "Properties.Time": {$gte: ISODate("2021-03-10")}})
此搜索匹配一个索引，它将搜索所有 3 个分片。它在 0.01 秒内非常快。
但如果我搜索
db.LogInfo.find(
{
"Properties.EapId": "12eap012",
"Properties.Time": {
"$gte": ISODate("2021-03-17")
}
}
)
应该使用分片键，我认为，它非常流畅。有时需要超过 5 秒，有时会超时。我已经检查了几次索引，但我不知道为什么。
请指教。谢谢。
更新：MongoDB版本：4.4.3
更新：分片构建为副本集 = 3。
更新：以下是有问题的搜索的查询计划。（由于 stackoverflow 的 30000 个字符限制，我放弃了 executionStat()。）
{
"queryPlanner" : {
"mongosPlannerVersion" : NumberInt(1),
"winningPlan" : {
"stage" : "SHARD_MERGE",
"shards" : [
{
"shardName" : "ice-shard-mongodb-sharded-shard-1",
"connectionString" : "ice-shard-mongodb-sharded-shard-1/ice-shard-mongodb-sharded-shard1-data-0.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017,ice-shard-mongodb-sharded-shard1-data-1.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017,ice-shard-mongodb-sharded-shard1-data-2.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017",
"serverInfo" : {
"host" : "ice-shard-mongodb-sharded-shard1-data-1",
"port" : NumberInt(27017),
"version" : "4.4.3",
"gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
},
"plannerVersion" : NumberInt(1),
"namespace" : "LogCenter.LogInfo",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Properties.EapId" : {
"$eq" : "12eap012"
}
},
{
"Properties.Time" : {
"$gte" : ISODate("2021-03-17T00:00:00.000+0000")
}
}
]
},
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : NumberInt(2),
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"winningPlan" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"Properties.EapId" : {
"$eq" : "12eap012"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Properties.Time" : 1.0
},
"indexName" : "Properties.Time_1",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : NumberInt(2),
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"isMultiKey" : false,
"multiKeyPaths" : {
"Properties.Time" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"Properties.Time" : [
"[new Date(1615939200000), new Date(9223372036854775807)]"
]
}
}
}
},
"rejectedPlans" : [
]
},
{
"shardName" : "ice-shard-mongodb-sharded-shard-0",
"connectionString" : "ice-shard-mongodb-sharded-shard-0/ice-shard-mongodb-sharded-shard0-data-0.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017,ice-shard-mongodb-sharded-shard0-data-1.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017,ice-shard-mongodb-sharded-shard0-data-2.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017",
"serverInfo" : {
"host" : "ice-shard-mongodb-sharded-shard0-data-0",
"port" : NumberInt(27017),
"version" : "4.4.3",
"gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
},
"plannerVersion" : NumberInt(1),
"namespace" : "LogCenter.LogInfo",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Properties.EapId" : {
"$eq" : "12eap012"
}
},
{
"Properties.Time" : {
"$gte" : ISODate("2021-03-17T00:00:00.000+0000")
}
}
]
},
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : NumberInt(2),
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"winningPlan" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"Properties.EapId" : {
"$eq" : "12eap012"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Properties.Time" : 1.0
},
"indexName" : "Properties.Time_1",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : NumberInt(2),
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"isMultiKey" : false,
"multiKeyPaths" : {
"Properties.Time" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"Properties.Time" : [
"[new Date(1615939200000), new Date(9223372036854775807)]"
]
}
}
}
},
"rejectedPlans" : [
]
},
{
"shardName" : "ice-shard-mongodb-sharded-shard-2",
"connectionString" : "ice-shard-mongodb-sharded-shard-2/ice-shard-mongodb-sharded-shard2-data-0.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017,ice-shard-mongodb-sharded-shard2-data-1.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017,ice-shard-mongodb-sharded-shard2-data-2.ice-shard-mongodb-sharded-headless.ice-system.svc.cluster.local:27017",
"serverInfo" : {
"host" : "ice-shard-mongodb-sharded-shard2-data-0",
"port" : NumberInt(27017),
"version" : "4.4.3",
"gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
},
"plannerVersion" : NumberInt(1),
"namespace" : "LogCenter.LogInfo",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"Properties.EapId" : {
"$eq" : "12eap012"
}
},
{
"Properties.Time" : {
"$gte" : ISODate("2021-03-17T00:00:00.000+0000")
}
}
]
},
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : NumberInt(2),
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"winningPlan" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"Properties.EapId" : {
"$eq" : "12eap012"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"Properties.Time" : 1.0
},
"indexName" : "Properties.Time_1",
"collation" : {
"locale" : "en_US",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : NumberInt(2),
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"isMultiKey" : false,
"multiKeyPaths" : {
"Properties.Time" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : NumberInt(2),
"direction" : "forward",
"indexBounds" : {
"Properties.Time" : [
"[new Date(1615939200000), new Date(9223372036854775807)]"
]
}
}
}
},
"rejectedPlans" : [
]
}
]
}
},
"serverInfo" : {
"host" : "ice-shard-mongodb-sharded-mongos-59576fb8b8-n47n5",
"port" : NumberInt(27017),
"version" : "4.4.3",
"gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
},
"ok" : 1.0,
"operationTime" : Timestamp(1616372015, 12),
"$clusterTime" : {
"clusterTime" : Timestamp(1616372015, 14),
"signature" : {
"hash" : BinData(0, "vVxiZGic6un9QIInadoHCfBCX2Y="),
"keyId" : NumberLong(6924833040434724866)
}
}
}
解决方案
我找到了我的搜索没有使用分片键的根本原因。这都是关于整理的。
分片键始终用于collation : { locale : "simple" }进行二进制比较，而在我的情况下，我的集合和索引旨在使用"collation" : { "locale" : "en_US", "strength" : 2 }.
在没有任何整理的情况下重新设计我的收藏后，现在一切都很好。
推荐阅读
- javascript - Onclick 切换开关
- javascript - 让 Datatables 和 Sparklines 一起玩得很好？
- sql - 当复合键和“始终作为身份生成”选项不起作用时，如何在 Oracle 中创建主键？
- ios - iOS AFNetwork 3.0：是否有更快的方法来发送多个 API 请求并等待所有请求完成？
- bash - 使用 jq 按索引从多个列表中打印元素
- html - bootstrap 3折叠按钮在徽标侧不在徽标下方显示内容
- pandas - 多行熊猫数据框到多列
- ios - 如何在第二个视图控制器内初始化视图控制器插座？
- html - 禁用的输入将不会在单击时需要一种方法来标记为单击时选中
- numpy - numpy 从 ndarray 的最后一个维度获取前 k 个元素