MongoDB performance issues with pointer permissions on large collections. #7065

New Issue Checklist

• I am not disclosing a vulnerability.
• I am not just asking a question.
• I have searched through existing issues.
• I can reproduce the issue with the latest version of Parse Server.

Issue Description

After upgrading from parse-server 2.7.4 to parse-server 4.4.0 we found a huge increase on the CPU load on a MongoDB 4.0.11 cluster. The primary node was originally getting peaks of about 20% usage and it got maxed out after the upgrade.

Looking at MongoDB performance logs we noticed that some queries had some logic that could be simplified a lot on collections with about 10s of millions of documents and were using pointer permissions.

A asking MongoDB to explain these find operations were indicating that indexes were used and that it takes about 600ms to perform the request to the MongoDB cluster looking at these queries you can see that some boolean reductions can be done and after making them by hand an explain operation on the same database collection was being executed in just a few milliseconds.

Example of filter statement on a find command found on the MongoDB logs taking 600ms to process:

{ $or: [ { $and: [ { _p_owner: "_User$xxxxxxxxxx" }, { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true } ] }, { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true, _p_buyer: "_User$xxxxxxxxxx" } ], _rperm: { $in: [ null, "*", "*", "xxxxxxxxxx" ] } }

This can be simplified by hand in several steps:

• { $or: [ { $and: [ { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true } ] }, { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true, _p_buyer: "_User$xxxxxxxxxx" } ], _rperm: { $in: [ null, "*", "*", "xxxxxxxxxx" ] } }
• { $or: [ { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true }, { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true, _p_buyer: "_User$xxxxxxxxxx" } ], _rperm: { $in: [ null, "*", "*", "xxxxxxxxxx" ] } }
• { $or: [ { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true } ], _rperm: { $in: [ null, "*", "*", "xxxxxxxxxx" ] } }
• { _p_owner: "_User$xxxxxxxxxx", status: "pending", buyerOffered: true, _rperm: { $in: [ null, "*", "*", "xxxxxxxxxx" ] } }

This last simplified query is executed in less than 5ms. So it seems that MongoDB 4.0 doesn't optimize these queries internally!

Steps to reproduce

In a large collection (16M documents) using at least two pointer permissions on MongoDB 4.0 queries perform from one of the users that are allowed to read a write the documents.

Schema used:

{ "className": "Offer", "fields": { "objectId": { "type": "String" }, "createdAt": { "type": "Date" }, "updatedAt": { "type": "Date" }, "ACL": { "type": "ACL" }, "status": { "type": "String" }, "owner": { "type": "Pointer", "targetClass": "_User" }, "buyer": { "type": "Pointer", "targetClass": "_User" }, "offerAmount": { "type": "Number" }, "originalPrice": { "type": "Number" }, "responded": { "type": "Boolean" } }, "classLevelPermissions": { "find": { "owner": true, "buyer": true }, "count": { "owner": true, "buyer": true }, "get": { "owner": true, "buyer": true }, "create": { "*": true, "requiresAuthentication": true, "owner": true, "buyer": true }, "update": { "owner": true, "buyer": true }, "delete": { "owner": true, "buyer": true }, "addField": {}, "protectedFields": {}, "readUserFields": [ "owner", "buyer" ], "writeUserFields": [ "owner", "buyer" ] }, "indexes": { "_id_": { "_id": 1 }, ... } }

Actual Outcome

Slow queries to MongoDB of the order of 600-800ms on large collections (16M+ documents)

Expected Outcome

Much faster queries

Environment

parse-server: 4.4.0
parse: 2.18.0

Server

• Parse Server version: 4.4.0
• Operating system: Debian hosting k8s
• Local or remote host (AWS, Azure, Google Cloud, Heroku, Digital Ocean, etc): DOKS

Database

• System (MongoDB or Postgres): MongoDB
• Database version: 4.0.11 replica set
• Local or remote host (MongoDB Atlas, mLab, AWS, Azure, Google Cloud, etc): in cluster

Client

• SDK (iOS, Android, JavaScript, PHP, Unity, etc): iOS and Node.JS
• SDK version: iOS SDK 1.19.0, JS SDK 2.18

Logs