Shulou(Shulou.com)06/01 Report--

Preface

When it comes to MongoDB's slow log analysis, we have to mention the profile parser, which writes recorded slow logs to the system.profile collection, which is a fixed set. We can query this collection to understand the current slow log, and then optimize the database.

Overall environment

MongoDB 3.2.5

Actual combat

Part1: output demonstration

When querying system.profile, we can observe that all operations, including remove,update,find, etc., are recorded in the system.profile collection, which contains a lot of information, such as:

{"op": "query", "ns": "test.c", "query": {"find": "c", "filter": {"a": 1}}, "keysExamined": 2, "docsExamined": 2, "cursorExhausted": true, "keyUpdates": 0, "writeConflicts": 0, "numYield": 0 "locks": {"Global": {"acquireCount": {"r": NumberLong (2)}}, "Database": {"acquireCount": {"r": NumberLong (1)}}, "Collection": {"acquireCount": {"r": NumberLong (1)}, "nreturned": 2, "responseLength": 108, "millis": 0 "execStats": {"stage": "FETCH", "nReturned": 2, "executionTimeMillisEstimate": 0, "works": 3, "advanced": 2, "needTime": 0, "needYield": 0, "saveState": 0, "restoreState": 0, "isEOF": 1, "invalidates": 0, "docsExamined": 2, "alreadyHasObj": 0 "inputStage": {"stage": "IXSCAN", "nReturned": 2, "executionTimeMillisEstimate": 0, "works": 3, "advanced": 2, "needTime": 0, "needYield": 0, "saveState": 0, "restoreState": 0, "isEOF": 1, "invalidates": 0, "keyPattern": {"a": 1}, "indexName": "axi1" "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"a": [[1.0,1.0] "]}," keysExamined ": 2," dupsTested ": 0," dupsDropped ": 0," seenInvalidated ": 0}} Ts: ISODate ("2015-09-03T15:26:14.948Z"), "client": "127.0.0.1", "allUsers": [], "user": ""}

Part2: output interpretation

System.profile.op

This item mainly includes the following categories

Insertqueryupdateremovegetmorecommand

Represents the type of slow log, whether it is query, insert, update, delete or something else.

System.profile.ns

This item indicates which collection under which library the slow log corresponds to.

System.profile.query

This item outputs the specific statements and behaviors of the slow log in detail

System.profile.keysExamined

This item indicates how many key MongoDB searched to find out the final result

System.profile.docsExamined

This item indicates how many documents MongoDB searched to find out the final result

System.profile.keyUpdates

How many index key of the table name are changed in this operation, and changing the index key will also have a small performance consumption, because the database not only needs to delete the old Key, but also inserts the new Key into the B-Tree index.

System.profile.writeConflicts

The number of write conflicts, such as update, a document being manipulated by another update

System.profile.numYield

The number of times to yield in order for other operations to be completed, usually occurs when the data that needs to be accessed has not been fully read into memory, and MongoDB will give priority to completing the operation in memory.

System.profile.locks

There are many kinds of locks generated in the operation, as follows:

GlobalRepresents global lock.MMAPV1JournalRepresents MMAPv1 storage engine specific lock to synchronize journal writes; for non-MMAPv1 storage engines, the mode forMMAPV1Journal is empty.DatabaseRepresents database lock.CollectionRepresents collection lock.MetadataRepresents metadata lock.oplogRepresents lock on the oplog.

There are also a variety of lock modes, as follows:

Lock ModeDescriptionRRepresents Shared (S) lock.WRepresents Exclusive (X) lock.rRepresents Intent Shared (IS) lock.wRepresents Intent Exclusive (IX) lock.

System.profile.locks.acquireCoun

The number of times a lock is requested under a variety of unused categories

System.profile.nreturned

The final number of documents returned by this operation

System.profile.responseLength

The size returned by the result (in bytes). If the value is too large, you need to consider ways such as limit () to reduce the output result.

System.profile.millis

How long does the operation take from start to finish, in milliseconds?

System.profile.execStats

Contains some statistics for this operation, and only those of query type will be displayed

System.profile.execStats.stage

Contains the details of the operation, such as whether an index is used

System.profile.ts

The time when the operation was performed

System.profile.client

Which client initiated the operation and shows the ip or hostname of the client

System.profile.allUsers

Which authenticated user performed the operation

System.profile.user

Whether to authenticate the user to perform this operation, such as using other user actions after authentication, this item is empty

Summary

The system.profile collection is one of the means to locate slow SQL, and understanding the meaning of each output item helps us to locate the problem more quickly. As the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them.

All right, that's all of this article. I hope the content of this article can bring some help to your study or work. If you have any questions, you can leave a message and exchange messages. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.