Database Profiler
On this page
The database profiler collects detailed information about Database Commands executed against a running mongod
instance. This includes CRUD operations as well as configuration and administration commands. The profiler writes all the data it collects to a system.profile
collection, a capped collection in each profiled database. See Database Profiler Output for an overview of the system.profile
documents created by the profiler.
The profiler is off
by default. You can enable the profiler on a per-database or per-instance basis at one of several profiling levels.
When enabled, profiling has an effect on database performance and disk use. See Database Profiler Overhead for more information.
This document outlines a number of key administration options for the database profiler. For additional related information, see:
Warning
Do not attempt to create a time series collection or view with the name system.profile
. MongoDB 6.3 and later versions return an IllegalOperation
error if you attempt to do so. Earlier MongoDB versions crash.
Profiling Levels
The following profiling levels are available:
Level | Description |
---|---|
0 | The profiler is off and does not collect any data. This is the default profiler level. |
1 | The profiler collects data for operations that take longer than the value of slowms or that match a filter.When a filter is set:
|
2 | The profiler collects data for all operations. |
Enable and Configure Database Profiling
You can enable database profiling for mongod
instances.
This section uses mongosh
helper db.setProfilingLevel()
helper to enable profiling. For instructions using the driver, see your driver documentation.
When you enable profiling for a mongod
instance, you set the profiling level to a value greater than 0. The profiler records data in the system.profile
collection. MongoDB creates the system.profile
collection in a database after you enable profiling for that database.
To enable profiling and set the profiling level, pass the profiling level to the db.setProfilingLevel()
helper. For example, to enable profiling for all database operations, consider the following operation in mongosh
:
db.setProfilingLevel(2)
The shell returns a document showing the previous level of profiling. The "ok" : 1
key-value pair indicates the operation succeeded:
{ "was" : 0, "slowms" : 100, "sampleRate" : 1.0, "ok" : 1 }
To verify the new setting, see the Check Profiling Level section.
Starting in MongoDB 5.0 (also available starting in 4.4.2, and 4.2.12), changes made to the database profiler level
, slowms
, sampleRate
, or filter
using the profile
command or db.setProfilingLevel()
wrapper method are recorded in the log file
.
Global and Per-Database Profiling Settings
The slowms and sampleRate profiling settings are global. When set, these settings affect all databases in your process.
When set via the profile
command or db.setProfilingLevel()
shell helper method, profiling level and filter settings are set at the database
level. When set as either command line or configuration file options, profiling level and filter
settings affect the entire process.
Specify the Threshold for Slow Operations
By default, the slow operation threshold is 100 milliseconds. To change the slow operation threshold, specify the desired threshold value in one of the following ways:
-
Set the value of
slowms
using theprofile
command ordb.setProfilingLevel()
shell helper method. -
Set the value of
--slowms
from the command line at startup. -
Set the value of
slowOpThresholdMs
in a configuration file.
For example, the following code sets the profiling level for the current mongod
instance to 1
and sets the slow operation threshold for the mongod
instance to 20 milliseconds:
db.setProfilingLevel(1, { slowms: 20 })
Profiling level of 1
will profile operations slower than the threshold.
Important
The slow operation threshold applies to all databases in a mongod
instance. It is used by both the database profiler and the diagnostic log and should be set to the highest useful value to avoid performance degradation.
Starting in MongoDB 4.0, you can use db.setProfilingLevel()
to configure slowms
and sampleRate
for mongos
. For the mongos
, the slowms
and sampleRate
configuration settings only affect the diagnostic log and not the profiler since profiling is not available on mongos
. [1]
For example, the following sets a mongos
instance's slow operation threshold for logging slow operations:
db.setProfilingLevel(0, { slowms: 20 })
Starting in MongoDB 4.2, the profiler entries and the diagnostic log messages (i.e. mongod/mongos log messages) for read/write operations include:
-
queryHash
to help identify slow queries with the same query shape. -
planCacheKey
to provide more insight into the query plan cache for slow queries.
Starting in version 4.2, secondary members of a replica set now log oplog entries that take longer than the slow operation threshold to apply. These slow oplog messages:
-
Are logged for the secondaries in the
diagnostic log
. -
Are logged under the
REPL
component with the textapplied op: <oplog entry> took <num>ms
. -
Do not depend on the log levels (either at the system or component level)
-
Do not depend on the profiling level.
-
May be affected by
slowOpSampleRate
, depending on your MongoDB version:-
In MongoDB 4.2, these slow oplog entries are not affected by the
slowOpSampleRate
. MongoDB logs all slow oplog entries regardless of the sample rate. -
In MongoDB 4.4 and later, these slow oplog entries are affected by the
slowOpSampleRate
.
-
The profiler does not capture slow oplog entries.
Profile a Random Sample of Slow Operations
To profile only a randomly sampled subset of all slow operations , specify the desired sample rate in one of the following ways: [2]
-
Set the value of
sampleRate
using theprofile
command ordb.setProfilingLevel()
shell helper method. -
Set the value of
--slowOpSampleRate
formongod
or--slowOpSampleRate
formongos
from the command line at startup. -
Set the value of
slowOpSampleRate
in a configuration file.
By default, sampleRate
is set to 1.0
, meaning all slow
operations are profiled. When sampleRate
is set between 0 and 1, databases with profiling level 1
will only profile a randomly sampled percentage of slow operations according to sampleRate
.
For example, the following method sets the profiling level for the mongod
to 1
and sets the profiler to sample 42% of all slow operations:
db.setProfilingLevel(1, { sampleRate: 0.42 })
The modified sample rate value also applies to the system log.
Starting in MongoDB 4.0, you can use db.setProfilingLevel()
to configure slowms
and sampleRate
for mongos
. For the mongos
, the slowms
and sampleRate
configuration settings only affect the diagnostic log and not the profiler since profiling is not available on mongos
. [1]
For example, the following sets a mongos
instance's sampling rate for logging slow operations:
db.setProfilingLevel(0, { sampleRate: 0.42 })
Important
When logLevel
is set to 0
, MongoDB records slow
operations to the diagnostic log at a rate determined by slowOpSampleRate
.
At higher logLevel
settings, all operations appear in the diagnostic log regardless of their latency with the following exception: the logging of slow oplog entry messages by the secondaries. The secondaries log only the slow oplog entries; increasing the logLevel
does not log all oplog entries.
[1] | (1, 2) See Database Profiling and Sharding. |
Set a Filter to Determine Profiled Operations
New in version 4.4.2.
You can set a filter to control which operations are profiled and logged. You can set the profiling filter in one of the following ways:
-
Set the value of
filter
using theprofile
command ordb.setProfilingLevel()
shell helper method. -
Set the value of
filter
in a configuration file.
For mongod
instances, the filter
affects both the diagnostic log and, if enabled, the profiler.
For mongos
instances, the filter
affects the diagnostic log only and not the profiler since profiling is not available on mongos
.
Note
When a profiling filter
is set, the slowms and sampleRate options do not affect the diagnostic log or the profiler.
For example, the following db.setProfilingLevel()
method sets for a mongod
instance:
-
the profiling level to
2
, -
the filter of
{ op: "query", millis: { $gt: 2000 } }
, which causes the profiler to only logquery
operations that took longer than 2 seconds.
db.setProfilingLevel( 2, { filter: { op: "query", millis: { $gt: 2000 } } } )
Check Profiling Level
To view the profiling level, issue the following from mongosh
:
db.getProfilingStatus()
The shell returns a document similar to the following:
{ "was" : 0, "slowms" : 100, "sampleRate" : 1.0, "ok" : 1 }
The was
field indicates the current profiling level.
The slowms
field indicates operation time threshold, in milliseconds, beyond which operations are considered slow.
The sampleRate
field indicates the percentage of slow operations that should be profiled.
Disable Profiling
To disable profiling, use the following helper in mongosh
:
db.setProfilingLevel(0)
Enable Profiling for an Entire mongod
Instance
For development purposes in testing environments, you can enable database profiling for an entire mongod
instance. The profiling level applies to all databases provided by the mongod
instance.
To enable profiling for a mongod
instance, pass the following options to mongod
at startup.
mongod --profile 1 --slowms 15 --slowOpSampleRate 0.5
Alternatively, you can specify operationProfiling in the configuration file.
This sets the profiling level to 1
, defines slow operations as those that last longer than 15
milliseconds, and specifies that only 50%
of slow operations should be profiled. [2]
The slowms
and slowOpSampleRate
also affect which operations are recorded to the diagnostic log when logLevel
is set to 0
. The slowms
and slowOpSampleRate
are also available to configure diagnostic logging for mongos
. [2]
Tip
See also:
Database Profiling and Sharding
You cannot enable profiling on a mongos
instance. To enable profiling in a sharded cluster, you must enable profiling for each mongod
instance in the cluster.
However, starting in MongoDB 4.0, you can set the --slowms
and slowOpSampleRate
on mongos
to configure the diagnostic log for slow operations.
View Profiler Data
The database profiler logs information about database operations in the system.profile
collection.
To view profiling information, query the system.profile
collection. To view example queries, see Example Profiler Data Queries. For an explanation of the output data, see Database Profiler Output.
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.
Tip
You can use $comment
to add data to the query predicate to make it easier to analyze data from the profiler.
Example Profiler Data Queries
This section displays example queries to the system.profile
collection. For an explanation of the query output, see Database Profiler Output.
To return the most recent 10 log entries in the system.profile
collection, run a query similar to the following:
db.system.profile.find().limit(10).sort( { ts : -1 } ).pretty()
To return all operations except command operations ($cmd), run a query similar to the following:
db.system.profile.find( { op: { $ne : 'command' } } ).pretty()
To return operations for a particular collection, run a query similar to the following. This example returns operations in the mydb
database's test
collection:
db.system.profile.find( { ns : 'mydb.test' } ).pretty()
To return operations slower than 5
milliseconds, run a query similar to the following:
db.system.profile.find( { millis : { $gt : 5 } } ).pretty()
To return information from a certain time range, run a query similar to the following:
db.system.profile.find({ ts : { $gt: new ISODate("2012-12-09T03:00:00Z"), $lt: new ISODate("2012-12-09T03:40:00Z") } }).pretty()
The following example looks at the time range, suppresses the user
field from the output to make it easier to read, and sorts the results by how long each operation took to run:
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 } )
Show the Five Most Recent Events
On a database that has profiling enabled, the show profile
helper in mongosh
displays the 5 most recent operations that took at least 1 millisecond to execute. Issue show profile
from mongosh
, as follows:
show profile
Profiler Overhead
When enabled, profiling has an effect on database performance, especially when configured with a profiling level of 2, or when using a low threshold value with a profiling level of 1. Profiling also consumes disk space, as it logs to both the system.profile
collection and also the MongoDB logfile
. Carefully consider any performance and security implications before configuring and enabling the profiler on a production deployment.
The system.profile
Collection
The system.profile
collection is a capped collection with a default size of 1 megabyte. A collection of this size can typically store several thousand profile documents, but some applications may use more or less profiling data per operation. If you need to change the size of the system.profile
collection, follow the steps below.
Change Size of system.profile
Collection on the Primary
To change the size of the system.profile
collection on the primary, you must:
-
Disable profiling.
-
Drop the
system.profile
collection. -
Create a new
system.profile
collection. -
Re-enable profiling.
For example, to create a new system.profile
collection that is 4000000
bytes (4 MB), use the following sequence of operations in mongosh
:
db.setProfilingLevel(0) db.system.profile.drop() db.createCollection( "system.profile", { capped: true, size:4000000 } ) db.setProfilingLevel(1)
Change Size of system.profile
Collection on a Secondary
To change the size of the system.profile
collection on a secondary, you must stop the secondary, run it as a standalone, and then perform the steps above. When done, restart the standalone as a member of the replica set. For more information, see Perform Maintenance on Replica Set Members.
[2] | (1, 2, 3) Starting in version 4.2, secondary members of a replica set now log oplog entries that take longer than the slow operation threshold to apply. These slow oplog messages:
|