MongoDB 添加索引引发的故障

Posted on | In | Views: ℃
alt

咸鱼翻身，还是咸鱼。

原因

线上MongoDB服务器资源报警，查看MongoDB log发现有大量的查询没有走索引。于是添加索引

操作

具体的查询语句列子:

1
command feeds.content_medium_hismatch command: count { count: "content_medium_hismatch", query: { update_time: { $gte: 1384099200000, $lt: 1384185600000 }, content_type_id: "28" } } planSummary: IXSCAN { content_type_id: 1 } keysExamined:191146 docsExamined:191146 fromMultiPlanner:1 replanned:1 numYields:10767 reslen:44 locks:{ Global: { acquireCount: { r: 21536 } }, Database: { acquireCount: { r: 10768 } }, Collection: { acquireCount: { r: 10768 } } } protocol:op_query 84011ms

通过MongoDB explain()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
repset:PRIMARY> db.content_medium_hismatch.find({ count: "content_medium_hismatch", query: { update_time: { $gte: 1384099200000, $lt: 1384185600000 }, content_type_id: "28" }}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "feeds.content_medium_hismatch",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "count" : {
                                                "$eq" : "content_medium_hismatch"
                                        }
                                },
                                {
                                        "query" : {
                                                "$eq" : {
                                                        "update_time" : {
                                                                "$gte" : 1384099200000,
                                                                "$lt" : 1384185600000
                                                        },
                                                        "content_type_id" : "28"
                                                }
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "$and" : [
                                        {
                                                "count" : {
                                                        "$eq" : "content_medium_hismatch"
                                                }
                                        },
                                        {
                                                "query" : {
                                                        "$eq" : {
                                                                "update_time" : {
                                                                        "$gte" : 1384099200000,
                                                                        "$lt" : 1384185600000
                                                                },
                                                                "content_type_id" : "28"
                                                        }
                                                }
                                        }
                                ]
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "public-ops-mongodb2.wj.babytree-ops.org",
                "port" : 29001,
                "version" : "3.4.2",
                "gitVersion" : "3f76e40c105fc223b3e5aac3e20dcd026b83b38b"
        },
        "ok" : 1
}
repset:PRIMARY> 

repset:PRIMARY> db.content_medium_hismatch.find({ count: "content_medium_hismatch", query: { update_time: { $gte: 1384099200000, $lt: 1384185600000 }, content_type_id: "28" }}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "feeds.content_medium_hismatch",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "count" : {
                                                "$eq" : "content_medium_hismatch"
                                        }
                                },
                                {
                                        "query" : {
                                                "$eq" : {
                                                        "update_time" : {
                                                                "$gte" : 1384099200000,
                                                                "$lt" : 1384185600000
                                                        },
                                                        "content_type_id" : "28"
                                                }
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "$and" : [
                                        {
                                                "count" : {
                                                        "$eq" : "content_medium_hismatch"
                                                }
                                        },
                                        {
                                                "query" : {
                                                        "$eq" : {
                                                                "update_time" : {
                                                                        "$gte" : 1384099200000,
                                                                        "$lt" : 1384185600000
                                                                },
                                                                "content_type_id" : "28"
                                                        }
                                                }
                                        }
                                ]
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "public-ops-mongodb2.wj.babytree-ops.org",
                "port" : 29001,
                "version" : "3.4.2",
                "gitVersion" : "3f76e40c105fc223b3e5aac3e20dcd026b83b38b"
        },
        "ok" : 1
}

于是很鲁莽的添加了索引操作

1
db.content_medium_hismatch.ensureIndex({update_time: 1, content_type_id: 1})

发现时间很长，于是就crtl+c中断操作，在执行登录MongoDB shell 发现操作都堵塞。执行tail -f /data/repset/log/mongod.log发现

1
2
3
4
5
6
7
8
9
10
11
2019-04-04T16:54:54.984+0800 I INDEX [conn2744778] build index on: feeds.content_medium_hismatch properties: { v: 2, key: { update_time: 1.0, content_type_id: 1.0 }, name: "update_time_1_content_type_id_1", ns: "feeds.content_medium_hismatch" }

Index Build: 33461300/138238312 0%
……
Index Build: 33461300/138238312 99%

Index: (2/3) BTree Bottom Up Progress: 99135500/138238312 1%
……
Index: (2/3) BTree Bottom Up Progress: 99135500/138238312 71%

2019-04-04T18:22:05.817+0800 I INDEX [conn2744778] build index done. scanned 138238312 total records. 5230 secs

还在执行创建索引操作。经过排查发现。MongoDB 不是和MySQL一样。中断了就不执行了。而且MongoDB 在前台创建索引操作。会把整个服务阻塞。直到索引创建成功，才会放开阻塞。这样操作直接造成了业务不可用状态，时间整整87分钟。鲁莽惹的祸。

总结

当系统已有大量数据时，创建索引就是个非常耗时的活，我们可以在后台执行，只需指定”backgroud: true”即可。

1
db.content_medium_hismatch.ensureIndex({update_time: 1, content_type_id: 1}, {backgroud: true})

回顾

最后在看一下如此之大的代价创建的索引之后使用的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
repset:PRIMARY> db.content_medium_hismatch.find({ update_time: { $gte: 1384099200000, $lt: 1384185600000 }, content_type_id: "28" }).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "feeds.content_medium_hismatch",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "content_type_id" : {
                                                "$eq" : "28"
                                        }
                                },
                                {
                                        "update_time" : {
                                                "$lt" : 1384185600000
                                        }
                                },
                                {
                                        "update_time" : {
                                                "$gte" : 1384099200000
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "update_time" : 1,
                                        "content_type_id" : 1
                                },
                                "indexName" : "update_time_1_content_type_id_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "update_time" : [ ],
                                        "content_type_id" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "update_time" : [
                                                "[1384099200000.0, 1384185600000.0)"
                                        ],
                                        "content_type_id" : [
                                                "[\"28\", \"28\"]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "content_type_id" : {
                                                "$eq" : "28"
                                        }
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "update_time" : 1
                                        },
                                        "indexName" : "update_time_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "update_time" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "update_time" : [
                                                        "[1384099200000.0, 1384185600000.0)"
                                                ]
                                        }
                                }
                        },
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "$and" : [
                                                {
                                                        "update_time" : {
                                                                "$lt" : 1384185600000
                                                        }
                                                },
                                                {
                                                        "update_time" : {
                                                                "$gte" : 1384099200000
                                                        }
                                                }
                                        ]
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "content_type_id" : 1
                                        },
                                        "indexName" : "content_type_id_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "content_type_id" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "content_type_id" : [
                                                        "[\"28\", \"28\"]"
                                                ]
                                        }
                                }
                        },
                        {
                                "stage" : "FETCH",
                                "filter" : {
                                        "$and" : [
                                                {
                                                        "update_time" : {
                                                                "$lt" : 1384185600000
                                                        }
                                                },
                                                {
                                                        "update_time" : {
                                                                "$gte" : 1384099200000
                                                        }
                                                }
                                        ]
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "content_type_id" : 1,
                                                "content_id" : 1
                                        },
                                        "indexName" : "content_type_id_1_content_id_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "content_type_id" : [ ],
                                                "content_id" : [ ]
                                        },
                                        "isUnique" : true,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "content_type_id" : [
                                                        "[\"28\", \"28\"]"
                                                ],
                                                "content_id" : [
                                                        "[MinKey, MaxKey]"
                                                ]
                                        }
                                }
                        }
                ]
        },
        "serverInfo" : {
                "host" : "public-ops-mongodb2.wj.babytree-ops.org",
                "port" : 29001,
                "version" : "3.4.2",
                "gitVersion" : "3f76e40c105fc223b3e5aac3e20dcd026b83b38b"
        },
        "ok" : 1
}

发现正常走了索引。总算没有白做。可是代价太大了。这个教训告诉我，在不熟悉的数据库操作一定要慎重。针对线上操作，一定要再三慎重。引以为戒！

-------------本文结束感谢您的阅读-------------

本文标题:MongoDB 添加索引引发的故障

文章作者:Wang Jiemin

发布时间:2019年04月17日 - 17:04

最后更新:2019年04月17日 - 17:04

原始链接:https://jiemin.wang/2019/04/17/mongodb-creata-index/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

0%