Faster Status Query for MongoDB

I have been using indexOnly query radically in our project. But I have not got a chance to prove it really makes a difference. So this post will talk about if MongoDB’s compound index can boost query performance in some cases.

Background

Status query is one of the most common behaviours in an even simple system. Checking if a user is activated. Role checking, ACL verifying, etc.

It’s very likely that sometimes you just care about one field’s data instead of whole document before process further tasks. In these cases, you probably can query on index only without touching document.

Before we start, let us define indexOnly query. In the mongo shell, you can explain any query by using explain function as follows. If you include the key in a compound index and exclude _id. Then the query is a indexOnly query. Which returns the value from index instead of loading the whole document.

db.test.find({ 0: 0 },{ 1: 1, _id: 0 }).explain()
{
        "cursor" : "BtreeCursor 0_1_1_1",
        "nscanned" : 100000,
        "nscannedObjects" : 100000,
        "n" : 100000,
        "millis" : 236,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : true,
}

Prepare

To start testing, let’s open mongo shell and execute the following script to prepare some data.

The data structure will end up like

{ _id: [ObjectId], "0": 0, "1": 1, /*...*/ "999": 999 }

and a compound index will be created on keys 0 and 1.

(function(){
    db.test.drop();
    db.test.ensureIndex({0:1, 1:1});

    var documents = 100000;
    var keys = 1000;

    var obj = {};
    for (var i = 0; i < keys; i++){
        obj[i] = i;
    }

    for(var j = 0; j < documents; j++){
        db.test.save(obj);
    }
})();

NOTE: the script above costs roughly 5 minutes to run.

Benchmark

Since we’ve prepared the data. What we need to do now is to simply prove our concept.

Here’s a benchmark script which can be run in mongo shell. It simply receive a parameter to tell benchmark script enable indexOnly query or not. And finally print the execution time.

var benchmarkIndexOnly = function(indexOnly){
    var query  = { 0: 0 },
        fields = { 1: 1 };

    if (indexOnly) {
        fields["_id"] = 0; // excludes _id to enable indexOnly
    }

    var fun = function(){
        return db.test.find(query, fields)
        // ensure full query coz mongo shell limits return result
                      .explain();
    };

    print("indexOnly: " + fun().indexOnly);

    var start = +new Date();

    for (var i = 0; i < 50; i++){
        fun();
    }

    var end = +new Date();

    print((end - start)/1000 + " s");
};

As my develop box is windows, so i ran the script on my develop environment. But the result I got was counter-intuitive and made me depressed. The result below shows by turning indexOnly off can get better performance.

> benchmarkIndexOnly(true);
indexOnly: true
7.377 s
>
> benchmarkIndexOnly(false);
indexOnly: false
7.344 s

Then I turned on the Linux box, prepared the data and executed the script again, and the result was different.

> benchmarkIndexOnly(true);
indexOnly: true
6.614 s
>
> benchmarkIndexOnly(false);
indexOnly: false
6.83 s

Conclusion

I am happy that indexOnly takes effect on Linux box as usually MongoDB is hosted on Linux server for better performance.

Things to notice

  • You will not take advantages of indexOnly feature if your index covers any field which is an array.
  • Neither can you get _id via indexOnly, even you put _id into compound indexes.

Environment

{
    "Windows": "Windows 7",
    "Linux": "Ubuntu 11.10"
    "MongoDB": "2.0.1"
}
Advertisements

2 thoughts on “Faster Status Query for MongoDB

  1. Hey there, I think your site might be having browser compatibility issues.

    When I look at your website in Opera, it looks fine but
    when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that,
    great blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s