Mongodb Query Profiler
Steps involved to analyse the performance of a MongoDB query
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>)
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 thanslowms(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()
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(...)
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.
