Support MariaDB/MySQL `NO_CACHE` flag for `filecache` SELECT queries

Background

We're operating a Nextcloud server with up to 800 active users in the last 5 minutes. Recently, we have run into performance bottlenecks.

We found that while the CPU and available memory are not an issue, the load on the CPU was capped at around 25% (see Grafana screenshot in details). This resulted in a degraded user experience, for example waiting times of up to 30s when switching file directories.

When looking into the MariaDB process list, there were dozens or sometimes hundreds of SELECT and UPDATE statements waiting for the lock of the filecache table. With almost 1000 UPDATE queries per second, it seems that obtaining/switching the query cache mutex is the limiting factor holding back the system.

To alleviate the issue, we made the query cache optional with SET GLOBAL query_cache_type = 2;. This is obviously a bad idea in some or even many cases, as this increases the load on the database immediately. However, in our case the server had enough hardware reserves and the page load speed increased.

However, the CPU load increased more than is actually necessary, as we basically disabled the query cache for most if not all queries. We quickly fixed this by hardcoding a SELECT SQL_CACHE ... into the getUserGroups() method of the user_saml app, which immediately reduced about 50% of the CPU load (we have really a lot of users and groups).

Suggestion

A better approach than activating the query cache for each single query would be to use the SQL_NO_CACHE statement for SELECT queries in the filecache table with query_cache_type = 1;. This could be activated with a new entry in the config.php.

There is a caveat: we have not yet fully validated this finding. At this point it seems highly likely and I'm very much interested in feedback.

How to use GitHub

• Please use the 👍 reaction to show that you are interested into the same feature.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Beginne mit der Überprüfung der filecache-SELECT-Abfragepfade und der in der Issue erwähnten config.php-Konfiguration; das getUserGroups()-Beispiel zeigt das beabsichtigte Verhalten des Query-Caches. Bestätige, wie MariaDB/MySQL SQL_NO_CACHE für diese Abfragen behandeln, und definiere die Fertigstellung als konfigurierbare Implementierung mit Validierung oder Tests, die die resultierenden filecache-Abfragen abdecken.