DEV Community

Kelp
Kelp

Posted on

Log MongoDB query plans when the query is slow.

How to tune the performance issue about MongoDB?

We can use .explain() then MongoDB will return explain-results.
The explain-results are look like this.

"executionStats" : {
   "executionSuccess" : <boolean>,
   "nReturned" : <int>,
   "executionTimeMillis" : <int>,
   "totalKeysExamined" : <int>,
   "totalDocsExamined" : <int>,
   "executionStages" : {
      "stage" : <STAGE1>
      "nReturned" : <int>,
      "executionTimeMillisEstimate" : <int>,
      "works" : <int>,
      "advanced" : <int>,
      "needTime" : <int>,
      "needYield" : <int>,
      "saveState" : <int>,
      "restoreState" : <int>,
      "isEOF" : <boolean>,
      ...
      "inputStage" : {
         "stage" : <STAGE2>,
         "nReturned" : <int>,
         "executionTimeMillisEstimate" : <int>,
         ...
         "inputStage" : {
            ...
         }
      }
   },
   "allPlansExecution" : [
      {
         "nReturned" : <int>,
         "executionTimeMillisEstimate" : <int>,
         "totalKeysExamined" : <int>,
         "totalDocsExamined" :<int>,
         "executionStages" : {
            "stage" : <STAGEA>,
            "nReturned" : <int>,
            "executionTimeMillisEstimate" : <int>,
            ...
            "inputStage" : {
               "stage" : <STAGEB>,
               ...
               "inputStage" : {
                 ...
               }
            }
         }
      },
      ...
   ]
}
  • executionStats.totalDocsExamined too large means your query need scan a lot of documents.
  • When the stage is COLLSCAN means MongoDB scanning documents from the collection not the index. Scanning from the index is more quickly than from the collection.

When we log all explain-results on the console. There are too much information, so that we can't find we want.

A Mongoose plugin to show query plans when the query is slow.

You can set your own when to show the explain result.

GitHub logo kelp404 / mongoose-profiler

A performance tuning tool for Mongoose. Show explain results when the query is slow.

mongoose-profiler

npm version Language grade: JavaScript Coverage Status Actions Status

This is a mongoose plugin for tuning performance.
It will show the explain results on the console when the query is slow. Such as mongodb scans all documents in the collection without index.
Don't use it on production.

Installation

$ npm install mongoose-profiler --save-dev

Quick start

const mongooseProfiler = require('mongoose-profiler');
schema.plugin(mongooseProfiler());

When you execute this query without the index then you will get some messages on the console.

ProductModel
  .where({state: 'active'})
  .where({owner: {$in: ['5c9d9428e7462d3d989cb69b', '5c9d95acea5c9b4036d97c88']}})
  .limit(100);
Mongoose:      64ms Products.find({ state: 'active', owner: { '$in': [ ObjectId("5c9d9428e7462d3d989cb69b"), ObjectId("5c9d95acea5c9b4036d97c88") ] } }, { skip: 0, limit: 100 })
[ { queryPlanner
     { plannerVersion: 1
       namespace: 'database.Products'
       indexFilterSet: false,
       parsedQuery:
        { '$and':
           [ { state:
const mongooseProfiler = require('mongoose-profiler');
schema.plugin(mongooseProfiler({
  isAlwaysShowQuery: true,
  duration: 1000,          // Show query plans when it took more than this time (ms).
  totalDocsExamined: 1000, // Show query plans when "totalDocsExamined" more than this value.
  level: 'COLLSCAN'        // Show query plans when the stage is "COLLSCAN".
}));

Top comments (1)

Collapse
 
boly38 profile image
Brice

Amazing,

Very usefull tool, I just added required index in fiew minutes :)

I so added a small github issue for this repo to improve very first use experience.

Thanks @Kelp :)