[mongodb-user] Query performance explain

[danyolgiax]

Hi, looking at our logs I see the following:

2018-11-21T09:01:10.924+0000 I COMMAND [conn66949] command company.Utenti
command: find { find: "Utenti", filter: { Username: "pd-sy1" }, skip: 0,
limit: 2, noCursorTimeout: false, $db: "company" } planSummary: IXSCAN {
Username: 1 } keysExamined:1 docsExamined:1 cursorExhausted:1 numYields:2
nreturned:1 reslen:32702 locks:{ Global: { acquireCount: { r: 6 } },
Database: { acquireCount: { r: 3 } }, Collection: { acquireCount: { r: 3 }
} } protocol:op_query 210ms
2018-11-21T09:01:12.208+0000 I COMMAND [conn63687] getmore local.oplog.rs
command: { getMore: 13947219678, collection: "oplog.rs", batchSize: 0 }
originatingCommand: { find: "oplog.rs", filter: { ts: { $gte:
Timestamp(1542724104, 10) } } } planSummary: COLLSCAN cursorid:13947219678
ntoreturn:0 keysExamined:0 docsExamined:0 numYields:2 nreturned:0 reslen:20
locks:{ Global: { acquireCount: { r: 8 } }, Database: { acquireCount: { r:
4 } }, oplog: { acquireCount: { r: 4 } } } 472ms
2018-11-21T09:01:12.502+0000 I COMMAND [conn67142] command company.Scadenzario
command: find { find: "Scadenzario", filter: { DataScadenza: { $gte: new
Date(1538344800000), $lte: new Date(1540940400000) } }, sort: {
DataScadenza: -1 }, skip: 0, noCursorTimeout: false, $db: "company" }
planSummary: IXSCAN { DataScadenza: 1 } cursorid:1069524993552
keysExamined:101 docsExamined:101 numYields:2 nreturned:101 reslen:112109
locks:{ Global: { acquireCount: { r: 6 } }, Database: { acquireCount: { r:
3 } }, Collection: { acquireCount: { r: 3 } } } protocol:op_query 1302ms

As you can see we have a query uses index that returns in 210ms, then a
"local.oplog.rs command" and finally a query that uses the index that
returns in 1302ms.

If I try the last query by command line it returns in 0.120ms.

Can you help me understanding why it happened?
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/b666d06e-0bf6-44e3-9bff-bdb477d74879%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

['Kevin Adistambha' via mongodb-user]

Hi,

As you can see we have a query uses index that returns in 210ms, then a “
local.oplog.rs command” and finally a query that uses the index that
returns in 1302ms.

It’s difficult to ascertain what happened when looking at an isolated
sequence like this. However from what I can understand:

- The first query could return in 210ms for any number of reasons
including: cold cache, slow disk, burdened server, etc.
- The oplog query indicates that a secondary node is syncing from this
one. However there is no indication if this node is the primary or a
secondary, since it is possible that a secondary can sync from another
secondary.
- The third query appears to be well-targeted using an index. However it
also yields twice. Typically a query yields when it needs to wait for
something, e.g. loading the documents from disk.
- Trying the last query (I assume) the second time returns faster.
Possibly because the related documents are in the cache already.

A lot more information would be needed to determine what really happened.
For starters:

- What is your MongoDB version, and what is the hardware spec of each
server
- Whether any setting in MongoDB was changed from its default, and
whether the Production Notes
<https://docs.mongodb.com/manual/administration/production-notes/> was
observed
- What problem are you trying to solve, and is it a consistent or an
intermittent problem
- Any relevant information that is relevant to any issue you’re trying
to solve, and what you have tried so far

Best regards,
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/dbc24cdd-1445-499e-aab1-5a14d59d00c5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.