Slow queries till Nextcloud 30.0.1

[anoriel]

Steps to reproduce

1. migrate from 29.0.x to 30.0.1
2. display files page
3. show processes list in mariadb

Expected behaviour

No delay as previous version

Actual behaviour

The page is loading for a long time, nearly 20sec.
It seems to be caused by slow queries in mariadb (example below)

Server configuration

**Operating system: Ubuntu 22.04

**Web server: nginx

**Database: mariadb 10.6.18

**PHP version: 8.3

**Nextcloud version: 30.0.1

**Group folders version: 18.0.3

**Are you using external storage, if yes which one: no

**Are you using encryption: no

**Are you using an external user-backend, if yes which one: LDAP

Client configuration

**Browser: all (Chrome, firefox, safari...)

**Operating system: all (Mac, Windows, Linux)

Logs

example of slow query:
SELECT f.folder_id, f.mount_point, f.quota, f.acl, c.fileid, c.storage, c.path, c.name, c.mimetype, c.mimepart, c.size, c.mtime, c.storage_mtime, c.etag, c.encrypted, c.parent, a.permissions AS group_permissions, c.permissions AS permissions FROM oc_group_folders f INNER JOIN oc_group_folders_groups a ON f.folder_id = a.folder_id LEFT JOIN oc_filecache c ON (c.name = CONCAT(f.folder_id, '')) AND (c.parent = '12345') LEFT JOIN oc_circles_membership w_j ON (w_j.single_id = 'abcdefghijklmnopqrstuvwxyz12345') AND (w_j.circle_id = a.circle_id) LEFT JOIN oc_circles_circle w_j_k ON w_j.circle_id = w_j_k.unique_id WHERE w_j.level >= 1;

[anoriel]

I forgot to mention that all indexes are ok, no repair command is waiting, no warning in nextcloud/admin page...

[provokateurin]

I suppose you already had the Circles/Teams app enabled before?
How many of those slow queries are you seeing concurrently when you do a single reload of a Groupfolder?
Are all the queries the same or are there different ones as well?

I'm not aware of any changes we did to the Database between 29 and 30, so I don't know if this is actually Groupfolders related or Server or your infrastructure. Can you check if you encounter any other performance problems after you upgraded to 30 (somewhere else, not in Groupfolders)?

[anoriel]

Yes Teams app enabled before and still is.
Nothing has been added or installed between 29 and 30, we only upgraded the core NC.
We have ~40 simultaneous queries but we got ~50 users working daily so it's not easy to isolate one user's queries...
Each query is taking from 2,5 to 6 seconds to execute so the main website is slowed down by this...

[anoriel]

All other apps seem to work perfectly (talk, calendar,...)
Only Files tab is affected

[anoriel]

If I remove the left join to filecache table, the query is executed instantanesously...
As this table is about 13Gb, it's not a surprise...

If I change the query to something like this, the answer is also executed quickely:

SELECT a.*, c.permissions AS permissions, c.fileid, c.storage, c.path, c.name, c.mimetype, c.mimepart, c.size, c.mtime, c.storage_mtime, c.etag, c.encrypted, c.parent FROM ( SELECT f.folder_id, f.mount_point, f.quota, f.acl, a.permissions AS group_permissions FROM oc_group_folders f INNER JOIN oc_group_folders_groups a ON f.folder_id = a.folder_id LEFT JOIN oc_circles_membership w_j ON (w_j.single_id = 'abcdefghijklmnopqrstuvwxyz12345') AND (w_j.circle_id = a.circle_id) LEFT JOIN oc_circles_circle w_j_k ON w_j.circle_id = w_j_k.unique_id WHERE w_j.level >= 1 ) a LEFT JOIN oc_filecache c ON (c.name = CONCAT(a.folder_id, '')) AND (c.parent = '12345') ;

Do you think you can update the app to change the left join to filecache table query?