MongoDB Slow Log analysis

In MySQL, the slow query log is often used as the basis for us to optimize the database, if there is a similar function in MongoDB? The answer is yes, that's MONGO database Profiler. Not only, but also some slow Query than MySQL Log for more detailed information. It is the subject of our passage.

Turn on the Profiling function

There are two ways to control the Profiling switch and level, the first is directly in the startup parameters directly set.

When you start MongoDB, add the –profile= level.

You can also invoke the Db.setprofilinglevel (level) command on the client to configure it in real time. The current profile level can be obtained through the db.getprofilinglevel () command.

> Db.setprofilinglevel (2); {"was": 0, "OK": 1} > Db.getprofilinglevel ()

The above italic level can take 0,1,2 three values, they say the meaning is as follows:

0– not open

--Record slow command (default = >100ms)

--Record all orders

The profile record will record the slow command at Level 1 o'clock, so what is this slow definition? Above we say that the default is the 100ms, of course there are settings, there are two ways to set the method and level, one is to add the –slowms boot parameter configuration. The second is to add the second parameter when calling Db.setprofilinglevel:

Db.setprofilinglevel (level, SLOWMS) Db.setprofilinglevel (1, 10);

Query Profiling Records

Unlike MySQL's slow query log, Mongo profile records are directly present in the system DB, recording location system.profile, so we can only query this collection record to get our profile record.

> Db.system.profile.find () {"TS": "Thu Jan 15:19:32 GMT-0500 (EST)", "info": "Query test. $cmd ntoreturn:1 reslen:66 nscanned:0 Query: {profile:2} nreturned:1 bytes:50 "," Millis ": 0} Db.system.profile.find ({info:/test.foo/}) {"TS": "Thu Jan 15:19:40 GMT-0500 (EST)", "info": "Insert Test.foo", "Millis": 0} {"TS": "Thu Jan 15:19:42 GMT-0500 (EST)", "info": "Insert Test.foo", "Millis": 0} {"TS": "Thu Jan 15:19:45 GMT-0500 (EST)", "info": "Query Test.foo ntoreturn:0 reslen:102 nscanned:2 Query: {} nreturned:2 bytes:86 "," Millis ": 0} {"TS": "Thu Jan 15:21:17 GMT-0500 (EST)", "info": "Query Test.foo ntoreturn:0 reslen:36 nscanned:2 Query: {$not: {x:2}} nreturned:0 bytes:20 "," Millis ": 0} {"TS": "Thu Jan 15:21:27 GMT-0500 (EST)", "info": "Query Test.foo ntoreturn:0 exception bytes:53", "Millis": 88}

List the profile records that have an execution time longer than a certain limit (5ms):

> Db.system.profile.find ({millis: {$gt: 5}}) {"TS": "Thu Jan 15:21:27 GMT-0500 (EST)", "info": "Query Test.foo ntoreturn:0 exception bytes:53", "Millis": 88}

View the latest profile records:

Db.system.profile.find (). Sort ({$natural:-1})

Mongo Shell also provides a relatively concise command show profile, which lists the last 5 execution times over 1ms of profile records.

Profile Information Content Detailed:

ts-when the command is executed.

Millis time-the command execution takes time, in milliseconds.

info-This command for more information.

Query-indicates that this is a query operation.

ntoreturn-the number of records returned by this query client. For example, the FindOne () command executes with a ntoreturn of 1. Ntoreturn is n when there is a limit (n) condition.

query-specific query conditions (such as x>3).

nscanned-the number of records scanned for this query.

reslen-returns the size of the result set.

nreturned-The result set actually returned by this query.

Update-indicates that this is an update operation.

Fastmod-indicates a fast modify operation. See Updates. These operations is normally quite fast.

Fastmodinsert–indicates A fast modify operation that performed an upsert.

Upsert-indicates that the Upsert parameter for update is true. The function of this parameter is to insert a record with the update condition if the record for the update does not exist.

Moved-Indicates whether the update has moved the data on the hard disk, if the new record is shorter than the original record, usually does not move the current record, if the new record is longer than the original record, you may move the record to another location, This will result in an update of the relevant index. More disk operations, plus index updates, can make such operations slower.

Insert-This is an insert insertion operation.

Getmore-This is a getmore operation, getmore usually occurs when the result set is larger than the query, the first query returns partial results, and subsequent results are obtained through Getmore.

MongoDB Query optimization

If nscanned (the number of records scanned) is much larger than nreturned (the number of records that return results), then we need to consider Gazzo to optimize record positioning.

Reslen if it is too large, then the result set that we return is too large, please check if the second parameter of the Find function writes only the property name you need. (Similar to not always select * in MySQL)

The recommendation for creating an index is to try not to add an index if it is rarely read, because the more indexes, the slower the write operation. If you read a large amount, then it is better to create an index. (like an RDBMS, seemingly crap-_-!!)

MongoDB Update optimization

If the amount of write queries or update volume is too large, it would be advantageous to add more indexes. and ~ ~ ~ ~ ~ ~ (omitting n words, similar to RDBMS)

Use fast Modify operations when possible (and usually with these, an index). See Updates.

Efficiency of the Profiler

Profiling function is sure to affect the efficiency, but not too serious, because he is using system.profile to record, and System.profile is a capped collection this collection There are some limitations and features in operation, but more efficient.

Turn http://blog.zol.com.cn/3044/article_3043984.html

MongoDB Slow Log analysis