0

I have

  • Sitecore 8.1 UPD3(CM, CD, Processing distributed environment)
  • mogodb 3.0.15 (sharded cluster and replication)

I am currently facing the problem that accessing CDs takes a very long time to query analytics.Interactions.
This query is as follows: (Excerpt from mongod's log)

$query: { 
  $or: [ 
    { ContactId: BinData(3, 5F98E7DE08126C42B1DC9196379EB7B7) }, 
    { ContactId: BinData(3, 3C1A9FBF535C1A4690FE9B86F14B8105) } 

  ], _t: "VisitData" 
}, 
$orderby: { StartDateTime: 1, _id: 1 } }

According to the log, this execution time takes a very long time of 35245 ms.

Even though {ContactId: 1, ContactVisiteIndex: 1} has an index, I can not understand that it takes such a long time.

How can I get the query to complete in a reasonable amount of time?

All of mongod's log:

2019-03-14T18:25:41.358+0900 I COMMAND [conn65760] query analytics.Interactions query: { $query: { $or: [ { ContactId: BinData(3, 5F98E7DE08126C42B1DC9196379EB7B7) }, { ContactId: BinData(3, 3C1A9FBF535C1A4690FE9B86F14B8105) } ], _t: "VisitData" }, $orderby: { StartDateTime: 1, _id: 1 } } planSummary: IXSCAN { StartDateTime: 1.0, _id: 1.0 } cursorid:47073259001 ntoreturn:0 ntoskip:0 nscanned:1944530 nscannedObjects:1944530 keyUpdates:0 writeConflicts:0 numYields:16100 nreturned:101 reslen:11029 locks:{ Global: { acquireCount: { r: 32202 } }, MMAPV1Journal: { acquireCount: { r: 16105 }, acquireWaitCount: { r: 15 }, timeAcquiringMicros: { r: 3696316 } }, Database: { acquireCount: { r: 16101 } }, Collection: { acquireCount: { R: 16101 }, acquireWaitCount: { R: 4 }, timeAcquiringMicros: { R: 86158 } } } 35245ms


2019/03/18

I noticed the following in the log:

planSummary: IXSCAN { StartDateTime: 1.0, _id: 1.0 }

What this IXSCAN shows is that you are using the wrong index.
We considered the following steps to get a valid index, but is it correct?

1st db.Interactions.createIndex( { ContactId:1, _t:1 } . {background: true})
2nd db.Interactions.clear()

3
  • We need to know version numbers and what type of setup you're running this on, to be able to form any reasonable answer.
    – Mark Cassidy
    Mar 15, 2019 at 2:27
  • I use follows mongoDB installation. monogoDB's version is 3.0.15 3 mongos instance 3 config instance 2 shard nodes with replica sets Is this information sufficient? Mar 15, 2019 at 2:39
  • For me, it seems fast because querying the Interaction Collection usually takes a lot of time. This is due to the large number of entries that you have in that particular collection. 35 sec it ok for me as I have an Interaction Collection with 30 millions of records and it takes lots of time to query. Mar 18, 2019 at 10:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.