MongoDB trace SQL statement and slow query collection

There is a need: tracking MongoDB SQL statements and slow query collection

Step one: Use the MongoDB self-function to see how many DML statements have been run over a period of time.

Run under the bin directory./mongostat-port Port number you can see the following data

Its output has the following columns:

• INSERTS/S number of insertions per second
• QUERY/S number of queries per second
• UPDATE/S Update times per second
• DELETE/S number of deletions per second
• GETMORE/S number of Getmore executed per second
• command/s command number per second, more than the above insert, find, UPDATE, delete more comprehensive, also counted other commands
• FLUSHS/S the number of times per second that Fsync data is written to the hard disk.
• MAPPED/S The amount of data that is mmap, in megabytes,
• Vsize Virtual memory usage, Unit MB
• Res Physical memory usage, Unit MB
• FAULTS/S the number of access failures per second (Linux only), the data is swapped out for physical memory and placed in swap. Do not exceed 100, otherwise the machine memory is too small, causing frequent swap writes. To upgrade the memory or expand it at this time
• Locked% Locked time percentage, try to control it below 50%
• idx Miss% index Miss percentage. If it's too high, consider whether the index is less .
• Q t|r|w When MongoDB receives too many commands and the database is locked and cannot be completed, it joins the command to the queue. This column shows the length of the total, read, and write 3 queues, all of which are 0 words MONGO no pressure. When high concurrency occurs, the general queue value is raised.
• Conn Current number of connections
• Time timestamp

The above is just a general view, and there is no trace to the SQL statement. Turn on slow query below

Running on the MongoDB client

Db.getprofilinglevel ()

To take 0,1,2 three values, they represent the following meanings:

0– not open

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

--Record all orders

Change this value by: If you change to 2

Db.setprofilinglevel (2)

The profile record will record slow commands at level 1 o'clock, so what is this slow definition? Above we say that its tacit think 100ms, of course there is a default setting, its setting method and the same level of two, one is by adding –slowms boot parameter configuration. The second is to add the second parameter when calling Db.setprofilinglevel:

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

The Mongo profile record is directly present in the system DB, and the record position is system.profile, so we can get our profile record just by querying this collection 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}

View the latest profile records:

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

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.

If you are viewing only query, the filter method is as follows:

Db.system.profile.find ({"OP": "Query"})

MongoDB trace SQL statement and slow query collection