Database Profiler Output
On this page
The database profiler captures data information about read and write operations, cursor operations, and database commands. To configure the database profile and set the thresholds for capturing profile data, see the Database Profiler section.
The database profiler writes data in the system.profile
collection,
which is a capped collection. To view the profiler's output,
use normal MongoDB queries on the system.profile
collection.
Note
Because the database profiler writes data to the
system.profile
collection in a
database, the profiler will profile some write activity, even for
databases that are otherwise read-only.
currentOp
and the
database profiler report the same
basic diagnostic information for all CRUD operations, including the
following:
getMore
(OP_GET_MORE andcommand
)
These operations are also included in the logging of
slow queries (see slowOpThresholdMs
for
more information about slow query logging).
Starting in MongoDB 4.4, it is no longer possible to perform any
operation, including reads, on the system.profile
collection from within a
transaction.
Example system.profile
Document
The following presents some sample documents found in the
system.profile
collection for
operations on a standalone:
The following document in the system.profile
reflects a find operation:
{ "op" : "query", "ns" : "test.report", "command" : { "find" : "report", "filter" : { "a" : { "$lte" : 500 } }, "lsid" : { "id" : UUID("5ccd5b81-b023-41f3-8959-bf99ed696ce9") }, "$db" : "test" }, "cursorid" : 33629063128, "keysExamined" : 101, "docsExamined" : 101, "fromMultiPlanner" : true, "numYield" : 2, "nreturned" : 101, "queryHash" : "811451DD", "planCacheKey" : "759981BA", "locks" : { "Global" : { "acquireCount" : { "r" : NumberLong(3), "w" : NumberLong(3) } }, "Database" : { "acquireCount" : { "r" : NumberLong(3) }, "acquireWaitCount" : { "r" : NumberLong(1) }, "timeAcquiringMicros" : { "r" : NumberLong(69130694) } }, "Collection" : { "acquireCount" : { "r" : NumberLong(3) } } }, "storage" : { "data" : { "bytesRead" : NumberLong(14736), "timeReadingMicros" : NumberLong(17) } }, "responseLength" : 1305014, "protocol" : "op_msg", "millis" : 69132, "planSummary" : "IXSCAN { a: 1, _id: -1 }", "execStats" : { "stage" : "FETCH", "nReturned" : 101, "executionTimeMillisEstimate" : 0, "works" : 101, "advanced" : 101, "needTime" : 0, "needYield" : 0, "saveState" : 3, "restoreState" : 2, "isEOF" : 0, "docsExamined" : 101, "alreadyHasObj" : 0, "inputStage" : { ... } }, "ts" : ISODate("2019-01-14T16:57:33.450Z"), "client" : "127.0.0.1", "appName" : "MongoDB Shell", "allUsers" : [ { "user" : "someuser", "db" : "admin" } ], "user" : "someuser@admin" }
The profile entry for update
(and
delete
) operation contains the entire update
command.
The following example reflects an update
operation on a collection named report
.
{ "op" : "update", "ns" : "test.report", "command" : { "q" : { }, "u" : { "$rename" : { "a" : "b" } }, "multi" : true, "upsert" : false }, "keysExamined" : 0, "docsExamined" : 25000, "nMatched" : 25000, "nModified" : 25000, "keysInserted" : 25000, "keysDeleted" : 25000000, "numYield" : 6985, "locks" : { "Global" : { "acquireCount" : { "r" : NumberLong(6986), "w" : NumberLong(13972) } }, "Database" : { "acquireCount" : { "w" : NumberLong(6986) }, "acquireWaitCount" : { "w" : NumberLong(1) }, "timeAcquiringMicros" : { "w" : NumberLong(60899375) } }, "Collection" : { "acquireCount" : { "w" : NumberLong(6986) } }, "Mutex" : { "acquireCount" : { "r" : NumberLong(25000) } } }, "storage" : { "data" : { "bytesRead" : NumberLong(126344060), "bytesWritten" : NumberLong(281834762), "timeReadingMicros" : NumberLong(94549), "timeWritingMicros" : NumberLong(139361) } }, "millis" : 164687, "planSummary" : "COLLSCAN", "execStats" : { "stage" : "UPDATE", "nReturned" : 0, "executionTimeMillisEstimate" : 103771, "works" : 25003, "advanced" : 0, "needTime" : 25002, "needYield" : 0, "saveState" : 6985, "restoreState" : 6985, "isEOF" : 1, "nMatched" : 25000, "nWouldModify" : 25000, "wouldInsert" : false, "inputStage" : { "stage" : "COLLSCAN", "nReturned" : 25000, "executionTimeMillisEstimate" : 0, "works" : 25002, "advanced" : 25000, "needTime" : 1, "needYield" : 0, "saveState" : 31985, "restoreState" : 31985, "isEOF" : 1, "direction" : "forward", "docsExamined" : 25000 } }, "ts" : ISODate("2019-01-14T23:33:01.806Z"), "client" : "127.0.0.1", "appName" : "MongoDB Shell", "allUsers" : [ { "user" : "someuser", "db" : "admin" } ], "user" : "someuser@admin" }
Output Reference
For any single operation, the documents created by the database profiler will include a subset of the following fields. The precise selection of fields in these documents depends on the type of operation.
Starting in MongoDB 4.2 (and in 4.0.9), for slow operations, the profiler
entries and
diagnostic log messages include
storage
information.
Note
For the output specific to the version of your MongoDB, refer to the appropriate version of the MongoDB Manual.
system.profile.op
The type of operation. The possible values are:
command
count
distinct
geoNear
getMore
group
insert
mapReduce
query
remove
update
system.profile.ns
The namespace the operation targets. Namespaces in MongoDB take the form of the database, followed by a dot (
.
), followed by the name of the collection.
system.profile.command
A document containing the full command object associated with this operation.
For example, the following output contains the command object for a
find
operation on a collection nameditems
in a database namedtest
:"command" : { "find" : "items", "filter" : { "sku" : 1403978 }, ... "$db" : "test" } The following example output contains the command object for a
getMore
operation generated by a command with cursor ID19234103609
on a collection nameditems
in a database namedtest
:"command" : { "getMore" : NumberLong("19234103609"), "collection" : "items", "batchSize" : 10, ... "$db" : "test" }, If the command document exceeds 50 kilobytes, the document has the following form:
"command" : { "$truncated": <string>, "comment": <string> } The
$truncated
field contains a string summary of the document excluding the document'scomment
field if present. If the summary still exceeds 50 kilobytes then it is further truncated, denoted by an ellipsis (...) at the end of the string.The
comment
field is present if a comment was passed to the operation. Starting in MongoDB 4.4, a comment may be attached to any database command.
system.profile.originatingCommand
Changed in version 3.6.
For
"getmore"
operations which retrieve the next batch of results from a cursor, theoriginatingCommand
field contains the full command object (e.g.find
oraggregate
) which originally created that cursor.
system.profile.keysExamined
Changed in version 3.2.0.
Renamed from
system.profile.nscanned
. The number of index keys that MongoDB scanned in order to carry out the operation.In general, if
keysExamined
is much higher thannreturned
, the database is scanning many index keys to find the result documents. Consider creating or adjusting indexes to improve query performance..Changed in version 3.4.
keysExamined
is available for the following commands and operations:
system.profile.docsExamined
Changed in version 3.2.0: Renamed from
system.profile.nscannedObjects
.The number of documents in the collection that MongoDB scanned in order to carry out the operation.
Changed in version 3.4.
docsExamined
is available for the following commands and operations:
system.profile.hasSortStage
Changed in version 3.2.0: Renamed from
system.profile.scanAndOrder
.hasSortStage
is a boolean that istrue
when a query cannot use the ordering in the index to return the requested sorted results; i.e. MongoDB must sort the documents after it receives the documents from a cursor. The field only appears when the value istrue
.Changed in version 3.4.
hasSortStage
is available for the following commands and operations:getMore
(OP_GET_MORE andcommand
)
system.profile.usedDisk
New in version 4.2.
A boolean that indicates whether any aggregation stage wrote data to temporary files due to memory restrictions.
Only appears if
usedDisk
is true.
system.profile.nMatched
The number of documents that match the query condition for the update operation.
system.profile.upsert
A boolean that indicates the update operation's
upsert
option value. Only appears ifupsert
is true.
system.profile.fromMultiPlanner
New in version 3.2.5.
A boolean that indicates whether the query planner evaluated multiple plans before choosing the winning execution plan for the query.
Only present when value is
true
.
system.profile.replanned
New in version 3.2.5.
A boolean that indicates whether the query system evicted a cached plan and re-evaluated all candidate plans.
Only present when value is
true
.
system.profile.replanReason
New in version 4.4.
A string that indicates the specific reason a cached plan was evicted.
Only present when value for
replanned
istrue
.
system.profile.keysDeleted
Removed in 3.4.
The number of index keys the update changed in the operation. Changing an index key carries a small performance cost because the database must remove the old key and inserts a new key into the B-tree index.
system.profile.writeConflicts
The number of conflicts encountered during the write operation; e.g. an
update
operation attempts to modify the same document as anotherupdate
operation. See also write conflict.
system.profile.numYield
The number of times the operation yielded to allow other operations to complete. Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. This allows other operations that have data in memory to complete while MongoDB reads in data for the yielding operation. For more information, see the FAQ on when operations yield.
system.profile.queryHash
A hexadecimal string that represents a hash of the query shape and is dependent only on the query shape.
queryHash
can help identify slow queries (including the query filter of write operations) with the same query shape.Note
As with any hash function, two different query shapes may result in the same hash value. However, the occurrence of hash collisions between different query shapes is unlikely.
For more information on
queryHash
andplanCacheKey
, seequeryHash
andplanCacheKey
.New in version 4.2.
system.profile.planCacheKey
A hash of the key for the plan cache entry associated with the query.
Unlike the
queryHash
, theplanCacheKey
is a function of both the query shape and the currently available indexes for that shape. That is, if indexes that can support the query shape are added/dropped, theplanCacheKey
value may change whereas thequeryHash
value would not change.For more information on
queryHash
andplanCacheKey
, seequeryHash
andplanCacheKey
.New in version 4.2.
system.profile.locks
The
system.profile.locks
provides information for various lock types and lock modes held during the operation.The possible lock types are:
Lock TypeDescriptionParallelBatchWriterMode
Represents a lock for parallel batch writer mode.
In earlier versions, PBWM information was reported as part of the
Global
lock information.New in version 4.2.
ReplicationStateTransition
Represents lock taken for replica set member state transitions.
New in version 4.2.
Global
Represents global lock.Database
Represents database lock.Collection
Represents collection lock.Mutex
Represents mutex.Metadata
Represents metadata lock.oplog
Represents lock on the oplog.The possible locking modes for the lock types are as follows:
Lock ModeDescriptionR
Represents Shared (S) lock.W
Represents Exclusive (X) lock.r
Represents Intent Shared (IS) lock.w
Represents Intent Exclusive (IX) lock.The returned lock information for the various lock types include:
system.profile.locks.acquireCount
Number of times the operation acquired the lock in the specified mode.
system.profile.locks.acquireWaitCount
Number of times the operation had to wait for the
acquireCount
lock acquisitions because the locks were held in a conflicting mode.acquireWaitCount
is less than or equal toacquireCount
.
system.profile.locks.timeAcquiringMicros
Cumulative time in microseconds that the operation had to wait to acquire the locks.
timeAcquiringMicros
divided byacquireWaitCount
gives an approximate average wait time for the particular lock mode.
system.profile.locks.deadlockCount
Number of times the operation encountered deadlocks while waiting for lock acquisitions.
For more information on lock modes, see What type of locking does MongoDB use?.
system.profile.storage
New in version 4.2: (Also available starting in 4.0.9)
The
system.profile.storage
information provides metrics on the storage engine data and wait time for the operation.Specific storage metrics are returned only if the values are greater than zero.
system.profile.storage.data.bytesRead
New in version 4.2: (Also available starting in 4.0.9)
Number of bytes read by the operation from the disk to the cache.
Data read from disk into the cache includes everything needed to execute the query. If the data is already in the cache, then the number of bytes read from disk could be
0
.The number of bytes read from disk includes more than the queried documents:
WiredTiger reads in units of pages and a page may contain one or several documents. If a document is in a page, all documents in that page are read into the cache and included in the
bytesRead
value.If the cache requires page management (such as eviction or rereads), the
bytesRead
value includes data read from disk in these operations.If the index is not in the cache or the index in the cache is stale, WiredTiger reads several internal and leaf pages from disk to reconstruct the index in cache.
system.profile.storage.data.timeReadingMicros
New in version 4.2: (Also available starting in 4.0.9)
Time in microseconds that the operation had to spend to read from the disk.
system.profile.storage.data.bytesWritten
New in version 4.2: (Also available starting in 4.0.9)
Number of bytes written by the operation from the cache to the disk.
WiredTiger typically doesn't require the query to write to disk. Data modified by the query is written to an in-memory cache that WiredTiger flushes to disk as part an eviction or checkpoint operation. In such cases,
bytesWritten
shows as 0.If the thread running the query requires forced page management (such as eviction), WiredTiger writes the page contents to disk. This flush likely includes data unrelated to changes made by the query itself, which can cause
bytesWritten
to show a higher value than expected.
system.profile.storage.data.timeWritingMicros
New in version 4.2: (Also available starting in 4.0.9)
Time in microseconds that the operation had to spend to write to the disk.
system.profile.storage.timeWaitingMicros.cache
New in version 4.2: (Also available starting in 4.0.9)
Time in microseconds that the operation had to wait for space in the cache.
system.profile.responseLength
The length in bytes of the operation's result document. A large
responseLength
can affect performance. To limit the size of the result document for a query operation, you can use any of the following:Note
When MongoDB writes query profile information to the log, the
responseLength
value is in a field namedreslen
.
system.profile.protocol
The MongoDB Wire Protocol request message format.
system.profile.millis
The time in milliseconds from the perspective of the
mongod
from the beginning of the operation to the end of the operation.
system.profile.execStats
A document that contains the execution statistics of the query operation. For other operations, the value is an empty document.
The
system.profile.execStats
presents the statistics as a tree; each node provides the statistics for the operation executed during that stage of the query operation.Note
The following fields list for
execStats
is not meant to be exhaustive as the returned fields vary per stage.
system.profile.client
The IP address or hostname of the client connection where the operation originates.
system.profile.appName
New in version 3.4.
The identifier of the client application which ran the operation. Use the
appName
connection string option to set a custom value for theappName
field.
system.profile.allUsers
An array of authenticated user information (user name and database) for the session. See also Users.