Same multiple queries select "filecache"."fileid,

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.

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.

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:

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

1 Like


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.

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 !!