MongoDB: degraded query performance

Question

I have a user's collection in MongoDB with over 2.5 million of records which constitute to 30 GB. I have about 4 to 6 GB of indexes. It's in sharded environment with two shards, each consisting of replica set. Servers are dedicated especially to Mongo with no overhead. Total RAM is over 10 GB which more than enough for the kind of queries I am performing (shown below).

My concern is that despite of having indexes to the appropriate fields time to retrieve the result is huge (2 minutes to whopping 30 minutes), which is not acceptable. I am newbie to MongoDB & really in confused state as to why this is happening.

Sample schema is:

user:
{
_id: UUID (indexed by default),
name: string,
dob: ISODate,
addr: string,
createdAt: ISODate (indexed),
.
.
.,
transaction:[
{
firstTransaction: ISODate(indexed),
lastTransaction: ISODate(indexed),
amount: float,
product: string (indexed),
.
.
.
},...
],
other sub documents...
}

Sub document length varies from 0- 50 or so.

Queries which I performed are:

1) db.user.find().min({createdAt:ISODate("2014-12-01")}).max({createdAt:ISODate("2014-12-31")}).explain()

This query worked slow at first, but then was lightning fast(I guess because of warming up).

2) db.user.find({transaction:{$elemMatch:{product:'mobile'}}}).explain()

This query took over 30 mins & warming up wasn't of help as every time the performance was same. It returned over half of the collection.

3) db.user.find({transaction:{$elemMatch:{product:'mobile'}}, firstTransaction:{$in:[ISODate("2015-01-01"),ISODate("2015-01-02")]}}}}).explain()

This is the main query which I want to be performant. But to my bad luck this query takes more than 30 mins to perform. I tried many versions of it such as this:

db.user.find({transaction:{$elemMatch:{product:'mobile'}}}).min({transaction:{$elemMatch:{firstTransaction:ISODate("2015-01-01")}}}).max({transaction:{$elemMatch:{firstTransaction:ISODate("2015-01-02")}}}).explain()

This query gave me error:

planner returned error: unable to find relevant index for max/min query & with hint():

planner returned error: hint provided does not work with min query

I used min max function because of the uncertainty of the range queries in MongoDB with $lt, $gt operators, which sometimes ignore either of the bound & end up scanning more documents than needed.

I used indexes such as:

db.user.ensureIndex({createdAt: 1})

db.user.ensureIndex({"transaction.firstTransaction":1})

db.user.ensureIndex({"transaction.lastTransaction":1})

db.user.ensureIndex({"transaction.product":1})

I tried to use compound indexing for the 3 query, which is:

db.user.ensureIndex({"transaction.firstTransaction":1, "transaction.product":1})

But this seems to give me no result. Query gets stuck & never returns the result. I mean it. NEVER. Like deadlocked. I don't know why. So I dropped this index & got the result after waiting for over half an hour (really frustrating).

Please help me out as I am really desperate to find out the solution & out of ideas.

This output might help:

Following is the output for:

query:

db.user.find({transaction:{$elemMatch:{product:"mobile", firstTransaction:{$gte:ISODate("2015-01-01"), $lt:ISODate("2015-01-02")}}}}).hint("transaction.firstTransaction_1_transaction.product_1").explain()

output:

{
        "clusteredType" : "ParallelSort",
        "shards" : {
                "test0/mrs00.test.com:27017,mrs01.test.com:27017" : [
                        {
                                "cursor" : "BtreeCursor transaction.product_1_transaction.firstTransaction_1",
                                "isMultiKey" : true,
                                "n" : 622,
                                "nscannedObjects" : 350931,
                                "nscanned" : 352000,
                                "nscannedObjectsAllPlans" : 350931,
                                "nscannedAllPlans" : 352000,
                                "scanAndOrder" : false,
                                "indexOnly" : false,
                                "nYields" : 119503,
                                "nChunkSkips" : 0,
                                "millis" : 375693,
                                "indexBounds" : {
                                        "transaction.product" : [
                                                [
                                                        "mobile",
                                                        "mobile"
                                                ]
                                        ],
                                        "transaction.firstTransaction" : [
                                                [
                                                        true,
                                                        ISODate("2015-01-02T00:00:00Z")
                                                ]
                                        ]
                                },
                                "server" : "ip-12-0-0-31:27017",
                                "filterSet" : false
                        }
                ],
                "test1/mrs10.test.com:27017,mrs11.test.com:27017" : [
                        {
                                "cursor" : "BtreeCursor transaction.product_1_transaction.firstTransaction_1",
                                "isMultiKey" : true,
                                "n" : 547,
                                "nscannedObjects" : 350984,
                                "nscanned" : 352028,
                                "nscannedObjectsAllPlans" : 350984,
                                "nscannedAllPlans" : 352028,
                                "scanAndOrder" : false,
                                "indexOnly" : false,
                                "nYields" : 132669,
                                "nChunkSkips" : 0,
                                "millis" : 891898,
                                "indexBounds" : {
                                        "transaction.product" : [
                                                [
                                                        "mobile",
                                                        "mobile"
                                                ]
                                        ],
                                        "transaction.firstTransaction" : [
                                                [
                                                        true,
                                                        ISODate("2015-01-02T00:00:00Z")
                                                ]
                                        ]
                                },
                                "server" : "ip-12-0-0-34:27017",
                                "filterSet" : false
                        }
                ]
        },
        "cursor" : "BtreeCursor transaction.product_1_transaction.firstTransaction_1",
        "n" : 1169,
        "nChunkSkips" : 0,
        "nYields" : 252172,
        "nscanned" : 704028,
        "nscannedAllPlans" : 704028,
        "nscannedObjects" : 701915,
        "nscannedObjectsAllPlans" : 701915,
        "millisShardTotal" : 1267591,
        "millisShardAvg" : 633795,
        "numQueries" : 2,
        "numShards" : 2,
        "millis" : 891910
}

Query:

db.user.find({transaction:{$elemMatch:{product:'mobile'}}}).explain()

Output:

{
        "clusteredType" : "ParallelSort",
        "shards" : {
                "test0/mrs00.test.com:27017,mrs01.test.com:27017" : [
                        {
                                "cursor" : "BtreeCursor transaction.product_1",
                                "isMultiKey" : true,
                                "n" : 553072,
                                "nscannedObjects" : 553072,
                                "nscanned" : 553072,
                                "nscannedObjectsAllPlans" : 553072,
                                "nscannedAllPlans" : 553072,
                                "scanAndOrder" : false,
                                "indexOnly" : false,
                                "nYields" : 164888,
                                "nChunkSkips" : 0,
                                "millis" : 337909,
                                "indexBounds" : {
                                        "transaction.product" : [
                                                [
                                                        "mobile",
                                                        "mobile"
                                                ]
                                        ]
                                },
                                "server" : "ip-12-0-0-31:27017",
                                "filterSet" : false
                        }
                ],
                "test1/mrs10.test.com:27017,mrs11.test.com:27017" : [
                        {
                                "cursor" : "BtreeCursor transaction.product_1",
                                "isMultiKey" : true,
                                "n" : 554176,
                                "nscannedObjects" : 554176,
                                "nscanned" : 554176,
                                "nscannedObjectsAllPlans" : 554176,
                                "nscannedAllPlans" : 554176,
                                "scanAndOrder" : false,
                                "indexOnly" : false,
                                "nYields" : 107496,
                                "nChunkSkips" : 0,
                                "millis" : 327928,
                                "indexBounds" : {
                                        "transaction.product" : [
                                                [
                                                        "mobile",
                                                        "mobile"
                                                ]
                                        ]
                                },
                                "server" : "ip-12-0-0-34:27017",
                                "filterSet" : false
                        }
                ]
        },
        "cursor" : "BtreeCursor transaction.product_1",
        "n" : 1107248,
        "nChunkSkips" : 0,
        "nYields" : 272384,
        "nscanned" : 1107248,
        "nscannedAllPlans" : 1107248,
        "nscannedObjects" : 1107248,
        "nscannedObjectsAllPlans" : 1107248,
        "millisShardTotal" : 665837,
        "millisShardAvg" : 332918,
        "numQueries" : 2,
        "numShards" : 2,
        "millis" : 337952
}

Please let me know if I have missed any of the details.

Thanks.

Answer 1

1st: Your queries are overly complicated. Using $elemMatch way too often. 2nd: if you can include your shard key in the query it will drastically improve speed.

I'm going to optimize your queries for you:

db.user.find({
     createdAt: {
          $gte: ISODate("2014-12-01"), 
          $lte: ISODate("2014-12-31")
     }
}).explain()

db.user.find({
    'transaction.product':'mobile'
}).explain()

db.user.find({
    'transaction.product':'mobile', 
    firstTransaction:{
       $in:[
           ISODate("2015-01-01"),
           ISODate("2015-01-02")
       ]
    }
}).explain()

Bottom line is this: include your shard key each time is a time saver.

It might even save time to loop through your shard keys and make the same query multiple times.

Answer 2

Reason for performance degradation was the large working set. For some queries (mainly range queries) the set exceeded physical limit & page faults occurred. Due to this performance got degraded. One solution I did was to apply some filters for the query which will limit the result set & tried to perform equality check instead of the range (iterating over range). Those tweaks worked for me. Hope it helps others too.