Database Profiler

Global and Per-Database Profiling Settings

slowms and sampleRate are global settings that affect all databases in the process.

Profiling level and filter are database-level settings when set with profile or db.setProfilingLevel(). When set as command-line or configuration file options, they affect the entire process.

Specify the Threshold for Slow Operations

By default, the slow operation threshold is 100 milliseconds.

Slow operations are logged based on workingMillis, which is the amount of time that MongoDB spends working on that operation. This means that factors such as waiting for locks and flow control do not affect whether an operation exceeds the slow operation threshold.

To change the slow operation threshold, use one of the following:

• 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.

The following example sets the profiling level for the currently connected database to 1 and sets the slow operation threshold for the mongod instance to 20 milliseconds:

db.setProfilingLevel( 1, { slowms: 20 } )

A profiling level of 1 causes the profiler to record operations slower than the slowms threshold.

For mongos, use db.setProfilingLevel() to configure slowms and sampleRate. These settings affect only the diagnostic log on mongos, not the profiler. Profiling is not available on mongos. [1]

The following example sets a mongos instance's slow operation threshold for logging slow operations to 20:

db.setProfilingLevel( 0, { slowms: 20 } )

The profiler entries and the diagnostic log messages (i.e. mongod/mongos logmessages) for read/write operations include:

• planCacheShapeHash to help identify slow queries with the same plan cache query shape.

  Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. If you're using an earlier MongoDB version, you'll only see the queryHash field. Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.

• 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 slow operations, set sampleRate 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 a profiling level 1 only profile a randomly sampled percentage of slow operations based on sampleRate.

The following example sets the profiling level to 1 and sets the profiles to sample 42% of slow operations:

db.setProfilingLevel( 1, { sampleRate: 0.42 } )

The modified sample rate value also applies to the system log.

For mongos, slowms and sampleRate affect only the diagnostic log, not the profiler. [1] To set the sampling rate for mongos logging:

db.setProfilingLevel( 0, { sampleRate: 0.42 } )

Set a Filter to Determine Profiled Operations

To control which operations are profiled and logged, set a filter in one of the following ways:

• profile command or db.setProfilingLevel().

• filter in a configuration file.

For mongod, filter affects both the diagnostic log and the profiler, if enabled. For mongos, filter affects the diagnostic log only.

The following example sets profiling level 1 with a filter that logs only query operations taking longer than 2 seconds:

db.setProfilingLevel( 1, { filter: { op: "query", millis: { $gt: 2000 } } } )

Check Profiling Level

To view the profiling level, run in mongosh:

db.getProfilingStatus()

The shell returns a document similar to the following:

{ "was" : 0, "slowms" : 100, "sampleRate" : 1.0, "ok" : 1 }

The returned document contains:

• was: current profiling level.

• slowms: operation time threshold in milliseconds.

• sampleRate: percentage of slow operations being profiled.

Disable Profiling

To disable profiling, run in mongosh:

db.setProfilingLevel(0)

Enable Profiling for an Entire mongod Instance

For development and test environments, you can enable profiling for an entire mongod instance. The profiling level applies to all databases on that instance.

Pass the following options to mongod at startup:

mongod --profile 1 --slowms 15 --slowOpSampleRate 0.5

Alternatively, specify operationProfiling in the configuration file.

This sets profiling level 1, defines slow operations as those lasting longer than 15 milliseconds, and profiles 50% of slow operations. [2]

slowms and slowOpSampleRate also affect operations recorded in the diagnostic log when logLevel is 0. Both settings also 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, enable it on each mongod instance in the cluster.

You can set --slowms and slowOpSampleRate on mongos to configure the diagnostic log for slow operations.

Example Profiler Data Queries

This section shows example queries on the system.profile collection. For query output details, see Database Profiler Output.

Return the most recent 10 log entries:

db.system.profile.find().limit(10).sort( { ts : -1 } ).pretty()

Return all operations except command operations ($cmd):

db.system.profile.find( { op: { $ne : 'command' } } ).pretty()

Return operations for a specific collection (this example uses mydb.test):

db.system.profile.find( { ns : 'mydb.test' } ).pretty()

Return operations taking longer than 5 milliseconds:

db.system.profile.find( { millis : { $gt : 5 } } ).pretty()

Return operations in a specific time range:

db.system.profile.find( {
   ts : {
      $gt: new ISODate("2012-12-09T03:00:00Z"),
      $lt: new ISODate("2012-12-09T03:40:00Z")
   }
} ).pretty()

Return operations in a time range, exclude the user field, and sort by duration:

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. Run show profile from mongosh:

show profile

The system.profile Collection

The system.profile collection is a capped collection with a default size of 1 megabyte, which can typically store several thousand profile documents. If you need to change the size, 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:

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 of 4000000 bytes (4 MB) 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, stop the secondary, run it as a standalone, and perform the steps above. Then, restart it as a replica set member. For more information, see Perform Maintenance on Self-Managed Replica Set Members.