Nextcloud oc_filecache growing huge : too many rows (hence backup fails with : lost connection to MySQL)

Thank you for the suggestions for my off topic problem :slight_smile:

For your problem I am out of ideas, sorry :frowning:

1 Like

I might have a broader issue: several times a day, my server start lagging a lot, and mysql takes a very high load, for no apparent reason. Restarting it solves the issue. Waiting a long time too (such as 1h).

Did you install the ‘prepare thumbnails’ plugin? That would be my first suspect (mostly because it is the only one I can think of :stuck_out_tongue: )

I didn’t.

Turns out I have another issue that is causing this: my oc_filecache table has… 12495189 rows. 38GB.
I don’t have that many files.
In fact around 10000000 “cached files” rows are from… /home/myaccount/ folder… Which is almost empty, apart from a symbolic link to /media, where I mount an external HDD (via USB, not smb or such). Only something like 10 000 rows are dedicaded directly to /media (in another group of oc_filecache table).

This explains also why mysql has been regularly overloading my server… turns out there is an issue with file caching ?

Related to : Nextcloud upgrade: how to shrink oc_filecache.ibd (441GB at the moment), same issue as you @wbk
(and maybe Le cron.php de nextcloud pompe toutes les ressources - #2 by tierce Something hammering MySQL (5M/s disk writes)?)

Sorry for keeping silent, yes, I also had the filecaching problem.
I removed all external storage from the Nextcloud configuration, but the problem persists because my server is not capable enough to remove records from the table. No help from me, at this point :frowning:
I may have some notes with a simple cursor for removing cache rows, let me check…

I “solved” my issue by very slowly (it took days) deleting most of these lines, from the category belonging to some “external storage” (actually /home, with a few symbolic link pointing to an external storage mounted that was counted a few hundred times…), then doing a “truncate” to empty the database table. The filecache rebuilding went well.

Problem not solved: the nextcloud cron did reindex several times some files (from one external storage, /home)… Disk full again.

:frowning:

I wished there were a more lightweight option for Nextcloud to present files through the web interface. As far as I understand, any file to be shown in Nextcloud, has to come from the cache. It would be nice if it could just link to the file on disk as long as no changes/versioning is made.

Sorry not to reply any sooner. I did look for my cleanup scripts, but have not found them.

1 Like

@wbk @Lapineige same issue as yours with a 2.7Gb oc_filecache !
I have 6 SMB/CIFS external_storage from NAS…
I understand why my NC has become so slow !
I was migrating it from LXC to Docker… and the SQL backup show me the issue.
I will report if I find something than can help !

1 Like

That’s a “tiny” one, mine went up to ~42GB :sweat_smile:

For your migration: just empty that file, (mariadbuse nextcloudtruncate oc_filecache), Nextcloud will recreate it.

2 Likes

Thanks !
I’ll give a try (actually is already in process…)

Me too. Thanks @Lapineige!

Back then I have spend hours (days, if not weeks) trying to delete just the records matching the external storage. That ran fast enough, but there were millions of records to be removed, and they got added again in a blink of an eye.

Now I can hardly believe that I just freed 330 GB of tablespace in less than 20 seconds :rofl:

MariaDB [nextcloud]> truncate oc_filecache; Query OK, 0 rows affected (17.120 sec)

I might finally be able to upgrade away from NC 18 :stuck_out_tongue:

truncate did run faster in my case too.
But the first time, it took days to remove a few million lines, and ~1 day to truncate.
Now truncating takes <1min, removing lines is still as slow.
I don’t know why. Maybe the storage is less overloaded.

works fine !

I do the trick…

LXC was too long and I stop it (it was the old instance)…
Docker look faster and the truncate take only some few seconds…

Thanks

To conclude my story with the oc_filecache:

YNH_APP_CURRENT_VERSION: 18.0.4~ynh1
YNH_APP_ID: nextcloud
YNH_APP_INSTANCE_NAME: nextcloud
YNH_APP_INSTANCE_NUMBER: '1'
YNH_APP_MANIFEST_VERSION: 22.1.1~ynh1
YNH_APP_UPGRADE_TYPE: UPGRADE_APP

....

2021-10-03 13:30:31,174: DEBUG - + echo '[####################] > Upgrade of nextcloud completed'
2021-10-03 13:30:31,174: DEBUG - + ynh_exit_properly
2021-10-03 13:30:31,174: DEBUG - + local exit_code=0
2021-10-03 13:30:31,175: DEBUG - + rm -rf /var/cache/yunohost/download/
2021-10-03 13:30:31,175: INFO - [####################] > Upgrade of nextcloud completed
2021-10-03 13:30:31,178: DEBUG - + '[' 0 -eq 0 ']'
2021-10-03 13:30:31,178: DEBUG - + exit 0
2021-10-03 13:30:32,180: DEBUG - Checking that required services are up and running...
2021-10-03 13:30:34,176: SUCCESS - nextcloud upgraded
success: true

Nextcloud did go into some kind of error mode after the upgrade (and did not recover yet), but that is a story for another thread :wink:

Nextcloud is running again, and I see I lost my shares.

I am quite sure the losing of shared files and directories is not a result of the upgrade, but of truncating oc_filecache (I remember reading somewhere that oc_filecache is needed for the sharing).

So, keep in mind: don’t truncate oc_filecache when you just shared a bunch of party photo’s with your friends!

For the actual error after the upgrade, I found it in /var/log/daemon.log. Many lines of:


Oct  3 15:25:07 online mysqld[553]: 2021-10-03 15:25:07 106 [ERROR] mysqld: Out of sort memory, consider increasing server sort buffer size
Oct  3 15:25:07 online mysqld[553]: 2021-10-03 15:25:07 106 [Warning] Sort aborted, host: localhost, user: nextcloud, thread: 106, query: SELECT `share`.`id`, `share`.`share_type`, `share`.`share_with`, `share`.`uid_owner`, `share`.`uid_initiator`, `share`.`parent`, `share`.`item_type`, `share`.`item_source`, `share`.`item_target`, `share`.`file_source`, `share`.`file_target`, `share`.`permissions`, `share`.`stime`, `share`.`accepted`, `share`.`expiration`, `share`.`token`, `share`.`mail_send`, `share_circle`.`unique_id` AS `share_circle_unique_id`, `share_circle`.`name` AS `share_circle_name`, `share_circle`.`display_name` AS `share_circle_display_name`, `share_circle`.`sanitized_name` AS `share_circle_sanitized_name`, `share_circle`.`source` AS `share_circle_source`, `share_circle`.`description` AS `share_circle_description`, `share_circle`.`settings` AS `share_circle_settings`, `share_circle`.`config` AS `share_circle_config`, `share_circle`.`contact_addressbook` AS `share_circle_contact_addressbook`, `share_circle`.`contact_groupname` AS `share_circle_contact_groupname`, `

I changed the value of
mariadb.cnf:sort_buffer_size = 64K
to
mariadb.cnf:sort_buffer_size = 16M
and
service mysql restart
for good measure.

Oh ! I didn’t know that ! :frowning:
But can it build the cache again ? (those files would not be available in the mid-time, but then… ?)

I am not so sure about that. Shares are in oc_share, and poin to a record in oc_filecache by fileid.
After truncating oc_filecache, I was happy enough to be able to upgrade, and did not take time to check if anything changed. In theory it could be that the upgrade from NC18 to NC22 messed up shares, but I doubt that (there would have been outcry if an upgrade did that :-P)

With the current data base scheme there would be no point in keeping oc_share-records after deleting records from oc_filecache: there’s no way to guarantee the correct relation between the share indications and the actual file being shared after rebuilding the cache (with new fileid’s).