Database Profiler

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 the profile command or db.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.

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 })

The profiler entries and the diagnostic log messages (i.e. mongod/mongos logmessages) 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.

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 text applied 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.

• Are affected by 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 the profile command or db.setProfilingLevel() shell helper method.

• Set the value of --slowOpSampleRate for mongod or --slowOpSampleRate for mongos 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 })

Set a Filter to Determine Profiled Operations

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 the profile command or db.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.

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 log query 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]

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, you can set the --slowms and slowOpSampleRate on mongos to configure the diagnostic log for slow operations.

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

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:

1. Disable profiling.

2. Drop the system.profile collection.

3. Create a new system.profile collection.

4. 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 Self-Managed Replica Set Members.