Performance Tuning on MongoDB

If your writing huge amount of data to MongoDB it is really important to have a proper performance tuning method with that. I will discuss one of the performance tuning method, but this might not be the only one

The explain() method returns a document that describes the process used to return the query results. If the mango has a collection name inventory and inventory is having 1500000 records, and this how it explain

db.inventory.find().explain()

{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 1500000,
    "nscannedObjects" : 1500000,
    "nscanned" : 1500000,
    "nscannedObjectsAllPlans" : 1500000,
    "nscannedAllPlans" : 1500000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 22,
    "indexBounds" : {},
    "server" : "myServerIp"
}

cursor displays BasicCursor to indicate a collection scan. n displays 1500000 to indicate that the query matches and returns three document.
nscanned and nscannedObjects display 1500000 to indicate that MongoDB had to scan ten documents 

db.inventory.find({"verb":"getInventoryPrice"}).explain()

{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 302,
    "nscannedObjects" : 1500000,
    "nscanned" : 1500000,
    "nscannedObjectsAllPlans" : 1500000,
    "nscannedAllPlans" : 1500000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 65,
    "indexBounds" : {},
    "server" : "myServerIp"
}

The difference between the number of matching documents and the number documents scanned may suggest that, to improve efficiency, the query might benefit from the use of an index.

Adding Indexes
Good performance starts with Indexing
Indexes can improve the performance by 2 to 3 times or 1000 ms query down to <1ms, but that is for good indexing

Key commands on indexing

db.inventory.ensureIndex( { verb: 1 } )
db.inventory.dropIndex( { verb: 1 } )

ascending 1
descending -1


db.inventory.getIndexKeys()
{
    "0" : {
        "_id" : 1
    },
    "1" : {
        "verb" : 1
    }
}
db.inventory.getIndexes()
{
    "0" : {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "ns" : "inventory.inventory",
        "name" : "_id_"
    },
    "1" : {
        "v" : 1,
        "key" : {
            "verb" : 1
        },
        "ns" : "inventory.inventory",
        "name" : "verb_1"
    }
}

scanAndOrder is a very bad thing in MongoDB
MongoDB sorts documents in-memory is very very expensive and with out an index large result sets can be rejected with an error

Explain After the above change

{
    "cursor" : "BtreeCursor verb_1",
    "isMultiKey" : false,
    "n" : 302,
    "nscannedObjects" : 302,
    "nscanned" : 302,
    "nscannedObjectsAllPlans" : 302,
    "nscannedAllPlans" : 302,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 2,
    "indexBounds" : {
        "verb" : [ 
            [ 
                "getInventoryPrice", 
                "getInventoryPrice"
            ]
        ]
    },
    "server" : "myServerIp"
}
BtreeCursor indicates that the query used an index. The cursor includes name of the index. When a query uses an index, the output of explain() includes indexBounds details.

Compare the Performance
To manually compare the performance of a query using more than one index, you can use the hint() method in conjunction with the explain() method.
db.inventory.find({"verb":"getInventoryPrice"}).explain().hint({"verb": 1})
{
    "cursor" : "BtreeCursor verb_1",
    "isMultiKey" : false,
    "n" : 302,
    "nscannedObjects" : 302,
    "nscanned" : 302,
    "nscannedObjectsAllPlans" : 302,
    "nscannedAllPlans" : 302,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "verb" : [ 
            [ 
                "getInventoryPrice", 
                "getInventoryPrice"
            ]
        ]
    },
    "server" : "myServerIp"
}

The ordering of fields in an Index should be
fields where extract exact values
fields where will sort
fields where query for range vallues like $in, $gt, $lt...Etc

Need to be very careful if you have
Many query patterns
Use mongoDb like RDBMS
Have many indexes for each collection

How to identify problematic quires by profiling
// Will profile all queries that take 100 ms
db.setProfilingLevel(1, 100);

// Will profile all queries
db.setProfilingLevel(2);

// Will disable the profiler
db.setProfilingLevel(0);

// Find the most recent profile entries
db.system.profile.find().sort({$natural:-1});
    
// Find all queries that took more than 5ms
db.system.profile.find( { millis : { $gt : 5 } } );
    
// Find only the slowest queries
db.system.profile.find().sort({millis:-1});

db.system.profile.find({op:{$in: ["query", "update", "command"]}})


So far we have discussed how to do indexing in manual methods. Now lets discuss about some easy ways
DEX- Dex is a MongoDB performance tuning tool that compares queries to the available indexes in the queried collection(s) and generates index suggestions based on simple heuristics. Currently you must provide a connection URI for your database.
https://github.com/mongolab/dex/blob/master/README.md

Identify Indexing with DEX
A Indexed query helps performance by several ways. The trick is to identify an ideal indexes. Even for experts, hand-crawling through the MongoDB logs for slow queries is a laborious process.
Dex helps you in this. Dex is a tool that looks at your slow queries and tells you exactly which indexes you need to make those queries fast.

Running Dex is easy! For up-to-date instructions, refer to our README on GitHub. However, what you do with Dex's recommendations depends on your situation.

Install Dex
>pip install dex # in sudo mode or use sudo pip install dex

How to find the Location on EC2

> vi /etc/mongod.conf

Location of mongodb log can be found with logpath=/var/log/mongo/mongod.log

dex -f /var/log/mongo/mongod.log mongodb://ec2-<myec2ip>.ap-southeast-2.compute.amazonaws.com:<mongo port>/inventory

> dex -f /var/log/mongo/mongod.log mongodb://ec2-<myec2ip>.ap-southeast-2.compute.amazonaws.com:<mongo port>/inventory

{
    'runStats': {
        'linesRecommended': 150,
        'linesProcessed': 169,
        'linesPassed': 66594
    },
    'results': [
        {
            'queryMask': '{"$query":{"verb":"<val>"}}',
            'namespace': 'mydb.inventory',
            'recommendation': {
                'index': '{"date": 1}',
                'namespace': 'mydb.inventory',
                'shellCommand': 'db["inventory"].ensureIndex({"date": 1}, {"background": true})'
            },
            'details': {
                'count': 95,
                'totalTimeMillis': 10676,
                'avgTimeMillis': 112
            }
        },
        ...............
    ]
}


Filter by query time (millis)
dex -f /var/log/mongo/mongod.log mongodb://ec2-<myec2ip>.ap-southeast-2.compute.amazonaws.com:<mongo port>/inventory -s 400

In more details 
dex -f /var/log/mongo/mongod.log mongodb://ec2-<myec2ip>.ap-southeast-2.compute.amazonaws.com:<mongo port>/inventory -v

Even Dex guide to find the required Indexes final decision in on you to find the required one



References
http://mongolab.org/dex/
http://blog.mongolab.com/2012/06/cardinal-ins/