MongoDB's Slow query

In MySQL, the slow query log is often used as a basis for our optimization of the database, which has similar functions in MongoDB? The answer is yes, that is MONGO database Profiler. Not only is there, but there are some more than MySQL slow Query Log for more detailed information. It is the subject of our article.

Turn on Profiling function

There are two ways to control Profiling switches and levels, the first of which is directly set in the startup parameters.

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

You can also call the Db.setprofilinglevel (level) command on the client to configure it in real time. You can get the current profile level by using the Db.getprofilinglevel () command.

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

The above italic level can take 0,1,2 three values, and they represent the following meanings:

0– not open.

Record Slow command (default is >100ms)

2– Record All commands

The profile record slows down the command at Level 1 o'clock, so what's the slow definition? Above we said that the tacit thought that the 100ms, of course, there is a default setting, there are two kinds of setting method and level, one is by adding –slowms boot parameter configuration. The second is to invoke Db.setprofilinglevel with the second argument:

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

Query Profiling Records

Unlike MySQL's slow query log, the Mongo profile record is directly present in the system DB, and the record location is System.profile, so we'll just have to check the collection record to get our profile records.

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

List profile records with execution times longer than a certain limit (5ms):

> Db.system.profile.find ({millis: {$gt: 5}}) {"TS": "Thu 2009 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})

The Mongo Shell also provides a concise command show profile, which lists profile records with more than 1ms of recent 5 execution times.

Profile Information Content Detailed:

ts-when the command is executed.

Millis time-This command is time-consuming, in milliseconds.

Info-the details of this command.

Query-indicates that this is a query operation.

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

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

nscanned-the number of records scanned by this query.

reslen-returns the size of the result set.

nreturned-The result set that this query actually returns.

Update-indicates that this is an update operation.

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

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

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

Moved-Indicates whether this update moves the data on the hard drive, and if the new record is shorter than the original record, the current record is not usually moved, and if the new record is longer than the original record, the record may be moved to another location. This can result in an update of the associated index. More disk operations, coupled with index updates, make such operations slower.

Insert-This is an insert insert operation.

Getmore-This is a getmore operation, getmore usually occurs when a query with a larger result set, the first query returns some of the results, and subsequent results are obtained by Getmore.

MongoDB Query optimization

If the nscanned (the number of records scanned) is much larger than the nreturned (the number of records that returns the result), then we need to consider the optimization of the record by Gazzo.

If the Reslen is too large, then the result set we are returning is too large, then see if the second parameter of the Find function only writes the name of the property you need. (similar to MySQL don't always select *)

The suggestion to create an index is that if you read less, try not to add an index, because the more indexes, the slower the write operation. If you read a lot, it's a good deal to create an index. (Like an RDBMS, it seems to be nonsense-_-!!)

MongoDB Update optimization

If the amount of query or update is too large, it would be helpful to add more indexes. and ~ ~ ~ ~ (omitted n word, and RDBMS almost the same truth)

Use the fast modify operations when possible (and usually with this, an index). Updates.

The efficiency of Profiler

The

Profiling feature is certainly effective, but not too serious, because he uses system.profile to record, and System.profile is a capped collection this collection There are some limitations and characteristics in operation, but the efficiency is higher.