Shulou(Shulou.com)06/01 Report--

Description: the optimization case is intended to express the need to understand the meaning of each parameter, combined with business analysis and logical implementation, as well as how index creation and column order are selected (not discussed here)

Describe the environment:

MongoDB version 3.0.9, replica set 3 nodes, 64GB CPU 16 core memory, disk 2TB SSD, using WT storage engine.

The table has more than 260 million data.

The general analysis is as follows:

Check the classification of slow queries in the log through mloginfo statistics (scp the production system log to the test server)

# mloginfo-queries mongod.log-20160427

Namespace operation pattern count min (ms) max (ms) mean (ms) 95%-ile (ms) sum (ms)

Database. Collection query {"gender": 1, "icial": 1, "stVal": 1, "version": 1} 997090 366 3961 802 namea 51923475

two。 The crawler logs information at a slow point in time:

……

2016-04-26T14:28:48.536+0800 I COMMAND [conn241925] query database. Collection query: {orderby: {goals: 1, diff: 1}, $query: {version: true, icial: true, stVal: {$gte: 20}, gender: "f"}} planSummary: IXSCAN {gender: 1.0, goals: 1.0, difficulty: 1.0, stateValue: 1.0, version:-1.0} ntoreturn:1000 ntoskip:0 nscanned:145640 nscannedObjects:145628 keyUpdates:0 writeConflicts:0 numYields:1137 nreturned:10 reslen:510 locks: {Global: {acquireCount: {r: 2276} AcquireWaitCount: {r: 28}, timeAcquiringMicros: {r: 22753}}, Database: {acquireCount: {r: 1138}}, Collection: {acquireCount: {r: 1138} 1675ms

There are a lot of such SQL statements, just one analysis.

Analyze the meaning of each parameter:

(1) the index used by the query sql: IXSCAN {gender: 1.0, goals: 1.0, diff: 1.0, stVal: 1.0, version:-1.0}

(2) the quantity expected by ntoreturn:1000 and the number expected by query statement, such as limit (40)

(3) the actual number returned by nreturned:10

(4) number of records skipped by ntoskip:0 skip () method

(5) nscanned:145640

Number of scans. When the number of scans is greater than the number returned (ntoreturn), consider using the index.

The difference between nscanned and nscannedObjects:

1. Nscanned: scans the document according to the index, and the scanned document may return the actual number returned (nreturned:10)

2. NscannedObjects: scan the complete document and scan the actual returned data (nscannedObjects:145628)

Http://stackoverflow.com/questions/13910097/explain-in-mongodb-differences-between-nscanned-and-nscannedobjects

Description

Nscanned considered the number of items (documents or index entries). The project may be an object or an index key. If an overlay index participates, nscanned may be higher than nscannedObjects

[nscanned Number of items (documents or index entries) examined. Items might be objects or index keys. If a "covered index" is involved, nscanned may be higher than nscannedObjects.]

NscannedObjects: the number of documents scanned.

(6) acquireCount: the number of operations to acquire a lock in a specific mode

(7) millis: execution time of 1675ms operation

Description:

There is no such value. To be clear, this value is also very important.

ScanAndOrder: Boolean value. When true, the index is not used in the sort. This field is displayed only when true is used.

(8) numYields:1137

Is the number of times the query waits for insertion

Queries need to make way for write operations.

NumYields is a counter of the number of reported operations that have been generated to allow other operations to be completed.

Https://docs.mongodb.org/manual/reference/method/db.currentOp/

Typically, when an operation occurs, the MongoDB they need to access has not yet been fully read into memory.

This allows data in memory to be quickly completed, while the data in MongoDB succumb to read operations and other operations.

[

NumYields is a counter that reports the number of times the operation has yielded to allow other operations to complete.

Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory.

This allows other operations that have data in memory to complete quickly while MongoDB reads in data for the yielding operation.

]

There may be other operations, such as problems with index construction. Even if you walk the index, you need to scan the entire index.

And the index does not overwrite the query, and the data needs to be loaded back. In addition, see if the sorting does not use the index.

Resulting in a lot of need to put separate memory sorting, energy consumption of memory.

In addition, if there is an in query, the data is scattered, loading the data may require multiple random IO and so on.

(9) observe the following parameters of the execution plan and slow log (not described)

NscannedObjects

Nscanned

ScanAndOrder

Millis

3. Analyze the sql execution plan when secondary (when the business is not busy)

Note: if the table has a large amount of data, such as hundreds of millions of data, adding allPlansExecution parameters will be very slow, careful online database execution (I am in the test database execution).

Db. Collection .find ({version: true, icial: true, stVal: {$gte: 20}, gender: "f"}) .sort ({goals: 1, diff: 1}) .explain ("allPlansExecution")

... "gender": 1, "icial": 1, "stVal": 1, "version": 1

[

{

"stage": "FETCH"

"filter": {

"icial": {

"$eq": true

}

}

"inputStage": {

"stage": "IXSCAN"

"keyPattern": {

"gender": 1

"goals": 1

"diff": 1

"stVal": 1

"version":-1

}

"indexName": "gender_1_goals_1_diff_1_stVal_1_version_-1"

"isMultiKey": false

"direction": "forward"

……

}

]

……

The index was not added correctly: execution plan

"executionStats": {

"executionSuccess": true

"nReturned": 10, the actual number of rows returned

"executionTimeMillis": 2000, millisecond of execution

"totalKeysExamined": 3030000, number of index rows scanned

"totalDocsExamined": 2910000, number of lines of document scanned

And there are filter filtering operations (that is, table-back operations). Currently, the sql chooses the gender_1_goals_1_diff_1_stVal_1_version_-1 index.

4. Suggestion

Combined with business analysis, the sql is executed 997090 times a day in the service; after analyzing the service and related sql, it is decided to violate the limit of up to five columns in the federated index recommended by mongodb:

It is recommended that you create the following indexes:

Db. Collection .createIndex ({gender:1,version:1,icial:1,goals:1,diff:1,stVal:1}, {background:true})

My side has been executed for about 90 minutes (when the business is not busy, the business here is busy at night.).

Execute the execution plan again

……

{

"stage": "FETCH"

"inputStage": {

"stage": "IXSCAN"

"keyPattern": {

"gender": 1

"version": 1

"icial": 1

"goals": 1

"diff": 1

"stVal": 1

}

"indexName": "gender_1_version_1_icial_1_goals_1_diff_1_stVal_1"

"isMultiKey": false

"direction": "forward"

……

}

}

……

"executionStats": {

"executionSuccess": true

"nReturned": 10

"executionTimeMillis": 0

"totalKeysExamined": 10

"totalDocsExamined": 10

The amount of data accessed has been significantly reduced by about 30W times.

The hint hint is used in the business implementation.

It is suggested to create an index: first, do the equivalent query, sort, and do the range query.

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.