Our customers have consistently told us through our ticketing system and in interviews that one of the most useful resources for identifying performance problems is our Slow Query Analyzer. We originally developed this feature because of how important indexing is for good performance in MongoDB.
For each query that takes longer than 100ms, our Slow Query Analyzer provides the query shape, statistics, and most importantly, index recommendations that populate the "Slow Queries" tab in the mLab management console. A summary analysis report is emailed to the mLab account's technical contact every week.
Over the last few months we've been improving the analyzer to support recommendations for a broader number of queries as well as new recommendation types. Some of the features we've added are:
- Additional query statistics
- Comprehensive support for $and and $or operators
- Support for aggregation commands
- Operator-specific recommendations
Additional Query Statistics
New statistics are available to better help you understand how your queries are performing. These include:
- The last time the operation was seen by the Slow Query Analyzer
- The average number of documents and index keys examined by the database in memory
- Whether or not the operation involved an in-memory sort
These additional statistics will help you prioritize your indexing efforts. We recommend prioritizing our recommendations for in-memory sorts, because sorting at query time is one of the least efficient operations your database can perform.
$and and $or queries
The Slow Query Analyzer now provides recommendations for $and and $or branches. For $or queries in particular, refactoring recommendations are provided to avoid a known bug, SERVER-13732.
The Slow Query Analyzer now supports aggregation pipelines, and will recommend indexes based on $sort and $match stages. If you run aggregations on your deployment, you should take a look through the index recommendations available on your deployment to see if performance improvements can be made.
Some query operators don't use indexes, or require special considerations. The Slow Query Analyzer now provides advice about several specific operators (like $where, $size, and $near), and we'll be expanding our support for operator-specific recommendations in the future.
Your feedback and questions are welcome and appreciated--we'll be available as always at firstname.lastname@example.org.