MongoDB involves a slow business--Slow query optimization analysis case--and parameter description

Description: The optimization case is to express the meaning of the individual parameters, combining the analysis of the business with the logical implementation, and how to create the index and column order is selected (no longer described here)

Environment Description :

MongoDB version 3.0.9, replica set 3 node, memory 64g,cpu core, disk 2TB SSD, using WT storage engine ...

The amount of data in this table is more than 260 million.

The general analysis is as follows:

1. View the classification of slow queries in the log by Mloginfo statistics (making the production system log SCP 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 N/A 51923475

2. The crawler in the slow time point log information :

......

2016-04-26t14:28:48.536+0800 I COMMAND [conn241925] query database. Set query: {by: {goals:1, diff:1}, $query: {versi On:true, Icial:true, Stval: {$gte: $}, 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 writeConf licts:0 numyields:1137 nreturned:10 reslen:510 locks:{Global: {acquirecount: {r:2276}, Acquirewaitcount: {r:28}, T Imeacquiringmicros: {r:22753}}, Database: {acquirecount: {r:1138}}, Collection: {acquirecount: {r:1138}} 1 675ms

This is a lot of SQL statements, only one analysis.

analyze the meaning of each parameter :

(1) Index currently used by this query sql: IXSCAN {gender:1.0, goals:1.0, diff:1.0, stval:1.0, Version:-1.0}

(2) ntoreturn:1000 expected return quantity, query statement expected number of returns, such as limit (40)

(3) Number of nreturned:10 actually returned

(4) The number of records skipped by the ntoskip:0 Skip () method

(5) nscanned:145640

Number of scans, when scan times are greater than the quantity returned (Ntoreturn), consider using the index

Nscanned and Nscannedobjects differences:

1, nscanned: Scan the document according to the index, the scan may return the actual quantity returned (NRETURNED:10)

2, Nscannedobjects: Scan the complete document, 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 (file or index entries). The item 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 could be higher than nscannedobjects. "

Nscannedobjects: Number of documents scanned.

(6) Acquirecount: Number of operations to acquire a lock in a specific mode

(7) MILLIS:1675MS Operation Execution Time

Description

Without this value, it is important to note that this value

Scanandorder: Boolean value, when True, indicates that the sort is not used to the index and only true when the field is displayed

(8) numyields:1137

is the number of times the query waits to be inserted

The query needs to give way to the write operation.

Numyields is a counter that reports the number of operations that have been generated to allow other operations to complete.

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

Typically, when operations are generated, they need access to the MongoDB that is not yet fully read into the in-memory data.

This allows in-memory data to be done quickly, while the data yielding operation in MongoDB reads such operations.

[

Numyields is a counter that reports the number of times the times the operation have yielded to and other operations to complete.

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

This allows and operations that has the data in memory to complete quickly while MongoDB reads in data for the yielding ope Ration.

]

There may be other operations, such as a problem with indexing, and even if you go through the index, you need to scan the entire index.

And the index does not overwrite the query, you need to load the data back row. Also see whether the sort is not used on the index,

Leads to a lot of memory that needs to be sorted separately, consuming energy.

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

(9) Observe the execution plan, slow log the following parameters (not described)

Nscannedobjects

nscanned

Scanandorder

Millis

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

Note: If the data volume of the table is particularly large, such as hundreds of billions of dollars, the addition of the Allplansexecution parameter will perform very slowly, carefully on-line database execution (I was in the test database execution).

Db. Set. Find ({version:true, icial:true, Stval: {$gte: $}, Gender: "F"}). Sort ({goals:1, diff:1}). Explain ("Allplans Execution ")

... "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",

......

}

]

......

Index not added correctly: execution plan

"Executionstats": {

"Executionsuccess": true,

"nreturned": 10, actual number of rows returned

"Executiontimemillis": 2000, execution of milliseconds

"totalkeysexamined": 3030000, scan index row count

"totaldocsexamined": 2910000, scan the number of document lines

And there is a filter filtering operation (that is, a return table operation). Currently, the SQL selects the Gender_1_goals_1_diff_1_stval_1_version_-1 index.

4. Recommendations

Combined with business analysis, the SQL was executed 997,090 times a day in the business, and after analyzing the business and related SQL, the limit of up to 5 columns for a federated index that violates MONGODB recommendations is determined:

It is recommended to create the following index:

Db. Collection. CreateIndex ({gender:1,version:1,icial:1,goals:1,diff:1,stval:1},{background:true});

I've been doing this for about 90 minutes (the business is not busy, this business is busy at night ...) )

Executing 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 was significantly reduced by about 30W.

The hint hint was used in the business implementation.

CREATE INDEX Suggestions: first do the equivalent query, in doing the sort, in Do scope query .

This article is from the "nine Finger God hack" blog, please be sure to keep this source http://beigai.blog.51cto.com/8308160/1768353

MongoDB involves a slow business--Slow query optimization analysis case--and parameter description