[SERVER-32826] Lookup Pipeline issue performance is very bad in more records Created: 22/Jan/18  Updated: 02/Apr/18  Resolved: 09/Mar/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.6.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: nasir Assignee: Kelsey Schubert
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-31760 Lookup sub-pipeline is not using inde... Closed
Operating System: ALL
Participants:

 Description   

Hey I am using Lookup Pipeline
the result is coming in 1 min when records are more than 2000



 Comments   
Comment by Kelsey Schubert [ 09/Mar/18 ]

Hi nasir

We haven’t heard back from you for some time, so I’m going to mark this ticket as resolved. If this is still an issue for you, please provide additional information and we will reopen the ticket.

Regards,
Kelsey

Comment by Kelsey Schubert [ 20/Feb/18 ]

Hi nasir,

First to address your question, MongoDB 3.6.3-rc1 has been released, so I'd expect MongoDB 3.6.3 GA to be released within about a week. Regarding your latest report, so we can investigate, would you please provide the output of the explain(true) using the inner pipeline of your lookup?

So it would like something similar this

db.foo.find(
    {"$expr": {
     "$and": [{
      "$lte": ["$date", ISODate("2016-01-16T19:00:00Z")]
     }, {
      "$gte": ["$date", ISODate("2014-12-18T19:00:00Z")]
     }, {
      "$eq": ["$wao_id", "$$wao_id_provider"]
     }).explain(true)

Thank you,
Kelsey

Comment by nasir [ 14/Feb/18 ]

Hi Kelsey,

I tested previously records around on v 3.7.1

_provider.wao = 1200
_wao.abc = 6000

it is fine in this much records

but when I tried with

_provider.wao = 2000
_wao.abc = 300,000

it is taking time around 30 sec to get records

the index has been created on both collection
on wao_id and date

could you please let me know the issue

thanks, the quick reply will really help to resolve our

Comment by nasir [ 01/Feb/18 ]

Hi Kelsey,

I have tested this in 3.7.1 and it is working fine in this version.

one thing I want to inform you that it will only work fast after creating index on both collection eq ids in my case ("$eq": ["$wao_id", "$$wao_id_provider"])

and if possible please try to release 3.6.3 as early as possible i.e mid of Feb

Thanks for your quick response

Comment by Kelsey Schubert [ 01/Feb/18 ]

Hi nasir,

After reviewing this issue, I suspect that you'd benefit from SERVER-31760. Would you please test the performance on the development release 3.7.1 to see if this issue has been resolved by the fix described in SERVER-31760?

We're currently working on backporting this ticket into the stable 3.6 branch, and it would be helpful to know whether this fix resolves this issue or if we need to do additional investigation to determine the cause of the poor performance you're observing. Currently, I would expect 3.6.3 to be released around early March. So if you're able to test against 3.7.1, it would help us more quickly identify if there may be additional work to do this space.

Please let me know if you have any questions about testing the performance of your lookup pipelines with the development release of MongoDB 3.7.1.

Thanks for your help,
Kelsey

Comment by Mark Agarunov [ 30/Jan/18 ]

Hello nasir,

Thank you for the report. Unfortunately to further diagnose this we'll need the logs and diagnostic data to see why you are experiencing this behavior. Additionally, if possible, please provide an example of structure of the documents you are querying. Without this, we have no way to reproduce or diagnose the cause of this issue.

Thanks,
Mark

Comment by nasir [ 30/Jan/18 ]

Any Update

and also let me know the release date of 3.6.3

Comment by nasir [ 29/Jan/18 ]

This the query, in this query, if I replace with normal lookup it works well but with this lookup inner pipeline it works very slow

db.getCollection("_provider.wao").aggregate([{
 "$match": {
  "account_id": "189766845"
 }
}, {
 "$match": {}
}, {
 "$lookup": {
  "from": "_wao.abc",
  "let": {
   "wao_id_provider": "$wao_id"
  },
  "pipeline": [{
   "$match": {
    "$expr": {
     "$and": [{
      "$lte": ["$date", ISODate("2016-01-16T19:00:00Z")]
     }, {
      "$gte": ["$date", ISODate("2014-12-18T19:00:00Z")]
     }, {
      "$eq": ["$wao_id", "$$wao_id_provider"]
     }]
    }
   }
  }],
  "as": "waoi"
 }
, {
 "$unwind": {
  "path": "$waoi",
  "preserveNullAndEmptyArrays": true
 }
]])

Comment by Mark Agarunov [ 22/Jan/18 ]

Hello nasir,

Thank you for the report. To get a better idea of what may be causing this, could you please provide the following:

  • The query you are experiencing this issue with
  • The complete logs from mongod when this issue is present
  • Please archive (tar or zip) the $dbpath/diagnostic.data directory.

This should give some insight into the behavior you're seeing.

Thanks,
Mark

Generated at Thu Feb 08 04:31:24 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.