Navigation

Database Profiler

Note

MongoDB has a Performance Advisor that automatically monitors for slow queries and suggests new indexes to improve query performance. This is available in MongoDB Atlas, our global cloud database service. Get started with an M10 instance MongoDB Atlas.

If you have an existing MongoDB database, you can learn how to migrate it into Atlas to get the Performance Advisor and other Atlas-only capabilities here.

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 the system.profile collection, a capped collection in the admin 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.

This document outlines a number of key administration options for the database profiler. For additional related information, consider the following resources:

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.
2 The profiler collects data for all operations.

Enable and Configure Database Profiling

You can enable database profiling from the mongo shell or through a driver using the profile command. This section will describe how to do so from the mongo shell. See your driver documentation if you want to control the profiler from within your application.

When you enable profiling, you also set the profiling level. 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, use the db.setProfilingLevel() helper in the mongo shell, passing the profiling level as a parameter. For example, to enable profiling for all database operations, consider the following operation in the mongo shell:

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.

Specify the Threshold for Slow Operations

To change the slow operation threshold, specify the desired threshold value in one of the following ways:

By default, the slow operation threshold is 100 milliseconds. Databases with a profiling level of 1 will profile operations slower than the threshold.

For example, the following code sets the profiling level for the current database to 1 and sets the slow operation threshold for the mongod instance to 20 milliseconds:

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

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 [1] and should be set to the highest useful value to avoid performance degradation.

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 under the REPL component with the text applied op: <oplog entry> took <num>ms. These slow oplog entries depend only on the slow operation threshold. They do not depend on the log levels (either at the system or component level), or the profiling level, or the slow operation sample rate. The profiler does not capture slow oplog entries.

Profile a Random Sample of Slow Operations

New in version 3.6.

To profile only a randomly sampled subset of all slow operations , specify the desired sample rate in one of the following ways: [2]

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 current database to 1 and sets the profiler to sample 42% of all slow operations:

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

Important

The sample rate value applies to all databases in a mongod instance. It is used by both the database profiler and the system log. [1]

Important

When logLevel is set to 0, MongoDB records slow operations to the diagnostic log at a rate determined by slowOpSampleRate. Starting in MongoDB 4.2, the secondaries of replica sets log all oplog entry messages that take longer than the slow operation threshold to apply regardless of the sample rate.

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.

:binary:`~bin.mongod` or :binary:`~bin.mongos`
[1](1, 2)

Starting in MongoDB 4.0, you can use db.setProfilingLevel() to configure slowms and sampleRate for mongos. The slowms and sampleRate configuration settings only affect the diagnostic log and not the profiler since profiling is not available on mongos.

See Database Profiling and Sharding.

Check Profiling Level

To view the profiling level, issue the following from the mongo shell:

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.

To return only the profiling level, use the db.getProfilingLevel() helper in the mongo shell as in the following:

db.getProfilingLevel()

Disable Profiling

To disable profiling, use the following helper in the mongo shell:

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, 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. You can use $comment to add data to the query document to make it easier to analyze data from the profiler. To view example queries, see Example Profiler Data Queries.

For an explanation of the output data, see Database Profiler Output.

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 the mongo shell displays the 5 most recent operations that took at least 1 millisecond to execute. Issue show profile from the mongo shell, as follows:

show profile

Profiler Overhead

When enabled, profiling has a minor effect on performance. 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.

Change Size of system.profile Collection on the Primary

To change the size of the system.profile collection, 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 collections that’s 4000000 bytes, use the following sequence of operations in the mongo shell:

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 are logged for the secondaries in the diagnostic log under the REPL component with the text applied op: <oplog entry> took <num>ms. These slow oplog entries depend only on the slow operation threshold. They do not depend on the log levels (either at the system or component level), or the profiling level, or the slow operation sample rate. The profiler does not capture slow oplog entries.