One of the first things to deal with when your system is rolled into production (or when you have your acceptance tests) is checking the system performance performance and tuning it.
When we talk about databases, we usually talk about index tuning, and MongoDB is no different.
How to Detect the Slow Queries
First, enable your MongoDB slow queries log. It's easy, just run db.setProfilingLevel(1, 1) from the command line.
How to Analyze the Slow Queries
Well, you can use the built-in queries (after all, the profiling is saved in a MongoDB collection). However, Dex, a tool from MongoLab, can help you shorten the time to index...
It's a Python-based product that can be easily installed. It provides a list of recommended indexes based on MongoLab best practices.
How to Install and Run
- Install python: yum -y install python
- Install pip
- Install Dex using pip: pip install dex
- Create a log file: touch mongodb.log
- Run it: sudo dex -p -v -f mongodb.log mongodb://user:password@serverDNS:serverPort/database. Don't forget to use the various flags to get errors in case of wrong configurations.
Should We Just Copy and Paste the New Indexes?
As you may know, every added index results in a performance degradation when INSERT, DELETE, and UPDATE statements are performed. Therefore, we should carefully select the indexes to be added:
- We should check that the new indexes do not overlap existing indexes. If there is an overlap, we should remove/unify the indexes' definitions (use the .db.collection.getIndexes() method to retrieve the data.
- We should check that the queries are frequently used in order to avoid saving a few seconds once a month while doing a SELECT, and pay for that at peak times with INSERTs.
MongoDB query tuning is easy: Just Tune It!