Mongodb Query Profiler
Tags: mongodb, database
Published: 2020-10-10 16:16:18

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](https://docs.mongodb.com/manual/indexes/).

## Enable MongoDB profiler

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

```Javascript
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 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:

```Javascript
db.system.profile.find().limit(1).sort({ts:-1}).pretty()
```

## Explain

The [explain command](https://docs.mongodb.com/manual/reference/command/explain/#dbcmd.explain) 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:

```Javascript
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](https://docs.mongodb.com/manual/reference/explain-results/#explain.queryPlanner) information about the winning plan evaluation.
- `executionStats` - It returns [queryPlanner](https://docs.mongodb.com/manual/reference/explain-results/#explain.queryPlanner) and the [executionStats](https://docs.mongodb.com/manual/reference/explain-results/#explain.executionStats) information, but rejected plans are not included in the latter.
- `allPlansExecution` - It returns [queryPlanner](https://docs.mongodb.com/manual/reference/explain-results/#explain.queryPlanner) and the [executionStats](https://docs.mongodb.com/manual/reference/explain-results/#explain.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](https://docs.mongodb.com/manual/indexes/).

### Resources

- [MongoDB database profiler](https://docs.mongodb.com/manual/tutorial/manage-the-database-profiler/)
- [Explain results](https://docs.mongodb.com/manual/reference/explain-results/)
- [MongoDB Indexes](https://docs.mongodb.com/manual/indexes/)