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.

首先检查 filecache 的 SELECT 查询路径以及 issue 中提到的 config.php 配置；getUserGroups() 示例展示了预期的查询缓存行为。确认 MariaDB/MySQL 如何处理这些查询中的 SQL_NO_CACHE，然后将完成标准定义为可配置的实现，并通过验证或测试覆盖生成的 filecache 查询。