MongoDB query taking too much time to retrieve data

Question

I have one collection with 3 million documents and the following indexes:

{ ts : 1 } , {u_id: 1}

Note that these are two separate ascending indexes, not a compound index.

When I run this query:

db.collection.find({u_id: 'user'}).sort({ts : -1}).skip(0).limit(1)

it takes +100ms. I have the following logs:

2017-04-15T06:42:01.147+0000 I COMMAND [conn783] query
db.collection query: { orderby: { ts: -1 }, $query: {
u_id: "user-ki-id } } planSummary: IXSCAN { u_id:
1 }, IXSCAN { u_id: 1 } ntoreturn:1 ntoskip:0 keysExamined:10795
docsExamined:10795 hasSortStage:1 cursorExhausted:1 keyUpdates:0
writeConflicts:0 numYields:86 nreturned:1 reslen:771 locks:{ Global: {
acquireCount: { r: 174 } }, Database: { acquireCount: { r: 87 } },
Collection: { acquireCount: { r: 87 } } } 246ms

A few notable points about the problem:

• There is no other load on MongoDB i.e. no other queries which take +100ms
• This is happening every minute; I think I am storing data every minute so this is happening
• The query flow is to first run the read query (as above), then the next query is a bulk insertion. This flow is repeated every one minute.

So my questions are:

• Why is it happening? Are there any design flaws in my indexing?
• Might it be worthwhile to change indexing to be descending, like {ts: -1}? What is the actual difference between these indexes?
• According to MongoDB documentation, when you are doing sorting with order then result will pick from disk not "in-memory". Does this explain why it takes +100ms?
• Can anybody explain me profiling log in detail level?
• Is it desired behaviour of MongoDB?

The same thing is also happening when I run a range search on this collection; this takes 3-5 seconds.

EDIT: I have only add {u_id: 1, ts: -1} index. Remove all other index (except _id). Still in first time query execution taking +100ms. This should not happen.
Query:

db.getCollection('locations') .find({u_id: "USR-WOWU"}) .sort({ts: -1}) .explain(true)

OutPut::

/* 1 */ { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "db_name.collection_name", "indexFilterSet" : false, "parsedQuery" : { "user_id" : { "$eq" : "USR-WOWU" } }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "u_id" : 1.0, "ts" : -1.0 }, "indexName" : "u_id_1_ts_-1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 1, "direction" : "forward", "indexBounds" : { "u_id" : [ "[\"USR-WOWU\", \"USR-WOWU\"]" ], "ts" : [ "[MaxKey, MinKey]" ] } } }, "rejectedPlans" : [] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 164, "executionTimeMillis" : 119, "totalKeysExamined" : 164, "totalDocsExamined" : 164, "executionStages" : { "stage" : "FETCH", "nReturned" : 164, "executionTimeMillisEstimate" : 120, "works" : 165, "advanced" : 164, "needTime" : 0, "needYield" : 0, "saveState" : 3, "restoreState" : 3, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 164, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 164, "executionTimeMillisEstimate" : 0, "works" : 165, "advanced" : 164, "needTime" : 0, "needYield" : 0, "saveState" : 3, "restoreState" : 3, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "u_id" : 1.0, "ts" : -1.0 }, "indexName" : "u_id_1_ts_-1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 1, "direction" : "forward", "indexBounds" : { "u_id" : [ "[\"USR-WOWU\", \"USR-WOWU\"]" ], "ts" : [ "[MaxKey, MinKey]" ] }, "keysExamined" : 164, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } }, "allPlansExecution" : [] }, "serverInfo" : { "host" : "manish", "port" : 22022, "version" : "3.2.13", "gitVersion" : "23899209cad60aaafe114f6aea6cb83025ff51bc" }, "ok" : 1.0 }

Please copy above jSON and format into any editor.

After above query, the next same query will response with in ~2 ms. But When I do few insertion then after one min same thing will be repeated. (1st time query will take time +100ms and then it will take ~2ms.)

Is something missing or anything is required to configuration in my mongoDB ??

Answer 1

Why is it happening

The docsExamined:10795 and hasSortStage:1 portions of this log line indicates that the query is scanning 10,795 from disk and then sorting the results in memory. A guide on interpreting log lines can be found here.

A performance improvement can likely be gained by indexing this query to avoid the in-memory sort.

For this query, you should try creating the index { 'u_id' : 1, 'ts' : -1 }.

Is it really worthfull if I will change indexing like {ts: -1} in descending order.

Indexes can be read in either direction, so the index order isn't super important on single field indexes. However, sort ordering can be very important in compound indexes.

Updated

Based on the explain plan, the query is now properly using the index to read the results from the index in order, which avoids the in-memory sort. It looks like this knocked off ~100ms off the query.

However, it looks like this query is no longer using .skip(0).limit(1). Can you add these back in and see if performance improves?

There doesn't appear to be anything wrong with your deployment; this behavior seems normal for queries that are not fully indexed.

Re-running the exact same query will be quick because the existing results ("the working set") are already stored in memory. Inserting new data can make the results of the query change, meaning the results may need to be read back into memory again.