Slow queries (rocketchat_message, rocketchat_uploads etc) after upgrade from 5.4.5 to 6.4.1. Very slow!

Description:

Hello!

After the upgrade from 5.4.5 to 6.4.1, Rocket.Chat is very slow.
In mongo logs i see:
"cached plan was less efficient than expected: expected trial execution to take 85 works but it took at least 850 works"
"Slow query"
and other

Steps to reproduce:

Validate database collections is ok.
Disable some services in "Troubleshooting" didn't help

Expected behavior:

Stable correct operation and correction of slow queries. Mongo optimization?

Server Setup Information:

• Version of Rocket.Chat Server: 6.4.1
• Operating System: Centos 7
• Deployment Method: docker
• Number of Running Instances: 1
• DB Replicaset Oplog: Enable
• NodeJS Version: 14.21.3
• MongoDB Version: 5.0

Client Setup Information

• Desktop App (3.8.16)
• Browsers (last versions)
• Mobile 4.36

Additional context

Relevant logs:

"ctx":"conn115","msg":"Slow query","attr":{"type":"command","ns":"rocketchat.$cmd","command":{"update":"rocketchat_sessions","updates":[{"q":{"instanceId":"123e8ba2-65c1-465f-b425-12319e81dce7","sessionId":"4GgTKLfgetY7T533","closedAt":{"$exists":false}},"u":{"$set":{"closedAt":{"$date":"2023-11-21T13:23:24.318Z"},"lastActivityAt":{"$date":"2023-11-21T13:23:24.318Z"},"_updatedAt":{"$date":"2023-11-21T13:23:24.318Z"}}}}],"ordered":true,"lsid":{"id":{"$uuid":"1e6657f2-aecc-4629-94db-0aee9a3a5f51"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1700573004,"i":5}},"signature":{"hash":{"$binary":{"base64":"AAAAAAAAAAAAAAAAAAAAAAAAAAA=","subType":"0"}},"keyId":0}},"$db":"rocketchat"},"numYields":0,"reslen":245,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":1}},"ReplicationStateTransition":{"acquireCount":{"w":3}},"Global":{"acquireCount":{"r":2,"w":1}},"Database":{"acquireCount":{"w":1}},"Collection":{"acquireCount":{"w":1}},"Mutex":{"acquireCount":{"r":1}}},"flowControl":{"acquireCount":1,"timeAcquiringMicros":4},"readConcern":{"level":"local","provenance":"implicitDefault"},"writeConcern":{"w":"majority","wtimeout":0,"provenance":"implicitDefault"},"storage":{},"remote":"192.168.131.4:51704","protocol":"op_msg","durationMillis":133}}
"ctx":"conn11","msg":"Slow query","attr":{"type":"command","ns":"rocketchat.rocketchat_message","command":{"find":"rocketchat_message","filter":{"_hidden":{"$ne":true},"rid":"EE8hr2kK2p3azXeeeKy8vKquwE3ipMNGG8","_updatedAt":{"$gt":{"$date":"2023-11-21T12:34:09.740Z"}}},"sort":{"ts":-1},"lsid":{"id":{"$uuid":"3era9f31-eaa1-49e8-9c12-89d334328247"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1700572990,"i":5}},"signature":{"hash":{"$binary":{"base64":"AAAAAAAAAAAAAAAAAAAAAAAAAAA=","subType":"0"}},"keyId":0}},"$db":"rocketchat"},"planSummary":"IXSCAN { rid: 1, ts: 1, _updatedAt: 1 }","keysExamined":5050,"docsExamined":3,"fromMultiPlanner":true,"replanned":true,"replanReason":"cached plan was less efficient than expected: expected trial execution to take 85 works but it took at least 850 works","cursorExhausted":true,"numYields":21,"nreturned":3,"queryHash":"631DE22B","planCacheKey":"6D781A93","reslen":1649,"locks":{"Global":{"acquireCount":{"r":22}},"Mutex":{"acquireCount":{"r":1}}},"readConcern":{"level":"local","provenance":"implicitDefault"},"storage":{"data":{"bytesRead":16400456,"timeReadingMicros":44262}},"remote":"192.168.131.4:52874","protocol":"op_msg","durationMillis":194}}
"ctx":"conn27","msg":"Slow query","attr":{"type":"command","ns":"rocketchat.rocketchat_message","command":{"find":"rocketchat_message","filter":{"_hidden":{"$ne":true},"rid":"54rJoXDDsKhwZ8khQKdpCF83GLRDWLECan","_updatedAt":{"$gt":{"$date":"2023-11-21T12:45:48.786Z"}}},"sort":{"ts":-1},"lsid":{"id":{"$uuid":"15t0a028-a119-43c8-a650-16feb5ee3470"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1700573428,"i":1}},"signature":{"hash":{"$binary":{"base64":"AAAAAAAAAAAAAAAAAAAAAAAAAAA=","subType":"0"}},"keyId":0}},"$db":"rocketchat"},"planSummary":"IXSCAN { rid: 1, ts: 1, _updatedAt: 1 }","keysExamined":3589,"docsExamined":6,"fromMultiPlanner":true,"cursorExhausted":true,"numYields":15,"nreturned":6,"queryHash":"631DE22B","planCacheKey":"6D781A93","reslen":2575,"locks":{"Global":{"acquireCount":{"r":16}},"Mutex":{"acquireCount":{"r":1}}},"readConcern":{"level":"local","provenance":"implicitDefault"},"storage":{},"remote":"192.168.131.4:53040","protocol":"op_msg","durationMillis":104}}
"ctx":"conn33","msg":"Slow query","attr":{"type":"command","ns":"rocketchat.rocketchat_notification_queue","command":{"findAndModify":"rocketchat_notification_queue","query":{"$and":[{"$or":[{"sending":{"$exists":false}},{"sending":{"$lte":{"$date":"2023-11-21T13:18:24.262Z"}}}]},{"$or":[{"schedule":{"$exists":false}},{"schedule":{"$lte":{"$date":"2023-11-21T13:23:24.262Z"}}}]},{"error":{"$exists":false}}]},"remove":false,"new":false,"upsert":false,"sort":{"ts":1},"update":{"$set":{"sending":{"$date":"2023-11-21T13:23:24.262Z"}}},"lsid":{"id":{"$uuid":"aada6f14-ab35-42e3-bc75-0c818bafc0e3"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1700573004,"i":5}},"signature":{"hash":{"$binary":{"base64":"AAAAAAAAAAAAAAAAAAAAAAAAAAA=","subType":"0"}},"keyId":0}},"$db":"rocketchat"},"planSummary":"IXSCAN { ts: 1 }","keysExamined":33,"docsExamined":33,"nMatched":1,"nModified":1,"nUpserted":0,"keysInserted":1,"keysDeleted":0,"numYields":0,"queryHash":"F5EE72FD","planCacheKey":"9A7A384A","reslen":783,"locks":{"ParallelBatchWriterMode":{"acquireCount":{"r":1}},"ReplicationStateTransition":{"acquireCount":{"w":2}},"Global":{"acquireCount":{"r":1,"w":1}},"Database":{"acquireCount":{"w":1}},"Collection":{"acquireCount":{"w":1}},"Mutex":{"acquireCount":{"r":1}}},"flowControl":{"acquireCount":1,"timeAcquiringMicros":2},"readConcern":{"level":"local","provenance":"implicitDefault"},"writeConcern":{"w":"majority","wtimeout":0,"provenance":"implicitDefault"},"storage":{},"remote":"192.168.131.4:54844","protocol":"op_msg","durationMillis":188}}

Start with the supplied MongoDB slow-query logs for rocketchat_message, rocketchat_sessions, and rocketchat_notification_queue, then compare the reported filters, sorts, examined keys, and query plans after the upgrade. Check the affected collections and existing indexes; done means identifying the cause of the regression and verifying stable query performance.