In this blog post we'd like to talk about a tool that we use on a daily basis when consulting MongoDB clients. A tool we like to give to the public under the MIT licence - the MongoDB Query Analyser by Totally Nerdy Ltd.
The history of the MongoDB Query Analyser
We work with MongoDB since the early versions by 10gen and have seen various setups by now. Despite the diversity of these, our approach to success always starts with making our client aware of how MongoDB understands and executes its database queries. That is the foundation of everything else, e.g. data modelling.
Over the last decades MongoDB's ad-hoc query language became more sophisticated with every updated version, especially MongoDBs Aggregation Framework. This meant Engineers needed to keep pace with understanding its inner work to be effective in their application of MongoDB.
Explaining a query
To understand how a MongoDB query was executed, the "explain" command is used to print all sorts of information to the screen instead of the expected documents.
Depending on the complexity of the explained query this output might seem overwhelming at first, and the untrained eye doesn't immediately spot important information.
Making a difference
We'd like to give you some key metrics to look out for as an example:
Index usage: you want all your queries use an index (COLLSCAN vs. IXSCAN)
No sorts in memory: add the field by which the documents get sorted at the end of an index
Query targeting: you want MongoDB to only look at the index keys/documents which you expect to be returned
How the MongoDB Query Analyser works
MongoDB offers the MongoDB Shell, which is a terminal tool for connecting to a database node and executing database queries and operations. This shell supports the execution of JavaScript.
The MongoDB Query Analyser is therefore written in JavaScript and available on our Github. It comes with a setup file, that contains the query to be analysed and some more constants for changing the Analyser's output.
By executing the setup file together with the Query Analyser through the MongoDB Shell you get the desired output.
How we use it
Instead of modifying the query in the setup file every time, we create one setup file for every query we'd like to analyse. This gives us a nice history of what queries we analysed in the past and we can come back to them anytime in the future to see if they still perform as expected. This can be automated even.
A disclaimer: the Github repository contains a setup file called "setup_tyny.dev.js", and is used as an example for fetching the query pipeline from a remote server, in this case our own SaaS tyny.dev. YOU DO NOT WANT TO USE THAT ON PRODUCTION SERVER!
This approach saves us time in development environments by not having us to create and manipulate setup files for every query.
Download the MongoDB Query Analyser here.
Please let us know what you think, and if and how the Query Analyser was helpful to you. Comments and PRs are welcome on Github.
Thank you very much.
Humongous regards,
