Antecedents feed

  1. The end of NovemberOur products and ServicestheDirecting the main libraryThere have been serious jitter, frequent lock and other situations.
  2. Because many services exist to insertMongoDB, and then query immediately, so the project does not enable read/write separation.
  3. The final location problem is due to: the server’s own disk + a large number ofThe slow queryLead to
  4. Based on the above situation, the operation and maintenance students focused on strengthening the matchingDirecting a slow queryFor monitoring and alarm

Fortunately, the cache expiration was upgraded just before the crash, and the expiration time was one month. C-side queries fell on the cache, so there was no P0 incident and only part of b-side logic was blocked


The accident playback

We do various monitoring is in place, the day suddenly received a high alarm database server load, so my colleagues and I went on Zabbix monitoring, as shown in the figure below, the screenshot is normal, when during accident forgot to leave figure, as you can imagine the data curve is the high low, anyway the low is very high.

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


To analyze

Our research and development did not have the authority to control the server, so we entrusted the operation and maintenance students to help us capture part 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       | 5 s      | {"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"}...Copy the code

If the query is slow, the first thing that should occur to all developers is the use of the index, so we immediately check the index, as shown below:

### index at that time

db.sku_main.ensureIndex({"_id": -1},{background:true});
db.sku_main.ensureIndex({"orgCode": 1, "_id": -1},{background:true});
db.sku_main.ensureIndex({"orgCode": 1, "upcCode": 1},{background:true}); .Copy the code

I have blocked the interference items, so it is obvious that the query can hit the index, so I need to face the first problem:

Is the first slow query in the above query records the root cause of the problem?

My judgment is: it should not be the root of the overall slow database, because the first of its query conditions is simple enough violence, completely hit the index, there is a bit of other query conditions on the index, the second in the query record there are the same structure of the query with different conditions, the time is very short.

When o&M students continued to check and query logs, they found another shocking query as follows:

### The 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
Copy the code

It takes 180 seconds and the query-based execution plan shows that it goes to the _ID_ index and performs a full table scan. The total amount of data scanned is 37567133.


Quickly solve

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

Considering that it was late at that time, we made a public announcement, prohibited the above query function and temporarily suspended part of the business. After a while, we switched the master and slave, and then went to see Zabbix monitoring and everything was fine.


Analysis of root cause

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

# # # the 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": 1.0}). Skip (1000).limit(1000);

### Expected index
db.sku_main.ensureIndex({"orgCode": 1, "_id": -1},{background:true});
Copy the code

Select * from orgCode where id is in reverse order by direction of index creation.

However, the key point is $LT

Index, direction and sort

In MongoDB, sort operations can ensure the order of results by retrieving documents from the index in the order of the index.

If MongoDB’s query planner cannot get the sort order from the index, it will need to sort the results in memory.

Note: When sorting memory, the default maximum limit is 32MB, exceeding which an error will be thrown

Single-column indexes don’t care about direction

Both MongoDB and MySQL use the tree structure as the index. If the sort direction is opposite to the index direction, just start traversing from the other end, as shown below:

# index
db.records.createIndex({a:1}); 

# query
db.records.find().sort({a:-1});

The index is in ascending order, but I want the query to be in descending order, SO I just need to traverse from the right end and vice versa
MIN 0 1 2 3 4 5 6 7 MAX
Copy the code

MongoDB’s composite index structure

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

The composite index structure is shown as follows:

Index {“score”: -1};

We need to address the second question: should composite indexes care about direction when used?

Assume two query conditions:

# query a
db.getCollection("records").find({ 
  "userid" : "ca2"
}).sort({"score": 1.0});# use index
{"userid": 1,"score"1} : -Query # 2
db.getCollection("records").find({ 
  "userid" : "ca2"
}).sort({"score" : 1.0});

# use index
{"userid": 1,"score"1} : -Copy the code

There is no problem with the above query, because it is affected by the sorting of score fields. It is just a question of traversing the data from the left or the right. How about the following query?

# Wrong example
db.getCollection("records").find({ 
  "userid" : "ca2"."score" : { 
    "$lt" : NumberLong(2038092587)
  }
}).sort({"score": 1.0});# use index
{"score"1} : -Copy the code

The error causes are as follows:

  • Because the Score field is sorted in reverse order, you need to traverse from the left in order to use the index
  • Searching for data smaller than a certain value in reverse order will inevitably scan a lot of useless data and discard it. In the current scenario, searching for data larger than a certain value is the best solution
  • Therefore, in order to consider more scenarios, MongoDB gives up the composite index and selects other indexes, such as the single-column index of Score

Targeted modification

$lt = $gt; $lt = $gt;

# original query
[TEMP INDEX] => lt: {"limit": 1000,"queryObject": {"_id": {"$lt": 2039180008}."categoryId": 23372,"orgCode": 351414,"fixedStatus": {"$in": [1, 2]}},"restrictedTypes": []."skip": 0."sortObject": {"_id"1}} : -# Original time[TEMP LT] => Timeout (timeout duration is 10 seconds)# Optimized query
[TEMP INDEX] => gt: {"limit": 1000,"queryObject": {"_id": {"$gt": 2039180008}."categoryId": 23372,"orgCode": 351414,"fixedStatus": {"$in": [1, 2]}},"restrictedTypes": []."skip": 0."sortObject": {"_id"1}} : -# Elapsed time after optimization[TEMP GT] => Time: 383ms, List Size: 999Copy the code

Modify the program

  1. Reverse sort conditions can be

    As mentioned above, the index can be traversed from left or right, so adjust the document scanning direction
# note: you need to actively declare the first (orgCode) field query direction, otherwise the default direction will be searched
sort({ "orgCode" : -1.0},{ "_id" : 1.0})
Copy the code

  2. Modifying business code

    1. Pre-check the query conditions_idMinimum value (very fast, fully indexed)
    2. willltQuery intogtThe query can be

Extended scenario: scenario without interference from other indexes

In order to simulate online accidents, we assume a composite index and a single column index, namely:

{"userid": 1, "score"1} {: -"score"1} : -Copy the code

What happens when we delete a single column index and query in the wrong direction?

# when only the compound index is left
db.getCollection("records").find({ 
  "userid" : "ca2"."score" : { 
    "$lt" : NumberLong(2038092587)
  }
}).sort({"score": 1.0});# use index
{"userid": 1,"score"1} : -Copy the code

conclusion

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, review the above content summarized as follows:

  • You can use analogies to learn about databases, but you need to pay extra attention to the differences (MySQL, MongoDB index, index direction).
  • MongoDB single-column indexes can be directionless
  • If MongoDB cannot sort by index, it will sort in memory. If the size exceeds the default limit (32M), this error will be reported
  • The direction of MongoDB composite index must be paid attention to when it is usedTo fully understand its logic,Either the exact same thing or the exact oppositeTo avoid index failure
  • For the previous item:But when the index selector does not have a better solution, the target index is used even if the query direction does not match the index direction

The last

If you find this helpful:

  1. Of course, give me a thumbs up
  2. Search and follow the public account “Yes Kerwin ah”, chat together ~
  3. Let’s look at some of the more recent ones.Leak fill a vacancy“Series bar, the series will continue to output ~
    • Reinforce your knowledge of Nginx by “fixing what’s missing”
    • Reinforce your RocketMQ knowledge base by “fixing the gaps.
    • Reinforce your Redis knowledge. (Laughs)
    • Enhance team Happiness & Surprise!