MONGO turn on slow query analysis

MongoDB Query Optimization Analysis

Summary:

In MySQL, the slow query log is often used as the basis for our query optimization, is there a similar function in MongoDB? The answer is yes, that is to turn on the profiling function. The tool collects writes about MONGODB, cursors, database commands, etc. on a running instance and can be opened at the database level or at the instance level. The tool writes all the collected to the System.profile collection, which is a capped collection. For more information see: http://docs.mongodb.org/manual/tutorial/manage-the-database-profiler/

Instructions for use:

1:profiling level description

0: Close, do not collect any data.
1: Collect slow query data, the default is a millisecond.
2: Collect all data

2: Turn on profiling and settings

1: Through MONGO Shell:
#查看状态: Level and Time
Drug:primary> db.getprofilingstatus ()
{"was": 1, "SLOWMS": 100}
#查看级别
Drug:primary> db.getprofilinglevel ()
1
#设置级别
Drug:primary> db.setprofilinglevel (2)
{"was": 1, "slowms": +, "OK": 1}
#设置级别和时间
Drug:primary> db.setprofilinglevel (1,200)
{"was": 2, "slowms": +, "OK": 1}

The above to operate if the test set below, only the operation of the collection is valid, if necessary for the entire instance to be valid, you need to set in all sets or open the parameters when opening:
2: Not through MONGO Shell:
Mongod--profile=1--slowms=15
or add 2 lines to the configuration file:
Profile = 1
SLOWMS = 300

3: Close Profiling

# Close
drug:primary> db.setprofilinglevel (0)
{"was": 1, "SLOWMS": $, "OK": 1}

4: Modify the size of the slow query log

#关闭Profiling
drug:primary> db.setprofilinglevel (0)
{"was": 0, "slowms": $, "OK": 1}
#删除system. Profile Collection
Drug:primary> Db.system.profile.drop ()
True
#创建一个新的system. Profile Collection
Drug:primary> db.createcollection ( "system.profile", {capped: True, size:4000000 })
{"OK": 1}
#重新开启Profiling
Drug:primary> db.setprofilinglevel (1)
{"was": 0, "slowms": $, "OK": 1}

Note: To change the size of the secondary system.profile, you must stop secondary, run it as a standalone, and then perform the above steps. When you are finished, restart the join replica set.

Slow Query (system.profile) Description:

For more information, see the following example: http://docs.mongodb.org/manual/reference/database-profiler/

1: Parameter meaning

Drug:primary> db.system.profile.find (). Pretty ()
{
    "OP": "Query",    #操作类型, insert, query, update, remove, Getmore, command   ,
    "ns": "Mc.user",  #操作的集合
    "query": {        #查询语句
        "mp_id": 5,
  &NBSP ;     "Is_fans": 1,
        "Latesttime": {
            "$ne": 0
       },
        "Latestmsgid": {
      &NBSP ;     "$GT": 0
       },
        "$where": "New Date" (THIS.LATESTN Ormaltime) >new Date (this.replytime) "
   },
   " Cursorid ": Numberlong (" 1475423943124458998 "),
   " Ntoreturn ": 0,    #返回的记录数. For example, the profile command returns a document (a result file), so the Ntoreturn value will be 1. The limit (5) command returns five files, so the Ntoreturn value is 5.

If the Ntoreturn value is 0, the command does not specify some file returns because it is a simple find () command that does not have a specified limit.
"Ntoskip": 0, the number of hops specified by the #skip () method
"nscanned": 304, #扫描数量
"Keyupdates": 0, #索引更新的数量, changing an index key with a small performance overhead, because the database must delete the old key and insert a new key into the B-tree index
"Numyield": 0,
"Lockstats": {#锁信息, R: Global read lock; w: Global write lock; R: Read lock for a particular database; W: Write lock for a specific database
"Timelockedmicros": {#锁
"R": Numberlong (19467),
"W": Numberlong (0)
},
"Timeacquiringmicros": {#锁等待
"R": Numberlong (7),
"W": Numberlong (9)
}
},
"nreturned": 101, #返回的数量
"ResponseLength": 74659, #结果字节长度
"Millis": #消耗的时间 (ms)
"TS": Isodate ("2014-02-25t02:13:54.899z"), #语句执行的时间
"Client": "127.0.0.1", #链接ip或则主机
"AllUsers": [],
"User": "" #用户
}

In addition to the above, there are:

Scanandorder:
Scanandorder is a Boolean value that is true when a query cannot use the order of the files in the index to return the result: MongoDB must sort the files it receives from a cursor after the file.
If Scanandorder is False,mongodb, the sequential index of these files can be used to return the ordered results.

Moved
The update operation moves one or more files to the new location on the disk.

Indicates whether the update 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, then may move the record to another location, this will cause the related index update. Disk operations more, plus index

Update, this will make the operation slower.

Nmoved:
The file is operating on disk.

Nupdated:
Update the number of documents

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.

If the nscanned(number of records scanned) is much larger than nreturned(the number of records returning results), consider Gazzo to optimize record positioning. ResponseLength If it is too large, the result set returned is too large to see if only the necessary fields are needed.

2: Daily use of the query

#返回最近的10条记录
Db.system.profile.find (). Limit (). Sort ({ts:-1}). Pretty ()

#返回所有的操作, except for command type
Db.system.profile.find ({op: {$ne: ' Command '}}). Pretty ()

#返回特定集合
Db.system.profile.find ({ns: ' Mydb.test '}). Pretty ()

#返回大于5毫秒慢的操作
Db.system.profile.find ({millis: {$gt: 5}}). Pretty ()

#从一个特定的时间范围内返回信息
Db.system.profile.find (
{
TS: {
$GT: New Isodate ("2012-12-09t03:00:00z"),
$LT: New Isodate ("2012-12-09t03:40:00z")
}
}
). Pretty ()

#特定时间, limit users, sort by time consumed
Db.system.profile.find (
{
TS: {
$GT: New Isodate ("2011-07-12t03:00:00z"),
$LT: New Isodate ("2011-07-12t03:40:00z")
}
},
{user:0}
). Sort ({millis:-1})

Summarize:

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 it is more efficient, so you can turn on this function when you use it.

MONGO turn on slow query analysis