Mongodb find() operation sometimes extremely slow

Question

I have a collection in mongodb (latest 3.0.4, not wiredtiger) with about 8 million documents. For the most part it's very fast but sometimes all reads to the database start stacking up and the culprit is the following query:

> db.currentOp();
{
    "inprog" : [
        {
            "desc" : "conn619",
            "threadId" : "0x858e8a0",
            "connectionId" : 619,
            "opid" : 4190651,
            "active" : true,
            "secs_running" : 171,
            "microsecs_running" : NumberLong(171584220),
            "op" : "query",
            "ns" : "default.products",
            "query" : {
                "$query" : {
                    "ps" : {
                        "$gt" : 0
                    },
                    "price" : {
                        "$type" : 1
                    },
                    "ships_to_australia" : true,
                    "category_memberships.node" : "2344577"
                },
                "orderby" : {
                    "salesRate" : -1
                }
            },
            "planSummary" : "IXSCAN { salesRate: -1.0 }",
            "client" : "142.0.36.114:29880",
            "numYields" : 135044,
            "locks" : {

            },
            "waitingForLock" : false,
            "lockStats" : {
                "Global" : {
                    "acquireCount" : {
                        "r" : NumberLong(270088)
                    }
                },
                "MMAPV1Journal" : {
                    "acquireCount" : {
                        "r" : NumberLong(154166)
                    },
                    "acquireWaitCount" : {
                        "r" : NumberLong(26)
                    },
                    "timeAcquiringMicros" : {
                        "r" : NumberLong(101532)
                    }
                },
                "Database" : {
                    "acquireCount" : {
                        "r" : NumberLong(135044)
                    }
                },
                "Collection" : {
                    "acquireCount" : {
                        "R" : NumberLong(135044)
                    },
                    "acquireWaitCount" : {
                        "R" : NumberLong(19122)
                    },
                    "timeAcquiringMicros" : {
                        "R" : NumberLong(14462598)
                    }
                }
            }
        }
    ]
}

The strange thing is the identical query is fast most of the time just sometimes it's incredibly slow like this and seems to lock any further read operations to the database completely which I thought shouldn't happen in mongo.

The server is dual CPU with 48GB of ram so the issue shouldn't be caused by hardware.

Answer 1

You are not using the correct index. The current index will bring the results sorted but it have to perform a full collection scan. You need an index like:

{"category_memberships.node":1,"ships_to_australia":1,"ps":1,"salesRate":-1}

This would limit the search and sorts at the same time.

The query execution variation has to do with the hotset and collection locking.