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()

  • 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 '19 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? – Kato Tetsuya Mar 15 '19 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. – Hishaam Namooya Mar 18 '19 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.