Playing nanny to oc_filecache growing up

ℹ️ Support
,
1

Hi
I would like to share my experience in keeping oc_filecache table under control and perhaps offer some food for thought on the matter.
The database of our NC instance periodically fills up the storage space allocated to it.
The reason is always the same:
users configure an external storage (SMB in our case) that contains millions of files that get recursively indexed.
Today oc_filecache table is ~500GB.

nextcloud> explain select count(*) from oc_filecache;
+------+-------------+--------------+-------+---------------+----------+---------+------+-----------+-------------+
| id   | select_type | table        | type  | possible_keys | key      | key_len | ref  | rows      | Extra       |`
+------+-------------+--------------+-------+---------------+----------+---------+------+-----------+-------------+
|    1 | SIMPLE      | oc_filecache | index | NULL          | fs_mtime | 8       | NULL | 597421495 | Using index |
+------+-------------+--------------+-------+---------------+----------+---------+------+-----------+-------------+

597421495 rows !

So I proceed as follows:

  1. find which ID storage is bloating oc_filecache table using a stored procedure (see below):
nextcloud> CALL count_per_storage();

+---------------------------------------------------------------+------------+-----------+
| storage                                                       | numeric_id | cnt       |
+---------------------------------------------------------------+------------+-----------+
[...]
| smb::user0@mynas//goodpath//                      |       5765 |   9013645 |
| smb::user1@mynas//evilpath//                      |       8719 | 158185971 |
| smb::user2@mynas//evilpath//                      |       8734 | 158200126 |
| smb::user3@mynas//evilpath//                      |       8840 | 159041494 |

(after first call you can do: SELECT * FROM storage_count ORDER BY cnt;

  1. add “evilpath” to excluded dirs list

occ config:app:get files_excludedirs exclude
occ config:app:set files_excludedirs exclude --value '[".snapshot",.......,"evilpath"]'

  1. since deleting hundreds of millions of records from oc_filecache would take days/weeks,
    I leverage percona-toolkit and by mimicking an alter table I exclude unnecessary records
    (see numeric_id above example) from the copy:
    pt-online-schema-change --alter "ENGINE=InnoDB" --where "storage NOT IN (8719,8734,8840)" --user=mysqluser --password=mysqlpassword --host localhost D=nextcloud,t=oc_filecache --execute
    (I have deliberately omitted the --force option in case someone copies&pastes without understanding the potential danger of this command)

#####################################
#with a little help of my frAInd
#this procedure create a ‘storage_count’ table listing all storages with more than 100K files

CREATE PROCEDURE `count_per_storage`()
BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE sid BIGINT;
 DECLARE sname VARCHAR(255);
 DECLARE cnt BIGINT;

 DECLARE cur CURSOR FOR SELECT numeric_id, id FROM oc_storages;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 DROP TABLE IF EXISTS storage_count;
 CREATE TABLE storage_count (
   storage VARCHAR(255),
   numeric_id BIGINT,
   cnt BIGINT
 );

 OPEN cur;
 loop_label: LOOP
   FETCH cur INTO sid, sname;
   IF done THEN LEAVE loop_label; END IF;

   SELECT COUNT(*) INTO cnt FROM oc_filecache WHERE storage = sid;

   IF cnt > 100000 THEN
     INSERT INTO storage_count VALUES (sname, sid, cnt);
   END IF;

 END LOOP;
 CLOSE cur;

 SELECT * FROM storage_count ORDER BY cnt DESC;
END
2

Out of curiosity, since it sounds like you’re allowing individual users to add their own External Storage mounts, how many of these millions of files are duplicates across the mounts? If very few, that’s fine, but I ask because:

  • this has other problems, in addition to filecache growth, for example it breaks collaboration and locking. (The authentication modes used with External Storage is another factor, with similar side effects). The underlying cause - taking some liberties - is that Nextcloud has no way of knowing with that sort of file structure these really are the same files.
  • I wonder if there may be a mount structure in your environment that only results in per-user level (unique) External Storage mounts when it comes to a user’s personal files, whereas other mounts (shared/team) files could be created for other communities of users (eliminating the duplication) at the global level.
3

Hi
yes, users are allowed to configure their own external storage mounts.
This allows occasional access to their own files (without setting up a vpn or an sftp client) from outside the local network to volumes for which individual users or user groups are authorized. Internally we use NFS/CIFS with a certain degree of user/group permissions granularity across the dozens of volumes/shares.
The real need that emerges is for a flag to globally disable the filecache for external mounts.

4

@valeriop

welcome to the community forum(s) of NC. Where volounteers tried to help each other.

out of interest:
this is a technical problem you have, right?

if yes: let’s take a look at the very first sentence of your thread

you answered

which means you have clicked “yes”…

would you please be so kind to let me know why you posted it under “general” though and not under “support”? Maybe there’s a point I’m missing here

5

@JimmyKater
No, I don’t have a technical question or problem for which I am asking for support and, as I wrote, my intent is to share my experience on how I manage the growing database usage caused by the normal operation of fscache in a context different from a home/so environment where at most someone might have several thousand files.
Perhaps this can be of help to someone else. Perhaps someone might get curious enough to look inside their own Nextcloud instance db and save themselves a headache in advance.

6

right. but then again, it’s technical only.

so I’d prefer to find this thread under “support” (nevertheless: YOU offer support under such circumstances)

7

@JimmyKater Feel free to move the discussion to whatever place you think is best, I am not attached to any particular label.
In alternative I could do a fake support request and answer by myself :smiley:
Cheers