I noticed that my Nextcloud server uses a lot of CPU periodically, and the timing correlates with running the Nextcloud cron task.
I enabled logging all queries on my PostgreSQL instance and noticed that Nextcloud cron task runs a lot of queries against oc_filecache table:
2024-10-15 18:50:31.782 UTC [40] LOG: execute <unnamed>: SELECT "filecache"."fileid", "storage", "path", "path_hash", "filecache"."parent", "filecache"."name", "mimetype", "mimepart", "size", "mtime", "storage_mtime", "encrypted", "etag", "filecache"."permissions", "checksum", "unencrypted_size", "metadata_etag", "creation_time", "upload_time", "meta"."json" AS "meta_json", "meta"."sync_token" AS "meta_sync_token" FROM "oc_filecache" "filecache" LEFT JOIN "oc_filecache_extended" "fe" ON "filecache"."fileid" = "fe"."fileid" LEFT JOIN "oc_files_metadata" "meta" ON "filecache"."fileid" = "meta"."file_id" WHERE "filecache"."parent" = $1 ORDER BY "name" ASC
This results in a high CPU activity on both Nextcloud and the database container (PostgreSQL 16).
My instance is not very large, about 400K files in total, images make up a fraction of that (10-25K probably).
I've already truncated the oc_filecache table, reindexed the table in Postgres, and done a full rescan via occ, but that did not improve the situation.
I've done all the optimizations mentioned in the issue template, even before using Memories.
Transcoding is enabled. Recognize is disabled.
After disabling Memories app within Nextcloud, the cron task uses a reasonable amount of CPU.
A quick code search over this repo suggests use of the filecache table so I suspect that the load is caused by the Memories app. I've seen similar reports of other people struggling with similar issues on the filecache table, but I did not notice any relations to a specific app like Memories.
I tried searching existing issues for similar topics but could not find one.
Steps To Reproduce
I don't have the time to test this out on a fully clean Nextcloud system, but I reckon it can be tested like this:
set up a clean Nextcloud instance (Docker, Postgres)
install and enable Memories
create a large number of files (1+ million to be safe), a good chunk of them images/videos
set Nextcloud cron task to be run every 5 minutes
observe CPU and container resource usage over time
Describe the bug
I noticed that my Nextcloud server uses a lot of CPU periodically, and the timing correlates with running the Nextcloud cron task.
I enabled logging all queries on my PostgreSQL instance and noticed that Nextcloud cron task runs a lot of queries against
oc_filecachetable:This results in a high CPU activity on both Nextcloud and the database container (PostgreSQL 16).
My instance is not very large, about 400K files in total, images make up a fraction of that (10-25K probably). I've already truncated the
oc_filecachetable, reindexed the table in Postgres, and done a full rescan viaocc, but that did not improve the situation.I've done all the optimizations mentioned in the issue template, even before using Memories. Transcoding is enabled. Recognize is disabled.
After disabling Memories app within Nextcloud, the cron task uses a reasonable amount of CPU.
A quick code search over this repo suggests use of the filecache table so I suspect that the load is caused by the Memories app. I've seen similar reports of other people struggling with similar issues on the filecache table, but I did not notice any relations to a specific app like Memories.
I tried searching existing issues for similar topics but could not find one.
Steps To Reproduce
I don't have the time to test this out on a fully clean Nextcloud system, but I reckon it can be tested like this:
Platform
Screenshots
Attached is the CPU usage graph of my server, with Memories app enabled and disabled.
I have not had this run for a very long time, but the effect is noticeable already.
Additional context
No relevant errors in Nextcloud logs.