Database Manual / CRUD Operations / CRUD Concepts / Query Optimization / Analyze Performance / Database Profiler

Find Slow Queries

MongoDB includes the Database Profiler, which can identify slow queries and help you determine how to improve query performance.

About This Task

Performance, Storage, and Security

This task uses the database profiler to identify slow queries on a running mongod instance. When enabled, the database profiler can affect performance and disk usage and expose unencrypted query data.

Warning

Consider the performance, storage, and security implications before using the database profiler on a production deployment.

Atlas Query Profiler

Atlas users can take advantage of the Atlas Query Profiler to identify slow queries with the convenience of visualization through a scatterplot chart.

For more information, see Monitor Query Performance with the Query Profiler.

Slow Queries

A slow query is one that takes longer than a specified amount of time to run. For this task, the slow query threshold is set to 100 milliseconds.

In some use cases, you may require queries run faster. In others, you may need to raise the threshold to focus only on those queries that are the slowest.

Choose a slow query threshold that reflects your specific application and database needs.

Context

When enabled, the database profiler monitors queries at a database-level only. If you need the profiler to monitor slow queries on multiple databases, run the db.setProfilingLevel() method on each database.

Sharded Clusters

The database profiler is not available through mongos.

Steps

1

Enable the database profiler.

To enable the database profiler to monitor slow queries, use the db.setProfilingLevel() method:

db.setProfilingLevel(1, 100)
{ was: 0, slowms: 1, sampleRate: 1, ok: 1}

This sets the profiling level to 1, which monitors for slow queries, and defines a query as slow if it takes longer than 100 milliseconds to run.

2

Check for slow queries.

To list any slow queries found by the database profiler, query the system.profile collection for relevant data:

db.system.profile.find( { },
   {
      command: 1,
      millis: 1,
      docsExamined: 1,
      keysExamined: 1,
      nreturned: 1
   }
).sort( { ts: -1 } )
[
   {
      command: {
         find: 'people',
         filter: { age: { '$gt': 35 } },
         lsid: { id: UUID('ae3e9932-0a78-47ab-b741-01dd3bfb3563') },
         '$db': 'contacts'
      },
      keysExamined: 0,
      docsExamined: 100000,
      nreturned: 40,
      millis: 143
   }
]

The command provides a list of slow queries observed by the database profiler.

The projection filters the return documents to include information that you may find useful in determining what caused the query to run slow.

  • If keysExamined is 0, it indicates that an index was not used by the query. To solve this, create an index on the collection.
  • If an index was used and docsExamined is much larger than nreturned, it indicates an ineffectual index. You may need to update the index or create a new one on a field or fields used by the query filter.
  • If keysExamined is high and docsExamined is low, it indicates effective index use.
3

Explain the query.

If the database profiler identifies a query that requires further investigation, use the explain() method to analyze the query plan and execution statistics.

For details, see Explain Slow Queries.

4

Disable the database profiler.

To ensure that the database profiler does not further disrupt performance, disable it when it's no longer needed:

db.setProfilingLevel(0)
{ was: 1, slowms: 1, sampleRate: 1, ok: 1}

Examples

Ignore Indexes

To evaluate performance on a collection with an index, you can set the query to ignore indexes using the hint( { $natural: 1 } ) method.

db.listingsAndReviews.find( {
   $or: [
      { "address.market": "Berlin" },
      { "review_scores.review_scores_cleanliness": { $lt: 5 } }
   ],
   $where: function () {
      return this.amenities && this.amenities.length > 15;
   }
} ).sort( { description: 1 } ).hint( { $natural: 1 } );

You may find this useful in cases where you want to compare how queries perform with a collection scan to that of an index scan.

Next Steps

Learn More