Slow query response - How to find reason?

1

I’m doing a query on parse server but it takes so much time to response. A simple query takes 8 seconds where a similar query takes 0 ms on mongodb(i checked with explain method). And some other queries throws an error with mongo connection timed out error.

İm suspected it’s because I’m using mongodb 4.4. when I was using 4.2 i didn’t have this issue. Or maybe i didn’t realised back then. I will install mongo 4.2 and will try again to see if it’s related to 4.4.

My question is: How can i trace this issue to source? What should I do first?

2

The first thing I’d check is the indexes. You can check if there is an appropriate index for your query either through explain, or mongo logs.

1 Like
3

I created indexes. Mongo uses indexes. There is no problem in database level.

But I guess problem occours when I uses nested Queries such as matchesQuery or doesntMatchKeyInQuery.

Inner query results are too big for parse server to handle I guess.

Also How can I use explain method with Parse Query?

I’m using like this:
query.explain(true);

But server logs doesnt show anything besides normal logs like cloucode executed or after save triggered.

How can I find logs for explain()?

Edit: I also find that we can use $hint operator within parse server. (Related github issue)

But Js docs doesn’t mention that. How can I use hint operator with find()?

4

In the case of relational queries, Parse Server will execute two queries in the database. You need to check and make sure that both of them have appropriate indexes. An easy way to do that is by checking the MongoDB logs. By default, it should log the slow queries. If you want to share the queries and the indexes you currently have I can also help you to figure it out. In terms of hint and explain, take a look at the following test case. It will help you to understand both of them.

1 Like
5

Thank you. I understand how to use hint operator.

I’m not able to reach my computer right now. İ will post my query at the evening (approximately 12 hours later.)

İ will edit this message or post a new one.

6

Hi @davimacedo . This is my query:

  const getProfiles = new Parse.Query(Parse.User);
  getProfiles.equalTo("private",false);


  const getPosts = new Parse.Query(Post);
  getPosts.matchesQuery("user",getProfiles);
  getPosts.doesNotMatchKeyInQuery("user","who",getBlock);
  getPosts.include("user");
  getPosts.exclude("words");
  getPosts.containsAll("words",wordArray);
  getPosts.descending("createdAt");
  getPosts.limit(40);

  if(request.params.date!==undefined){
    getPosts.lessThan("createdAt",request.params.date);
  }

I have indexes for private field in User collection. And I also have necessary indexes for Post class.

My user class has 100k users. And only 50 or 60 of them are set private to true. So my getProfiles query returns 100k objects. I think this is the problem. Fetching 100k object takes a lot of time. Also doing query with 100k keys in mongo is alos taking alot of time.

And in future if user base grows this query will take much more time than now. Its not a good way.

When I remove getPosts.matchesQuery("user",getProfiles); query runs as expected.

How can we optimize this query?

7

I think you are right. In the short term, I’d try to invert the logic:

const getProfiles = new Parse.Query(Parse.User);
getProfiles.equalTo("private",true);

const getPosts = new Parse.Query(Post);
  getPosts.doesNotMatchQuery("user",getProfiles);
  getPosts.doesNotMatchKeyInQuery("user","who",getBlock);
  getPosts.include("user");
  getPosts.exclude("words");
  getPosts.containsAll("words",wordArray);
  getPosts.descending("createdAt");
  getPosts.limit(40);

  if(request.params.date!==undefined){
    getPosts.lessThan("createdAt",request.params.date);
  }

For the long term, I’d probably try to either denormalizate the data and store the private flag in the User Class but also in the Post class or try to use aggregate for this query.

1 Like
8

Thank you. I looked up for aggregate queries. But I didn’t understand the logic behind it.
But if I understand correctly parse server still fetches objects. İf this is false can you explain a little bit? What are the advantages of aggregate?

for now seems like duplicating private field is the solution.

9

I understand that private=true is only 50 objects, and private=false are 100K-50, that’s why I suggested you to invert the logic, so Parse Server will fetch less object for the inner query.

The aggregate command run in MongoDB side, will not require Parse Server to fetch the inner query data and it will probably be faster.

Duplicating the information for sure will have the best performance.

1 Like
10

Its only 50 objects because I decided that way. İts a test database not a production environment. In production environment we cant know how users will behave.

Seems like duplicating values is best solution. Thank you very much