I don’t know if this is the best category in which to post this topic. If there’s a better place for this, please point me in that direction.
I’m looking for advice from other Nextcloud system administrators about strategies in dealing with the oc_filecache table as it continues to grow without any upper bound.
We’re running Nextcloud-11.0.3. Our Nextcloud database is on a MySQL-5.7 server.
Our MySQL-5.7 oc_filecache table now has about 428,000,000 rows and is about 400GB. The time required to dump this table and do backups is becoming increasingly long and unmanageable. Eventually we will have to implement some changes in how we handle this data unless some fundamental changes are made to Nextcloud such that there is not this single large database table.
Has anything significant in this area changed in Nextcloud-12?
What is the future for the oc_filecache table? Is there anything in the Nextcloud roadmap that deals with this specific area? If so, where can I find that documented?
Is there any other documentation that anyone can point me to that discusses issues such as how best to deal with large oc_filecache tables?
Do you have a high number of small files? For the same size as yours, my setup would have 1000 times less rows than yours!
Versioning and trash-bin could also create a lot of additional entries, especially when the cleaning routines don’t work (your cronjob runs regularly)?
Yes, it’s quite conceivable that we do have a large number of small files, as well as a large number of large files, and a large number of any size in between.
I’m running occ file:scan processes now to get counts of all files and filer per user. However, I’m encountering problems like this:
Exception while scanning: An exception occurred while executing 'UPDATE oc_filecache SET size=? WHERE (size <> ? OR size IS NULL) AND fileid = ? ’ with params [0, 0, “4150902”]:
and this happens whether I have file locking disabled or enabled, and before and after flushing the redis server cache, and oc_file_locks is empty.
The Nextcloud cron.php is set to run once every 15 minutes from system cron, and logs show it is running regularly. I’ve disabled this now temporarily while I try to debug that process.
Nevermind the comment in my previous post about the “Lock wait timeout” errors. I realize now that this has nothing to do with Nextcloud file locking, but is a MySQL transaction locking issue. I’m not sure yet how to deal with this, but I imagine it has something to do with MySQL server tuning.
As far as I can tell, my Nextcloud cron process is working fine.
Our problems seem to be related to the number of files our users have, including in external storages, the length of time it’s taking to scan all of these files, and the size of the filecache database. I’m guessing we’re probably pushing the limits of what is viable for our current Nextcloud deployment and the infrastructure it’s running on.
I’ll be looking into the MySQL Galera cluster option, and while this will surely help with database performance and reliability, I don’t know that it’s going to solve the problem of the ever-increasing time required to do backups of the filecache database.
