Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the disaster caused by the MongoDB composite index. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Previously on...

At the end of November, the MongoDB main database of our goods and services had serious jitter, frequent lock and so on.

Because many businesses have logic such as inserting MongoDB and then querying immediately, the project does not enable read-write separation.

The final positioning problem is caused by the server's own disk + a large number of slow queries.

Based on the above, the operation and maintenance students focused on strengthening the monitoring and alarm of MongoDB slow queries.

Fortunately, the upgrade of the cache expiration time was completed just before the accident, and the expiration time was one month, and the C-side queries fell on the cache, so it did not cause a P0 accident and only blocked part of the B-side logic.

Accident playback

All kinds of monitoring of our company are in place. On that day, we suddenly received an alarm notification of a high load on the database server, so my colleagues and I quickly logged in to Zabbix monitoring. As shown below, the screenshot was in a normal state. I forgot to take pictures during the accident, so we can imagine that the data curve at that time should be very low, and the low should be very high.

Zabbix distributed monitoring system official website: https://www.zabbix.com/

Start the analysis

Our R & D does not have the permission to control the server, so we entrust OPS students to help us grab some of the query records, as shown below:

- -+ Op | Duration | Query-- -+ query | 5 s | {"filter": {"orgCode": 350119 "fixedStatus": {"$in": [1,2]}}, "sort": {"_ id":-1}, "find": "sku_main"} query | 5s | {"filter": {"orgCode": 350119, "fixedStatus": {"$in": [1,2]}}, "sort": {"_ id":-1} "find": "sku_main"} query | 4 s | {"filter": {"orgCode": 346814, "fixedStatus": {"$in": [1,2]}}, "sort": {"_ id":-1}, "find": "sku_main"} query | 4 s | {"filter": {"orgCode": 346814 "fixedStatus": {"$in": [1,2]}}, "sort": {"_ id":-1}, "find": "sku_main"} query | 4 s | {"filter": {"orgCode": 346814, "fixedStatus": {"$in": [1,2]}}, "sort": {"_ id":-1}, "find": "sku_main"}.

If the query is very slow, the first thing all R & D should think about is the use of the index, so check the index immediately, as follows:

# the current index db.sku_main.ensureIndex ({"orgCode": 1, "_ id":-1}, {background:true}); db.sku_main.ensureIndex ({"orgCode": 1, "upcCode": 1}, {background:true});....

I have shielded the distractors, but it is obvious that this query can hit the index, so we need to face the first question:

Is the slow query at the top of the above query record the root cause of the problem?

My judgment is that it should not be the root cause of the overall slowness of the database, because first, its query conditions are simple and violent enough to completely hit the index, and there are only a little other query conditions on top of the index. second, there are also queries with the same structure and different conditions in the query record, which takes a very short time.

When the operation and maintenance students continued to check the query log, they found another shocking query, as follows:

# scene log query: {$query: {shopCategories.0: {$exists: false}, orgCode: 337451, fixedStatus: {$in: [1,2]}, _ id: {$lt: 2038092587}} $orderby: {_ id:-1}} planSummary: IXSCAN {_ id: 1} ntoreturn:1000 ntoskip:0 keysExamined:37567133 docsExamined:37567133 cursorExhausted:1 keyUpdates:0 writeConflicts:0 numYields:293501 nreturned:659 reslen:2469894 locks: {Global: {acquireCount: {r: 587004}}, Database: {acquireCount: {r: 293502}}, Collection: {acquireCount: {r: 293502} # time-consuming 179530ms

# it takes 180 seconds and the execution plan based on the query shows that it runs the _ id_ index and scans the whole table. The total amount of scanned data is 37567133. No wonder it is slow.

Solve it quickly

After locating the problem, there is no way to correct it immediately. The first priority is to stop the loss.

It was also late at that time, so we issued a notice, banned the above query function and temporarily suspended some of the business. After a while, we switched between master and slave, and then everything was all right when we went to see Zabbix monitoring.

Analyze the root causes

Let's review the query statement and our expected index, as follows:

# original Query db.getCollection ("sku_main"). Find ({"orgCode": NumberLong (337451), "fixedStatus": {"$in": [1.0,2.0]}, "shopCategories": {"$exists": false} "_ id": {"$lt": NumberLong (2038092587)}) .sort ({"_ id":-2038092587}) .skip (1000) .limit (1000) # expected index db.sku_main.ensureIndex ({"orgCode": 1, "_ id":-1}, {background:true})

At first glance, it seems that everything is very Nice, field orgCode equivalent query, field _ id is sorted in reverse order according to the direction of creating the index, why is it so slow?

But the key point is on $lt

Knowledge point 1: index, direction and ranking

In MongoDB, sorting operations can ensure the ordering of results by getting documents from the index in the order of the index.

If MongoDB's query planner cannot get the sort order from the index, it needs to sort the results in memory.

Note: sorting operations without indexes will be terminated when memory exceeds 32MB, that is, MongoDB can only support non-index sorting within 32MB.

Knowledge point 2: single-column indexes don't care about direction.

Both MongoDB and MySQL use the tree structure as the index. If the sorting direction is opposite to the index direction, you just need to traverse from the other end, as shown below:

# Index db.records.createIndex ({asort 1}); # query db.records.find (). Sort ({asort 1}); # Index is in ascending order, but my query is in descending order, I only need to traverse from the right side to meet the demand, and vice versa MIN 0 1 2 3 4 5 6 7 MAX

Compound Index structure of MongoDB

Official introduction: MongoDB supports compound indexes, where a single index structure holds references to multiple fields within a collection's documents.

The schematic diagram of the composite index structure is as follows:

The index happens to be the same as what we discussed, userid order, score reverse order.

We need to face the second question: does the composite index need to care about direction when using it?

Suppose two query conditions:

# query one db.getCollection ("records"). Find ({"userid": "ca2"}). Sort ({"score":-1.0}); # query two db.getCollection ("records"). Find ({"userid": "ca2"}) .sort ({"score": 1.0})

There is nothing wrong with the above query, because it is only a question of traversing the data from the left or the right due to the sorting of the score field, so what about the next query?

# error demonstration db.getCollection ("records"). Find ({"userid": "ca2", "score": {"$lt": NumberLong (2038092587)}) .sort ({"score":-1.0})

The reasons for the error are as follows:

Because the score fields are sorted in reverse order, in order to use the index, you need to traverse from the left

Finding data less than a certain value from the reverse order is bound to scan a lot of useless data and then discard it. Finding a value greater than a certain value is the best solution in the current scene.

So in order to consider more scenarios, MongoDB abandoned the composite index in this case and chose other indexes, such as score's single-column index.

Targeted modification

After reading the root causes carefully, review the online query statement as follows:

# original Query db.getCollection ("sku_main"). Find ({"orgCode": NumberLong (337451), "fixedStatus": {"$in": [1.0,2.0]}, "shopCategories": {"$exists": false} "_ id": {"$lt": NumberLong (2038092587)}) .sort ({"_ id":-2038092587}) .skip (1000) .limit (1000) # expected index db.sku_main.ensureIndex ({"orgCode": 1, "_ id":-1}, {background:true})

The mistake is exactly the same, so MongoDB abandoned the use of the composite index and should be a single-column index, so it was specifically modified to change the $lt condition to $gt to observe the optimization result:

# original query [TEMP INDEX] = > lt: {"limit": 1000, "queryObject": {"_ id": {"$lt": 2039180008}, "categoryId": 23372, "orgCode": 351414, "fixedStatus": {"$in": [1jue 2]}}, "restrictedTypes": [], "skip": 0 "sortObject": {"_ id":-1}} # original timeout [TEMP LT] = > timeout (timeout 10s) # optimized query [TEMP INDEX] = > gt: {"limit": 1000, "queryObject": {"_ id": {"$gt": 2039180008}, "categoryId": 23372, "orgCode": 351414, "fixedStatus": {"$in": [1jue 2]}}, "restrictedTypes": [], "skip": 0 "sortObject": {"_ id":-1}} # time after optimization [TEMP GT] = > time: 383ms, List Size: 999

The analysis of the small 2000 words, in fact, the change is only two characters, of course, the real change needs to consider the needs of the business, but now that the problem has been located, it is not difficult to modify what.

This is what the disaster caused by the MongoDB composite index shared by the editor is like. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.