Same multiple queries select "filecache"."fileid,

[Jean-Charles]

Nextcloud version (eg, 20.0.5): 21.0.9
Operating system and version (eg, Ubuntu 20.04): debian 10
Apache or nginx version (eg, Apache 2.4.25): nginx
PHP version (eg, 7.4): 7.3-fpm

The issue you are facing:
The same issue happened with the nextcloud v.20.0.12. So I just installed the 21.0.9, but while watching the database logs, I noticed that the query below (always with the same parameters) is executed multiple times over and over. It results in slowing the database heavily. Sometimes the query can reach near 1800ms when there are about a 100 connections to the database.

2022-07-08 11:09:01.042 CEST [19569] partage@partage LOG: duration: 654.906 ms execute : SELECT “filecache”.“fileid”, “storage”, “path”, “path_hash”, “filecache”.“parent”, “name”, “mimetype”, “mimepart”, “size”, “mtime”, “storage_mtime”, “encrypted”, “etag”, “permissions”, “checksum”, “metadata_etag”, “creation_time”, “upload_time” FROM “oc_filecache” “filecache” LEFT JOIN “oc_filecache_extended” “fe” ON “filecache”.“fileid” = “fe”.“fileid” WHERE (“storage” = $1) AND (“path_hash” = $2)
2022-07-08 11:09:01.042 CEST [19569] partage@partage DETAIL: parameters: $1 = ‘1’, $2 = ‘d41d8cd98f00b204e9800998ecf8427e’

I found this topic of someone facing the same issue.

Massive increase in the number of queries after migration

Support intro

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces: example Or for longer, use three backticks above and below the code snippet: longer example here Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can

Nextcloud version…

But it doesn’t solve the problem when re-installing nextcloud.

The query builder is located in the function “selectFileCahe” in the php script “nextcloud/lib/private/Files/Cache/CacheQueryBuilder.php”, but I don’t know what’s triggering it.

Is this the first time you’ve seen this error? (Y/N): N

Steps to replicate it:

The output of your Nextcloud log in Admin > Logging:
Nothing useful.

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):
Nothing useful.

The output of your Apache/nginx/system log in /var/log/____:
Nothing useful.

Output errors in nextcloud.log in /var/www/ or as admin user in top right menu, filtering for errors. Use a pastebin service if necessary.
Nothing useful.

[tflidd]

You didn’t migrate from owncloud, so in your case it might be different.

Did your cronjobs run recently? Do you use any of the caching mechanisms (APCU/Redis)?
Cronjobs can clean up old stuff that potentially clutters the database, and caching reduces the load of the database. In that context, in can change a lot when optimizing the database-caching itself.

Next thing is the Nextcloud version, 21 went out of support in February:

Maintenance and Release Schedule · nextcloud/server Wiki

Especially for the latest version they reported some improvements on the database load.

[Jean-Charles]

Hello,

Thanks for your answer.

Yes cronjobs run normally.
I use both cachings mechanisms, APCU and redis :
‘memcache.distributed’ => ‘\OC\Memcache\Redis’,
‘memcache.locking’ => ‘\OC\Memcache\Redis’,
‘memcache.local’ => ‘\OC\Memcache\APCu’,

I’ve already noticed this issue with the previous version (20.0.12), and the upgrade didn’t solve anything. I’ve to finish my tests before upgrading to 22.

[Jean-Charles]

Hi,
finally solved !!
index was missing on table oc_filecache (columns storage + path_hash).
this query is executed all the time when you open a file, but now, 0.1 sec !!