Replies: 14 comments 6 replies
-
|
Hey @jeanbmar. Thank you for the report. We're going to look at reproducing this. I think some rough examples of queries you are using and (rough) collections that you have defined would be helpful in this. Here are some initial questions I have for you that would help with troubleshooting:
|
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Hey @denolfe, thank you for your quick response. query GetBrawler {
Brawlers {
totalDocs
docs {
id
name
slug
portrait {
filename
type
}
description
shortDescription
disabled
rarityIndex
rarityName
stats {
id
value
}
skills {
description
slug
name
type {
icon {
filename
type
}
}
}
starPowers {
name
description
slug
disabled
icon {
filename
type
}
}
gadgets {
description
name
slug
disabled
icon {
filename
type
}
}
pets {
slug
name
stats {
id
value
}
defaultSkin {
thumbnail {
filename
}
}
}
skins {
id
name
cost
obtainType
resource {
icon {
filename
type
}
}
campaigns {
icon {
filename
type
}
}
model {
filename
type
}
thumbnail {
filename
type
}
}
}
}
}This query returns only 60 docs. It took 66s to get the result. |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Hey @jeanbmar — I can shed some light on this for you quick. This is absolutely something going wrong at some point in either custom hooks or a bug within Payload's code as that type of performance is absolutely unacceptable. We've got sites in production with Payload right now that load up 60+ relationships, but the responses are returned in around 1 sec, which is a far cry from what you're experiencing. We'll get to the bottom of this. Regarding the differences between how we Funny thing is that for the first ~2 years of Payload's development, we did use Mongoose's We may be able to optimize the way that our own That problem definitely lies somewhere else. Few questions for you:
|
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Hey @jmikrut, thank you for helping on this. Good call on the custom options. About the other questions, I've checked the I confirm the issue happens both in development and production environments, with both local and remote Payload instances. I've made a test with Payload 0.14.31-beta.0 and response times are identical. |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
I've enabled mongoose debug option. |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
I just noticed I forgot to put the |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Hey @jeanbmar — ok this is all gold. For now, I think it would be a very good idea to increase your But outside of that, I can see two further optimizations that we could make here:
What happens if you increase your |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
I just made an attempt with but doesn't matter for sequential queries such as here: The pool size is probably close to optimal when it reaches the number of relationships. As long as there's only one user doing queries at least. I agree with batching being difficult, in particular with GraphQL. |
Beta Was this translation helpful? Give feedback.
0 replies
-
These are actually also executed in parallel. The promises are all added to an array of promises where are all awaited in parallel. Can't see it in that file but all relationships are added to a top-level I'm surprised that increasing Nonetheless, we have identified 2 things that we are going to build right now which will ideally dramatically improve performance for GraphQL relationships:
Give us a bit here and we'll get these two items published. If you could give them a shot, that would be great! We've also got a test repo setup that we'll be doing some testing with. Will report back shortly! |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Hey @jeanbmar — optimization 1, reducing Optimization #2 will be coming in the following days. Will report back here when it's in place. For now, I'd like to suggest moving this post to Discussions, as it only deals with optimizations at this point. What do you think? If you agree, I'll move it! |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Perfect let's do this. Thank you. |
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Hey there, sorry I never got back with the results. The optimization has halved the queries and execution time so that's definitely positive, thank you. I believe you will have to address this situation when Payload becomes very popular (and you guys are doing a great job overall so I'm sure it will), because other people will deal with this issue and it can kill some projects. |
Beta Was this translation helpful? Give feedback.
3 replies
-
|
Hey @jeanbmar — I totally hear you. We've been deliberating internally regarding how to improve population performance and I'm going to brain-dump here for a minute.
TL;DR, an ensemblistic approach would be great - I agree. But achieving it would be fairly difficult, I think. We will continue to investigate. @jeanbmar have you ever been successful with something like this in the past? Or have you used tools / APIs that handle this gracefully before? |
Beta Was this translation helpful? Give feedback.
-
|
Hey @jmikrut, populate will work differently if used on a single record (after findOne, findById) or multiple records (find). This is my JavaScript statement: await Subscription.find().populate('bot').exec();And this is the Mongoose debug log: Mongoose retrieves all the Bot records to join with my Subscription records using a single network and db query. |
Beta Was this translation helpful? Give feedback.
-
|
Ahhh I see what you're saying. This is something that we could certainly do sooner than later. I can see two complexities to handle:
Given the first bullet, this might still only be a marginal improvement. Worthwhile for sure, but if you're going more than one level of depth, you'd still have duplicative queries being run. Regardless, I think this is worthwhile and it will be done at some point - of course we'd accept PRs as well! We'd probably want to build it into the Thanks for the great discussion! |
Beta Was this translation helpful? Give feedback.
-
https://www.reddit.com/r/node/comments/tpv7iy/does_prisma_work_in_production/i2gj9ty/ |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
Hey @endlessuniverse — thanks for sharing! Payload actually does this too. We memoize all |
Beta Was this translation helpful? Give feedback.
-
|
@jeanbmar just a heads-up, we did a lot of reading about this and realized that this is a common problem that GraphQL APIs face called the N+1 problem. But, it has a beautiful solution! And we just implemented it! This should dramatically improve the performance of even the most complex queries. Once it's merged, will you give it a shot? |
Beta Was this translation helpful? Give feedback.
2 replies
-
|
I will be happy to try it :). Thank you! |
Beta Was this translation helpful? Give feedback.
-
|
Released in v1.0.10 |
Beta Was this translation helpful? Give feedback.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Bug Report
Our CMS model contains ~30 collections with low volumes of data (from 5 to maybe 300 rows per collection).
We use relationships a lot, and the response times are extreme. A query that would take maybe 1s in a RDBMS can take several minutes to run.
Expected Behavior
Database queries should run in a reasonable time.
Current Behavior
Using REST and GraphQL APIs triggers the performance issues.
Possible Solution
I've had a look at the source code and my understanding is that Payload manipulates relationship rows 1 by 1.
Let's say we have a one to many relationship between A and B, ie each record from A is storing 0 or more references to records of B.
Using Mongoose populate method, the following would happen:
Mongoose made 2 queries to MongoDB.
Using Payload the following happens:
Payload generated 1 + B queries to MongoDB.
Now if you have 10 relationships in a collection and you need to stitch 6 collections together the amount of MongoDB queries generated becomes enormous.
Steps to Reproduce
We can provide an access to our environment as well as queries if you need to observe the issue.
Detailed Description
Payload: 0.13.6
Beta Was this translation helpful? Give feedback.
All reactions