Mongodb 3 Query optimization

Turn on slow query profiling

Profiling level Description

0: Close, do not collect any data. 1: Collect slow query data, default is 100 milliseconds. 2: Collect all data

1. Open profiling by modifying the configuration file

To modify the startup mongo.conf, insert the following code

# turn on slow query, 200 ms record profile = 1= 200

2, after the start of MongoDB service, through the Mongoshell to carry out temporary open, as long as the MongoDB service is closed, the next time the open will not start, you have to open again

(1), in the case of MongoDB has permission, login through the command, if there is no permission can not write--username after the content

MONGO--host 127.0.0.1:27017--username Your user name--password your password--authenticationdatabase admin

(2), jump to the database to turn on slow query monitoring

Use test

(3), set profiling

1: Via MONGO Shell:#View status: Level and TimeDrug:primary>Db.getprofilingstatus () {" was": 1,"slowms": 100 }#viewing LevelsDrug:primary>Db.getprofilinglevel ()1#Set LevelDrug:primary> Db.setprofilinglevel (2){ " was": 1,"slowms": 100,"OK": 1 }#set level and timeDrug:primary> Db.setprofilinglevel (1,200){ " was": 2,"slowms": 100,"OK": 1}

(4), modify the size of the "Slow query log"

#Close ProfilingDrug:primary> Db.setprofilinglevel (0){ " was": 0,"slowms": 200,"OK": 1 }#Delete System.profile CollectionDrug:primary>Db.system.profile.drop ()true#Create a new System.profile collectionDrug:primary> Db.createcollection ("System.profile", {capped:true, size:4000000 } ){ "OK": 1 }#re-open profilingDrug:primary> Db.setprofilinglevel (1){ " was": 0,"slowms": 200,"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," Is_fans ": 1," Latesttime ": {" $ne ": 0}, "Latestmsgid": {"$GT": 0}, "$where": "New Date (This.latestnormaltime) >new date (This.repl Ytime) "}," 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, #skip () method specifies the number of hops "nscanned": 304, #扫描数量"keyupdates": 0, #索引更新的数量,changing an index key carries a small performance overhead because the database must delete the old key and insert a new key to 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 particular database "Timelockedmicros": {#锁 "R": Numberlong (19467), "W": Numberlong (0)}, "Timeacquiringmicros": {#锁等待 "R": Numberlong (7), "W ": Numberlong (9)},"nreturned": 101, #返回的数量"responselength": 74659, #响应字节长 Degrees "millis": #消耗的时间 (msec) "ts": Isodate ("2014-02-25t02:13:54.899z"), #语句执行的时间 "cl Ient ":" 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. That is: True: Document is sorted, False: index is used. The moved 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 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 ({ts:-1}). Pretty () #返回所有的操作, except the command type Db.system.profile.find ({op: {$ne: ' Command '}}). Pretty () #返回特定集合db. System.profile.find ({ns: ' Mydb.test '}). Pretty () #返回大于5毫秒慢的操作db. Syste                        M.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 Db.system.profile.find by Time consumed ({ts: { $GT: New Isodate ("2011-07-12t03:00:00z"), $lt: New Isodate ("2011-07-12t03:4 0:00z ")}}, {user:0}). Sort ({millis:-1})

Mongodb 3 Query optimization