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 the possible issue, we discovered the bottleneck: the database. The solution was to restructure the data to make it consumable from a web page.
Although I had sometimes used the SQL Server profiler, I had no experience with the MongoDB profiler, so here are the steps involved in analysing 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 it can affect MongoDB performance.
Retrieve last query profile#
Once profiling is enabled, it is possible to retrieve the last profiled query (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 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 also available on collection and cursor.
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 were we able to improve its performance.
Not our case, but it’s worth remembering that performance problems are often associated with missing or wrongly implemented indexes. If so, please have a look at MongoDB Indexes.