File cache table excessively large (and does not shrink after data removal) / Nextcloud should defragment file cache table

[ruedigerkupper]

Problem

My nextcloud instance (small-scale, single-server setup, 6 users) has an excessively large database size (20 GB as of today). This size doesn't sensibly relate to the amount of data managed (approx. 1 TB, now reduced to 4.3 GB).

I have found that almost all of the 20GB is located in a single table, the file cache:

root@helge:~# ls -lh /var/snap/nextcloud/current/mysql/nextcloud/oc_filecache.*
-rw-r----- 1 root root 21K Jan 8 2017 /var/snap/nextcloud/current/mysql/nextcloud/oc_filecache.frm
-rw-r----- 1 root root 19G Nov 27 15:17 /var/snap/nextcloud/current/mysql/nextcloud/oc_filecache.ibd

This file seems to grow and grow, but never shrinks.

Further details

• My nextcloud used to manage roughly 1TB of data, most of them located on external storage (external disks attached to my server).
• External storage was included through nextcloud’s “external storage” app as “Local”.
• I have since removed all external storage from my nextcloud instance:

  root@helge:~# nextcloud.occ files_external:list --all
  No mounts configured

• The data managed by my nextcloud instance is now only 4.3 GB (compared to 1 TB before). Surely this should reduce the file cache?
• I have called “files:scan” and “files:cleanup”, but to no effect:

  root@helge:~# nextcloud.occ files:cleanup
  0 orphaned file cache entries deleted

Here’s something I don’t understand: there should be thousands of orphaned entries now that most of the data is gone!

=> I suspect that my file cache table is somehow broken, but I don’t know how to fix it. I believe I should clear the table and reproduce it, but I do not know how to do that.

Steps to reproduce

Unsure, sorry

Expected behaviour

Flie cache table should shrink after external storage was removed.

Actual behaviour

File cache table stays the same (and is excessively large)

General server configuration

Operating system: Linux helge 4.13.0-17-generic #20-Ubuntu SMP Mon Nov 6 10:04:08 UTC 2017 x86_64

Web server: Apache/2.4.28 (Unix) OpenSSL/1.0.2g (fpm-fcgi)

Database: mysql 5.7.18

PHP version: 7.0.23

PHP-modules loaded

``` - Core - date - libxml - openssl - pcre - sqlite3 - zlib - bz2 - ctype - curl - dom - hash - fileinfo - filter - gd - SPL - iconv - intl - json - mbstring - mcrypt - PDO - session - pdo_sqlite - posix - Reflection - standard - SimpleXML - mysqlnd - exif - tokenizer - xml - xmlreader - xmlwriter - zip - pdo_mysql - cgi-fcgi - redis ```

Nextcloud configuration

Nextcloud version: 11.0.5 (stable) - 11.0.5.1 (snap version 3680)

Updated from an older Nextcloud/ownCloud or fresh install: Nextcloud snap, updated from previous snap versions (3317, 2707)

Where did you install Nextcloud from: snap

Are you using external storage, if yes which one: \OC\Files\Storage\Local, see description for details!

Are you using encryption: no

Are you using an external user-backend, if yes which one: Nextcloud sync client from the ubuntu store

Enabled apps

``` - activity: 2.4.1 - admin_audit: 1.1.0 - audioplayer: 2.2.1 - comments: 1.1.0 - dav: 1.1.1 - federatedfilesharing: 1.1.1 - federation: 1.1.1 - files: 1.6.1 - files_external: 1.1.2 - files_pdfviewer: 1.0.1 - files_sharing: 1.1.1 - files_texteditor: 2.2 - files_trashbin: 1.1.0 - files_versions: 1.4.0 - files_videoplayer: 1.0.0 - firstrunwizard: 2.0 - gallery: 16.0.0 - issuetemplate: 0.2.2 - logreader: 2.0.0 - lookup_server_connector: 1.0.0 - nextcloud_announcements: 1.0 - notifications: 1.0.1 - password_policy: 1.1.0 - provisioning_api: 1.1.0 - serverinfo: 1.1.1 - sharebymail: 1.0.1 - survey_client: 0.1.5 - systemtags: 1.1.3 - theming: 1.1.1 - twofactor_backupcodes: 1.0.0 - workflowengine: 1.1.1 ```

Disabled apps

``` - activitylog - calendar - encryption - external - files_accesscontrol - files_automatedtagging - files_retention - ownbackup - templateeditor - user_external - user_ldap - user_saml ```

Content of config/config.php

``` { "apps_paths": [ { "path": "\/snap\/nextcloud\/current\/htdocs\/apps", "url": "\/apps", "writable": false }, { "path": "\/var\/snap\/nextcloud\/current\/nextcloud\/extra-apps", "url": "\/extra-apps", "writable": true } ], "supportedDatabases": [ "mysql" ], "memcache.locking": "\\OC\\Memcache\\Redis", "memcache.local": "\\OC\\Memcache\\Redis", "redis": { "host": "\/tmp\/sockets\/redis.sock", "port": 0 }, "instanceid": "ocwzswpevaos", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "rkupper.no-ip.org", "helge" ], "datadirectory": "\/media\/Data\/nextcloud\/data", "overwrite.cli.url": "http:\/\/rkupper.no-ip.org", "dbtype": "mysql", "version": "11.0.5.1", "dbname": "nextcloud", "dbhost": "localhost:\/tmp\/sockets\/mysql.sock", "dbport": "", "dbtableprefix": "oc_", "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "logtimezone": "UTC", "installed": true, "mail_smtpmode": "php", "mail_smtpsecure": "tls", "mail_from_address": "ruediger", "mail_domain": "rkupper.no-ip.org", "mail_smtpauthtype": "LOGIN", "mail_smtpauth": 1, "mail_smtpname": "***REMOVED SENSITIVE VALUE***", "mail_smtppassword": "***REMOVED SENSITIVE VALUE***", "loglevel": 3, "maintenance": false, "singleuser": false, "log_rotate_size": 1073741824 } ```

Client configuration

Browser: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/605.1 (KHTML, like Gecko) Version/11.0 Safari/605.1 Ubuntu/17.10 (3.26.1-1ubuntu1) Epiphany/3.26.1 (Web App)

Operating system: GNU/Linux (Ubuntu 17.10)

[ArnisR]

Yes, there is no way with NC commands to reduce oc_filecache table if files&folders are deleted or renamed outside NC. It seems the same apply to external shares. If changes are made outside, NC can only add files to oc_filecache.

I my case oc_filecache was expanded due large amount of photos and previews generated by previewgenerator app (20 previews for each photo generated by default). Every preview is also registered in oc_filecache table. I had to modify previewgenerator, manually delete all previews and all db records for files that referenced to preview folder. Then regenerate reduced amount of previews again. It's time consuming process.

There is definitely need for NC maintenance command which keep oc_filecache table optimized according to local filesystem. It would also allows more easily to use NC for fastlane file upload and managing with direct Samba/FTP shares. Because http/webdav upload for numerous files is lagging and not as robust and safe as Samba/FTP.

[MorrisJobke]

cc @icewind1991

[ruedigerkupper]

@ArnisR: That would explain the size of the file cache: The external disk hosted my photo library and was indexed by previewgenerator. So how would I proceed to reduce the file cache manually? Is there a way to simply clear the table and regenerate it?

[ArnisR]

@ruedigerkupper : I can't say. There are several other records regarding NC system in that table, not only files. So I wouldn't do it. I saw a thread where someone presented php file which make comparison of actual files and records in database and delete db records for the non-existent. But nobody confirms it's working and my knowledge of MySQL is too short to make conclusion.

I didn't risk. Because most overhead was due experiments with previews I'd managed only them. I put NC in maintenance mode and made backup of database. Delete all content of appdata_.../preview/ folder. With phpmyadmin selected (with search) all records in oc_filecache table where column "path" contain appdata_your-NCspecificpath-here/preview/ and deleted them. I have NC12. I don't know maybe NC11 put previews in different folder (under each user).

Then I modified previewgenerator - https://github.com/rullzer/previewgenerator/issues/78

Switch maintenance mode off and run preview:generate-all. In my case it takes almost 3 days to finish generating them for ~85 K photos on i3 CPU.

At this moment I have problem with this - #7269

[ruedigerkupper]

Thanks, ArnisR! It have meanwhile solved my problem, and it turned out too be much easier than that:

• Investigating the oc_filecache table from the mysql prompt showed that it only had abut 4000 rows -- nothing that could account for 20 GB of size!
• However, the table space was largely empty:

  mysql> show table status like "oc_filecache";
  +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+---------+
  | Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free   | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment |
  +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+---------+
  | oc_filecache | InnoDB |      10 | Dynamic    | 4106 |           5119 |    21020672 |               0 |     35749888 | 19579011072 |       20409581 | 2017-11-26 23:42:15 | 2017-11-27 18:56:42 | NULL       | utf8_bin  |     NULL |                |         |
  +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-------------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+---------+

  Note "Data_length" (=21 MB) and "Data_free" (=19 GB)!! So, the removed files had actually been cleaned from the file cache, but the InnoDB table was so badly fragmented that it occupied 1000 times the disk space needed for its data.

Going through the mysql reference, I came about https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html. That states that an InnoDB table can be defragmented by issuing a "no-op" ALTER TABLE command:

mysql> ALTER TABLE oc_filecache FORCE;

And -- magically -- this reduced the table to a file size of 8 MB (!!!):

mysql> show table status like "oc_filecache";
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| oc_filecache | InnoDB |      10 | Dynamic    | 4168 |            381 |     1589248 |               0 |       671744 |   2097152 |       20409581 | 2017-11-28 00:40:41 | NULL        | NULL       | utf8_bin  |     NULL |                |         |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+

Summary

So let's get this straight:

• After removing external storage, Nextcloud left me with a table that occupied 20 GB of disk space, but which contained only 8 MB of data.
• How the table got so badly fragmented is unclear, but obviously it can happen.
• The problem could be solved by a simple mysql command for defragmenting the table (ALTER TABLE oc_filecache FORCE;).
• But neither does the regular nextcloud cron job perform such defragmentation, nor does nextcloud provide a command for manually doing so.
• Instead, the user is required to read the mysql reference (I really followed a steep learning curve -- until yesterday, I had never worked with mysql ;-) )

Feature request

Nextcloud should defragment the oc_filecache table regularly, probably as part of the regular maintenance cron job.

[LnLogN]

Same issue here, our internal next cloud service has only a few hundred users, but the database was over 130GB. We just upgraded from owncloud 9 to nextcloud 12.0.4 and manually cleared the oc_filecache table leaving only the shared links ( with the php script ArnisR mentioned), this leaves only about 400MB, with the scan for all local directories, the database size is only at 700MB(only 2TB data locally), and the web interface functions normally (the service will rescan mounted directories when a user enters that mounted directory, and display the files).

With a closer look at the file system, we found out of the space database inserts are from different users mounting the same file systems over smb (the fs has a few PB of data). The bit of annoying thing is when doing the occ files:scan $user command, the service will actually try to scan all the mounted folders this user have, including the smb mounted folder, which takes forever, we couldn't afford to have users waiting for those scans, so wrote a script to scan only local files.

So here are a few suggestions:

1. please allow files:scan only scan local directory, with something like: occ files:scan --local, because local directories path are much more functionally important than mounted paths
2. have all external paths stored in a separate temporary filecache table, maybe external_filecache? so we can clear the table and not affect the function of the web service.

[rullzer]

Maybe an explicit occ command is fine. But I'm really against doing this in a regular cron job as it rebuilds the table basically. So on larger instances that is not a good idea.

[cgrima]

Same issue here with a 80GB oc_filecache.ibd while my entire database is roughly 4TB. @ArnisR , @LnLogN, could you give a link to the php script you are mentioning?

@ArnisR, could you give an idea on how long the defragmenting took for you?

For Info, I could get the oc_filecache.ibd growing rate from recent backups: Sept. 1, 2017: 46GB Nov.1, 2017: 61GB Dec. 1, 2017: 69GB Dec. 27, 2017: 85GB In the meantime, my database grew up from ~3.5TB to ~4TB, with a mix of small and >1GB files. 7 users.

[pprotschka]

same issue here. my database was 61gigs with only 400gb of data. I had the root of an external ubuntu server mounted... ~73,918,234 ROWs! lol. anyways is there a way to prevent the indexing of external shares?

[McKay1717]

Same issue here 20Go of DB for 200Go of local data Nextcloud version 14.0.3.0

[helmut72]

anyways is there a way to prevent the indexing of external shares?

Second that. I also want to prevent indexing of external shares. It's not needed for my usecase.

[helmut72]

MySQL regulary stops working because of this:

mysql> show table status like "oc_filecache";
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
| Name         | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation   | Checksum | Create_options        | Comment |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
| oc_filecache | InnoDB |      10 | Compressed | 76443704 |            134 | 10273595392 |               0 |  11548246016 |  82837504 |       84169982 | 2018-12-11 12:45:45 | 2019-01-10 09:51:44 | NULL       | utf8mb4_bin |     NULL | row_format=COMPRESSED |         |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
1 row in set (0,00 sec)

[chaosgrid]

*bump

I'm quite surprised that there is no "clean up files in index that have actually been deleted on disk" functionality in Nextcloud, since this is obviously vital to keep Nextcloud running smoothly. In my case, I did move the data directory and it resulted in some files (appdata and __groupfolder files) being duplicated in the filecache because it generated a new storage entry. I did reset the original storage entry but now I have stale filecache entries. I will probably delete those manually now and then I will have to also clean up tables like oc_share and oc_activity for the deleted filecache entries...

I wonder, we also have SMB mounted external storages and for those Nextcloud automatically cleans up the filecache for files that get deleted (and I did not activate the notify feature as far as I'm aware) ... why does this not work for local files?

[da3dsoul]

Is this fixed? I have local storage mapped using the local filesystem, and I not only don't delete files from inside nextcloud, but I delete them from a windows machine over samba to the filesystem directly. A folder that gets hundreds of files added and deleted daily only has 226 entries, like I'd expect, so that's nice to see.

I run this, which I don't think has any cleanup scripts https://github.com/crazy-max/docker-nextcloud I'm on NC 15.2. I do open the directory reasonably often on nextcloud, so if there's a script in there, then that might explain it.

EDIT: Okay, it just cleans them up when I enter each folder, but if I don't, then it gets bigger.

[paulrolandw]

I am now having the same ish issue.

18G /var/lib/mysql/next/oc_filecache.ibd

18G for 5x users (testing) and keeps increasing rapidly is a bit much. We are only using nextcloud with external smb shares, nothing local. This 18G has been gathered in 3 months.

Also, I have 34 million rows, so de-fragment in my case I doubt will do anything, not much free data:

mysql> show table status like "oc_filecache" \G; 1. row Name: oc_filecache Engine: InnoDB Version: 10 Row_format: Compressed Rows: 35592262 Avg_row_length: 349 Data_length: 12424568832 Max_data_length: 0 Index_length: 5660049408 Data_free: 2097152 Auto_increment: 40670072 Create_time: 2019-02-27 11:13:12 Update_time: 2019-05-17 13:38:23 Check_time: NULL Collation: utf8mb4_bin Checksum: NULL Create_options: row_format=COMPRESSED Comment: 1 row in set (0.00 sec)

[helmut72]

My instance with SMB connection only runs flawless without Cron, but with Ajax. No db table blows up.

[nate2014jatc]

I just came across this thread after having my database lock up and kill my server because it kept getting thrown into recovery mode.

MariaDB [nextcloud]> show table status like "oc_filecache" \G;
*************************** 1. row ***************************
            Name: oc_filecache
          Engine: InnoDB
         Version: 10
      Row_format: Compressed
            Rows: 11700392
  Avg_row_length: 413
     Data_length: 4843307008
 Max_data_length: 0
    Index_length: 1800232960
       Data_free: 1884815360
  Auto_increment: 25774142
     Create_time: 2019-10-27 02:04:54
     Update_time: NULL
      Check_time: NULL
       Collation: utf8mb4_bin
        Checksum: NULL
  Create_options: row_format=COMPRESSED
         Comment: 
Max_index_length: 0
       Temporary: N
1 row in set (0.002 sec)

[jgrete]

THe same problem with NC 18.0.3... after years no solution?

show table status like "oc_filecache" \G; 1. row Name: oc_filecache Engine: InnoDB Version: 10 Row_format: Compressed Rows: 43111070 Avg_row_length: 1640 Data_length: 70715596800 Max_data_length: 0 Index_length: 8240365568 Data_free: 3670016 Auto_increment: 68154221 Create_time: 2020-01-21 13:39:53 Update_time: 2020-04-04 12:21:39 Check_time: NULL Collation: utf8mb4_bin Checksum: NULL Create_options: row_format=COMPRESSED Comment: Max_index_length: 0 Temporary: N 1 row in set (0.000 sec)

I was voting for NC on every occasion, but things like this are just ridiculous...

[tanguy-opendsi]

The same problem on nextcloud 17.0.2

[nate2014jatc]

BUMP: Has there really been no discussion or proposition of a fix for this? Perhaps add a note in the setup guide(s) stating that this might be an issue, and offering a (janky, improper) "solution" of running an alter op on the table every XX days?

[sergiomb2]

my oc_filecache.ibd have 134G more than 50% of the storage , may I truncate oc_filecache and run files:scan again ?

[dirks]

Just a footnote, we are running a medium sized (few hundred users) nc instance with a mix of a few TB local and external (smb) storage and do not see the problem. Filecache table has ~10M rows and size is ~3.7 GB, which sounds ok to me. I guess this is because of postgresql-db backends auto-vacuum mostly preventing the fragmentation.

[tanguy-opendsi]

@dirks thx for reply can you give use more info, Nuber of users general size of external storage, how did you configure your external storage ? "General user" ? Best regards

[ruedigerkupper]

@dirks yes, that's very interesting. Would like to know how exactly you avoid the problem!

[dirks]

As written, we are using postgresql as db. If I understand the problem correctly, you are suffering from fragmentation. Postgresql takes care of fragmentation issues via vacuum (there is an auto vacuum maintenance process that runs in the background).

It sounds as if optimize table is what you need on mysql/mariadb systems, after huge deletes and similar changes. You could check how costly a periodic optimize table is. If it is cheap enough maybe nc-devs can be convinced to include it in a cron job. Otherwise it remains the task of the admin to take care of it.

[tanguy-opendsi]

@dirks thx for your reply, but, we need to understand how your Nextcloud work. Ofc you'r using psql as db, but we don't realy your external storage config. We don't need domain, we just need what type of auth did you use

Best regards

[paulrolandw]

Nothing to do with fragmentation, it is nextcloud doing something. I did manage to fix it but can't remember how unfortunately. Will look in the sql logs etc soon. I think it has something to do with the version of samba settings (protocol) and the versions plugin/app.

[helmut72]

how did you configure your external storage ? "General user" ?

My configuration was by user mount, without a global user. It's a long time ago, but I had the feeling, it indexes the big SMB share for every single user = multiple times.

[tanguy-opendsi]

@helmut72 Ofc when your configuration use the credential /user Each access for each user are stored in db. For exemple you have 200 users with 200 access to /SMBSTORAGE/README In db you will have 200 lines .... Follow https://github.com/nextcloud/server/issues/20349

[paulrolandw]

how did you configure your external storage ? "General user" ?

My configuration was by user mount, without a global user. It's a long time ago, but I had the feeling, it indexes the big SMB share for every single user = multiple times.

It's not, we have log in credentials, save in database.

[tanguy-opendsi]

@paulrolandw . If you check your table oc_filecache . Did you have multiple row with same path ?

[paulrolandw]

@paulrolandw . If you check your table oc_filecache . Did you have multiple row with same path ? I do have a high number of rows but db is 5GB over almost two years with 100 users. I do remember setting this up in smb.conf: client max protocol = NT1 client min protocol = NT1

mysql> show table status like "oc_filecache" \G 1. row Name: oc_filecache Engine: InnoDB Version: 10 Row_format: Compressed Rows: 552746 Avg_row_length: 155 Data_length: 85827584 Max_data_length: 0 Index_length: 96231424 Data_free: 3145728 Auto_increment: 600505 Create_time: 2019-02-27 11:13:12 Update_time: NULL Check_time: NULL Collation: utf8mb4_bin Checksum: NULL Create_options: row_format=COMPRESSED Comment: 1 row in set (0.00 sec)

Also, sql history: SET GLOBAL innodb_file_per_table=1;

mysql> EXPLAIN SELECT * FROM oc_filecache \G 1. row id: 1 select_type: SIMPLE table: oc_filecache partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 552749 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)

[tanguy-opendsi]

Hello today :

MariaDB [nextcloud]> show table status like "oc_filecache" \G ; 1. row Name: oc_filecache Engine: InnoDB Version: 10 Row_format: Compressed Rows: 94906843 Avg_row_length: 223 Data_length: 21174935552 Max_data_length: 0 Index_length: 16863305728 Data_free: 4718592 Auto_increment: 218451538 Create_time: 2020-01-27 16:23:23 Update_time: 2020-04-21 08:42:06 Check_time: NULL Collation: utf8mb4_bin Checksum: NULL Create_options: row_format=COMPRESSED Comment: Max_index_length: 0 Temporary: N

94M Row .. Large External storage https://github.com/nextcloud/server/issues/20349

[GieltjE]

This should be a high priority bug, it causess a log of excess logs too:

"remoteAddr":"","user":"--","app":"files_antivirus","method":"","url":"--","message":"Can not open for reading. File: 8021Account:..........

So not only does it increase the table size, increase the required ram, increase the backup sizes it also causes cron and background processes to use more resources.

[kachunkachunk]

This has been a fun issue for me over the past few days. Excessive CPU time was found from a php cron job (never figured out which), but I went down the rabbit hole of discovering my oc_filecache table had 60M rows in it, and taking about 30GB of space. It was due to both deleting/adding files outside of Nextcloud, but primarily it was all down to external SMB storage allowing Nextcloud to cache/scan/detect BTRFS snapshot folders. This multiplies the amount of files it tracks. And if it's not cleaning up these records on deletion, well... that's a lot of redundant files/entries made seemingly non-redundant to the table's perspective, due to datestamps in the path making these unique again.

To cut my table size down, I had to unmount the storage and confirm removal by deleting stagnant external storage rows in the oc_storages table. After that, the occ scan command actually started to delete rows finally, but the process is super inefficient and slow.

It'd be faster to just write a query that deleted all oc_filecache rows that contained #snapshot/* in the path, but I wanted to see how it managed on its own. It's been I think 24 hours and it's done 20M rows out of 60 so far. The MariaDb server has ample resources.

I'm wondering how to keep this thing in check or control. And if my php CPU usage issues will subside after clearing this thing out (I'm not even sure anymore). Admittedly I'm contemplating moving off to something a bit simpler, since I/users are only using a subset of functions that Nextcloud has to offer, and I need it to better handle when external changes occur to files. Heck, even having reasonable handling/access of BTRFS snapshot trees/folders/mounts is beneficial (versions/restoring).

Would be nice if the oc_filecache can just be set up to not track/scan external/SMB storage at all.

[nate2014jatc]

BUMP! Just ran into this issue again 😄

[Pisoko]

For us this became an issue as well:

Name: oc_filecache Engine: InnoDB Version: 10 Row_format: Compressed Rows: 203.321.208 Avg_row_length: 132 _Datalength: 26.861.674.496

And I'm not comfortable start to manipulating the database manually. :( My Database VM is running out of space - and I increased / resized the LVM already this year.

[cls-nebadje]

Got this problem too. Increased the VM disk size already a month ago. It doubled in a month from 8 GB to 16 GB without adding any files. No external drive removed. Just a nextcloud instance sitting there. Was on 21.0.4 or so, just upgraded to 21.0.5 and now upgrading to 22.2.0

Basically this problem arose this summer with more or less the same files which were on this server all the time.

Edit: It doesn't seem to be oc_filecache AFAIKS:

mysql> show table status like "oc_filecache";
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
| Name         | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation   | Checksum | Create_options        | Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
| oc_filecache | InnoDB |      10 | Compressed | 350257 |            159 |    55934976 |               0 |     66527232 |   4194304 |         519199 | 2021-09-09 13:19:05 | 2021-11-09 11:52:31 | NULL       | utf8mb4_bin |     NULL | row_format=COMPRESSED |         |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+-----------------------+---------+
1 row in set (0.00 sec)

It seems that mysql's binlog keeps growing on my machine:

root@nextcloud:/var/lib/mysql# du -ch binlog.*
[...]
13G total

I continue to investigate this and leave this note here since this can be a culprit as well.

Edit 2: Yep, binlog seems to grow with the latest nextcloud releases at least on my machine.

root@nextcloud:/var/lib/mysql# ls -l binlog.0* | wc -l
148

I lowered the binlog expiration time from 30 days to one week:

nano /etc/mysql/mysql.conf.d/mysqld.cnf

# expire logs after 7 days:
binlog_expire_logs_seconds = 604800

Restart the mysql service and voilà:

root@nextcloud:/var/lib/mysql# ls -l binlog.0* | wc -l
43

This lowered the footprint drastically:

root@nextcloud:/var/lib/mysql# du -ch binlog.*
[...]
3.6G    total

[paradeiser]

Same problem: binlog files are eating up all my diskspace until it generates an 'Internal Server Error'.

Now I set 'binlog_expire_logs_seconds = 172800' these two days generate 44 GB of binlog files 😮

If I check these files using 'less' I see mostly ics calendar data. 🧐

[obel1x]

ok, now my honest opinion, checking this issues here and other issues of that product and having a look at filecache structure.

well that table looks like the most important table of nextcloud taking the most work on larger filecollections. Despite it is so important, the structure is not efficient, badly normalized and the handling in nexctloud lacks of features, which leads to performance-, ressource- and stability- problems (like deadlocking, swapping files out, timeouts) as such. I even wonder how larger workload can be handeld by this.

For example: my table has about 1.299.481 entries (which i consider to be a small system). The Datasize itself is ~355 MB (Mariadb, Dynamic Rows). Yesterday the Index size was about 655 MB. So the indices are larger, than the table itself (the Database is defragmented and optimzed). This as such is an inidcation of bad tablemanagement. Having a look at the used querys and needed memory/buffers/tempfiles on database also indicates this. Now, i don't want to complain about it too much, as Nextcloud is my favourite piece of Software in many ways.

So what i am suggesting: Do the maintainers see a chance to refactor the table management of the filecache in NC completly? Until now, my time has been very short, but i may help with that having experience in other systems with refactoring databasestructures and code. I am sure, doing this would be a big benefit for Nexcloud to get accepted in more companies...

I just don't want to start something and get some "we won't do this". And maybe it will take time and help, but i would like to think about getting into this.

I would be happy to hear some opinions from the maintainers here.

[paradeiser]

Even tho I limited binlogs to 2 days the over all size is still growing (time intervals from file to file are getting shorter: currently about 4 min at binlog-files 101M): Now at 65G for binlog files in total, 2 days

804K    /var/lib/mysql/#innodb_temp
207M    /var/lib/mysql/nextcloud
116K    /var/lib/mysql/sys
36K     /var/lib/mysql/mysql
1.6M    /var/lib/mysql/performance_schema
65G     /var/lib/mysql
65G     total

mysql database size: 148,4 MB user files: ~2GB binlog mostly show ics calendar data.

This is getting absurd… any tips how to contain this? Upgrading the server hdd every couple of days doesn't seem to be the right way.

Thanks, paradeiser

[ruedigerkupper]

Please consider opening a new issue for the binlog problem. This issue is about oc_filecache and your problem seems to be unrelated to that (correct me if that's wrong). Please do not hijack other reports, this will only make it more difficult to find solutions. Thanks!

[paradeiser]

Thanks @ruedigerkupper, you are right - I just replied to @cls-nebadje 's comment about the same topic. I opened a new issue about binlog files are growing excessively big and fast - and hid my posting here.

[flotpg]

same issue here - also have excessive writes which already killed a SSD: https://github.com/nextcloud/server/issues/22419

[flotpg]

14 hours ago I executed truncate oc_filecache;

This seems to solved the issue. My oc_filecache was over 300GB, now it's 21MB.

[helmut72]

@flotpg This deletes all favourites of the users.

[ruedigerkupper]

Please see above: https://github.com/nextcloud/server/issues/7312#issuecomment-347377692

[flotpg]

Thanks @ruedigerkupper

[J0WI]

OPTIMIZE TABLE or mysqloptimize should also do the trick.

[obel1x]

OPTIMIZE TABLE or mysqloptimize should also do the trick.

No, they won't - also truncation oc_filecache is not a solution, as the table will be populated again when accessing the files or do a scan. edit: So truncate will be a quick solution for reducing the size, but when using remote files or removable storages, it will grow again and never shrink automagcially. Also truncating has disadvanteges as mentioned above.

imo still, the big writes and reads are also realated to bad structure of the table, building up large index-tables. what helped for me was to remove those indexes: Index "fs_size" in der Tabelle "oc_filecache". Index "fs_id_storage_size" in der Tabelle "oc_filecache". Index "fs_storage_path_prefix" in der Tabelle "oc_filecache".

After that, do optimize table. NC will than complain about those indices missing, but i checked and i am fine with them missing. (in fact there is exactly one strange query in the jobs, selecting files with "size < 0", that is showing in my slowlog - but it is no good idea to index size-cloumn, so its ok)

That cut my tablesize into half without deleting data, reducing writes and reads massively all the time in cost of a few (very few) slower reads in special cases.

Maybe you want to try this and report.