DEV Community

Cover image for Mongodb Query Profiler
Antonio Morrone
Antonio Morrone

Posted on • Originally published at antomor.com

Mongodb Query Profiler

Some months ago, I was creating a web page to show some aggregated data, but I soon noticed the API used to retrieve the data was very slow. After investigating on the possible issue, we discovered the bottleneck: the database. The solution was to re-structure the data to make it consumable from a web page.

Although I had used sometimes the SQLServer profiler, I had no experience about MongoDB profiler, so here below the steps involved to analyse a MongoDB query.

It doesn't apply to our case, but in many situations performance problems are related to missing or wrongly implemented MongoDB Indexes.

Enable MongoDB profiler

By default, MongoDB doesn't profile any query. It is possible to set the profiling level executing:

db.setProfilingLevel(<profile_level>)
Enter fullscreen mode Exit fullscreen mode

where profile_level can be:

  • 0 - the profiler is off, so it doesn't collect any data.
  • 1 - the profiler collects data only if the operation takes more than slowms (a configurable threshold).
  • 2 - the profiler collects data for all operations.

IMPORTANT: At the end of the analysis, please remember to disable profiling with: db.setProfilingLevel(0), since that it can affect MongoDB performance.

Retrieve last query profile

Once profiling is enabled, it is possible to retrieve last query profiled (meaning that the query must be executed after setting the profiling level) with the command:

db.system.profile.find().limit(1).sort({ts:-1}).pretty()
Enter fullscreen mode Exit fullscreen mode

Explain

The explain command comes in handy, since that it provides information on many common operations such as aggregate, count, distinct, find, findAndModify, delete and update. Nevertheless a method with the same name, is available on collection and cursor also.

So if we want to analyse the information associated with an aggregate operation we can execute:

db.<collection_name>.explain(<verbosity>).aggregate(...)
Enter fullscreen mode Exit fullscreen mode

where:

  • <collection_name> is the name of the collection on which the aggregation is performed
  • <verbosity> is the level of verbosity we want to extract.

Explain verbosity

The explain command accepts one of the following verbosity levels:

  • queryPlanner - It returns the queryPlanner information about the winning plan evaluation.
  • executionStats - It returns queryPlanner and the executionStats information, but rejected plans are not included in the latter.
  • allPlansExecution - It returns queryPlanner and the executionStats information about all the evaluated plans (including also rejected plans).

Conclusion

Only by understanding how the query was executed by MongoDB, we were able to improve its performance.

Not our case, but I would remind that performance problems are often associated to missing or wrongly implemented indexes. If so, please have a look at MongoDB Indexes.

Resources

Top comments (0)