[SERVER-42483] $multiply operations are slow. Created: 29/Jul/19  Updated: 06/Dec/19  Resolved: 06/Dec/19

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

Type: Bug Priority: Major - P3
Reporter: Ryan McQuen Assignee: Danny Hatcher (Inactive)
Resolution: Incomplete Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Mongoose: 5.4.14


Attachments: File diagnostic.data.tar.xz     Text File fast_query_explained.txt     File fast_query_no_multiply.json     Text File slow_query_explained.txt     File slow_query_with_multiply.json    
Operating System: ALL
Participants:

 Description   

When using `$multiply` inside of an aggregate, the execution time of a query is increased dramatically.

 

For example, on a 10,000 item match, it is up to 30x slower than the same aggregate with no `$multiply` calculations. For example, I am including two queries with the only difference being using or not using `multiply`. I would expect the calculation to be slower, but not by this order of magnitude.

 

I have the queries stored in a file but I am not sure where to attach or send those files.



 Comments   
Comment by Danny Hatcher (Inactive) [ 06/Dec/19 ]

Sorry to hear that you were unable to reproduce but glad to hear that you're not seeing the issue anymore.

Comment by Ryan McQuen [ 06/Dec/19 ]

Daniel, unfortunately, no.

 

We can close this bug and I will reference it when I get around to making a Mongoose bug. Thank you for the help!

Comment by Danny Hatcher (Inactive) [ 06/Dec/19 ]

rmcquen@funko.com, have you had time to check?

Comment by Ryan McQuen [ 08/Nov/19 ]

Daniel, sorry, I have been very busy and have not had a chance to run this through Mongoose yet. I will try to make time in our next sprint.

Comment by Danny Hatcher (Inactive) [ 08/Nov/19 ]

rmcquen@funko.com, have you been able to see this issue again?

Comment by Danny Hatcher (Inactive) [ 17/Oct/19 ]

To clarify, if you run those same explain commands via Mongoose do you see the same data or is it significantly worse?

Comment by Ryan McQuen [ 14/Oct/19 ]

Hmmm, maybe I need to increase the sample size beyond 2000 then. I am also using Mongoose, so maybe there is another factor at play here?

Comment by Danny Hatcher (Inactive) [ 14/Oct/19 ]

From the two explain outputs, it appears that the query that didn't apply any $multiply took 61 milliseconds while the query that performed 10,000 if-then $multiply took 99 milliseconds. Does this match your interpretation? If that is accurate, I don't believe there's a bug here.

Comment by Ryan McQuen [ 10/Oct/19 ]

Actually, I got it under 150 with XZ compression:

diagnostic.data.tar.xz

Comment by Ryan McQuen [ 10/Oct/19 ]

Daniel, I've attached the explains here but the diagnostic data is too large for the 150mb limit, is there some other way I can share it to you?

Comment by Ryan McQuen [ 20/Sep/19 ]

Daniel, sorry for the late reply, working on getting approval to share that data.

Comment by Danny Hatcher (Inactive) [ 04/Sep/19 ]

rmcquen@funko.com, have you had a chance to read my previous comment?

Comment by Danny Hatcher (Inactive) [ 05/Aug/19 ]

Could you please provide the explain(true) outputs for both fast and slow calculations around your inferred breaking point? Additionally, please attach your "diagnostic.data" directory (located under your $dbpath) to this ticket so I can confirm that it's not a system issue.

Comment by Ryan McQuen [ 30/Jul/19 ]

Daniel, thanks for the reply! The tipping point seems to be around 2000 items, at which point the calculation (even a calculation of multiplying by 1), takes about twice as long when being processed by multiply. On my system the difference is ~250ms vs ~500ms.

Comment by Danny Hatcher (Inactive) [ 30/Jul/19 ]

Adding a large amount of equations to perform on top of a query that didn't otherwise do those equations will always slow things down. The exact decrease of the slowdown will be dependent on many things including the environment in which the query is run. Do you see a performance decrease of this scale when performing a small amount of multiplies?

Generated at Thu Feb 08 05:00:36 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.